实验：基于cancel的不完全恢复 实验环境查看 lsnrctl status select open_mode from v$database; --监听与数据库状态 show parameter recovery; select flashback_on from v$database; archive log list; --闪回与归档的配置 1)准备环境：rman全库备份 rman
实验：基于cancel的不完全恢复
实验环境查看
lsnrctl status
select open_mode from v$database;
--监听与数据库状态
show parameter recovery;
select flashback_on from v$database;
archive log list;
--闪回与归档的配置
1)准备环境：rman全库备份
rman> backup as compressed backupset full database;
--压缩备份
--backup full database ;备份集备份
starting backup at 20-mar-13
using target database control file instead of recovery catalog
allocated channel: ora_disk_1
channel ora_disk_1: sid=45 device type=disk
channel ora_disk_1: starting compressed full datafile backup set
channel ora_disk_1: specifying datafile(s) in backup set
input datafile file number=00001 name=/u01/app/oracle/oradata/ora11gr2/system01.dbf
input datafile file number=00002 name=/u01/app/oracle/oradata/ora11gr2/sysaux01.dbf
input datafile file number=00003 name=/u01/app/oracle/oradata/ora11gr2/undotbs01.dbf
input datafile file number=00005 name=/u01/app/oracle/oradata/ora11gr2/example01.dbf
input datafile file number=00004 name=/u01/app/oracle/oradata/ora11gr2/users01.dbf
channel ora_disk_1: starting piece 1 at 20-mar-13
channel ora_disk_1: finished piece 1 at 20-mar-13
piece handle=/u01/app/fra/ora11gr2/backupset/2013_03_20/o1_mf_nnndf_tag20130320t151949_8nlrrrfm_.bkp tag=tag20130320t151949 comment=none
channel ora_disk_1: backup set complete, elapsed time: 00:02:17
channel ora_disk_1: starting compressed full datafile backup set
channel ora_disk_1: specifying datafile(s) in backup set
including current control file in backup set
including current spfile in backup set
channel ora_disk_1: starting piece 1 at 20-mar-13
channel ora_disk_1: finished piece 1 at 20-mar-13
piece handle=/u01/app/fra/ora11gr2/backupset/2013_03_20/o1_mf_ncsnf_tag20130320t151949_8nlrx2qs_.bkp tag=tag20130320t151949 comment=none
channel ora_disk_1: backup set complete, elapsed time: 00:00:01
finished backup at 20-mar-13
2)创建测试数据
sys@ora11gr2>create table scott.t1 as select 1 as id from dual;
table created.
sys@ora11gr2>alter system archive log current;
system altered.
sys@ora11gr2>create table scott.t2 as select 2 as id from dual;
table created.
sys@ora11gr2>alter system archive log current;
system altered.
sys@ora11gr2>create table scott.t3 as select 3 as id from dual;
table created.
sys@ora11gr2>alter system archive log current;
system altered.
sys@ora11gr2>
sys@ora11gr2>select table_name,tablespace_name from dba_tables where owner='scott' and table_name like 't_';
table_name tablespace_name
------------------------- ------------------------------
t3 users
t2 users
t1 users
完成测试数据构造后，查看生成的归档日志
ls /u01/app/oracle/fast_recovery_area/prod/archivelog/2014_10_08
ls -lrt
3)删除所有数据文件和在线数据文件
在sqlplus里面删除：
sys@prod>select name from v$datafile;
name
--------------------
/u01/app/oracle/oradata/prod/datafile/o1_mf_system_b2251bs1_.dbf
/u01/app/oracle/oradata/prod/datafile/o1_mf_sysaux_b2251bvo_.dbf
/u01/app/oracle/oradata/prod/datafile/o1_mf_undotbs1_b2251bw5_.dbf
/u01/app/oracle/oradata/prod/datafile/o1_mf_users_b2251byw_.dbf
/u01/app/oracle/oradata/prod/datafile/o1_mf_example_b2257d0c_.dbf
/u01/app/oracle/oradata/prod/datafile/tbs_move_01.dbf
sys@prod>select member from v$logfile;
member
----------------------------------------------------------------------------------------------------------------------------------
/u01/app/oracle/oradata/prod/onlinelog/o1_mf_3_b22567o2_.log
/u01/app/oracle/fast_recovery_area/prod/onlinelog/o1_mf_3_b2256827_.log
/u01/app/oracle/oradata/prod/onlinelog/o1_mf_2_b2255zsg_.log
/u01/app/oracle/fast_recovery_area/prod/onlinelog/o1_mf_2_b22560gb_.log
/u01/app/oracle/oradata/prod/onlinelog/o1_mf_1_b2255npg_.log
/u01/app/oracle/fast_recovery_area/prod/onlinelog/o1_mf_1_b2255nxl_.log
sys@prod>!rm /u01/app/oracle/oradata/prod/datafile/*.dbf;
sys@prod>!rm /u01/app/oracle/oradata/prod/onlinelog/*.log;
sys@prod>!rm /u01/app/oracle/fast_recovery_area/prod/onlinelog/*.log;
在os里面删除：
[oracle@ocmu ora11gr2]$ pwd
/u01/app/oracle/oradata/ora11gr2
[oracle@ocmu ora11gr2]$ ls
control01.ctl example01.dbf redo02.log sysaux01.dbf temp01.dbf users01.dbf
control02.ctl redo01.log redo03.log system01.dbf undotbs01.dbf
[oracle@ocmu ora11gr2]$ rm *.log
[oracle@ocmu ora11gr2]$ ls
control01.ctl example01.dbf system01.dbf undotbs01.dbf
control02.ctl sysaux01.dbf temp01.dbf users01.dbf
[oracle@ocmu ora11gr2]$
4)数据库启动到mount模式
sys@ora11gr2>shutdown abort;
oracle instance shut down.
sys@ora11gr2>startup mount;
oracle instance started.
total system global area 841162752 bytes
fixed size 1339768 bytes
variable size 532680328 bytes
database buffers 301989888 bytes
redo buffers 5152768 bytes
database mounted.
sys@ora11gr2>
5)rman还原数据库
rman> restore database;
starting restore at 20-mar-13
using target database control file instead of recovery catalog
allocated channel: ora_disk_1
channel ora_disk_1: sid=18 device type=disk
channel ora_disk_1: starting datafile backup set restore
channel ora_disk_1: specifying datafile(s) to restore from backup set
channel ora_disk_1: restoring datafile 00001 to /u01/app/oracle/oradata/ora11gr2/system01.dbf
channel ora_disk_1: restoring datafile 00002 to /u01/app/oracle/oradata/ora11gr2/sysaux01.dbf
channel ora_disk_1: restoring datafile 00003 to /u01/app/oracle/oradata/ora11gr2/undotbs01.dbf
channel ora_disk_1: restoring datafile 00004 to /u01/app/oracle/oradata/ora11gr2/users01.dbf
channel ora_disk_1: restoring datafile 00005 to /u01/app/oracle/oradata/ora11gr2/example01.dbf
channel ora_disk_1: reading from backup piece /u01/app/fra/ora11gr2/backupset/2013_03_20/o1_mf_nnndf_tag20130320t151949_8nlrrrfm_.bkp
channel ora_disk_1: piece handle=/u01/app/fra/ora11gr2/backupset/2013_03_20/o1_mf_nnndf_tag20130320t151949_8nlrrrfm_.bkp tag=tag20130320t151949
channel ora_disk_1: restored backup piece 1
channel ora_disk_1: restore complete, elapsed time: 00:03:26
finished restore at 20-mar-13
rman>
6)基于cancel恢复
sys@ora11gr2>recover database until cancel;
--在sqlplus中完成
ora-00279: change 883460 generated at 03/20/2013 15:19:50 needed for thread 1
ora-00289: suggestion :
/u01/app/fra/ora11gr2/archivelog/2013_03_20/o1_mf_1_8_8nlrzy8w_.arc
ora-00280: change 883460 for thread 1 is in sequence #8
specify log: {=suggested | filename | auto | cancel}
--应用sequence #8为8，名字为o1_mf_1_8_8nlrzy8w_.arc的归档日志文件，直接按回车键
ora-00279: change 884069 generated at 03/20/2013 15:23:42 needed for thread 1
ora-00289: suggestion :
/u01/app/fra/ora11gr2/archivelog/2013_03_20/o1_mf_1_9_8nls091w_.arc
ora-00280: change 884069 for thread 1 is in sequence #9
ora-00278: log file
'/u01/app/fra/ora11gr2/archivelog/2013_03_20/o1_mf_1_8_8nlrzy8w_.arc' no longer
needed for this recovery
specify log: {=suggested | filename | auto | cancel}
--应用sequence #9为9的这个归档日志文件，直接按回车键
ora-00279: change 884101 generated at 03/20/2013 15:23:53 needed for thread 1
ora-00289: suggestion :
/u01/app/fra/ora11gr2/archivelog/2013_03_20/o1_mf_1_10_8nls0os6_.arc
ora-00280: change 884101 for thread 1 is in sequence #10
ora-00278: log file
'/u01/app/fra/ora11gr2/archivelog/2013_03_20/o1_mf_1_9_8nls091w_.arc' no longer
needed for this recovery
specify log: {=suggested | filename | auto | cancel}
cancel
--手动输入cancel结束恢复，不管后面有没有可恢复文件，都会结束；
media recovery cancelled.
sys@ora11gr2>
sys@ora11gr2>alter database open resetlogs;
database altered.
sys@ora11gr2>
7）检查
sys@ora11gr2>select table_name,tablespace_name from dba_tables where owner='scott' and table_name like 't_';
table_name tablespace_name
------------------------- ------------------------------
t2 users
t1 users
sys@ora11gr2>
注 应用了两个归档日志，第三个归档日志cancel了，从结果中也可以看到，恢复以后的数据库中，只
存在t1,t2两张表，它们俩的重做日志就在前两个归档中，t3表的所有重做条目都在第三个归档中，
由于采取了cancel，所以，t3表并未恢复