【DATAGUARD 學習】監控primary庫和standby庫
1 檢視程式的活動狀態。
TESTDG>select process,status,thread#,sequence#,block#,blocks
2 from v$managed_standby;
PROCESS STATUS THREAD# SEQUENCE# BLOCK# BLOCKS
--------- ------------ ---------- ---------- ---------- ----------
ARCH CLOSING 1 42 1 205
ARCH CLOSING 1 43 6145 1929
ARCH CLOSING 1 40 1 982
ARCH CLOSING 1 41 1 304
RFS IDLE 0 0 0 0
MRP0 WAIT_FOR_LOG 1 44 0 0
RFS IDLE 1 44 5205 2
RFS IDLE 0 0 0 0
已選擇8行。
PROCESS：程式名 ARCH ,RFS,MRP0
CLIENT_PROCESS：對應的主庫中的程式如 ARCH,LGWR
STATUS: 程式的當前狀態
1)ALLOCATED: 正在準備連線主庫
2)ATTACHED: 正在連線主庫
3)CONNECTED:已經連線主庫
4)IDLE:空閒
5)RECEIVING:歸檔日誌接收中
6)OPENING:歸檔日誌處理中
7)CLOSING:歸檔日誌處理完，收尾中
8)WRITING:REDO資料庫寫向歸檔檔案中
9)WAIT_FOR_LOG:等待新的REDO資料中
10)WAIT_FOR_GAP:歸檔有中斷，正在等待中斷的那部分REDO資料.
11)APPLYING_LOG: 應用REDO資料。
THREAD#程式號
SEQUENCE# :歸檔序列號
BLOCK# :？？
BLOCKS : ？？
TESTDG>col dest_name for a35
TESTDG>select dest_name,archived_thread#,archived_seq#,applied_thread#,
2 applied_seq#,db_unique_name
3 from v$archive_dest_status
4 where status='VALID';
DEST_NAME ARCH_THR# ARCH_SEQ# APP_THR# APP_SEQ# DB_UNIQUE_NAME
------------------ ---------------------------- ------------ ------------------------LOG_ARCHIVE_DEST_1 1 43 0 0 testdg
LOG_ARCHIVE_DEST_2 0 0 0 0 orcl
STANDBY_ARCHIVE_DEST 1 42 1 40 NONE
TESTDG>--檢查歸檔檔案路徑和建立資訊
TESTDG>col name for a35
TESTDG>select name,creator,sequence#,applied,completion_time from v$archived_log;
NAME CREATOR SEQUENCE# APP COMPLETION_TIM
----------------------------------- ------- ---------- --- --------------
D:\ORACLE\ARCHDG\ARC_1_33_728782665.ARC ARCH 33 YES 20-9月 -10
D:\ORACLE\ARCHDG\ARC_1_31_728782665.ARC ARCH 31 YES 20-9月 -10
D:\ORACLE\ARCHDG\ARC_1_32_728782665.ARC ARCH 32 YES 20-9月 -10
D:\ORACLE\ARCHDG\ARC_1_34_728782665.ARC ARCH 34 YES 20-9月 -10
D:\ORACLE\ARCHDG\ARC_1_35_728782665.ARC ARCH 35 YES 20-9月 -10
D:\ORACLE\ARCHDG\ARC_1_36_728782665.ARC ARCH 36 YES 20-9月 -10
D:\ORACLE\ARCHDG\ARC_1_37_728782665.ARC ARCH 37 YES 20-9月 -10
D:\ORACLE\ARCHDG\ARC_1_38_728782665.ARC ARCH 38 YES 20-9月 -10
D:\ORACLE\ARCHDG\ARC_1_39_728782665.ARC ARCH 39 YES 20-9月 -10
D:\ORACLE\ARCHDG\ARC_1_40_728782665.ARC ARCH 40 YES 20-9月 -10
D:\ORACLE\ARCHDG\ARC_1_41_728782665.ARC ARCH 41 YES 20-9月 -10
D:\ORACLE\ARCHDG\ARC_1_42_728782665.ARC ARCH 42 YES 20-9月 -10
D:\ORACLE\ARCHDG\ARC_1_43_728782665.ARC ARCH 43 YES 20-9月 -10
已選擇13行。
--檢視當前REDO 應用和REDO傳輸服務的活動狀態。
查詢物理standby的當前redo應用和redo傳輸服務的狀態。
TESTDG>select process,status,thread#,sequence#,block#,blocks
2 from v$managed_standby;
PROCESS STATUS THREAD# SEQUENCE# BLOCK# BLOCKS
--------- ------------ ---------- ---------- ---------- ----------
ARCH CLOSING 1 42 1 205
ARCH CLOSING 1 43 6145 1929
ARCH CLOSING 1 40 1 982
ARCH CLOSING 1 41 1 304
RFS IDLE 0 0 0 0
RFS IDLE 1 44 2 38
RFS IDLE 0 0 0 0
已選擇7行。
TESTDG>--檢查歸檔歷史
TESTDG>select first_time,first_change#,next_change# ,sequence# from v$log_history;
FIRST_TIME FIRST_CHANGE# NEXT_CHANGE# SEQUENCE#
-------------- ------------- ------------ ----------
03-9月 -10 886308 921211 1
03-9月 -10 921211 929925 2
03-9月 -10 929925 946708 3
03-9月 -10 946708 967419 4
03-9月 -10 967419 992343 5
04-9月 -10 992343 1015062 6
05-9月 -10 1015062 1057813 7
06-9月 -10 1057813 1088443 8
07-9月 -10 1088443 1095408 9
07-9月 -10 1095408 1123437 10
08-9月 -10 1123437 1152797 11
09-9月 -10 1152797 1178390 12
10-9月 -10 1178390 1178915 13
10-9月 -10 1178915 1178920 14
10-9月 -10 1178920 1213830 15
11-9月 -10 1213830 1252759 16
12-9月 -10 1252759 1284851 17
13-9月 -10 1284851 1324677 18
17-9月 -10 1324677 1359386 19
18-9月 -10 1359386 1386121 20
18-9月 -10 1386121 1411650 21
19-9月 -10 1411650 1432583 22
19-9月 -10 1432583 1454040 23
20-9月 -10 1454040 1454045 24
20-9月 -10 1454045 1459973 25
20-9月 -10 1459973 1462648 26
20-9月 -10 1462648 1468542 27
20-9月 -10 1468542 1496216 28
20-9月 -10 1496216 1498057 29
20-9月 -10 1498057 1504914 30
20-9月 -10 1504914 1511719 31
20-9月 -10 1511719 1516010 32
20-9月 -10 1516010 1524084 33
20-9月 -10 1524084 1526028 34
20-9月 -10 1526028 1527720 35
20-9月 -10 1527720 1530395 36
20-9月 -10 1530395 1530671 37
20-9月 -10 1530671 1531644 38
20-9月 -10 1531644 1532453 39
20-9月 -10 1532453 1533213 40
20-9月 -10 1533213 1533482 41
20-9月 -10 1533482 1533698 42
20-9月 -10 1533698 1538619 43
已選擇43行。
--查詢最後應用的歸檔檔案
TESTDG>select thread#,max(sequence#) as LAST_APPLIED_LOG
2 FROM V$LOG_HISTORY
3 GROUP BY THREAD#;
THREAD# LAST_APPLIED_LOG
---------- ----------------
1 43
--也可以透過 v$archived_log的APP列獲得相同的功能！
TESTDG>select thread#,sequence#,applied from v$archived_log;
THREAD# SEQUENCE# APP
---------- ---------- ---
1 33 YES
1 31 YES
1 32 YES
1 34 YES
1 35 YES
1 36 YES
1 37 YES
1 38 YES
1 39 YES
1 40 YES
1 41 YES
1 42 YES
1 43 YES
已選擇13行。
ORCL>--在主庫上檢視未接收的日誌
ORCL>select local.thread#,local.sequence# from
2 ( select thread#,sequence# from v$archived_log where dest_id=1) local
3 where local.sequence# not in
4 (select sequence# from v$archived_log where dest_id=2 and thread#=local.thread#);
THREAD# SEQUENCE#
---------- ----------
1 17
1 18
1 19
1 20
1 21
1 22
1 23
1 24
1 25
1 26
1 27
1 28
1 29
1 30
注意：dest_id =N 就是LOG_ARCHIVE_DEST_N 中的那個N.
--檢查應用模式(是否啟用的實時應用)
TESTDG>select recovery_mode from v$archive_dest_status where dest_id=2;
RECOVERY_MODE
-----------------------
IDLE --沒有啟用，啟用了實時應用則為：MANAGED READ TIME APPLY
---DATAGUARD 事件(v$dataguard_status).該檢視顯示alert.log和trace檔案中記錄的事件。
TESTDG>select message from v$dataguard_status;
MESSAGE
-------------------------------------------------------------
ARC0: Archival started
ARC1: Archival started
ARC2: Archival started
ARC3: 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 8424
RFS[1]: Identified database type as 'physical standby'
Redo Shipping Client Connected as PUBLIC
-- Connected User is Valid
RFS[2]: Assigned to RFS process 8308
RFS[2]: Identified database type as 'physical standby'
Redo Shipping Client Connected as PUBLIC
-- Connected User is Valid
RFS[3]: Assigned to RFS process 8064
RFS[3]: Identified database type as 'physical standby'
Attempt to start background Managed Standby Recovery process
MRP0: Background Managed Standby Recovery process started
Managed Standby Recovery not using Real Time Apply
MESSAGE
----------------------------------------------------------------
Media Recovery Log D:\ORACLE\ARCHDG\ARC_1_31_728782665.ARC
Media Recovery Log D:\ORACLE\ARCHDG\ARC_1_32_728782665.ARC
Media Recovery Log D:\ORACLE\ARCHDG\ARC_1_33_728782665.ARC
Media Recovery Waiting for thread 1 sequence 34
Redo Shipping Client Connected as PUBLIC
-- Connected User is Valid
RFS[4]: Assigned to RFS process 8656
RFS[4]: Identified database type as 'physical standby'
Redo Shipping Client Connected as PUBLIC
-- Connected User is Valid
RFS[5]: Assigned to RFS process 8812
MESSAGE
--------------------------------------------------------
RFS[5]: Identified database type as 'physical standby'
Primary database is in MAXIMUM PERFORMANCE mode
Primary database is in MAXIMUM PERFORMANCE mode
RFS[4]: Successfully opened standby log 4: 'D:\ORACLE\TESTDGDATA\STANDBY01.LOG'
RFS[5]: Successfully opened standby log 5: 'D:\ORACLE\TESTDGDATA\STANDBY02.LOG'
Media Recovery Log D:\ORACLE\ARCHDG\ARC_1_34_728782665.ARC
Media Recovery Waiting for thread 1 sequence 35 (in transit)
Redo Shipping Client Connected as PUBLIC
-- Connected User is Valid
RFS[6]: Assigned to RFS process 7224
RFS[6]: Identified database type as 'physical standby'
MESSAGE
--------------------------------------------------------------------------------
Primary database is in MAXIMUM PERFORMANCE mode
RFS[4]: Successfully opened standby log 5: 'D:\ORACLE\TESTDGDATA\STANDBY02.LOG'
Media Recovery Log D:\ORACLE\ARCHDG\ARC_1_35_728782665.ARC
Media Recovery Waiting for thread 1 sequence 36 (in transit)
MRP0: Background Media Recovery cancelled with status 16037
MRP0: Background Media Recovery process shutdown
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
Media Recovery Waiting for thread 1 sequence 36 (in transit)
MESSAGE
--------------------------------------------------------------------------------
Primary database is in MAXIMUM PERFORMANCE mode
RFS[4]: Successfully opened standby log 5: 'D:\ORACLE\TESTDGDATA\STANDBY02.LOG'
Media Recovery Log D:\ORACLE\ARCHDG\ARC_1_36_728782665.ARC
Media Recovery Waiting for thread 1 sequence 37 (in transit)
Primary database is in MAXIMUM PERFORMANCE mode
RFS[4]: Successfully opened standby log 5: 'D:\ORACLE\TESTDGDATA\STANDBY02.LOG'
Media Recovery Log D:\ORACLE\ARCHDG\ARC_1_37_728782665.ARC
Media Recovery Waiting for thread 1 sequence 38 (in transit)
Primary database is in MAXIMUM PERFORMANCE mode
RFS[4]: Successfully opened standby log 5: 'D:\ORACLE\TESTDGDATA\STANDBY02.LOG'
Media Recovery Log D:\ORACLE\ARCHDG\ARC_1_38_728782665.ARC
MESSAGE
--------------------------------------------------------------------------------
MRP0: Background Media Recovery terminated with error 1274
MRP0: Background Media Recovery process shutdown
Redo Shipping Client Connected as PUBLIC
-- Connected User is Valid
RFS[7]: Assigned to RFS process 10272
RFS[7]: Identified database type as 'physical standby'
Attempt to start background Managed Standby Recovery process
MRP0: Background Managed Standby Recovery process started
Managed Standby Recovery not using Real Time Apply
Media Recovery Log D:\ORACLE\ARCHDG\ARC_1_38_728782665.ARC
Media Recovery Waiting for thread 1 sequence 39
MESSAGE
--------------------------------------------------------------------------------
Redo Shipping Client Connected as PUBLIC
-- Connected User is Valid
RFS[8]: Assigned to RFS process 8292
RFS[8]: Identified database type as 'physical standby'
Primary database is in MAXIMUM PERFORMANCE mode
Redo Shipping Client Connected as PUBLIC
-- Connected User is Valid
RFS[9]: Assigned to RFS process 9672
RFS[9]: Identified database type as 'physical standby'
Primary database is in MAXIMUM PERFORMANCE mode
RFS[8]: Successfully opened standby log 4: 'D:\ORACLE\TESTDGDATA\STANDBY01.LOG'
MESSAGE
--------------------------------------------------------------------------------
RFS[9]: Successfully opened standby log 5: 'D:\ORACLE\TESTDGDATA\STANDBY02.LOG'
Media Recovery Log D:\ORACLE\ARCHDG\ARC_1_39_728782665.ARC
Media Recovery Waiting for thread 1 sequence 40 (in transit)
Primary database is in MAXIMUM PERFORMANCE mode
RFS[8]: Successfully opened standby log 5: 'D:\ORACLE\TESTDGDATA\STANDBY02.LOG'
Media Recovery Log D:\ORACLE\ARCHDG\ARC_1_40_728782665.ARC
MRP0: Background Media Recovery terminated with error 1274
MRP0: Background Media Recovery process shutdown
Primary database is in MAXIMUM PERFORMANCE mode
RFS[8]: Successfully opened standby log 5: 'D:\ORACLE\TESTDGDATA\STANDBY02.LOG'
Primary database is in MAXIMUM PERFORMANCE mode
MESSAGE
--------------------------------------------------------------------------------
RFS[8]: Successfully opened standby log 5: 'D:\ORACLE\TESTDGDATA\STANDBY02.LOG'
Primary database is in MAXIMUM PERFORMANCE mode
RFS[8]: Successfully opened standby log 5: 'D:\ORACLE\TESTDGDATA\STANDBY02.LOG'
Attempt to start background Managed Standby Recovery process
MRP0: Background Managed Standby Recovery process started
Managed Standby Recovery not using Real Time Apply
Media Recovery Log D:\ORACLE\ARCHDG\ARC_1_40_728782665.ARC
Media Recovery Log D:\ORACLE\ARCHDG\ARC_1_41_728782665.ARC
Media Recovery Log D:\ORACLE\ARCHDG\ARC_1_42_728782665.ARC
Media Recovery Log D:\ORACLE\ARCHDG\ARC_1_43_728782665.ARC
Media Recovery Waiting for thread 1 sequence 44 (in transit)
MESSAGE
--------------------------------------------------------------------------------
MRP0: Background Media Recovery cancelled with status 16037
MRP0: Background Media Recovery process shutdown
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
Media Recovery Waiting for thread 1 sequence 44 (in transit)
已選擇117行。
--查詢當前資料庫的基本資訊如資料庫的角色和保護模式，保護級別。
TESTDG>select database_role,db_unique_name,
2 open_mode,protection_mode,
3 protection_level,switchover_status
4 from v$database;
DATABASE_ROLE DB_UNIQUE_ OPEN_MODE PROTECTION_MODE PROTECTION_LEVEL
------------- ---------- --------- --------------- ----------------
SWITCHOVER_STATUS
------------------
PHYSICAL STANDBY testdg READ ONLY MAXIMUM PERFORMANCE MAXIMUM PERFORMANCE
NOT ALLOWED
---檢視failover後快速啟動的資訊。
TESTDG>select fs_failover_status ,
2 fs_failover_current_target,
3 fs_failover_threshold,
4 fs_failover_observer_present from v$database;
FS_FAIL_STATUS FS_FAIL_CUR_TARGET FS_FAIL_THRESHOLD FS_FAIL
-------------- ------------------- ------------------ ----
DISABLED 0
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/22664653/viewspace-674606/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 【DATAGUARD 學習】如何停止standby資料庫資料庫
- Dataguard從庫效能的監控
- 【DATAGUARD 學習】使用duplicate 建立物理standby 資料庫資料庫
- dataguard standby資料庫的關閉和啟動資料庫
- Dataguard環境修改主庫和standby庫online redo log&standby redo log大小
- 配置 Oracle 10g RAC primary + RAC physical standby dataguardOracle 10g
- dataguard之物理standby庫failover 切換AI
- 關於建立DataGuard Physical Standby資料庫資料庫
- dataguard standby備庫磁碟空間滿(ZT)
- 【DATAGUARD 學習】管理影響備庫的主庫事件事件
- 【DATAGUARD 學習】測試standby應用REDO
- dataguard 搭建 oracle_sid不同 2節點 primary+standbyOracle
- dataguard 搭建 oracle_sid相同 2節點 primary+standbyOracle
- 【RAC,DATAGUARD】Creating a physical standby from ASM (RAC ) primary之四ASM
- 資料庫監控資料庫
- 監控Logical standby databaseDatabase
- prometheus 監控學習Prometheus
- 資料庫效能監控資料庫
- standby database to primary database.Database
- 監控備庫效能，為Active DataGuard的備庫生成statspack報告並實現定時傳送
- 2 Day DBA-管理方案物件-監控和優化資料庫-積極的資料庫監控物件優化資料庫
- ORACLE 11G DataGuard Failover後如何修復standby庫OracleAI
- MySQL監控-Datadog資料庫監控調研MySql資料庫
- Standby OS i/o問題導致Primary 庫不能正常歸檔問題
- 【DataGuard】Oracle DataGuard 日誌傳輸狀態監控Oracle
- 資料庫繁忙程度監控資料庫
- 資料庫監控軟體資料庫
- SQL Server資料庫監控SQLServer資料庫
- 資料庫監控指令碼資料庫指令碼
- 監控Oracle資料庫方法Oracle資料庫
- 監控資料庫指令碼資料庫指令碼
- zabbix監控oracle資料庫Oracle資料庫
- 【DATAGUARD】 將11g物理備庫轉換為Snapshot Standby
- 物理Standby資料庫及邏輯Standby資料庫（Physical Standby & Logical Standby）資料庫
- 資料庫監控---PIGOSS BSM資料庫Go
- shell監控mysql 8.0資料庫MySql資料庫
- shell監控mysql 5.7資料庫MySql資料庫
- [zt]資料庫監控指令碼資料庫指令碼