首页 > 代码库 > 使用控制文件快照辅助恢复
使用控制文件快照辅助恢复
如果没有设置控制文件自动备份，则在备份的时候首先会有控制文件快照，这样快照的信息不包含备份集的信息。
启用控制文件自动备份之后，在备份完之后会有控制文件快照，以及控制文件和spfile的备份。
Rman >show controlfile autobackup;
Rman>show snapshot controlfile name;
Rman>configure controlfile autobackup on;
测试如下：
RMAN> backup database format ‘/home/oracle/backup/mark_%d_%T_%s‘;
建立测试表
SQL> conn xzsp/xzsp
Connected.
SQL> create table mark(id number,edate date,escn number);
SQL>grant dba to xzsp;
begin
for i in 1 .. 4 loop
insert into mark values(i,sysdate,dbms_flashback.get_system_change_number);
execute immediate ‘alter system switch logfile‘;
dbms_lock.sleep(15);
end loop;
end;
/
SQL>commit; //后来加上的
SQL> col escn for 99999999999999
SQL> alter session set nls_date_format=‘yyyy-mm-dd hh24:mi:ss‘;
Session altered.
SQL> select * from mark;
ID EDATE ESCN
---------- ------------------- ---------------
1 2014-08-25 01:41:50 585713
2 2014-08-25 01:42:08 585720
3 2014-08-25 01:42:23 585727
4 2014-08-25 01:42:43 585734
记录一下生成的归档日志
SQL> select stamp,FIRST_TIME,name from v$archived_log;
856489320 2014-08-24 03:49:54
+FLASH/orcl/archivelog/2014_08_25/thread_1_seq_18.272.856489319
STAMP FIRST_TIME
---------- -------------------
NAME
--------------------------------------------------------------------- -----------
856489328 2014-08-25 01:41:53
+FLASH/orcl/archivelog/2014_08_25/thread_1_seq_19.273.856489329
856489348 2014-08-25 01:42:08
+FLASH/orcl/archivelog/2014_08_25/thread_1_seq_20.274.856489349
856489363 2014-08-25 01:42:28
+FLASH/orcl/archivelog/2014_08_25/thread_1_seq_21.275.856489363
18 rows selected.
进行数据库全备份
backup database format ‘/home/oracle/backup/mark_%d_%T_%s‘;
删除数据文件和控制文件
使用snap_controffile还原控制文件
RMAN> restore controlfile from ‘/opt/oracle/102/dbs/snapcf_orcl.f‘;
Starting restore at 25-AUG-14
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=155 devtype=DISK
channel ORA_DISK_1: copied control file copy
output filename=+DATA/orcl/controlfile/current.260.856490717
output filename=+FLASH/orcl/controlfile/current.276.856490717
Finished restore at 25-AUG-14
基于SCN恢复数据
RMAN> alter database mount;
database mounted
released channel: ORA_DISK_1
RMAN> run
2> {set until scn 585734;
3> restore database;
4> recover database;
5> }
executing command: SET until clause
Starting restore at 25-AUG-14
Starting implicit crosscheck backup at 25-AUG-14
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=155 devtype=DISK
Crosschecked 9 objects
Finished implicit crosscheck backup at 25-AUG-14
Starting implicit crosscheck copy at 25-AUG-14
using channel ORA_DISK_1
Finished implicit crosscheck copy at 25-AUG-14
searching for all files in the recovery area
cataloging files...
cataloging done
List of Cataloged Files
=======================
File Name: +flash/ORCL/ARCHIVELOG/2014_08_25/thread_1_seq_18.272.8564 89319
File Name: +flash/ORCL/ARCHIVELOG/2014_08_25/thread_1_seq_19.273.8564 89329
File Name: +flash/ORCL/ARCHIVELOG/2014_08_25/thread_1_seq_20.274.8564 89349
File Name: +flash/ORCL/ARCHIVELOG/2014_08_25/thread_1_seq_21.275.8564 89363
File Name: +flash/ORCL/ARCHIVELOG/2014_08_23/thread_1_seq_5.267.85639 4649
File Name: +flash/ORCL/BACKUPSET/2014_08_23/annnf0_ORCL_0.263.8563925 09
File Name: +flash/ORCL/BACKUPSET/2014_08_23/nnndf0_ORCL_0.262.8563925 09
File Name: +flash/ORCL/BACKUPSET/2014_08_23/ncnnf0_ORCL_0.261.8563925 25
File Name: +flash/ORCL/BACKUPSET/2014_08_23/annnf0_ORCL_0.265.8563925 29
File Name: +flash/ORCL/AUTOBACKUP/2014_08_23/s_856392530.264.85639253 1
File Name: +flash/ORCL/AUTOBACKUP/2014_08_23/s_856394273.266.85639427 3
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to +DATA/orcl/datafile/system.256.856384885
restoring datafile 00002 to +DATA/orcl/datafile/undotbs1.258.85638488 5
restoring datafile 00003 to +DATA/orcl/datafile/sysaux.257.856384885
restoring datafile 00004 to +DATA/orcl/datafile/users.259.856384885
restoring datafile 00005 to +DATA/orcl/datafile/xzsp.265.856388579
restoring datafile 00006 to +DATA/orcl/datafile/rman.266.856390787
channel ORA_DISK_1: reading from backup piece /home/oracle/backup/mar k_ORCL_20140825_28
channel ORA_DISK_1: restored backup piece 1
piece handle=/home/oracle/backup/mark_ORCL_20140825_28 tag=TAG2014082 5T013112
channel ORA_DISK_1: restore complete, elapsed time: 00:00:16
Finished restore at 25-AUG-14
Starting recover at 25-AUG-14
using channel ORA_DISK_1
starting media recovery
archive log thread 1 sequence 18 is already on disk as file +FLASH/or cl/archivelog/2014_08_25/thread_1_seq_18.272.856489319
archive log thread 1 sequence 19 is already on disk as file +FLASH/or cl/archivelog/2014_08_25/thread_1_seq_19.273.856489329
archive log thread 1 sequence 20 is already on disk as file +FLASH/or cl/archivelog/2014_08_25/thread_1_seq_20.274.856489349
archive log thread 1 sequence 21 is already on disk as file +FLASH/or cl/archivelog/2014_08_25/thread_1_seq_21.275.856489363
archive log filename=+FLASH/orcl/archivelog/2014_08_25/thread_1_seq_1 8.272.856489319 thread=1 sequence=18
archive log filename=+FLASH/orcl/archivelog/2014_08_25/thread_1_seq_1 9.273.856489329 thread=1 sequence=19
archive log filename=+FLASH/orcl/archivelog/2014_08_25/thread_1_seq_2 0.274.856489349 thread=1 sequence=20
archive log filename=+FLASH/orcl/archivelog/2014_08_25/thread_1_seq_2 1.275.856489363 thread=1 sequence=21
media recovery complete, elapsed time: 00:00:03
Finished recover at 25-AUG-14
RMAN> alter database open resetlogs;
测试数据：
SQL> select * from mark;
no rows selected
发现执行了脚本没有commit
?
注：不完全恢复也可以基于时间点恢复
使用控制文件快照辅助恢复