Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

impdp hanging on schema_export

Posted on 2016-11-07
4
Medium Priority
?
143 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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
In today's business world, data is more important than ever for informing marketing campaigns. Accessing and using data, however, may not come naturally to some creative marketing professionals. Here are four tips for adapting to wield data for insi…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …

650 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