Check and fix the dataguard issue with commands in Oracle

Leave a reply

On Primary Server:

Check and verify that we are working on Primary side of database:

Oracle AI Database 26ai Enterprise Edition Release 23.26.1.0.0 - Production
Version 23.26.1.0.0
SQL> Select name,open_mode,database_role from v$database;
NAME	  OPEN_MODE	       DATABASE_ROLE
--------- -------------------- ----------------
ORCL	  READ WRITE	       PRIMARY

Check if the gap is resolvable or not

SQL> select status,gap_Status from v$archive_Dest_Status where dest_id=2;
STATUS	  GAP_STATUS
--------- ------------------------
ERROR	  RESOLVABLE GAP

If the gap is resolvable then we have to check the error detail why it occurs:

SQL> SELECT dest_id, status, error FROM v$archive_dest WHERE status <> 'INACTIVE'; 
   DEST_ID STATUS     ERROR
---------- ---------  ---------------------------------------------
	 1     VALID       ORA-12543: TNS:destination host unreachable

Note: As per error, its seems that Standby database server is down, any network issue, or listener is down because the destination is unreachable.

Check the Primary archive sequence

SQL> select max(sequence#) from v$archived_log;
MAX(SEQUENCE#)
--------------
	    41

On Standby Server:

In my case, Our Standby Server is rebooted so its listener service and database is stopped. So, i first start the listener service with following command.

lsnrctl start

Verify the Standby Database Server 

SQL> Select name,open_mode,database_role from v$database;
NAME	  OPEN_MODE	       DATABASE_ROLE
--------- -------------------- ----------------
ORCL	  MOUNTED	       PHYSICAL STANDBY

Now we have to check that our MRP process is running or not

SQL> SELECT process, status, sequence#
FROM v$managed_standby;  2  
PROCESS   STATUS		SEQUENCE#
--------- -------------------- ----------
DGRD	  ALLOCATED			0
DGRD	  ALLOCATED			0
ARCH	  CONNECTED			0
ARCH	  CONNECTED			0
ARCH	  CONNECTED			0
ARCH	  CONNECTED			0
6 rows selected.

Note: As you can see, MRP process is not running so we will start the recovery process

Verify the applied archive in current situation:

SQL> select max(sequence#) from v$archived_log where applied='YES';
MAX(SEQUENCE#)
--------------
	    38

Start the MRP process:


SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE disconnect;

Database altered.

Once started, we have to again run and check the process is started or not

SQL> SELECT process, status, sequence#
  2  FROM v$managed_standby;
PROCESS   STATUS		SEQUENCE#
--------- -------------------- ----------
    DGRD	  ALLOCATED			0
    DGRD	  ALLOCATED			0
    ARCH	  CLOSING	       39
    ARCH	  CONNECTED			0
    ARCH	  CONNECTED			0
    ARCH	  CONNECTED			0
     RFS	  IDLE				0
     RFS	  IDLE		       42
     RFS	  IDLE				0
     RFS	  IDLE				0
     RFS	  IDLE				0
    MRP0	  APPLYING_LOG	   42
12 rows selected.

Note: As you see MRP process is started, so we have to have for 5 to 10 minutes depend upon the archive gap and this will fixed the issue.