由数据文件目录空间利用率达到100%而导致数据库异常挂起的故障处理过程
错误内容描述:
Mon Aug 03 14:05:11 2015
Thread 1 cannot allocate new log, sequence 990
Private strand flush not complete
Current log# 1 seq# 989 mem# 0: /oradata/orcl/redo01.log
Errors in file /u01/oracle/diag/rdbms/orcl/orcl/trace/orcl_lgwr_8390.trc:
ORA-19502: write error on file "/oradata/orcl/control01.ctl", block number 597 (block
size=16384)
ORA-27072: File I/O error
Additional information: 4
Additional information: 597
Additional information: 4096
LGWR (ospid: 8390): terminating the instance due to error 19502
Mon Aug 03 14:05:14 2015
System state dump requested by (instance=1, osid=8390 (LGWR)), summary=[abnormal instance
termination].
System State dumped to trace file
/u01/oracle/diag/rdbms/orcl/orcl/trace/orcl_diag_8380_20150803140514.trc
错误原因分析:
查看oracle日志文件,查看具体的数据库错误内容:
*** 2015-08-03 14:05:14.313
error 19502 detected in background process
ORA-19502: write error on file "/oradata/orcl/control01.ctl", block number 597 (block
size=16384)
ORA-27072: File I/O error
Additional information: 4
Additional information: 597
Additional information: 4096
kjzduptcctx: Notifying DIAG for crash event
----- Abridged Call Stack Trace -----
ksedsts()+465
ksbrdp()+3507
()+265
----- End of Abridged Call Stack Trace -----
*** 2015-08-03 14:05:14.655
LGWR (ospid: 8390): terminating the instance due to error 19502
ksuitm: waiting up to [5] seconds before killing DIAG(8380)
从日志内容来看,数据库出现写错误,无法将数据写入磁盘。对于这种情况,一般有3种情况:
第一:数据文件目录空间或表空间满了
第二:数据文件目录变成只读文件系统了
第三:指定的读写文件不存在
查看表空间磁盘目录:
[oracle@orcl ~]$ df -h
Filesystem Size Used Avail Use% Mounted on
/dev/sda3 90G 27G 59G 31% /
tmpfs 3.9G 3.7M 3.9G 1% /dev/shm
/dev/sda1 1008M 62M 896M 7% /boot
/dev/sdb1 394G 374G 17M 100% /oradata
/dev/sr0 3.6G 3.6G 0 100% /media/RHEL_6.5 x86_64 Disc 1
发现表空间磁盘目录/dev/sdb1已使用100%,所以此次导致数据库异常挂起的原因就是数据文件目录空间
满了。
因为该目录不能进行存储扩容,所以只能通过挂载磁盘空间至其他目录,将该目录下的的部分数据文件
:此处将NMAFILE001' 、nmafile002、nmafile003、NMAIOOP001移动到空闲的目录下。在这里移动到
oracle用户下新建的目录oradata下。具体过程如下:
1、在oracle数据库目录下创建目录:mkdir /oradata/oradata/并挂载磁盘至该目录下。
2、关闭数据库(shutdown immediate),然后通过mv命令将上述数据文件移动到新建的目录下。
3、移动完成后启动数据库在mount模式下更改数据库配置。
SQL> startup mount
ORACLE instance started.
Total System Global Area 2856087552 bytes
Fixed Size 2256672 bytes
Variable Size 805306592 bytes
Database Buffers 2030043136 bytes
Redo Buffers 18481152 bytes
Database mounted.
在mount模式下使用alter database rename...命名使移动后的数据文件生效:
SQL> alter database rename file '/oradata/orcl/NMAFILE001' to
'/home/oracle/oradata/NMAFILE001';
Database altered.
SQL> alter database rename file '/oradata/orcl/nmafile002' to
'/home/oracle/oradata/nmafile002';