首页 > 代码库 > 使用控制文件快照辅助恢复

使用控制文件快照辅助恢复

如果没有设置控制文件自动备份,则在备份的时候首先会有控制文件快照,这样快照的信息不包含备份集的信息。

启用控制文件自动备份之后,在备份完之后会有控制文件快照,以及控制文件和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

?

注:不完全恢复也可以基于时间点恢复

使用控制文件快照辅助恢复