6 Replies Latest reply on Oct 3, 2014 3:54 AM by Srini Chavali-Oracle

    Cannot import dump due to these errors.

    user12240205

      Our client sent us a export dump file.  We want to import it into our own 11g R2 DB.

       

      We import using SYSTEM account. But get these errors:

       

      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.01.00 via conventional path

       

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

       

      import done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set

      export client uses AL32UTF8 character set (possible charset conversion)

      . importing SYSTEM's objects into SYSTEM

       

      IMP-00017: following statement failed with ORACLE error 1119:

      "CREATE TABLESPACE "ARGUS_AEXP_DATA_01" BLOCKSIZE 8192 DATAFILE  'M:\ORACLE\"

      "ORADATA\PRD1680\ARGUS_AEXP_DATA_01.DBF' SIZE 1213M       AUTOEXTEND ON NEXT"

      " 52428800  MAXSIZE 32767M EXTENT MANAGEMENT LOCAL  AUTOALLOCATE  ONLINE PER"

      "MANENT  SEGMENT SPACE MANAGEMENT AUTO"

      IMP-00003: ORACLE error 1119 encountered

      ORA-01119: error in creating database file 'M:\ORACLE\ORADATA\PRD1680\ARGUS_AEXP_DATA_01.DBF'

      ORA-27040: file create error, unable to create file

      OSD-04002: unable to open file

      O/S-Error: (OS 3) The system cannot find the path specified.

       

       

      IMP-00003: ORACLE error 1119 encountered

      ORA-01119: error in creating database file 'M:\ORACLE\ORADATA\PRD1680\MEDDRA_DATA_101.ORA'

      ORA-27040: file create error, unable to create file

      OSD-04002: unable to open file

      O/S-Error: (OS 3) The system cannot find the path specified.

       

       

      IMP-00017: following statement failed with ORACLE error 959:

      "ALTER USER "SYS" IDENTIFIED BY VALUES 'A6DECCC9E6C9B145' TEMPORARY TABLESPA"

      "CE "TEMP02""

      IMP-00003: ORACLE error 959 encountered

      ORA-00959: tablespace 'TEMP02' does not exist

       

       

      IMP-00017: following statement failed with ORACLE error 959:

      "CREATE USER "ARGUS_APP" IDENTIFIED BY VALUES 'D39BD42FDBAF7AF3' DEFAULT TAB"

      "LESPACE "USERS" TEMPORARY TABLESPACE "TEMP02""

      IMP-00003: ORACLE error 959 encountered

      ORA-00959: tablespace 'TEMP02' does not exist

       

       

      IMP-00017: following statement failed with ORACLE error 959:

      "CREATE USER "WHO_DATA_B0314" IDENTIFIED BY VALUES 'BB5E4C2F919F1DD0' DEFAUL"

      "T TABLESPACE "WHO_DATA_B0314" TEMPORARY TABLESPACE "TEMP02""

      IMP-00003: ORACLE error 959 encountered

      ORA-00959: tablespace 'WHO_DATA_B0314' does not exist

       

       

      IMP-00017: following statement failed with ORACLE error 1917:

      "GRANT UNLIMITED TABLESPACE TO "TSMSYS""

      IMP-00003: ORACLE error 1917 encountered

      ORA-01917: user or role 'TSMSYS' does not exist

       

       

      IMP-00017: following statement failed with ORACLE error 1917:

      "GRANT MERGE ANY VIEW TO "ARGUS_LOGIN""

      IMP-00003: ORACLE error 1917 encountered

      ORA-01917: user or role 'ARGUS_LOGIN' does not exist

       

       

      IMP-00017: following statement failed with ORACLE error 1917:

      "GRANT "MEDDRA_ACCESS_12" TO "ARGUS_APP""

      IMP-00003: ORACLE error 1917 encountered

      ORA-01917: user or role 'ARGUS_APP' does not exist

       

      This is the export log's first few lines:

      Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production

      With the OLAP option

      Export done in AL32UTF8 character set and AL16UTF16 NCHAR character set

       

       

      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                    DEF$_AQCALL          0 rows exported

       

      I have identified this possible solution:

      (1.) Users cannot be created because temp tablespace TEMP02 does not exist. So Create a temporary tablespace called TEMP02 and make it the temporary tablespace.

      (2.) How to get over the ORA-01119. Our DBs data-files are in different path for the tablespaces?

      (3.) If the above 2 succeed then the users will be created successfully, since the tablespaces are present.

      (4.) The system privilege grants will be successful since the users exist.

      (5.) Granting roles to users will be successful also.

       

      Any help would be greatly appreciated?

        • 1. Re: Cannot import dump due to these errors.
          Dan Jankowski

          Have a look at the metadata filter parameters - you can potentially pre-create your data files, users, roles etc, and then use the EXCLUDE parameter to exclude these from the import (or if you just want tables and nothing else, use INCLUDE). Also look at REMAP_TABLESPACE if you want to use different tablespaces to the source.

           

          Data Pump Import

          • 2. Re: Cannot import dump due to these errors.
            Cobert

            Hi,

            Dan has already provided the link, problem is you're pulling data into a database without any of the users created, your import wants to import into the same DB structure. It looks like somebody gave you a full datapump dumpfile, with sys, system etc. all included, and you already have those.

             

            You'll have to at a minimal do the below (if you need all of those users)

             

            create 3 x users, say my_new_schema_1, my_new_schema_2, my_new_schema_3 and make sure you've space in USERS, then in conjunction with the doc above you'll need to add something akin to the below to your datapump import (suggest you create a parfile for it)

             

            remap_schema=BATCH_OPER:my_new_schema_2

            remap_schema=ARGUS_APP:my_new_schema_1

            remap_schema=ARGUS_LOGIN:my_new_schema_3

            remap_tablespace=ARGUS_AEXP_DATA_01:users

             

            Cheers,

            • 3. Re: Cannot import dump due to these errors.
              user12240205

              THANKS for the quick reply. The export was not done using  DATA PUMP. It was done using normal export. We are trying to import it like this:

               

              IMP system/**** file=kdkd.dmp full=y ignore=y

              • 4. Re: Cannot import dump due to these errors.
                Cobert

                Ok well with old imp it'll be a case of fromuser= touser= so you'll need to switch the user that's importing, do one schema at a time is probably cleanest. Do you just need the data to look at or are you setting something up? Best to get the core schema that you need, or find out from the suppliers of the dumpfile what you require.

                 

                fromuser=

                touser=

                ignore=y

                 

                Syntax for the old import is available using imp -help

                 

                Again though - find out what schemas you need to import instead of importing the entire dumpfile.

                1 person found this helpful
                • 5. Re: Cannot import dump due to these errors.
                  bhagatsingh

                  Hi,

                   

                  Seems below path doesn't exists , where it is trying to create the tablespace.

                   

                  M:\ORACLE\ORADATA\PRD1680

                   

                  Can check the existance of this path. and the write permission on the directory.

                   

                   

                  Rajkishore

                  • 6. Re: Cannot import dump due to these errors.
                    Srini Chavali-Oracle

                    Moved to the Export/Import forum - pl post future questions in appropriate forum