Oracle7 Server Distributed Systems Manual, Vol. 2 Go to Product Documentation Library
Library
Go to books for this product
Product
Go to Contents for this book
Contents
Go to Index
Index



Go to previous file in sequence Go to next file in sequence

Multi-Master Replication


This chapter explains how to use the symmetric replication facility to create and maintain replicated master sites. The topics discussed include the following:

Note that most of the activities described in this chapter can be accomplished much more easily by using Oracle's Replication Manager, a GUI interface for replication. See the documentation for Oracle Replication Manager for more information.

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

The symmetric replication facility supports the replication of tables, views, indexes, synonyms, triggers, and packages at master sites. All master sites participating in a replicated environment must have the same replicated objects. If you only want to replicate a subset of the objects to a given site, you should consider designating that site as a snapshot site.

Replication Support for Tables

The symmetric replication facility supports replication of both data-level (DML) and schema-level (DDL) changes to replicated tables. Whenever you perform an INSERT, UPDATE, or DELETE on a table replicated using row-level replication, Oracle ensures that the change is ultimately applied at each replica of the table. Whenever you update a table by calling a replicated procedure, your procedure call is ultimately replicated to all other sites in the replicated environment. Propagation of these changes between master sites is explained in detail starting [*].

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

Any changes that you make to a non-table object using the procedures provided in the DBMS_REPCAT package are ultimately replicated to all other sites in the replicated environment. You should always use the procedures provided in the DBMS_REPCAT package to update non-table replicated 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

When you begin to design your replicated environment, you need to make the following decisions:

Before Creating a Replicated Environment

After you have determined which objects you want to replicate, you must ensure that you have the privileges necessary to create these objects at each site. Additionally, once you have determined which sites will make up your replicated environment, you must ensure that these sites can communicate with one another by creating the necessary database links.

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

A replication administrator is responsible for configuring the replicated environment. You can create one replication administrator to administer all replicated objects at a site, or you can have separate administrators for the replicated objects in each schema. All symmetric replication procedures should be run while you are connected as a replication administrator.

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

To create a replication administrator for a single schema, call DBMS_REPCAT_ADMIN.GRANT_ADMIN_REPGROUP, as shown in the following example:

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:

	CREATE USER repadmin IDENTIFIED BY repadminpassword;

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

Additional Information: The parameter for the GRANT_ADMIN_ANY_REPGROUP procedure is described in Table 12 - 175, and the exceptions are listed in Table 12 - 176.

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

To create the necessary links to perform replication administration, you must complete the following steps at each master site in the replicated environment:

In the following example, assume that you have three master sites whose global database names are sales.widgetek.com, inventory.widgetek.com, and hq.widgetek.com. Now assume that you have created user REPADMIN at each site with password SECRET, and that this user will act as the replication administrator for this replicated environment. At the SALES site, you would need to create the database links as shown below:

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

When you call a procedure in the DBMS_REPCAT package at a master site, the symmetric replication facility attempts to perform any deferred replication administration for any replicated schema that is passed as an argument to the DBMS_REPCAT procedure.

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

Certain replication activities are run as SYS and require access to remote nodes. By creating a surrogate replication administrator at the remote site, you preclude the need for a SYS to SYS database link between master sites. The surrogate replication administrator performs actions on behalf of the symmetric replication facility at the remote site.

Granting Privileges

To create a surrogate replication administrator at your current site, complete the following steps:

	CREATE USER surrogate IDENTIFIED BY surrogate_password;

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

Additional Information: The parameter for the GRANT_SURROGATE_REPCAT procedure is described in Table 12 - 183, and the exceptions are listed in Table 12 - 184.

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

You need to create the links used by the symmetric replication facility in a similar manner to those created for each replication administrator. The replication facility, however, requires special privileges at each site to perform required operations. Therefore, the user that you specify in the CONNECT TO clause must have these privileges. This special user is called the surrogate replication administrator.

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

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

End Users

End users require no additional privileges beyond those necessary to update the local objects. The ability to apply these changes at the replicated sites is determined by the links used to propagate the changes, and whether the changes are propagated synchronously or asynchronously.

Synchronous Propagation

When you update a table at a site that is propagating changes synchronously, Oracle fires a trigger that calls a package which in turn makes a remote procedure call to the site or sites that are receiving the change synchronously.

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 privilege domain of an asynchronously replicated transaction when executed at the remote site is determined by

When the replication administrator schedules the execution of the deferred transaction queue, he or she can require that the deferred transaction be pushed as the user who originally queued the transaction, or as the connected session user.

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

To create a multi-master replicated environment, you (the replication administrator) must use the procedures in the DBMS_REPCAT package to complete the following steps:

Note: If you will be adding additional master sites to your replicated environment, you may prefer to defer generating replication support until after these sites have been added. However, because the tables will be copied to the new master sites without the supporting triggers and packages any changes that occur before you generate replication support will not be replicated.

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.

Attention: You must generate replication support before attempting to update any tables you have registered as replicated objects. If you are unsure whether replication support has been generated for a particular object, see the RepGenerated view (see page 13 - 6).

Creating a Replicated Object Group

Only objects in a replicated object group can be replicated to other master and snapshot sites. When you create a replicated object group, the name of the object group is added to the RepGroup view at that site. Additionally, the object group name and the global database name (and the connection qualifier, if any, associated with the object group for the local site are added to the RepSite view for the site, and by default, this site is designated as the master definition site.

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

Only objects in a replicated object group can be replicated to another site. To add an object to a replicated object group, call the procedure CREATE_MASTER_REPOBJECT in the DBMS_REPCAT package. As shown in the following example, this object need not already exist:

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

When Oracle attempts to create the object at each master site, two parameters affect how the object is created: USE_EXISTING_OBJECT and COPY_ROWS. The default value for both of these parameters is TRUE.

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

Oracle raises a duplicateobject exception if the table at the master site is not the same shape as the table at the master definition site. Shape of a table refers to the number of columns, the name of these columns, and the datatype of the columns. This exception is stored in the RepCatLog view.

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

If you are using row-level replication, Oracle must know which rows to compare when you push changes from one site to another. Because two rows may have different row IDs at different replication sites, Oracle uses the primary key for a table to determine which rows to compare. If you do not want to use the primary key for a table, or if the table does not have a primary key, you must call the SET_COLUMNS procedure in the DBMS_REPCAT package, as shown in the following example:

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

To add another master site to your replicated environment, call the ADD_MASTER_DATABASE procedure in the DBMS_REPCAT package, as shown in the following example:

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

If you are adding a new master site to an existing replicated environment (one in which replication activity has already occurred), you must perform the following steps:

Warning: Do not resume replication activity or do additional DBMS_REPCAT.* administration for the replicated object group until the new master site appears in the RepSite view at the master definition site. Any changes that you make at any site will not be propagated to the new site until it is included in the RepSite view and you may not be able to resynchronize your data.

Adding a Site to an Existing Replicated Environment Using Offline Instantiation

Offline instantiation of a master site allows you to create a new master site while limiting the time required for existing sites in your replicated system to be quiesced. It is primarily useful for those sites with very large databases where the time required to transfer the data through network links to the new site would be prohibitive.

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:

Warning: Do not resume replication activity or do additional DBMS_REPCAT.* administration for the replicated object group until the new master site appears in the RepSite view at the master definition site. Any changes that you make at any site will not be propagated to the new site until it is included in the RepSite view and you may not be able to resynchronize your data.

Notes:

Generating Replication Support

The triggers, packages and procedures needed to support replication are not created until you call the GENERATE_REPLICATION_SUPPORT procedure in the DBMS_REPCAT package, as shown in the following example:

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

Under synchronous propagation, a transaction is owned by the owner of the trigger and will be propagated to the remote sit with that owner's privileges. You can change the ownership of the transaction, usually to the replication administrator who has full privileges at the remote site, by using GEN_OBJ_OWNER.

Row-Level Replication

If you want to use row-level replication for a table, you should call GENERATE_REPLICATION_SUPPORT immediately after any calls that define the replicated table, including CREATE_MASTER_REPOBJECT, ALTER_MASTER_REPOBJECT, ALTER_MASTER_PROPAGATION, SET_COLUMNS, and any conflict resolution routines, such as ADD_UPDATE_RESOLUTION. You may make multiple definition calls (for example, you may add multiple conflict resolution methods), before generating replication support. If you have only modified the propagation method, you may prefer to call GENERATE_REPLICATION_TRIGGER. If you have only modified the conflict resolution method for a table, you may prefer to call GENERATE_REPLICATION_PACKAGE.

Procedural Replication

If you are generating support for a package (body), Oracle generates the package (body) wrapper and you need to designate a prefix for the package (body). You should use the same prefix for the package body as you do for the package. If you do not designate a prefix, the default prefix is "defer_".

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

As shown in Figure 4 - 1, whenever you make a Data Manipulation Language (DML) change to a local table replicated using synchronous row-level replication, this change is synchronously propagated to the other master sites in the replicated environment using generated triggers and their associated packages. When you apply your local change, these triggers issue calls to generated procedures at the remote 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

LONG and LONG RAW columns cannot be not replicated using row-level replication and, if present, are skipped prior to logging in the deferred RPC queue.

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

The necessary remote procedure calls to support synchronous replication are included in the generated trigger and its associated package for each object. When you call GENERATE_REPLICATION_SUPPORT, Oracle regenerates these triggers at all master sites to add the necessary remote procedure calls for the new site. Conversely, if you remove a master site, Oracle removes these calls from the generated trigger.

Privilege Domain at the Remote Site

The privilege domain in which the transaction is executed at the remote site is determined by the database link that is used to connect to the remote site. Because the remote procedure is called from within a local generated procedure, Oracle looks for a private database link for the owner of this generated procedure. The owner of a generated procedure can also be specified by using the parameter GEN_TRIG_OWNER of the GENERATE_REP_SUPPORT procedure to place triggers and packages in any valid schema.

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 all of your sites are communicating synchronously with one another you should never experience an update conflict. However, if even one site is sending changes asynchronously to another site, you may experience conflicts at any site in your replicated environment.

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

As shown in Figure 4 - 2, whenever you make a Data Manipulation Language (DML) change to a local table replicated using asynchronous row-level replication, this change is asynchronously propagated to the other master sites in the replicated environment using generated triggers and their associated packages.

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:

Restrictions

LONG and LONG RAW columns cannot be not replicated using row-level replication and, if present, are skipped prior to logging in the deferred RPC queue.

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

For both scheduled and manual executions, you must specify the fully qualified database name for the remote site to which you want to push the 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

The privilege domain in which the transaction is executed at the remote site is determined by the database link that is used to connect to the remote site. If the EXECUTE_AS_USER parameter is TRUE, Oracle looks for a database link for the connected session user. If EXECUTE_AS_USER is FALSE (the default), Oracle looks for a database link for the user who originated the transaction (that is, the user who issued the DML statements on the local table).

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

The symmetric replication facility uses two-phase commit to ensure that transactions queued for a remote site are never lost. A transaction is not removed from the queue at the local site until it is successfully propagated to the remote site. Note that successful propagation does not imply successful completion of the transaction at the remote site. If the transaction cannot be successfully applied at the remote site, such as when an unresolvable conflict occurs, the transaction is logged in the DefError view at the remote site.

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

When you push changes from one master site to another, Oracle compares the values for the row at the originating master site, before any changes were applied, to the current values for the row at the destination master site. If the values are different, a conflict has occurred. Conflict detection and conflict resolution are described in Chapter 6.

Scheduling Execution of the Deferred Transaction Queue

To establish communication between master sites, call the SCHEDULE_EXECUTION procedure in the DBMS_DEFER_SYS package, as shown in the following example:

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

To force the deferred transactions queued at your current master site to be pushed to another master site, call the EXECUTE procedure in the DBMS_DEFER_SYS package, as shown in the following example:

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

To stop automatic propagation of deferred transactions between master sites, call the UNSCHEDULE_EXECUTION procedure in the DBMS_DEFER_SYS package, as shown in the following example:

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

To determine whether propagation of the deferred transaction queue from the current site to another site is enabled, call the DISABLED procedure in the DBMS_DEFER_SYS package, as shown in the following example:

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

When you add a new database site, you get to select a default propagation method for that site. Because this propagation method determines how the new site both sends changes to and receives changes from all existing sites, the order in which you add sites is important.

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

If you are not satisfied with the default propagation method between sites, you can alter the method used to send changes from one site to a given set of sites by calling DBMS_REPCAT.ALTER_MASTER_PROPAGATION, as shown in the following example:

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

After altering the propagation mode of an object group, you need to regenerate the supporting triggers for these objects at each site in the replicated environment. To generate the supporting triggers and their associated packages for all members of an object group for a given set of master sites, call the DBMS_REPCAT.GENERATE_REPLICATION_TRIGGER procedure, as shown in the following example:

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

Whenever you make a Data Definition Language (DDL) change in a replicated environment, for example, by calling DBMS_REPCAT.ALTER_MASTER_REPOBJECT, this change must be propagated to all other sites in the replicated environment. Note that, in this manual, "DDL changes" refer to any schema-level changes, whether they involve user-supplied DDL statements (as is the case with ALTER_MASTER_REPOBJECT), or not (as is the case with CREATE_MASTER_REPOBJECT and other DDL-like procedures).

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.

By synchronously broadcasting the change Oracle ensures that all sites are aware of the change, and thus are capable of remaining in sync. By allowing the change to be applied at the site at a future point in time, Oracle provides you with the flexibility to choose the most appropriate time to apply changes at a site.

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

Many of the procedures used to alter your replicated environment require that you resynchronize the replicated data at each master site. This is referred to as quiescing the system. You can quiesce the system by calling SUSPEND_MASTER_ACTIVITY. After making the necessary changes to your replicated environment, you can resume normal replication activity by calling RESUME_MASTER_ACTIVITY. Both of these procedures must be called at the master definition site.

During a Quiesce

The following steps describe how the symmetric replication facility resynchronizes the master sites in your replicated environment when you call DBMS_REPCAT.SUSPEND_MASTER_ACTIVITY. All master sites must be available to quiesce the replicated environment.

Figure 4 - 6. Quiescing the Replicated Environment Step 1: Calling SUSPEND_MASTER_ACTIVITY

Figure 4 - 7. Quiescing the Replicated Environment Step 2: Pushing the Deferred Transaction Queue

Figure 4 - 8. Quiescing the Replicated Environment Step 3: Master Sites Return Status to Master Definition Site

Figure 4 - 9. Quiescing the Replicated Environment Step 4: Quiesce Completed

Suspending Replication Activity

To suspend replication activity for an object group, call the procedure SUSPEND_MASTER_ACTIVITY in the DBMS_REPCAT package, as shown in the following example:

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:

There may be additional situations in which you find it necessary to suspend replication activity. For example, administrators may wish to suspend activity and manually perform queries and updates on the replicas to restore equivalence if an unexpected conflict is detected, which was not resolved.

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

Under synchronous propagation, a transaction is owned by the owner of the trigger and will be propagated to the remote sit with that owner's privileges. You can change the ownership of the transaction, usually to the replication administrator who has full privileges at the remote site, by using GEN_OBJ_OWNER.

Resuming Replication Activity

After quiescing your replicated environment, you can resume normal replication activity by calling the RESUME_MASTER_ACTIVITY procedure in the DBMS_REPCAT package, as shown in the following example:

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

DDL changes to a replicated object must be made by calling DBMS_REPCAT.ALTER_MASTER_REPOBJECT from the master definition site. The ALTER_MASTER_REPOBJECT procedure lets you supply the DDL that you want to have applied to the replicated object.

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

Supplying the DDL

To alter an object in your replicated environment, call the ALTER_MASTER_REPOBJECT procedure in the DBMS_REPCAT package, as shown in the following example:

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

This section describes how to drop an object from a replicated object group. This change ultimately affects all master and snapshot sites. Information on adding an object to the replicated object group is described [*].

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

To remove one or more master databases from your replicated environment, complete the following steps in order:

Dropping a Master Site If You Cannot Quiesce

Oracle Corporation recommends that DBMS_REPCAT.REMOVE_MASTER_DATABASES be called for a replicated object group only when that object group is quiesced. When an object group cannot be quiesced and one or more masters must be removed from the object group, the replication administrator must

The next time the object group is quiesced, in particular, the replication administrator must complete the following steps in order as soon as possible after the masters are removed.

Dropping a Master Site from a Replicated Environment

To remove one or more master databases from a replicated environment, call the REMOVE_MASTER_DATABASES procedure in the DBMS_REPCAT package.

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

To drop a replicated object group from your current site, call the DROP_MASTER_REPGROUP procedure in the DBMS_REPCAT package, as shown in the following example:

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

Whenever you install symmetric replication capabilities at a site, Oracle installs the replication catalog, which consists of tables and views. These tables are used by master and snapshot sites to determine such information as what objects are being replicated, where they are being replicated, and if any errors have occurred while administering replication.

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.




Go to previous file in sequence Go to next file in sequence
Prev Next
Oracle
Copyright © 1996 Oracle Corporation.
All Rights Reserved.
Go to Product Documentation Library
Library
Go to books for this product
Product
Go to Contents for this book
Contents
Go to Index
Index