Solved

impdp hanging on schema_export

Posted on 2016-11-07
4
72 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

Has Powershell sent you back into the Stone Age?

If managing Active Directory using Windows Powershell® is making you feel like you stepped back in time, you are not alone.  For nearly 20 years, AD admins around the world have used one tool for day-to-day AD management: Hyena. Discover why.

Question has a verified solution.

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

Background Information Recently I have fixed file server permission issues for one of my client. The client has 1800 users and one Windows Server 2008 R2 domain joined file server with 12 TB of data, 250+ shared folders and the folder structure i…
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
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…
The viewer will learn how to count occurrences of each item in an array.

730 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