新增資料檔案與恢復cf

Recovering Through an Added Datafile with a Backup Control File: Scenario

引自： b14192.pdf p404

如果有以下場景發生，

1 備份資料庫

2 建立了一個表空間，新增了兩個資料檔案

3 恢復了控制檔案，並開始做介質恢復

將會出現下述錯誤

ORA-00283: recovery session canceled due to errors

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

ORA-01110: data file 6: '/u01/app/oracle/oradata/al32/test02.dbf'

ORA-01110: data file 5: '/u01/app/oracle/oradata/al32/test01.dbf'

解決方法描述如下

[@more@]

啟動資料庫

SQL> startup

ORACLE instance started.

Total System Global Area 1258291200 bytes

Fixed Size 2083728 bytes

Variable Size 352322672 bytes

Database Buffers 889192448 bytes

Redo Buffers 14692352 bytes

Database mounted.

Database opened.

確定當前的資料檔案

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

FILE# NAME

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

4 /u01/app/oracle/oradata/al32/ts_ww01.dbf

3 /u01/app/oracle/oradata/al32/sysaux01.dbf

2 /u01/app/oracle/oradata/al32/undotbs01.dbf

1 /u01/app/oracle/oradata/al32/system01.dbf

SQL> select tablespace_name from dba_tablespaces;

TABLESPACE_NAME

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

SYSTEM

UNDOTBS1

SYSAUX

TEMP

TS_WW

對資料庫做個備份

RMAN> backup incremental level 0 database;

Starting backup at 20110114.08:55:45

using channel ORA_DISK_1

channel ORA_DISK_1: starting incremental level 0 datafile backupset

channel ORA_DISK_1: specifying datafile(s) in backupset

input datafile fno=00001 name=/u01/app/oracle/oradata/al32/system01.dbf

input datafile fno=00002 name=/u01/app/oracle/oradata/al32/undotbs01.dbf

input datafile fno=00003 name=/u01/app/oracle/oradata/al32/sysaux01.dbf

input datafile fno=00004 name=/u01/app/oracle/oradata/al32/ts_ww01.dbf

channel ORA_DISK_1: starting piece 1 at 20110114.08:55:45

channel ORA_DISK_1: finished piece 1 at 20110114.08:56:00

piece handle=/u01/fra/AL32/backupset/2011_01_14/o1_mf_nnnd0_TAG20110114T085545_6lz7rkk6_.bkp tag=TAG20110114T085545 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:15

channel ORA_DISK_1: starting incremental level 0 datafile backupset

channel ORA_DISK_1: specifying datafile(s) in backupset

including current control file in backupset

including current SPFILE in backupset

channel ORA_DISK_1: starting piece 1 at 20110114.08:56:00

channel ORA_DISK_1: finished piece 1 at 20110114.08:56:01

piece handle=/u01/fra/AL32/backupset/2011_01_14/o1_mf_ncsn0_TAG20110114T085545_6lz7s0qq_.bkp tag=TAG20110114T085545 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01

Finished backup at 20110114.08:56:01

新增表空間

SQL>

1 create tablespace test datafile

2 '/u01/app/oracle/oradata/al32/test01.dbf' size 10M,

3 '/u01/app/oracle/oradata/al32/test02.dbf' size 10M

4 extent management local

5* segment space management auto

6 /

Tablespace created.

停止例項

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL>

RMAN> startup nomount

Oracle instance started

Total System Global Area 1258291200 bytes

Fixed Size 2083728 bytes

Variable Size 352322672 bytes

Database Buffers 889192448 bytes

Redo Buffers 14692352 bytes

恢復控制檔案

RMAN> restore controlfile from autobackup;

Starting restore at 20110114.08:59:02

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: sid=156 devtype=DISK

recovery area destination: /u01/fra

database name (or database unique name) used for search: AL32

channel ORA_DISK_1: no autobackups found in the recovery area

autobackup search outside recovery area not attempted because DBID was not set

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-03002: failure of restore command at 01/14/2011 08:59:04

RMAN-06172: no autobackup found or specified handle is not a valid copy or piece

RMAN> restore controlfile from '/u01/fra/AL32/backupset/2011_01_14/o1_mf_ncsn0_TAG20110114T085545_6lz7s0qq_.bkp';

Starting restore at 20110114.08:59:23

using channel ORA_DISK_1

channel ORA_DISK_1: restoring control file

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

output filename=/u01/app/oracle/oradata/al32/control01.ctl

output filename=/u01/app/oracle/oradata/al32/control02.ctl

output filename=/u01/app/oracle/oradata/al32/control03.ctl

Finished restore at 20110114.08:59:25

mount資料庫

RMAN> sql 'alter database mount';

sql statement: alter database mount

released channel: ORA_DISK_1

嘗試恢復資料庫

RMAN> recover database;

Starting recover at 20110114.09:00:54

Starting implicit crosscheck backup at 20110114.09:00:54

allocated channel: ORA_DISK_1

channel ORA_DISK_1: sid=156 devtype=DISK

Crosschecked 1 objects

Finished implicit crosscheck backup at 20110114.09:00:55

Starting implicit crosscheck copy at 20110114.09:00:55

using channel ORA_DISK_1

Finished implicit crosscheck copy at 20110114.09:00:55

searching for all files in the recovery area

cataloging files...

cataloging done

List of Cataloged Files

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

File Name: /u01/fra/AL32/backupset/2011_01_14/o1_mf_ncsn0_TAG20110114T085545_6lz7s0qq_.bkp

using channel ORA_DISK_1

starting media recovery

archive log thread 1 sequence 1 is already on disk as file /u01/app/oracle/oradata/al32/redo01.log

archive log filename=/u01/app/oracle/oradata/al32/redo01.log thread=1 sequence=1

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-03002: failure of recover command at 01/14/2011 09:00:57

ORA-01422: exact fetch returns more than requested number of rows

RMAN-20505: create datafile during recovery

RMAN-11003: failure during parse/execution of SQL statement: alter database recover logfile '/u01/app/oracle/oradata/al32/redo01.log'

ORA-00283: recovery session canceled due to errors

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

ORA-01110: data file 6: '/u01/app/oracle/oradata/al32/test02.dbf'

ORA-01110: data file 5: '/u01/app/oracle/oradata/al32/test01.dbf'

檢視當前資料檔案

SQL> conn /as sysdba

Connected.

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

FILE# NAME

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

4 /u01/app/oracle/oradata/al32/ts_ww01.dbf

3 /u01/app/oracle/oradata/al32/sysaux01.dbf

2 /u01/app/oracle/oradata/al32/undotbs01.dbf

1 /u01/app/oracle/oradata/al32/system01.dbf

5 /u01/app/oracle/product/10.2.0/db_1/dbs/UNNAMED00005

6 /u01/app/oracle/product/10.2.0/db_1/dbs/UNNAMED00006

檢視alert日誌，確認檔案號與檔名稱

$ tail $ORACLE_BASE/admin/al32/bdump/alert_al32.log

Media Recovery Log /u01/app/oracle/oradata/al32/redo01.log

File #5 added to control file as 'UNNAMED00005'. Originally created as:

'/u01/app/oracle/oradata/al32/test01.dbf'

File #6 added to control file as 'UNNAMED00006'. Originally created as:

'/u01/app/oracle/oradata/al32/test02.dbf'

Some recovered datafiles maybe left media fuzzy

Media recovery may continue but open resetlogs may fail

Fri Jan 14 09:00:57 2011

Media Recovery failed with error 1244

ORA-283 signalled during: alter database recover logfile '/u01/app/oracle/oradata/al32/redo01.log'...

將檔名修改正確

SQL> alter database rename file

2 '/u01/app/oracle/product/10.2.0/db_1/dbs/UNNAMED00005' to

3 '/u01/app/oracle/oradata/al32/test01.dbf';

Database altered.

SQL> alter database rename file

2 '/u01/app/oracle/product/10.2.0/db_1/dbs/UNNAMED00006' to

3 '/u01/app/oracle/oradata/al32/test02.dbf';

Database altered.

繼續恢復操作

SQL> recover database using backup controlfile until cancel;

ORA-00279: change 9467942 generated at 01/14/2011 08:57:31 needed for thread 1

ORA-00289: suggestion : /u01/fra/AL32/archivelog/2011_01_14/o1_mf_1_1_%u_.arc

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

Specify log: {=suggested | filename | AUTO | CANCEL}

/u01/app/oracle/oradata/al32/redo01.log

Log applied.

Media recovery complete.

SQL> alter database open noresetlogs;

alter database open noresetlogs

*

ERROR at line 1:

ORA-01588: must use RESETLOGS option for database open

SQL> alter database open resetlogs;

Database altered.

SQL>

完成恢復