Solved

impdp hanging on schema_export

Posted on 2016-11-07
  • Windows Server 2012
  • Databases
  • NoSQL Databases
  • Windows Batch
  • Oracle Database
  • +1
4
33 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
  • 3
4 Comments
 
LVL 16

Expert Comment

by:rolutola
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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Many companies are looking to get out of the datacenter business and to services like Microsoft Azure to provide Infrastructure as a Service (IaaS) solutions for legacy client server workloads, rather than continuing to make capital investments in h…
A procedure for exporting installed hotfix details of remote computers using powershell
This tutorial will walk an individual through the process of configuring basic necessities in order to use the 2010 version of Data Protection Manager. These include storage, agents, and protection jobs. Launch Data Protection Manager from the deskt…
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…

707 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now