joe_echavarria
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(han dle => h1, filename => 'test.LOG', directory => 'E_DRIVE_APPLMS', filetype => 3);
dbms_datapump.set_paramete r(handle => h1, name => 'KEEP_MASTER', value => 0);
dbms_datapump.metadata_fil ter(handle => h1, name => 'SCHEMA_EXPR', value => 'IN(''PLATEAUDEV'')');
dbms_datapump.add_file(han dle => h1, filename => 'test%U.DMP', directory => 'E_DRIVE_APPLMS', filetype => 1);
dbms_datapump.set_paramete r(handle => h1, name => 'INCLUDE_METADATA', value => 1);
dbms_datapump.set_paramete r(handle => h1, name => 'DATA_ACCESS_METHOD', value => 'AUTOMATIC');
dbms_datapump.set_paramete r(handle => h1, name => 'ESTIMATE', value => 'BLOCKS');
dbms_datapump.start_job(ha ndle => h1, skip_current => 0, abort_step => 0);
dbms_datapump.detach(handl e => h1);
end;
/
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
dbms_datapump.add_file(han
dbms_datapump.set_paramete
dbms_datapump.metadata_fil
dbms_datapump.add_file(han
dbms_datapump.set_paramete
dbms_datapump.set_paramete
dbms_datapump.set_paramete
dbms_datapump.start_job(ha
dbms_datapump.detach(handl
end;
/
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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?
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?
>>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.
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.
ASKER
Exporting in parallel and building the indexes in parallel helped to improve the process , and it now takes 2 hours left.
ASKER
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.