Schema Refresh Using Data pump

Term  Schema Refresh is a type of data synchronization process from Production Database to Testing  Database . Below Example shows step by step process of a Schema Refresh using Data Pump Job.

1)      Create a DBA privileged User for Schema Refresh on Destination Database;

SQL>Create user DBA_REFRESH identified by dba_refresh;

SQL>GRANT DBA TO DBA_REFRESH;

2)      Create a Public Database Link

Connect as DBA_REFRESH user

C:\>sqlplus dba_refresh/dba_refresh@Target_Service_Name

Connected.

      CREATE public DATABASE LINK REFRESH

      connect to ‘Source Schema’

      identified by Password

      using 'Connection String';

3)  Create Procedure For Schema Refresh On DBA_REFRESH user Of the Destination Database.

         CREATE OR REPLACE PROCEDURE DBA_REFRESH.refresh_schema

        ( source_schema in varchar2,

         destination_schema in varchar2,

         new_password in varchar2 default 'dba_refresh',

         network_link in varchar2 default 'REFRESH')

         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)  Execute created Procedure with appropriate values.

begin        

 DBA_REFRESH.refresh_schema (‘SOURCE_SCHEMA’,'DESTINATION_SCHEMA');

 end;

  /