Link to home
Start Free TrialLog in
Avatar of joe_echavarria
joe_echavarriaFlag for Dominican Republic

asked on

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;
/
ASKER CERTIFIED SOLUTION
Avatar of Geert G
Geert G
Flag of Belgium image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of joe_echavarria

ASKER

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.
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?
Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

>>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.
Exporting in parallel and building the indexes in parallel helped to improve the process , and it now takes 2 hours left.