5 Replies Latest reply: Feb 12, 2013 7:38 PM by moslee RSS

    Oracle Traditional Import Overrides Password

    moslee
      Hi to all

      I had just successfully finished a full importing from Oracle 9i DB to Oracle 11gR2 DB. My export was a full db export.

      Prior to this importing, my 11g was a newly created DB with the default SYS, System etc.. schema. Their passwords is different from those in 9i.

      However, i realised that after importing... their passwords in 11g was replaced by those passwords in 9i, including SYS and SYSTEM user...

      Is this normal? Thanks for sharing..
        • 1. Re: Oracle Traditional Import Overrides Password
          Srini Chavali-Oracle
          Pl post the complete export and import commands used, along with the first 15 lines of the export and import logs

          HTH
          Srini
          • 2. Re: Oracle Traditional Import Overrides Password
            moslee
            Hi Srini

            Here is the details.... I deleted my export log.. Is it that under normal circumstances, password file in the new database will not be override?

            exp system/<pwd>@db FULL=Y FILE=export.dmp log=export.log
            imp system/<pwd>@dbnew FULL=Y FILE=export.dmp log=import.log


            Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
            With the Partitioning, OLAP, Data Mining and Real Application Testing options

            Export file created by EXPORT:V09.02.00 via conventional path
            import done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set
            export client uses US7ASCII character set (possible charset conversion)
            . importing SYSTEM's objects into SYSTEM
            IMP-00017: following statement failed with ORACLE error 3214:
            "CREATE UNDO TABLESPACE "UNDOTBS" BLOCKSIZE 4096 DATAFILE 'F:\ORADATA\UNDO\"
            "UNDOTBS.DBF' SIZE 5120 , 'F:\ORADATA\UNDO\UNDOTBS1.DBF' SIZE 5120 "
            " EXTENT MANAGEMENT LOCAL "
            IMP-00003: ORACLE error 3214 encountered
            ORA-03214: File Size specified is smaller than minimum required
            IMP-00015: following statement failed because the object already exists:
            "CREATE TEMPORARY TABLESPACE "TEMP" BLOCKSIZE 4096 TEMPFILE 'D:\ORADATA\TEM"
            "P\TEMP.DBF' SIZE 7524 AUTOEXTEND ON NEXT 20971520 MAXSIZE 8192M EXTE"
            "NT MANAGEMENT LOCAL UNIFORM SIZE 10485760"

            Edited by: moslee on Nov 26, 2012 5:13 PM
            • 3. Re: Oracle Traditional Import Overrides Password
              Srini Chavali-Oracle
              I have not experienced this situation of the password being replaced for SYS and SYSTEM upon an import - can you reproduce this behavior consistently ?

              HTH
              Srini
              • 4. Re: Oracle Traditional Import Overrides Password
                moslee
                Hi Srini

                Oh yes...I tested a few times and my SYSTEM & SYS password were replaced by the 9i password (redo the whole testing with a new installation of OS server and Oracle 11g) . I am able to login in 11g for those user schema imported from Oracle 9i.. Their passwords are the same as in 9i.. Will not the password file from 9i replaced the 11g password file?

                Here are the details and first 15 lines of my latest export/import log...

                In Oracle 9i (source)
                ----------------------------
                1. set NLS_LANG=.US7ASCII
                2. exp system/<pwd>@db FULL=Y INDEXES=N FILE=DB_27Nov12.DMP LOG=DB_27Nov12.log

                ===============
                Export Log
                ===============
                Connected to: Oracle9i Enterprise Edition Release 9.2.0.8.0 - Production
                With the Partitioning, OLAP and Oracle Data Mining options
                JServer Release 9.2.0.8.0 - Production
                Export done in US7ASCII character set and AL16UTF16 NCHAR character set
                Note: indexes on tables will not be exported

                About to export the entire database ...
                . exporting tablespace definitions
                . exporting profiles
                . exporting user definitions
                . exporting roles
                . exporting resource costs
                . exporting rollback segment definitions
                . exporting database links
                . exporting sequence numbers
                . exporting directory aliases
                . exporting context namespaces
                . exporting foreign function library names
                . exporting PUBLIC type synonyms
                . exporting private type synonyms
                . exporting object type definitions
                . exporting system procedural objects and actions
                . exporting pre-schema procedural objects and actions
                . exporting cluster definitions
                . about to export SYSTEM's tables via Conventional Path ...
                . . exporting table AQ$_INTERNET_AGENTS 0 rows exported
                ===================================


                Copy the dmp file from old server over to the new server using Run program
                .
                .

                In Oracle 11g (destination)
                ----------------------------
                1. Created a new 11g database with pre-created tablespaces..
                2. imp system/<pwd>@dbnew FULL=Y IGNORE=Y FILE=DB_27Nov12.DMP LOG=import_DB_27Nov12.log

                ===============
                Import Log
                ===============
                Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
                With the Partitioning, OLAP, Data Mining and Real Application Testing options

                Export file created by EXPORT:V09.02.00 via conventional path
                import done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set
                export client uses US7ASCII character set (possible charset conversion)
                . importing SYSTEM's objects into SYSTEM
                IMP-00017: following statement failed with ORACLE error 29339:
                "CREATE UNDO TABLESPACE "UNDOTBS" BLOCKSIZE 4096 DATAFILE 'F:\ORADATA\UNDO\"
                "UNDOTBS.DBF' SIZE 5120M , 'F:\ORADATA\UNDO\UNDOTBS1.DBF' SIZE 5120M "
                " EXTENT MANAGEMENT LOCAL "
                IMP-00003: ORACLE error 29339 encountered
                ORA-29339: tablespace block size 4096 does not match configured block sizes
                IMP-00017: following statement failed with ORACLE error 29339:
                "CREATE TEMPORARY TABLESPACE "TEMP" BLOCKSIZE 4096 TEMPFILE 'D:\ORADATA\TEM"
                "P\TEMP.DBF' SIZE 7524M AUTOEXTEND ON NEXT 20971520 MAXSIZE 8192M EXT"
                "ENT MANAGEMENT LOCAL UNIFORM SIZE 10485760"
                IMP-00003: ORACLE error 29339 encountered
                ORA-29339: tablespace block size 4096 does not match configured block sizes
                IMP-00017: following statement failed with ORACLE error 29339:
                "CREATE TABLESPACE "USERS" BLOCKSIZE 4096 DATAFILE 'D:\ORADATA\OFA\USERS.DB"
                "F' SIZE 838860800 AUTOEXTEND ON NEXT 10485760 MAXSIZE 1048576000 EX"
                "TENT MANAGEMENT LOCAL UNIFORM SIZE 327680 ONLINE PERMANENT NOLOGGING SEGM"
                "ENT SPACE MANAGEMENT AUTO"
                ===================================

                *This change of password is not a serious problem to me as there are only 2 DBAs who knows those passwords.. Just curious to know more about this issue..

                Edited by: moslee on Nov 27, 2012 5:37 PM
                • 5. Re: Oracle Traditional Import Overrides Password
                  moslee
                  Password being replaced for SYS and SYSTEM upon a full import. (No idea why)