資料庫角色說明
源頭庫: Primary
備援庫: Physical
Logical
檢查1：
從 V$DATAGUARD_STATS 視圖檢查 備援庫的LAG(log傳送lag與apply lag)。
[Physical]
SQL>
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:49
[Logical]
SQL>
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: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
檢查2：
通過遵循序列號，還可以查看重做日誌是否正確發送並應用於備用。
[Primary]
SQL>
select max(sequence#),thread# from v$archived_log group by thread#;
MAX(SEQUENCE#) THREAD#
-------------- ----------
27251 1
22326 2
[Physical]
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
檢查3：
下面的查詢顯示了主要和備用上的最新檔案。
它顯示了在 Primary 中生成的最後一個存檔和應用於 Standby 的最後一個存檔。
SQL>
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#;
檢查4：
從 V$DATAGUARD_STATUS 視圖可以看出 Alert Log 中是否有錯誤。
可以通過在此處選擇嚴重性列中的“錯誤”列來創建警報。
[Physical]
SQL>
column message format a66
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)
檢查5：
Redo Apply 和 Redo Transport 服務的狀態可以從 V$MANAGED_STANDBY 視圖中看到。
[Primary]
SQL>
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]
SQL>
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]
SQL>
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 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.
注意：如果您注意到，作為查詢的結果，在邏輯備用數據庫中沒有執行重做應用作業的 LSP 進程。
原因是 v$managed_standby 視圖對物理備用數據庫有效。
檢查6：
從 V$LOGSTDBY_TRANSACTION 視圖可以看到 SQL APPLY 當前處理的事務的信息。
[Logical]
SQL>
SELECT primary_xid, type,mining_status, apply_status FROM v$logstdby_transaction;
no rows selected
檢查 7：
可以看到哪些檔案是什麼時候傳輸到備用的。
[Physical]
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.
檢查8：
您可以使用以下查詢從 V$STANDBY_LOG 視圖中獲取有關備用重做日誌的信息。
[Primary]
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 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.
[Physical]
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
6 rows selected.
[Logical]
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 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
上述查詢中 ARCHIVED 和 STATUS 列的組合含義如下：
ARC / STATUS
NO UNASSIGNED : Standby Redo Log 已歸檔，適合重複使用。
YES UNASSIGNED : Standby Redo Log 從未使用過，可以使用。
NO ACTIVE : 顯示對備用重做日誌的寫入已完成，備用重做日誌預計將被歸檔。
YES ACTIVE : 顯示備用重做日誌已被積極使用且尚未歸檔。
檢查9：
在備用數據庫沒有收到重做的情況下，我們應該檢查 V$ARCHIVE_DEST。
查詢存檔目的地。
[Primary]
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
[Physical]
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
[Logical]
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
該視圖中VALID_NOW列的值和含義如下；
YES: 此值表明存檔日誌目標已定義並且對當前數據庫角色有效。通常 LOG_ARCHIVE_DEST_1 是這樣定義的。
WRONG VALID_TYPE: 此值顯示已定義存檔日誌目標，但在備用角色中沒有可使用的備用日誌。
WRONG VALID_ROLE: 此值表明存檔日誌目標定義不正確。處於備用角色時無法讀取在線日誌。
UNKNOWN 此值表明存檔日誌目標未定義。
檢查 10：
如果目的地沒有問題，則檢查錯誤。
[Primary]
SQL>
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 INACTIVE
檢查 11：
如果沒有錯誤，則檢查以下內容:
TNSNAMES.ORA 文件，
LOG_ARCHIVE_DEST_n 參數，
LOG_ARCHIVE_DEST_STATE_n 參數，
LISTENER.ORA 文件，
Listener 是否在 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
留言列表
資訊技術 (11)
