利川信息网
育儿
当前位置:首页 > 育儿

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

发布时间:2019-09-13 20:03:41 编辑:笔名

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

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

查看本文来源

汉森四磨汤的主要成分
精神焦虑抑郁消化不良胃反酸
孩子中暑怎么办
小孩发烧怎么退烧