In Oracle : Schema refresh using Data Pump in 10g

Oracle Applications EBS 11i, R12      Oracle Database      Data Warehouse & BI      Oracle Middleware      PL/SQL      Linux ...

Oracle DBA

Real Application Cluster (RAC) Maintenance tasks Backup and Recovery Database Architecture Replication Oracle Performance Tuning Oracle DBA - Other articles Oracle scripts/ Selects for DBA Oracle Errors (ORA-nnnnn)

The last articles in the site (HOT)

The most visited articles in the site

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

Oracle Applications EBS 11i, R12      Oracle Database      Data Warehouse & BI      Oracle Middleware      PL/SQL      Linux ...

 

     Copyright (c) 2011-2015  www.in-oracle.com  |  Disclaimer: The views expressed on this web site are my own and do not reflect the views of Oracle Corporation. You may use the information from this site only at your risk.