Oracle Advanced Replication – Administration Tasks (Concepts/no SQL)

1. Suspending Master Activity
2. Resume Master Activity
3. Applying Administration Tasks
4. Applying Transactions
5. Removing Transactions
6. Removing Errors
7. Drop Replication Object

Suspending Master Activity
Whenever some administration tasks needs to be taken, such as adding an object to a replication group, you must suspend master activity for the nominated group. This will put the group on the master and master definition site into QUIESCED mode. At this stage, only SELECT statements are allowed on the objects within the group. Any other DML or DDL statements will generate ORA-23311 or ORA-23326.

ORA-23311 object group “name” is quiesced
Cause: SUSPEND_MASTER_ACTIVITY has been called before the object group has resumed normal operation.
Action: If a RESUME_MASTER_ACTIVITY request is pending, wait until it completes, and then reinvoke SUSPEND_MASTER_ACTIVITY.
ORA-23326 the system is being quiesced
Cause: A deferred remote procedure call operation was attempted while the database was quiesced.
Action: Resume database activity with the DBMS_REPCAT.RESUME_MASTER_ACTIVITY call.

For example, exec dbms_repcat.suspend_master_activity(‘BILLING_RUN’);
You must issue this command only on the master DEFINITION site. All master sites involved in the group will be QUIESCED accordingly.

Resume Master Activity
Once administration tasks are completed, ie, there is nothing in the DBA_REPCATLOG, you may put the database back into normal mode. Again, this command must only be entered at the master DEFINITION site. For example: exec dbms_repcat.resume_master_activity(‘BILLING_RUN’);

Applying Administration Tasks
Administration tasks are queued in DBA_REPCATLOG. The job dbms_repcat.do_deferred_repcat_admin(‘”ACME”‘, FALSE); at both the master and master definition site should apply all these requests. You may choose to execute this manually, or run the job.

Applying Transactions
Transactions are queued in DEFTRAN. Each transaction has one or more corresponding DEFCALL. For a simple one-way replication, you should not have any transactions in the master site! Otherwise you need to add some conflict resolution rules. In our example setup, ACME is only using simple one-way replication.

The job sys.dbms_defer_sys.execute will apply these transactions. Again, you can wait for the job to automatically execute, or run it manually. While it is running, you should see the number of DEFCALL and DEFTRAN decrease as transactions are applied.

Removing Transactions
If transactions in the DEFTRAN are no longer required, use the command DBMS_DEFER_SYS.DELETE_TRAN to remove the given transaction. For example, if you added a replication object to a group, and removed it later on, the transactions associated with these groups may still be left in DEFTRAN. Use DBMS_DEFER_SYS.DELETE_TRAN to remove then.

In drastic situations, you can delete them from SYSTEM.DEF$_CALL. Deleting a call will remove the corresponding entry in SYSTEM.DEF$_TRAN.

Removing Errors
Errors in administration tasks, such as adding groups, are found in DBA_REPCATLOG. This view also holds admin tasks that are still pending. For example:

SELECT ONAME, REQUEST, MESSAGE
FROM dba_repcatlog WHERE status = ‘ERROR’

Once transactions are submitted, any errors are logged in the DEFERROR view. There is a procedure to reapply the error, DBMS_DEFER_SYS.EXECUTE_ERROR. Or you can delete the error using DBMS_DEFER_SYS.DELETE_ERROR. As a last resort, you can remove errors by deleting entries from the table SYSTEM.DEF$_ERROR.

Drop Replication Object
When an object no loner needs to be part of replication group, use the DBMS_REPCAT.DROP_MASTER_REPOBJECT package. For example:

exec dbms_repcat.drop_master_repobject(-
sname => ‘ACME’,oname => ‘PARENTPARTICIPANT’,type => ‘TABLE’,drop_objects=>false);