由数据文件目录空间利用率达到100%而导致数据库异常挂起的故障处理过程

由数据文件目录空间利用率达到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<-kjzdssdmp()+267<-kjzduptcctx()+232<-kjzdicrshnfy()+63<-ksuitm()+5570<-


ksbrdp()+3507<-opirip()+623<-opidrv()+603<-sou2o()+103<-opimai_real()+250<-ssthrdmain


()+265<-main()+201<-__libc_start_main()+253
----- 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';


Database altered.


SQL>  alter database rename file '/oradata/orcl/nmafile003' to 


'/home/oracle/oradata/nmafile003';


Database altered.


SQL> alter database rename file '/oradata/orcl/NMAIOOP001' to 


'/home/oracle/oradata/NMAIOOP001';


Database altered.


生效后用打开数据库


SQL> alter database open;


Database altered.


SQL> quit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit 


Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options


打开数据库后，检查数据库，数据库依然是read,write模式，告警日志也已无错误信息抛出，客户反馈


数据库亦能正常访问。然后退出数据库，查看监听状态。


[oracle@orcl ~]$ lsnrctl status


LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 03-AUG-2015 16:34:11


Copyright (c) 1991, 2013, Oracle.  All rights reserved.


Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=orcl)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 11.2.0.4.0 - Production
Start Date                19-JUN-2015 10:44:14
Uptime                    45 days 5 hr. 49 min. 57 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/oracle/product/11.2.0/db_1/network/admin/listener.ora
Listener Log File         /u01/oracle/diag/tnslsnr/orcl/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=orcl)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Services Summary...
Service "orcl" has 1 instance(s).
  Instance "orcl", status READY, has 1 handler(s) for this service...
Service "orclXDB" has 1 instance(s).
  Instance "orcl", status READY, has 1 handler(s) for this service...
The command completed successfully
[oracle@orcl ~]$ 


通过查看，监听已正常监听到数据库服务，数据库使用正常。
通知客户，确认故障是否还存在，通过电话确认，故障已解决。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/31403259/viewspace-2138926/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/31403259/viewspace-2138926/