Datapump Export/Import errors when Migrating Database

[gssr141]

Hi,

Can you plz answer following quetions ?

1). I am migrating database from 10g to 11g from one host to another host

what parameters we should use in EXPDP, for better export?
why Iam asking is after migration ( import done ) I am missing some of the objects, I dont know why I am missing ?
2). what Parameter should use in IMPDP, for better import ?

why I am asking is I am gettimg below errors when I am importing,

ORA-39082: Object type PACKAGE_BODY:"LILLY_OWNER"."LILLY_SEQMOL2DKEYS$RP" created with compilation warnings
ORA-39082: Object type PACKAGE_BODY:"LILLY_OWNER"."LILLY_STEREO$RP" created with compilation warnings
ORA-39082: Object type PACKAGE_BODY:"LILLY_OWNER"."LILLY_SALTS$RP" created with compilation warnings
ORA-39082: Object type PACKAGE_BODY:"LILLY_OWNER"."LILLY_FSIDX$RL" created with compilation warnings
ORA-39082: Object type PACKAGE_BODY:"LILLY_OWNER"."LILLY_SAMID_READ_PKG" created with compilation warnings
ORA-39082: Object type PACKAGE_BODY:"LILLY_OWNER"."LILLY_ENKFIL$RP" created with compilation warnings
ORA-39082: Object type PACKAGE_BODY:"LILLY_OWNER"."LILLY_FSIDX$RP" created with compilation warnings

Please post your answers, and send me any links/materials for db migration .

Here I am pasting what parameters Iam using generally;

For EXPORT :
$ expdp parfile=export.par

export.par file Contains :

full=y
directory=dumpfiles1
dumpfile=CR06172102_%Uexport.dmp
logfile=CR06172102_export.log
job_name=CR06172102_export1
parallel=4
flashback_time = "to_timestamp(to_char(sysdate,'YYYY-MM-DD HH24:MI:SS'),'YYYY-MM-DD HH24:MI:SS')"

For IMPORT:
$impdp parfile=import.par

import.par file contains :

full=y
directory=dump_files1
dumpfile=CR06172102_%Uexport.dmp
logfile=CR06172102_import.log
job_name=CR06172102_import
parallel=8
EXCLUDE=TABLESPACE
EXCLUDE=DIRECTORY
EXCLUDE=SCHEMA:"='TSMSYS'"
EXCLUDE=SCHEMA:"='SYS'"
EXCLUDE=SCHEMA:"='SYSTEM'"
EXCLUDE=SCHEMA:"='WMSYS'"
EXCLUDE=SCHEMA:"='DBSNMP'"
EXCLUDE=SCHEMA:"='OUTLN'"
EXCLUDE=SCHEMA:"='SYSMAN'"
EXCLUDE=SCHEMA:"='ANONYMOUS'"
EXCLUDE=SCHEMA:"='CTXSYS'"
EXCLUDE=SCHEMA:"='DIP'"
EXCLUDE=SCHEMA:"='ORAMAN'"
EXCLUDE=SCHEMA:"='XDB'"

[vnktummala]

1. export parameters what you are using are fine. Depends on number of CPUs available in the server you can increase parallel option to make it more faster.

2. a) what is the status of these package bodies in source database?
b) have you tried to recompile them in target after import?
c) you don't need to exclude system schemas. If full=y then oracle will not export or import any system schemas.

Thanks,

[gssr141]

Hi Vijay,

Thanks for your Quick response, But One thing After Import I missed some of the objects , like Triggers and etc, What could be the reason ? these trigeers are available in source database but not available in target database (New DB ).

Thanks.

[vnktummala]

Here is the excerpt from oracle documentation.

A full export does not export triggers owned by schema SYS. You must manually re-create SYS triggers either before or after the full import. Oracle recommends that you re-create them after the import in case they define actions that would impede progress of the import.

Please read this for more information.

Thanks,