In-Oracle.com  -> Oracle DBA

-> Schema refresh using Data Pump in 10g

 Schema refresh using Data Pump in 10g

Here are the steps for implementing a schema refresh using Data Pump:

1. Create a new user on the target database with DBA role granted

2. Create a DBlink in the new schema to point to the source schema

3. Create the procedure which will refresh the data

4. Run the schema refresh 

5. Known error: ORA-31626: job does not exist, ORA-39001: invalid argument value

1. Create a new user on the target database with DBA role granted

connected as sys run the following commands:

CREATE USER DBA_TOOLS identified by <password>;

grant DBA to DBA_TOOLS; 

2. Create a DBlink in the new schema to point to the source schema

CREATE DATABASE LINK "db_LINK_NAME" 

connect to source_schema

identified by password

using 'DB_alias';

3. Create the procedure which will refresh the data

CREATE OR REPLACE PROCEDURE DBA_TOOLS.copy_schema

          ( source_schema in varchar2,

           destination_schema in varchar2,

           new_password in varchar2 default 'newuser',

           network_link in varchar2 default 'db_LINK_NAME')

as 

 

  JobHandle   number; 

  js  varchar2(9); -- COMPLETED or STOPPED 

  q   varchar2(1) := chr(39); 

 

 

BEGIN  /* open a new schema level import job using a default DB link */ 

   JobHandle := dbms_datapump.open ('IMPORT',

                                                                 'SCHEMA',

                                                                  network_link); 

                          

  /* restrict to the schema we want to copy */ 

  dbms_datapump.metadata_filter ( JobHandle,

                                                              'SCHEMA_LIST',

                                                               q||source_schema||q);

 

  /* remap the importing schema name to the schema we want to create */

   dbms_datapump.metadata_remap ( JobHandle,

                                                                 'REMAP_SCHEMA',

                                                                  source_schema,

                                                                  destination_schema);

  

   /* Set datapump parameters*/                            

   dbms_datapump.set_parameter ( JobHandle,

                                                             'TABLE_EXISTS_ACTION',

                                                              'REPLACE' );

                                

  /* start the job */ 

  dbms_datapump.start_job( JobHandle);   

 

  /* wait for the job to finish */ 

  dbms_datapump.wait_for_job( JobHandle, js);   

 

end;

/

4. Run the schema refresh 

                 begin        

                     DBA_TOOLS.copy_schema(‘SOURCE_SCHEMA’,'DESTINATION_SCHEMA');

                 end;

                  /

Note: On a production database, in 1h:08 min 24.14 Gb were transferred. 

NOTE: You may receive:
 

ORA-31626: job does not exist
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 79
ORA-06512: at "SYS.DBMS_DATAPUMP", line 938
ORA-06512: at "SYS.DBMS_DATAPUMP", line 4566

Check:

1)  the DBA_TOOLS (who run the code, the owner of the code) has the "create table" privilege

ORA-39001: invalid argument value
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 79
ORA-06512: at "SYS.DBMS_DATAPUMP", line 3444
ORA-06512: at "SYS.DBMS_DATAPUMP", line 5233

Check:

the DBA_TOOLS see the DB_LINK (You can create a public DB_LINK for testing this.).

In-Oracle.com  -> Oracle DBA

-> Schema refresh using Data Pump in 10g