Data Replication : Using Change Notification and DBMS_SCHEDULER as a LISTENER

The case study example for data replication posted involves a lot of manual intervention such as

• Each time a new table is added to the schema for data replication, a trigger has to be created to insert the data into the REPL_QUEUE.
• The Archive Machine has to be manually started periodically periodically to replicate the data into the replication schema.

To make this process more seamless we can leverage 

Oracle Change Notification

DBMS_SCHEDULER to run the archive machine in the background

With these changes the only additional steps required to add a table to the replication engine are

On the Main schema

• Providing the SELECT and DELETE grants on the new table to the schema into which the new table data is to be replicated.
• Registering the table with the Change Notification process.

On the REPLICA schema

• Create the similar table in the REPLICA schema with the 2 additional columns for ARCHIVE_LOG_HEADER_ID and ARCHIVE_DATE columns.
• Make an entry in ARCHIVE_RULES table for the new table for which data is to be replicated.
• Start the DBMS_SCHEDULER as a listener process

Below are the steps to run the replication process using DB Change Notification and a scheduler.

In this you can skip the creation of the triggers on the CUSTOMER and DAILY_TRANSACTIONS tables as we will be using change notification for the same.

Create CUST schema objects

Enable Change Notification on the CUSTOMER schema as per the link provided above.

Below are the steps to be carried out for registering the CUSTOMER schema objects to be replicated with CN. 

 Create the Change Notification Callback procedure as per link below

    Oracle Change Notification Callback procedure

Registration of the CUST schema tables with CN 

 Register the callbak procedure chnf_repl_callback and the CUSTOMER table with Change Notification

BEGIN
  REGISTER_CALLBACK();
--rollback; 
END; 

Once the callback procedure and the CUSTOMER tables are registered with change notification we get the registration id REGID by querying user_change_notification_regs table. Inn this case the REGID is 301.

We register the DAILY_TRANSACTIONS table by passing the REGID and the table name to REG_OBJ_WT_CN procedure.

Register DAILY_TRANCATIONS table and any additional objects to be replicated with CN

DECLARE
  P_REG_ID NUMBER;
  P_TABLE_NAME VARCHAR2(200);
BEGIN
  P_REG_ID := 601;
  P_TABLE_NAME := 'DAILY_TRANSACTIONS';

  REG_OBJ_WT_CN(
    P_REG_ID => P_REG_ID,
    P_TABLE_NAME => P_TABLE_NAME
  );
  COMMIT;
--rollback; 
END;

Verify the registration by querying the user_change_notification_regs table.

It should have entries for both the DAILY_TRANSACTIONS and the CUSTOMER tables.

REGID	TABLE_NAME	CALLBACK
602	CUST.DAILY_TRANSACTIONS	plsql://chnf_repl_callback?PR=0
602	CUST.CUSTOMER	plsql://chnf_repl_callback?PR=0

  

On replication schema

Make the entries in ARCHIVE_RULES table.

NUM_RULE_ID	TXT_RULE_DESC	TXT_RULE_QUERY	TXT_ACTIVE_FLG	TXT_ARCHIVE_TABLE	TXT_DELETE_TABLE	TXT_TBL_OWNER
100002	replicate the customer data to REPLICA schema	SELECT UNIQUE_ID FROM CUST.REPL_QUEUE WHERE REPL_STAT = 'PNDNG' AND TBL_NAME = 'CUSTOMER'	Y	CUSTOMER	REPL_QUEUE	CUST
100002	replicate the customer daily transactions data to REPLICA schema	SELECT UNIQUE_ID FROM CUST.REPL_QUEUE WHERE REPL_STAT = 'PNDNG' AND TBL_NAME = 'DAILY_TRANSACTIONS'	Y	DAILY_TRANSACTIONS	REPL_QUEUE	CUST

Create the driver procedure to run the replication machine.

CREATE OR REPLACE PROCEDURE RUN_ARCHIVE_MACHINE_FOR_REPLICATION AS 

  v_program_name   VARCHAR2(100) := 'RUN_ARCHIVE_MACHINE_FOR_REPLICATION';
  v_table_name     VARCHAR2(100) := NULL;
  ARCHIVE_EXCEPTION   EXCEPTION;
  v_err_desc       VARCHAR2(500);
  v_error_code     NUMBER       := PKG_ARCHIVE.SUCCESS;
  v_archv_btn  VARCHAR2(10) := 'STOP';
  v_loop_cntr NUMBER := 0;
BEGIN

  WHILE TRUE
  LOOP --{
      SELECT ARCHIVE_BTN
      INTO v_archv_btn
      FROM ARCHIVE_CNTRL;
      
      IF v_archv_btn = 'START'
      THEN
          v_loop_cntr := v_loop_cntr + 1;
        PR_ARCHIVE_MACHINE;
        COMMIT;
--          v_err_desc := 'Calling archive machine ' || v_loop_cntr;
--          PR_ARCHIVE_ERROR_LOG(v_program_name, v_table_name ,v_err_desc, SQLCODE , SQLERRM , SYSDATE , USER, v_error_code);

      ELSE
        EXIT;
      END IF;  
      DBMS_LOCK.SLEEP (20);    
    
  END LOOP; --}
  
END RUN_ARCHIVE_MACHINE_FOR_REPLICATION;

  

Enable the archive button (ARCHIVE_BTN) in the archive control (ARCHIVE_CNTRL) table.

UPDATE ARCHIVE_CNTRL SET ARCHIVE_BTN = 'START';

Create the scheduler to run the replication machine.

  

BEGIN
    DBMS_SCHEDULER.create_job (job_name     => 'REPLICATION_SCHEDULER',
                               job_type     => 'STORED_PROCEDURE',
                               job_action   => 'RUN_ARCHIVE_MACHINE_FOR_REPLICATION',
                               start_date   => TO_dATE('08-FEB-25 17:43:00','DD-MON-YY HH24:MI:SS'),
                               enabled      => TRUE);
END;
/

Once all the steps for change notification, registration, archive rules setup, archive scheduler setup etc are complete the replication process should run seamlessly without any manual intervention when data is inserted into the main schema tables.

We are now ready to insert test data and check the replication process.

Create and run the data pump procedure to insert test data into the CUST schema tables

BEGIN
  CUSTOMER_DATA_PUMP();
  COMMIT; 
END;

If we check in the REPLICA schema CUSTOMER and DAILY_TRANSACTIONS tables, the data should be replicated.

Now if we add another table in the CUST schema for replication all we have to do is,

Give SELECT grants on that table to REPLICA schema

Register the new table with change notification

Create the similar table in REPLICA schema with 2 additional colums - ARCHIVE_LOG_HEADER_ID and ARCHIVE_DATE

Add a new rule in ARCHIVE_RULES table in REPLICA schema

Sample steps for the same in as below

Adding a new table to the replication engine

Thought for the day

Faithful friends are a sturdy shelter

  whoever finds one has found a treasure

Faithful friends are beyond price

  no amount can balance their worth.

Faithful friends are life saving medicine

  and those who fear the Lord will find them

Those who fear the Lord will direct their friendship aright

  for as they are, so are their neighbours also.

Sirach 6:14-17

What a friend we have in Jesus

Have we trials and temptations?

Is there trouble anywhere?

We should never be discouraged

Take it to the Lord in prayer

Can we find a friend so faithful

Who will all our sorrows share?

Jesus know our every weakness

Take it to the Lord in prayer