今天在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>
|
最后仍然要重复之前那句话:无知者不能无畏.
