DBA Tips for Verifying Oracle Data Replication!!

 Author: Adith Modena

Oracle Replication is the process of creating and maintaining replica versions of your database objects. Oracle Replication provides alternate data access points and hence aids in improved performance and increased availability. You can still access the replicas of data if any part of the distributed database is down. Database replication can be surprisingly complex and it is always good idea to double-check audit to ensure that the replication is working. 

You should have replication monitoring scripts in-place for verifying and auditing replication of your Oracle databases. Always run a weekly replication during off-hours when no online users are updating the system.

You should keep a check on table row counts for top tables. If you find any non-matching rows then ROWID should be identified and corrected.

You can use a replication monitoring script as below to verify the replication counts for a single table. 

#!/BIN/KSH
$ORACLE_HOME/BIN/SQLPLUS - S PERFSTAT /PERFSTAT<<!
SPOOL /TMP/MYTABLE_REPLICATION.LST

SELECT PID, SEQ_NBR_KEY FROM
(
SELECT * FROM MYTABLE MINUS SELECT * FROM MYTABLE@REMOTE
);
SPOOL OFF

MYVAR=EXPR(`CAT /TMP/MYTABLE_REPLICATION.LST`)
IF [ $MYVAR -NE 0 ]; THEN ECHO "BAD REPLICATION"

Below script can be used to monitor the replication status, sites and groups.

CONNECT REPADMIN/REPADMIN

COLUMN SNAME FORMAT A20 HEAD "SCHEMA_NAME"
COLUMN MASTERDEF FORMAT A10 HEAD "MASTERDEF?"
COLUMN ONAME FORMAT A20 HEAD "OBJECT_NAME"
COLUMN GNAME FORMAT A20 HEAD "GROUP_NAME"
COLUMN OBJECT FORMAT A35 TRUNC
COLUMN DB_LINK FORMAT A35 HEAD "DB_LINK"
COLUMN MESSAGE FORMAT A25
COLUMN BROKEN FORMAT A6 HEAD "BROKEN?"

SPOOL /TMP/MYTABLE_REPLICATION.LST

REPLICATION SCHEMAS/ SITES
SELECT SNAME, MASTERDEF, DB_LINK FROM SYS.DBA_REPSCHEMA;

REPLICATION LOG
SELECT REQUEST, STATUS, MESSAGE, ERRNUM FROM SYS.DBA_REPCATLOG;

ENTRIES IN THE JOB QUEUE
SELECT JOB, LAST_DATE, LAST_SEC, NEXT_DATE, NEXT_SEC, BROKEN, FAILURES, WHAT FROM SYS.DBA_JOBS WHERE SCHEMA_USER = 'REPADMIN';

REPLICATION STATUS
SELECT SNAME, MASTER, STATUS FROM SYS.DBA_REPCAT;

OBJECTS REGISTERED FOR REPLICATION
SELECT GNAME, TYPE||' '||SNAME||'.'||ONAME OBJECT, STATUS FROM SYS.DBA_REPOBJECT;

SPOOL OFF Read Again!!

 More Oracle Articles, Database Articles and DBA Tips
   Database Security: Step by step guideline
   Oracle Performance Enhancement using Parallel Execution Tips
   Oracle Tuning Guide for Concurrent Disk I/O
   User Managed Backups In Oracle Database – Physical Backups
   Speed Up Oracle Data Loading!!