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;