• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 167
  • Last Modified:

impdp hanging on schema_export

I am currently trying to run a script that I have written however it constantly hangs halfway through, this has worked on the odd occasion but more often than not it gets nowhere. Here is the script:

:: load metadata only

impdp  *username*/******@Server1 exclude=user REMAP_SCHEMA=SC_MUTBLDN:SC_MUTBLDN schemas=SC_MUTBLDN CONTENT=METADATA_ONLY dumpfile=ALL_METADATA_DAILY.dmp logfile=ALL_METADATA_DAILY.log



:: load data only

impdp  *username*/******@Server1  Full=Y EXCLUDE=TABLE:"LIKE'CRAC%'"  dumpfile=ALL_DATA_DAILY.DMP table_exists_action=replace  logfile=ALL_DATA_DAILY.log

impdp  *username*/******@Server1  EXCLUDE=TABLE:"LIKE'CRAC%'"  REMAP_SCHEMA=SC_MUTBLDN:SC_MUTBLDN   dumpfile=ALL_DATA_DAILY.DMP table_exists_action=replace  logfile=ALL_DATA_DAILY.log


:: dump data minus crac tables

impdp *username*/******@Server1 tables=CRAC_TYPE dumpfile=CRAC_TYPE.dmp table_exists_action=replace logfile=CRAC_TYPE.log
impdp *username*/******@Server1 tables=CRAC_KEYWORD dumpfile=CRAC_KEYWORD.dmp table_exists_action=replace logfile=CRAC_KEYWORD.log
impdp *username*/******@Server1 tables=CRAC_DEALTYPE dumpfile=CRAC_DEALTYPE.dmp table_exists_action=replace logfile=CRAC_DEALTYPE.log
impdp *username*/******@Server1 tables=CRAC_TABLENAME dumpfile=CRAC_TABLENAME.dmp table_exists_action=replace logfile=CRAC_TABLENAME.log
impdp *username*/******@Server1 tables=CRAC_DATABASENAME dumpfile=CRAC_DATABASENAME.dmp table_exists_action=replace logfile=CRAC_DATABASENAME.log
impdp *username*/******@Server1 tables=CRAC_DB_UPDATE dumpfile=CRAC_DB_UPDATE.dmp table_exists_action=replace logfile=CRAC_DB_UPDATE.log
impdp *username*/******@Server1 tables=CRAC_ACTION dumpfile=CRAC_ACTION.dmp table_exists_action=replace logfile=CRAC_ACTION.log
impdp *username*/******@Server1 tables=CRAC_DEPT dumpfile=CRAC_DEPT.dmp table_exists_action=replace logfile=CRAC_DEPT.log
impdp *username*/******@Server1 tables=CRAC_USER dumpfile=CRAC_USER.dmp table_exists_action=replace logfile=CRAC_USER.log
impdp *username*/******@Server1 tables=CRAC_ERRTYPE dumpfile=CRAC_ERRTYPE.dmp table_exists_action=replace logfile=CRAC_ERRTYPE.log
impdp *username*/******@Server1 tables=CRAC_ERRREASON dumpfile=CRAC_ERRREASON.dmp table_exists_action=replace logfile=CRAC_ERRREASON.log
impdp *username*/******@Server1 tables=CRAC_CALYPSO dumpfile=CRAC_CALYPSO.dmp table_exists_action=replace logfile=CRAC_CALYPSO.log
impdp *username*/******@Server1 tables=CRAC_PRODUCT dumpfile=CRAC_PRODUCT.dmp table_exists_action=replace logfile=CRAC_PRODUCT.log
impdp *username*/******@Server1 tables=CRAC_DB dumpfile=CRAC_DB.dmp table_exists_action=replace logfile=CRAC_DB.log
impdp *username*/******@Server1 tables=CRAC_DEALNUM dumpfile=CRAC_DEALNUM.dmp table_exists_action=replace logfile=CRAC_DEALNUM.log
impdp *username*/******@Server1 tables=CRAC_TABLE_AMENDED dumpfile=CRAC_TABLE_AMENDED.dmp table_exists_action=replace logfile=CRAC_TABLE_AMENDED.log



:: sql plus to check out put

sqlplus *username*/******@Server1 @C:\ORACLE\admin\MUTBLDN\run.sql

here is where it hangs



Ideally I'd like to eventually run this as a scheduled task from a remote server as well.
0
Elliott Clark
Asked:
Elliott Clark
  • 3
1 Solution
 
Richard OlutolaConsultantCommented:
Although you said you're trying a schema IMPORT with REMAP but I could also see FULL=Y in one of the commands.

Was your export a FULL export or schema export?
Please try a schema export followed by a schema import into the target schema.

R.
0
 
Elliott ClarkDBAAuthor Commented:
Hi Rolutola,

thanks for your response, I have tried taking out the "Full=Y" and run the script again, however it gets stuck at the same place! Here is my expdp script, hopefully this will help shed some more light :



:: clean folder
del /q  \\server2\C$\ORACLE\admin\MUTBLDN\dpdump\*


:: dump metadata only

expdp  *username*/******@Server2  schemas=SC_MUTBLDN CONTENT=METADATA_ONLY dumpfile=ALL_METADATA_DAILY.dmp logfile=ALL_METADATA_DAILY.log


:: dump data only

expdp  *username*/******@Server2 schemas=SC_MUTBLDN "exclude=procobj:\"in(SELECT NAME FROM sys.OBJ$ WHERE TYPE# IN (47,48,66,67,68,69,71,72,74))\"" EXCLUDE=TABLE:"LIKE'CRAC%'" dumpfile=ALL_DATA_DAILY.dmp logfile=ALL_DATA_DAILY.log




:: dump data minus crac tables

expdp *username*/******@Server2 tables=CRAC_TYPE dumpfile=CRAC_TYPE.dmp logfile=CRAC_TYPE.log
expdp *username*/******@Server2 tables=CRAC_KEYWORD dumpfile=CRAC_KEYWORD.dmp logfile=CRAC_KEYWORD.log
expdp *username*/******@Server2 tables=CRAC_DEALTYPE dumpfile=CRAC_DEALTYPE.dmp logfile=CRAC_DEALTYPE.log
expdp *username*/******@Server2 tables=CRAC_TABLENAME dumpfile=CRAC_TABLENAME.dmp logfile=CRAC_TABLENAME.log
expdp *username*/******@Server2 tables=CRAC_DATABASENAME dumpfile=CRAC_DATABASENAME.dmp logfile=CRAC_DATABASENAME.log
expdp *username*/******@Server2 tables=CRAC_DB_UPDATE dumpfile=CRAC_DB_UPDATE.dmp logfile=CRAC_DB_UPDATE.log
expdp *username*/******@Server2 tables=CRAC_ACTION dumpfile=CRAC_ACTION.dmp logfile=CRAC_ACTION.log
expdp *username*/******@Server2 tables=CRAC_DEPT dumpfile=CRAC_DEPT.dmp logfile=CRAC_DEPT.log
expdp *username*/******@Server2 tables=CRAC_USER dumpfile=CRAC_USER.dmp logfile=CRAC_USER.log
expdp *username*/******@Server2 tables=CRAC_ERRTYPE dumpfile=CRAC_ERRTYPE.dmp logfile=CRAC_ERRTYPE.log
expdp *username*/******@Server2 tables=CRAC_ERRREASON dumpfile=CRAC_ERRREASON.dmp logfile=CRAC_ERRREASON.log
expdp *username*/******@Server2 tables=CRAC_CALYPSO dumpfile=CRAC_CALYPSO.dmp logfile=CRAC_CALYPSO.log
expdp *username*/******@Server2 tables=CRAC_PRODUCT dumpfile=CRAC_PRODUCT.dmp logfile=CRAC_PRODUCT.log
expdp *username*/******@Server2 tables=CRAC_DB dumpfile=CRAC_DB.dmp logfile=CRAC_DB.log
expdp *username*/******@Server2 tables=CRAC_DEALNUM dumpfile=CRAC_DEALNUM.dmp logfile=CRAC_DEALNUM.log
expdp *username*/******@Server2 tables=CRAC_TABLE_AMENDED dumpfile=CRAC_TABLE_AMENDED.dmp logfile=CRAC_TABLE_AMENDED.log


:: clean remote folder prior to moving data dumps and logs to DR

del /q  \\Server1\C$\ORACLE\admin\MUTBLDN\dpdump\*

XCOPY \\server2\C$\ORACLE\admin\MUTBLDN\dpdump\*.dmp \\server1\C$\ORACLE\admin\MUTBLDN\dpdump\* /y



:: sql plus to check out put

sqlplus *username*/******@Server2 @\\server1\c$\ORACLE\admin\MUTBLDN\run.sql


this script runs fine and completes (it is run as a scheduled task from a 3rd server separate to both these servers, which is where I'd eventually also like the first script in this question to run from)
0
 
Elliott ClarkDBAAuthor Commented:
Turns out there was a corrupt file from one of the exports. Therefore after deleting, re exporting the import worked!
0
 
Elliott ClarkDBAAuthor Commented:
Solution resolved issue
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now