• < link rel="alternate" href="../b10733.pdf" title="PDF version" type="application/pdf">

    Skip Headers

    Oracle® Database Advanced Replication Managemen t API Reference
    10g Release 1 (10.1)
    Part Number B10733-01
    Go to Documentation Home
    Home    		 Go to Book List
    Book List    		 Go to Table of Contents
    Contents    		 Go to Index
    Index    		 Go to Master Index
    Master Index    		 Go to Feedback page
    Feedback
    Go to previous page
    Previous< /a>    		 Go to next page
    Next
    		 View PDF

    7
    Managing a Master Replication Environment

    As your data delivery needs change due to growth, shrinkage, or emergencies, you are undoubtedly going to need to change the configuration of your replication environment. This chapter discusses managing the master sites of your replication environment. Specifically, this section describes altering and reconfiguring your master sites.

    This chapter contains these topics:

    Changing the Master Definition Site

    Many replication administrative tasks can be performed only from the master definition site. Use th e RELOCATE_MASTERDEF procedure in the DBMS_REPCAT package to move the master definition site to another mas ter site. This API is especially useful when the master definition site becomes unavailable and you need to specify a new master defi nition site (see "Option 2: The Old Master Definition Site Is Not Available").

    O ption 1: All Master Sites Are Available

    Perform the actions in this se ction to change the master definition site if all master sites are available. Meet the following requirements to complete these actio ns:

    Executed As: Replication Administrator

    Executed At: Any Master Site

    Replication Status: Running Normally (Not Quiesced)

    Complete the following st eps:

    Step 1 Connect to a master site as the repli cation administrator.
    CONNECT repadmin/repadmin@orc1.world
    Step 2 Relocate the master definition site.
    BEGIN
DBMS_REPCAT.RELOCATE_MASTERDEF (

     gname => 'hr_repg',
      old_masterdef => 'orc1.world',
      new_masterdef =>
 'orc2.world',
      notify_masters => TRUE,
      include_old_masterdef => TRUE);
END;
/

    Option 2: The Old Master Definition Site Is Not Available

    Perform the actions in this section to change the master definition site if the old master definition site is not available. Meet the following requirements to complete these actions:

    Executed As: Replication Administrator

    Executed At: Any Master Site

    Replication Status: Norma l

    Complete the following steps:

    Step 1 Connect to a master site as the replication administrator.
    
CONNECT repadmin/repadmin@orc2.world
    Step 2 Relocate the master definition site.
    BEGIN
DBMS_REPCAT.RELOCATE_MASTERDEF (
      gname => 'hr_repg',
      old_masterde
f => 'orc1.world',
      new_masterdef => 'orc2.world',
      notify_masters => TRUE
,
      include_old_masterdef => FALSE);
END;
/

    Adding New Master Sites

    As your replication environment expands, you may need to add new master sites to a master group. You can either add new master sites to a master group that is running normally or to a master group that is quiesc ed. If the master group is not quiesced, then users can perform data manipulation language (DML) operations on the data while the new master sites are being added. However, more administrative actions are required when adding new master sites if the master group is not quiesced.

    Note:

    When adding a master site to a master group that contains tables with circular dependencies or a table that contains a self-referential constraint, you must precreate the table definitions and manually load the data at the new master site. The following is an example of a circular dependency: Table A has a foreign key constraint on table B, and table B has a foreign key constraint on table A.

    Follow the instructions in the appropriat e section to add new master sites to a master group:

    Adding New Master Sites Without Quiescing the Master Gr oup

    This section contains procedures for adding new master sites to an existing master group that is not quiesced. These new sites may or may not already be replication sites (master sites or materialize d view sites) in other replication groups.

    You can use one of the following methods when you are adding a new master site without quiescing the master group:

    Use full database export/import and change-based recovery to add all of the replication groups at the master def inition site to the new master sites. When you use this method, the following conditions apply:

    If your environment do es not meet all of these conditions, then you must use object-level export/import to add the new master sites. Figure 7-1 summarizes these conditions.

    Note:
    • To use change-ba sed recovery, the existing master site and the new master site must be running under the same operating system, although the release of the operating system can differ. This condition does not apply to full database export/import.
    • You can use either Data Pump export/import or original export/import to perform exports and imports in an Advanced R eplication environment. General references to export/import in this document refer to both Data Pump and original export/import.

    Figure 7-1 Dete rmining Which Method to Use When Adding Master Sites

    Text description o f rarmanmb.gif follows

    Text description of the illustration rarmanmb.gif

    Use object-level export/import to add a master group to master sites that already have other replication gr oups or to add a master group to master sites that do not currently have any replication groups. This method can add one or more mast er groups to new master sites at a time, and you can choose a subset of the master groups at the master definition site to add to the new master sites during the operation.

    If you use object-level export/import and there are i ntegrity constraints that span more than one master group, then you must temporarily disable these integrity constraints on the table being added to a new master site, if the other tables to which these constraints refer already exist at the new master site. Initial ly, there are two rows in the DEFSCHEDULE data dictionary view that refer to the new master sites. When propagation is c aught up, there is one row in this view, and when propagation from all the master sites to the new master site is caught up, you can re-enable the integrity constraints you disabled.

    Again, the two methods for adding new maste r sites without quiescing the master groups are the following:

    When you use either method, propagation of deferred transactions to the new master site is partially or completely disabled while the new master sites are being added. Therefore, make sure each existing master site has en ough free space to store the largest unpropagated deferred transaction queue that you may encounter.

    In addition, the following restrictions apply to both methods:

    Also, before adding new master sites with either method, make sure you properly set up your new master sites for multimaster replication.

    Note:

    If progress appears to stop during one of the pro cedures described in the following sections, then check your trace files and the alert log for messages.

    See Also:

    Using Full Database Export/Import or Chang e-Based Recovery

    Figure 7-2 show s the major steps for using full database export/import or change-based recovery to add new master sites to a master group without qu iescing. The following example script adds the new master sites orc4.world and orc5.world to the hr_r epg master group. In this example, orc4.world is added using full database export/import and orc5.world is added using change-based recovery.

    Fig ure 7-2 Using Full Database Export/Import or Change-Based Recovery

    Text description of repma022.gif follows

    Text description of the illustration repma022.gif< /p>

    Meet the following requirements to complete these actions:

    Executed As: Replication Administrator, unless specified otherwise

    Executed At:

    • Step 1 at Each New Master Site
    • Steps 2 - 5 at Master Definition Site
    • Step 6 at the Master Definition Site and at Each New Master Site
    • Step 7 requires an export at the Master Definition site and a file transfer between sites.
    • Steps 8 - 10< /a> at Each New Master Site

    Replication Status: Running N ormally (Not Quiesced)

    Complete the following steps to use full database export/import or cha nge-based recovery to add sites to a master group.

    Note:

    If you are viewing this document online, then you can copy the text fr om the "BEGINNING OF SCRIPT" line after this note to the "END OF SCRIPT" line into a text editor and then edit the text to create a script for your environment.

    /**
*********************** BEGINNING OF SCRIPT ******************************
    Step 1 If you are using full database export/import, then create the databases that you want to add to t he master group.

    This step is not required if you are using change-based recovery.

    See Also:

    Oracle Databas e Administrator's Guide for information about creating a database

    */

SET ECHO ON

SPOOL add_masters_full.out

PAUSE Press <RETURN> when the databases for the new master sites are created.

/*
    Step 2 Set up each new master site as a replication site.

    Remember that you need to configure the following:

    • The replication admini strator at each new master site
    • A scheduled link from each existing master site to each new master site
    • A scheduled link from each new master site to each exist ing master site
    • A schedule purge job at each new master site
    */

PAUSE Press <RETURN> to cont
inue the new master sites have been setup and the 
required scheduled links have been created.

<
/a>/*
    See Also:
    Step 3 Connect as the replication administrator to the master definition site. 
    */

CONNECT repadmin/repadmin@orc1.world

/*
    Step 4 Specify new master sites for each master group.

    Before you begin, create the required schedul ed links between existing master sites and each new master site if they do not already exist.

    See Also: 
    */

BEGIN
DBMS_REPCAT.SPECIFY_NEW_MASTERS (
      gname => 'HR_REPG',
      master_list => 'orc4.world,orc5.world');
END;
/

/*

    You can begin to track the extension process by qu erying the following data dictionary views in another SQL*Plus session:

    • DBA_REPSITES_NEW
    • DBA_R EPEXTENSIONS
    */

PAUSE Press <RETURN> when you have completed the these steps.

/*
    Step 5 Add the new master sites.
    < /a>

    Before running the following procedure, ensure that there are an adequate number of background jobs running at eac h new master site. If you are using full database export/import, then make sure there is enough space in your rollback segments or un do tablespace for the export before you run this procedure.
    See Also: 
    */
 
VARIABLE masterdef_flashback_scn NUMB
ER;
VARIABLE extension_id VARCHAR2(32);
BEGIN
DBMS_REPCAT.ADD_NEW_MASTERS (
      export_required => true,
      available_master
_list => NULL, 
      masterdef_flashback_scn => :masterdef_flashback_scn, 
      exten
sion_id => :extension_id,
      break_trans_to_masterdef => false,    
      break_tran
s_to_new_masters => false,    
      percentage_for_catchup_mdef => 80,    
      cycle
_seconds_mdef => 60,    
      percentage_for_catchup_new => 80,    
      cycle_second
s_new => 60);
END;
/

/*





    The values for masterdef_flashback_scn and extension_id are saved in
to variables to be used later in the process. To see these values, you can query the DBA_REPSITES_NEW and DBA_REPE
XTENSIONS data dictionary views.
    

    */

PAUSE Pre
ss <RETURN> when you have completed the these steps.

/*


    

<
a name="36415">

    If you need to undo the changes made to a particular master site by the SPECIFY_NEW_MASTERS and ADD_NEW_MASTERS procedures, then use the DBMS_REPCAT.UNDO_ADD_NEW_MASTERS_REQUEST procedure.
    
 

    For the export_required parameter, true is specified because orc4
.world is being added using full database export/import. Although orc5.world is using change-based recovery, the
true setting is correct because at least one new master site is added using export/import.
    
 

    After successfully executing this procedure, monitor its progress by querying the DBA_REPCATLOG data dictiona
ry view in another SQL*Plus session. Do not proceed to Step 7 until there is no remaining informatio
n in this view about adding the new master sites. Assuming no extraneous information exists in DBA_REPCATLOG from other
operations, you can enter the following statement:
    

    SELECT COUNT(*) FROM DBA_REPCATLOG;


    



    All of the processing is complete when this statement returns zero (0).
    


    */

PAUSE Press <RETURN> to continue when DBA_REPC
ATLOG is empty.

/*

    



    Step 6 If you are using full database export/import, then create a directory object at each database.
    
 

    For master sites being added using change-based recovery, this step is not required and you can procee
d to Step 8.
    
 

    Each database
involved in this operation must have a directory object to hold the Data Pump dump file, and the user who will perform the export or
import must have READ and WRITE privileges on this directory object. In this example, a Data Pump export is
 performed at the master definition site, and a Data Pump import is performed at each new master site.
    
 

    If you are using full database export/import, then, while connected in SQL*Plus to the a database as an administrative user
 who can create directory objects using the SQL statement CREATE DIRECTORY, create a directory object to ho
ld the Data Pump dump file and log files. For example:
    

    */

CONNECT SYSTEM/MANAGER@orc1.world

CREATE DIRECTORY DPUMP_DIR AS '/usr/dpump_dir';

CONNECT SYSTEM/MANAGER@orc4.world

CREATE DIRECTORY DPUM
P_DIR AS '/usr/dpump_dir';

CONNECT SYSTEM/MANAGER@orc5.world

CREATE DIRECTORY DPUMP_DIR AS '/usr/dpump_dir';

/*


    



    In this example, SYSTEM user performs all exports and imports. If a user other than the
 user who created the directory object will perform the export or import, then grant this user READ and WRITE privileges on the directory object.
    
 

    Make sure you complete these actions at each database
involved in the operation.
    
 

    Step 7 Perform the fol
lowing substeps for the master sites being added using full database export/import.
    
 

    F
or master sites being added using change-based recovery, these substeps are not required and you can proceed to Step 8.
    
 

    Perform full database export of the mast
er definition database. Use the system change number (SCN) returned by the masterdef_flashback_scn parameter in Step 5 for the FLASHBACK_SCN export parameter.
    
 

    You can
query the DBA_REPEXTENSIONS data dictionary view for the FLASHBACK_SCN value:
    

    SELECT FLASHBACK_SCN FROM DBA_REPEXTENSIONS; 


    



    In this exam
ple, assume that the value returned by this query is 124723.
    
 

    In this example, orc4.world is using full database export/import. Therefore, perform the full database export of the master definition data
base so that it can be imported into orc4.world during a later step. However, the orc5.world database is us
ing change-based recovery. Therefore, the export would not be required if you were adding only orc5.world.
    
 

    On a command line, perform the export. This example connects as the SYSTEM user. The followin
g is an example Data Pump export command:
    

    expdp system/manager FULL=y DIRECTORY=DPUMP_DIR DUM
PFILE=fulldb_orc1.dmp 
FLASHBACK_SCN=124723


    



    Consider the following wh
en you run the Export utility:
    

      

    • Only users with the DBA role or the EXP_FULL_DATABASE role can export in full database mode.
      • 

    • Make sure the UNDO_RETENTION initialization parameter is set correctly before performing the export.
      • 

    • If you use the original Export utility, then do not use the CONSISTENT export pa
rameter. This parameter does not apply to Data Pump. 

      

      

      

      
See Also:  

    

    */
<
a name="24224">
PAUSE Press <RETURN> to continue when the export is complete.

/*


    



    Resume propagation to the master definition site.<
/p>
 

    Running the following procedure indicates that export is effectively finished and propagation
 can be enabled for both extended and unaffected master groups at the master sites.
    

    */

BEGIN
DBMS_REPCAT.RESUME_PROPAGATION_TO_MDEF (
      extension_id => :extension_id);
END;
/

<
a name="31371">/*


    



    You can find the extension_id by q
uerying the DBA_REPSITES_NEW data dictionary view.
    
 

    Transfer the export dump file
 to the new master sites.
    
 

    Using the DBMS_FILE_TRANSFER package, FTP, or some oth
er method, transfer the export dump file to the other new master sites that are being added with full database export/import. You wil
l need this export dump file at each new site to perform the import described in the next step.
    

    <
/a>*/

PAUSE Press <RETURN> to continue after transferring the dump file. 

/*


    



    Set the JOB_QUEUE_PROCESSES initialization parameter to zero for each new master site.
    

    */

PAUSE Press <RETURN> to continue after JOB_QUEUE_PROCESSES is set to zero at 
each new master site.

/*

    



    Step 8 Perform
import or change-based recovery at each new master site.
    

    

      

    • If you are using full database export/import, then complete the full database import of the database
you exported in Step 7 at each new master site that is being added
with full database export/import. 

      

      Perform the import. This example connects as the 
SYSTEM user to perform the import at orc4.world. The following is an example import command:
      

      impdp system/manager FULL=y DIRECTORY=DPUMP_DIR DUMPFILE=fulldb_orc1.dmp


      



      Only users with the DBA role or the IMP_FULL_DATABASE role can import in full dat
abase mode.
      
 

      

      

      
See Also:  

      Oracle Database Utilities for information about performing a Data Pump import
    

 

    */

PAUSE Press <RETURN> to
continue when the import is complete.

/*


    


      

    • If you are using change-based recovery, then perform change-based recovery using the syste
m change number (SCN) returned by the masterdef_flashback_scn parameter in Step 5. You
can query the DBA_REPEXTENSIONS data dictionary view for the masterdef_flashback_scn value. 

      

      You can perform a change-based recovery in one of the following ways:
      

 

      Connect to the site where you will perfo
rm the change-based recovery:
    
 

    */

CONNECT repadmin/repadmin@orc5.world

PAUSE Press <RETURN> to continue whe
n the change-based recovery is complete. You 
can use a separate terminal window to perform the change-based recovery.

/*

    



    Step 9 Configure
 the new sites for multimaster replication by completing the following steps:
    

    

      

    1. Ensure that the database structures, such as the datafiles, exis
t for the replicated schemas at each new master site. In this example, the replicated schema is hr.
      2. 

    2. Set the global name for each new master site. The global name for each new master site must match the g
lobal names specified in the SPECIFY_NEW_MASTERS procedure that you ran in Step 4. You
can query the DBLINK column in the DBA_REPSITES_NEW data dictionary view to see the global name for each ne
w master site. 

      

      You can set the global name using the ALTER DATABASE statement, as in the following example:
      

      ALTER DATABASE RENAME GLOBAL_NAME TO orc4.world
;


      

      3. 

    3. Create the appropriate scheduled links between the n
ew master sites and the existing master sites, including the master definition site. 

      

      

      

      
See Also:  

      "Creating Scheduled Links Between the Master Sites" for information
      <
/div>
    
 

    */

PAUSE Pre
ss <RETURN> when you have completed the these steps.

/*

    


<
h5 class="LST">Step 10 Allow new masters to receive deferred transactions.
 

    The following procedure enables the propagation of deferred transactions from other prepared new mast
er sites and existing master sites to the invocation master site. This procedure also enables the propagation of deferred transaction
s from the invocation master site to the other new master sites and existing master sites.
    
 

    

    

    


    
Caution:  

    Do not invoke this procedur
e until instantiation (export/import or change-based recovery) of the new master site is complete. 
    

    Do not allow any data manipulation language (DML) statements directly on the objects in the extended master group in the new mas
ter site until execution of this procedure returns successfully, because these DML statements may not be replicated.
    

    
 

    */

CONNECT repadm
in/repadmin@orc4.world

BEGIN
DBMS_REPCAT.P
REPARE_INSTANTIATED_MASTER (
      extension_id  => :extension_id);
END;
/

CONNECT repadmin/repadmin@orc5.world

BEGIN

DBMS_REPCAT.PREPARE_INSTANTIATED_MASTER (
      extension_
id  => :extension_id);
END;
/

SET ECHO OFF

SPOOL OFF

/*


    



    

    

    


    
Note:  

    You can
find the extension_id by querying the DBA_REPSITES_NEW data dictionary view.
    

    

    ************************** END OF SCRIPT **********************************/

    



    Using Object-Level Export/I
mport
    

 

    Figure 7-3 shows the major
 steps for using object-level export/import to add new master sites to a master group without quiescing. The following example proced
ure adds the new master sites orc4.world and orc5.world to the hr_repg master group. An object
-level export/import involves exporting and importing the tables in a master group. When you export and import the tables, other depe
ndent database objects, such as indexes, are exported and imported as well.
    
 

    If you have an in
tegrity constraint that spans two master groups, then you have a child table in one master group (the child master group) and a paren
t table in a different master group (the parent master group). In this case, Oracle Corporation recommends that you add new master si
tes to both master groups at the same time. However, if you cannot do this, then you must quiesce the child master group before addin
g new master sites to it. Here, the child table includes a foreign key, which makes it dependent on the values in the parent table. I
f you do not quiesce the child master group, then conflicts may result when you add master sites to it. You can still add master site
s to the parent master group without quiescing it.
    
 

    Figure 7-3 Using Object-Level Export/Import
    
Text description of re
pma023.gif follows 

    Text description of the illustration repma023.gif
    
 

    Meet the following requirements to complete these actions:
    
 

    Executed As: Replication Administrator, unless specified otherwise
    
 

    Executed At:
    

      

    • Steps 1 - 6 at Master Definition Site
      • 

    • Step
7 at the Master Definition Site and at Each New Master Site
      • 
Steps 8 - 9 at Master
 Definition Site

    • Step 10 requires a file transf
er between sites.
      • 

    • Steps 11 - 12 at Each New Master Site
    
 

    Replication Status: Running Normally (Not Quiesced)
    
 

    Complete the following steps to use object-level export/
import to add sites to a master group.
    
 

    

    

    


    
Note:  

    If you are viewing this document online, then you can copy the text from the "BEGI
NNING OF SCRIPT" line after this note to the "END OF SCRIPT" line into a text editor and then edit t
he text to create a script for your environment.
    

    

    /**************
*********** BEGINNING OF SCRIPT ******************************

    



    Step 1 If the users for the replicated schemas do not exist at the new master sites, then create them now.
 

    In this example, the replicated schema is hr. This schema probably already exist
at the new master sites because it is a sample schema that is installed when you install Oracle.
    
 

    

    

    
See Also:  

    Oracle Database Sample Schemas for general information
 about the sample schemas and for information about installing them
    

    *
/

SET ECHO ON

SPOOL add_masters_object.out

PAUSE Press <RETURN> to continue when the users are created at the new master 
sites.

/*

    



    Step 2 If
any of the tables in the master group have circular dependencies, then precreate these tables at the new master sites.
    
 

    Failure to precreate these tables will result in errors later in the procedure. If there are no cir
cular dependencies, then this step is not required, and you can proceed to Step 3.
    
 

    Some of the tables in the hr schema contain circular dependencies. Therefore, in this example, th
e tables in the hr schema must be precreated at each new master site. Again, the hr schema tables are typic
ally created during Oracle installation and so may already exist at the new master sites.
    
 

    If
you need to precreate tables, then disable referential integrity constraints for these tables at the new master sites before the impo
rt. Referential integrity constraints can cause errors when you import data into existing tables. This example disables the referenti
al integrity constraints for the precreated tables in the hr schema at the new master sites.
    
 
<
p class="BP">Further, the precreated tables at the new master sites should not contain any data. This example truncates the tables in
 the hr schema at the new master sites to ensure that they do not contain any data.
    
 

    

    

    
See Also:  

    

    */

PAUSE Press <RETURN> to continue when the tables are precreated at the new 
master sites, if table precreatio
n is required. After the tables are precreated, 
the following statements disable the referential integrity constraints related 
to t
he hr schema and truncate the tables in the hr schema at the new site.

CONNECT oe/oe@orc4.wo
rld

ALTER TABLE oe.warehouses 
  DISABLE CONSTRAINT warehouses_location_
fk;

ALTER TABLE oe.customers 
  DISABLE CONSTRAINT customers_account_man
ager_fk;

ALTER TABLE oe.orders 
  DISABLE CONSTRAINT orders_sales_rep_fk
;

CONNECT hr/hr@orc4.world

ALTER TABLE hr.countries
 
  DISABLE CONSTRAINT countr_reg_fk;

ALTER TABLE hr.departments 
  DISABLE CONSTRAINT dept_mgr_fk
  DISABLE CONSTRAINT dept_loc_fk;

ALTER TABLE hr.employees
  DISABLE CONSTRAINT emp_dept_fk
  DISABLE CONSTRAINT emp_j
ob_fk
  DISABLE CONSTRAINT emp_manager_fk;

ALTER TABLE hr.job_history
  DISABLE CONSTRAINT jhist_job_fk
  DISABLE CONSTRAINT jhist_emp_fk
  DISAB
LE CONSTRAINT jhist_dept_fk;

ALTER TABLE hr.locations 
  DISABLE CONSTRA
INT loc_c_id_fk;

TRUNCATE TABLE hr.countries;
TRUNCATE TABLE hr.departme
nts;
TRUNCATE TABLE hr.employees;
TRUNCATE TABLE hr.jobs;
TRUNCATE TABLE
hr.job_history;
TRUNCATE TABLE hr.locations;
TRUNCATE TABLE hr.regions;

CONNECT oe/oe@orc5.world

ALTER TABLE oe.warehouses 

  DISABLE CONSTRAINT warehouses_location_fk;

ALTER TABLE oe.customers 

 DISABLE CONSTRAINT customers_account_manager_fk;

ALTER TABLE oe.orders 
  DISABLE CONSTRAINT orders_sales_rep_fk;

CONNECT hr/hr@orc5.world

ALTER TABLE hr.countries 
  DISABLE CONSTRAINT countr_reg_fk;

ALTER TABLE hr.departments 
  DISABLE CONSTRAINT dept_mgr_fk
  DISABLE CONSTRAINT dept
_loc_fk;

ALTER TABLE hr.employees
  DISABLE CONSTRAINT emp_dept_fk
  DISABLE CONSTRAINT emp_job_fk
  DISABLE CONSTRAINT emp_manager_fk;

ALTER TABLE hr.job_history
  DISABLE CONSTRAINT jhist_job_fk
  DISABLE CONSTRAINT
 jhist_emp_fk
  DISABLE CONSTRAINT jhist_dept_fk;

ALTER TABLE hr.locatio
ns 
  DISABLE CONSTRAINT loc_c_id_fk;

TRUNCATE TABLE hr.countries;
TRUNCATE TABLE hr.departments;
TRUNCATE TABLE hr.employees;
TRUNCATE TABLE hr.
jobs;
TRUNCATE TABLE hr.job_history;
TRUNCATE TABLE hr.locations;
TRUNCAT
E TABLE hr.regions;

/*

    



    Step 3 Set up each new master site as a replication site.
    
 

    Remember
that you need to configure the following:
    

    

      

    • The replication administrator at each new master site
      • 

    • A scheduled link
from each existing master site to each new master site
      • 

    • A scheduled link from eac
h new master site to each existing master site
      • 

    • A schedule purge job at each new
master site
    
 

    */

PAU
SE Press <RETURN> to continue the new master sites have been setup and the 
required scheduled links have been created.

/*

    



 
Step 4 Connect to the master definition site as the replication administrator
.

    */

CONNECT repadmin/repadmin@orc1.world

/*

    



    Step
 5 Specify new master sites for each master group.
    

    */

BEGIN
DBMS_REPCAT.SPECIFY_NEW_MASTERS (
      gna
me => 'hr_repg',
      master_list => 'orc4.world,orc5.world');
END;
/

/*


    



    You can begin to trac
k the extension process by querying the following data dictionary views in another SQL*Plus session:
    

    

      

    • DBA_REPSITES_NEW
      • 

    • DBA_REPEXTENSIONS
    
 

    Step 6 Add the new master sites.
    
 

    Before running the following procedure,
ensure that there are an adequate number of background jobs running at each new master site. Also, make sure there is enough space in
 your rollback segments or undo tablespace for the export before you run this procedure.
    
 

    

    

    
See Also:  

    

    */

VARIABLE
masterdef_flashback_scn NUMBER;
VARIABLE extension_id VARCHAR2(32);
BEGIN
DBMS_REPCAT.ADD_NEW_MASTERS (
      export_required => true,
      available_master_list => 'orc4.world,orc5.world', 
      masterdef_flashback_scn => :masterdef_
flashback_scn, 
      extension_id => :extension_id,
      break_trans_to_masterdef =>
false,    
      break_trans_to_new_masters => false,    
      percentage_for_catchup_mde
f => 80,    
      cycle_seconds_mdef => 60,    
      percentage_for_catchup_new =>
 80,    
      cycle_seconds_new => 60);
END;
/

<
a name="31829">/*


    



    The sites specified for the available_mas
ter_list parameter must be same as the sites specified in the SPECIFY_NEW_MASTERS procedure in Step 5.
    
 

    The values for masterdef_flashback_scn and extensio
n_id are saved into variables to be used later in the process. To see these values, you can also query the DBA_REPSITES_
NEW and DBA_REPEXTENSIONS data dictionary views.
    
 

    If you need to undo the
changes made to a particular master site by the SPECIFY_NEW_MASTERS and ADD_NEW_MASTERS procedures, then us
e the UNDO_ADD_NEW_MASTERS_REQUEST procedure.
    
 

    After successfully executing this
procedure, monitor its progress by querying the DBA_REPCATLOG data dictionary view in another SQL*Plus session. Do not p
roceed to Step 8 until there is no remaining information in this view about adding the new master si
tes. Assuming there is no extraneous information in DBA_REPCATLOG from other operations, you can enter the following sta
tement:
    

    SELECT COUNT(*) FROM DBA_REPCATLOG;


    




    All of the processing is complete when this statement returns zero (0).
    

    */

PAUSE Press <RETURN> to continue when DBA_REPCATLOG is empty.

/*

    



    Step 7 Create a directory obj
ect at each database.
    
 

    Each database involved in this operation must have a directory
object to hold the Data Pump dump file, and the user who will perform the export or import must have READ and WRIT
E privileges on this directory object. In this example, a Data Pump export is performed at the master definition site, and a D
ata Pump import is performed at each new master site.
    
 

    While connected in SQL*Plus to the a da
tabase as an administrative user who can create directory objects using the SQL statement CREATE DIRECTORY,
 create a directory object to hold the Data Pump dump file and log files. For example:
    

    */

CONNECT SYSTEM/MANAGER@orc1.world

CREATE DIRECTORY DPU
MP_DIR AS '/usr/dpump_dir';

CONNECT SYSTEM/MANAGER@orc4.world

CREATE DIRECTORY DPUMP_DIR AS '/usr/dpump_dir';

CONNECT SYSTEM/MANAGER@orc5.world


CREATE DIRECTORY DPUMP_DIR AS '/usr/dpump_dir';

/*



    



    In this example, SYSTEM user performs all exports and i
mports. If a user other than the user who created the directory object will perform the export or import, then grant this user 
READ and WRITE privileges on the directory object.
    

    Make sure you complete
 these actions at each database involved in the operation.

    



    Step 8 Perform object-level export of tables at master definition database.
    
 

    At the master definition database, perform an object-level export for each master table in the master groups that will be created
 at the new master sites. An object-level export includes exports performed in table mode, user mode, or tablespace mode.
    
 

    Use the system change number (SCN) returned by the masterdef_flashback_scn parameter in Ste
p 6 for the FLASHBACK_SCN export parameter. You can query the DBA_REPEXTENSIONS data dictionary view for the FLASHBACK_SCN value:
    

    SELECT FLASHBACK_SCN FRO
M DBA_REPEXTENSIONS; 


    



    In this example, assume that the SCN value is <
code>3456871.
    
 

    On a command line, perform the export. This example connects as the SYSTEM user. The following is an example Data Pump export command:
    

    expdp system/mana
ger TABLES=HR.COUNTRIES,HR.DEPARTMENTS,HR.EMPLOYEES,HR.JOB_
HISTORY,HR.JOBS,HR.LOCATIONS,HR.REGIONS DIRECTORY=DPUMP_DIR DUMPFILE=hr_
tables.dmp CONTENT=data_only FLASHBACK_SCN=3456871


    



    The CONTENT<
/code> parameter is used in this example because the tables already exist at the import sites. You may not need to specify this param
eter.
    
 

    Consider the following when you run perform the export:
    

      

    • Make sure the UNDO_RETENTION initialization parameter is set correctly before perf
orming the export.
      • 

    • If you use the original Export utility, then do not use the <
code>CONSISTENT export parameter. This parameter does not apply to Data Pump. 

      

      

      

      
See Also:  
<
/td>
    

    */

PAUSE Press <RET
URN> to continue when the export is complete.

/*

    



    Step 9 Resume propagation to the master definition site.
    
<
/a> 

    Running the following procedure indicates that export is effectively finished and propagation can be enabled for b
oth extended and unaffected master groups at the master sites.
    

    */

CONNECT repadmin/repadmin@orc1.world

BEGIN
DBMS_REPCAT.RESUME_PROPAGATION_TO_MDEF (
      extension_id => :extension_id);
END;
/

/*


    



    You can find the extension_id by querying the DBA_REPSITES_NEW data dictionary view.
    
 

    Step 10 Transfer the export dump files to the new master site
s.
    
 

    Using the DBMS_FILE_TRANSFER package, FTP, or some other method, tran
sfer the export dump files to the other new master sites that are being added with object-level export/import. You will need these ex
port dump files at each new site to perform the import described in the next step.
    

    */

PAUSE Press <RETURN> to continue when the export dump files have been
tra
nsfered to the new master sites that are being added with object-level
export/import.

/*

    



    Step 11 Perform object-level
imports at each new master site of each object you exported in Step 8.
    
<
/a> 

    On a command line, perform the import. This example connects as the SYSTEM user. The following is an
example import command:
    

    impdp system/manager TABLES=HR.COUNTRIES,HR.DEPARTMENTS,HR.EMPLOYEES,
HR.JOB_
HISTORY,HR.JOBS,HR.LOCATIONS,HR.REGIONS DIRECTORY=DPUMP_DIR DUMPFILE=hr_
tables.dmp CONTENT=data_only TABLE_EXISTS_ACTION=app
end


    



    Other objects, such as the indexes based on the tables, are impor
ted automatically. The CONTENT and TABLE_EXISTS_ACTION parameters are used in this example because the tabl
es already exist at the import sites. You may not need to specify these parameters.
    
 

    


See Also:  

    Oracle Database Utilities for information about p
erforming a Data Pump import
    
 

    Perform the object-level imports at each
 site:
    

    */

PAUSE Press <RETURN> to continue whe
n the imports are complete at each site. You 
can use a separate terminal window to perform the object-level imports.

/*

    



    Step 12 Allow new
 masters to receive deferred transactions.
    
 

    The following procedure enables the propag
ation of deferred transactions from other prepared new master sites and existing master sites to the invocation master site. This pro
cedure also enables the propagation of deferred transactions from the invocation master site to the other new master sites and existi
ng master sites.
    
 

    

    

    


    
Caution:  

    Do not invoke this procedure until object-level export/import for the new master site is complete. 
    

    Do not allow any data manipulation language (DML) statements directly on the objects in the exte
nded master group in the new master site until execution of this procedure returns successfully, because these DML statements may not
 be replicated.
    

    

    */

CONN
ECT repadmin/repadmin@orc4.world

BEGIN
DBM
S_REPCAT.PREPARE_INSTANTIATED_MASTER (
      extension_id  => :extension_id);
END;
/

CONNECT repadmin/repadmin@orc5.world

BEGIN
DBMS_REPCAT.PREPARE_INSTANTIATED_MASTER (

extension_id  => :extension_id);
END;
/

SET ECHO
OFF

SPOOL OFF

/*

    



    

    

    


    
Note:  

    You can find the extension_id by querying the DBA_REPSITES_NEW data dictionary view.
    

    

    ************************** END OF SCRIPT **********************************/

    




    Adding New Master Sites to a Quiesced
Master Group
    

 

    You can add new master sites to a quiesced master group i
n one of the following ways:
    

 

    T
ypically, you should only use the ADD_MASTER_DATABASE procedure if you have a relatively small master group or if you pl
an to precreate the replication tables and load the data into them at the new master sites. If this is not the case, the ADD_MA
STER_DATABASE procedure may not be a good option because the entire master group is copied over the network. For larger master
 groups, either precreate the objects in the master group at the new master sites or use offline instantiation.
    
<
/a> 

    Adding New Master Sites Using the
ADD_MASTER_DATABASE Procedure
    

     

    You can use the ADD_MASTER_DATABAS
E procedure to add additional master sites to an existing master group that is quiesced. Executing this procedure replicates e
xisting master objects to the new site. If any master site is lower than 9.0.1 compatibility level, then you must use the following p
rocedure. That is, the master group must be quiesced to extend it with new master sites. You control the compatibility level of a dat
abase with the COMPATIBLE initialization parameter.
    
 

    Meet the following requireme
nts to complete these actions:
    
 

    Executed As: Replication Adminis
trator
    
 

    Executed At: Master Definition Site
    

 

    Replication Status: Quiesced
    
 

    Complete the f
ollowing steps to use the ADD_MASTER_DATABASE procedure to add sites to a master group.
    
 

    

    /************************* BEGINNING OF SCRIPT ******************************

    Step 1 Set up the new master site.
    Make sure the appropriate schema and database links have been created before adding your new master site
. Be sure to create the database links from the new master site to each of the existing masters sites. Also, create a database link f
rom each of the existing master sites to the new master site. After the database links have been created, make sure that you also def
ine the scheduled links for each of the new database links.
    

    

    


    
Note:  

    If you are viewing t
his document online, then you can copy the text from the "BEGINNING OF SCRIPT" line after this note to the "END OF SCRIPT" line into a text editor and then edit the text to create a script for your environment.
    

    




 

 

    

    
See Also:  

    

    */

SET ECHO ON

SPOOL add_masters_quiesced.out

PAUSE Press <RETURN>
to the new master site has been set up.

/*

    



    Step 2 Connect to the master definition site as the replication administrator.
    

    */

CONNECT repadmin/repadmin@orc1.world


/*

    



    Step 3 If the replicat
ion status is normal, then change the status to quiesced.
    

    */

BEGIN
DBMS_REPCAT.SUSPEND_MASTER_ACTIVITY (
      gname => 'hr_repg');
END;
/

/*

    



    Step 4 Add the new master sites.
    
 

    This example assumes that the replicated objects do not exist at the new master site. Therefore, the co
py_rows parameter is set to true to copy the rows in the replicated objects at the master definition site to the
new master site, and the use_existing_objects parameter is set to false so that Advanced Replication create
s the replicated objects at the new site. If the replicated objects already exist at the new site but do not contain any data, then s
et use_existing_objects to true.
    

    */

BEGIN
DBMS_REPCAT.ADD_MASTER_DATABASE (
      gnam
e => 'hr_repg',
      master => 'orc4.world',
      use_existing_objects => FALSE,
<
a name="12881">      copy_rows => TRUE,
      propagation_mode => 'ASYNCHRONOUS');

END;
/

/*


    



    You should wait until the DBA_REPCATLOG view is empty. This view has temporary information that is cleared after succe
ssful execution. Execute the following SELECT statement in another SQL*Plus session to monitor the DBA_REPCATLOG view:
    

    SELECT COUNT(*) FROM DBA_REPCATLOG WHERE GNAME = 'HR_REPG';


    



    All of the processing is complete when this statement returns zero (0).
    

    */

PAUSE Press <RETURN> to continue when DBA_REPCATLOG is empty.


/*

    



    S
tep 5 Resume replication activity.
    

    */

BEGIN
DBMS_REPCAT.RESUME_MASTER_ACTIVITY (
      gname => 'hr_r
epg');
END;
/

SET ECHO OFF

SPOOL OFF

/************************* END OF SCRIPT *************************
*********/

    



    Adding New Master Sites with Offline Instantiation Using Export/Import
    

 

    Expanding established replication environments can cause network traffic when you add a new master site to y
our replication environment using the ADD_MASTER_DATABASE procedure. This is caused by propagating the entire contents o
f the table or materialized view through the network to the new replicated site.
    
 

    To minimize
such network traffic, you can expand your replication environment by using the offline instantiation procedure. Offline instantiation
 takes advantage of Oracle's Export and Import utilities, which allow you to create an export file and transfer the data to the new s
ite through another storage medium, such as CD-ROM, tape, and so on.
    
 

    The following script is
an example of how to perform an offline instantiation of a master site. This script can potentially eliminate large amounts of networ
k traffic caused by the other method of adding a new master site to an existing quiesced master group. The script assumes that the hr schema does not exist at the new master site and instantiates this schema at the new master site. The hr s
chema is created automatically when Oracle is installed. You may choose to drop the hr schema at the new master site bef
ore you start this example.
    
 

    

    

    


    
Note:  

    You can use either Data Pump export/import or original export/import to perform exports and i
mports in an Advanced Replication environment. General references to export/import in this document refer to both Data Pump and origi
nal export/import.
    

    
 

    Meet the following requirements to complete t
hese actions:
    
 

    Executed As: Replication Administrator, unless sp
ecified otherwise
    
 

    Executed At: Master Definition Site and New M
aster Site
    
 

    Replication Status: Quiesced and Partial
    
 

    Complete the following steps to use offline instantiation to add sites to a master group.
    
 

    

    

    


    
Note:  

    If
 you are viewing this document online, then you can copy the text from the "BEGINNING OF SCRIPT" line after this note to the "END OF
SCRIPT" line into a text editor and then edit the text to create a script for your environment.
    

    

    /************************* BEGINNING OF SCRIPT *********************
*********

    



    Step 1 Set up the new master site.
    
 

    Make sure the appropriate schema and database links have been created before performin
g the offline instantiation of your new master site. Be sure to create the database links from the new master site to each of the exi
sting masters sites. Also, create a database link from each of the existing master sites to the new master site. After the database l
inks have been created, make sure that you also define the scheduled links for each of the new database links.
    
 


    */

SET ECHO ON

SPOOL add_masters_instant.out

PAUSE Press <RETURN> to the new master site has been set up.

/*


    



    Step 2 Connect to the master definition site
as the replication administrator.
    

    */

CONNECT
 repadmin/repadmin@orc1.world

/*

    



    Step 3 Suspend master activity.
    
 

    You need to suspend maste
r activity for the existing master sites before exporting your master data and beginning the offline instantiation process.
    

    */

BEGIN
DBM
S_REPCAT.SUSPEND_MASTER_ACTIVITY (
      gname => 'hr_repg');
END;
/

/*

    



    Step 4 Verify that there are no pending transactions in a separate SQL*Plus session.
    
 

    This includes pushing any outstanding deferred transactions, resolving any error transactions, and pushing any administrative transa
ctions. This step must be performed at each of the existing master sites.
    
 

    Check the error tra
nsaction queue.
    

    SELECT * FROM DEFERROR;


    



    If any deferred transactions have been entered into the error queue, then you need to resolve the error situation and then
 manually reexecute the deferred transaction. The following is an example:
    

    BEGIN
DBMS_DEFER_SYS.EXECUTE_ERROR (
      deferred_tran_id => '128323',
      destination => 'orc1.world');
END;
/


    



    Check for outstanding administrative requests.
    

    SELECT * FR
OM DBA_REPCATLOG;


    



    If any administrative requests remain, then you can
 manually execute these requests or wait for them to be executed automatically. You may need to execute the DBMS_REPCAT.DO_DEFE
RRED_REPCAT_ADMIN procedure several times, because some administrative operations have multiple steps. The following is an exa
mple:
    

    BEGIN
DBMS_REPCAT.DO_DEFERRED_REPCAT_A
DMIN (
      gname => 'hr_repg',
      all_sites => TRUE);
END;

/

*/

PAUSE Press <RETURN>
; to continue when you have verified that there are no 
pending requests.

/*

    



    Step 5 Begin offline instantiation procedure.
    
*/

BEGIN
D
BMS_OFFLINE_OG.BEGIN_INSTANTIATION (
      gname => 'hr_repg',
      new_site => 'o
rc4.world');
END;
/

/*

    You should wait until the DBA_REPCATLOG view is empty. This view has temporary inform ation that is cleared after successful execution. Execute the following SELECT statement in another SQL*Plus session to monitor the DBA_REPCATLOG view:

    SELECT * FROM DBA_REPCATLOG WHERE GNAME = 'HR_RE
PG';

*/

PAUSE Press <RETURN> to continue when
 DBA_REPCATLOG is empty.

/*
    Step 6 Create a directory object at each database.

    Each databa se involved in this operation must have a directory object to hold the Data Pump dump file, and the user who will perform the export or import must have READ and WRITE privileges on this directory object. In this example, a Data Pump export is performed at the master definition site, and a Data Pump import is performed at the new master site.

    While connected in SQL*Plus to a database as an administrative user who can create directory objects using the SQL statem ent CREATE DIRECTORY, create a directory object to hold the Data Pump dump file and log files. For example: 

    */

CONNECT SYSTEM/MANAGER@orc1.world

CREATE DIRECTORY DPUMP_DIR AS '/usr/dpump_dir';

CONNECT SYSTEM/MA
NAGER@orc4.world

CREATE DIRECTORY DPUMP_DIR AS '/usr/dpump_dir';

/*

    Make sure you complete these actions at both databas es involved in the operation. In this example, SYSTEM user creates the directory objects and performs all exports and im ports. If a user who does not own the directory object will perform the export or import, then grant the user READ and < code>WRITE privileges on the directory object.

    Step 7 In a separate terminal window, perform the export.

    On a command line, per form the export. This example connects as the SYSTEM user. The following is an example Data Pump export command:

    expdp system/manager SCHEMAS=hr DIRECTORY=DPUMP_DIR DUMPFILE=hr_schema.dmp

    When you export tables, their indexes are exported automatically.

    See Also:

    Oracle Database Utilities fo r information about performing a Data Pump export

    */


PAUSE Press <RETURN> to continue when the export is complete.

/*
    Step 8 Resume partial replication activity .

    Because it may take some time to complete the offline instantiation process, you ca n resume replication activity for the remaining master sites (excluding the new master site) by executing the RESUME_SUBSET_OF_ MASTERS procedure in the DBMS_OFFLINE_OG package after the export is complete. In the following example, replicat ion activity is resumed at all master sites except the new master site -- orc4.world.

    */

CONNECT repadmin/repadmin@orc1.world

BEGIN

DBMS_OFFLINE_OG.RESUME_SUBSET_OF_MASTERS (
      gname =>
; 'hr_repg',
      new_site => 'orc4.world');
END;
/
<
/a>
/*
    Step 9 Transfer the export dump files to the new master site.

    Using the DBMS_FILE_TRANSFER package, FTP, or some other method, transfer the export dump file to the new master site. You will need this export dump file at the new site to perform the import described in the next step.

    */

PAUSE Press <RETURN> to continue when the export dump file has been
transfered to the new master
site.

/*
    Step 10 Connect to the new master site as the replication administrator.
    */

CONNECT repadmin/repadmin@orc4.world

/*
    Step 11 Prepare the new master site.

    You must prepare the new site to import the data in your export file. Make sure you execute the following proc edure at the new master site.

    */

BEGIN
DBMS_OFFLINE_OG.BEGIN_LOAD (
      gname => 'hr_repg',
      new_site => 'orc4.world');
END;
/

/*
<
/pre>



    Step 12 In a separate terminal window, import d
ata from export dump file.
    
 

    On a command line, perform the import. This example connec
ts as the SYSTEM user. The following is an example import command:
    

    impdp system/
manager SCHEMAS=hr DIRECTORY=DPUMP_DIR DUMPFILE=hr_schema.dmp


    



    Other o
bjects, such as the indexes based on the tables, are imported automatically.
    
 

    

    

    
See Also:  

    Oracle Database Utilities for information about performi
ng a Data Pump import
    

    */

PAU
SE Press <RETURN> to continue when the import is complete.

/*

    




    Step 13 Complete the load process at new master site.
    
 

    After importing the export file, you are ready to complete the offline instantiation process at the n
ew master site. Executing the DBMS_OFFLINE_OG.END_LOAD procedure prepares the new site for normal replication activity.<
/p>

    */

BEGIN
DBMS_OFFLINE_OG.END_LOAD (
      gname => 'hr_repg',
      new_site => 'orc4.
world');
END;
/

/*

    



    Step 14 Connect to the master definition site as the replication administrator.

    

    */

CONNECT repadmin/repadmin@orc1.world

/*

    



    Step
15 Complete instantiation process.
    
 

    After completing the steps at the new master site,
 you are ready to complete the offline instantiation process. Executing the END_INSTANTIATION procedure in the DBM
S_OFFLINE_OG package completes the process and resumes normal replication activity at all master sites. Make sure you execute
the following procedure at the master definition site.
    

    */

BEGIN
DBMS_OFFLINE_OG.END_INSTANTIATION (
      gname
=> 'hr_repg',
      new_site => 'orc4.world');
END;
/

SET ECHO OFF

SPOOL OFF


/************************* END OF SCRIPT **********************************/

    



    Removing a Master Site from a Master Group
    

 

    When it becomes necessary to remove a master site from a master group, use the REMOVE_M
ASTER_DATABASES procedure to drop one or more master sites.
    
 

    Meet the following require
ments to complete these actions:
    
 

    Executed As: Replication Admin
istrator
    
 

    Executed At: Master Definition Site
    
 

    Replication Status: Quiesced
    
 

    Complete the
 following steps to remove a master site.
    
 

    

    

    


    
Note
:  

    If you are viewing this document online, then you can copy the text from the "B
EGINNING OF SCRIPT" line after this note to the "END OF SCRIPT" line into a text editor and then edi
t the text to create a script for your environment.
    

    

    /***********
************** BEGINNING OF SCRIPT ******************************

    



    Step 1 Connect to the master definition site as the replication administrator.
    

    */

SET ECHO ON

SPOOL remove_masters.o
ut

CONNECT repadmin/repadmin@orc1.world

/*

    



    Step 2 If the replication status is normal for the mas
ter group, then change the status to quiesced.
    

    */

BEGIN
DBMS_REPCAT.SUSPEND_MASTER_ACTIVITY (
      gnam
e => 'hr_repg');
END;
/

/*

    



    Step 3 Remove the master site.
    

    */

BEGIN
DBMS_REPCAT.REMOVE_MAST
ER_DATABASES (
      gname => 'hr_repg',
      master_list => 'orc4.world');
END;
/

/*


    



    You should wait until the DBA_REPCATLOG view is empty. Execute the following SELECT stateme
nt in another SQL*Plus session to monitor the DBA_REPCATLOG view:
    

    SELECT * FROM
DBA_REPCATLOG WHERE GNAME = 'HR_REPG';

*/

PAUSE Pre
ss <RETURN> to continue when DBA_REPCATLOG is empty for the master 
group.

/*

    



    Step 4 Resume master activity for the master group.
    

    */

BEGIN
DBMS_REPCAT.RESUME_MASTER_ACTIVITY (
      gname => 'hr_repg');
END;
/

SET ECHO OFF

SPOOL OFF

/************************* END OF SCRIPT **********************************/

    



    Removing an Unavailable Master Site<
/font>
    

     

    The sites being removed from a master group do not have to be accessib
le. When a master site will not be available for an extended period of time due to a system or network failure, you might decide to d
rop the master site from the master group.
    
 

    However, because the site is unavailable, you most
 likely cannot suspend replication activity for the master group. You can use the REMOVE_MASTER_DATABASES procedure in t
he DBMS_REPCAT package to remove master sites from a master group, even if the master group is not quiesced.
    
 

    If this is the case, you are responsible for:
    

      

    • Cleaning the deferred transaction queue
      • 

    • Removing any data inconsisten
cies
    
 

    Specifically, the next time that you suspend replication activity for a master gro
up, you must complete the following steps as soon as possible after the unavailable master sites are removed:
    
 

    Step 1 Suspend replication activity for the master group.
    
 

    See "SUSPEND_MASTER_ACTIVITY Procedure" for information.
    
 

    Step 2 Delete all defer
red transactions from each master site where the destination for the transaction is a removed master site.
    
 

    See "DELETE_TRAN Procedure" for information.<
/p>
 

    Step 3 Remove all deferred transactions from remo
ved master sites.
    
 

    See "DELETE_TRAN Procedure" for information.
    
 

    Step
4 Reexecute or delete all error transactions at each remaining master site.
    
 

    See "Managing the Error Queue" for information about reexecuting error trans
actions, and see "DELETE_TRAN Procedure" for information about remo
ving error transactions.
    
 

    Step 5 Ensure that no de
ferred or error transactions exist at each remaining master.
    
 

    If you cannot remove one
 or more deferred transactions from a remaining master, execute the DBMS_DEFER_SYS.DELETE_TRAN procedure at the master s
ite.
    
 

    Step 6 Ensure that all replicated data is co
nsistent.
    
 

    See Chapter 16, "DBMS_RECTIFIER_DIFF"
 for information about determining and correcting differences.
    
 

    Step 7 Resume replication activity for the master group.
    
 

    See "RESUME_MASTER_ACTIVITY Procedure" for information.
    



    

    

    


    
Note:  

    After droppin
g an unavailable master site from a master group, you should also remove the master group from the dropped site to finish the cleanup
.
    

    
 

    Updating the Comments Fields in Data Dictionary Views
    

 

    Several procedures in the DBMS_REPCAT package enable you to update the comment information in the various data diction
ary views associated with replication. Table 7-1 lists the appropriate procedure to call for ea
ch view.
    
 

     Table 7-1 Updating Comments in Advanced Replication Facility Views  
    


<
td class="Formal">
    "COMMENT_ON_REPSITES Procedure".
    
<
td class="Formal">

    DBA_REPRESOLUTION
(uniqueness conflicts)

    Using Procedural Replication
    Procedural replication can o
ffer performance advantages for large batch-oriented operations operating on large numbers of rows that can be run serially within a
replication environment.
    A good example of an appropriate application is a purge operation, a
lso referred to as an archive operation, that you run infrequently (for example, once in each quarter) during off hours to remove old
 data, or data that was "logically" deleted from the online database. An example using procedural replication to purge deleted rows i
s described in the "Avoiding Delete Conflicts" section in Chapter 5, "Conflict Resolution Concepts and Architecture", of Oracle Database Advanced Replication.
    Restrictions on Procedural Replication
    All parameters for a replicated procedure must be IN parameters; OUT and IN/OUT modes ar
e not supported. The following datatypes are supported for these parameters:
      

    • VARCHAR2
      • 

    • NVARCHAR2
      • 

    • NUMBER
      • 

    • DATE
      • 

    • RAW
      • 

    • ROWID

    • CHAR
      • 

    • NCHAR

      • 

    • Binary LOB (BLOB)
      • 

    • Character LOB (CLOB)
      • 

    • National character LOB (NCLO
B)
      • 

    • User-defined datatypes
    O
racle cannot detect update conflicts produced by replicated procedures. Replicated procedures must detect and resolve conflicts thems
elves. Because of the difficulties involved in writing your own conflict resolution routines, it is best to simply avoid the possibil
ity of conflicts altogether.
    Adhering to the following guidelines helps you ensure that your
tables remain consistent at all sites when you plan to use procedural replication:
      

    • <
a name="22556">You must disable row-level replication within the body of the deferred procedure. See "Updating the Comments Fields in Data Dictionary Views".
      • 

    • Only one replicated procedure should be run at a time, as described in "Serializing Trans
actions".
      • 

    • Deferred transactions should be p
ropagated serially. For more information about guidelines for scheduled links, see Oracle Database Advanced Replication.
      • 

    • The replicated procedure must be packaged and the package cannot contain any functions. Standalone deferred procedur
es and standalone or packaged deferred functions are not currently supported.
      • 

    • Th
e deferred procedures must reference only locally owned data.
      • 

    • The procedures sho
uld not use locally generated fields, values, or environmentally dependent SQL functions. For example, the procedure should not call
SYSDATE.
      • 

    • Your data ownership should be statically partitioned. That
 is, ownership of a row should not change between sites.
      • 

    • If you have multiple ma
ster groups at a master site, and one or more master groups are quiesced, then you cannot perform procedural replication on any maste
r group at the master site. This restriction is enforced because a procedure in one master group may update objects in another master
 group. You can only perform procedural replication when all of the master groups on a master site are replicating data normally (tha
t is, when none of the master groups is quiesced).
    For example, if you have a procedure
 named sal_raise in master group A on master site db1, then you cannot run the sal_raise proce
dure if master group B on master site db1 is quiesced, even if master group A is replicating normally.
      

    • When using procedural replication, a procedure call is only propagated to master
replication sites. The procedure call is not propagated to materialized view sites. However, procedural replication can be initiated
at a materialized view site. In this case, the procedure call is propagated to all of the master sites in the replication environment
, but the procedure call is not propagated to any other materialized view sites. Other materialized view sites must pull changes made
 at the master site by performing a materialized view refresh.
    For example, suppose a r
eplication environment includes two master sites named msite1 and msite2 and two materialized view sites na
med mview1 and mview2. If procedural replication is initiated at mview1, then the procedure is
 run at mview1 and the procedure call is propagated to the two master sites, msite1 and msite2
, where the procedure is also run. However, the procedure call is not propagated to mview2. Therefore, during the next r
efresh, mview2 pulls down all of the changes made by the procedure at its master site.
    User-Defined Types and Procedural Replication<
/font>
     

    When using procedural replication, the user-defined types and the obje
cts referenced in the procedure must meet the following conditions:
    

      

    • 
For an object type, all replication sites must agree about the order of attributes in the object type. You establish the attribut
e order when you create the object type. Consider the following object type: 

      CREATE TYPE cust_a
ddress_typ AS OBJECT
     (street_address     VARCHAR2(40), 
      postal_code        VARCHAR
2(10), 
      city               VARCHAR2(30), 
      state_province     VARCHAR2(10), 
      country_id         CHAR(2));
/


      



      At all replication sites, street_address must be the first attribute, postal_code must be the second at
tribute, city must be the third attribute, and so on.
      • 

    • For an Or
acle object, all replication sites must have the same object identifier (OID), schema owner, and type name for each replicated object
 type. 

      

      You can meet these conditions by always using distributed schema management to cre
ate or modify any replicated object, including object types, tables with column objects, and object tables. If you do not use distrib
uted schema management to create and modify object types, then replication errors may result.
      
 

      

    


     View
 DBMS_REPCAT Procedure
 See for Parameter Information
    


    


    DBA_REPGRO
UP

    
    		


    COMMENT_ON_REPGROUP(
 gname            IN VARCHAR2,
 comment          IN VARCHAR2)

    
    		


    "COMMENT_ON_REPGROUP Procedure".
    

    


    DBA_REPOBJECT

    
    		


    COMMENT_ON_REPOBJECT(
 sname            IN VARCHAR2, 
 oname            IN VARCH
AR2, 
 type             IN VARCHAR2, 
 comment          IN VARCHAR2)

    
    		


    "COMMENT_ON_REPOBJECT Procedure".
    

    


    DBA_RE
PSITES

    
    		


    COMMENT_ON_REPSITES(
 gname
   IN VARCHAR2, 
 master           IN VARCHAR, 
 comment          IN VARCHAR2)

    
    		

    

    


    <
/a>DBA_REPCOLUMN_GROUP

    
    		


    COMMENT_ON_COLUMN_GROUP(
 sname            IN VARCHAR2, 
 oname            IN VARCHAR2, 
 column_group     IN VAR
CHAR2, 
 comment          IN VARCHAR2)

    
    		


    "COMMENT_ON_COLUMN_GROUP Procedure".
    

    


    DBA_REPPRIORITY_GROUP

    
    		


    COMMENT_ON_PRIORITY_GROUP(
 gname            IN VARCHAR2, 
 pgroup           IN VARCHAR2)
 comment          IN VARCHAR2)


    
    		


    "COMMENT_ON_PRIORITY_GROUP Procedures".
    

    


    DBA_REPPRIORITY_GROUP
(site priority group)

    
    		


    COMMENT_ON_SITE_PR
IORITY(
 gname            IN VARCHAR2, 
 name             IN VARCHAR2, 

comment          IN VARCHAR2)

    
    		


    "COMME
NT_ON_PRIORITY_GROUP Procedures".
    

    


COMMENT_ON_UNIQUE_RESOLUTION(
 sname            IN VARCHAR2,
 oname            IN VARCHAR2,
 constraint_name  IN VARCHAR2,
 sequence_no      IN NUMBER,

 comment          IN VARCHAR2)




    The parameters
 for the COMMENT_ON_UNIQUE_RESOLUTION procedures are described in "COMMENT_ON_conflicttype_
RESOLUTION Procedure".
    

    


    DBA_REPRESOLUTION
(update conflicts)

    
    		


    COMMENT_ON_UPDATE_RESOLUTION(
 sname            IN VARCHAR2,
 oname
    IN VARCHAR2,
 column_group     IN VARCHAR2,
 sequence_no      IN NUMBER, 
 comment          IN VARCHAR2)

    
    		


    The parameters for the C
OMMENT_ON_UNIQUE_RESOLUTION procedures are described in "COMMENT_ON_conflicttype_RESOLUTION Proce
dure".
    

    


    DBA_REPRESOLUTION
(delete conflicts)

    
    		


    COMMENT_ON_DELETE_RESOLUTION(
 sname            IN VARCHAR2,
 oname            IN VARCHAR2,

 sequence_no      IN NUMBER, 
 comment          IN VARCHAR2)

    
    		


    The parameters for the COMMENT_ON_UNIQUE_RESOLUTION procedures are described in "COMMENT_ON_conflicttype_RESOLUTION Procedure".
    
 


 

 

 


 


 

 


 


 

 



    

    
See Also:  

    Oracle Database Advanced Replication for more information
 about type agreement at replication sites

    • Serializing Transactions

    < /a>

    Serial execution ensures that your data remains consistent. The replication facility propagates and executes repli cated transactions one at a time. For example, assume that you have two procedures, A and B, that perform updates on local data. Now assume that you perform the following actions, in order:
    Step 1 Execute A and B locally.
    S tep 2 Queue requests to execute other replicas of A and B on other nodes.
    Step 3 Commit.

    The replicas of A and B o n the other nodes are executed completely serially, in the same order that they were committed at the originating site. If A and B execute concu rrently at the originating site, however, then they may produce different results locally than they do remotely. Executing A and B se rially at the originating site ensures that all sites have identical results. Propagating the transaction serially ensures that A and B are executing in serial order at the target site in all cases.

    Alternatively, you could wr ite the procedures carefully, to ensure serialization. For example, you could use SELECT... FOR UPDAT E for queries to ensure serialization at the originating site and at the target site if you are using parallel propagation.

    Generating Supp ort for Replicated Procedures

    You must disable row-level replication s upport at the start of your procedure, and then re-enable support at the end. This operation ensures that any updates that occur as a result of executing the procedure are not propagated to other sites. Row-level replication is enabled and disabled by calling the fo llowing procedures, respectively:

    When you generate replic ation support for your replicated package, Oracle creates a wrapper package in the schema of the replication propagator.

    Note:

    Unregistering the current propagator drops all existing generated wrappers in the propagator's schema. Replication support for wrapped stored procedures must be regenerated after you register a new propagator.

    The wrapper package has the same name as the original package, but its name is prefixed wi th the string you supply when you generate replication support for the procedure. If you do not supply a prefix, then Oracle uses the default prefix, defer_. The wrapper procedure has the same parameters as the original, along with two additional parame ters: call_local and call_remote. These two CHAR parameters determine where the procedure is e xecuted. When call_local is 'Y', the procedure is executed locally. When call_remote is 'Y', the procedure will ultimately be executed at all other master sites in the replication environment.

    < /a>

    The remote procedures are called directly if you are propagating changes synchronously, or calls to these procedur es are added to the deferred transaction queue if you are propagating changes asynchronously. By default, call_local is 'N', and call_remote is 'Y'.

    Oracle generates replicat ion support for a package in two phases. The first phase creates the package specification at all sites. Phase two generates the pack age body at all sites. These two phases are necessary to support synchronous replication.

    For example, suppose you create the package emp_mgmt containing the procedure new_dept, which takes one argume nt, email. To replicate this package to all master sites in your system, you can use the Replication Management tool to add the package to a master group and then generate replication support for the object. After completing these steps, an application can call procedure in the replicated package as follows:

    BEGIN
defer_emp_
mgmt.new_dept( email        => 'jones',
                         call_local   => 'Y',

                        call_remote  => 'Y');
END;
/
    See Also:

    The Replicat ion Management tool's online help for more information about managing master groups and replicated objects using the Replication Mana gement tool

    As shown in Figure 7-4< /a>, the logic of the wrapper procedure ensures that the procedure is called at the local site and subsequently at all remote sites. The logic of the wrapper procedure also ensures that when the replicated procedure is called at the remote sites, call_remote is false, ensuring that the procedure is not further propagated.

    If you ar e operating in a mixed replication environment with static partitioning of data ownership (that is, if you are not preventing row-lev el replication), then Advanced Replication preserves the order of operations at the remote node, because both row-level and procedura l replication use the same asynchronous queue.

    Figure 7-4 Asynchronous Procedural Replication

    Text description of rep ma008.gif follows

    Text description of the illustration repma008.gif