Check Dataguard

Check if dataguard is running properly

Here is, how you can check if you dataguard configuration is running properly. There exist two ways :

  1. You can check it by the official way, like it is said in the oracle documentation
  2. Check it by your-self, I means add some rows or table in the primary database

I will only here explain the first method, as the second needs only to put the standby database to a read mode, and check it manually. Follow this link if you want to put your physical standby database in read mode.

So, you have to follow some step to check your dataguard configuration.

  1. View the sequence of your arch log on the primary db
  2. View the sequence of your arch log on the standby db
  3. switch a log file on the primary db
  4. check the present of the last arch log on standby database
  5. check if arch log is applyed on standby database

Here is how you can do these step.

1. On the primary db, execute this command in sqlplus with sysdba privilege :

SELECT SEQUENCE#,APPLIED FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;

Here was my output limited to the last two row of the query :
SEQUENCE# APP
---------- ---
         1 YES
         2 YES
         3 YES
         4 YES
         5 YES

And it is good, if all the sequence from the primary database, are also on the standby database and they are applied as you can see,
You can also check it in the alert.log of the db with this kind of message :

Wed Oct  8 18:04:02 2008
Primary database is in MAXIMUM PERFORMANCE mode
RFS[4]: Successfully opened standby log 4: '/u01/app/oracle/product/10.2.0/db_1/dbs/srl1.f'
Wed Oct  8 18:04:04 2008
Media Recovery Log /u01/app/oracle/oradata/arch/log1_6_660158745.arc
Media Recovery Waiting for thread 1 sequence 7 (in transit)

2. On the db database, check also the sequence of your arch log, still by doing that :

SELECT SEQUENCE#,APPLIED FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;

and you will probably get the same output as one the primary database.

3. Force a switch log on the primary db, like that there will be a new arch log, and we will be able to check if we can transport it to the standby db, and if we can apply it to the standby db. So, execute this command :

alter system switch logfile;

and now, if you re-execute the command of point 1, you will get a new arch log, that has to be transported to the standby db.
It has to be done automatically, if you dataguard configuration is properly set up.

4. Check the presence of the new arch log on standby db, with this command :

SELECT SEQUENCE#,APPLIED FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;

If you see the new sequence, it's good, otherwise check you dataguard config.

5. Check if the new arch log is appley to the standby db, don't forget that it perhaps needs sometime to get applyed if there is a lot of activity. So, execute this command :

SELECT SEQUENCE#,APPLIED FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;

The second way to check it !

There is also the other, but you have to disable temporary appliyng of redo log with this command :

SQL> alter database recover managed standby database cancel;

execute this command on the standby database !

and then put the standby database to be open in read only , to verify that the change on the primary database are also on the standby database. To put the db in open read only, let's execute this command :

alter database open read only;

and then check the change on your db with a sql query !

Unless otherwise stated, the content of this page is licensed under Creative Commons Attribution-ShareAlike 3.0 License