4 Replies Latest reply: Aug 20, 2012 11:19 AM by Dean Gagne-Oracle RSS

    Importing enitre database as new SID and in different location

    956430
      Hello everybody,

      I've received a .dmp file from a client and I am having a difficult time trying to restore it.

      I am running the following command:

      imp MED/<password>@127.0.0.1/MED_TEST file=/home/oracle/Desktop/backup.dmp FULL=y STATISTICS=none log=/home/oracle/Desktop/recent_attempt.log

      I was expecting that to try to import the data under the SID MED_TEST (instead of MED), however this isn't happening- There are no changes to the MED_TEST.
      I checked the log file and it says that it's a IMP-00015 error when attempting to create everything in /oradata/MED/datafiles/* where as the all of the other SID's (including MED_TEST) have their data located in the directory of /u01/app/oracle/product/11/2/0/dbhome_3.
      Also all of the triggers are attempting to be bound to MED.table_names instead of MED_TEST.

      How do I import this under a different SID? (I don't mind if the data get put in a different directory- I'm just not sure if it will work that way)

      I am working on a CentOS machine. Please let me know if there is any other relevant information that would help.

      Thanks, I've been struggling with this all yesterday and today,
      -Chris
        • 1. Re: Importing enitre database as new SID and in different location
          Dean Gagne-Oracle
          Can you post the first 20 or so lines from the import command. This may help us determine what is happening.

          Thanks

          Dean
          • 2. Re: Importing enitre database as new SID and in different location
            956430
            Yes, gladly:

            Here is the beginning of the log file:
            -----------------------
            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:V11.02.00 via conventional path

            Warning: the objects were exported by SYSTEM, not by you

            import done in US7ASCII character set and AL16UTF16 NCHAR character set
            import server uses WE8MSWIN1252 character set (possible charset conversion)
            . importing SYSTEM's objects into SYSTEM
            IMP-00015: following statement failed because the object already exists:
            "CREATE TABLESPACE "SYSAUX" BLOCKSIZE 8192 DATAFILE '/oradata/MED/datafiles"
            "/sysaux01.dbf' SIZE 817889280 AUTOEXTEND ON NEXT 10485760 MAXSIZE 32"
            "767M EXTENT MANAGEMENT LOCAL AUTOALLOCATE ONLINE PERMANENT SEGMENT SPACE"
            " MANAGEMENT AUTO"
            IMP-00015: following statement failed because the object already exists:
            "CREATE UNDO TABLESPACE "UNDOTBS1" BLOCKSIZE 8192 DATAFILE '/oradata/MED/da"
            "tafiles/undotbs01.dbf' SIZE 120586240 AUTOEXTEND ON NEXT 5242880 MAX"
            "SIZE 32767M EXTENT MANAGEMENT LOCAL "
            IMP-00015: following statement failed because the object already exists:
            "CREATE TEMPORARY TABLESPACE "TEMP" BLOCKSIZE 8192 TEMPFILE '/oradata/MED/d"
            "atafiles/temp01.dbf' SIZE 750780416 AUTOEXTEND ON NEXT 655360 MAXSIZ"
            "E 32767M EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1048576"
            IMP-00015: following statement failed because the object already exists:
            "CREATE TABLESPACE "USERS" BLOCKSIZE 8192 DATAFILE '/oradata/MED/datafiles/"
            "users01.dbf' SIZE 216268800 AUTOEXTEND ON NEXT 1310720 MAXSIZE 32767"
            "M EXTENT MANAGEMENT LOCAL AUTOALLOCATE ONLINE PERMANENT SEGMENT SPACE MA"
            "NAGEMENT AUTO"
            IMP-00017: following statement failed with ORACLE error 1119:
            "CREATE TABLESPACE "MED" BLOCKSIZE 8192 DATAFILE '/oradata/MED/datafiles/me"
            "d.dbf' SIZE 134217728 AUTOEXTEND ON NEXT 33554432 MAXSIZE 4092M EXTE"
            "NT MANAGEMENT LOCAL AUTOALLOCATE ONLINE PERMANENT SEGMENT SPACE MANAGEME"
            "NT AUTO"
            IMP-00003: ORACLE error 1119 encountered
            ORA-01119: error in creating database file '/oradata/MED/datafiles/med.dbf'
            ORA-27038: created file already exists
            Additional information: 1
            IMP-00017: following statement failed with ORACLE error 1119:
            "CREATE TABLESPACE "MED_LOB" BLOCKSIZE 8192 DATAFILE '/oradata/MED/datafile"
            "s/med_lob.dbf' SIZE 167772160 AUTOEXTEND ON NEXT 33554432 MAXSIZE 40"
            "92M EXTENT MANAGEMENT LOCAL AUTOALLOCATE ONLINE PERMANENT SEGMENT SPACE "
            "MANAGEMENT AUTO"
            IMP-00003: ORACLE error 1119 encountered
            ORA-01119: error in creating database file '/oradata/MED/datafiles/med_lob.dbf'
            ORA-27038: created file already exists
            Additional information: 1
            • 3. Re: Importing enitre database as new SID and in different location
              User286067
              What is your ORACLE_SID set to? Perhaps impdp doesn't like your syntax (that is acceptable for sqlplus i think).

              try

              impdp med/password@med_test ....

              Raj
              • 4. Re: Importing enitre database as new SID and in different location
                Dean Gagne-Oracle
                Hi,

                Some of the errors you posted are showing that the tablespaces already exist. Those make sense. What looks strange to me is that some of the failures are due to datafiles already existing. Do you think that your customer has the same layout of a directory structure as you? This looks strange to me.

                Dean