一个错误的数据文件的恢复

文件名与目录名重名[@more@]

环境:RHEL AS3 测试库

12:03:07 SQL> select * from v$version;

BANNER

----------------------------------------------------------------

Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production

PL/SQL Release 9.2.0.4.0 - Production

CORE 9.2.0.3.0 Production

TNS for Linux: Version 9.2.0.4.0 - Production

NLSRTL Version 9.2.0.4.0 – Production

问题: 测试库不能正常SHUTDOWN

SQL>shutdown immediate;

ORA-01116: error in opening database file 15

ORA-01110: data file 15: '/SERVER/ora9/oradata/ora9

'

ORA-27041: unable to open file

Linux Error: 2: No such file or directory

查看数据文件.

SQL>select file_id,file_name,tablespace_name from dba_data_files

1 /SERVER/ora9/oradata/ora9/system01.dbf SYSTEM

2 /SERVER/ora9/oradata/ora9/hyf.dbf HYF

3 /SERVER/ora9/oradata/ora9/cwmlite01.dbf CWMLITE

4 /SERVER/ora9/oradata/ora9/drsys01.dbf DRSYS

5 /SERVER/ora9/oradata/ora9/indx01.dbf INDX

6 /SERVER/ora9/oradata/ora9/odm01.dbf ODM

7 /SERVER/ora9/oradata/ora9/tools01.dbf TOOLS

8 /SERVER/ora9/oradata/ora9/users01.dbf USERS

9 /SERVER/ora9/oradata/ora9/xdb01.dbf XDB

10 /SERVER/ora9/oradata/ora9/t2007q1.dbf T2007Q1

11 /SERVER/ora9/oradata/ora9/t2007q2.dbf T2007Q2

12 /SERVER/ora9/oradata/ora9/t2007q3.dbf T2007Q3

13 /SERVER/ora9/oradata/ora9/t2007q4.dbf T2007Q4

14 /SERVER/ora9/oradata/ora9/undo.dbf UNDO

15 /SERVER/ora9/oradata/ora9 USERDATA

16 /SERVER/ora9/oradata/ora9/atu.dbf ATU

发现datafile 15 的文件名跟我的目录名一样.

想直接删除表空间:

SQL>drop tablespace USERDATA ;

ORA-01116: error in opening database file 15

ORA-01110: data file 15: '/SERVER/ora9/oradata/ora9

'

ORA-27041: unable to open file

Linux Error: 2: No such file or directory

OFFLINE:

SQL>alter tablespace UESRDATA offline;

ORA-01116: error in opening database file 15

ORA-01110: data file 15: '/SERVER/ora9/oradata/ora9

'

ORA-27041: unable to open file

Linux Error: 2: No such file or directory

试着更名,可是不能OFFLINE也不能改名.

想到的办法是:重建控制文件了.

- 先SHUTDOWN ABORT;

- STARTUP MOUNT:

- Alter database backup controlfile to trace;

得到重建控制文件脚本;

STARTUP NOMOUNT

CREATE CONTROLFILE REUSE DATABASE "ORA9" RESETLOGS FORCE LOGGING ARCHIVELOG

-- SET STANDBY TO MAXIMIZE PERFORMANCE

MAXLOGFILES 5

MAXLOGMEMBERS 3

MAXDATAFILES 100

MAXINSTANCES 1

MAXLOGHISTORY 226

LOGFILE

GROUP 1 '/SERVER/ora9/oradata/ora9/redo01.log' SIZE 20M,

GROUP 2 '/SERVER/ora9/oradata/ora9/redo02.log' SIZE 20M,

GROUP 3 '/SERVER/ora9/oradata/ora9/redo03.log' SIZE 20M

-- STANDBY LOGFILE

DATAFILE

'/SERVER/ora9/oradata/ora9/drsys01.dbf',

'/SERVER/ora9/oradata/ora9/indx01.dbf',

'/SERVER/ora9/oradata/ora9/odm01.dbf',

'/SERVER/ora9/oradata/ora9/tools01.dbf',

'/SERVER/ora9/oradata/ora9/users01.dbf',

'/SERVER/ora9/oradata/ora9/xdb01.dbf',

'/SERVER/ora9/oradata/ora9/t2007q1.dbf',

'/SERVER/ora9/oradata/ora9/t2007q2.dbf',

'/SERVER/ora9/oradata/ora9/t2007q3.dbf',

'/SERVER/ora9/oradata/ora9/t2007q4.dbf',

'/SERVER/ora9/oradata/ora9/undo.dbf',

'/SERVER/ora9/oradata/ora9',

'/SERVER/ora9/oradata/ora9/atu.dbf'

CHARACTER SET ZHS16GBK

并在脚本中删除错误的数据文件: '/SERVER/ora9/oradata/ora9'

在重建控制文件后,试着打开数据库:

11:37:28 SQL> alter database open resetlogs;

alter database open resetlogs

*

ERROR at line 1:

ORA-01194: file 1 needs more recovery to be consistent

ORA-01110: data file 1: '/SERVER/ora9/oradata/ora9/system01.dbf'

//需要恢复

11:38:13 SQL> recover database using backup controlfile;

ORA-00279: change 10703011 generated at 12/19/2007 07:22:31 needed for thread

ORA-00289: suggestion : /SERVER/ora9/arc/1_108.dbf

ORA-00280: change 10703011 for thread 1 is in sequence #108

11:38:31 Specify log: {=suggested | filename | AUTO | CANCEL}

/SERVER/ora9/oradata/ora9/redo02.log

ORA-00310: archived log contains sequence 107; sequence 108 required

ORA-00334: archived log: '/SERVER/ora9/oradata/ora9/redo02.log'

11:38:41 SQL> recover database using backup controlfile;

ORA-00279: change 10703011 generated at 12/19/2007 07:22:31 needed for thread

ORA-00289: suggestion : /SERVER/ora9/arc/1_108.dbf

ORA-00280: change 10703011 for thread 1 is in sequence #108

11:39:16 Specify log: {=suggested | filename | AUTO | CANCEL}

/SERVER/ora9/oradata/ora9/redo03.log (使用的是当前日志)

ORA-00283: recovery session canceled due to errors

ORA-01244: unnamed datafile(s) added to controlfile by media recovery

ORA-01110: data file 15: '/SERVER/ora9/oradata/ora9

'

ORA-01112: media recovery not started

11:42:07 SQL> recover database using backup controlfile;

ORA-00283: recovery session canceled due to errors

ORA-01111: name for data file 15 is unknown - rename to correct file

ORA-01110: data file 15: '/SERVER/ora9/product/9.2/dbs/UNNAMED00015'

ORA-01157: cannot identify/lock data file 15 - see DBWR trace file

ORA-01111: name for data file 15 is unknown - rename to correct file

ORA-01110: data file 15: '/SERVER/ora9/product/9.2/dbs/UNNAMED00015'

由于系统表空间中,在记录这个数据文件,而在控制文件中没有,所以系统报这个错误.

11:49:00 SQL> select name from v$datafile;

NAME

--------------------------------------------------------------------------------

/SERVER/ora9/oradata/ora9/system01.dbf

/SERVER/ora9/oradata/ora9/hyf.dbf

/SERVER/ora9/oradata/ora9/cwmlite01.dbf

/SERVER/ora9/oradata/ora9/drsys01.dbf

/SERVER/ora9/oradata/ora9/indx01.dbf

/SERVER/ora9/oradata/ora9/odm01.dbf

/SERVER/ora9/oradata/ora9/tools01.dbf

/SERVER/ora9/oradata/ora9/users01.dbf

/SERVER/ora9/oradata/ora9/xdb01.dbf

/SERVER/ora9/oradata/ora9/t2007q1.dbf

/SERVER/ora9/oradata/ora9/t2007q2.dbf

NAME

--------------------------------------------------------------------------------

/SERVER/ora9/oradata/ora9/t2007q3.dbf

/SERVER/ora9/oradata/ora9/t2007q4.dbf

/SERVER/ora9/oradata/ora9/undo.dbf

/SERVER/ora9/product/9.2/dbs/UNNAMED00015

/SERVER/ora9/oradata/ora9/atu.dbf

16 rows selected.

11:49:07 SQL> alter database datafile '/SERVER/ora9/product/9.2/dbs/UNNAMED00015' offline;

Database altered.

11:53:43 SQL> alter database open resetlogs;

alter database open resetlogs

*

ERROR at line 1:

ORA-01113: file 1 needs media recovery

ORA-01110: data file 1: '/SERVER/ora9/oradata/ora9/system01.dbf'

11:54:34 SQL> recover database using backup controlfile;

ORA-00279: change 10712117 generated at 12/19/2007 10:12:24 needed for thread 1

ORA-00289: suggestion : /SERVER/ora9/arc/1_108.dbf

ORA-00280: change 10712117 for thread 1 is in sequence #108

11:54:45 Specify log: {=suggested | filename | AUTO | CANCEL}

/SERVER/ora9/oradata/ora9/redo03.log (当前日志)

Log applied.

Media recovery complete.

11:55:01 SQL> alter database open resetlogs;

alter database open resetlogs

*

ERROR at line 1:

ORA-01245: offline file 15 will be lost if RESETLOGS is done

ORA-01111: name for data file 15 is unknown - rename to correct file

ORA-01110: data file 15: '/SERVER/ora9/product/9.2/dbs/UNNAMED00015'

11:55:09 SQL> alter database datafile '/SERVER/ora9/product/9.2/dbs/UNNAMED00015' offline;

Database altered.

11:55:32 SQL> alter database open resetlogs;

alter database open resetlogs

*

ERROR at line 1:

ORA-01245: offline file 15 will be lost if RESETLOGS is done

ORA-01111: name for data file 15 is unknown - rename to correct file

ORA-01110: data file 15: '/SERVER/ora9/product/9.2/dbs/UNNAMED00015'

11:58:47 SQL> alter database datafile 15 offline drop;

Database altered.

12:01:55 SQL> alter database open resetlogs;

Database altered.

到此,数据库正常打开,恢复全部完成.

后绪工作:

1. 增加临时文件:

alter tablespace temp add tempfile

'/SERVER/ora9/oradata/ora9/temp01.dbf' size 200M reuse;

2 . 做一个RMAN全备.

后续思考的问题:

这个错误的数据文件是怎么被系统(LINUX,ORACLE)接受的??

当我在安全备份后,想重演一下错误的发生过程时:却不成功.

13:50: SQL> create tablespace test1

datafile ' RVER/ora9/oradata/ora9' size 20M ;13:52:06 2

create tablespace test1

*

ERROR at line 1:

ORA-01119: error in creating database file ' RVER/ora9/oradata/ora9'

ORA-27038: skgfrcre: file exists

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/703656/viewspace-995024/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/703656/viewspace-995024/