Oracle 基于备份控制文件的恢复(unsing backup controlfile)

sys@SYBO2SZ> archive log list;   -->看看归档情况，log sequence从1开始    
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /u02/database/SYBO2SZ/archive/
Oldest online log sequence     1
Next log sequence to archive   1
Current log sequence           1
sys@SYBO2SZ> alter system switch logfile;

-->首先热备数据库
sys@SYBO2SZ> @db_hot_bak

-->新的incarnation 793471702已经产生，可以看到同时也产生了一个尾数为118的上一个incarnation的归档日志
sys@SYBO2SZ> ho ls -hltr /u02/database/SYBO2SZ/archive
total 54M
-rw-r----- 1 oracle oinstall 1.0K 2012-09-08 16:48 arch_792094299_1_118.arc
-rw-r----- 1 oracle oinstall 9.0K 2012-09-08 16:48 arch_792094299_1_117.arc
-rw-r----- 1 oracle oinstall  43K 2012-09-08 16:51 arch_793471702_1_1.arc

-->删除表空间以更新控制文件
sys@SYBO2SZ> drop tablespace tbs including contents and datafiles;

Tablespace dropped.

sys@SYBO2SZ> alter system switch logfile;

System altered.

-->切换日志后，新增了尾数为2的归档日志
sys@SYBO2SZ> ho ls -hltr /u02/database/SYBO2SZ/archive
total 54M
-rw-r----- 1 oracle oinstall 1.0K 2012-09-08 16:48 arch_792094299_1_118.arc
-rw-r----- 1 oracle oinstall 9.0K 2012-09-08 16:48 arch_792094299_1_117.arc
-rw-r----- 1 oracle oinstall  43K 2012-09-08 16:51 arch_793471702_1_1.arc
-rw-r----- 1 oracle oinstall  50K 2012-09-08 16:58 arch_793471702_1_2.arc

sys@SYBO2SZ> show parameter background_dump_dest

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
background_dump_dest                 string      /u02/database/SYBO2SZ/bdump

-->从alert log file中查询表空间被删除的准确时间
sys@SYBO2SZ> ho cat -n /u02/database/SYBO2SZ/bdump/alert_SYBO2SZ.log | grep tbs.dbf
  6959  '/u02/database/SYBO2SZ/oradata/imp_tbs.dbf'
  6962  '/u02/database/SYBO2SZ/oradata/imp_tbs.dbf'...
 11014  alter database create datafile 9 as '/u02/database/SYBO2SZ/oradata/tbs.dbf'
 11016  Completed: alter database create datafile 9 as '/u02/database/SYBO2SZ/oradata/tbs.dbf'
 11273  Deleted file /u02/database/SYBO2SZ/oradata/tbs.dbf

-->下面可以看到表空间及数据文件被删除的时间
-->同时也看到了控制文件进行了自动备份，因为RMAN配置中控制文件自动备份被置为ON
-->此处使用之前备份的控制文件来恢复测试，因此此处不考虑使用自动备份的控制文件
sys@SYBO2SZ> ho more +11270 /u02/database/SYBO2SZ/bdump/alert_SYBO2SZ.log
Sat Sep  8 16:57:56 2012
drop tablespace tbs including contents and datafiles
Sat Sep  8 16:57:58 2012
Deleted file /u02/database/SYBO2SZ/oradata/tbs.dbf
Starting control autobackup
Control autobackup written to DISK device
        handle '/u02/database/SYBO2SZ/backup/rman/20120907/SYBO2SZ_lev1_201209071410_c-209726751-20120908-05'
Completed: drop tablespace tbs including contents and datafiles

sys@SYBO2SZ> shutdown immediate;

-->还原控制文件及所有的数据文件
sys@SYBO2SZ> ho cp /u02/database/SYBO2SZ/backup/hotbak/contlbak.ctl /u02/database/SYBO2SZ/controlf/cntl1SYBO2SZ.ctl

sys@SYBO2SZ> ho cp /u02/database/SYBO2SZ/backup/hotbak/contlbak.ctl /u02/database/SYBO2SZ/controlf/cntl2SYBO2SZ.ctl

sys@SYBO2SZ> ho cp /u02/database/SYBO2SZ/backup/hotbak/contlbak.ctl /u02/database/SYBO2SZ/controlf/cntl3SYBO2SZ.ctl

sys@SYBO2SZ> ho cp /u02/database/SYBO2SZ/backup/hotbak/*.dbf /u02/database/SYBO2SZ/oradata/.

sys@SYBO2SZ> ho cp /u02/database/SYBO2SZ/backup/hotbak/undotbs* /u02/database/SYBO2SZ/undo/.

sys@SYBO2SZ> startup mount;
ORACLE instance started.

-->使用using backup controlfile选项以及设定时间点来恢复数据库
sys@SYBO2SZ> recover database until time '2012-09-08:16:57:56' using backup controlfile;
ORA-00279: change 1079138 generated at 09/08/2012 16:55:09 needed for thread 1
ORA-00289: suggestion : /u02/database/SYBO2SZ/archive/arch_793471702_1_2.arc
ORA-00280: change 1079138 for thread 1 is in sequence #2


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
auto
Log applied.
Media recovery complete.

sys@SYBO2SZ> alter database open resetlogs;

Database altered.

-->验证恢复的对象中的记录数
sys@SYBO2SZ> select count(*) from tb_emp;

  COUNT(*)
----------
        11