|Oracle7 Server Distributed Systems Manual, Vol. 2||
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.
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 .
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.
There are three categories of users in a replicated environment:
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.
Users querying and updating replicated objects.
Note: The packages DBMS_REPCAT_ADMIN and DBMS_REPCAT_AUTH are owned by SYS. Access to these packages should not be granted widely.
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');
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.
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.
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.
CREATE USER surrogate IDENTIFIED BY surrogate_password;
DBMS_REPCAT_AUTH.GRANT_SURROGATE_REPCAT(userid => 'surrogate');
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.
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';
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.
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.
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.
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.
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 .
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
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.|
|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.|
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.
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.
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:
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 220.127.116.11.
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.
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.
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.
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.
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.
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.
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:
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
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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:
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.
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.
To ensure that the change is properly applied at all master sites, you should apply these changes in the following order:
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.
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.
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.
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.
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:
Indicates which schemas are being replicated at a given site.
Indicates where each object group is being replicated
indicates which objects in each schema are being replicated.
Indicates the status of any asynchronously propagated administrative changes.
Copyright © 1996 Oracle Corporation.
All Rights Reserved.