rman恢复－不完全恢复

rman不完全恢复
只适用于archivelog模式，只能在mount状态下完成。
基于时间恢复
基于时间恢复是指当出现用户错误（例如误删除表、误截断表）时，恢复到指定时间点的恢复。
示例：
模拟误截断表t_user。
在终端设置环境变量nls_date_format指定日期时间格式。
执行rman,启动数据库到mount状态。
使用set until time命令指定要恢复到的时间点。

转储、恢复数据库，并使用resetlogs选项打开数据库。

--模拟误截断表t_user。SQL> host date2011年 08月 29日 星期一 21:01:29 CSTSQL> truncate table t_user;Table truncated.--在终端设置环境变量nls_date_format指定日期时间格式。[oracle@localhost ~]$ export NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS'--恢复[oracle@localhost ~]$ rman target sys/oracle@oralife nocatalogRMAN> run {2> startup force mount;3> set until time='2011-08-29 21:01:00';4> restore database;5> recover database;6> sql 'alter database open resetlogs';7> }--已恢复SQL> conn sys/oracle@oralife as sysdbaConnected.SQL> select count(*) from t_user;  COUNT(*)----------         2

在实际环境下，应该使用LogMiner确定误操作时间点。
在执行了不完全恢复之后，推荐删除早期所有备份，重新备份数据库。如：
run {
 delete noprompt backup;
 delete noprompt copy;
 backup database format='/oracle/10g/oracle/rman/%d_%s.dbf';
 sql 'alter system archive log current';
 }

基于SCN恢复
基于SCN恢复是指当出现用户错误（例如误删除表、误截断表）时，恢复到指定SCN点的恢复。
模拟误删除表t_user。
执行rman,启动数据库到mount状态。
使用set until scn命令指定要恢复到的scn点。
转储、恢复数据库，并使用resetlogs选项打开数据库。

--模拟误删除表t_user。SQL> select count(*) from t_user;  COUNT(*)----------         2SQL> insert into t_user select 'spring mvc_' from dual;1 row created.SQL> commit;Commit complete.SQL> alter system switch logfile;System altered.SQL> alter system checkpoint;System altered.SQL> select current_scn from v$database;CURRENT_SCN-----------    1214281SQL> drop table t_user;Table dropped.--恢复[oracle@localhost ~]$ rman target sys/oracle@oralife nocatalogRMAN> run {2> startup force mount;3> set until scn=1214281;4> restore database;5> recover database;6> sql 'alter database open resetlogs';7> }--已恢复SQL> select count(*) from t_user;  COUNT(*)----------         3

在实际环境下，应该使用LogMiner确定误操作SCN点。
在执行了不完全恢复之后，推荐删除早期所有备份，重新备份数据库。

基于日志序列号恢复
基于日志序列号恢复是指恢复数据库到指定日志序列号的状态。

--查看归档日志信息SQL> select * from t_user;TEXT--------------------java_spring_spring mvc_SQL> insert into t_user select 'oracle_' from dual;1 row created.SQL> commit;Commit complete.SQL> alter system switch logfile;System altered.SQL> alter system checkpoint;System altered.SQL> select sequence#,name,first_change# from v$archived_log where status='A' order by sequence#; SEQUENCE# NAME                                                                                                 FIRST_CHANGE#---------- ---------------------------------------------------------------------------------------------------- -------------         1 /oracle/10g/oracle/log/archive_log/archive_1_1_760487088.arclog                                            1214497         1 /oracle/10g/oracle/log/archive_log2/archive_1_1_760487088.arclog                                           1214497         1 /oracle/10g/oracle/product/10.2.0/db_1/flash_recovery_area/ORALIFE/archivelog/2011_08_29/o1_mf_1_1_7       1214498           5q9bh9d_.arc         1 /oracle/10g/oracle/log/archive_log/archive_1_1_760487985.arclog                                            1214498         1 /oracle/10g/oracle/log/archive_log2/archive_1_1_760487985.arclog                                           1214498         1 /oracle/10g/oracle/product/10.2.0/db_1/flash_recovery_area/ORALIFE/archivelog/2011_08_29/o1_mf_1_1_7       1214497           5q95ksf_.arc6 rows selected.SQL> insert into t_user select 'oracle_seq3' from dual;1 row created.SQL> commit;Commit complete.SQL> alter system switch logfile; --生成日志序列号为2的归档日志System altered.SQL> alter system checkpoint;System altered.SQL> select sequence#,name,first_change# from v$archived_log where status='A' order by sequence#; SEQUENCE# NAME                                                                                                 FIRST_CHANGE#---------- ---------------------------------------------------------------------------------------------------- -------------         1 /oracle/10g/oracle/log/archive_log/archive_1_1_760487088.arclog                                            1214497         1 /oracle/10g/oracle/log/archive_log2/archive_1_1_760487088.arclog                                           1214497         1 /oracle/10g/oracle/product/10.2.0/db_1/flash_recovery_area/ORALIFE/archivelog/2011_08_29/o1_mf_1_1_7       1214497           5q95ksf_.arc         1 /oracle/10g/oracle/log/archive_log/archive_1_1_760487985.arclog                                            1214498         1 /oracle/10g/oracle/log/archive_log2/archive_1_1_760487985.arclog                                           1214498         1 /oracle/10g/oracle/product/10.2.0/db_1/flash_recovery_area/ORALIFE/archivelog/2011_08_29/o1_mf_1_1_7       1214498           5q9bh9d_.arc         2 /oracle/10g/oracle/log/archive_log/archive_1_2_760487985.arclog                                            1216167 SEQUENCE# NAME                                                                                                 FIRST_CHANGE#---------- ---------------------------------------------------------------------------------------------------- -------------         2 /oracle/10g/oracle/log/archive_log2/archive_1_2_760487985.arclog                                           1216167         2 /oracle/10g/oracle/product/10.2.0/db_1/flash_recovery_area/ORALIFE/archivelog/2011_08_29/o1_mf_1_2_7       1216167           5q9cvt1_.arc9 rows selected.SQL> insert into t_user select 'oracle_seq3_act' from dual;1 row created.SQL> commit;Commit complete.SQL> alter system switch logfile; --生成日志序列号为3的归档日志System altered.SQL> alter system checkpoint;System altered.SQL> select sequence#,name,first_change# from v$archived_log where status='A' order by sequence#; SEQUENCE# NAME                                                                                                 FIRST_CHANGE#---------- ---------------------------------------------------------------------------------------------------- -------------         1 /oracle/10g/oracle/log/archive_log/archive_1_1_760487088.arclog                                            1214497         1 /oracle/10g/oracle/log/archive_log2/archive_1_1_760487088.arclog                                           1214497         1 /oracle/10g/oracle/product/10.2.0/db_1/flash_recovery_area/ORALIFE/archivelog/2011_08_29/o1_mf_1_1_7       1214497           5q95ksf_.arc         1 /oracle/10g/oracle/log/archive_log/archive_1_1_760487985.arclog                                            1214498         1 /oracle/10g/oracle/log/archive_log2/archive_1_1_760487985.arclog                                           1214498         1 /oracle/10g/oracle/product/10.2.0/db_1/flash_recovery_area/ORALIFE/archivelog/2011_08_29/o1_mf_1_1_7       1214498           5q9bh9d_.arc         2 /oracle/10g/oracle/log/archive_log/archive_1_2_760487985.arclog                                            1216167 SEQUENCE# NAME                                                                                                 FIRST_CHANGE#---------- ---------------------------------------------------------------------------------------------------- -------------         2 /oracle/10g/oracle/log/archive_log2/archive_1_2_760487985.arclog                                           1216167         2 /oracle/10g/oracle/product/10.2.0/db_1/flash_recovery_area/ORALIFE/archivelog/2011_08_29/o1_mf_1_2_7       1216167           5q9cvt1_.arc         3 /oracle/10g/oracle/log/archive_log/archive_1_3_760487985.arclog                                            1216186         3 /oracle/10g/oracle/log/archive_log2/archive_1_3_760487985.arclog                                           1216186         3 /oracle/10g/oracle/product/10.2.0/db_1/flash_recovery_area/ORALIFE/archivelog/2011_08_29/o1_mf_1_3_7       1216186           5q9f4d6_.arc12 rows selected.--恢复到日志序列号为3时的状态[oracle@localhost ~]$ rman target sys/oracle@oralife nocatalogRMAN> run { startup force mount; set until sequence=3; restore database; recover database; sql 'alter database open resetlogs';}--查看，可见不包括日志序列号为3的归档日志信息（oracle_seq3_act)，即恢复到日志序列号为2的归档日志SQL> conn sys/oracle@oralife as sysdbaConnected.SQL> select * from t_user;TEXT--------------------java_spring_oracle_oracle_seq3spring mvc_

可见不包括日志序列号为3的归档日志信息（oracle_seq3_act)，即恢复到日志序列号为2的归档日志。

在执行了不完全恢复之后，推荐删除早期所有备份，重新备份数据库。

基于备份控制文件恢复
基于备份控制文件恢复是指使用备份控制文件恢复数据库的过程。当误删除了表空间或数据库所有控制文件全部损坏时，可以使用这种恢复方法。
模拟误删除了test表空间，因为当前控制文件没有包含该表空间的信息，所以必须使用备份控制文件恢复被误删除的表空间。如果没有使用恢复目录，必须激活控制文件自动备份，否则将不转储控制文件备份。
模拟误删除test表空间。
查看alert日志文件，确定操作时间。
使用rman启动数据库到mount状态。
设置数据库id。
转储控制文件，恢复数据库。

--模拟误删除test表空间。SQL> create tablespace rtest DATAFILE '/oracle/10g/oracle/product/10.2.0/oradata/oralife/rtest.dbf' size 10M;Tablespace created.SQL> create table r_test (text varchar(20)) tablespace rtest;Table created.SQL> insert into r_test select '00001' from dual;1 row created.SQL> commit;Commit complete.SQL> alter system switch logfile;System altered.SQL> alter system checkpoint;System altered.SQL> insert into r_test select '00002' from dual;1 row created.SQL> commit;Commit complete.SQL> alter system switch logfile;System altered.SQL> alter system checkpoint;System altered.SQL> select count(*) from r_test;  COUNT(*)----------         2SQL> drop tablespace rtest including contents;Tablespace dropped.SQL> select dbid from v$database;      DBID----------3898974595SQL> select count(*) from r_test;select count(*) from r_test                     *ERROR at line 1:ORA-00942: table or view does not existSQL> host tail -n 15 /oracle/10g/oracle/product/10.2.0/db_1/admin/oralife/bdump/alert_oralife.log  Current log# 4 seq# 7 mem# 0: /oracle/10g/oracle/product/10.2.0/oradata/oralife/redo04.log  Current log# 4 seq# 7 mem# 1: /oracle/10g/oracle/product/10.2.0/oradata/oralife/redo04_add.logTue Aug 30 22:17:30 2011drop tablespace rtest including contentsTue Aug 30 22:17:30 2011Starting control autobackupTue Aug 30 22:17:31 2011Errors in file /oracle/10g/oracle/product/10.2.0/db_1/admin/oralife/udump/oralife_ora_4751.trc:Tue Aug 30 22:17:31 2011Errors in file /oracle/10g/oracle/product/10.2.0/db_1/admin/oralife/udump/oralife_ora_4751.trc:Tue Aug 30 22:17:31 2011Errors in file /oracle/10g/oracle/product/10.2.0/db_1/admin/oralife/udump/oralife_ora_4751.trc:Control autobackup written to DISK device        handle '/oracle/10g/oracle/product/10.2.0/db_1/flash_recovery_area/ORALIFE/autobackup/2011_08_30/o1_mf_s_760573050_75sw7twr_.bkp'Completed: drop tablespace rtest including contents--恢复[oracle@localhost ~]$ export NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS'  [oracle@localhost ~]$ rman target sys/oracle@oralife nocatalogRMAN> startup force nomount;RMAN> set dbid=3898974595;RMAN> restore controlfile from autobackup;RMAN> alter database mount;RMAN> run { set until time='2011-08-30 22:17:30'; restore database; recover database; sql 'alter database open resetlogs';}--查看SQL> quit[oracle@localhost ~]$ sqlplus sys/oracle@oralife as sysdbaSQL> select count(*) from r_test;select count(*) from r_test                     *ERROR at line 1:ORA-00376: file 6 cannot be read at this timeORA-01111: name for data file 6 is unknown - rename to correct fileORA-01110: data file 6:'/oracle/10g/oracle/product/10.2.0/db_1/dbs/MISSING00006'SQL> select name from v$datafile;NAME--------------------------------------------------------------------------------/oracle/10g/oracle/product/10.2.0/oradata/oralife/system01.dbf/oracle/10g/oracle/product/10.2.0/oradatabak/undotbs01.dbf/oracle/10g/oracle/product/10.2.0/oradatabak/sysaux01.dbf/oracle/10g/oracle/product/10.2.0/oradata/oralife/user01.dbf/oracle/10g/oracle/product/10.2.0/oradata/oralife/example01.dbf/oracle/10g/oracle/product/10.2.0/db_1/dbs/MISSING000066 rows selected.

恢复失败，失败原因:restore controlfile from autobackup;用于转储自动备份的最新的控制文件，即删除表空间rtest之后自动备份的控制文件。

解决办法：使用restore controlfile from autobackup until time "to_date('2011-08-31 21:26:16','yyyy-mm-dd hh24:mi:ss')"; 查看alert日志文件确定删除表空间rtest之前自动备份控制文件时间，也就是删除表空间rtest之前的时间点。

SQL> create tablespace rtest DATAFILE '/oracle/10g/oracle/product/10.2.0/oradata/oralife/rtest.dbf' size 10M;  Tablespace created.SQL> create table r_test (text varchar(20)) tablespace rtest;  Table created.SQL> insert into r_test select '00001' from dual;  1 row created.SQL> commit;Commit complete.SQL> alter system switch logfile;System altered.SQL> alter system checkpoint;System altered.--删除早期所有备份，重新备份数据库RMAN> run { delete noprompt backup; delete noprompt copy; backup database format='/oracle/10g/oracle/rman/%d_%s.dbf'; sql 'alter system archive log current'; }--删除表空间与对应的数据文件SQL> insert into r_test select '00002' from dual;  1 row created.SQL> commit;Commit complete.SQL> alter system switch logfile;System altered.SQL> alter system checkpoint;System altered.SQL> select count(*) from r_test;  COUNT(*)----------         2SQL> drop tablespace rtest including contents and datafiles;Tablespace dropped.SQL> select dbid from v$database;      DBID----------3898974595SQL> select count(*) from r_test;select count(*) from r_test                     *ERROR at line 1:ORA-00942: table or view does not exist--查看alert日志，以确定操作时间，Wed Aug 31 21:26:17 2011SQL> host tail -n 20 /oracle/10g/oracle/product/10.2.0/db_1/admin/oralife/bdump/alert_oralife.log    Current log# 4 seq# 4 mem# 0: /oracle/10g/oracle/product/10.2.0/oradata/oralife/redo04.log  Current log# 4 seq# 4 mem# 1: /oracle/10g/oracle/product/10.2.0/oradata/oralife/redo04_add.logWed Aug 31 21:25:26 2011Thread 1 advanced to log sequence 5  Current log# 2 seq# 5 mem# 0: /oracle/10g/oracle/product/10.2.0/oradata/oralife/redo02.log  Current log# 2 seq# 5 mem# 1: /oracle/10g/oracle/product/10.2.0/oradata/oralife/redo02_add.logWed Aug 31 21:26:17 2011drop tablespace rtest including contents and datafilesWed Aug 31 21:26:18 2011Deleted file /oracle/10g/oracle/product/10.2.0/oradata/oralife/rtest.dbfStarting control autobackupWed Aug 31 21:26:18 2011Errors in file /oracle/10g/oracle/product/10.2.0/db_1/admin/oralife/udump/oralife_ora_3646.trc:Wed Aug 31 21:26:18 2011Errors in file /oracle/10g/oracle/product/10.2.0/db_1/admin/oralife/udump/oralife_ora_3646.trc:Wed Aug 31 21:26:18 2011Errors in file /oracle/10g/oracle/product/10.2.0/db_1/admin/oralife/udump/oralife_ora_3646.trc:Control autobackup written to DISK device        handle '/oracle/10g/oracle/product/10.2.0/db_1/flash_recovery_area/ORALIFE/autobackup/2011_08_31/o1_mf_s_760656378_75wfmtcm_.bkp'Completed: drop tablespace rtest including contents and datafiles--恢复控制文件[oracle@localhost ~]$ export NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS'    [oracle@localhost ~]$ rman target sys/oracle@oralife nocatalogRMAN> startup force nomount;RMAN> set dbid=3898974595;  --与下面set until time（即删除表空间rtest的操作时间)相比少了1秒， 以转储正确的控制文件，--如果这里设置的时间 与alert日志中删除表空间的操作时间相同，会转储最新的控制文件（没有包含表空间rtest信息)导致恢复失败。RMAN> restore controlfile from autobackup until time "to_date('2011-08-31 21:26:16','yyyy-mm-dd hh24:mi:ss')"; Starting restore at 2011-08-31 21:35:29using channel ORA_DISK_1recovery area destination: /oracle/10g/oracle/product/10.2.0/db_1/flash_recovery_areadatabase name (or database unique name) used for search: ORALIFEchannel ORA_DISK_1: autobackup found in the recovery areachannel ORA_DISK_1: autobackup found: /oracle/10g/oracle/product/10.2.0/db_1/flash_recovery_area/ORALIFE/autobackup/2011_08_31/o1_mf_s_760656297_75wfk9gc_.bkpchannel ORA_DISK_1: control file restore from autobackup completeoutput filename=/oracle/10g/oracle/product/10.2.0/oradata/oralife/control01.ctloutput filename=/oracle/10g/oracle/product/10.2.0/oradata/oralife/control02.ctloutput filename=/oracle/10g/oracle/product/10.2.0/oradata/oralife/control03.ctlFinished restore at 2011-08-31 21:35:33RMAN> alter database mount;RMAN> run { set until time='2011-08-31 21:26:17'; restore database; recover database; sql 'alter database open resetlogs';6>  }executing command: SET until clauseStarting restore at 2011-08-31 21:36:43Starting implicit crosscheck backup at 2011-08-31 21:36:43allocated channel: ORA_DISK_1channel ORA_DISK_1: sid=156 devtype=DISKCrosschecked 1 objectsFinished implicit crosscheck backup at 2011-08-31 21:36:44Starting implicit crosscheck copy at 2011-08-31 21:36:44using channel ORA_DISK_1Finished implicit crosscheck copy at 2011-08-31 21:36:44searching for all files in the recovery areacataloging files...cataloging doneList of Cataloged Files=======================File Name: /oracle/10g/oracle/product/10.2.0/db_1/flash_recovery_area/ORALIFE/archivelog/2011_08_31/o1_mf_1_4_75wfl6w2_.arcFile Name: /oracle/10g/oracle/product/10.2.0/db_1/flash_recovery_area/ORALIFE/archivelog/2011_08_31/o1_mf_1_3_75wfkc9w_.arcFile Name: /oracle/10g/oracle/product/10.2.0/db_1/flash_recovery_area/ORALIFE/autobackup/2011_08_31/o1_mf_s_760656297_75wfk9gc_.bkpFile Name: /oracle/10g/oracle/product/10.2.0/db_1/flash_recovery_area/ORALIFE/autobackup/2011_08_31/o1_mf_s_760656378_75wfmtcm_.bkpusing channel ORA_DISK_1channel ORA_DISK_1: starting datafile backupset restorechannel ORA_DISK_1: specifying datafile(s) to restore from backup setrestoring datafile 00001 to /oracle/10g/oracle/product/10.2.0/oradata/oralife/system01.dbfrestoring datafile 00002 to /oracle/10g/oracle/product/10.2.0/oradatabak/undotbs01.dbfrestoring datafile 00003 to /oracle/10g/oracle/product/10.2.0/oradatabak/sysaux01.dbfrestoring datafile 00004 to /oracle/10g/oracle/product/10.2.0/oradata/oralife/user01.dbfrestoring datafile 00005 to /oracle/10g/oracle/product/10.2.0/oradata/oralife/example01.dbfrestoring datafile 00006 to /oracle/10g/oracle/product/10.2.0/oradata/oralife/rtest.dbf --转储了表空间rtest对应的数据文件channel ORA_DISK_1: reading from backup piece /oracle/10g/oracle/rman/ORALIFE_98.dbfchannel ORA_DISK_1: restored backup piece 1piece handle=/oracle/10g/oracle/rman/ORALIFE_98.dbf tag=TAG20110831T212421channel ORA_DISK_1: restore complete, elapsed time: 00:00:35Finished restore at 2011-08-31 21:37:20Starting recover at 2011-08-31 21:37:20using channel ORA_DISK_1starting media recoveryarchive log thread 1 sequence 3 is already on disk as file /oracle/10g/oracle/product/10.2.0/db_1/flash_recovery_area/ORALIFE/archivelog/2011_08_31/o1_mf_1_3_75wfkc9w_.arcarchive log thread 1 sequence 4 is already on disk as file /oracle/10g/oracle/product/10.2.0/db_1/flash_recovery_area/ORALIFE/archivelog/2011_08_31/o1_mf_1_4_75wfl6w2_.arcarchive log thread 1 sequence 5 is already on disk as file /oracle/10g/oracle/product/10.2.0/oradata/oralife/redo02.logarchive log filename=/oracle/10g/oracle/product/10.2.0/db_1/flash_recovery_area/ORALIFE/archivelog/2011_08_31/o1_mf_1_3_75wfkc9w_.arc thread=1 sequence=3archive log filename=/oracle/10g/oracle/product/10.2.0/db_1/flash_recovery_area/ORALIFE/archivelog/2011_08_31/o1_mf_1_4_75wfl6w2_.arc thread=1 sequence=4archive log filename=/oracle/10g/oracle/product/10.2.0/oradata/oralife/redo02.log thread=1 sequence=5media recovery complete, elapsed time: 00:00:03Finished recover at 2011-08-31 21:37:25sql statement: alter database open resetlogs--查看，已恢复[oracle@localhost ~]$ sqlplus sys/oracle@oralife as sysdbaSQL> select count(*) from r_test;  COUNT(*)----------         2