一次曲折的rman控制文件恢复

一次曲折的rman控制文件恢复
用rman备份一个最新的控制文件
rman target / catalog rman/rman@catdb
RMAN>backup current controlfile format =
'/oracle/backup/backup_%U';
破坏控制文件
select name from v$controlfile;
/dev/rctrlfile1
/dev/rctrlfile2
/dev/rctrlfile3
用dd把控制文件都搞坏，如下：
# dd if=/dev/zero of=/dev/rctrlfile1 bs=32k
---
---
没有归档当前的redo log。

SQL> shutdown abort;
ORACLE instance shut down.
尝试正常启动数据库
SQL> startup
ORACLE instance started.
Total System Global Area 167772160 bytes
Fixed Size 2094904 bytes
Variable Size 100665544 bytes
Database Buffers 58720256 bytes
Redo Buffers 6291456 bytes
ORA-00205: error in identifying control file, check alert log for more info
报错，把数据库启动到nomount状态
SQL> shutdown
ORA-01507: database not mounted
ORACLE instance shut down.
SQL> startup nomount
ORACLE instance started.
Total System Global Area 167772160 bytes
Fixed Size 2094904 bytes
Variable Size 100665544 bytes
Database Buffers 58720256 bytes
Redo Buffers 6291456 bytes
SQL>
连接rman，准备恢复数据库文件。

ibmchen$[/oracle]rman target / catalog rman/rman@catdb
Recovery Manager: Release 10.2.0.5.0 - Production on Wed Mar 14 16:18:09 2012
Copyright (c) 1982, 2007, Oracle. All rights reserved.
connected to target database: orac (not mounted)
connected to recovery catalog database
检查是否打开CONTROLFILE AUTOBACKUP ON
RMAN> show all;
RMAN configuration parameters are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP OFF; # default CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT
'/oracle/backup/%U';
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
结果：没有打开AUTOBACKUP
列出备份的控制文件
RMAN> list backup of controlfile;
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------
---------------
214 Full 5.30M DISK 00:00:05
14-MAR-12
BP Key: 216 Status: AVAILABLE Compressed: NO Tag: TAG20120314T051102
Piece Name:
/oracle/app/oracle/product/10.2.0/db_1/dbs/05n5que6_1_1
Control File Included: Ckp SCN: 5593672 Ckp time:
14-MAR-12
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------
---------------
246 Full 5.30M DISK 00:00:04
14-MAR-12
BP Key: 250 Status: AVAILABLE Compressed: NO Tag: TAG20120314T090504
Piece Name:
/oracle/app/oracle/product/10.2.0/db_1/dbs/07n5rc5h_1_1
Control File Included: Ckp SCN: 5637823 Ckp time:
14-MAR-12
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------
---------------
283 Full 5.30M DISK 00:00:07
14-MAR-12
BP Key: 287 Status: AVAILABLE Compressed: NO Tag: TAG20120314T102947
Piece Name: /oracle/backup/backup_09n5rh38_1_1
Control File Included: Ckp SCN: 5642424 Ckp time:
14-MAR-12
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------
---------------
315 Full 5.27M DISK 00:00:03
14-MAR-12
BP Key: 318 Status: AVAILABLE Compressed: NO Tag: TAG20120314T132455
Piece Name: /oracle/backup/control.bkp
Control File Included: Ckp SCN: 5644103 Ckp time:
14-MAR-12
挑一个最新的备份（最新的未必好使）
恢复控制文件：
RMAN> restore controlfile from '/oracle/backup/control.bkp';
Starting restore at 14-MAR-12
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=155 devtype=DISK
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:08 output filename=/dev/rctrlfile1
output filename=/dev/rctrlfile2
output filename=/dev/rctrlfile3
Finished restore at 14-MAR-12
RMAN>
控制文件恢复完毕，将数据库启动到mount状态
SQL> alter database mount;
Database altered.
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open
提示要用resetlogs方式打开
SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: '/dev/rsystem'
数据不一致，提示需要recover database
SQL> recover database until cancel;
ORA-00283: recovery session canceled due to errors
ORA-01610: recovery using the BACKUP CONTROLFILE option must be done
提示需要用BACKUP CONTROLFILE方式recover database
SQL> recover database using backup controlfile until cancel; ORA-00279: change 5646533 generated at 03/14/2012 14:02:44 needed for thread 1
ORA-00289: suggestion : /oracle/arch/arch1_161_772505712.dbf ORA-00280: change 5646533 for thread 1 is in sequence #161
Specify log: {<RET>=suggested | filename | AUTO | CANCEL} auto
ORA-00308: cannot open archived log
'/oracle/arch/arch1_161_772505712.dbf'
ORA-27037: unable to obtain file status
IBM AIX RISC System/6000 Error: 2: No such file or directory Additional information: 3
ORA-00308: cannot open archived log
'/oracle/arch/arch1_161_772505712.dbf'
ORA-27037: unable to obtain file status
IBM AIX RISC System/6000 Error: 2: No such file or directory Additional information: 3
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01194: file 1 needs more recovery to be consistent
我晕，/oracle/arch/arch1_161_772505712.dbf没有归档。

反正是测试库，直接restore（当前数据库状态为mount）
RMAN> restore database;
Starting restore at 14-MAR-12
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=151 devtype=DISK
channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to /dev/rsystem
restoring datafile 00002 to /dev/rundo1
restoring datafile 00003 to /dev/rsysaux
restoring datafile 00004 to /dev/ruser
restoring datafile 00005 to /dev/ruser1
channel ORA_DISK_1: reading from backup piece
/oracle/backup/backup_08n5rh12_1_1
channel ORA_DISK_1: restored backup piece 1
piece handle=/oracle/backup/backup_08n5rh12_1_1
tag=TAG20120314T102947
channel ORA_DISK_1: restore complete, elapsed time: 00:03:48 Finished restore at 14-MAR-12
restore 完成，open resetlogs
RMAN> alter database open resetlogs;
RMAN-00571:
=========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS
===============
RMAN-00571:
=========================================================== RMAN-03002: failure of alter db command at 03/14/2012 18:21:50 ORA-01152: file 1 was not restored from a sufficiently old backup
ORA-01110: data file 1: '/dev/rsystem'
同样报错，数据不一致，需要recover
SQL> recover database until cancel;
ORA-00283: recovery session canceled due to errors
ORA-01610: recovery using the BACKUP CONTROLFILE option must be done
同样提示需要用BACKUP CONTROLFILE来恢复数据库
SQL> recover database using backup controlfile until cancel; ORA-00279: change 5644103 generated at 03/14/2012 10:40:12 needed for thread 1
ORA-00289: suggestion : /oracle/arch/arch1_160_772505712.dbf ORA-00280: change 5644103 for thread 1 is in sequence #160
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
ORA-00279: change 5646533 generated at 03/14/2012 14:02:44 needed for thread 1
ORA-00289: suggestion : /oracle/arch/arch1_161_772505712.dbf ORA-00280: change 5646533 for thread 1 is in sequence #161 ORA-00278: log file '/oracle/arch/arch1_160_772505712.dbf' no longer needed for
this recovery
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
ORA-00308: cannot open archived log
'/oracle/arch/arch1_161_772505712.dbf'
ORA-27037: unable to obtain file status
IBM AIX RISC System/6000 Error: 2: No such file or directory Additional information: 3
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: '/dev/rsystem'
SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: '/dev/rsystem'
结果一样，由于控制文件很新，需要的日志没有归档，数据库不能正常打开。

好了既然不能用新的控制文件，检查备份吧，就用最新一次全备的控制文件
用list backup命令检查
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------
---------------
282 Full 412.16M DISK 00:00:50
14-MAR-12
BP Key: 286 Status: AVAILABLE Compressed: NO Tag: TAG20120314T102947
Piece Name: /oracle/backup/backup_08n5rh12_1_1
List of Datafiles in backup set 282
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
1 Full 5642384 14-MAR-1
2 /dev/rsystem
2 Full 5642384 14-MAR-12 /dev/rundo1
3 Full 564238
4 14-MAR-12 /dev/rsysaux
4 Full 5642384 14-MAR-12 /dev/ruser
5 Full 5642384 14-MAR-12 /dev/ruser1
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------
---------------
283 Full 5.30M DISK 00:00:07
14-MAR-12
BP Key: 287 Status: AVAILABLE Compressed: NO Tag: TAG20120314T102947
Piece Name: /oracle/backup/backup_09n5rh38_1_1
Control File Included: Ckp SCN: 5642424 Ckp time:
14-MAR-12
SPFILE Included: Modification time: 03-MAR-12
控制文件/oracle/backup/backup_09n5rh38_1_1可用
OK，重新将数据库打开到nomount状态，恢复控制文件
RMAN> restore controlfile from
'/oracle/backup/backup_09n5rh38_1_1';
Starting restore at 14-MAR-12
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=156 devtype=DISK
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:07 output filename=/dev/rctrlfile1
output filename=/dev/rctrlfile2
output filename=/dev/rctrlfile3
Finished restore at 14-MAR-12
将数据库打开到mount状态
RMAN> alter database mount;
database mounted
released channel: ORA_DISK_1
成功mount。

然后在用备份的控制文件recover database。

SQL> recover database using backup controlfile until cancel; ORA-00279: change 5642384 generated at 03/14/2012 10:29:55 needed for thread 1
ORA-00289: suggestion : /oracle/arch/arch1_159_772505712.dbf ORA-00280: change 5642384 for thread 1 is in sequence #159
Specify log: {<RET>=suggested | filename | AUTO | CANCEL} auto
ORA-00279: change 5642883 generated at 03/14/2012 10:40:12 needed for thread 1
ORA-00289: suggestion : /oracle/arch/arch1_160_772505712.dbf ORA-00280: change 5642883 for thread 1 is in sequence #160 ORA-00278: log file '/oracle/arch/arch1_159_772505712.dbf' no longer needed for
this recovery
ORA-00279: change 5646533 generated at 03/14/2012 14:02:44 needed for thread 1
ORA-00289: suggestion : /oracle/arch/arch1_161_772505712.dbf
ORA-00280: change 5646533 for thread 1 is in sequence #161 ORA-00278: log file '/oracle/arch/arch1_160_772505712.dbf' no longer needed for
this recovery
ORA-00308: cannot open archived log
'/oracle/arch/arch1_161_772505712.dbf'
ORA-27037: unable to obtain file status
IBM AIX RISC System/6000 Error: 2: No such file or directory Additional information: 3
ok，又需要/oracle/arch/arch1_161_772505712.dbf归档文件，但未归档，没有呀。

好吧，open resetlogs
SQL> alter database open resetlogs;
Database altered.
SQL>
终于，数据库成功open。

总结，控制文件一定要有3个或3个以上，如果某个发生损坏，正常shutdown，看错误信息，去掉坏的，最好用好的控制文件dd或copy 覆盖坏的，然后正常打开。

如果用rman恢复，最新的那个未必好使，用最近全备的那个即可。