Monitoring operation in Dataguard

Check dataguard role is primary or stand by 

[oracle@oracle-db-19c ~]$ 
[oracle@oracle-db-19c ~]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Wed Feb 1 16:45:17 2023
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

SQL> set pagesize 200
SQL> set linesize 200
SQL> 
SQL> col DATABASE_ROLE format a30
SQL> col INSTANCE format a30
SQL> col OPEN_MODE format a30
SQL> col PROTECTION_MODE format a30
SQL> col PROTECTION_LEVEL format a30
SQL> col SWITCHOVER_STATUS format a30
SQL> SELECT DATABASE_ROLE, DB_UNIQUE_NAME INSTANCE, OPEN_MODE, PROTECTION_MODE, PROTECTION_LEVEL, SWITCHOVER_STATUS FROM V$DATABASE;

DATABASE_ROLE                  INSTANCE                       OPEN_MODE                      PROTECTION_MODE                PROTECTION_LEVEL               SWITCHOVER_STATUS
------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------
PRIMARY                        cdb1                           READ WRITE                     MAXIMUM PERFORMANCE            MAXIMUM PERFORMANCE            NOT ALLOWED

SQL> 

Monitoring recovery progress of stand by dataguard

SQL> 
SQL> 
SQL> SELECT TO_CHAR (START_TIME, 'DD-MON-YYYY HH24:MI:SS') "Recovery Start Time",
  2         TO_CHAR (item) || ' = ' || TO_CHAR (sofar) || ' ' || TO_CHAR (units)
  3             "Progress"
  4    FROM v$recovery_progress
  5   WHERE start_time = (SELECT MAX (start_time) FROM v$recovery_progress);

no rows selected

SQL> 

Monitoring lag in dataguard stand by and RAC

SQL> 
SQL> COLUMN applied_time FOR a30
SQL> SET LINESIZE 140
SQL> 
SQL> SELECT DB_NAME,
  2  APPLIED_TIME,
  3  LOG_ARCHIVED - LOG_APPLIED LOG_GAP,
  4  (CASE
  5  WHEN ( ( APPLIED_TIME IS NOT NULL
  6  AND (LOG_ARCHIVED - LOG_APPLIED) IS NULL)
  7  OR ( APPLIED_TIME IS NULL
  8  AND (LOG_ARCHIVED - LOG_APPLIED) IS NOT NULL)
  9  OR ( (LOG_ARCHIVED - LOG_APPLIED) > 5))
 10  THEN
 11  'Error! Log Gap is '
 12  ELSE
 13  'OK!'
 14  END)
 15  Status
 16  FROM (SELECT INSTANCE_NAME DB_NAME
 17  FROM GV$INSTANCE
 18  WHERE INST_ID = 1),
 19  (SELECT MAX (SEQUENCE#) LOG_ARCHIVED
 20  FROM V$ARCHIVED_LOG
 21  WHERE DEST_ID = 1 AND ARCHIVED = 'YES' AND THREAD# = 1),
 22  (SELECT MAX (SEQUENCE#) LOG_APPLIED
 23  FROM V$ARCHIVED_LOG
 24  WHERE DEST_ID = 2 AND APPLIED = 'YES' AND THREAD# = 1),
 25  (SELECT TO_CHAR (MAX (COMPLETION_TIME), 'DD-MON/HH24:MI') APPLIED_TIME
 26  FROM V$ARCHIVED_LOG
 27  WHERE DEST_ID = 2 AND APPLIED = 'YES' AND THREAD# = 1)
 28  UNION
 29  SELECT DB_NAME,
 30  APPLIED_TIME,
 31  LOG_ARCHIVED - LOG_APPLIED LOG_GAP,
 32  (CASE
 33  WHEN ( ( APPLIED_TIME IS NOT NULL
 34  AND (LOG_ARCHIVED - LOG_APPLIED) IS NULL)
 35  OR ( APPLIED_TIME IS NULL
 36  AND (LOG_ARCHIVED - LOG_APPLIED) IS NOT NULL)
 37  OR ( (LOG_ARCHIVED - LOG_APPLIED) > 5))
 38  THEN
 39  'Error! Log Gap is '
 40  ELSE
 41  'OK!'
 42  END)
 43  Status
 44  FROM (SELECT INSTANCE_NAME DB_NAME
 45  FROM GV$INSTANCE
 46  WHERE INST_ID = 2),
 47  (SELECT MAX (SEQUENCE#) LOG_ARCHIVED
 48  FROM V$ARCHIVED_LOG
 49  WHERE DEST_ID = 1 AND ARCHIVED = 'YES' AND THREAD# = 2),
 50  (SELECT MAX (SEQUENCE#) LOG_APPLIED
 51  FROM V$ARCHIVED_LOG
 52  WHERE DEST_ID = 2 AND APPLIED = 'YES' AND THREAD# = 2),
 53  (SELECT TO_CHAR (MAX (COMPLETION_TIME), 'DD-MON/HH24:MI') APPLIED_TIME
 54  FROM V$ARCHIVED_LOG
 55  WHERE DEST_ID = 2 AND APPLIED = 'YES' AND THREAD# = 2)
 56  ;

DB_NAME                                          APPLIED_TIME                      LOG_GAP
------------------------------------------------ ------------------------------ ----------
STATUS
------------------------------------------------------
cdb1
OK!


SQL> 

Monitoring dataguard stand by background processes

SQL> SELECT PROCESS, STATUS, THREAD#, SEQUENCE#, BLOCK#, BLOCKS FROM V$MANAGED_STANDBY;

PROCESS                     STATUS                                  THREAD#  SEQUENCE#     BLOCK#     BLOCKS
--------------------------- ------------------------------------ ---------- ---------- ---------- ----------
ARCH                        CONNECTED                                     0          0          0          0
ARCH                        CLOSING                                       1        127      73728       1986
ARCH                        CONNECTED                                     0          0          0          0
ARCH                        CONNECTED                                     0          0          0          0
DGRD                        ALLOCATED                                     0          0          0          0
DGRD                        ALLOCATED                                     0          0          0          0
DGRD                        ALLOCATED                                     0          0          0          0

7 rows selected.

SQL> 

View dataguard messages

SQL> 
SQL> show user;
USER is "SYS"
SQL> SELECT MESSAGE FROM V$DATAGUARD_STATUS;

MESSAGE
--------------------------------------------------------------------------------------------------------------------------------------------
Redo network throttle feature is disabled at mount time
STARTING ARCH PROCESSES
ARC0: Archival started
ARC1: Archival started
ARC2: Archival started
Becoming a 'no FAL' ARCH
Becoming the 'no SRL' ARCH
ARC3: Archival started
STARTING ARCH PROCESSES COMPLETE
Gap Manager starting
Beginning to archive T-1.S-127 (SCN:0x0000000001339b27-SCN:0x0000000001340b2c)
Completed archiving T-1.S-127 (SCN:0x0000000001339b27-SCN:0x0000000001340b2c)

12 rows selected.

SQL> 

Last log applied or received in stand by dataguard

SELECT 'Last Log applied : ' Logs,
       TO_CHAR (next_time, 'DD-MON-YY:HH24:MI:SS') Time
  FROM v$archived_log
 WHERE sequence# = (SELECT MAX (sequence#)
                      FROM v$archived_log
                     WHERE applied = 'YES')
UNION
SELECT 'Last Log received : ' Logs,
       TO_CHAR (next_time, 'DD-MON-YY:HH24:MI:SS') Time
FROM v$archived_log
where sequence# = (select max(sequence#) from v$archived_log);

SQL> 
SQL> show user;
USER is "SYS"
SQL> SELECT 'Last Log applied : ' Logs,
  2         TO_CHAR (next_time, 'DD-MON-YY:HH24:MI:SS') Time
  3    FROM v$archived_log
  4   WHERE sequence# = (SELECT MAX (sequence#)
  5                        FROM v$archived_log
  6                       WHERE applied = 'YES')
  7  UNION
  8  SELECT 'Last Log received : ' Logs,
  9         TO_CHAR (next_time, 'DD-MON-YY:HH24:MI:SS') Time
 10  FROM v$archived_log
 11  where sequence# = (select max(sequence#) from v$archived_log);

LOGS
------------------------------------------------------------
TIME
---------------------------------------------------------------------------------
Last Log received :
01-FEB-23:17:00:31


SQL> 

Monitoring recovery progress of stand by dataguard

SQL> 
SQL> show user;
USER is "SYS"
SQL> SELECT TO_CHAR (START_TIME, 'DD-MON-YYYY HH24:MI:SS') "Recovery Start Time",
  2         TO_CHAR (item) || ' = ' || TO_CHAR (sofar) || ' ' || TO_CHAR (units)
  3             "Progress"
  4    FROM v$recovery_progress
  5   WHERE start_time = (SELECT MAX (start_time) FROM v$recovery_progress);

no rows selected

SQL> 

 Stop / Start managed recovery process (MRP) in stand by dataguard

Cancel MRP process in standby:

alter database recover managed standby database cancel;

Start MRP:

alter database recover managed standby database disconnect from session;

For real time media recovery:

alter database recover managed standby database using current logfile disconnect from session;