DG學習筆記(12)_相關檢視

DG學習筆記(12)_相關檢視

1 如何查詢當前DataGuard使用的模式 v$database
SQL> select NAME,RESETLOGS_TIME,LOG_MODE,OPEN_MODE,PROTECTION_MODE,REMOTE_ARCHIVE,DATABASE_ROLE,SWITCHOVER_STATUS,FORCE_LOGGING from v$database;

 

2 檢視當前LOG_ARCHIVE_DEST_N 引數的設定 V$ARCHIVE_DEST
SQL> select dest_id, dest_name, STATUS,ARCHIVER,DESTINATION,REOPEN_SECS,DELAY_MINS,NET_TIMEOUT,PROCESS,MAX_FAILURE,TRANSMIT_MODE,ASYNC_BLOCKS,AFFIRM,TYPE from v$archive_dest where dest_id = 1;

 

3 查詢是否建立了Standby Redo Log.(Standby端)
SQL> SELECT * FROM V$LOGFILE WHERE TYPE = 'STANDBY';

 

4 查詢當前線上日誌的狀態
SQL> SELECT THREAD#, SEQUENCE#, ARCHIVED, STATUS FROM V$LOG;

 

5 查詢Primary端最新生成的歸檔檔案Sequence#
SQL> SELECT MAX(SEQUENCE#) FROM V$ARCHIVED_LOG;

 

6 比較兩個歸檔路徑是不是同步．
SELECT LOCAL.THREAD#, LOCAL.SEQUENCE#
FROM
 (SELECT THREAD#, SEQUENCE# FROM V$ARCHIVED_LOG WHERE DEST_ID=1) LOCAL
  WHERE LOCAL.SEQUENCE# NOT IN (SELECT SEQUENCE# FROM V$ARCHIVED_LOG WHERE DEST_ID=2 AND THREAD# = LOCAL.THREAD#);

如沒資料返回，則表示同步的．

 

7 檢視DG相關程式的狀態.(Primary ,Standby　端分別執行)
SELECT PROCESS, STATUS, THREAD#, SEQUENCE#, BLOCK#, BLOCKS FROM V$MANAGED_STANDBY;

 

8 查詢從Primary接收到的所有歸檔(Standby 端)
SQL> SELECT REGISTRAR, CREATOR, THREAD#, SEQUENCE#, FIRST_CHANGE#, NEXT_CHANGE# FROM V$ARCHIVED_LOG;

10 查詢已應用的歸檔(Standby 端).V$LOG_HISTORY
SQL> SELECT THREAD#, SEQUENCE#, FIRST_CHANGE#, NEXT_CHANGE#  FROM V$LOG_HISTORY;

SQL> SELECT THREAD#, MAX(SEQUENCE#) AS "LAST_APPLIED_LOG" FROM V$LOG_HISTORY GROUP BY THREAD#;

SQL> SELECT THREAD#, SEQUENCE#, APPLIED FROM V$ARCHIVED_LOG;

11 查詢沒有傳輸過去的歸檔(Primary端)
SQL> SELECT LOCAL.THREAD#, LOCAL.SEQUENCE# FROM (SELECT THREAD#, SEQUENCE# FROM V$ARCHIVED_LOG WHERE DEST_ID=1) LOCAL
  WHERE
 LOCAL.SEQUENCE# NOT IN
 (SELECT SEQUENCE# FROM V$ARCHIVED_LOG WHERE DEST_ID=2 AND THREAD# = LOCAL.THREAD#);

12 查詢主,備是否存在GAP
SQL> select * from v$archive_gap;