DataGuard not sync troubleshooting

1)Check RECOVERY_FILE_DEST

col name for a15
SELECT name, SPACE_LIMIT/1024/1024/1024 AS SPACE, SPACE_USED/1024/1024/1024 AS USED, SPACE_RECLAIMABLE/1024/1024/1024 AS RECLAIMABLE FROM V$RECOVERY_FILE_DEST;

If reclaimable space is close to zero then delete archivelogs that no longer needed:

RMAN> delete archivelog all completed before 'sysdate-1';
RMAN> delete noprompt archivelog until sequence=3790;

Check RMAN Archivelog Deletion Policy. Why recovrey_file_dest was filled up?

2)Check for LAG from V$DATAGUARD_STATS View.

set linesize 9000
column name format a25
column value format a20
column time_computed format a25
select name, value, time_computed from v$dataguard_stats;

3)Check whether Redo Logs are sent and applied to Standby correctly or not, by following the Sequence numbers.

Primary:
select max(sequence#),thread# from v$archived_log group by thread#;

Standby:
select max(sequence#), thread#, applied from v$archived_log group by thread#, applied;

4)
The query below shows the latest archives on Primary and Standby. It shows the last archive produced in Primary and the last archive applied to Standby.

SELECT a.resetlogs_id, DECODE (a.thread#, 1, 'node1', 2, 'node2') HOST, b.last_seq prmy_last_file,
      a.applied_seq stdby_last_file, CASE WHEN b.last_seq - a.applied_seq > 2 THEN '=>' ELSE to_char(b.last_seq - a.applied_seq) END archive_difference, TO_CHAR (a.latest_apply_time, 'dd/mm/yyyy hh24:mi:ss') stdby_latest_time
 FROM
     (SELECT    resetlogs_id, thread#, MAX (sequence#) applied_seq, MAX (next_time) latest_apply_time
           FROM v#archived_log
          WHERE applied = 'YES'
       GROUP BY resetlogs_id, thread#) a,
      (SELECT   resetlogs_id, thread#, MAX (sequence#) last_seq
           FROM v$archived_log
       GROUP BY resetlogs_id, thread#) b
WHERE a.thread# = b.thread#
ORDER BY a.thread#;

5)
It can be seen if there is an error in the Alert Log from v$dataguard_status View. An alarm can be created by selecting the “Error” column in the Severity Column here.

col message for a60
SELECT timestamp, facility, message FROM v$dataguard_status ORDER BY timestamp;

6)
The status of Redo Apply and Redo Transport services. Run on Primary and Standby database.

SELECT process, status, group#, thread#, sequence# FROM v$managed_standby order by process, group#, thread#, sequence#;

NOTE: If you notice, there is no LSP process that performs Redo-Apply job in Logical Standby Databases as a result of the query. The reason is that v$managed_standby view is valid for Physical Standby Database.

7)
The information of the transactions currently processd by SQL APPLY

SELECT primary_xid, type, mining_status, apply_status FROM v$logstdby_transaction;

8)
Which archives are transmitted to the standby and when

select thread#, sequence#, round((blocks*block_size)/1024/1024), first_time, next_time, completion_time from gv$archived_log where registrar='RFS' order by 6;

9)
Get information about Standby Redo Logs from v$standby_log view

olumn dbid format a15
SELECT group#, thread#, sequence#, dbid, archived, status FROM v$standby_log;

ARC     STATUS         Comment
NO      UNASSIGNED     Standby Redo Log(SRL) has been archived and is suitable for reuse.
YES     UNASSIGNED     Standby Redo Log has never been used and is ready for use.
NO      ACTIVE         Writing to SRL is finished and SRL is expected to be archived.
YES     ACTIVE         SRL is actively used and is not yet archied.

10)
In cases wherer Standby Database does not receive Redos, we should check v$archive_dest.

SELECT dest_id, valid_type, valid_role, valid_now FROM v$archive_dest;

YES                  The Archive log destination has been defined and is valid for the current database role. Usually LOG_ARCHIVE_DEST_1 is defined this way.
WRONG VALID_TYPE     The Archive log destination has been defined, but there are no Standby Logs to use when in Standby Role.
WRONG VALID_ROLE     The Archive log destination is incorrectly defined. Online Logs cannot be read while in Standby Role.
UNKNOWN              The Archive log destination is not defined.

11)
If there are no problems in the destinations, errors are checked. Run on Primary:

column destination format a30
column error format a20
SELECT DEST_ID, STATUS, DESTINATION, ERROR FROM V$ARCHIVE_DEST WHERE DEST_ID <=5;

and check the following:

• TNSNAMES.ORA file
• LOG_ARCHIVE_DEST_n parameters
• LOG_ARCHIVE_DEST_STATE_n parameters
• LISTENER.ORA file
• Whether Listener runs on Standby
• Whether instances are running is checked on standby

12)

SELECT dest_id, status, destination, error FROM v$archive_dest WHERE dest_id<=5;

SELECT sequence#, applied FROM v#archived_log ORDER BY sequence#;
SELECT message FROM v#dataguard_status;

select to_char(current_scn) from v#database;
select scn_to_timestamp(16783654) from dual;
select thread#, sequence#, status from v#log;
select process, status, thread#, sequence#, block#, blocks from v#managed_standby;

select * from v#dataguard_stats;
select first_time, to_char(first_change#), to_char(next_change#), sequence# from v#log_history;