由于控制文件計劃不當而導致數據庫停止
2024-07-21 02:34:15
供稿：網友
由于控制文件所在磁盤空間滿了，歸檔的時候無法再往控制文件中添加歸檔信息，導致數據庫crash。 這是由于當初對文件磁盤分配沒有充分考慮而導致的。 系統檢查到數據庫無法登陸，不過internal可以登陸察看日志文件，發現無法歸檔；同時檢查v$session_wait發現很多session等待與歸檔相關的事件。In alert.logThu Mar 31 17:44:25 2005
ARC0: Beginning to archive log# 3 seq# 865
Thu Mar 31 17:44:26 2005
ARC1: Beginning to archive log# 3 seq# 865
Thu Mar 31 17:44:27 2005
ARC0: Beginning to archive log# 3 seq# 865
Thu Mar 31 17:44:28 2005
ARC1: Beginning to archive log# 3 seq# 865
Thu Mar 31 17:44:29 2005
ARC0: Beginning to archive log# 3 seq# 865
Thu Mar 31 17:44:30 2005
ARC1: Beginning to archive log# 3 seq# 865
Thu Mar 31 17:44:31 2005
ARC0: Beginning to archive log# 3 seq# 865
Thu Mar 31 17:44:32 2005
ARC1: Beginning to archive log# 3 seq# 865
Thu Mar 31 17:44:33 2005 當時懷疑歸檔目錄寫滿了，一看還剩余好多空間。執行手工歸檔SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /data/Oracle/BMKLNP01/archive
Oldest online log sequence 865
Next log sequence to archive 865
Current log sequence 868
SQL> alter system archive log current;
alter system archive log current
*
ERROR at line 1:
ORA-19502: write error on file "/data/oracle/BMKLNP01/data1/control01.ctl",
blockno 969 (blocksize=8192)
ORA-27063: skgfospo: number of bytes read/written is incorrect
Additional information: 24576
Additional information: 32768 搜索錯誤信息，發現通常是磁盤寫滿導致；SQL> host ls -l /data/oracle/BMKLNP01/data1/control01.ctl /data/oracle/BMKLNP01/
index1/control02.ctl
-rw-r----- 1 oracle dba 7987200 Mar 31 17:46 /data/oracle/BMKLNP01/data
1/control01.ctl
-rw-r----- 1 oracle dba 7938048 Mar 31 17:46 /data/oracle/BMKLNP01/inde
x1/control02.ctllonespappb34>df -k /data/oracle/BMKLNP01/data1/control01.ctl
Filesystem kbytes used avail capacity Mounted on
/dev/vx/dsk/p4_srdf_oradg/bmklnp01_data1
2048000 2048000 0 100% /data/oracle/BMKLNP01/data1 過了一會，數據庫自動crash；可能是因為Arch進程嘗試多次后自動死掉導致。看來也應該增加個腳本檢查控制文件的空間了。
