We will check data guard status in SQL Plus in this article in many ways.
Check 1:
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;
NAME VALUE TIME_COMPUTED
------------------------- -------------------- --------------------
transport lag +00 00:00:00 01/16/2017 14:18:49
apply lag +00 00:00:00 01/16/2017 14:18:49
apply finish time +00 00:00:00.000 01/16/2017 14:18:49
estimated startup time 29 01/16/2017 14:18:49Check 2:
It is also possible to see whether Redo Logs are sent and applied to Standby correctly or not, by following the Sequence numbers.
[Primary-1 ]
SQL> select max(sequence#),thread# from v$archived_log group by thread#;
MAX(SEQUENCE#) THREAD#
-------------- ----------
27251 1
22326 2
[Physical-1]
SQL> select max(sequence#),thread#, applied from v$archived_log group by thread#, applied;
MAX(SEQUENCE#) THREAD# APPLIED
-------------- ---------- ---------
27250 1 YES
27251 1 IN-MEMORY
22326 2 YESCheck 3:
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#;Check 4:
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.
[Physical-1] SQL> column message format a66
[Physical-1] SQL> SELECT timestamp, facility, message FROM v$dataguard_status ORDER by timestamp;
TIMESTAMP FACILITY MESSAGE
--------- ------------------------ --------------------------------------------------
15-JAN-17 Log Apply Services Managed Standby Recovery not using Real Time Apply
16-JAN-17 Remote File Server RFS[50]: Assigned to RFS process 28009
16-JAN-17 Remote File Server Primary database is in MAXIMUM PERFORMANCE mode
16-JAN-17 Log Transport Services ARC5: Completed archiving thread 1 sequence 575 (0-0)
16-JAN-17 Log Transport Services ARC6: Beginning to archive thread 2 sequence 339 (6091863-6091873)
16-JAN-17 Log Transport Services ARC6: Completed archiving thread 2 sequence 339 (0-0)
Check 5:
The status of Redo Apply and Redo Transport services can be seen from V$MANAGED_STANDBY View.
[Primary-1]
column group# format a10
SELECT process, status, group#, thread#, sequence# FROM v$managed_standby order by process, group#, thread#, sequence#;
PROCESS STATUS GROUP# THREAD# SEQUENCE#
--------- ------------ ---------- ---------- ----------
ARCH CLOSING 1 1 561
ARCH CLOSING 1 1 565
ARCH CLOSING 1 1 569
ARCH CLOSING 1 1 573
ARCH CLOSING 1 1 575
ARCH CLOSING N/A 1 538
ARCH CLOSING N/A 1 561
ARCH CLOSING N/A 1 569
LGWR CLOSING 2 1 568
LNS WRITING 2 1 576
LNS WRITING 2 1 576
11 rows selected.
[Physical-1]
column group# format a10
SELECT process, status, group#, thread#, sequence# FROM v$managed_standby order by process, group#, thread#, sequence#;
PROCESS STATUS GROUP# THREAD# SEQUENCE#
--------- ------------ ---------- ---------- ----------
ARCH CLOSING 5 1 571
ARCH CONNECTED N/A 0 0
ARCH CONNECTED N/A 0 0
ARCH CONNECTED N/A 0 0
ARCH CONNECTED N/A 0 0
ARCH CONNECTED N/A 0 0
ARCH CONNECTED N/A 0 0
ARCH CONNECTED N/A 0 0
MRP0 APPLYING_LOG N/A 2 340
RFS IDLE N/A 0 0
RFS IDLE N/A 0 0
11 rows selected.
[Logical-1]
column group# format a10
SELECT process, status, group#, thread#, sequence# FROM v$managed_standby order by process, group#, thread#, sequence#;
PROCESS STATUS GROUP# THREAD# SEQUENCE#
--------- ------------ ---------- ---------- ----------
ARCH CLOSING 1 1 95
ARCH CLOSING 5 1 574
ARCH CLOSING 6 1 573
ARCH CLOSING 8 2 332
ARCH CLOSING 8 2 334
ARCH CLOSING 9 2 333
ARCH CLOSING 9 2 335
ARCH CONNECTED N/A 0 0
RFS IDLE N/A 0 0
RFS IDLE N/A 0 0
RFS IDLE N/A 0 0NOTE: 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.
Check 6:
The information of the transactions currently processed by SQL APPLY can be seen from the V$LOGSTDBY_TRANSACTION View.
[Logical-1]
SELECT primary_xid, type,mining_status, apply_status FROM v$logstdby_transaction;
no rows selectedCheck 7:
It can be seen which archives are transmitted to the standby when.
[Physical-1]
select THREAD#,sequence#, round((blocks*block_size)/1024/1024) as round_block_size, first_time, next_time, completion_time from gv$archived_log where REGISTRAR='RFS' order by 6;
THREAD# SEQUENCE# ROUND_BLOCK_SIZE FIRST_TIM NEXT_TIME COMPLETION
------- ---------- ---------------- --------- ----------- ----------
1 79450 656 15-JAN-22 15-JAN-22 15-JAN-22
2 96900 656 15-JAN-22 15-JAN-22 15-JAN-22
2 96900 656 15-JAN-22 15-JAN-22 15-JAN-22
1 79451 656 15-JAN-22 15-JAN-22 15-JAN-22
1 79451 655 15-JAN-22 15-JAN-22 15-JAN-22
2 96901 656 15-JAN-22 15-JAN-22 15-JAN-22
2 96901 667 15-JAN-22 15-JAN-22 15-JAN-22
1866 rows selected.Check 8:
You can get information about Standby Redo Logs from V$STANDBY_LOG View by using the following queries.
[Primary-1]
set linesize 9000
column dbid format a15
SELECT group#, thread#, sequence#, dbid, archived, status FROM v$standby_log;
GROUP# THREAD# SEQUENCE# DBID ARC STATUS
---------- ---------- ---------- --------------- --- ----------
5 1 0 UNASSIGNED YES UNASSIGNED
6 1 0 UNASSIGNED YES UNASSIGNED
7 1 0 UNASSIGNED YES UNASSIGNED
8 2 0 UNASSIGNED YES UNASSIGNED
9 2 0 UNASSIGNED YES UNASSIGNED
10 2 0 UNASSIGNED YES UNASSIGNED
[Physical-1]
SQL> set linesize 9000
column dbid format a15
SELECT group#, thread#, sequence#, dbid, archived, status FROM v$standby_log;
GROUP# THREAD# SEQUENCE# DBID ARC STATUS
---------- ---------- ---------- --------------- --- ----------
5 1 0 UNASSIGNED NO UNASSIGNED
6 1 576 1769705496 YES ACTIVE
7 1 0 UNASSIGNED NO UNASSIGNED
8 2 340 1769705496 YES ACTIVE
9 2 0 UNASSIGNED NO UNASSIGNED
10 2 0 UNASSIGNED YES UNASSIGNED
[Logical-1]
set linesize 9000
column dbid format a15
SELECT group#, thread#, sequence#, dbid, archived, status FROM v$standby_log;
GROUP# THREAD# SEQUENCE# DBID ARC STATUS
---------- ---------- ---------- --------------- --- ----------
5 1 576 1769705496 YES ACTIVE
6 1 0 UNASSIGNED NO UNASSIGNED
7 1 0 UNASSIGNED NO UNASSIGNED
8 2 340 1769705496 YES ACTIVE
9 2 0 UNASSIGNED NO UNASSIGNED
10 2 0 UNASSIGNED NO UNASSIGNED
The combinations of the ARCHIVED and STATUS columns in the above query mean the following;
ARC STATUS EXPLANATION
NO UNASSIGNED Standby Redo Log has been archived and is suitable for reuse.
YES UNASSIGNED Standby Redo Log has never been used and is ready for use.
NO ACTIVE Shows that writing to Standby Redo Log is finished and Standby Redo Log is expected to be archived.
YES ACTIVE Shows that Standby Redo Log is actively used and is not yet archived.Check 9:
In cases where Standby Database does not receive Redos, we should check V$ARCHIVE_DEST.
Query ARCHIVE DESTINATION.
[Primary-1]
SELECT dest_id,valid_type,valid_role,valid_now FROM v$archive_dest;
DEST_ID VALID_TYPE VALID_ROLE VALID_NOW
---------- --------------- ------------ ----------------
1 ALL_LOGFILES ALL_ROLES YES
2 ALL_LOGFILES PRIMARY_ROLE YES
3 ALL_LOGFILES PRIMARY_ROLE YES
4 ALL_LOGFILES ALL_ROLES UNKNOWN
[Physical-1]
SELECT dest_id,valid_type,valid_role,valid_now FROM v$archive_dest;
DEST_ID VALID_TYPE VALID_ROLE VALID_NOW
---------- --------------- ------------ ----------------
1 ALL_LOGFILES ALL_ROLES YES
2 ALL_LOGFILES ALL_ROLES UNKNOWN
[Logical-1]
SELECT dest_id,valid_type,valid_role,valid_now FROM v$archive_dest;
DEST_ID VALID_TYPE VALID_ROLE VALID_NOW
---------- --------------- ------------ ----------------
1 ALL_LOGFILES ALL_ROLES YES
2 ALL_LOGFILES ALL_ROLES UNKNOWNValues and meanings of the VALID_NOW column in this View are as follows;
YES This value shows that 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 This value shows that the Archive log destination has been defined, but there are no Standby Logs to use when in Standby Role.
WRONG_VALID_ROLE This value shows that the Archive log destination is incorrectly defined. Online Logs cannot be read while in Standby Role.
UNKNOWN This value shows that the Archive Log destination is not defined.Check 10:
If there are no problems in the destinations, errors are checked.
[Primary-1]
column destination format a30
column error format a20
set linesize 9000
SELECT DEST_ID, STATUS, DESTINATION, ERROR FROM V$ARCHIVE_DEST WHERE DEST_ID <=5;
DEST_ID STATUS DESTINATION ERROR
--------- --------- ------------------------------ --------------------
1 VALID USE_DB_RECOVERY_FILE_DEST
2 VALID standby
3 VALID logical
4 INACTIVE
5 INACTIVECheck 11:
If there is no error, the following are checked.
- 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.
[Physical-1]
Queries to Check Standby
SQL> SELECT SEQUENCE#,APPLIED FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;
SEQUENCE# APPLIED
---------- ---------
10033 YES
10034 YES
10035 YES
10036 YES
10037 YES
10038 YES
SQL> SELECT MESSAGE FROM V$DATAGUARD_STATUS;
MESSAGE
--------------------------------------------------------------------------------
ARCj: Beginning to archive thread 1 sequence 10037 (12423487630539-1242348971229
8)
ARCj: Completed archiving thread 1 sequence 10037 (0-0)
Media Recovery Log +FRA/mwstdbydb/archivelog/2015_12_29/thread_1_seq_10037.862.8
99733563
Media Recovery Waiting for thread 2 sequence 7938 (in transit)
ARC0: Beginning to archive thread 2 sequence 7938 (12423487903683-12423490247632
)
MESSAGE
--------------------------------------------------------------------------------
ARC0: Completed archiving thread 2 sequence 7938 (0-0)
Media Recovery Log +FRA/mwstdbydb/archivelog/2015_12_29/thread_2_seq_7938.863.89
9734925
Media Recovery Waiting for thread 1 sequence 10038 (in transit)
ARC1: Beginning to archive thread 1 sequence 10038 (12423489712298-1242349181804
2)
ARC1: Completed archiving thread 1 sequence 10038 (0-0)
Media Recovery Log +FRA/mwstdbydb/archivelog/2015_12_29/thread_1_seq_10038.864.8
99738951
MESSAGE
--------------------------------------------------------------------------------
Media Recovery Waiting for thread 2 sequence 7939 (in transit)
ARC3: Beginning to archive thread 2 sequence 7939 (12423490247632-12423492561200
)
ARC3: Completed archiving thread 2 sequence 7939 (0-0)
Media Recovery Log +FRA/mwstdbydb/archivelog/2015_12_29/thread_2_seq_7939.865.89
9740831
Media Recovery Waiting for thread 1 sequence 10039 (in transit)
SQL> select to_char(current_scn) from v$database;
TO_CHAR(CURRENT_SCN)
----------------------------------------
12423492561199
SQL> select scn_to_timestamp(1681797) from dual; PROD
SCN_TO_TIMESTAMP(12423492561199)
---------------------------------------------------------------------------
29-DEC-15 04.00.31.000000000 PM
SQL> select thread#, sequence#, status from v$log; PROD
SCN_TO_TIMESTAMP(12423492561199)
---------------------------------------------------------------------------
29-DEC-15 04.00.31.000000000 PM
SQL> select thread#, sequence#, status from v$log;
THREAD# SEQUENCE# STATUS
---------- ---------- ----------------
1 10038 INACTIVE
1 10039 INACTIVE
1 10040 CURRENT
1 10036 INACTIVE
1 10037 INACTIVE
2 7938 INACTIVE
2 7939 INACTIVE
2 7940 CURRENT
2 7936 INACTIVE
2 7937 INACTIVE
SQL> select process, status, thread#, sequence#, block#, blocks from v$managed_standby;
PROCESS STATUS THREAD# SEQUENCE# BLOCK# BLOCKS
--------- ------------ ---------- ---------- ---------- ----------
ARCH CLOSING 2 7938 1613824 582
ARCH CLOSING 1 10038 1613824 648
ARCH CONNECTED 0 0 0 0
ARCH CLOSING 2 7939 1613824 510
ARCH CLOSING 1 10039 1613824 171
ARCH CLOSING 1 10030 1613824 1800
ARCH CLOSING 2 7931 1613824 796
ARCH CLOSING 2 7932 71680 1080
ARCH CLOSING 1 10031 829440 1043
ARCH CLOSING 2 7933 1613824 271
ARCH CLOSING 1 10032 1613824 38
PROCESS STATUS THREAD# SEQUENCE# BLOCK# BLOCKS
--------- ------------ ---------- ---------- ---------- ----------
ARCH CLOSING 2 7934 1695744 239
ARCH CLOSING 1 10033 1613824 11
ARCH CLOSING 2 7935 1613824 191
ARCH CLOSING 1 10034 1613824 332
ARCH CLOSING 1 10035 1318912 497
ARCH CLOSING 2 7936 1230848 236
ARCH CLOSING 1 10036 1611776 1964
ARCH CLOSING 2 7937 1613824 70
ARCH CLOSING 1 10037 1613824 90
RFS IDLE 0 0 0 0
RFS IDLE 0 0 0 0
PROCESS STATUS THREAD# SEQUENCE# BLOCK# BLOCKS
--------- ------------ ---------- ---------- ---------- ----------
RFS IDLE 1 10040 181017 1
RFS IDLE 2 7940 1204818 2
RFS IDLE 0 0 0 0
RFS IDLE 0 0 0 0
MRP0 WAIT_FOR_LOG 2 7940 0 0
SQL> select * from V$dataguard_Stats;
NAME VALUE UNIT TIME_COMPUTED DATUM_TIME
-------------------------------- ---------------------------------------------------------------- ------------------------------ ------------------------------ ------------------------------
transport lag +00 00:00:00 day(2) to second(0) interval 12/29/2015 17:02:32 12/29/2015 17:02:31
apply lag +00 01:02:00 day(2) to second(0) interval 12/29/2015 17:02:32 12/29/2015 17:02:31
apply finish time +00 00:00:04.884 day(2) to second(3) interval 12/29/2015 17:02:32
estimated startup time 45 second 12/29/2015 17:02:32
SQL> SELECT FIRST_TIME, TO_CHAR(FIRST_CHANGE#), TO_CHAR(NEXT_CHANGE#), SEQUENCE# FROM V$LOG_HISTORY;
FIRST_TIM TO_CHAR(FIRST_CHANGE#) TO_CHAR(NEXT_CHANGE#) SEQUENCE#
--------- ---------------------------------------- ---------------------------------------- ----------
29-DEC-15 12423479700143 12423481704223 10033
29-DEC-15 12423481518592 12423483684832 7935
29-DEC-15 12423481704223 12423483761666 10034
29-DEC-15 12423483761666 12423485543281 10035
29-DEC-15 12423483684832 12423485543694 7936
29-DEC-15 12423485543281 12423487630539 10036
29-DEC-15 12423485543694 12423487903683 7937
29-DEC-15 12423487630539 12423489712298 10037
29-DEC-15 12423487903683 12423490247632 7938
29-DEC-15 12423489712298 12423491818042 10038
29-DEC-15 12423490247632 12423492561200 7939
SQL> SELECT SEQUENCE#,APPLIED FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;
SEQUENCE# APPLIED
---------- ---------
10033 YES
10034 YES
10035 YES
10036 YES
10037 YES
10038 YES
SQL> SELECT MESSAGE FROM V$DATAGUARD_STATUS;
MESSAGE
--------------------------------------------------------------------------------
ARCj: Beginning to archive thread 1 sequence 10037 (12423487630539-1242348971229
8)
ARCj: Completed archiving thread 1 sequence 10037 (0-0)
Media Recovery Log +FRA/mwstdbydb/archivelog/2015_12_29/thread_1_seq_10037.862.8
99733563
Media Recovery Waiting for thread 2 sequence 7938 (in transit)
ARC0: Beginning to archive thread 2 sequence 7938 (12423487903683-12423490247632
)
MESSAGE
--------------------------------------------------------------------------------
ARC0: Completed archiving thread 2 sequence 7938 (0-0)
Media Recovery Log +FRA/mwstdbydb/archivelog/2015_12_29/thread_2_seq_7938.863.89
9734925
Media Recovery Waiting for thread 1 sequence 10038 (in transit)
ARC1: Beginning to archive thread 1 sequence 10038 (12423489712298-1242349181804
2)
ARC1: Completed archiving thread 1 sequence 10038 (0-0)
Media Recovery Log +FRA/mwstdbydb/archivelog/2015_12_29/thread_1_seq_10038.864.8
99738951
MESSAGE
--------------------------------------------------------------------------------
Media Recovery Waiting for thread 2 sequence 7939 (in transit)
ARC3: Beginning to archive thread 2 sequence 7939 (12423490247632-12423492561200
)
ARC3: Completed archiving thread 2 sequence 7939 (0-0)
Media Recovery Log +FRA/mwstdbydb/archivelog/2015_12_29/thread_2_seq_7939.865.89
9740831
Media Recovery Waiting for thread 1 sequence 10039 (in transit)
SQL> select to_char(current_scn) from v$database;
TO_CHAR(CURRENT_SCN)
----------------------------------------
12423492561199
SQL> select scn_to_timestamp(1681797) from dual; PROD
SCN_TO_TIMESTAMP(12423492561199)
---------------------------------------------------------------------------
29-DEC-15 04.00.31.000000000 PM
SQL> select thread#, sequence#, status from v$log; PROD
SCN_TO_TIMESTAMP(12423492561199)
---------------------------------------------------------------------------
29-DEC-15 04.00.31.000000000 PM
SQL> select thread#, sequence#, status from v$log;
THREAD# SEQUENCE# STATUS
---------- ---------- ----------------
1 10038 INACTIVE
1 10039 INACTIVE
1 10040 CURRENT
1 10036 INACTIVE
1 10037 INACTIVE
2 7938 INACTIVE
2 7939 INACTIVE
2 7940 CURRENT
2 7936 INACTIVE
2 7937 INACTIVE
SQL> select process, status, thread#, sequence#, block#, blocks from v$managed_standby;
PROCESS STATUS THREAD# SEQUENCE# BLOCK# BLOCKS
--------- ------------ ---------- ---------- ---------- ----------
ARCH CLOSING 2 7938 1613824 582
ARCH CLOSING 1 10038 1613824 648
ARCH CONNECTED 0 0 0 0
ARCH CLOSING 2 7939 1613824 510
ARCH CLOSING 1 10039 1613824 171
ARCH CLOSING 1 10030 1613824 1800
ARCH CLOSING 2 7931 1613824 796
ARCH CLOSING 2 7932 71680 1080
ARCH CLOSING 1 10031 829440 1043
ARCH CLOSING 2 7933 1613824 271
ARCH CLOSING 1 10032 1613824 38
PROCESS STATUS THREAD# SEQUENCE# BLOCK# BLOCKS
--------- ------------ ---------- ---------- ---------- ----------
ARCH CLOSING 2 7934 1695744 239
ARCH CLOSING 1 10033 1613824 11
ARCH CLOSING 2 7935 1613824 191
ARCH CLOSING 1 10034 1613824 332
ARCH CLOSING 1 10035 1318912 497
ARCH CLOSING 2 7936 1230848 236
ARCH CLOSING 1 10036 1611776 1964
ARCH CLOSING 2 7937 1613824 70
ARCH CLOSING 1 10037 1613824 90
RFS IDLE 0 0 0 0
RFS IDLE 0 0 0 0
PROCESS STATUS THREAD# SEQUENCE# BLOCK# BLOCKS
--------- ------------ ---------- ---------- ---------- ----------
RFS IDLE 1 10040 181017 1
RFS IDLE 2 7940 1204818 2
RFS IDLE 0 0 0 0
RFS IDLE 0 0 0 0
MRP0 WAIT_FOR_LOG 2 7940 0 0
SQL> select * from V$dataguard_Stats;
NAME VALUE UNIT TIME_COMPUTED DATUM_TIME
-------------------------------- -------------------------------------------- ------------------------------ ------------------------------ ------------------------------
transport lag +00 00:00:00 day(2) to second(0) interval 12/29/2015 17:02:32 12/29/2015 17:02:31
apply lag +00 01:02:00 day(2) to second(0) interval 12/29/2015 17:02:32 12/29/2015 17:02:31
apply finish time +00 00:00:04.884 day(2) to second(3) interval 12/29/2015 17:02:32
estimated startup time 45 second 12/29/2015 17:02:32
SQL> SELECT FIRST_TIME, TO_CHAR(FIRST_CHANGE#) as FIRST_CHANGE, TO_CHAR(NEXT_CHANGE#) as NEXT_CHANGE, SEQUENCE# FROM V$LOG_HISTORY;
FIRST_TIME FIRST_CHANGE NEXT_CHANGE SEQUENCE#
-------------------- ---------------------------------------------------------
29-DEC-15 12423479700143 12423481704223 10033
29-DEC-15 12423481518592 12423483684832 7935
29-DEC-15 12423481704223 12423483761666 10034
29-DEC-15 12423483761666 12423485543281 10035
29-DEC-15 12423483684832 12423485543694 7936
29-DEC-15 12423485543281 12423487630539 10036
29-DEC-15 12423485543694 12423487903683 7937
29-DEC-15 12423487630539 12423489712298 10037
29-DEC-15 12423487903683 12423490247632 7938
29-DEC-15 12423489712298 12423491818042 10038
29-DEC-15 12423490247632 12423492561200