ASM丟魂記（一）
1、現象：
昨天，一個朋友很著急給我打電話，說客戶有個RAC資料庫突然的宕掉了，請我幫忙，通過遠端連線去檢視使用者的資料庫。由於我公司是電信的線路，無法通過遠端登入。然後，我讓朋友給我弄出了ALERT日誌來。
2、分析原因：
通過ALERT日誌，經過初步判斷是磁碟空間不足的問題。
Tue Mar 3 14:42:52 2009
Errors in file /u01/app/oracle/admin/orcl/udump/orcl2_ora_15166.trc:
ORA-19504: 無法建立檔案"+DATA/orcl/2_30388_640712653.dbf"
ORA-17502: ksfdcre: 4 未能建立檔案 +DATA/orcl/2_30388_640712653.dbf
ORA-15041: diskgroup space exhausted
Tue Mar 3 14:42:52 2009
ARCH: Error 19504 Creating archive log file to '+DATA/orcl/2_30388_640712653.dbf'
ARCH: Failed to archive thread 2 sequence 30388 (19504)
Tue Mar 3 14:42:52 2009
Errors in file /u01/app/oracle/admin/orcl/udump/orcl2_ora_15166.trc:
ORA-16038: 日誌 4 序列號 30388 無法歸檔
ORA-19504: 無法建立檔案""
ORA-00312: 聯機日誌 4 執行緒 2: '+DATA/orcl/onlinelog/group_4.293.640717141'
3、初步問題解決：
關閉資料庫歸檔模式。隨後重新啟動資料庫，結果資料庫能夠正常啟動。
4、遺留問題：
使用者是LIUNX下做的RAC,使用的是ASM磁碟組。目前有幾個疑問：
1、ASM磁碟組的磁碟耗盡，出現資料庫當機。
2.也許是磁碟空間還有，但是某個資料塊有問題，造成歸檔日誌無法寫入。
3.ASM本身有BUG，磁碟空間還有，但是沒有BALANCE。
5、後續解決：
分析是否磁碟空間有剩餘：
SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - 64bi
PL/SQL Release 10.2.0.2.0 - Production
CORE 10.2.0.2.0 Production
TNS for Linux: Version 10.2.0.2.0 - Production
NLSRTL Version 10.2.0.2.0 - Production
SQL> select total_mb,free_mb from v$asm_diskgroup;
TOTAL_MB FREE_MB
---------- ----------
1048570 23
SQL> select total_mb,free_mb from v$asm_disk;
TOTAL_MB FREE_MB
---------- ----------
1048570 0
從這裡看出，磁碟空間已經沒有。那莫排除後2種情況。對我來說，我最希望的是第一種情況。如果是後兩種，麻煩多多。
6、那莫是什麼佔用空間了呢?
list backup;
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
1364 Incr 0 27.50M SBT_TAPE 00:00:08 08-NOV-08
BP Key: 1364 Status: AVAILABLE Compressed: NO Tag: TAG20081108T021409
Handle: aojv59nu_1_1 Media:
SPFILE Included: Modification time: 08-NOV-08
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
1366 Incr 0 27.50M SBT_TAPE 00:00:07 09-NOV-08
BP Key: 1366 Status: AVAILABLE Compressed: NO Tag: TAG20081109T021431
Handle: aqjv7u4t_1_1 Media:
SPFILE Included: Modification time: 08-NOV-08
。。。。。
------- ---- -- ---------- ----------- ------------ ---------------
1456 Incr 0 27.50M SBT_TAPE 00:00:06 22-DEC-08
BP Key: 1456 Status: AVAILABLE Compressed: NO Tag: TAG20081222T021449
Handle: drk2rv96_1_1 Media:
SPFILE Included: Modification time: 22-DEC-08
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
1458 Incr 0 27.50M SBT_TAPE 00:00:06 23-DEC-08
BP Key: 1458 Status: AVAILABLE Compressed: NO Tag: TAG20081223T021531
Handle: dtk2ujp1_1_1 Media:
SPFILE Included: Modification time: 23-DEC-08
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
1459 Incr 0 56.61G SBT_TAPE 00:29:59 24-DEC-08
BP Key: 1459 Status: AVAILABLE Compressed: NO Tag: TAG20081224T021519
Handle: duk316do_1_1 Media:
List of Datafiles in backup set 1459
RMAN> crosscheck archivelog all;
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=143 instance=orcl2 devtype=DISK
validation succeeded for archived log
archive log filename=+DATA/orcl/1_16745_640712653.dbf recid=43748 stamp=674876551
validation succeeded for archived log
archive log filename=+DATA/orcl/1_16746_640712653.dbf recid=43749 stamp=674876587
validation succeeded for archived log
archive log filename=+DATA/orcl/1_16747_640712653.dbf recid=43750 stamp=674876612
validation succeeded for archived log
archive log filename=+DATA/orcl/1_16748_640712653.dbf recid=43752 stamp=674876629
。。。。。。。。。。
validation succeeded for archived log
archive log filename=+DATA/orcl/2_30380_640712653.dbf recid=52475 stamp=680487056
validation succeeded for archived log
archive log filename=+DATA/orcl/2_30381_640712653.dbf recid=52479 stamp=680487245
validation succeeded for archived log
archive log filename=+DATA/orcl/2_30382_640712653.dbf recid=52483 stamp=680487335
validation succeeded for archived log
archive log filename=+DATA/orcl/2_30383_640712653.dbf recid=52487 stamp=680487518
validation succeeded for archived log
archive log filename=+DATA/orcl/2_30384_640712653.dbf recid=52492 stamp=680487611
validation succeeded for archived log
archive log filename=+DATA/orcl/2_30385_640712653.dbf recid=52495 stamp=680487686
validation succeeded for archived log
archive log filename=+DATA/orcl/2_30386_640712653.dbf recid=52500 stamp=680487707
validation succeeded for archived log
archive log filename=+DATA/orcl/2_30387_640712653.dbf recid=52503 stamp=680488729
Crosschecked 8756 objects
30387-16745=1.4W個歸檔日誌。
由於歸檔日誌太多，造成控制檔案被覆蓋，只有顯示8756個歸檔日誌，實際我認為有1.4W個。
檢視
SQL> select * from v$controlfile_record_section;
TYPE RECORD_SIZE RECORDS_TOTAL RECORDS_USED FIRST_INDEX
---------------------------- ----------- ------------- ------------ -----------
LAST_INDEX LAST_RECID
---------- ----------
DATABASE 316 1 1 0
0 0
CKPT PROGRESS 8180 35 0 0
0 0
REDO THREAD 256 32 2 0
0 2
TYPE RECORD_SIZE RECORDS_TOTAL RECORDS_USED FIRST_INDEX
---------------------------- ----------- ------------- ------------ -----------
LAST_INDEX LAST_RECID
---------- ----------
REDO LOG 72 192 4 0
0 4
DATAFILE 428 1024 22 0
0 8552
FILENAME 524 4674 27 0
0 0
TYPE RECORD_SIZE RECORDS_TOTAL RECORDS_USED FIRST_INDEX
---------------------------- ----------- ------------- ------------ -----------
LAST_INDEX LAST_RECID
---------- ----------
TABLESPACE 68 1024 23 0
0 35
TEMPORARY FILENAME 56 1024 1 0
0 1
RMAN CONFIGURATION 1108 50 0 0
0 0
TYPE RECORD_SIZE RECORDS_TOTAL RECORDS_USED FIRST_INDEX
---------------------------- ----------- ------------- ------------ -----------
LAST_INDEX LAST_RECID
---------- ----------
LOG HISTORY 56 8700 8700 4700
4699 52549
OFFLINE RANGE 200 1063 0 0
0 0
ARCHIVED LOG 584 8756 8756 4346
4345 52503
TYPE RECORD_SIZE RECORDS_TOTAL RECORDS_USED FIRST_INDEX
---------------------------- ----------- ------------- ------------ -----------
LAST_INDEX LAST_RECID
---------- ----------
BACKUP SET 40 1227 1227 340
339 1566
BACKUP PIECE 736 1000 1000 567
566 1566
BACKUP DATAFILE 116 1128 1128 124
123 4635
TYPE RECORD_SIZE RECORDS_TOTAL RECORDS_USED FIRST_INDEX
---------------------------- ----------- ------------- ------------ -----------
LAST_INDEX LAST_RECID
---------- ----------
BACKUP REDOLOG 76 11540 11540 9104
9103 26413
DATAFILE COPY 660 1016 1 1
1 1
BACKUP CORRUPTION 44 1115 0 0
0 0
TYPE RECORD_SIZE RECORDS_TOTAL RECORDS_USED FIRST_INDEX
---------------------------- ----------- ------------- ------------ -----------
LAST_INDEX LAST_RECID
---------- ----------
COPY CORRUPTION 40 1227 0 0
0 0
DELETED OBJECT 20 10862 10862 2541
2540 29695
PROXY COPY 852 1017 0 0
0 0
TYPE RECORD_SIZE RECORDS_TOTAL RECORDS_USED FIRST_INDEX
---------------------------- ----------- ------------- ------------ -----------
LAST_INDEX LAST_RECID
---------- ----------
BACKUP SPFILE 36 454 245 1
245 245
DATABASE INCARNATION 56 292 1 1
1 1
FLASHBACK LOG 84 2048 0 0
0 0
TYPE RECORD_SIZE RECORDS_TOTAL RECORDS_USED FIRST_INDEX
---------------------------- ----------- ------------- ------------ -----------
LAST_INDEX LAST_RECID
---------- ----------
RECOVERY DESTINATION 180 1 0 0
0 0
INSTANCE SPACE RESERVATION 28 1055 2 0
0 0
REMOVABLE RECOVERY FILES 32 1000 0 0
0 0
TYPE RECORD_SIZE RECORDS_TOTAL RECORDS_USED FIRST_INDEX
---------------------------- ----------- ------------- ------------ -----------
LAST_INDEX LAST_RECID
---------- ----------
RMAN STATUS 116 141 141 5
4 1837
THREAD INSTANCE NAME MAPPING 80 32 32 0
0 0
MTTR 100 32 2 0
0 0
TYPE RECORD_SIZE RECORDS_TOTAL RECORDS_USED FIRST_INDEX
---------------------------- ----------- ------------- ------------ -----------
LAST_INDEX LAST_RECID
---------- ----------
DATAFILE HISTORY 568 57 0 0
0 0
STANDBY DATABASE MATRIX 400 10 10 0
0 0
GUARANTEED RESTORE POINT 212 2048 0 0
0 0
TYPE RECORD_SIZE RECORDS_TOTAL RECORDS_USED FIRST_INDEX
---------------------------- ----------- ------------- ------------ -----------
LAST_INDEX LAST_RECID
---------- ----------
RESTORE POINT 212 2083 0 0
0 0
看來這個命令也是隻顯示控制檔案歸檔日誌的數量。
解決問題：有兩種途徑：
1.通過ORACLE內部的SQL語句刪除日誌。
2.通過ASMCMD介面來刪除，就能解決。
問題貌似在這裡就可以結束。。。。。。。。。。。
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/8334342/viewspace-561444/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- AsM丟魂記？（二）ASM
- AsM丟魂記？（三）ASM
- 一次asm磁碟頭部資訊丟失故障ASM
- ASM磁碟組丟失member kfed修復ASM
- 網路驚魂記 (轉)
- ASM Disk丟失的臨時解決方法ASM
- ASM學習筆記_ASM安裝ASM筆記
- 類魂的魂在哪裡？——類魂遊戲設計思考遊戲設計
- asm 磁碟更換記ASM
- 最近的一次ASM diskgroup線上遷移記錄ASM
- asm磁碟組中的磁碟頭資訊丟失恢復實驗ASM
- ASM相關概念（筆記）ASM筆記
- 登登等登 瞪登等登，原生一對一直播交友app原始碼 丟丟丟APP原始碼
- ASM驅動安裝與ASM盤建立（一）ASM
- ASM管理環境----資料檔案丟失介質恢復（MEDIA RECOVERY）ASM
- 記一次驚魂的Win10啟動卡死問題恢復過程Win10
- 一個 MVC 框架以 MVVM 之「魂」復活了！MVC框架MVVM
- Java反射詳解篇--一篇入魂Java反射
- 從《黑暗之魂1》的地圖設計一覽魂系遊戲的創造之美（上）地圖遊戲
- 從《黑暗之魂1》的地圖設計一覽魂系遊戲的創造之美（中）地圖遊戲
- 從《黑暗之魂1》的地圖設計一覽魂系遊戲的創造之美（下）地圖遊戲
- 驚魂36小時，一次生產事故，動態磁碟刪除卷分割槽丟失，資料恢復案例實戰資料恢復
- Oracle 11g RAC ASM VOTEDISK所在磁碟全部丟失重大故障恢復OracleASM
- 針對11.2 RAC丟失OCR和Votedisk所在ASM Diskgroup的恢復手段ASM
- ASM學習筆記_磁碟的管理ASM筆記
- 記一次oracle資料庫redolog全部丟失的恢復Oracle資料庫
- 修復ASM磁碟頭（一）ASM
- ASM磁碟故障診斷（一）ASM
- 丟失重做日誌讀書筆記筆記
- IT業魂在何方
- asm files,asm directories,asm templatesASM
- 記錄一次重啟伺服器 Redis 資料丟失問題伺服器Redis
- MySQL的root密碼忘記或丟失的解決方法一例MySql密碼
- 記一次Oracle 聯機日誌檔案丟失的處理方案Oracle
- ASM學習筆記_體系結構ASM筆記
- ASM Failure Group的一點理解ASMAI
- asm的一些基本操作ASM
- RabbitMQ靈魂拷問MQ