站点导航：开发者在线 Builder.com.cn数据库DBA警世录:无知者不可无畏

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

开发者在线 Builder.com.cn 更新时间:2007-09-17作者：盖国强 来源:eygle.com

本文关键词： 盖国强 DBA

 

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

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

sql>alter datafile ' D：oracleQASsapdata1system_1SYSTEM.DATA1' offline drop;
database altered
sql>alter datafile ' D：oracleQASsapdata1qasusr_1QASUSR.DATA1' offline drop;
database altered

然后数据库无法打开.

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

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

Sun Jul 02 14:34:25 2006
Errors in file d:oracleqassaptracebackgroundqas_arc1_1844.trc:
ORA-19504: failed to create file "C:ORACLEQASORAARCHQASARCHARC01148.001"
ORA-19504: failed to create file "C:ORACLEQASORAARCHQASARCHARC01148.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>

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

查看本文来源

推荐阅读与 盖国强 DBA 相关开发者在线内容

盖国强 DBA相关技术文章

用户评论

  • 用户名
  • 评论内容

BBS讨论

热门栏目推荐

热门软件技术推荐


开发者在线 盖国强 最新报道

开发者在线 DBA 最新报道