Physical Standby Data Guard Health Check

Physical Standby Data Guard Health Check :

After creating any DR server, our main concern is that it should be in synchronized state So if an outage occurs at Production database, we can move to standby database as soon as possible.

To know more about it Oracle Dataguard

We have following steps to check Data Guard Health.

Database Details :

Step 1: Check Primary database protection mode and switchover status.

SQL> SELECT NAME,OPEN_MODE,DATABASE_ROLE,PROTECTION_MODE,DB_UNIQUE_NAME,SWITCHOVER_STATUS FROM V$DATABASE;

NAME			       OPEN_MODE	    DATABASE_ROLE    PROTECTION_MODE	  DB_UNIQUE_NAME		 SWITCHOVER_STATUS
------------------------------ -------------------- ---------------- -------------------- ------------------------------ --------------------
MGR			       READ WRITE	    PRIMARY	     MAXIMUM PERFORMANCE  MGR				 TO STANDBY

Step 2: Check maximum archive log sequence at primary :

SQL> SELECT MAX(SEQUENCE#) FROM V$THREAD;

MAX(SEQUENCE#)
--------------
	    47

We can see here Primary Database’s current archive sequence is 47.

Step 3: Check database role, protection mode at standby database :

SQL> SELECT NAME,OPEN_MODE,DATABASE_ROLE,PROTECTION_MODE,DB_UNIQUE_NAME FROM V$DATABASE;

NAME	  OPEN_MODE	       DATABASE_ROLE	PROTECTION_MODE      DB_UNIQUE_NAME
--------- -------------------- ---------------- -------------------- ------------------------------
MGR	  MOUNTED	       PHYSICAL STANDBY MAXIMUM PERFORMANCE  std_mgr

The standby database is in a Physical Standby role and it is in a maximum performance mode.

Step 4: Now, At standby database check archived logs are applied or not until the maximum sequence we checked from Primary Database.

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

 SEQUENCE# APPLIED
---------- ---------
	19 YES
	20 YES
	21 YES
	22 YES
	23 YES
	24 YES
	25 YES
	26 YES
	27 YES
	28 YES
	29 YES
	30 YES
	31 YES
	32 YES
	33 YES
	34 YES
	35 YES
	36 YES
	37 YES
	38 YES
	39 YES
	40 YES
	41 YES
	42 YES
	43 YES
	44 YES
	45 YES
	46 IN-MEMORY

28 rows selected.

SQL>

Here we can see v$archived_log says Archive log until sequence 45 is applied to the standby database and archive no 46 is in memory.

Step 5: Check recovery progress with the v$managed_standby view.

I have just checked v$managed _standby view after viewing v$archived_log, recovery is made till the 47th archive sequence and MRP process is waiting for next archive log that is 48.

SQL> select process,pid,status,sequence# from v$managed_standby; 

PROCESS 	 PID STATUS	   SEQUENCE#
--------- ---------- ------------ ----------
ARCH		4000 CLOSING		  34
ARCH		4002 CLOSING		  41
ARCH		4008 CLOSING		  43
ARCH		4006 CONNECTED		   0
ARCH		4004 CLOSING		  47
ARCH		4010 CLOSING		  44
ARCH		4012 CLOSING		  45
ARCH		4014 CLOSING		  46
RFS	       12609 IDLE		   0
RFS	       12603 IDLE		   0
RFS	       12605 IDLE		  48
RFS	       12607 IDLE		   0
MRP0	       15643 WAIT_FOR_LOG	  48

Stay tuned for More articles on Oracle DataGuard

Thank you for giving your valuable time to read the above information.

If you want to be updated with all our articles send us the Invitation or Follow us:

Telegram Channel: https://t.me/helporacle

Skant Gupta’s LinkedIn: www.linkedin.com/in/skantali/

Joel Perez’s LinkedIn: Joel Perez’s Profile

LinkedIn Group: Oracle Cloud DBAAS

Facebook Page: OracleHelp