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: {
/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>
完成恢复
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/271063/viewspace-1044641/ ，如需转载，请注明出处，否则将追究法律责任。
