Oracle7 Server Distributed Systems Manual, Vol. 2

Library

Product

Contents

Index

Multi-Master Replication

• Replication Support for Master Sites
• Before Creating a Replicated Environment
• Creating a Multi-Master Replicated Environment
• Creating a Replicated Object
• Adding a Master Site
• Generating Replication Support
• Synchronously Propagating DML Changes Among Master Sites
• Asynchronously Propagating DML Changes Among Master Sites
• Selecting a Propagation Method
• Quiescing the Replicated Environment
• Altering a Replicated Object
• Listing Information about a Replicated Environment

• database links and security in a replicated environment

• creating the master definition site

• creating replicated objects

• generating replication support

• creating additional master sites

• propagating data-level changes between master sites

• propagating schema-level changes between master sites

• quiescing a replicated environment

• altering a replicated object

• removing a master site

Additional Information: The symmetric replication facility is available to users of the advanced replication option only. The advanced replication option is separately installable, and may require additional system resources. Consult your operating system-specific Oracle installation instructions for more information.

Replication Support for Master Sites

Replication Support for Tables

The symmetric replication facility supports the following data types: NUMBER,DATE, VARCHAR2, CHAR, ROWID, and RAW. Your table can include columns with other data types (e.g. LONG and LONG RAW, but these columns will not be populated by the symmetric replication facility.

Whenever you create, alter, or drop a table using the procedures provided in the DBMS_REPCAT package, that change is ultimately applied at all other master sites in the replicated environment, by the replication facility. Propagation of these changes between master sites is explained in detail starting .

Replication Support for Non-table Objects

For example, suppose that you choose to replace a stored procedure at a master site. If that procedure is used to update a table that is replicated using row-level replication, any changes made through the procedure will continue to be properly forwarded to the other replicated sites.

If you are using procedural replication, however, you must only update this procedure using the method for altering a replicated object described . This ensures that the proper replication support for this procedure is generated at all replication sites.

Designing Your Replicated Environment

• You should first determine which objects you want to replicate. Objects must be part of a replicated object group. A replicated object group can include objects from multiple schemas, but each object can only belong to one object group.

• Next, you should determine where you want these objects replicated. You also need to determine which sites will be master sites, which must contain all replicated objects, and which will be snapshot sites, which can contain a subset of the replicated objects. A typical replicated environment may consist of two master sites each supporting several snapshot sites.

• You must also determine how you want Oracle to propagate your changes among the replication sites -- synchronously or asynchronously. If you choose to propagate your changes asynchronously, you must also determine how frequently you want to propagate your changes.

• Finally, you must choose to either partition ownership of your data and avoid update conflicts, or you must select a method of resolving these conflicts, should they occur. If all of your master and snapshot sites are propagating their changes synchronously, you need not worry about conflicts.

Before Creating a Replicated Environment

There are three categories of users in a replicated environment:

replication administrators

Users who are responsible for configuring and maintaining a replicated environment.

symmetric replication facility

Certain replication activities are run as SYS and must execute on remote nodes.

end users

Users querying and updating replicated objects.

Replication Administrators

Note: The packages DBMS_REPCAT_ADMIN and DBMS_REPCAT_AUTH are owned by SYS. Access to these packages should not be granted widely.

Granting Privileges

DBMS_REPCAT_ADMIN.GRANT_ADMIN_REPGROUP(userid => 'acctng');

In this example, the ACCTNG user now has the privileges needed to administer any replicated objects in the ACCTNG schema at the current site. The ACCTNG user must already exist, before you call GRANT_ADMIN_REPGROUP. You can now GRANT or REVOKE privileges for this user as needed. This procedure is most useful if your replicated object group does not span schemas.

Additional Information: The parameter for the GRANT_ADMIN_REPGROUP procedure is described in Table 12 - 177, and the exceptions are listed in Table 12 - 178.

To create a replication administrator for all replicated groups at your current site (also known as a global replication administrator), complete the following steps:

1. Create the replication administrator account, as shown in the following example:

	CREATE USER repadmin IDENTIFIED BY repadminpassword;

2. Grant the replication administrator the necessary privileges to administer all replicated objects at the current site by calling DBMS_REPCAT_ADMIN.GRANT_ADMIN_ANY_REPGROUP as shown in the following example:

DBMS_REPCAT_ADMIN.GRANT_ADMIN_ANY_REPGROUP(userid => 'repadmin');

3. You can now GRANT additional privileges to the replication administrator as necessary.

Revoking Privileges Call the REVOKE_ADMIN_REPGROUP or REVOKE_ADMIN_ANY_REPGROUP procedures in the DBMS_REPCAT_ADMIN package to revoke the privileges and roles granted above.

Attention: Calling the REVOKE_privs procedures will revoke all privileges granted with the associated GRANT_privs procedures, even if identical privileges were granted through additional channels.

Additional Information: The parameter for the REVOKE_ADMIN_REPGROUP procedure is described in Table 12 - 181, and the exceptions are listed in Table 12 - 182. The parameter for the REVOKE_ADMIN_ANY_REPGROUP procedure is described in Table 12 - 179, and the exceptions are listed in Table 12 - 180.

Creating Database Links

• Connect to the site as the replication administrator.

• Ensure that the initialization parameter GLOBAL_NAMES is set to TRUE.

• Create a private database link to every other master site in the replicated environment. That is, if the schema is replicated on N masters, you must create N-1 links. Each database link must include the fully qualified database name of the remote master site and the username and password of the replication administrator at that site.

CONNECT repadmin/secret

CREATE PUBLIC DATABASE LINK inventory.widgetek.com 
    USING 'inventory.widgetek.com';

CREATE DATABASE LINK inventory.widgetek.com CONNECT TO repadmin
    IDENTIFIED BY secret;

CREATE PUBLIC DATABASE LINK hq.widgetek.com
    USING 'hq.widgetek.com';

CREATE DATABASE LINK hq.widgetek.com CONNECT TO repadmin
    IDENTIFIED BY secret;

Using a public link to provide the database specification of the remote database eliminates the need to include a USING clause in each of your private database links. To determine the global database name of a remote database, you can query the GLOBAL_NAME data dictionary view.

Replication Administration Usage Notes

All calls to procedures in the DBMS_REPCAT package for a given replicated object group should be performed serially. That is, only one person should be adding to, or altering a replicated environment at a time, or your environment may become out of sync. This includes activities that you may have set up to be performed by a background process.

To be safe, you should disable any DBMS_REPCAT.* jobs in the local job queue whenever you are administering a replicated environment.

You can use the USER_JOBS view to determine which jobs in the queue are associated with a given replicated object group. Then call DBMS_JOB.BROKEN to disable these jobs temporarily.

After completing your administrative activities, you can call this procedure again to re-enable the jobs.

If your object group spans schemas you will probably find it easiest to designate one person as the replication administrator for all replicated objects. If your object groups do not span schemas, you can designate one person as the replication administrator for each schema containing replicated objects. This person would be responsible for all calls made using the DBMS_REPCAT package to administer an object group at all master sites in the replicated environment. Different users can administer different schemas.

Because the DBMS_REPCAT package does not provide any additional access control, EXECUTE privileges on this package should not be granted widely.

A user granted EXECUTE privileges on DBMS_REPCAT does not gain any privileges on non-replication catalog views, but can modify the replication catalog views and disrupt a replicated environment. If you desire more flexibility, you should create a cover package for DBMS_REPCAT that provides the appropriate level of access control, and then grant wider access only on this cover package.

Note that granting EXECUTE on DBMS_REPCAT to a user does not give that user any greater privileges on the replicated objects. You must call the appropriate DBMS_REPCAT_ADMIN procedure to ensure that your replication administrator has the necessary privileges on all objects at all sites to perform the necessary DDL and DML operations that are supported by the procedures in DBMS_REPCAT.

Symmetric Replication Facility

Granting Privileges

1. Create the surrogate replication administrator account, as shown in the following example:

	CREATE USER surrogate IDENTIFIED BY surrogate_password;

You will probably find it easiest to create the same user as the surrogate replication administrator at all master sites in your replicated environment.

2. Grant the surrogate replication administrator the necessary privileges to act on behalf of the symmetric replication facility for all replicated object groups at the current site by calling DBMS_REPCAT_AUTH.GRANT_SURROGATE_REPCAT, as shown in the following example:

	DBMS_REPCAT_AUTH.GRANT_SURROGATE_REPCAT(userid => 'surrogate');

3. You can now GRANT additional privileges to the surrogate replication administrator as necessary.

Revoking Privileges Call the REVOKE_SURROGATE_REPCAT procedure in the DBMS_REPCAT_AUTH package to revoke the privileges and roles granted above.

Additional Information: The parameter for the REVOKE_SURROGATE_REPCAT procedure is described in Table 12 - 185, and the exceptions are listed in Table 12 - 186.

Creating Database Links

To create the necessary links for the symmetric replication facility, you must complete the following steps at each master site:

• Connect to the local site as SYSOPER or SYSDBA.

• Create a private database link to every other remote master site in the replicated environment. That is, if the schema is replicated to N masters, you must create N-1 links. Each database link must include the fully qualified database name of the remote master site and the username and password of the surrogate replication administrator at that site. For example, to create a link to the HQ master site from the previous example, you could issue the following statement:

	CREATE DATABASE LINK hq.widgetek.com CONNECT TO surrogate
	    IDENTIFIED BY surrogate_password USING 'hq.widgetek.com';

End Users

Synchronous Propagation

To call the remote procedure, Oracle must connect to the remote database using a database link. Because the generated trigger package at the local site has the same owner as the owner of the table being updated, Oracle first looks for a private database link for the table owner. If no such link exists, Oracle next looks for a private database link for the connected user. If Oracle does not find a private database link, it next looks for a public database link to the remote site.

The privilege domain at the remote site is determined by the connection at the remote site. Oracle connects to the remote site using the username and password specified in the CONNECT TO clause of the database link, if one was given; otherwise, Oracle attempts the connection using the username and password for the local user making the update. The user that Oracle ultimately connects as must have the EXECUTE privilege on the generated procedures for the object being updated for the update to complete successfully.

Asynchronous Propagation

• the value of the EXECUTE_AS_USER argument to the DBMS_DEFER_SYS.EXECUTE procedure

• the available database links to the remote site

To push the deferred transaction to the remote location, Oracle must first establish a connection to the remote site. To determine which link to use, Oracle checks the EXECUTE_AS_USER setting. If this parameter is FALSE, the default, Oracle looks for a database link for the user who originated the transaction.

If this parameter is TRUE, Oracle looks for a database link for the connected session user. (Typically, the replication administrator pushes the deferred transaction queue.)

If the available link does not include a username and password, Oracle uses the username and password associated with the current session (regardless of the setting of EXECUTE_AS_USER). If the current session is a background process (such as when you schedule periodic execution of the deferred transaction queue), there is no available username and password, and the connection fails.

Once a connection is established, the privilege domain of the transaction at the remote site is determined by the privilege domain of the connection. For example, if the connection is made using the database link created for the replication administrator, the privilege domain of the replication administrator at the remote site determines whether the transaction can be successfully executed at the remote site. To execute the transaction successfully, this user (the remote replication administrator) must have the EXECUTE privilege on the generated procedures for the object being updated.

If all transactions will be pushed to the remote site as the replication administrator (EXECUTE_AS_USER = TRUE), no additional database links are necessary. To ensure that the replication administrator at the remote site has the necessary privileges to apply these transactions, you must either GRANT the replication administrator at each site the EXECUTE ANY PROCEDURE system privilege, or GRANT the replication administrator the EXECUTE object privilege on each of the generated replication procedures.

Note: When you replicate an object, its associated privileges are not replicated. To replicate the GRANT command to each site, you can use the DBMS_REPCAT.EXECUTE_DDL procedure described .

If transactions will be pushed to the remote site as the originating user (EXECUTE_AS_USER = FALSE), each user should have a private database link to each site participating in the replicated environment. This link should include a username and password. The user specified in the CONNECT TO clause must have the necessary EXECUTE privileges on the generated replication procedures.

For example, if user SCOTT at the New York site has UPDATE privileges on the ACCT.EMP and ACCT.DEPT tables, and you create the following private link while connected as SCOTT

CREATE DATABASE LINK hq.widgetek.com 
    CONNECT TO scott IDENTIFIED BY tiger;

then user SCOTT at the remote HQ site must have EXECUTE privileges on the ACCT.EMP$RP and ACCT.DEPT$RP packages. Because the generated packages are considered to have the same owner as the owner of the table, the table owner automatically has the ability to execute the packages. All other users must either be granted the EXECUTE ANY PROCEDURE privilege at each site, or be granted EXECUTE on each generated package at each remote site for every table they may update.

Creating a Multi-Master Replicated Environment

• First, you must select which of the sites in your new multi-master replicated environment will be the master definition site.

This site is used to create all other master sites for a given replicated object group. When selecting a master definition site, you should choose the site that is most likely to be available at all times. You can change master definition sites if necessary.

• Next, make sure that you have created all of the necessary links between the sites that you have selected to participate in your replicated environment. You must also have granted the necessary privileges to the replication administrator and surrogate replication administrator. Only the replication administrator can create a replicated environment.

• To create your replicated environment, you must begin by creating an empty master replicated object group at the site you have selected to be the master definition site. Create this replicated object group by calling DBMS_REPCAT.CREATE_MASTER_REPGROUP.

• For each object, such as a table or procedure, that you want to add to the replicated object group, you must perform the following steps:

• First, add the object to the replicated object group by calling the CREATE_MASTER_REPOBJECT procedure.

• If the object is a table that requires conflict resolution routines, call the appropriate ADD_conflicttype_RESOLUTION procedures. These procedure are described in Chapter 6.

• For each object that requires replication support, call GENERATE_REPLICATION_SUPPORT to create the necessary triggers, packages, and procedures. The owner of the replicated object must have the EXECUTE privilege on the DBMS_DEFER package in order for Oracle to create these objects successfully.

You must either call GENERATE_REPLICATION_SUPPORT for each object before adding any master sites and again after adding any master sites, or you must ensure that no updates are made to the tables at any of the master sites until after you have added all master sites and then call GENERATE_REPLICATION_SUPPORT.

• For each additional master site that you want to include in your replicated environment, call ADD_MASTER_DATABASE.

• For sites in your replication environment that are propagating their changes asynchronously, you must schedule this propagation by calling DBMS_DEFER_SYS.SCHEDULE_EXECUTION.

• Because all activity at the master definition site was suspended when you called CREATE_MASTER_REPGROUP, you must now call RESUME_MASTER_ACTIVITY to restore normal activity.

Creating a Replicated Object Group

To create a new, empty, quiesced master replicated object group, call DBMS_REPCAT.CREATE_MASTER_REPGROUP, as shown in the following example:

DBMS_REPCAT.CREATE_MASTER_REPGROUP(
    gname          => 'acct',
    group_comment => 'created by '||JSMITH||' on '||1-24-96, 
    master_comment => 'created by '||JSMITH||' on '||1-24-96);

When you create a new replicated object group, you may choose to add a comment to the RepGroup or RepSite views. In this example, the comment indicates when the replicated object group was first created and by whom.

Additional Information: The parameters for the CREATE_MASTER_REPGROUP procedure are described in Table 12 - 100, and the exceptions are listed in Table 12 - 101.

Creating a Replicated Object

DBMS_REPCAT.CREATE_MASTER_REPOBJECT(
    sname              => 'acct_rec', 
    oname              => 'emp', 
    type               => 'table', 
    use_existing_object => TRUE, 
    ddl_text           => 'CREATE TABLE acct_rec.emp AS . . .', 
    comment            => 'created by . . .',
    retry              => FALSE,
    copy_rows          => TRUE,
    gname              => 'acct');

This example adds the EMP table to the replicated object group ACCT. The comment is added to the RepObject view. The types of objects that you can replicate are: tables, views, indexes, synonyms, triggers, procedures, functions, packages, and package bodies.

Note: If a table has a foreign key constraint that references columns in the table, you may need to precreate and populate the table at the new master site.

Attention: Note that the symmetric replication facility does not support replication of clustered tables.

To avoid name conflicts, the name of the replicated table should not exceed 27 bytes in length.

You must call this procedure at the master definition site. Assuming that you have no other master sites yet, the object is created at the master definition site, if it does not already exist, using the DDL that you provided.

If you pass a SQL statement as the DDL text, it must not include a trailing semicolon. If you supply a PL/SQL package, package body, function, or procedure, it must include the trailing semicolon.

Before calling this procedure the replicated object group must be quiesced. If you are creating a new replicated object group, the system is automatically quiesced when you call CREATE_MASTER_REPGROUP; otherwise, you must call SUSPEND_MASTER_ACTIVITY.

You can also use this procedure to add an object to an existing replicated environment, which may include additional master sites. If your environment does include one or more additional master sites the object is asynchronously added to the replicated object group at those sites as described .

Replicating the Object at Each Master Site

If an object does not already exist at a master site, this procedure creates the object for you. For tables, you can choose to either populate the table with the data from the master definition site, or to populate the table yourself. If you choose to populate the table yourself, you are responsible for the consistency of the table at each master site.

If a table already exists at a master site, you can choose to

• use the existing table but allow Oracle to ensure consistency by updating the existing table with values from the master definition site, if any discrepancies are found

• use the existing table and be responsible for consistency yourself

If any non-table object already exists at a master site, Oracle raises a duplicateobject exception if the object at the master site does not have the same SQL definition as the object at the master definition site. This exception is stored in the RepCatLog view.

Precreating an object can be useful if you find it faster to copy the files yourself (for example, by exporting the tables of a quiesced master site and importing them into a potential master site), instead of having the replication facility copy them for you. If you do this, you should take great care to ensure that the contents of the objects at both sites are identical. You also need to ensure that no changes are replicated to that site until you have imported the data.

Table 4 - 1 displays the appropriate settings for these parameters.

Object Already Exists?	COPY_ROWS	USE_EXISTING_OBJECT	Result
yes	TRUE	TRUE	duplicateobject message if objects do not match. For tables, use data from master definition site.
yes	FALSE	TRUE	duplicateobject message if objects do not match. For tables, Admin must ensure contents are identical.
yes	TRUE/FALSE	FALSE	duplicateobject message
no	TRUE	TRUE/FALSE	Object is created. Tables populated using data from master definition site.
no	FALSE	TRUE/FALSE	Object is created. Admin must populate tables and ensure consistency of tables at all sites.

Table 4 - 1. Object Creation at Master Sites

Additional Information: The parameters for the CREATE_MASTER_REPOBJECT procedure are shown in Table 12 - 102, and the exceptions are listed in Table 12 - 103. See also the discussion of offline instantiation and page 5 - 10.

Using an Alternate Primary Key

DBMS_REPCAT.SET_COLUMNS(
    sname       => 'acct_rec', 
    oname       => 'emp', 
    column_list => 'emp_no,ename');

In this example, both the employee number and the employee name are used to determine matching rows when pushing changes between replicated copies of the EMP table.

Because this key is used to determine matching rows at different sites, the columns that you specify in the COLUMN_LIST must result in a unique identifier for the row. The SET_COLUMNS procedure does not enforce uniqueness. This key is used as a substitute for the primary key for comparing replicated rows only, and is not a general substitute for defining a primary key constraint on a table.

Additional Information: The parameters for the SET_COLUMNS procedure are described in Table 12 - 167, and the exceptions are listed in Table 12 - 168.

Adding a Master Site

DBMS_REPCAT.ADD_MASTER_DATABASE(
    gname                => 'acct', 
    master               => 'acct_ny.ny.com', 
    use_existing_objects => TRUE, 
    copy_rows            => TRUE, 
    comment              => 'master site added by `||user||
                            ' on `||sysdate,
    propagation_mode     => 'asynchronous');

In this example, the ACCT_NY database is added as a new master replication site for the ACCT replicated object group. This site will propagate changes to all other existing sites asynchronously, and all existing sites will asynchronously propagate changes to this site. For information on selecting a propagation mode, refer to page 4 - 33.

You must call ADD_MASTER_DATABASE at the master definition site. The replication catalog views at the new master site are updated with the information necessary to create the replicated object group. The replicated object group at the new master site is populated asynchronously as described .

The replicated objects are created in the replicated object group as described in "Replicating the Object at Each Master Site" .

Note: Oracle attempts to create the objects in dependency order. If you have circular dependencies between objects, you may need to precreate and populate these objects at the new master site in order for this procedure to complete successfully.

Note: If a table has a foreign key constraint that references columns in the table, you may need to precreate and populate the table at the new master site.

Additional Information: The parameters for the ADD_MASTER_DATABASE procedure are described in Table 12 - 64, and the exceptions are listed in Table 12 - 65.

Adding a Site to an Existing Replicated Environment

• Make sure that you have the necessary links and privileges for the new site as described . You must create links from this site to all existing sites, and from all existing sites to this site.

• Suspend all replication activity for the replicated object group by calling DBMS_REPCAT.SUSPEND_MASTER_ACTIVITY at the master definition site, as described .

• Add the new master site by calling ADD_MASTER_DATABASE as described above. When you add a site to an existing replicated environment, Oracle recreates all of the generated triggers and their associated packages at all existing master sites. This is necessary to ensure that DML changes at these existing sites are propagated to the new site using the appropriate method.

• If your new master database site will be propagating changes asynchronously, you must also schedule propagation of changes between the new master site and existing master sites, as described . Additionally, if any existing sites will be asynchronously propagating changes to this site, you must be certain that you have scheduled execution of the deferred transaction queue at those sites as well.

• After creating a new master site you must resume replication activity by calling DBMS_REPCAT.RESUME_MASTER_ACTIVITY at the master definition site.

Adding a Site to an Existing Replicated Environment Using Offline Instantiation

Offline instantiation requires only that your existing master sites be quiesced long enough to do an export of the database objects to tape from the master site being used as the source. You can then unquiesce the sites, transport the tape to the new site, import the export file, then bring the new site online.

The following are the steps necessary to instantiate a new site:

• Make sure that you have the necessary links and privileges for the new site as described . You must create links from this site to all existing sites, and from all existing sites to this site.

• Suspend all replication activity for the replicated object group by calling DBMS_REPCAT.SUSPEND_MASTER_ACTIVITY at the master definition site, as described .

• From the master definition site, call the procedure DBMS_OFFLINE_OG.BEGIN_INSTANTIATION (gname, new_site). This procedure will add the specified new_site to the replicated object group, gname.

• Export the user-defined tables for each schema making up the object group, gname. This can be done from any master site. Note that, if your object group consists of more than one schema, you will need to export the user-defined tables for each schema. For more information about using the Export utility, see page 7 - 12 and Oracle7 Server Utilities.

• From the master definition site, call the procedure DBMS_OFFLINE_OG.RESUME_SUBSET_OF_MASTERS (gname, new_site). At this point, normal, non-administrative activities can resume at the existing master sites. The new site, however, will remain quiesced.

Therefore, propagation of job queues to the new site is disabled, but the jobs will remain in the queue for later propagation when the new site is unquiesced.

• At this point, the tape holding the export file can be transferred to the new site.

• After the tape is available at the new site, from the new master site, call DBMS_OFFLINE_OG.BEGIN_LOAD (gname, new_site). This procedure disables triggers at the new site so that no updates can be made while you import your data. The procedure also disables propagation of the job queue from the new site to the master sites.

• Use the Import utility to import the data from tape. For more information about using the Import utility, see page 7 - 12 and Oracle7 Server Utilities.

• When the import is completed, reenable the new site's triggers and job queue propagation by calling DBMS_OFFLINE_OG.END_LOAD (gname, new_site).

• Return to the master definition site and call the procedure DBMS_OFFLINE_OG.END_INSTANTIATION (gname, new_site). This procedure resumes propagation to the new site.

• If you will be creating multiple new sites, you must perform the above steps for each new site. New sites cannot be instantiated as a group.

• Be careful to call the listed procedures from the appropriate site. Failure to do so could cause unexpected and unwanted results.

• If you need to reenable queue propagation manually, use the procedure DBMS_DEFER_SYS.SET_DISABLED(). See page 12 - 20 for more information.

• The propagation method used for offline instantiation is asynchronous.

Generating Replication Support

DBMS_REPCAT.GENERATE_REPLICATION_SUPPORT(
    sname            => 'acct_rec', 
    oname            => 'emp', 
    type             => 'table',
    distributed      => TRUE,
    gen_rep2_trigger => FALSE
    gen_obj_owner    => 'REPADMIN');

You must call this procedure from the master definition site for each object in the replicated object group (tables, packages, package bodies, and procedures). If you are generating replication support for an object that is not owned by the replication administrator, the owner of the object must have the EXECUTE privilege on the DBMS_DEFER package.

Note: If your compatibility mode is set to 7.3.0 or greater, the DISTRIBUTED option must be set to TRUE. If the compatibility mode at any of your snapshot sites is earlier than 7.3.0, you must set GEN_REP2@_TRIGGER to TRUE and set compatibility mode of the master site to 7.3.0.0.

Because the generated triggers may include calls to the generated procedures (to support synchronous replication), Oracle generates replication support in two phases. When you call GENERATE_REPLICATION_SUPPORT, Oracle begins phase one by synchronously broadcasting the request to all sites to create the necessary generated packages. These packages are created asynchronously as described . For procedural replication, phase one generates the package specification.

Phase two does not begin until each site indicates to the master definition site that it has generated the packages necessary to support replication. Oracle then begins phase two by synchronously broadcasting the request to generate the necessary triggers and their associated packages at each site. Once again, these objects are created asynchronously as described . For procedural replication, phase two generates the package body.

Note: Oracle has been optimized to allow additional calls to GENERATE_REPLICATION_SUPPORT and to allow CREATE_MASTER_REPOBJECT to proceed after Oracle has broadcast the request to create the packages at each site.

It is not necessary to wait until all packages have actually been created at all of the sites to begin processing these types of requests. Any other procedures will not be executed until after GENERATE_REPLICATION_SUPPORT completes phase two.

There may be times when you only need to create the generated packages, such as when you change conflict resolution methods for an object, and other times when you only need to generate the triggers, such as when you change propagation methods for a site. In these situations, you can save time by using the DBMS_REPCAT procedures GENERATE_REPLICATION_PACKAGE (described ) and GENERATE_REPLICATION_TRIGGER (described ) as appropriate. Once again, Oracle can begin processing these requests before phase two of GENERATE_REPLICATION_SUPPORT begins.

Transaction Ownership

Row-Level Replication

Procedural Replication

All of the parameters to your replicated procedure must be IN parameters, and must be of type: NUMBER, DATE, VARCHAR2, CHAR, ROWID or RAW. When generating wrappers, you should call GENERATE_REPLICATION_SUPPORT immediately after calling CREATE_MASTER_REPOBJECT, ALTER_MASTER_PROPAGATION, or ALTER_MASTER_REPOBJECT.

If you have only modified the propagation method, you may prefer to call GENERATE_REPLICATION_TRIGGER. You may also call GENERATE_REPLICATION_TRIGGER instead of GENERATE_REPLICATION_SUPPORT after adding a new master site (assuming that you have already called GENERATE_REPLICATION_SUPPORT for each object).

If you are using procedural replication, you should generate support for your replicated packages. Refer to page 8 - 13, for information on how to design your replicated procedures to work with tables that also support row-level replication. If you do not generate replication support for your replicated packages, you assume responsibility for making sure that the transactions are properly applied at each replicated site.

Additional Information: The parameters for the GENERATE_REPLICATION_SUPPORT procedure are described in Table 12 - 145, and the exceptions are listed in Table 12 - 146.

Synchronously Propagating DML Changes Among Master Sites

Oracle ensures that all distributed transactions either commit or rollback in the event of a failure. See the discussion of distributed updates in Oracle7 Server Distributed Systems, Volume I for more information.

Restrictions

Figure 4 - 1. Propagating Changes using Synchronous Row-Level Replication

Warning: Because of the locking mechanism used by synchronous replication, deadlocks can occur. When you perform a synchronously replicated update, Oracle first locks the local row and then uses an AFTER ROW trigger to lock the remote row. These locks are released when the transaction commits at each site.

Destination of Synchronously Replicated Transactions

Privilege Domain at the Remote Site

Oracle uses the username and password supplied with this link to connect to the remote database. The privilege domain of this user at the remote site determines whether the remote generated procedure can be successfully executed to apply the transaction at the remote site. If no username and password is supplied with the link, Oracle uses the username and password associated with the local connected session; that is, the username and password for the user performing the local update.

If Oracle cannot find a link for the owner of the table, it looks for a public link. If Oracle cannot find a private database link, it will use a public database link, if one is available. Once again, if no username and password is supplied with the link, Oracle uses the username and password for the local connected session.

Suggestion: Including a username and password as part of the database link greatly simplifies the number of privileges that you need to grant to each user at each site. For example, if all of the objects in your replicated object group have the same owner, by creating a private database link for this user (with the username and password), you could simply grant this user EXECUTE ANY PROCEDURE at each site.

Conflict Detection

If the change is being propagated synchronously, an error is raised and a rollback will be required. If the change is propagated asynchronously, Oracle automatically detects these conflicts and either logs the conflict or, if you designate an appropriate resolution method, resolves the conflict. Conflict detection and conflict resolution are described in Chapter 6.

Asynchronously Propagating DML Changes Among Master Sites

When you apply your local change, these triggers are fired to build deferred calls to generated procedures at the remote master sites. Procedural replication, illustrated , uses procedure wrappers to build deferred transactions. The deferred transactions, however, are propagated in the same manner, whether you use procedural replication, row-level replication, or some combination of both.

Propagation of deferred transactions is controlled by job queue processes that you set up. You may choose to forward calls at frequent intervals, such as every few seconds, thus simulating event-based propagation.

Alternatively, you may initiate propagation at points in time when connectivity is available or communications costs are lowest, such as during evening hours. Queued transactions can be propagated to different destinations at different frequencies according to priority. If a remote system is unavailable, the deferred transactions targeted for that system remain in the local queue for later propagation.

The DBMS_DEFER_SYS package contains the procedures that you must use to forward changes from your current master to another master site in the same replicated environment. There are two different methods that you can use to propagate changes to a master site:

• You can automatically propagate the changes at a regular interval by calling the SCHEDULE_EXECUTION procedure once for each master site.

• You can manually push the changes made at a given master site by calling the EXECUTE procedure to forward any changes made since the last time changes were propagated from the given master site, either manually or automatically.

Restrictions

Figure 4 - 2. Applying Changes to an Updatable Snapshot

As shown in Figure 4 - 3, the arguments that you pass to the EXECUTE or SCHEDULE_EXECUTION procedures determine how the transactions are executed at the remote site. For scheduled executions, Oracle creates a job queue entry for you, and the settings of the job queue initialization parameters also affect the execution of the transactions.

Figure 4 - 3. Deferred Transactions

Destination of Deferred Transactions

Attention: EXECUTE and SCHEDULE_EXECUTION do not expand database link names. If you do not specify a fully qualified database link name, your transactions cannot be successfully propagated to the remote site.

Connecting to Remote Destinations

To ensure that the transactions are able to execute successfully at the remote site, you should create the necessary private database links for these users. These links should include a username and password, and the username specified should have the appropriate privileges on the generated procedures at the remote site.

Oracle first searches for the appropriate private database link. If none is found, it attempts to complete the connection using a public database link. If the database link includes a username and password, Oracle completes the connection using this information. If the link does not include a username and password, Oracle attempts to use the username and password from the current local session. This username and password combination must exist as a valid user at the remote site in order for the connection to succeed.

Note that if your database link does not include the username and password information, Oracle uses the username and password for the connected session, even if EXECUTE_AS_USER is FALSE. Because the user at the remote site determines the privilege domain in which the transaction is executed at the remote site, this may cause unexpected results.

If you are using a background process to push your deferred transaction queue (that is, if you called SCHEDULE_EXECUTION), your database link must include a username and password, or the connection will fail. This is because the current session, a background process, has no associated username and password.

Maintaining Data Integrity

Deferred transactions maintain transactional consistency and ordering. Multiple procedure calls submitted within a single local transaction are executed as a single transaction remotely. Deferred transactions are executed remotely in the same order as they are committed locally. Order of execution within a transaction is also preserved. The deferred transaction executes every remote procedure call at each system in the same order as it was executed within the local transaction. Each remote procedure call and each deferred transaction is executed exactly once on each remote system.

Conflict Detection

Scheduling Execution of the Deferred Transaction Queue

DBMS_DEFER_SYS.SCHEDULE_EXECUTION(
    dblink    => 'acct_ny.ny.com', 
    interval  => 'SYSDATE + 1', 
    next_date => SYSDATE);

In this example, once a day, any deferred remote procedure calls queued at your current master site will be forwarded and applied to the ACCT_NY master site.

The scheduling information that you supply to this procedure is recorded in the DefSchedule view described in Table 13 - 24. You can either schedule the changes to be propagated once at a specified time, or to be forwarded periodically using a given formula.

To remove a scheduled job from the DefSchedule view, call the procedure UNSCHEDULE_EXECUTION.

When you call SCHEDULE_EXECUTION, Oracle creates a job queue entry using the INTERVAL and NEXT_DATE information that you supply to schedule the call to EXECUTE. Every few seconds (based on the value of the JOB_QUEUE_INTERVAL parameter), a background process checks the job queue to determine if there are any pending jobs. If so, the job is executed. You can experience some delay if you do not have enough available job queue background processes (as determined by the value of the JOB_QUEUE_PROCESSES parameter) to execute the outstanding jobs.

To schedule asynchronous propagation among all of your master sites, you must call this procedure N - 1 times at each master site, where N is the total number of master sites in your replicated environment. For example, if you have three master sites, you would need to call this procedure twice at each master site. To change the interval at which changes are forwarded, simply call this procedure again with a new interval formula.

Additional Information: The parameters for the SCHEDULE_EXECUTION procedure are described in Table 12 - 25.

Forcing Execution of the Deferred Transaction Queue

DBMS_DEFER_SYS.EXECUTE(destination => 'acct_ny.ny.com');

In this example, any deferred transactions queued at your current site are immediately pushed to the ACCT_NY master site. In this example, these transactions will be pushed as the user who originally performed the update at the local site.

When you call EXECUTE, Oracle forwards and applies any outstanding remote procedure calls queued at your current location, to the given master site and records any errors or unresolvable update conflicts in the DefError view at the destination site.

This procedure can also be called automatically by an Oracle background process that is scheduled using the SCHEDULE_EXECUTION procedure. Anytime EXECUTE is called automatically, Oracle records the date information in the LAST_DATE field of the DefSchedule view.

Additional Information: The parameters for the EXECUTE procedure are shown in Table 12 - 23.

Removing a Master Site from the Deferred Execution List

DBMS_DEFER_SYS.UNSCHEDULE_EXECUTION( dblink => 'acct_ny.ny.com');

In this example, any deferred remote procedure calls queued at your current master or snapshot site will no longer be automatically forwarded to the master site referred to by the dblink ACCT_NY.NY.COM.

No changes will be pushed to this site from your current site until you either manually push them by calling EXECUTE or you reschedule automatic execution. You should call UNSCHEDULE_EXECUTION if you drop a master site from your replicated environment.

Additional Information: The parameters for the UNSCHEDULE_EXECUTION procedure are described in Table 12 - 28, and the exceptions are listed in Table 12 - 29.

Determining if Propagation of the Deferred Transaction Queue is Enabled

DBMS_DEFER_SYS.DISABLED ( dblink => 'acct_ny.ny.com');

The procedure will return TRUE, if propagation to the specified site is enabled, if not, it will return FALSE.

Selecting a Propagation Method

For example, suppose that you have three sites: A, B, and C. If you first create site A as the master definition site, and then add site B with a propagation_mode of SYNCHRONOUS, site A will send changes to site B synchronously and site B will send changes to site A synchronously. There is no need to schedule execution at either site, because neither site is creating deferred transactions. Now suppose that you create site C with a propagation_mode of ASYNCHRONOUS. The propagation modes are now as illustrated in Figure 4 - 4.

Figure 4 - 4. Selecting a Propagation Method

You must now schedule propagation of the deferred transaction queue from site A to site C, from site B to site C, and from site C to site A and to site B. But even more importantly, what if you created site A as the master definition site, then site C with a propagation_mode of ASYNCHRONOUS, then added site B with a propagation_mode of SYNCHRONOUS? Now the propagation modes would be as shown in Figure 4 - 5.

Figure 4 - 5. Ordering Considerations

Each time that you add a new site to your replicated environment, you must consider the effects of this addition on how changes are sent to and received from existing sites.

Altering Propagation

DBMS_REPCAT.ALTER_MASTER_PROPAGATION(
            gname            => 'acct',
            master           => 'site_a',
            dblink_list      => 'site_b, site_c',
            propagation_mode => 'synchronous');

Site A, as illustrated in Figure 4 - 5, would synchronously propagate its updates to sites B and C, after the triggers are regenerated. Site A would continue to receive changes from these sites asynchronously.

You must call this procedure from site A, and the replicated object group for which you are altering the propagation mode must be quiesced. After altering the propagation mode for an object group, be sure to make the appropriate GENERATE_REPLICATION_SUPPORT, DBMS_DEFER_SYS.SCHEDULE_EXECUTION, or DBMS_DEFER_SYS.UNSCHEDULE_EXECUTION call at the designated master site.

Additional Information: The parameters for the ALTER_MASTER_PROPAGATION procedure are described in Table Table 12 - 72, and the exceptions are listed in Table Table 12 - 73.

Generating Replication Triggers

DBMS_REPCAT.GENERATE_REPLICATION_TRIGGER( gname => 'acct');

Because no list of master sites was specified in this example, Oracle will regenerate the supporting triggers and their associated packages for the objects in the GNAME object group at all master sites. You must call this procedure from the master definition site for the given replicated object group. Oracle must successfully create the necessary triggers at the master definition site for this procedure to complete successfully. These objects are asynchronously created at the other master sites as described .

Attention: The GENERATE_REPLICATION_TRIGGER procedure is overloaded to allow you to generate support for a single object at all master sites or for an object group at a list of sites. Because the parameter types are the same for both calls, you may need to use named notation to indicate whether you are calling the procedure for a single object or for an object group.

Additional Information: The parameters for the GENERATE_REPLICATION_TRIGGER procedure are described in Table 12 - 147, and the exceptions are listed in Table 12 - 148.

Propagating DDL Changes Among Master Sites

Warning: Schema level changes should only be made using the procedures provided in the DBMS_REPCAT package. As a replication administrator, you must ensure that any local customizations made outside the scope of the replication facility do not interfere with replication activities.

1. When you make a DDL change using the procedures provided in the DBMS_REPCAT package, the request for a DDL change is synchronously broadcast to each master site, where the request is recorded in the RepCatLog view. The DDL statement is stored in a child table of the RepCatLog view at each site. At the master definition site, the RepCatLog view also indicates that the master definition site is awaiting a callback from each master site. If this synchronous broadcast fails, for any reason, you receive an error message, and the transaction is rolled back.

2. Whenever you synchronously broadcast an event (such as when you make a DDL change), Oracle automatically inserts a job into the job queue, if one does not already exist for the replicated schema. This job periodically executes the procedure DO_DEFERRED_REPCAT_ADMIN. Whenever you synchronously broadcast an event, Oracle attempts to start this job immediately in order to apply the replicated changes at each master site.

Assuming that Oracle does not encounter any errors, DO_DEFERRED_REPCAT_ADMIN will be run whenever a background process is available to execute the job.

The initialization parameter JOB_QUEUE_INTERVAL determines how often the background process wakes up. You can experience a delay if you do not have enough background processes available to execute the outstanding jobs. For more information on scheduling jobs, see Chapter 10.

If the initialization parameter JOB_QUEUE_PROCESSES is set to zero at a master site, you must manually connect to that site and invoke DO_DEFERRED_REPCAT_ADMIN to execute asynchronous requests at that site. Because this procedure may use dynamic SQL to perform DDL, you must never invoke it as a remote procedure call.

3. The next time that DO_DEFERRED_REPCAT_ADMIN is called at each master site, that site checks the RepCatLog view to see if there are any actions that need to be performed. When Oracle sees the request for a DDL change in the RepCatLog view, it applies the DDL statement in the child table to the current master site and updates any local views as appropriate. This event can occur asynchronously at each master site.

4. The success or failure of this action at each master site is noted in the RepCatLog view at each site. Ultimately, this information is propagated to the master definition site. If the event completed successfully at a master site, the callback for that site is removed from the RepCatLog view at the master definition site.

5. If the event completed successfully at all sites, all entries in the RepCatLog view at all sites, including the master definition site, will have been removed.

If an object requires automatically generated replication support, you must regenerate this support after you alter the object by calling the GENERATE_REPLICATION_SUPPORT procedure. Oracle then updates the generated triggers, packages and procedures as necessary to support replication of the altered object at all master sites.

Note that although the DDL must be successfully applied at the master definition site in order for these procedures to complete without error, this does not guarantee that the DDL is successfully applied at each master site. The RepCatLog view contains interim status and any asynchronous error messages generated by the request.

Any snapshot sites that are affected by a DDL change are updated the next time you perform a refresh of the snapshot site. While all master sites can communicate with one another, snapshot sites can only communicate with their associated master site. For more information on snapshot site refresh, see page 5 - 20.

If you must alter the shape of a snapshot as the result of a change to its master, you must drop and recreate the snapshot by calling the DBMS_REPCAT.DROP_SNAPSHOT_REPOBJECT procedure and then the DBMS_REPCAT.CREATE_SNAPSHOT_REPOBJECT procedure.

Quiescing the Replicated Environment

During a Quiesce

1. As shown in Figure 4 - 6, when you call SUSPEND_MASTER_ACTIVITY, Oracle synchronously broadcasts this request to each of the master sites, and this request is recorded in the RepCatLog view. Once the site begins to quiesce, its status in the Repgroup view changes from "normal" to "quiescing". This is true of the master definition site as well. At the master definition site, the RepCatLog view also indicates that the master definition site is awaiting a callback (a message indicating that the site has successfully quiesced) from each of the master sites. Each master site must report back to the master definition site when its status changes from "quiescing" to "quiesced". The master definition site cannot change its status to "quiesced" until all of the other master sites have reported back that they are now "quiesced".

2. Every few minutes a background process wakes up at each master site and checks the RepCatLog view to see if there are any actions that need to be performed. As shown in Figure 4 - 7, the quiesce request implies that Oracle must push any deferred transactions between the master sites. This is accomplished by forcing the NEXT_DATE value for the associated job to SYSDATE. The pushing of the deferred transaction queue can occur asynchronously at each master site.

3. As each master site successfully pushes its outstanding transactions, its status changes to "quiesced". As shown in Figure 4 - 8, ultimately, this status is sent to the master definition site, and the "await_callback" entry for that master site is removed from the RepCatLog view at the master definition site.

4. When the master definition site has successfully quiesced, and is satisfied that all of its associated master sites are also "quiesced", it changes its status to "quiesced", as shown in Figure 4 - 9.

Suspending Replication Activity

DBMS_REPCAST.SUSPEND_MASTER_ACTIVITY(
     gname            => 'acct'
     execute_as_user  => 'FALSE'));

This example suspends all replication activity for the ACCT object group. You can continue to query objects in this object group, but any modifications that cause replication triggers to fire will not succeed.

Attention: The current implementation of SUSPEND_MASTER_ACTIVITY quiesces all replicated object groups at each master site. No updates can occur to replicated data if the status of any replicated object group at that site is "quiescing" or "quiesced".

You must call this procedure from the master definition site. The replicated object group must be in normal operation when you call this procedure. It suspends all activity at all master replication sites that invoke calls to generated replication procedures. All pending queued procedure calls are processed. Local updates that do not fire replication triggers will still succeed. Each master remains in this state until you invoke the procedure RESUME_MASTER_ACTIVITY.

This procedure typically operates asynchronously at the master definition and the master sites. The RepCatLog view contains interim status.

You must call DBMS_REPCAT.SUSPEND_MASTER_ACTIVITY before calling any of the procedures listed below:

• DBMS_REPCAT.ADD_MASTER_DATABASE

• DBMS_REPCAT.ALTER_MASTER_REPOBJECT

• DBMS_REPCAT.ALTER_MASTER_PROPAGATION

• DBMS_REPCAT.GENERATE_REPLICATION_SUPPORT

• DBMS_REPCAT.GENERATE_REPLICATION_TRIGGER

• DBMS_REPCAT.GENERATE_REPLICATION_PACKAGE

• DBMS_REPCAT.CREATE_MASTER_REPOBJECT

At a minimum, you must wait until the status field in the RepGroup view is "quiescing" at the master definition site. If the presence of a non-empty deferred transaction queue at a site could cause a problem, you should wait until this status is "quiesced" before proceeding. If an enabled replication trigger at a site could cause a problem, you should wait until the status is "quiesced" before proceeding.

Additional Information: The parameter for the SUSPEND_MASTER_ACTIVITY procedure is described in Table 12 - 169, and the exceptions are listed in Table 12 - 170.

Transaction Ownership

Resuming Replication Activity

DBMS_REPCAT.RESUME_MASTER_ACTIVITY(gname => 'acct');

In this example, deferred remote procedure calls can once again be queued in master sites containing the ACCT replicated object group. You must call this procedure from the master definition site. When you call this procedure, the request is synchronously broadcast to all other master sites in the replicated environment, and the status for the replicate object group is updated to "normal" in the RepCatLog view.

Additional Information: The parameters for the RESUME_MASTER_ACTIVITY procedure are described in Table 12 - 165, and the exceptions are listed in Table 12 - 166.

Altering a Replicated Object

To ensure that the change is properly applied at all master sites, you should apply these changes in the following order:

1. If the object being altered is a master table for any updatable snapshots, you should replicate any snapshot changes to the master table as described .

2. Before altering a replicated object, you must quiesce the system by calling SUSPEND_MASTER_ACTIVITY at the master definition site. This pushes any queued transactions to the master sites in the replicated environment.

3. Alter the object by calling ALTER_MASTER_REPOBJECT at the master definition site. If the object requires replication support, you must regenerate any necessary triggers and packages by calling GENERATE_REPLICATION_SUPPORT.

4. Check the RepCatLog view at the master definition site to ensure that the object was successfully modified at each master site. The DDL changes to the object and any supporting objects are asynchronously applied at each master site as described .

5. If you altered a table used as the master for any snapshots, you must now drop and re-create those snapshots. Any other replicated objects at a snapshot site would be automatically re-created the next time you called REFRESH_SNAPSHOT_REPGROUP at the snapshot site.

6. You may now resume normal replication activity by calling RESUME_MASTER_ACTIVITY at the master definition site.

Supplying the DDL

DBMS_REPCAT.ALTER_MASTER_REPOBJECT(
    sname    => 'acct_rec', 
    oname    => 'emp', 
    type     => 'table', 
    ddl_text => 'ALTER TABLE acct_rec.emp ADD 
                (healthplan NUMBER(7,2) DEFAULT 100 NOT NULL)',
    comment  => `updated by '||user ||' on `||SYSDATE);

This example adds another column to the EMP table. The DDL text supplied is asynchronously applied at each master site as described . If you had made this change using the ALTER TABLE command, instead of by passing this command as an argument to the ALTER_MASTER_REPOBJECT procedure, this change would not have been propagated to the other master sites in the replicated environment.

Local customization of individual replicas at snapshot or master sites is outside the scope of Oracle's symmetric replication facility. As a replication administrator, you must ensure that local customizations do not interfere with any global customizations done with ALTER_MASTER_REPOBJECT.

Additional Information: The parameters for the ALTER_MASTER_REPOBJECT procedure are described in Table 12 - 74, and the exceptions are listed in Table 12 - 75.

Dropping a Replicated Object

To drop a replicated object from a replicated object group, call the DROP_MASTER_REPOBJECT procedure in the DBMS_REPCAT package at the master definition site, as shown in the following example:

DBMS_REPCAT.DROP_MASTER_REPOBJECT(
    sname        => 'acct_rec', 
    oname        => 'emp', 
    type         => 'table', 
    drop_objects => FALSE);

In this example, when the procedure is executed, all automatically generated supporting objects, such as triggers and packages, used to replicate EMP will be dropped from each site. Because DROP_OBJECTS is set to FALSE, the default, the EMP table will continue to exist at all of the master sites. You can request that the object and any dependent objects be dropped at all sites by setting DROP_OBJECTS to TRUE.

This change is synchronously broadcast to each master site as described . Because this procedure is then executed asynchronously at each site, the RepCatLog view contains the interim status and any asynchronous error messages generated by the request.

Before dropping a replicated table, be certain that no snapshots depend on this object.

Additional Information: The parameters for the DROP_MASTER_REPOBJECT procedure are described in Table 12 - 123, and the exceptions are listed in Table 12 - 124.

Removing a Master Site

1. Remove the databases from the replicated environment by calling REMOVE_MASTER_DATABASES at the master definition site.

2. Remove the replicated object group from each database by calling DROP_MASTER_REPGROUP at each of the master sites dropped in step 1.

Dropping a Master Site If You Cannot Quiesce

• clean the deferred RPC queue and

• remove any data inconsistencies.

1. Quiesce the object group by calling DBMS_REPCAT.SUSPEND_MASTER_ACTIVITY at the master definition site.

2. Delete transactions whose destination set includes the removed masters by calling DBMS_DEFER_SYS.DELETE_TRANSACTION at each remaining master.

3. Delete transactions whose destination set includes former and remaining masters by calling DBMS_DEFER_SYS.DELETE_TRANSACTION at each former master.

4. Resolve each conflict in the DefError view at each remaining master, either by applying the error with DBMS_DEFER_SYS.EXECUTE_ERROR or by deleting the error with DBMS_DEFER_SYS.DELETE_ERROR.

5. Ensure that the DefError and DefTranDest views are empty at each remaining master.

6. If the DefTran view is not empty at a remaining master, truncate the SYSTEM.DEF$_CALL table at that master.

7. Ensure that all replicated data is consistent. The DBMS_RECTIFIER_DIFF package can be used to determine and fix differences.

8. Unquiesce the object group by calling DBMS_REPCAT.RESUME_MASTER_ACTIVITY at the master definition site.

Dropping a Master Site from a Replicated Environment

Calling REMOVE_MASTER_DATABASES causes the replication triggers and their associated packages to be regenerated at all remaining master sites.

You cannot remove the master definition site. The databases being removed do not have to be accessible when you call this procedure. All other master databases in the replicated environment must be available.

For example, suppose A is the master definition site and sites B, C, D, and E are master sites for the replicated schema R. If masters C and E become inaccessible and should no longer be masters, you should execute the following procedure at site A:

DBMS_REPCAT.REMOVE_MASTER_DATABASES(
    gname       => 'R', 
    master_list => 'C,E');

The following code is also acceptable:

master_table dbms_utility.dblink_array;
...
master_table(1) := 'C';
master_table(2) := 'E';
DBMS_REPCAT.REMOVE_MASTER_DATABASES(
    gname        => 'R', 
    master_table => master_table);

After calling this procedure, you should call DROP_MASTER_REPGROUP at each of the master sites that you removed. Otherwise, all of the replication views, such as RepGroup, will still be available (making the object group appear to be replicated), but the site will no longer receive changes from other master sites.

Additional Information: The parameters for the REMOVE_MASTER_DATABASES procedure are described in Table 12 - 161, and the exceptions are listed in Table 12 - 162.

Dropping a Replicated Object Group from a Former Master Site

DBMS_REPCAT.DROP_MASTER_REPGROUP(
    gname         => 'acct', 
    drop_contents => TRUE, 
    all_sites     => FALSE);

In this example, when this procedure is executed, the ACCT object group is no longer replicated at the current site. Because the second argument is TRUE, all replicated objects in this object group are dropped from the database.

Dropping the replicated object group removes all entries in the local replication views, such as RepGroup, that pertain to that replicated object group. Dropping the replicated object group at a master site also does not automatically remove that master site from the RepSite view at all other master sites. Before calling this procedure, you should call REMOVE_MASTER_DATABASES at the master definition site to ensure that replicated changes are no longer sent to the object group that you are dropping. Additionally, before calling this procedure, you can avoid unnecessary errors if you make sure that this object group is not being used as the master replication object group for any snapshot sites.

To drop the master definition site, you must first have dropped all of the master sites in the replicated environment. To drop the replicated object group from all sites, including the master definition site, you can call this procedure at the master definition site, and set ALL_SITES to TRUE.

Additional Information: The parameters for the DROP_MASTER_REPGROUP procedure are described in Table 12 - 121, and the exceptions are listed in Table 12 - 122.

Listing Information about a Replicated Environment

Warning: Do not modify the replication catalog tables directly. All modification should be made to replication catalog views by using the procedures provided in the DBMS_REPCAT package.

Each of these views is briefly described below. They are described in more detail in Chapter 13.

Each view has three versions, which have different prefixes: USER_*, ALL_*, and SYS.DBA_*. The views used to determine the status of the objects in your replication environment are as follows:

RepGroup

Indicates which schemas are being replicated at a given site.

RepSite

Indicates where each object group is being replicated

RepObject

indicates which objects in each schema are being replicated.

RepCatLog

Indicates the status of any asynchronously propagated administrative changes.

Prev Next

Copyright © 1996 Oracle Corporation. All Rights Reserved.

Library

Product

Contents

Index