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