oracle中对UNNAMEDnnnnn文件的处理-白红宇

一：构造一个UNNAMEDnnnnn文件

其中nnnnn为数据文件绝对文件号

1 控制文件备份时当前联机日志状态如下

select group#,archived,sequence#,status from v$log

GROUP# ARC SEQUENCE# STATUS

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

1 YES 2 ACTIVE

2 YES 1 ACTIVE

3 YES 3 ACTIVE

4 YES 4 ACTIVE

5 YES 5 ACTIVE

6 NO 6 CURRENT

2 当前控制控制文件备份如下

RMAN> list backup of controlfile;

List of Backup Sets

===================

BS Key Type LV Size Device Type Elapsed Time Completion Time

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

5 Full 7.11M DISK 00:00:03 14-DEC-12

BP Key: 5 Status: AVAILABLE Compressed: NO Tag: TAG20121214T062943

Piece Name: /oracle/app/db1/dbs/0fnsqnsl_1_1

Control File Included: Ckp SCN: 2779647876 Ckp time: 14-DEC-12

3 创建表空间及测试数据时日志状态如下：

SQL> select group#,archived,sequence#,status from v$Log;

GROUP# ARC SEQUENCE# STATUS

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

1 YES 14 INACTIVE

2 YES 13 INACTIVE

3 NO 15 CURRENT

4 YES 10 INACTIVE

5 YES 11 INACTIVE

6 YES 12 INACTIVE

4 此时创建表空间

SQL> create tablespace jiujian datafile '/oracle/test/jiujian.dbf' size 1m;

Tablespace created.

SQL> select file#,name from v$datafile;

FILE# NAME

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

1 /oracle/test/system1.dbf

2 /oracle/test/zxb.dbf

3 /oracle/test/sysaux01.dbf

4 /oracle/test/users01.dbf

5 /oracle/test/zxa.dbf

6 /oracle/test/test1.dbf

7 /oracle/test/zxc.dbf

8 /oracle/test/undotbs1.dbf

9 /oracle/test/zxbig.dbf

10 /oracle/test2.dbf

11 /oracle/test/jiujian.dbf

5 插入测试数据：

SQL> create table t3(x int) tablespace jiujian1;

Table created.

SQL> begin

2 for i in 1..20 loop

3 insert into t3 values(i);

4 end loop;

5 commit;

6 end;

7 /

PL/SQL procedure successfully completed.

SQL> select * from t3;

----------

6 6到14号归档的scn范围

SEQUENCE# NAME FIRST_CHANGE# NEXT_CHANGE#

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

6 /oracle/archive/1_6_801957264.dbf 2779647184 2779648239

7 /oracle/archive/1_7_801957264.dbf 2779648239 2779648241

8 /oracle/archive/1_8_801957264.dbf 2779648241 2779648243

9 /oracle/archive/1_9_801957264.dbf 2779648243 2779648245

10 /oracle/archive/1_10_801957264.dbf 2779648245 2779648247

11 /oracle/archive/1_11_801957264.dbf 2779648247 2779648250

12 /oracle/archive/1_12_801957264.dbf 2779648250 2779648252

13 /oracle/archive/1_13_801957264.dbf 2779648252 2779648254

14 /oracle/archive/1_14_801957264.dbf 2779648254 2779648256

7 重新启动数据库到非加载状态并恢复控制文件

SQL> startup force nomount;

ORACLE instance started.

Total System Global Area 322961408 bytes

Fixed Size 2020480 bytes

Variable Size 92277632 bytes

Database Buffers 222298112 bytes

Redo Buffers 6365184 bytes

Recovery Manager complete.

[oracle@oracle ~]$ rman target /

Recovery Manager: Release 10.2.0.1.0 - Production on Fri Dec 14 07:16:07 2012

connected to target database: CRM (not mounted)

RMAN> restore controlfile from '/oracle/app/db1/dbs/0fnsqnsl_1_1';

Starting restore at 14-DEC-12

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: sid=210 devtype=DISK

channel ORA_DISK_1: restoring control file

channel ORA_DISK_1: restore complete, elapsed time: 00:00:01

output filename=/oracle/CRM2/CRM/control01.ctl

output filename=/oracle/CRM2/CRM/control02.ctl

Finished restore at 14-DEC-12

RMAN> alter database mount;

database mounted

released channel: ORA_DISK_1

注意恢复控制文件后下边日志seq号的变化

SQL> select group#,archived,sequence#,status from v$log;

GROUP# ARC SEQUENCE# STATUS

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

1 YES 2 INACTIVE

2 YES 1 INACTIVE

6 NO 6 CURRENT

4 YES 4 INACTIVE

5 YES 5 INACTIVE

3 YES 3 INACTIVE

8 恢复

SQL> recover database ;

ORA-00283: recovery session canceled due to errors

ORA-01610: recovery using the BACKUP CONTROLFILE option must be done

SQL> recover database using backup controlfile;

ORA-00279: change 2779647876 generated at 12/14/2012 06:17:29 needed for thread

ORA-00289: suggestion : /oracle/archive/1_6_801957264.dbf

ORA-00280: change 2779647876 for thread 1 is in sequence #6

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

auto <---------------输入auto

ORA-00279: change 2779648239 generated at 12/14/2012 06:44:13 needed for thread

ORA-00289: suggestion : /oracle/archive/1_7_801957264.dbf

ORA-00280: change 2779648239 for thread 1 is in sequence #7

ORA-00278: log file '/oracle/archive/1_6_801957264.dbf' no longer needed for

this recovery

ORA-00279: change 2779648241 generated at 12/14/2012 06:44:14 needed for thread

ORA-00289: suggestion : /oracle/archive/1_8_801957264.dbf

ORA-00280: change 2779648241 for thread 1 is in sequence #8

ORA-00278: log file '/oracle/archive/1_7_801957264.dbf' no longer needed for

this recovery

ORA-00279: change 2779648243 generated at 12/14/2012 06:44:14 needed for thread

ORA-00289: suggestion : /oracle/archive/1_9_801957264.dbf

ORA-00280: change 2779648243 for thread 1 is in sequence #9

ORA-00278: log file '/oracle/archive/1_8_801957264.dbf' no longer needed for

this recovery

ORA-00279: change 2779648245 generated at 12/14/2012 06:44:15 needed for thread

ORA-00289: suggestion : /oracle/archive/1_10_801957264.dbf

ORA-00280: change 2779648245 for thread 1 is in sequence #10

ORA-00278: log file '/oracle/archive/1_9_801957264.dbf' no longer needed for

this recovery

ORA-00279: change 2779648247 generated at 12/14/2012 06:44:16 needed for thread

ORA-00289: suggestion : /oracle/archive/1_11_801957264.dbf

ORA-00280: change 2779648247 for thread 1 is in sequence #11

ORA-00278: log file '/oracle/archive/1_10_801957264.dbf' no longer needed for

this recovery

ORA-00279: change 2779648250 generated at 12/14/2012 06:44:19 needed for thread

ORA-00289: suggestion : /oracle/archive/1_12_801957264.dbf

ORA-00280: change 2779648250 for thread 1 is in sequence #12

ORA-00278: log file '/oracle/archive/1_11_801957264.dbf' no longer needed for

this recovery

ORA-00279: change 2779648252 generated at 12/14/2012 06:44:21 needed for thread

ORA-00289: suggestion : /oracle/archive/1_13_801957264.dbf

ORA-00280: change 2779648252 for thread 1 is in sequence #13

ORA-00278: log file '/oracle/archive/1_12_801957264.dbf' no longer needed for

this recovery

ORA-00279: change 2779648254 generated at 12/14/2012 06:44:22 needed for thread

ORA-00289: suggestion : /oracle/archive/1_14_801957264.dbf

ORA-00280: change 2779648254 for thread 1 is in sequence #14

ORA-00278: log file '/oracle/archive/1_13_801957264.dbf' no longer needed for

this recovery

ORA-00279: change 2779648256 generated at 12/14/2012 06:44:23 needed for thread

ORA-00289: suggestion : /oracle/archive/1_15_801957264.dbf

ORA-00280: change 2779648256 for thread 1 is in sequence #15

ORA-00278: log file '/oracle/archive/1_14_801957264.dbf' no longer needed for

this recovery

ORA-00308: cannot open archived log '/oracle/archive/1_15_801957264.dbf'

ORA-27037: unable to obtain file status

Linux-x86_64 Error: 2: No such file or directory

Additional information: 3

注意

1 这里提示找不到15号归档，由于15号归档是恢复控制文件前，数据库当前联机日志。

2 seq号为15时对应的日志文件为

3 恢复控制文件后seq号发生了改变但是日志文件内容可能还没变。所以我们用seq号为15时刻的日志文件进行尝试性恢复，过程如下：

SQL> recover database using backup controlfile;

ORA-00279: change 2779648256 generated at 12/14/2012 06:44:23 needed for thread

ORA-00289: suggestion : /oracle/archive/1_15_801957264.dbf

ORA-00280: change 2779648256 for thread 1 is in sequence #15

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

ORA-00283: recovery session canceled due to errors

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

ORA-01110: data file 11: '/oracle/test/jiujian1.dbf'

ORA-01112: media recovery not started

从以上信息可看到我们之前新建的表空间已经被添加到了控制文件中

SQL> select file#,name,status from v$datafile;

FILE# NAME STATUS

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

1 /oracle/test/system1.dbf SYSTEM

2 /oracle/test/zxb.dbf ONLINE

3 /oracle/test/sysaux01.dbf ONLINE

4 /oracle/test/users01.dbf ONLINE

5 /oracle/test/zxa.dbf ONLINE

6 /oracle/test/test1.dbf ONLINE

7 /oracle/test/zxc.dbf ONLINE

8 /oracle/test/undotbs1.dbf ONLINE

9 /oracle/test/zxbig.dbf ONLINE

10 /oracle/test2.dbf ONLINE

11 /oracle/app/db1/dbs/UNNAMED00011 RECOVER

二：处理UNNAMEDnnnnn问题

1 重命名数据文件

SQL> alter database rename file '/oracle/app/db1/dbs/UNNAMED00011' to ' f';

Database altered.

2 重命名数据文件后数据文件头部信息和控制文件中数据文件信息如下：

的信息
数据文件头部情况	控制文件中该数据文件信息
chkpt cnt: 5	Checkpoint cnt:1
Checkpointed at scn: 0x0000.a5ae1636	Stop scn: 0xffff.ffffffff
thread:1 rba:(0xf.8f1.10)	thread:1 rba:(0xf.9.10)

3 综合以上信息我们需要继续应用联机日志/oracle/CRM2/CRM/redo03.log进行恢复过程，如下：

QL> recover database using backup controlfile;

ORA-00279: change 2779648278 generated at 12/14/2012 06:45:04 needed for thread

ORA-00289: suggestion : /oracle/archive/1_15_801957264.dbf

ORA-00280: change 2779648278 for thread 1 is in sequence #15

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

/oracle/CRM2/CRM/redo03.log

Log applied.

Media recovery complete.

4 恢复后数据文件头部信息和控制文件中数据文件信息如下：

数据文件/oracle/test/jiujian1.db的信息
数据文件头部情况	控制文件中该数据文件信息
chkpt cnt: 6	Checkpoint cnt:6
Checkpointed at scn: 0x0000.a5ae1636	Stop scn: 0x0000.a5ae1636

SQL> alter database open resetlogs;

Database altered.

SQL> select * from t3;

----------

20 rows selected.

本文转自 zhangxuwl 51CTO博客，原文链接：http://blog.51cto.com/jiujian/1089389，如需转载请自行联系原作者