Archiving : Changes for data replication

Many a time there can be a scenario where you do not want to archive data, but instead you may want to replicate the data to another database such as a separate reporting database where you don't want to run queries continuously on the main production database, but would rather run the same queries on a parallel database so as to not hamper the performance of the existing application in production.

We can achieve this data replication by a number of database features like database change notification, oracle advanced queues, oracle golden gate, oracle dataguard etc. But some of these features may be licensed and may not be suitable. Also the replication schema required may be such that the data from all the tables in the primary database is not required, but the data is required for only a few select tables.

To achieve this data replication, the archiving solution has been enhanced to provide a data replication functionality. A config parameter has been set, through which the archive machine can be used either for archiving or data replication.

A point to be noted here is that the functionality for archiving or replication has been developed not from the performance aspect (as it is a row by row insert and delete), but more from making it a restartable light weight program which can keep running in the background and do the job without hampering the main application process.

Below are the changes and enhancements made to the code

Enhancements to the archive solution to replicate data from one schema to another.

Archiving and replication for tables which have no primary key.

Restructured the code to work for archiving or replication based on config parameter.

Restructured the code for restartability.

The solution has been tested for most of the commonly used datatypes in the database namely VARCHAR2, NUMBER, DATE, CLOB, BLOB, FLOAT, TIMESTAMP.

Limitations

The performance of the code may not be optimum as it has been written for a row by row insert and delete to keep it a light weight application which can keep running in the background.

If the table has LONG datatype variables the archiving will fail. Such tables data will have to be manually archived or replicated with the Oracle COPY command or any other data replication features in oracle like data pump etc

In PKG_ARCHIVE I have maintained a configuration parameter

RUN_MODE.

This parameter can be set to ARCHIVE or REPLICATE.

For each table for which data is to be replicated a trigger is to be created which will insert the rowid of the record which has been inserted or updated into a queue table. The replication process will poll on this queue table for all records which are inserted. It will then query the main table and copy the data into the replication schema.

Queue table into which main table records will be inserted.

CREATE TABLE HR.REPL_QUEUE 
   (	TBL_NAME VARCHAR2(100 BYTE), 
	UNIQUE_ID VARCHAR2(100 BYTE), 
	REPL_STAT VARCHAR2(10 BYTE), 
	OPRTN VARCHAR2(10 BYTE)
) TABLESPACE HR ;

Trigger on main table to insert data into the replication queue.

CREATE OR REPLACE TRIGGER TR_REPL_QUEUE_EMP_TEST
AFTER INSERT OR UPDATE ON EMP_TEST
REFERENCING OLD AS OLD NEW AS NEW
FOR EACH ROW 
BEGIN
	IF INSERTING THEN
		BEGIN
			INSERT INTO REPL_QUEUE
			(
				TBL_NAME,
				UNIQUE_ID,
				REPL_STAT,
				OPRTN
			)
			VALUES
			(
			'EMP_TEST',
			:NEW.ROWID,
			'PNDNG',
			'INSERT'
			);
		EXCEPTION
		  WHEN DUP_VAL_ON_INDEX THEN
			NULL;
		END;
	ELSIF UPDATING THEN
		BEGIN
			INSERT INTO REPL_QUEUE
			(
				TBL_NAME,
				UNIQUE_ID,
				REPL_STAT,
				OPRTN
			)
			VALUES
			(
			'EMP_TEST',
			:NEW.ROWID,
			'PNDNG',
			'UPDATE'
			);
		EXCEPTION
		  WHEN DUP_VAL_ON_INDEX THEN
			NULL;
		END;
	END IF;
END TR_REPL_QUEUE_EMP_TEST;

You can refer to the next post for the main objects modified in the archival solution 

Thought for the day

The Lord honors a father above his children, 

  And he confirms a mother's right over her children. 

Those who honor their father atone for their sins, 

  And those who respect their mother are like those who lay up a treasure.

Sirach 3:2-3