物理磁盘空间使用已满导致数据库hang起
情况描述
一天公司小张过来咨询，说是数据库查询报错了；乍一看好像是数据库有坏快了，为了排查更加详细的错误信息，决定查看一下告警日志，发现问题所在，原来是数据库的物理磁盘空间满了
Writing to the above trace file is disabled for now on... Tue Jul 29 17:30:32 2014 Non critical error ORA-48181 caught while writing to trace file "/u01/app/oracle/diag/rdbms/orcl/ORCL/trace/ORCL_ora_2626.trc" Error message: Linux-x86_64 Error: 28: No space left on device Additional information: 1 Writing to the above trace file is disabled for now on... Tue Jul 29 18:00:00 2014 Non critical error ORA-48181 caught while writing to trace file "/u01/app/oracle/diag/rdbms/orcl/ORCL/trace/ORCL_j001_2703.trc" Error message: Linux-x86_64 Error: 28: No space left on device Additional information: 1 Writing to the above trace file is disabled for now on... Tue Jul 29 18:48:46 2014 Non critical error ORA-48113 caught while writing to trace file "/u01/app/oracle/diag/rdbms/orcl/ORCL/trace/ORCL_mmon_1642.trc" Error message: Writing to the above trace file is disabled for now on... Tue Jul 29 18:57:23 2014 Non critical error ORA-48181 caught while writing to trace file "/u01/app/oracle/diag/rdbms/orcl/ORCL/trace/ORCL_ora_2405.trc" Error message: Linux-x86_64 Error: 28: No space left on device Additional information: 1 Writing to the above trace file is disabled for now on... *********************************************************************** Fatal NI connect error 12170. Tue Jul 29 18:57:23 2014 Non critical error ORA-48181 caught while writing to trace file "/u01/app/oracle/diag/rdbms/orcl/ORCL/trace/ORCL_ora_2407.trc" Error message: Linux-x86_64 Error: 28: No space left on device Additional information: 1 Writing to the above trace file is disabled for now on... ***********************************************************************
然后就开始查看物理磁盘使用情况，是那个目录下的文件占用了大量的磁盘空间(du -sh /home/)，最终在$ORACLE_HOME/dbs目录下找到了大量的数据文件
lypt-> cd /u01/app/oracle/product/11.2.0/db_1/dbs/ lypt-> ll total 18141444 -rw-r----- 1 oracle oinstall 524296192 Jul 31 15:50 D:DATAXLCL_BASE_TBSPC001.DBF -rw-r----- 1 oracle oinstall 5368717312 Jul 31 13:05 D:DATAXLCL_BUSINESS_TBSPC001.DBF -rw-r----- 1 oracle oinstall 2147491840 Jul 31 13:05 D:DATAXLCL_DATA_TBSPC001.DBF -rw-r----- 1 oracle oinstall 524296192 Jul 31 13:20 D:DATAXLCL_FACT2_TBSPC001.DBF -rw-r----- 1 oracle oinstall 524296192 Jul 31 13:05 D:DATAXLCL_FACT3_TBSPC001.DBF -rw-r----- 1 oracle oinstall 524296192 Jul 31 13:05 D:DATAXLCL_FACT_TBSPC001.DBF -rw-r----- 1 oracle oinstall 524296192 Jul 31 15:00 D:DATAXLCL_GIS_TBSPC001.DBF -rw-r----- 1 oracle oinstall 2147491840 Jul 31 13:05 D:DATAXLCL_INDEX_TBSPC001.DBF -rw-r----- 1 oracle oinstall 524296192 Jul 31 13:05 D:DATAXLCL_PUB_TBSPC001.DBF -rw-r----- 1 oracle oinstall 524296192 Jul 31 13:05 D:DATAXLCL_REGIONINSIDE_TBSPC001.DBF -rw-r----- 1 oracle oinstall 524296192 Jul 31 16:38 D:DATAXLCL_REGIONUSER_TBSPC001.DBF -rw-r----- 1 oracle oinstall 524296192 Jul 31 13:05 D:DATAXLCL_STA_TBSPC001.DBF -rw-r----- 1 oracle oinstall 524296192 Jul 31 16:39 D:DATAXLCL_SUB1_TBSPC001.DBF -rw-r----- 1 oracle oinstall 524296192 Jul 31 13:05 D:DATAXLCL_SUB2_TBSPC001.DBF -rw-r----- 1 oracle oinstall 524296192 Jul 31 13:05 D:DATAXLCL_SUB3_TBSPC001.DBF -rw-r----- 1 oracle oinstall 524296192 Jul 31 16:39 D:DATAXLCL_SUB4_TBSPC001.DBF -rw-r----- 1 oracle oinstall 524296192 Jul 31 13:05 D:DATAXLCL_SUB5_TBSPC001.DBF -rw-r----- 1 oracle oinstall 524296192 Jul 31 13:05 D:DATAXLCL_SUB6_TBSPC001.DBF -rw-r----- 1 oracle oinstall 524296192 Jul 31 13:05 D:DATAXLCL_SUB7_TBSPC001.DBF -rw-r----- 1 oracle oinstall 524296192 Jul 31 13:05 D:DATAXLCL_SUB8_TBSPC001.DBF -rw-rw---- 1 oracle oinstall 1544 Jul 29 13:45 hc_ORCL.dat -rw-r--r-- 1 oracle oinstall 2851 May 15 2009 init.ora -rw-r----- 1 oracle oinstall 24 Jul 23 14:41 lkORCL -rw-r----- 1 oracle oinstall 1536 Jul 23 14:44 orapwORCL -rw-r----- 1 oracle oinstall 2560 Jul 31 15:50 spfileORCL.ora lypt->
看到这些文件之后，我有点懵；在Linux下创建表空间，数据文件命名上居然按照windows下来写；看了他们开发人员写的代码之后，原来是创建表空间的时候，没有指定具体的绝对路径，同时命名时候，居然是用windows下的路径风格来。接下来就是如何先解决数据库刮起的问题吧。
首先，找到一个个头大点的，无关紧要的文件，先挪动到其他系统上去；这时，数据库挂起的问题得到解决(只要你腾出空间来，Oracle会自动恢复回来)
其次，登陆Oracle,根据业务需要，适当调整一些数据表空间的位置，或者直接删除一些不必要的表空间。这个就要看具体情况了；
最后，问题得到解决.
