情景回顾:尝试做RMAN恢复实验,首先做了一个全备,然后shutdown,删除所有controlfile,后恢复。然后又shutdown,删除所有controlfile和datafile,尝试恢复出现以下情况:
C:\Documents and Settings\p485224>rman target /
Recovery Manager: Release 9.2.0.1.0 - Production
Copyright (c) 1995, 2002, Oracle Corporation. All rights reserved.
connected to target database (not started)
RMAN> startup
Oracle instance started
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of startup command at 04/11/2008 12:55:28
ORA-00205: error in identifying controlfile, check alert log for more info
RMAN> restore controlfile from autobackup;
Starting restore at 2008-04-11 12:55:47
using target database controlfile instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=11 devtype=DISK
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 04/11/2008 12:55:47
RMAN-06495: must explicitly specify DBID with SET DBID command
RMAN> set dbid=3206997025;
executing command: SET DBID
RMAN> restore controlfile from autobackup;
Starting restore at 2008-04-11 12:56:08
using channel ORA_DISK_1
channel ORA_DISK_1: looking for autobackup on day: 20080411
channel ORA_DISK_1: autobackup found: c-3206997025-20080411-00
channel ORA_DISK_1: controlfile restore from autobackup complete
replicating controlfile
input filename=D:\OPT\ORACLE9I\PRODUCT\9.2.0\ORADATA\ORA92US\CONTROL01.CTL
output filename=D:\OPT\ORACLE9I\PRODUCT\9.2.0\ORADATA\ORA92US\CONTROL02.CTL
output filename=D:\OPT\ORACLE9I\PRODUCT\9.2.0\ORADATA\ORA92US\CONTROL03.CTL
Finished restore at 2008-04-11 12:56:17
RMAN> recover database;
Starting recover at 2008-04-11 12:56:23
using channel ORA_DISK_1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 04/11/2008 12:56:23
ORA-01507: database not mounted
RMAN> alter database mount;
database mounted
RMAN> recover database;
Starting recover at 2008-04-11 12:56:36
using channel ORA_DISK_1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 04/11/2008 12:56:36
RMAN-06094: datafile 1 must be restored
RMAN> restore database;
Starting restore at 2008-04-11 12:56:43
using channel ORA_DISK_1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 04/11/2008 12:56:44
RMAN-06026: some targets not found - aborting restore
RMAN-06023: no backup or copy of datafile 3 found to restore
RMAN-06023: no backup or copy of datafile 2 found to restore
RMAN-06023: no backup or copy of datafile 1 found to restore
RMAN>
解决过程:根据同事的建议,如果是catalog的话,可以用set incarnation到备份之前的version来解决;因为我没有用catalog,所以只能用另外一种方法,就是用dbms_backup_restore包从备份集中恢复。过程如下:
C:\Documents and Settings\p485224>set oracle_sid=ora92us
C:\Documents and Settings\p485224>sqlplus
SQL*Plus: Release 9.2.0.1.0 - Production on Fri Apr 11 19:18:39 2008
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Enter user-name: / as sysdba
Connected to an idle instance.
SQL>
SQL>
SQL> startup nomount;
ORACLE instance started.
Total System Global Area 135338868 bytes
Fixed Size 453492 bytes
Variable Size 109051904 bytes
Database Buffers 25165824 bytes
Redo Buffers 667648 bytes
SQL>
//在nomount状态下恢复datafile by using dbms_backup_restore
SQL> DECLARE
2 devtype varchar2(256);
3 done boolean;
4 BEGIN
5 devtype:=sys.dbms_backup_restore.deviceAllocate (type=>'',ident=>'t1');
6 sys.dbms_backup_restore.restoreSetDatafile;
7 sys.dbms_backup_restore.restoreDatafileTo(dfnumber=>01,
8 toname=>'D:\opt\oracle9i\product\9.2.0\oradata\ora92us\system01.dbf');
9 sys.dbms_backup_restore.restoreDatafileTo(dfnumber=>02,
10 toname=>'D:\opt\oracle9i\product\9.2.0\oradata\ora92us\undotbs01.dbf');
11 sys.dbms_backup_restore.restoreDatafileTo(dfnumber=>03,
12 toname=>'D:\opt\oracle9i\product\9.2.0\oradata\ora92us\users01.dbf');
13 sys.dbms_backup_restore.restoreBackupPiece(done=>done,
14 handle=>'D:\ora_bak\RMAN\ORA92US\DF_ORA92US_1_1.BAK', params=>null);
15 sys.dbms_backup_restore.deviceDeallocate;
16 END;
17 /
PL/SQL procedure successfully completed.
SQL> CREATE CONTROLFILE REUSE DATABASE "ORA92US" NORESETLOGS ARCHIVELOG
2 -- SET STANDBY TO MAXIMIZE PERFORMANCE
3 MAXLOGFILES 5
4 MAXLOGMEMBERS 3
5 MAXDATAFILES 100
6 MAXINSTANCES 1
7 MAXLOGHISTORY 1361
8 LOGFILE
9 GROUP 1 'D:\opt\oracle9i\product\9.2.0\oradata\ora92us\redo01.log' SIZE 10M,
10 GROUP 2 'D:\opt\oracle9i\product\9.2.0\oradata\ora92us\redo02.log' SIZE 10M,
11 GROUP 3 'D:\opt\oracle9i\product\9.2.0\oradata\ora92us\redo03.log' SIZE 10M
12 -- STANDBY LOGFILE
13 DATAFILE
14 'D:\opt\oracle9i\product\9.2.0\oradata\ora92us\system01.dbf',
15 'D:\opt\oracle9i\product\9.2.0\oradata\ora92us\undotbs01.dbf',
16 'D:\opt\oracle9i\product\9.2.0\oradata\ora92us\users01.dbf'
17 CHARACTER SET US7ASCII
18 ;
CREATE CONTROLFILE REUSE DATABASE "ORA92US" NORESETLOGS ARCHIVELOG
*
ERROR at line 1:
ORA-01503: CREATE CONTROLFILE failed
ORA-01189: file is from a different RESETLOGS than previous files
ORA-01517: log member:
'D:\opt\oracle9i\product\9.2.0\oradata\ora92us\redo01.log'
//此处想用noresetlogs方式创建controlfile没有成功,因为online redo log已经被resetlogs过了,不再是完整的redo log;如果所有redo log都完好,可以用noresetlogs方式创建
SQL> CREATE CONTROLFILE REUSE DATABASE "ORA92US" RESETLOGS ARCHIVELOG
2 -- SET STANDBY TO MAXIMIZE PERFORMANCE
3 MAXLOGFILES 5
4 MAXLOGMEMBERS 3
5 MAXDATAFILES 100
6 MAXINSTANCES 1
7 MAXLOGHISTORY 1361
8 LOGFILE
9 GROUP 1 'D:\opt\oracle9i\product\9.2.0\oradata\ora92us\redo01.log' SIZE 10M,
10 GROUP 2 'D:\opt\oracle9i\product\9.2.0\oradata\ora92us\redo02.log' SIZE 10M,
11 GROUP 3 'D:\opt\oracle9i\product\9.2.0\oradata\ora92us\redo03.log' SIZE 10M
12 -- STANDBY LOGFILE
13 DATAFILE
14 'D:\opt\oracle9i\product\9.2.0\oradata\ora92us\system01.dbf',
15 'D:\opt\oracle9i\product\9.2.0\oradata\ora92us\undotbs01.dbf',
16 'D:\opt\oracle9i\product\9.2.0\oradata\ora92us\users01.dbf'
17 CHARACTER SET US7ASCII
18 ;
Control file created.
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 1647746 generated at 04/11/2008 12:42:57 needed for thread 1
ORA-00289: suggestion : D:\OPT\ORACLE9I\ARCHIVELOG\ORA92US\ARC00003.001
ORA-00280: change 1647746 for thread 1 is in sequence #3
Specify log: {=suggested | filename | AUTO | CANCEL}
cancel
Media recovery cancelled.
//此处应该用明确的命令“recover database using backup controlfile until cancel”
SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01113: file 1 needs media recovery
ORA-01110: data file 1:
'D:\OPT\ORACLE9I\PRODUCT\9.2.0\ORADATA\ORA92US\SYSTEM01.DBF'
SQL> recover database using backup contorlfile until cancel;
ORA-00905: missing keyword
SQL> recover database using backup controlfile until cancel;
ORA-00279: change 1647746 generated at 04/11/2008 12:42:57 needed for thread 1
ORA-00289: suggestion : D:\OPT\ORACLE9I\ARCHIVELOG\ORA92US\ARC00003.001
ORA-00280: change 1647746 for thread 1 is in sequence #3
Specify log: {=suggested | filename | AUTO | CANCEL}
cancel
Media recovery cancelled.
SQL> alter database open resetlogs;
Database altered.
SQL> select name from v$datafile;
NAME
--------------------------------------------------------------------------------
D:\OPT\ORACLE9I\PRODUCT\9.2.0\ORADATA\ORA92US\SYSTEM01.DBF
D:\OPT\ORACLE9I\PRODUCT\9.2.0\ORADATA\ORA92US\UNDOTBS01.DBF
D:\OPT\ORACLE9I\PRODUCT\9.2.0\ORADATA\ORA92US\USERS01.DBF
SQL>
SQL> select tablespace_name from dba_tablespaces;
TABLESPACE_NAME
------------------------------
SYSTEM
UNDOTBS1
TEMP
USERS
SQL>
//恢复成功
小结:如果数据库经过恢复,特别是resetlogs之后的不完全恢复,一定重新做一次全备!好习惯可以避免很多麻烦!
PS:过程中遇到一个小问题——在用 dbms_backup_restore 恢复数据文件的时候由于第一次有错误,重新编辑在运行的时候出现ORA-19568:a device is already allocated to this session错误,oracle给出的action是Deallocate the current device。一时也没想到怎么deallocate,于是退出当前session,没想到重新进入之后就ok了。原来退出session自动deallocate device了~
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/9765498/viewspace-234526/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/9765498/viewspace-234526/