Database Tutorials MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux 
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.
1 2 3 4 5 6 7 8 9 10 11 12 | [Physical-1] SQL> set linesize 9000 [Physical-1] SQL> column name format a25 [Physical-1] SQL> column value format a20 [Physical-1] SQL> column time_computed format a25 [Physical-1] SQL> 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:49 |
1 2 3 4 5 6 7 8 9 10 11 12 | [Logical-1] SQL> set linesize 9000 [Logical-1] SQL> column name format a25 [Logical-1] SQL> column value format a20 [Logical-1] SQL> column time_computed format a25 [Logical-1] SQL> SELECT name, value, time_computed FROM v$dataguard_stats; NAME VALUE TIME_COMPUTED ------------------------- -------------------- ------------------------- transport lag +00 00:00:00 01/16/2017 14:18:24 apply lag +00 02:01:50 01/16/2017 14:18:24 apply finish time 01/16/2017 14:18:24 estimated startup time 25 01/16/2017 14:18:24 |
Check 2:
It is also possible to see whether Redo Logs are sent and applied to Standby correctly or not, by following the Sequence numbers.
1 2 3 4 5 6 | [Primary-1 ]SQL> select max(sequence#),thread# from v$archived_log group by thread#; MAX(SEQUENCE#) THREAD# -------------- ---------- 27251 1 22326 2 |
1 2 3 4 5 6 7 | [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 YES |
Check 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.
1 2 3 4 5 6 7 8 9 10 11 | 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.
1 2 3 4 5 6 7 8 9 10 11 | [Physical-2] SQL> column message format a66 [Physical-2] 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.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 | [Primary-1] SQL> column group# format a10 [Primary-1] SQL> 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. |
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 | [Physical-1] SQL> column group# format a10 [Physical-1] SQL> 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. |
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 | [Logical-1] SQL> column group# format a10 [Logical-1] SQL> 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 0 PROCESS STATUS GROUP# THREAD# SEQUENCE# --------- ------------ ---------- ---------- ---------- RFS IDLE N/A 0 0 RFS IDLE N/A 0 0 RFS IDLE N/A 0 0 RFS IDLE N/A 0 0 RFS IDLE N/A 0 0 16 rows selected. |
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.
Check 6:
The information of the transactions currently processed by SQL APPLY can be seen from the V$LOGSTDBY_TRANSACTION View.
1 2 3 | [Logical-1] SQL> SELECT primary_xid, type,mining_status, apply_status FROM v$logstdby_transaction; no rows selected |
Check 7:
It can be seen which archives are transmitted to the standby when.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 | [Physical-1] SQL>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; . . . THREAD# SEQUENCE# ROUND((BLOCKS*BLOCK_SIZE)/1024/1024) FIRST_TIM NEXT_TIME COMPLETIO ---------- ---------- ------------------------------------ --------- --------- --------- 1 79450 656 15-JAN-19 15-JAN-19 15-JAN-19 2 96900 665 15-JAN-19 15-JAN-19 15-JAN-19 2 96900 665 15-JAN-19 15-JAN-19 15-JAN-19 1 79451 655 15-JAN-19 15-JAN-19 15-JAN-19 1 79451 655 15-JAN-19 15-JAN-19 15-JAN-19 2 96901 667 15-JAN-19 15-JAN-19 15-JAN-19 2 96901 667 15-JAN-19 15-JAN-19 15-JAN-19 1866 rows selected. |
Check 8:
You can get information about Standby Redo Logs from V$STANDBY_LOG View by using the following queries.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | [Primary-1] SQL> set linesize 9000 [Primary-1] SQL> column dbid format a15 [Primary-1] SQL> 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 6 rows selected. |
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | [Physical-1] SQL> set linesize 9000 [Physical-1] SQL> column dbid format a15 [Physical-1] SQL> 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 6 rows selected. |
1 2 3 4 5 6 7 8 9 10 11 12 | [Logical-1] SQL> set linesize 9000 [Logical-1] SQL> column dbid format a15 [Logical-1] SQL> 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
| 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.
1 2 3 4 5 6 7 8 | [Primary-1] SQL> 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 |
1 2 3 4 5 6 | [Physical-1] SQL> 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 |
1 2 3 4 5 6 | [Logical-1] SQL> 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 |
Values 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.
1 2 3 4 5 6 7 8 9 10 11 12 | [Primary-1] SQL> column destination format a30 [Primary-1] SQL> column error format a20 [Primary-1] SQL> set linesize 9000 [Primary-1] SQL> 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 INACTIVE |
Check 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.
Queries to Check Standby
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 | 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 |
Hi Onur.
Thanks for the article. Goog stuff. Helpful. Good formatting too.
Could you please translate Tukish to English in “Check 3”? Thanks
Thanks. I translated Check 3 to English.
teşekkür ederim (Google Translate 🙂 )Thanks for the translation, Onur. Gr8 website.
Hi Onur.
Thank you very much for the sharing such informative and helpful article.
Thank for sharing! This post is very useful!
Thank for sharing! This post is very useful!