Monitoring standby database made easy

we can use a simple trick to check if the standby database is up to date or not :

The trick is we are going to create a table and populate it every minute on primary server with a scheduled job using a  simple procedure.When we query the standby server we will see the table synced and if it is not the server is struck some where and has an issue.

Table DDL :


 CREATE TABLE "ORACLE"."DG_SYNC_STATUS"
   (    "TSTAMP" TIMESTAMP (6),
    "DB_UNIQUE_NAME" VARCHAR2(30 BYTE),
    "PRIMARY_SCN" NUMBER,
    "STANDBY_SCN" NUMBER,
    "PROTECTION_MODE" VARCHAR2(20 BYTE),
    "PROTECTION_LEVEL" VARCHAR2(20 BYTE)
   )  TABLESPACE "USERS" ;

 then we will create a simple procedure to populate the table every minute and this also will cleanup rows older than sysdate -3 so you don't have to worry about cleaning it up or space issues.

create or replace
PROCEDURE                                                        DG_SYNC_STATUS_CHECK_PRC
AS
BEGIN
  INSERT INTO DG_SYNC_STATUS (TSTAMP,DB_UNIQUE_NAME,PRIMARY_SCN,PROTECTION_MODE,PROTECTION_LEVEL)
  SELECT SYSTIMESTAMP, DB_UNIQUE_NAME, CURRENT_SCN, PROTECTION_MODE, PROTECTION_LEVEL
  FROM V$DATABASE ;

  DELETE FROM DG_SYNC_STATUS
  WHERE TSTAMP < SYSTIMESTAMP - 3;
  COMMIT;
END;


Once the table and procedure is created.schedule a job to execute this procedure to populate table every minute.here is sample screenshot for your scheduled job.

here is sample output of table being populated

That's it easy and simple way to check if the databases are in sync are not.Don't forget that you have to query the standby server to check if they are in sync or not.

Not a big solution but help full if you want to check daily.