由于控制文件计划不当而导致数据库停止
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进程尝试多次后自动死掉导致。看来也应该增加个脚本检查控制文件的空间了。
