Data Guard相關的動態效能檢視
Data Guard相關的動態效能檢視
1.檢視程式的活動狀況---v$managed_standby
該檢視就是專為顯示standby相關程式的當前狀態資訊，例如：
[]
SQL> select process,client_process,sequence#,status from v$managed_standby;
PROCESS CLIENT_P SEQUENCE# STATUS
--------- -------- ---------- ------------
ARCH ARCH 39 CLOSING
ARCH ARCH 40 CLOSING
RFS LGWR 41 IDLE
RFS ARCH 0 IDLE
MRP0 N/A 41 WAIT_FOR_LOG
RFS N/A 0 IDLE
6 rows selected.
透過上述查詢可以得知primary開了兩個歸檔程式，使用lgwr同步傳輸方式與standby通訊，已經接收完40的日誌，正等待41。
2.確認redo應用進度---v$archive_dest_status
該檢視顯示歸檔檔案路徑配置資訊及redo的應用情況等，例如：
[html]
SQL> select dest_name,archived_thread#,archived_seq#,applied_thread#,applied_seq#,db_unique_name from v$archive_dest_status where status='VALID';
DEST_NAME ARCHIVED_THREAD# ARCHIVED_SEQ# APPLIED_THREAD# APPLIED_SEQ# DB_UNIQUE_NAME
-------------------- ---------------- ------------- --------------- ------------ ------------------------------
LOG_ARCHIVE_DEST_1 1 40 0 0 10gstandby
LOG_ARCHIVE_DEST_2 0 0 0 0 10gpri
STANDBY_ARCHIVE_DEST 1 39 1 39 NONE
3.檢查歸檔檔案路徑及建立資訊---v$archived_log
該檢視查詢standby資料庫歸檔檔案的一些附加資訊，比如檔案建立時間啦，建立程式啦，歸檔序號啦，是否被應用啦之類，例如：
[html]
SQL> select name,creator,sequence#,applied,completion_time from v$archived_log;
NAME CREATOR SEQUENCE# APP COMPLETIO
---------------------------------------------------------------------- ------- ---------- --- ---------
/u01/app/oracle/oradata/orcl/archivelog/1_13_793805797.dbf ARCH 13 YES 20-SEP-12
/u01/app/oracle/oradata/orcl/archivelog/1_14_793805797.dbf ARCH 14 YES 20-SEP-12
/u01/app/oracle/oradata/orcl/archivelog/1_15_793805797.dbf ARCH 15 YES 20-SEP-12
/u01/app/oracle/oradata/orcl/archivelog/1_16_793805797.dbf ARCH 16 YES 20-SEP-12
/u01/app/oracle/oradata/orcl/archivelog/1_17_793805797.dbf ARCH 17 YES 20-SEP-12
/u01/app/oracle/oradata/orcl/archivelog/1_18_793805797.dbf ARCH 18 YES 20-SEP-12
/u01/app/oracle/oradata/orcl/archivelog/1_19_793805797.dbf ARCH 19 YES 20-SEP-12
/u01/app/oracle/oradata/orcl/archivelog/1_20_793805797.dbf ARCH 20 YES 20-SEP-12
/u01/app/oracle/oradata/orcl/archivelog/1_21_793805797.dbf ARCH 21 YES 20-SEP-12
/u01/app/oracle/oradata/orcl/archivelog/1_22_793805797.dbf ARCH 22 YES 17-JAN-13
/u01/app/oracle/oradata/orcl/archivelog/1_23_793805797.dbf ARCH 23 YES 17-JAN-13
/u01/app/oracle/oradata/orcl/archivelog/1_24_793805797.dbf ARCH 24 YES 17-JAN-13
/u01/app/oracle/oradata/orcl/archivelog/1_25_793805797.dbf ARCH 25 YES 17-JAN-13
/u01/app/oracle/oradata/orcl/archivelog/1_26_793805797.dbf ARCH 26 YES 17-JAN-13
/u01/app/oracle/oradata/orcl/archivelog/1_27_793805797.dbf ARCH 27 YES 17-JAN-13
/u01/app/oracle/oradata/orcl/archivelog/1_28_793805797.dbf ARCH 28 YES 17-JAN-13
/u01/app/oracle/oradata/orcl/archivelog/1_29_793805797.dbf ARCH 29 YES 17-JAN-13
/u01/app/oracle/oradata/orcl/archivelog/1_30_793805797.dbf ARCH 30 YES 17-JAN-13
/u01/app/oracle/oradata/orcl/archivelog/1_31_793805797.dbf ARCH 31 YES 17-JAN-13
/u01/app/oracle/oradata/orcl/archivelog/1_32_793805797.dbf ARCH 32 YES 17-JAN-13
/u01/app/oracle/oradata/orcl/archivelog/1_33_793805797.dbf ARCH 33 YES 17-JAN-13
/u01/app/oracle/oradata/orcl/archivelog/1_34_793805797.dbf ARCH 34 YES 17-JAN-13
/u01/app/oracle/oradata/orcl/archivelog/1_35_793805797.dbf ARCH 35 YES 17-JAN-13
/u01/app/oracle/oradata/orcl/archivelog/1_36_793805797.dbf ARCH 36 YES 17-JAN-13
/u01/app/oracle/oradata/orcl/archivelog/1_37_793805797.dbf ARCH 37 YES 17-JAN-13
/u01/app/oracle/oradata/orcl/archivelog/1_38_793805797.dbf ARCH 38 YES 17-JAN-13
/u01/app/oracle/oradata/orcl/archivelog/1_39_793805797.dbf ARCH 39 YES 17-JAN-13
/u01/app/oracle/oradata/orcl/archivelog/1_40_793805797.dbf ARCH 40 YES 18-JAN-13
28 rows selected.
4.查詢歸檔歷史---v$log_history
該檢視查詢standby庫中所有已被應用的歸檔檔案資訊(不論該歸檔檔案是否還存在)，例如：
[html]
SQL> select first_time,first_change#,next_change#,sequence# from v$log_history;
FIRST_TIM FIRST_CHANGE# NEXT_CHANGE# SEQUENCE#
--------- ------------- ------------ ----------
12-SEP-12 446075 477821 1
12-SEP-12 477821 500567 2
20-SEP-12 500567 500632 3
20-SEP-12 500632 500635 4
20-SEP-12 500635 501761 5
20-SEP-12 501761 501943 6
20-SEP-12 501943 507264 7
20-SEP-12 507264 509553 8
20-SEP-12 509553 510248 9
20-SEP-12 510248 510707 10
20-SEP-12 510707 510708 11
20-SEP-12 510708 511515 12
20-SEP-12 511515 531605 13
20-SEP-12 531605 532507 14
20-SEP-12 532507 532606 15
20-SEP-12 532606 532667 16
20-SEP-12 532667 532717 17
20-SEP-12 532717 532730 18
20-SEP-12 532730 532969 19
20-SEP-12 532969 534190 20
20-SEP-12 534190 534200 21
20-SEP-12 534200 534324 22
20-SEP-12 534324 534724 23
17-JAN-13 534724 536874 24
17-JAN-13 536874 539504 25
17-JAN-13 539504 539620 26
17-JAN-13 539620 539739 27
17-JAN-13 539739 539821 28
17-JAN-13 539821 539884 29
17-JAN-13 539884 541323 30
17-JAN-13 541323 541324 31
17-JAN-13 541324 543867 32
17-JAN-13 543867 546890 33
17-JAN-13 546890 546902 34
17-JAN-13 546902 546917 35
17-JAN-13 546917 546992 36
17-JAN-13 546992 546993 37
17-JAN-13 546993 547203 38
17-JAN-13 547203 547225 39
17-JAN-13 547225 585657 40
40 rows selected.
5.查詢當前資料的基本資訊---v$database資訊。
例如，查詢資料庫角色，保護模式，保護級別等：
[html]
SQL> select database_role,db_unique_name,open_mode,protection_mode,protection_level,switchover_status from v$database;
DATABASE_ROLE DB_UNIQUE_NAME OPEN_MODE PROTECTION_MODE PROTECTION_LEVEL SWITCHOVER_STATUS
---------------- ------------------------------ ---------- -------------------- -------------------- --------------------
PHYSICAL STANDBY 10gstandby MOUNTED MAXIMUM AVAILABILITY MAXIMUM AVAILABILITY NOT ALLOWED
6.檢查應用模式(是否啟用了實時應用)---v$archive_dest_status
查詢v$archive_dest_status檢視，如果開啟了實時應用，則recovery_mode會顯示為：MANAGED REAL TIME APPLY，例如：
[html]
SQL> select recovery_mode from v$archive_dest_status where dest_id=2;
RECOVERY_MODE
-----------------------
MANAGED
7.Data guard事件---v$dataguard_status
該檢視顯示那些被自動觸發寫入alert.log或伺服器trace檔案的事件。通常是在你不便訪問到伺服器查詢alert.log時，可以臨時訪問本檢視檢視一些與dataguard相關的資訊，例如：
[html]
SQL> select message from v$dataguard_status;
MESSAGE
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
ARC0: Archival started
ARC1: Archival started
ARC0: Becoming the 'no FAL' ARCH
ARC0: Becoming the 'no SRL' ARCH
ARC1: Becoming the heartbeat ARCH
Redo Shipping Client Connected as PUBLIC
-- Connected User is Valid
RFS[1]: Assigned to RFS process 23222
RFS[1]: Identified database type as 'physical standby'
Primary database is in MAXIMUM AVAILABILITY mode
Changing standby controlfile to MAXIMUM AVAILABILITY mode
Redo Shipping Client Connected as PUBLIC
-- Connected User is Valid
RFS[2]: Assigned to RFS process 23224
RFS[2]: Identified database type as 'physical standby'
Primary database is in MAXIMUM AVAILABILITY mode
Standby controlfile consistent with primary
Primary database is in MAXIMUM AVAILABILITY mode
Standby controlfile consistent with primary
RFS[2]: Successfully opened standby log 4: '/u01/app/oracle/oradata/orcl/redo04.log'
Redo Shipping Client Connected as PUBLIC
-- Connected User is Valid
RFS[3]: Assigned to RFS process 23226
RFS[3]: Identified database type as 'physical standby'
RFS[3]: Successfully opened standby log 5: '/u01/app/oracle/oradata/orcl/redo05.log'
Media Recovery Start: Managed Standby Recovery
Managed Standby Recovery not using Real Time Apply
Media Recovery Log /u01/app/oracle/oradata/orcl/archivelog/1_37_793805797.dbf
Media Recovery Waiting for thread 1 sequence 38 (in transit)
Redo Shipping Client Connected as PUBLIC
-- Connected User is Valid
RFS[4]: Assigned to RFS process 23232
RFS[4]: Identified database type as 'physical standby'
Attempt to start background Managed Standby Recovery process
Primary database is in MAXIMUM AVAILABILITY mode
Standby controlfile consistent with primary
RFS[2]: Successfully opened standby log 5: '/u01/app/oracle/oradata/orcl/redo05.log'
Media Recovery Log /u01/app/oracle/oradata/orcl/archivelog/1_38_793805797.dbf
Media Recovery Waiting for thread 1 sequence 39 (in transit)
Primary database is in MAXIMUM AVAILABILITY mode
Standby controlfile consistent with primary
RFS[2]: Successfully opened standby log 4: '/u01/app/oracle/oradata/orcl/redo04.log'
Media Recovery Log /u01/app/oracle/oradata/orcl/archivelog/1_39_793805797.dbf
Media Recovery Waiting for thread 1 sequence 40 (in transit)
Managed Standby Recovery Canceled
Attempt to start background Managed Standby Recovery process
MRP0: Background Managed Standby Recovery process started
Managed Standby Recovery not using Real Time Apply
Clearing online redo logfile 1 /u01/app/oracle/oradata/orcl/redo01.log
Clearing online redo logfile 1 complete
Media Recovery Waiting for thread 1 sequence 40 (in transit)
Primary database is in MAXIMUM AVAILABILITY mode
Standby controlfile consistent with primary
RFS[2]: Successfully opened standby log 5: '/u01/app/oracle/oradata/orcl/redo05.log'
Media Recovery Log /u01/app/oracle/oradata/orcl/archivelog/1_40_793805797.dbf
Media Recovery Waiting for thread 1 sequence 41 (in transit)
56 rows selected.
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/23490154/viewspace-1061860/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 19 Oracle Data Guard 相關檢視Oracle
- RMAN相關的動態效能檢視
- RMAN備份相關的動態效能檢視
- SGA相關的幾個動態效能檢視
- 備份相關的動態效能檢視及監控
- 與oracle10g data guard(dg)緊密關聯的相關檢視Oracle
- 8.1關於動態效能檢視
- 關於SGA的常用動態效能檢視
- 動態效能檢視
- Oracle許可權（二）許可權相關的動態效能檢視與資料字典檢視Oracle
- Oracle檢視：常用動態效能檢視Oracle
- oracle-一些檢視效能相關的檢視Oracle
- ASM動態效能檢視ASM
- Data Guard相關的一些設定
- (重要)關於效能的幾個主要動態檢視
- 18 與Oracle Data Guard 相關的SQL語句OracleSQL
- （轉）Oracle 動態效能檢視Oracle
- V$PGASTAT動態效能檢視AST
- 動態效能檢視基礎
- 【DataGuard】部署Data Guard相關引數詳解
- Standby (Data guard) 常用維護命令及相關概念
- db2常用動態效能檢視DB2
- oracle最重要的9個動態效能檢視Oracle
- sql server 檢視tempdb使用的相關檢視SQLServer
- 10g 動態效能檢視[final]
- oracle最重要的9個動態效能檢視(zt)Oracle
- 1 關於 Oracle Data GuardOracle
- oracle Metrics相關檢視Oracle
- 11g 自動記憶體管理先關動態效能檢視三個記憶體
- Dataguard (Standby) 相關的檢視(View)View
- 資料字典和動態效能檢視基礎
- V$PGA_TARGET_ADVICE 動態效能檢視
- v$動態效能檢視和隱含引數
- ORACLE動態效能檢視統計值溢位Oracle
- 【DataGuard】部署Data Guard相關引數詳解 - Oracle官方文件描述Oracle
- 檢視Oracle基礎配置資訊和效能相關資訊的指令碼Oracle指令碼
- oracle wait event的一些動態效能檢視OracleAI
- 檢視oracle鎖相關資訊Oracle