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;

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;

1 YES 2 INACTIVE

2 YES 1 INACTIVE

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

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

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

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

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

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

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

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

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号归档是恢复控制文件前，数据库当前联机日志。

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

<a>/oracle/CRM2/CRM/redo03.log</a>

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 重命名数据文件

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-00280: change 2779648278 for thread 1 is in sequence #15

/oracle/CRM2/CRM/redo03.log

Log applied.

Media recovery complete.

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

数据文件/oracle/test/jiujian1.db的信息

chkpt cnt: 6

Checkpoint cnt:6

Stop scn: 0x0000.a5ae1636

SQL> alter database open resetlogs;

SQL> select * from t3;

20 rows selected.

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

oracle中对UNNAMEDnnnnn文件的处理

继续阅读

报错：'mysql' 不是内部或外部命令，也不是可运行的程序或批处理文件。

Linxu常用命令技巧汇总

ERROR 1 (HY000): Can't create/write to file '/tmp/#sql_4188_1.MYI' (Errcode: 28)

艰难安装LDAP,SSL认证

《Linux命令行与Shell脚本编程大全第2版.布卢姆》pdf

MySQL的4种隔离级别？出现问题

XX系统实施过程问题总结

无组件上传图片到数据库中，最完整解决方案

【MySQL数据库】数据库索引事务1.索引2.事务

neo4j之cypher使用文档

NOSQL安全攻击

mybatis_入门程序Mybatis入门

登录plsql 报错 the account is locked --用户被锁

sqlServer根据经纬查距离

SequoiaDB巨杉数据库C++驱动概述

Oracle 批量查询传入List 返回List