Hi Gurus/Experts:
I am involve in creating test databases. For this export files (Dump Files and Logs) are created and I am trying to import and am getting tons of error messages. Here is what the scenerio is:
******************
EXPORT:
******************
1. Export was performed on a UNIX Box.
2. Export was successful and created Export Dump and Export Log Files.
3. Oracle Database for export used was Oracle 9i Version 9.2.0.6.0.
4. SQL*PLUS used was 9.2.0.1.0.
******************
IMPORT:
******************
1. Import is performed on a brand new database which is created by utilizing
Oracle Database Configuration Assistant.
Following Configuration Assistant option were unchecked so that extra files
should not be created:
Oracle Spatial, Oracle Ultra Search, Oracle Data Mining, Oracle OLAP, Example
Schemas, Oracle Intermedia, Oracle Text, Oracle XMLDP.
Only option I checked is Oracle JVM.
1. Import is performed on Window XP.
2. Import is successfully completed with WARNINGS.
3. Oracle 9i Version 9.2.0.1.0 is used.
4. SQL*PLUS version 9.2.0.1.0 is used.
*****************
RESULT:
*****************
1. Import is Only Partially Successful.
2. A number of objects were created but I received a lot of errors I am pasting at the end
of this message.
The parfile I used is as follows. (Please read between the doted lines)
......................................................................
USERID = System/<password>@atlsmo
FILE = E:\regis\import\atlsmo.dmp
LOG = E:\regis\import\atlsmo_022706_x.log
BUFFER = 4096000
FULL = Y
IGNORE = Y
......................................................................
The errors I received are as follows. (Since there are hundreds of the same kind
of errors, I only pasted a part of them here for EXPERTS to visualize a sample
of those errors. Please see these errors between the doted lines and I separated eack kind of them by ***********)
IMP-00061: Warning: Object type "SYSTEM"."REPCAT$_OBJECT_NULL_VECTOR" already exists with a different identifier
"CREATE TYPE "REPCAT$_OBJECT_NULL_VECTOR" TIMESTAMP '2004-01-21:18:31:02' OI"
"D 'D1766315C6B208E1E0340003BA4D64BE' AS OBJECT"
"("
" -- type owner, name, hashcode for the type represented by null_vector"
" type_owner VARCHAR2(30),"
" type_name VARCHAR2(30),"
" type_hashcode RAW(17),"
" -- null_vector for a particular object instance"
" -- ROBJ REVISIT: should only contain the null image, and not version#"
" null_vector RAW(2000)"
")"
***************************************************************************
IMP-00017: following statement failed with ORACLE error 23327:
"BEGIN SYS.DBMS_DEFER_IMPORT_INTERNAL.QUEUE_IMPORT_CHECK('ATLSMO.REGIS.ASO"
"','SVR4-be-64bit-8.1.0'); END;"
IMP-00003: ORACLE error 23327 encountered
ORA-23327: imported deferred rpc data does not match GLOBAL NAME and platform of importing db
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 86
ORA-06512: at "SYS.DBMS_DEFER_IMPORT_INTERNAL", line 30
ORA-06512: at line 1
. . importing table "DEF$_AQCALL" 0 rows imported
IMP-00017: following statement failed with ORACLE error 23327:
"BEGIN SYS.DBMS_DEFER_IMPORT_INTERNAL.QUEUE_IMPORT_CHECK('ATLSMO.REGIS.ASO"
"','SVR4-be-64bit-8.1.0'); END;"
IMP-00003: ORACLE error 23327 encountered
ORA-23327: imported deferred rpc data does not match GLOBAL NAME and platform of importing db
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 86
ORA-06512: at "SYS.DBMS_DEFER_IMPORT_INTERNAL", line 30
ORA-06512: at line 1
. . importing table "DEF$_AQERROR" 0 rows imported
. . importing table "DEF$_CALL" 0 rows imported
IMP-00017: following statement failed with ORACLE error 23327:
"BEGIN SYS.DBMS_DEFER_IMPORT_INTERNAL.QUEUE_IMPORT_CHECK('ATLSMO.REGIS.ASO"
"','SVR4-be-64bit-8.1.0'); END;"
IMP-00003: ORACLE error 23327 encountered
ORA-23327: imported deferred rpc data does not match GLOBAL NAME and platform of importing db
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 86
ORA-06512: at "SYS.DBMS_DEFER_IMPORT_INTERNAL", line 30
ORA-06512: at line 1
. . importing table "DEF$_CALLDEST" 0 rows imported
**********************************************************************************
IMP-00019: row rejected due to ORACLE error 1
IMP-00003: ORACLE error 1 encountered
ORA-00001: unique constraint (SYSTEM.HELP_TOPIC_SEQ) violated
Column 1 @
Column 2 1
Column 3
IMP-00019: row rejected due to ORACLE error 1
IMP-00003: ORACLE error 1 encountered
ORA-00001: unique constraint (SYSTEM.HELP_TOPIC_SEQ) violated
Column 1 @
Column 2 2
Column 3 @ ("at" sign)
IMP-00019: row rejected due to ORACLE error 1
IMP-00003: ORACLE error 1 encountered
ORA-00001: unique constraint (SYSTEM.HELP_TOPIC_SEQ) violated
Column 1 @
Column 2 3
Column 3 -------------
IMP-00019: row rejected due to ORACLE error 1
IMP-00003: ORACLE error 1 encountered
ORA-00001: unique constraint (SYSTEM.HELP_TOPIC_SEQ) violated
Column 1 @
Column 2 4
Column 3 Runs the SQL*Plus statements in the specified scr...
IMP-00019: row rejected due to ORACLE error 1
IMP-00003: ORACLE error 1 encountered
ORA-00001: unique constraint (SYSTEM.HELP_TOPIC_SEQ) violated
Column 1 @
Column 2 5
Column 3 called from the local file system or a web server...
IMP-00019: row rejected due to ORACLE error 1
IMP-00003: ORACLE error 1 encountered
ORA-00001: unique constraint (SYSTEM.HELP_TOPIC_SEQ) violated
Column 1 @
Column 2 6
Column 3 In iSQL*Plus the script can only be called from a...
********************************************************************************
IMP-00017: following statement failed with ORACLE error 2264:
"ALTER TABLE "REPCAT$_AUDIT_ATTRIBUTE" ADD CONSTRAINT "REPCAT$_AUDIT_ATTRIBU"
"TE_C1" CHECK ((data_type_id in (2, 4, 5, 6, 7) and"
" data_length is not null)"
" or (data_type_id not in (2, 4, 5, 6, 7) and"
" data_length is null)"
" ) ENABLE NOVALIDATE"
IMP-00003: ORACLE error 2264 encountered
ORA-02264: name already used by an existing constraint
. . importing table "REPCAT$_AUDIT_COLUMN" 0 rows imported
. . importing table "REPCAT$_COLUMN_GROUP" 0 rows imported
. . importing table "REPCAT$_CONFLICT" 0 rows imported
IMP-00017: following statement failed with ORACLE error 2264:
"ALTER TABLE "REPCAT$_CONFLICT" ADD CONSTRAINT "REPCAT$_CONFLICT_C1" CHECK ("
"conflict_type_id in (1, 2, 3)) ENABLE NOVALIDATE"
IMP-00003: ORACLE error 2264 encountered
ORA-02264: name already used by an existing constraint
. . importing table "REPCAT$_DDL" 0 rows imported
. . importing table "REPCAT$_EXCEPTIONS" 0 rows imported
. . importing table "REPCAT$_EXTENSION" 0 rows imported
**********************************************************************************
IMP-00019: row rejected due to ORACLE error 1
IMP-00003: ORACLE error 1 encountered
ORA-00001: unique constraint (SYSTEM.REPCAT$_OBJECT_TYPE_PK) violated
Column 1 -1
Column 2 MATERIALIZED VIEW
Column 3 01
Column 4
IMP-00019: row rejected due to ORACLE error 1
IMP-00003: ORACLE error 1 encountered
ORA-00001: unique constraint (SYSTEM.REPCAT$_OBJECT_TYPE_PK) violated
Column 1 1
Column 2 INDEX
Column 3 01
Column 4
IMP-00019: row rejected due to ORACLE error 1
IMP-00003: ORACLE error 1 encountered
ORA-00001: unique constraint (SYSTEM.REPCAT$_OBJECT_TYPE_PK) violated
Column 1 2
Column 2 TABLE
Column 3 01
Column 4
***************************************************************************************
IMP-00019: row rejected due to ORACLE error 1
IMP-00003: ORACLE error 1 encountered
ORA-00001: unique constraint (WMSYS.WM$ENV_VARS_PK) violated
***************************************************************************************
IMP-00060: Warning: Skipping table "ODM"."DMS_QUEUE_TABLE" because object type "SYS"."AQ$_JMS_USERPROPARRAY" does not exist
***************************************************************************************
IMP-00003: ORACLE error 1658 encountered
ORA-01658: unable to create INITIAL extent for segment in tablespace REGIS_DATA_MEDIUM
IMP-00017: following statement failed with ORACLE error 1658:
"CREATE TABLE "DELPHI_MATCH_ARCHIVE" ("FK_TRANS_ID_DELPHI_MATCH" NUMBER(38, "
"0) NOT NULL ENABLE, "FK_SITE_ID_DELPHI_MATCH" VARCHAR2(3) NOT NULL ENABLE, "
""FK_DELPHI_FIS_YEAR" NUMBER(4, 0) NOT NULL ENABLE, "FK_DTF_BATCH_ID" NUMBER"
"(38, 0) NOT NULL ENABLE, "FK_SEQKEY" NUMBER(38, 0) NOT NULL ENABLE, "NOT_OR"
"IGINAL" VARCHAR2(1) NOT NULL ENABLE, "MATCH_TYPE" VARCHAR2(1) NOT NULL ENAB"
"LE, "GROUP_ID" NUMBER(38, 0)) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 25"
"5 STORAGE(INITIAL 5242880 FREELISTS 1 FREELIST GROUPS 1) TABLESPACE "REGIS_"
"DATA_MEDIUM" LOGGING NOCOMPRESS"
***************************************************************************************
IMP-00003: ORACLE error 1741 encountered
ORA-01741: illegal zero-length identifier
. importing REPSYS's objects into REPSYS
. importing REPADMIN's objects into REPADMIN
. importing DBWARE's objects into DBWARE
. importing ODM's objects into ODM
IMP-00041: Warning: object created with compilation warnings
****************************************************************************************
IMP-00003: ORACLE error 942 encountered
ORA-00942: table or view does not exist
IMP-00017: following statement failed with ORACLE error 942:
"CREATE SNAPSHOT "R_TRANS" USING ("SNAP$_R_TRANS", (5, 'ATLSMO.REGIS.ASO', 1"
", 0, "REGIS", "R_TRANS", '2003-10-08:10:17:40', 0, 0, '1950-01-01:12:00:00'"
", '', 0, -0.E+00, 0, NULL), 2162779, 1, UPDATE "USLOG$_R_TRANS", ('2003-10-"
"08:10:17:40', 0, 0, 0, 0, 0, 268435456, 0, NULL, NULL), '@ASORO.REGIS.ASO')"
" REFRESH FORCE WITH ROWID FOR UPDATE AS"
"select * from R_TRANS@ASORO.REGIS.ASO"
""
****************************************************************************************
IMP-00003: ORACLE error 1403 encountered
ORA-01403: no data found
ORA-06512: at "SYS.DBMS_AQ_SYS_IMP_INTERNAL", line 197
ORA-06512: at "SYS.DBMS_AQ_IMP_INTERNAL", line 31
ORA-06512: at line 2
IMP-00017: following statement failed with ORACLE error 24002:
"BEGIN "
"SYS.DBMS_AQ_IMP_INTERNAL.IMPORT_QUEUE(HEXTORAW('D177510623B20999E0340003BA4"
"D64BE'),'DMS_QUEUE_TABLE','AQ$_DMS_QUEUE_TABLE_E',1,0,0,0,0,'exception queu"
"e');"
"COMMIT; END;"
****************************************************************************************
IMP-00017: following statement failed with ORACLE error 1403:
"BEGIN "
"SYS.DBMS_AQ_IMP_INTERNAL.IMPORT_QUEUE_TABLE('DMS_QUEUE_TABLE',1,8,2,0,0,'OD"
"M Task Queues maintained in this table');"
"COMMIT; END;"
IMP-00003: ORACLE error 1403 encountered
ORA-01403: no data found
ORA-06512: at "SYS.DBMS_AQ_SYS_IMP_INTERNAL", line 197
ORA-06512: at "SYS.DBMS_AQ_IMP_INTERNAL", line 31
ORA-06512: at line 2
IMP-00017: following statement failed with ORACLE error 24002:
"BEGIN "
"SYS.DBMS_AQ_IMP_INTERNAL.IMPORT_QUEUE(HEXTORAW('D177510623B20999E0340003BA4"
"D64BE'),'DMS_QUEUE_TABLE','AQ$_DMS_QUEUE_TABLE_E',1,0,0,0,0,'exception queu"
"e');"
"COMMIT; END;"
IMP-00003: ORACLE error 24002 encountered
ORA-24002: QUEUE_TABLE ODM.DMS_QUEUE_TABLE does not exist
ORA-06512: at "SYS.DBMS_AQ_SYS_IMP_INTERNAL", line 261
ORA-06512: at "SYS.DBMS_AQ_IMP_INTERNAL", line 56
ORA-06512: at line 2
IMP-00091: Above error occurred on the following function and object: CREATE AQ$_DMS_QUEUE_TABLE_E. Remaining PL/SQL blocks for this object will be skipped.
*****************************************************************************************
IMP-00017: following statement failed with ORACLE error 2298:
"ALTER TABLE "DELPHI_MATCH" ENABLE CONSTRAINT "FK_DELPHI_MATCH_DELPHI_ENTRY""
IMP-00003: ORACLE error 2298 encountered
ORA-02298: cannot validate (REGIS.FK_DELPHI_MATCH_DELPHI_ENTRY) - parent keys not found
IMP-00017: following statement failed with ORACLE error 23308:
"BEGIN SYS.DBMS_REPCAT_MIG.CLEANUP_IMPORT('EXPORT:V09.02.00'); END;"
IMP-00003: ORACLE error 23308 encountered
ORA-23308: object REGIS.R_DAFIS_MATCH does not exist or is invalid
ORA-06512: at "SYS.DBMS_REPCAT_MIG_INTERNAL", line 883
ORA-06512: at "SYS.DBMS_REPCAT_MIG", line 108
ORA-06512: at line 1
****************************************************************************************
Import terminated successfully with warnings.
****************************************************************************************
Please provide me the elaboration of the following:
1. What to do next to import the exported dump file without errors.
2. Is it possible to unload the imported data from the database without
dropping and recreating the oracle database? If so how?
3. I do not want to recreate the database, just want to import those objects
(tables, views, procedures, packages, triggers, etc) which are not imported
in the database. What should I do?
4. I was reading in EXPERT_EXCHANGE that with option ROWS = Y and
INDEXES =N, CONSTRAINTS = N, the database can be imported and loaded quickly, but then we have to build indexes, etc? There may be hundreds of indexes then in such a situation is there an easy way to build indexes, constraints.
5. It took me a lot of time to find out by comparing the EXPORT LOG with the result of the queries against the database whether the objects are there in the database or not. Also I performed lot of queries in order to find out that the number of rows inserted in the imported tables match with the entries in the Exported Log. Is there some script to find an easy way?
After initial failure I changed the parfile and this time I used import twice. Here what I used the parameter in parfile when I imported first time.
FULL = Y
ROWS = Y
CONSTRAINTS = N
INDEXES = N
(Did not use IGNORE Parameter at all for the 1st import)
2nsd time I used the following parameters:
FULL = Y
ROWS = N
CONSTRAINTS = Y
INDEXES = Y
IGNORE = Y
Still I got tons of errors and the newly created Database (Using Database Configuration Assistant) lacks more than 4000 objects to be loaded in the new database.
What should I do?
Shouls I recreate the database (I can do so) but what tool (Database Configuration Assistant or what ) should I use so that the import should go smoothly. Some suggest that I should use reverse engineering (which I am not skilled) to create exactly same databse as it is where export is created and the newly created database should be EMPTY. I think when I created the new database it was empty as except JVM, I did not click on any other options to create it. Shouls I use some script to create the database? What script? I am really confused as nothing works. PLEASE, PLEASE HELP ME.
I also do not know how to import without FULL = Y. Does it mean I have to import all the objects (more than 34000 of them !) one by one if I DONOT USE FULL = Y.
Please reply.
Thanks.
MR