ORACLE 歸檔空間滿導致的enq: TX - row lock contention
2016年10月10日，客戶一預警系統發生會話數飆高，系統響應極慢，後來確診根源是歸檔空間滿，引起所有redo耗盡，導致會話堆積，下面是處理過程。
作業系統：HP-UX B.11.31 U ia64
資料庫版本：ORACLE 10.2.0.5 RAC
按照常規處理思路，首先檢視RAC資料庫的告警日誌：
例項1的告警日誌
Mon Oct 10 19:24:48 EAT 2016
ORACLE Instance orcl1 - Can not allocate log, archival required
.
.
Mon Oct 10 19:26:28 EAT 2016
ORACLE Instance orcl1 - Can not allocate log, archival required
.
.
Mon Oct 10 20:06:21 EAT 2016
Errors in file /oracle/app/oracle/admin/orcl/bdump/orcl1_arc0_22630.trc:
ORA-19504: failed to create file "+SFILEARCHDG01"
ORA-17502: ksfdcre:4 Failed to create file +SFILEARCHDG01
ORA-15041: diskgroup space exhausted
例項2的告警日誌
Mon Oct 10 20:12:52 EAT 2016
Errors in file /oracle/app/oracle/admin/orcl/bdump/orcl2_arc1_8962.trc:
ORA-19504: failed to create file "+SFILEARCHDG01"
ORA-17502: ksfdcre:4 Failed to create file +SFILEARCHDG01
ORA-15041: diskgroup space exhausted
ARC1: Error 19504 Creating archive log file to '+SFILEARCHDG01'
ARCH: Archival stopped, error occurred. Will continue retrying
Mon Oct 10 20:12:52 EAT 2016
ORACLE Instance orcl2 - Archival Error
Mon Oct 10 20:12:52 EAT 2016
ORA-16038: log 12 sequence# 77053 cannot be archived
ORA-19504: failed to create file ""
ORA-00312: online log 12 thread 2: '+ORCLDG1/orcl/onlinelog/group_12.291.819723519'
ORA-00312: online log 12 thread 2: '+ORCLDG2/orcl/onlinelog/group_12.266.819723541'
從例項1登入資料庫檢視ASM磁碟組使用情況
orcl1:[/oracle/app/oracle/admin/orcl/bdump]$asmcmd
ASMCMD> lsdg
State Type Rebal Unbal Sector Block AU Total_MB Free_MB Req_mir_free_MB Usable_file_MB Offline_disks Name
MOUNTED NORMAL N N 1024 4096 1048576 1024000 678 102400 -50861 0 ORCLDG1/
MOUNTED NORMAL N N 1024 4096 1048576 1024000 1389 102400 -50505 0 ORCLDG2/
MOUNTED EXTERN N N 1024 4096 1048576 512000 100210 0 100210 0 ORCLDG3/
MOUNTED NORMAL N N 1024 4096 1048576 307200 9659 102400 -46370 0 ORCLSYSDG01/
MOUNTED NORMAL N N 1024 4096 1048576 204800 1376 0 688 0 SFILEARCHDG01/
透過檢視告警日誌和ASM磁碟使用情況及2個節點個告警日誌：可以判斷是歸檔空間滿、歸檔異常，redo組耗光後例項hang住了。
如果檢視資料庫例項1的等待事件，發現等待事件 enq: TX - row lock contention已經比較嚴重：
SQL> SELECT INST_ID, EVENT, COUNT(*) CNT, SUM(WAIT_TIME) WT_TM
2 FROM GV$SESSION_WAIT
3 GROUP BY INST_ID, EVENT
4 ORDER BY INST_ID, CNT DESC;
INST_ID EVENT CNT WT_TM
---------- ---------------------------------------- ---------- ----------
1 SQL*Net message from client 265 0
1 enq: TX - row lock contention 132 0
1 db file sequential read 107 35
1 jobq slave wait 19 0
1 rdbms ipc message 15 4
1 read by other session 7 0
1 wait for a undo record 5 0
1 gcs remote message 4 0
1 enq: HW - contention 2 0
1 log file sequential read 2 0
1 db file scattered read 2 0
1 buffer busy waits 2 0
1 latch: cache buffers chains 2 -2
1 enq: US - contention 2 0
1 PX Deq: Txn Recovery Start 2 0
按等待事件檢視資料庫中會話、執行sql的sql_id相關資訊，發現等待事件enq: TX - row lock contention全是sql_id為90frgddxc2my0的sql引起的，並且有200多個積壓的會話。
SQL> select * from (
2 select a.inst_id,
3 a.sid,
4 a.sql_id,
5 a.event,
6 a.status,
7 connect_by_isleaf as isleaf,
8 sys_connect_by_path(sid,'<-') tree,
9 level as tree_level
10 from gv$session a
11 start with a.blocking_session is not null and event like '%enq: TX%'
12 connect by nocycle a.sid = prior a.blocking_Session
13 )
14 where isleaf =1
15 order by tree_level asc;
INST_ID SID SQL_ID EVENT STATUS ISLEAF TREE TREE_LEVEL
---------- ---------- ------------- ---------------------------------------- -------- ---------- -------------------------------------------------------------------------------- ----------
1 1892 90frgddxc2my0 enq: TX - row lock contention ACTIVE 1 <-1892 1
SQL> select count(*)
2 from gv$session
3 where event='enq: TX - row lock contention'
4 and inst_id=1
5 and status='ACTIVE';
COUNT(*)
----------
210
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ------------
1 1 58663 1073741824 2 NO ACTIVE 1.5122E+13 10-OCT-16
2 1 58662 1073741824 2 NO INACTIVE 1.5122E+13 10-OCT-16
3 1 58661 1073741824 2 NO INACTIVE 1.5122E+13 10-OCT-16
4 1 58660 1073741824 2 NO INACTIVE 1.5122E+13 10-OCT-16
5 1 58665 1073741824 2 NO CURRENT 1.5122E+13 10-OCT-16
6 1 58664 1073741824 2 NO ACTIVE 1.5122E+13 10-OCT-16
7 2 77116 1073741824 2 NO ACTIVE 1.5122E+13 11-OCT-16
8 2 77117 1073741824 2 NO ACTIVE 1.5122E+13 11-OCT-16
9 2 77118 1073741824 2 NO ACTIVE 1.5122E+13 11-OCT-16
10 2 77114 1073741824 2 YES ACTIVE 1.5122E+13 10-OCT-16
11 2 77115 1073741824 2 NO ACTIVE 1.5122E+13 10-OCT-16
12 2 77119 1073741824 2 NO CURRENT 1.5122E+13 11-OCT-16
到此，透過查詢到的資訊並結合告警日誌，可以肯定：大量併發dml操作，引發歸檔暴增，耗光歸檔空間，使用完redo組，導致會話積壓，進而叢集hang住，應用系統響應緩慢。
透過觀察1節點的告警日誌，還發現2個例項的redo切換很頻繁，1分鐘一次：
節點1
Mon Oct 10 20:05:19 EAT 2016
Thread 1 cannot allocate new log, sequence 58490
All online logs needed archiving
Current log# 1 seq# 58489 mem# 0: +ORCLSYSDG01/orcl/onlinelog/group_1.262.819722231
Current log# 1 seq# 58489 mem# 1: +ORCLGDG02/orcl/onlinelog/group_1.264.819722239
Mon Oct 10 20:05:48 EAT 2016
Thread 1 advanced to log sequence 58490 (LGWR switch)
Current log# 5 seq# 58490 mem# 0: +ORCLGDG01/orcl/onlinelog/group_5.293.819723461
Current log# 5 seq# 58490 mem# 1: +ORCLGDG02/orcl/onlinelog/group_5.268.819723471
Mon Oct 10 20:06:18 EAT 2016
ORACLE Instance hnrmdb1 - Can not allocate log, archival required
Mon Oct 10 20:06:18 EAT 2016
Thread 1 cannot allocate new log, sequence 58491
All online logs needed archiving
Current log# 5 seq# 58490 mem# 0: +ORCLGDG01/orcl/onlinelog/group_5.293.819723461
Current log# 5 seq# 58490 mem# 1: +ORCLGDG02/orcl/onlinelog/group_5.268.819723471
Mon Oct 10 20:06:20 EAT 2016
Thread 1 advanced to log sequence 58491 (LGWR switch)
Current log# 6 seq# 58491 mem# 0: +ORCLGDG01/orcl/onlinelog/group_6.292.819723491
Current log# 6 seq# 58491 mem# 1: +ORCLGDG02/orcl/onlinelog/group_6.267.819723505
採取的措施是，清理歸檔空間並增加2個例項的redo組數。
增加redo組：
SQL> select * from v$logfile;
GROUP# STATUS TYPE MEMBER IS_
---------- ------- ------- ------------------------------------------------------------- ---
7 ONLINE +ORCLDG01/orcl/onlinelog/group_7.275.819721599 NO
7 ONLINE +ORCLDG02/orcl/onlinelog/group_7.270.819721609 NO
12 ONLINE +ORCLDG01/orcl/onlinelog/group_12.291.819723519 NO
12 ONLINE +ORCLDG02/orcl/onlinelog/group_12.266.819723541 NO
11 ONLINE +ORCLDG01/orcl/onlinelog/group_11.297.819722567 NO
11 ONLINE +ORCLDG02/orcl/onlinelog/group_11.272.819722583 NO
10 ONLINE +ORCLDG01/orcl/onlinelog/group_10.288.819722545 NO
10 ONLINE +ORCLDG02/orcl/onlinelog/group_10.261.819722555 NO
9 ONLINE +ORCLDG01/orcl/onlinelog/group_9.266.819722429 NO
9 ONLINE +ORCLDG02/orcl/onlinelog/group_9.262.819722437 NO
8 ONLINE +ORCLDG01/orcl/onlinelog/group_8.265.819721623 NO
8 ONLINE +ORCLDG02/orcl/onlinelog/group_8.269.819721631 NO
4 ONLINE +ORCLDG01/orcl/onlinelog/group_4.294.819723443 NO
4 ONLINE +ORCLDG02/orcl/onlinelog/group_4.265.819723451 NO
3 ONLINE +ORCLDG01/orcl/onlinelog/group_3.279.819722327 NO
3 ONLINE +ORCLDG02/orcl/onlinelog/group_3.271.819722335 NO
2 ONLINE +ORCLDG01/orcl/onlinelog/group_2.267.819722257 NO
2 ONLINE +ORCLDG02/orcl/onlinelog/group_2.263.819722265 NO
1 ONLINE +ORCLDG01/orcl/onlinelog/group_1.262.819722231 NO
1 ONLINE +ORCLDG02/orcl/onlinelog/group_1.264.819722239 NO
6 ONLINE +ORCLDG01/orcl/onlinelog/group_6.292.819723491 NO
6 ONLINE +ORCLDG02/orcl/onlinelog/group_6.267.819723505 NO
5 ONLINE +ORCLDG01/orcl/onlinelog/group_5.293.819723461 NO
5 ONLINE +ORCLDG02/orcl/onlinelog/group_5.268.819723471 NO
Wed Oct 12 20:19:41 EAT 2016
SUCCESS: diskgroup SFILEARCHDG01 was mounted
Wed Oct 12 20:19:46 EAT 2016
SUCCESS: diskgroup SFILEARCHDG01 was dismounted
經求證是10g ASM rac才會出現的，oracle採取的策略是在歸檔目錄不適用的時候對其下線，在歸檔程式需要切換歸檔的時候，才會將歸檔的卷組上線。
作業系統：HP-UX B.11.31 U ia64
資料庫版本：ORACLE 10.2.0.5 RAC
按照常規處理思路，首先檢視RAC資料庫的告警日誌：
例項1的告警日誌
Mon Oct 10 19:24:48 EAT 2016
ORACLE Instance orcl1 - Can not allocate log, archival required
.
.
Mon Oct 10 19:26:28 EAT 2016
ORACLE Instance orcl1 - Can not allocate log, archival required
.
.
Mon Oct 10 20:06:21 EAT 2016
Errors in file /oracle/app/oracle/admin/orcl/bdump/orcl1_arc0_22630.trc:
ORA-19504: failed to create file "+SFILEARCHDG01"
ORA-17502: ksfdcre:4 Failed to create file +SFILEARCHDG01
ORA-15041: diskgroup space exhausted
例項2的告警日誌
Mon Oct 10 20:12:52 EAT 2016
Errors in file /oracle/app/oracle/admin/orcl/bdump/orcl2_arc1_8962.trc:
ORA-19504: failed to create file "+SFILEARCHDG01"
ORA-17502: ksfdcre:4 Failed to create file +SFILEARCHDG01
ORA-15041: diskgroup space exhausted
ARC1: Error 19504 Creating archive log file to '+SFILEARCHDG01'
ARCH: Archival stopped, error occurred. Will continue retrying
Mon Oct 10 20:12:52 EAT 2016
ORACLE Instance orcl2 - Archival Error
Mon Oct 10 20:12:52 EAT 2016
ORA-16038: log 12 sequence# 77053 cannot be archived
ORA-19504: failed to create file ""
ORA-00312: online log 12 thread 2: '+ORCLDG1/orcl/onlinelog/group_12.291.819723519'
ORA-00312: online log 12 thread 2: '+ORCLDG2/orcl/onlinelog/group_12.266.819723541'
從例項1登入資料庫檢視ASM磁碟組使用情況
orcl1:[/oracle/app/oracle/admin/orcl/bdump]$asmcmd
ASMCMD> lsdg
State Type Rebal Unbal Sector Block AU Total_MB Free_MB Req_mir_free_MB Usable_file_MB Offline_disks Name
MOUNTED NORMAL N N 1024 4096 1048576 1024000 678 102400 -50861 0 ORCLDG1/
MOUNTED NORMAL N N 1024 4096 1048576 1024000 1389 102400 -50505 0 ORCLDG2/
MOUNTED EXTERN N N 1024 4096 1048576 512000 100210 0 100210 0 ORCLDG3/
MOUNTED NORMAL N N 1024 4096 1048576 307200 9659 102400 -46370 0 ORCLSYSDG01/
MOUNTED NORMAL N N 1024 4096 1048576 204800 1376 0 688 0 SFILEARCHDG01/
透過檢視告警日誌和ASM磁碟使用情況及2個節點個告警日誌：可以判斷是歸檔空間滿、歸檔異常，redo組耗光後例項hang住了。
如果檢視資料庫例項1的等待事件，發現等待事件 enq: TX - row lock contention已經比較嚴重：
SQL> SELECT INST_ID, EVENT, COUNT(*) CNT, SUM(WAIT_TIME) WT_TM
2 FROM GV$SESSION_WAIT
3 GROUP BY INST_ID, EVENT
4 ORDER BY INST_ID, CNT DESC;
INST_ID EVENT CNT WT_TM
---------- ---------------------------------------- ---------- ----------
1 SQL*Net message from client 265 0
1 enq: TX - row lock contention 132 0
1 db file sequential read 107 35
1 jobq slave wait 19 0
1 rdbms ipc message 15 4
1 read by other session 7 0
1 wait for a undo record 5 0
1 gcs remote message 4 0
1 enq: HW - contention 2 0
1 log file sequential read 2 0
1 db file scattered read 2 0
1 buffer busy waits 2 0
1 latch: cache buffers chains 2 -2
1 enq: US - contention 2 0
1 PX Deq: Txn Recovery Start 2 0
按等待事件檢視資料庫中會話、執行sql的sql_id相關資訊，發現等待事件enq: TX - row lock contention全是sql_id為90frgddxc2my0的sql引起的，並且有200多個積壓的會話。
SQL> select * from (
2 select a.inst_id,
3 a.sid,
4 a.sql_id,
5 a.event,
6 a.status,
7 connect_by_isleaf as isleaf,
8 sys_connect_by_path(sid,'<-') tree,
9 level as tree_level
10 from gv$session a
11 start with a.blocking_session is not null and event like '%enq: TX%'
12 connect by nocycle a.sid = prior a.blocking_Session
13 )
14 where isleaf =1
15 order by tree_level asc;
INST_ID SID SQL_ID EVENT STATUS ISLEAF TREE TREE_LEVEL
---------- ---------- ------------- ---------------------------------------- -------- ---------- -------------------------------------------------------------------------------- ----------
1 1892 90frgddxc2my0 enq: TX - row lock contention ACTIVE 1 <-1892 1
SQL> select count(*)
2 from gv$session
3 where event='enq: TX - row lock contention'
4 and inst_id=1
5 and status='ACTIVE';
COUNT(*)
----------
210
經確定90frgddxc2my0是一個update語句
SQL> select sql_fulltext from v$sql where sql_id='90frgddxc2my0';
SQL_FULLTEXT
--------------------------------------------------------------------------------
UPDATE INTF.OMGALARM A SET A.ALARMACTION = :B3 , A.VERSION = A.RECEIVETIME, A.RE
檢視叢集資料庫的日誌組狀態，發現例項2的redo組已經全部用上。SQL> select sql_fulltext from v$sql where sql_id='90frgddxc2my0';
SQL_FULLTEXT
--------------------------------------------------------------------------------
UPDATE INTF.OMGALARM A SET A.ALARMACTION = :B3 , A.VERSION = A.RECEIVETIME, A.RE
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ------------
1 1 58663 1073741824 2 NO ACTIVE 1.5122E+13 10-OCT-16
2 1 58662 1073741824 2 NO INACTIVE 1.5122E+13 10-OCT-16
3 1 58661 1073741824 2 NO INACTIVE 1.5122E+13 10-OCT-16
4 1 58660 1073741824 2 NO INACTIVE 1.5122E+13 10-OCT-16
5 1 58665 1073741824 2 NO CURRENT 1.5122E+13 10-OCT-16
6 1 58664 1073741824 2 NO ACTIVE 1.5122E+13 10-OCT-16
7 2 77116 1073741824 2 NO ACTIVE 1.5122E+13 11-OCT-16
8 2 77117 1073741824 2 NO ACTIVE 1.5122E+13 11-OCT-16
9 2 77118 1073741824 2 NO ACTIVE 1.5122E+13 11-OCT-16
10 2 77114 1073741824 2 YES ACTIVE 1.5122E+13 10-OCT-16
11 2 77115 1073741824 2 NO ACTIVE 1.5122E+13 10-OCT-16
12 2 77119 1073741824 2 NO CURRENT 1.5122E+13 11-OCT-16
到此，透過查詢到的資訊並結合告警日誌，可以肯定：大量併發dml操作，引發歸檔暴增，耗光歸檔空間，使用完redo組，導致會話積壓，進而叢集hang住，應用系統響應緩慢。
透過觀察1節點的告警日誌，還發現2個例項的redo切換很頻繁，1分鐘一次：
節點1
Mon Oct 10 20:05:19 EAT 2016
Thread 1 cannot allocate new log, sequence 58490
All online logs needed archiving
Current log# 1 seq# 58489 mem# 0: +ORCLSYSDG01/orcl/onlinelog/group_1.262.819722231
Current log# 1 seq# 58489 mem# 1: +ORCLGDG02/orcl/onlinelog/group_1.264.819722239
Mon Oct 10 20:05:48 EAT 2016
Thread 1 advanced to log sequence 58490 (LGWR switch)
Current log# 5 seq# 58490 mem# 0: +ORCLGDG01/orcl/onlinelog/group_5.293.819723461
Current log# 5 seq# 58490 mem# 1: +ORCLGDG02/orcl/onlinelog/group_5.268.819723471
Mon Oct 10 20:06:18 EAT 2016
ORACLE Instance hnrmdb1 - Can not allocate log, archival required
Mon Oct 10 20:06:18 EAT 2016
Thread 1 cannot allocate new log, sequence 58491
All online logs needed archiving
Current log# 5 seq# 58490 mem# 0: +ORCLGDG01/orcl/onlinelog/group_5.293.819723461
Current log# 5 seq# 58490 mem# 1: +ORCLGDG02/orcl/onlinelog/group_5.268.819723471
Mon Oct 10 20:06:20 EAT 2016
Thread 1 advanced to log sequence 58491 (LGWR switch)
Current log# 6 seq# 58491 mem# 0: +ORCLGDG01/orcl/onlinelog/group_6.292.819723491
Current log# 6 seq# 58491 mem# 1: +ORCLGDG02/orcl/onlinelog/group_6.267.819723505
採取的措施是，清理歸檔空間並增加2個例項的redo組數。
增加redo組：
SQL> select * from v$logfile;
GROUP# STATUS TYPE MEMBER IS_
---------- ------- ------- ------------------------------------------------------------- ---
7 ONLINE +ORCLDG01/orcl/onlinelog/group_7.275.819721599 NO
7 ONLINE +ORCLDG02/orcl/onlinelog/group_7.270.819721609 NO
12 ONLINE +ORCLDG01/orcl/onlinelog/group_12.291.819723519 NO
12 ONLINE +ORCLDG02/orcl/onlinelog/group_12.266.819723541 NO
11 ONLINE +ORCLDG01/orcl/onlinelog/group_11.297.819722567 NO
11 ONLINE +ORCLDG02/orcl/onlinelog/group_11.272.819722583 NO
10 ONLINE +ORCLDG01/orcl/onlinelog/group_10.288.819722545 NO
10 ONLINE +ORCLDG02/orcl/onlinelog/group_10.261.819722555 NO
9 ONLINE +ORCLDG01/orcl/onlinelog/group_9.266.819722429 NO
9 ONLINE +ORCLDG02/orcl/onlinelog/group_9.262.819722437 NO
8 ONLINE +ORCLDG01/orcl/onlinelog/group_8.265.819721623 NO
8 ONLINE +ORCLDG02/orcl/onlinelog/group_8.269.819721631 NO
4 ONLINE +ORCLDG01/orcl/onlinelog/group_4.294.819723443 NO
4 ONLINE +ORCLDG02/orcl/onlinelog/group_4.265.819723451 NO
3 ONLINE +ORCLDG01/orcl/onlinelog/group_3.279.819722327 NO
3 ONLINE +ORCLDG02/orcl/onlinelog/group_3.271.819722335 NO
2 ONLINE +ORCLDG01/orcl/onlinelog/group_2.267.819722257 NO
2 ONLINE +ORCLDG02/orcl/onlinelog/group_2.263.819722265 NO
1 ONLINE +ORCLDG01/orcl/onlinelog/group_1.262.819722231 NO
1 ONLINE +ORCLDG02/orcl/onlinelog/group_1.264.819722239 NO
6 ONLINE +ORCLDG01/orcl/onlinelog/group_6.292.819723491 NO
6 ONLINE +ORCLDG02/orcl/onlinelog/group_6.267.819723505 NO
5 ONLINE +ORCLDG01/orcl/onlinelog/group_5.293.819723461 NO
5 ONLINE +ORCLDG02/orcl/onlinelog/group_5.268.819723471 NO
SQL> alter database add logfile '+ORCLDG03' size 1073741824;
Database altered.
SQL> alter database add logfile thread 2 '+ORCLDG03' size 1073741824;
Database altered.
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ------------
1 1 58682 1073741824 2 NO ACTIVE 1.5122E+13 11-OCT-16
2 1 58679 1073741824 2 NO ACTIVE 1.5122E+13 11-OCT-16
3 1 58680 1073741824 2 NO ACTIVE 1.5122E+13 11-OCT-16
4 1 58678 1073741824 2 YES INACTIVE 1.5122E+13 11-OCT-16
5 1 58677 1073741824 2 YES INACTIVE 1.5122E+13 11-OCT-16
6 1 58681 1073741824 2 NO ACTIVE 1.5122E+13 11-OCT-16
7 2 77128 1073741824 2 NO INACTIVE 1.5122E+13 11-OCT-16
8 2 77129 1073741824 2 NO INACTIVE 1.5122E+13 11-OCT-16
9 2 77130 1073741824 2 NO ACTIVE 1.5122E+13 11-OCT-16
10 2 77126 1073741824 2 NO INACTIVE 1.5122E+13 11-OCT-16
11 2 77127 1073741824 2 NO INACTIVE 1.5122E+13 11-OCT-16
12 2 77125 1073741824 2 NO INACTIVE 1.5122E+13 11-OCT-16
13 1 58683 1073741824 1 NO CURRENT 1.5122E+13 11-OCT-16
14 2 77131 1073741824 1 NO CURRENT 1.5122E+13 11-OCT-16
增加完redo，檢視積壓的會話已經有下降了：
SQL> select count(1) from v$session ;
COUNT(1)
----------
852
SQL> select count(1) from v$session where status='ACTIVE';
COUNT(1)
----------
525
SQL> /
COUNT(1)
----------
525
SQL> /
COUNT(1)
----------
523
清理歸檔空間：
orcl1:[/oracle/app/oracle/admin/orcl/bdump]$asmcmd
ASMCMD> lsdg
State Type Rebal Unbal Sector Block AU Total_MB Free_MB Req_mir_free_MB Usable_file_MB Offline_disks Name
MOUNTED NORMAL N N 1024 4096 1048576 1024000 678 102400 -50861 0 ORCLDG01/
MOUNTED NORMAL N N 1024 4096 1048576 1024000 1389 102400 -50505 0 ORCLDG02/
MOUNTED EXTERN N N 1024 4096 1048576 512000 98144 0 98144 0 ORCLDG03/
MOUNTED NORMAL N N 1024 4096 1048576 307200 9659 102400 -46370 0 ORCLSYSDG01/
MOUNTED NORMAL N N 1024 4096 1048576 204800 13248 0 6624 0 SFILEARCHDG01/
ASMCMD> cd SFILEARCHDG01
ASMCMD> ls
HNRMDB/
ASMCMD> cd ORCL
ASMCMD> ls
ARCHIVELOG/
ASMCMD> cd ARCHIVELOG
ASMCMD> ls
2016_10_10/
2016_10_11/
ASMCMD> rm -r 2016_10_10
You may delete multiple files and/or directories.
Are you sure? (y/n) y
ORA-15032: not all alterations performed
ORA-15173: entry 'thread_1_seq_58606.367.924907269' does not exist in directory '2016_10_10' (DBD ERROR: OCIStmtExecute)
ASMCMD> lsdg
State Type Rebal Unbal Sector Block AU Total_MB Free_MB Req_mir_free_MB Usable_file_MB Offline_disks Name
MOUNTED NORMAL N N 1024 4096 1048576 1024000 678 102400 -50861 0 ORCLDG01/
MOUNTED NORMAL N N 1024 4096 1048576 1024000 1389 102400 -50505 0 ORCLDG02/
MOUNTED EXTERN N N 1024 4096 1048576 512000 98144 0 98144 0 ORCLDG03/
MOUNTED NORMAL N N 1024 4096 1048576 307200 9659 102400 -46370 0 ORCLSYSDG01/
MOUNTED NORMAL N N 1024 4096 1048576 204800 114264 0 57132 0 SFILEARCHDG01/
ASMCMD> exit
清理完歸檔空間，資料庫狀態恢復正常：
SQL> select count(*)
2 from gv$session
3 where event='enq: TX - row lock contention'
4 and inst_id=1
5 and status='ACTIVE'
6 ;
COUNT(*)
----------
117
SQL> /
COUNT(*)
----------
117
SQL> /
COUNT(*)
----------
43
SQL> /
COUNT(*)
----------
7
SQL> /
COUNT(*)
----------
0
SQL> select count(*) from v$session where status='ACTIVE';
COUNT(*)
----------
68
SQL> select count(*)
2 from gv$session
3 where event='enq: TX - row lock contention'
4 and inst_id=1
5 and status='ACTIVE'
6 ;
COUNT(*)
----------
0
本次故障處理過程中，發現大量如下資訊：Database altered.
SQL> alter database add logfile thread 2 '+ORCLDG03' size 1073741824;
Database altered.
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ------------
1 1 58682 1073741824 2 NO ACTIVE 1.5122E+13 11-OCT-16
2 1 58679 1073741824 2 NO ACTIVE 1.5122E+13 11-OCT-16
3 1 58680 1073741824 2 NO ACTIVE 1.5122E+13 11-OCT-16
4 1 58678 1073741824 2 YES INACTIVE 1.5122E+13 11-OCT-16
5 1 58677 1073741824 2 YES INACTIVE 1.5122E+13 11-OCT-16
6 1 58681 1073741824 2 NO ACTIVE 1.5122E+13 11-OCT-16
7 2 77128 1073741824 2 NO INACTIVE 1.5122E+13 11-OCT-16
8 2 77129 1073741824 2 NO INACTIVE 1.5122E+13 11-OCT-16
9 2 77130 1073741824 2 NO ACTIVE 1.5122E+13 11-OCT-16
10 2 77126 1073741824 2 NO INACTIVE 1.5122E+13 11-OCT-16
11 2 77127 1073741824 2 NO INACTIVE 1.5122E+13 11-OCT-16
12 2 77125 1073741824 2 NO INACTIVE 1.5122E+13 11-OCT-16
13 1 58683 1073741824 1 NO CURRENT 1.5122E+13 11-OCT-16
14 2 77131 1073741824 1 NO CURRENT 1.5122E+13 11-OCT-16
增加完redo，檢視積壓的會話已經有下降了：
SQL> select count(1) from v$session ;
COUNT(1)
----------
852
SQL> select count(1) from v$session where status='ACTIVE';
COUNT(1)
----------
525
SQL> /
COUNT(1)
----------
525
SQL> /
COUNT(1)
----------
523
清理歸檔空間：
orcl1:[/oracle/app/oracle/admin/orcl/bdump]$asmcmd
ASMCMD> lsdg
State Type Rebal Unbal Sector Block AU Total_MB Free_MB Req_mir_free_MB Usable_file_MB Offline_disks Name
MOUNTED NORMAL N N 1024 4096 1048576 1024000 678 102400 -50861 0 ORCLDG01/
MOUNTED NORMAL N N 1024 4096 1048576 1024000 1389 102400 -50505 0 ORCLDG02/
MOUNTED EXTERN N N 1024 4096 1048576 512000 98144 0 98144 0 ORCLDG03/
MOUNTED NORMAL N N 1024 4096 1048576 307200 9659 102400 -46370 0 ORCLSYSDG01/
MOUNTED NORMAL N N 1024 4096 1048576 204800 13248 0 6624 0 SFILEARCHDG01/
ASMCMD> cd SFILEARCHDG01
ASMCMD> ls
HNRMDB/
ASMCMD> cd ORCL
ASMCMD> ls
ARCHIVELOG/
ASMCMD> cd ARCHIVELOG
ASMCMD> ls
2016_10_10/
2016_10_11/
ASMCMD> rm -r 2016_10_10
You may delete multiple files and/or directories.
Are you sure? (y/n) y
ORA-15032: not all alterations performed
ORA-15173: entry 'thread_1_seq_58606.367.924907269' does not exist in directory '2016_10_10' (DBD ERROR: OCIStmtExecute)
ASMCMD> lsdg
State Type Rebal Unbal Sector Block AU Total_MB Free_MB Req_mir_free_MB Usable_file_MB Offline_disks Name
MOUNTED NORMAL N N 1024 4096 1048576 1024000 678 102400 -50861 0 ORCLDG01/
MOUNTED NORMAL N N 1024 4096 1048576 1024000 1389 102400 -50505 0 ORCLDG02/
MOUNTED EXTERN N N 1024 4096 1048576 512000 98144 0 98144 0 ORCLDG03/
MOUNTED NORMAL N N 1024 4096 1048576 307200 9659 102400 -46370 0 ORCLSYSDG01/
MOUNTED NORMAL N N 1024 4096 1048576 204800 114264 0 57132 0 SFILEARCHDG01/
ASMCMD> exit
清理完歸檔空間，資料庫狀態恢復正常：
SQL> select count(*)
2 from gv$session
3 where event='enq: TX - row lock contention'
4 and inst_id=1
5 and status='ACTIVE'
6 ;
COUNT(*)
----------
117
SQL> /
COUNT(*)
----------
117
SQL> /
COUNT(*)
----------
43
SQL> /
COUNT(*)
----------
7
SQL> /
COUNT(*)
----------
0
SQL> select count(*) from v$session where status='ACTIVE';
COUNT(*)
----------
68
SQL> select count(*)
2 from gv$session
3 where event='enq: TX - row lock contention'
4 and inst_id=1
5 and status='ACTIVE'
6 ;
COUNT(*)
----------
0
Wed Oct 12 20:19:41 EAT 2016
SUCCESS: diskgroup SFILEARCHDG01 was mounted
Wed Oct 12 20:19:46 EAT 2016
SUCCESS: diskgroup SFILEARCHDG01 was dismounted
經求證是10g ASM rac才會出現的，oracle採取的策略是在歸檔目錄不適用的時候對其下線，在歸檔程式需要切換歸檔的時候，才會將歸檔的卷組上線。
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/29357786/viewspace-2127004/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- enq: TX - row lock contentionENQ
- 等待事件enq: TX - row lock contention事件ENQ
- 【等待事件】-enq: TX - row lock contention事件ENQ
- 奇異的enq: TX - row lock contentionENQ
- 等待事件enq TX row lock contention分析事件ENQ
- Oracle Enqueues Wait Events 三 enq: TX - row lock contentionOracleENQAI
- enq: TX - row lock contention等待事件處理ENQ事件
- 無關的表引起的enq: TX - row lock contentionENQ
- AWR實戰分析之----enq: TX - row lock contentionENQ
- enq: TX – row lock contention的測試和案例分析ENQ
- 關於enq: TX - row lock contention行鎖的總結ENQ
- 20161208理解enq TX - row lock contentionENQ
- 使用oradebug dump processstate 來診斷enq: TX - row lock contentionENQ
- 一次資料庫相關操作卡住的排查--enq: TX - row lock contention資料庫ENQ
- zt_Oracle enq: TX contention 和 enq: TM contention 等待事件OracleENQ事件
- enq:TX - index contentionENQIndex
- enq: TX - index contentionENQIndex
- enq: TX - index contention等待ENQIndex
- ORACLE DATAGUARD災備歸檔空間滿導致的ORA-00600 [2619]Oracle
- ORACLE的歸檔空間滿導致的監聽故障資料庫無法啟動Oracle資料庫
- Oracle10g中的flashback啟用日誌歸檔,寫滿空間導致錯誤Oracle
- undo表空間滿導致的ogg discard檔案寫滿
- 歸檔空間不足導致例項死鎖
- enq: TX - index contention基礎理論ENQIndex
- 故障排除 | enq：TX - index contention等待事件ENQIndex事件
- Troubleshooting 'enq: TX - index contention' WaitsENQIndexAI
- 如何解決enq: TX- index contentionENQIndex
- 主外來鍵約束之主表插入未提交導致外來鍵表插入hang住的等待事件 TX-row lock contention事件
- 手工清除歸檔處理歸檔空間滿
- 10g ORACLE_HOME空間滿導致SYSAUX表空間離線OracleUX
- row lock contention 阻塞程式查詢
- enq: TX - index contention故障修復一例ENQIndex
- enq:SQ-contention / DFS lock handle（SV）ENQ
- 'enq: TX - index contention' Waits in a RAC Environment. [ID 873243.1]ENQIndexAI
- MySQL 磁碟空間滿導致表空間相關資料檔案損壞故障處理MySql
- 關於enq: TX - index contention 等待的探討與測試ENQIndex
- ASM空間爆滿導致資料庫掛起ASM資料庫
- AWR不自動刪除導致SYSAUX表空間滿UX