DBA警世录:无知者不可无畏

« ITPUB电子杂志第十期-诊断案例专刊出版 | Blog首页 | DBA Scripts:获取用户创建语句 »

今天在Itpub上看到这样一个问题:执行了SHUTDOWN ABORT命令后，数据库文件打不开.

仔细看了一下帖子内容,发现作者执行了如下几条命令:

sql>alter datafile ' D：\oracle\QAS\sapdata1\system_1\SYSTEM.DATA1' offline drop;
database altered
sql>alter datafile ' D：\oracle\QAS\sapdata1\qasusr_1\QASUSR.DATA1' offline drop;
database altered

然后数据库无法打开.

我们注意到,在第一条命令中,作者offline drop掉了SYSTEM表空间中的数据文件.不管之前怎样,现在的数据库肯定是无法启动了.

在作者提供的日志中,可以看到之前数据库的错误提示:

Sun Jul 02 14:34:25 2006
Errors in file d:\oracle\qas\saptrace\background\qas_arc1_1844.trc:
ORA-19504: failed to create file "C:\ORACLE\QAS\ORAARCH\QASARCHARC01148.001"
ORA-19504: failed to create file "C:\ORACLE\QAS\ORAARCH\QASARCHARC01148.001"
ORA-27044: unable to write the header block of file
OSD-04008: WriteFile() failure, unable to write to file
O/S-Error: (OS 112) 磁盘空间不足。

是因为磁盘空间不足导致数据库错误.

而作者在不清楚具体原因时,竟然贸然offline drop了重要的数据文件.这一行为非常草率.做为DBA我们不仅不能想当然,也不能无知者无畏.

当然,offline drop的文件,可以通过重建控制文件的方式重新加入数据库,再尝试正常的恢复.

SQL> alter database backup controlfile to trace ;

Database altered.

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.

Total System Global Area 139531744 bytes
Fixed Size 452064 bytes
Variable Size 121634816 bytes
Database Buffers 16777216 bytes
Redo Buffers 667648 bytes
Database mounted.
SQL> select name from v$datafile;

NAME
--------------------------------------------------------
/opt/oracle/oradata/eygle/system01.dbf
/opt/oracle/oradata/eygle/undotbs01.dbf
/opt/oracle/oradata/eygle/users01.dbf
/opt/oracle/oradata/eygle/eygle01.dbf

SQL> alter database datafile '/opt/oracle/oradata/eygle/users01.dbf' offline drop;

Database altered.

SQL> alter database open;

Database altered.

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup nomount;
ORACLE instance started.

Total System Global Area 139531744 bytes
Fixed Size 452064 bytes
Variable Size 121634816 bytes
Database Buffers 16777216 bytes
Redo Buffers 667648 bytes
SQL> CREATE CONTROLFILE REUSE DATABASE "EYGLE" NORESETLOGS ARCHIVELOG
2 -- SET STANDBY TO MAXIMIZE PERFORMANCE
3 MAXLOGFILES 5
4 MAXLOGMEMBERS 3
5 MAXDATAFILES 100
6 MAXINSTANCES 1
7 MAXLOGHISTORY 226
8 LOGFILE
9 GROUP 1 '/opt/oracle/oradata/eygle/redo01.log' SIZE 10M,
10 GROUP 2 '/opt/oracle/oradata/eygle/redo02.log' SIZE 10M,
11 GROUP 3 '/opt/oracle/oradata/eygle/redo03.log' SIZE 10M
12 -- STANDBY LOGFILE
13 DATAFILE
14 '/opt/oracle/oradata/eygle/system01.dbf',
15 '/opt/oracle/oradata/eygle/undotbs01.dbf',
16 '/opt/oracle/oradata/eygle/users01.dbf',
17 '/opt/oracle/oradata/eygle/eygle01.dbf'
18 CHARACTER SET ZHS16GBK
19 ;

Control file created.

SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01113: file 3 needs media recovery
ORA-01110: data file 3: '/opt/oracle/oradata/eygle/users01.dbf'

SQL> recover datafile 3;
Media recovery complete.
SQL> alter database open;

Database altered.

SQL>

最后仍然要重复之前那句话:无知者不能无畏.

历史上的今天...
>> 2010-07-14文章:

内容整理-增加技术专题页面

[招聘信息] - 万达院线招聘DBA一名

关于Oracle Check类型约束的导入与启用

>> 2009-07-14文章:

10g Profile FAILED_LOGIN_ATTEMPTS如何计数?

>> 2008-07-14文章:

Donald Knuth:串行并行、单线程及多线程