Import Options

[Ragz]

HI,

I am trying to migrate data from 10.1.0.4 to 10.2.0.4 using export/import utility. I did export with full=y as system and the export was successful without any errors. During import I am facing lot of errors.
imp system/password parfile= import.par
Parameters used:
File=export.dmp
Log=import.log
Full=Y
Rows=Y
INDEXES=Y
GRANTS=Y
IGNORE=Y
COMMIT=Y
BUFFER=64000
Constraints=Y
feedback=10000
statistics=None

Following are the errors I am concerned about:


IMP-00017: following statement failed with ORACLE error 1031:
"CREATE TYPE "ODCIOBJECT" TIMESTAMP '2005-09-21:11:39:20' OID 'F8484817186D9"
"6D4E0330A01014C96D4' AS object"
"("
" ObjectSchema VARCHAR2(30),"
" ObjectName VARCHAR2(30)"
");"
IMP-00003: ORACLE error 1031 encountered
ORA-01031: insufficient privileges
IMP-00017: following statement failed with ORACLE error 1031:
"CREATE TYPE "ODCIOBJECTLIST" TIMESTAMP '2005-09-21:11:39:20' OID 'F84848171"
"87296D4E0330A01014C96D4' AS VARRAY(32) of ODCIObject;"
IMP-00003: ORACLE error 1031 encountered
ORA-01031: insufficient privileges
. importing EXFSYS's objects into EXFSYS
. importing OLAPSYS's objects into OLAPSYS
. importing SDS's objects into SDS
. importing SYSMAN's objects into SYSMAN
. importing SDS's objects into SDS
. importing TOAD's objects into TOAD
. importing SQLTXPLAIN's objects into SQLTXPLAIN
. importing SYSTEM's objects into SYSTEM
. importing SYS's objects into SYS
. importing SYSTEM's objects into SYSTEM
IMP-00017: following statement failed with ORACLE error 6550:
"BEGIN "
"declare"
" dummy integer;"
" begin"
" select 1 into dummy from dual "
" where exists (select 1 from all_synonyms"
" where owner = 'PUBLIC' and synonym_name = 'DB"
"MS_WM');"
" exception when no_data_found then"
" raise_application_error(-20000, 'OWM Not Installed');"
" end;"
""
" if (sys.ltadm.existnonliveworkspaces OR sys.ltadm.existversio"
"nedtabs) then"
" SYS.WM_ERROR.RAISEERROR(SYS.LT.WM_ERROR_189_NO);"
" else"
" begin"
" delete from wmsys.wm$workspaces_table;"
" exception when others then null;"
" end;"
""
" begin"
" delete from wmsys.wm$workspace_priv_table;"
" exception when others then null;"
" end;"
""
" begin"
" delete from wmsys.wm$version_hierarchy_table ;"
" exception when others then null;"
" end;"
""
" begin"
" execute immediate 'drop sequence wmsys.wm$up_del_trig_nam"
"e_sequence' ; "
" exception when others then null;"
" end;"
""
" begin"
" execute immediate 'drop sequence wmsys.wm$insteadof_trigs"
"_sequence' ; "
" exception when others then null;"
" end;"
""
" begin "
" execute immediate 'drop sequence wmsys.wm$lock_sequence' "
"; "
" exception when others then null;"
" end;"
""
" begin"
" execute immediate 'drop sequence wmsys.wm$vtid' ; "
" exception when others then null;"
" end;"
""
" begin"
" execute immediate 'drop sequence wmsys.wm$adt_sequence' ;"
" "
" exception when others then null;"
" end;"
""
" begin"
" execute immediate 'drop sequence wmsys.wm$version_sequenc"
"e' ; "
" exception when others then null;"
" end;"
""
" begin"
" execute immediate 'drop sequence wmsys.wm$udtrig_dispatch"
"er_sequence' ; "
" exception when others then null;"
" end;"
""
" execute immediate 'create sequence wmsys.wm$up_del_trig_nam"
"e_sequence start with 71 nocache';"
" execute immediate 'create sequence wmsys.wm$insteadof_trigs"
"_sequence start with 71 nocache';"
" execute immediate 'create sequence wmsys.wm$lock_sequence s"
"tart with 142 increment by 2 nocache';"
" execute immediate 'create sequence wmsys.wm$vtid start with"
" 71 nocache';"
" execute immediate 'create sequence wmsys.wm$adt_sequence st"
"art with 71 nocache';"
" execute immediate 'create sequence wmsys.wm$version_sequenc"
"e start with 71 nocache';"
" execute immediate 'create sequence wmsys.wm$udtrig_dispatch"
"er_sequence start with 71 nocache'; "
""
" begin"
" dbms_aqadm.stop_queue(queue_name => 'WMSYS.WM$EVENT_QUEUE"
"');"
" dbms_aqadm.drop_queue(queue_name => 'WMSYS.WM$EVENT_QUEUE"
"');"
" dbms_aqadm.drop_queue_table(queue_table => 'WMSYS.WM$EVEN"
"T_QUEUE_TABLE', force => true);"
" exception when others then null;"
" end;"
" end if ;"
"COMMIT; END;"
IMP-00003: ORACLE error 6550 encountered
ORA-06550: line 12, column 19:
PLS-00201: identifier 'SYS.LTADM' must be declared
ORA-06550: line 12, column 15:
PL/SQL: Statement ignored
. importing SYS's objects into SYS
. importing SYSTEM's objects into SYSTEM
IMP-00017: following statement failed with ORACLE error 6550:
"BEGIN "
"dbms_server_alert.set_threshold(2018,dbms_server_alert.operator_GE,'100',db"
"ms_server_alert.operator_NONE,'',1,2,'trprod',dbms_server_alert.object_type"
"_SYSTEM,'');"
"COMMIT; END;"
IMP-00003: ORACLE error 6550 encountered
ORA-06550: line 2, column 92:
PLS-00302: component 'OPERATOR_NONE' must be declared
ORA-06550: line 2, column 1:
PL/SQL: Statement ignored
IMP-00019: row rejected due to ORACLE error 1
IMP-00003: ORACLE error 1 encountered
ORA-00001: unique constraint (SYSTEM.REPCAT$_AUDIT_ATTRIBUTE_PK) violated
Column 1 TIMESTAMP
Column 2 3
Column 3
Column 4 SYSDATE


Thanks for your help


Ragz

[Brian Carr]

Hi,

>> ORA-01031: insufficient privileges
Are all the users that you are importing to created and have the exact permissions? Why are you not importing as SYS?

>> imp
Consider using IMPDP as its faster and gives you more control. See http://www.dba-oracle.com/t_import_impdp_i...mance_speed.htm

See here for some migration tips - http://www.dba-oracle.com/t_migration_error_message_tips.htm

[Ragz]

Hi Brian,

I tried to export and import as sys but in export i found a waning EXP-00105: parameter CONSISTENT is not supported for this user

During import apart from other errors there i found IMP-00017: following statement failed with ORACLE error 2298 and ORA-02298: cannot validate (THCPFN01.SYS_C0012699) - parent keys not found

Metalink Note 1061860.6 says Importing User Gives ORA-02298 Parent Keys Not Found Error. It says to import as system/password.

Thanks for your help.


Ragz

[HAL9000]

Ragz,

Please listenen to this and learn how to find-out error codes yourself:

http://www.dba-oracle.com/t_google_keyword_searching.htm

"I tried to export and import as sys but in export i found a waning EXP-00105: parameter CONSISTENT is not supported for this user"

EXP-00105: parameter string is not supported for this user

Cause: The user attempted to specify either CONSISTENT or OBJECT_CONSISTENT when connected as sysdba.

Action: If a consistent export is needed, then connect as another user.