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

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

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

作者：eygle |【转载时请务必以超链接形式标明文章原始出处和作者信息及本声明】
链接：http://www.eygle.com/archives/2006/07/be_careful_dba.html

站内相关文章|Related Articles

记录一下Drop表空间的速度

系统对象IDL_UB1$表的含义及作用

Oracle初学者入门指南-系统与用户数据分离

How to maintain Oracle10g Recyclebin?

今天在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>

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

-----
这篇【DBA警世录:无知者不可无畏】来自 www.eygle.com | CSDN技术网摘| del.icio.us|365Key

By eygle on 2006-07-14 13:13 | Comments (1) | Posted to Beginner | Edit |Pageviews:

相关文章	随机文章
记录一下Drop表空间的速度系统对象IDL_UB1$表的含义及作用 Oracle初学者入门指南-系统与用户数据分离 How to maintain Oracle10g Recyclebin?	又是一年放榜时 CSDN英雄会与《深入浅出Oracle》 Eygle.com两周年庆感谢d.c.b.a 新书第六章定稿 Danny Wright(丹尼·瑞特)的美妙音乐

网上相关主题:

留言 (1)

Terrible....

Posted by: yanggq at August 30, 2006 4:32 AM

www.eygle.com

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

留言 (1)

发表留言: