Improve Import and Export of database.

Hi ,

  I am want to improve the Import and Export of a database schema from PROD to a QA database.    I am using the standard imp/exp through  Cloud Control.   I guess it uses impdp/expdp.

  The current version is 10.2.0.4  , below the SQL from exp from the Cloud

   I need any tips or recommendations to improve performance of import and export and syntax required.

declare
  h1   NUMBER;
begin
     h1 := dbms_datapump.open (operation => 'EXPORT', job_mode => 'SCHEMA', job_name => 'TEST', version => 'COMPATIBLE');
    dbms_datapump.set_parallel(handle => h1, degree => 1);
    dbms_datapump.add_file(handle => h1, filename => 'test.LOG', directory => 'E_DRIVE_APPLMS', filetype => 3);
    dbms_datapump.set_parameter(handle => h1, name => 'KEEP_MASTER', value => 0);
    dbms_datapump.metadata_filter(handle => h1, name => 'SCHEMA_EXPR', value => 'IN(''PLATEAUDEV'')');
    dbms_datapump.add_file(handle => h1, filename => 'test%U.DMP', directory => 'E_DRIVE_APPLMS', filetype => 1);
    dbms_datapump.set_parameter(handle => h1, name => 'INCLUDE_METADATA', value => 1);
    dbms_datapump.set_parameter(handle => h1, name => 'DATA_ACCESS_METHOD', value => 'AUTOMATIC');
    dbms_datapump.set_parameter(handle => h1, name => 'ESTIMATE', value => 'BLOCKS');
    dbms_datapump.start_job(handle => h1, skip_current => 0, abort_step => 0);
    dbms_datapump.detach(handle => h1);
end;
/
LVL 1
joe_echavarriaDatabase AdministratorAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Geert GOracle dbaCommented:
if it's enterprise ... then try higher degree for parallel
    dbms_datapump.set_parallel(handle => h1, degree => 4);

there is 3 items to look at
export time
copy time to dest
import time

exporting in parallel can help
building the indexes in parallel can also help after import

check the docs for more clues
http://docs.oracle.com/cd/E11882_01/appdev.112/e40758/d_datpmp.htm#ARPLS356

what times have you got now, for what size
it might help to analyze the log to see where time is spent

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
joe_echavarriaDatabase AdministratorAuthor Commented:
Thanks Geert Gruwez for your reply.

When you say "building the indexes in parallel can also help after import ",      should the indexes be all disable before importing ? , if so , how to build the indexes in parallel after the import ?

I do not know how exactly the time is taking now, because it is a new assignment for me to improve it.

How do we analyze the log to see where time is spent ?

Thanks.
Mark GeerlingsDatabase AdministratorCommented:
You will have to give us some more information including:
1. How long does this process take now?  Is that: 5 minutes; 5 hours; 5 days, etc.?
2. How big is/are the dump file(s)?
3. What kind of storage system(s) do your source and target databases use?  Is that: local disk, NAS, SAN, flash, etc.?
4. Do you use ASM in one or both of these databases?
5. How much time is spent now for the export and how much for the import us index rebuild?  Also, do you need to copy the export from one system to another in between?
slightwv (䄆 Netminder) Commented:
>>You will have to give us some more information including:

6: Is there network connectivity between the systems?  If so, look at NETWORK_LINK and you don;t need to actually create the DMP file.
joe_echavarriaDatabase AdministratorAuthor Commented:
Exporting in parallel and building the indexes in parallel helped to improve the process , and it now takes 2 hours left.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.