?
Solved

impdp hanging on schema_export

Posted on 2016-11-07
4
Medium Priority
?
120 Views
Last Modified: 2016-11-19
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
Comment
Question by:Elliott Clark
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
4 Comments
 
LVL 16

Expert Comment

by:Richard Olutola
ID: 41878049
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
 

Author Comment

by:Elliott Clark
ID: 41878274
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
 

Accepted Solution

by:
Elliott Clark earned 0 total points
ID: 41886129
Turns out there was a corrupt file from one of the exports. Therefore after deleting, re exporting the import worked!
0
 

Author Closing Comment

by:Elliott Clark
ID: 41894070
Solution resolved issue
0

Featured Post

On Demand Webinar: Networking for the Cloud Era

Ready to improve network connectivity? Watch this webinar to learn how SD-WANs and a one-click instant connect tool can boost provisions, deployment, and management of your cloud connection.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This post contains step-by-step instructions for setting up alerting in Percona Monitoring and Management (PMM) using Grafana.
In this blog post, we’ll look at how ClickHouse performs in a general analytical workload using the star schema benchmark test.
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…
Suggested Courses

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question