Oracle使用ancestor incarnation完成基于时间点的不完全恢复

最新推荐文章于 2021-04-12 03:58:58 发布

Monkey·D·Boy

最新推荐文章于 2021-04-12 03:58:58 发布

阅读量402

点赞数

分类专栏： Oracle备份恢复专题

原文链接：https://blog.csdn.net/lihuarongaini/article/details/101402272

版权

Oracle备份恢复专题专栏收录该内容

19 篇文章 0 订阅

订阅专栏

每次以resetlogs选项打开数据库时一个新的数据库incarnation就会被创建。执行open resetlogs操作时就会对当前的联机重做日志文件进行归档。incarnation会将重做日志序列号设置为1,并且指联机重做日志一个新的时间戳。它也会增加incarnation的序号，它被用来唯一标记和识另重做日志流。

什么情况下使用
在使用了open resetlogs之后，但是想恢复到resetlogs之前的时间点的数据，这时可以使用ancestor incarnation。

incarnation（分身）：就是数据库由于resetlogs之后，在不同时间点的版本

incarnation可能存在的几种关系
1.current incarnation是由那个incarnation执行open resetlog操作产生的，那个incarnation就是current incarnation的parent incarnation

2.parent incarnation和它parent incarnation的incarnation就叫作current incarnation的ancestor incarnations

3.如果两个incarnation共享相同的ancestor那么它们就是sibling incarnations

SQL> select * from v$database_incarnation;

INCARNATION# RESETLOGS_CHANGE# RESETLOGS_TI PRIOR_RESETLOGS_CHANGE# PRIOR_RESETL STATUS RESETLOGS_ID PRIOR_INCARNATION# FLASHBACK_DATABASE_ALLOWED

       1                 1 30-JUN-05                          0              PARENT     562360180                  0 NO
       2            446075 05-SEP-14                          1 30-JUN-05    PARENT     857466832                  1 NO
       3           2849317 27-JAN-15                     446075 05-SEP-14    PARENT     870102602                  2 NO
       4           2880152 27-JAN-15                    2849317 27-JAN-15    PARENT     870133266                  3 NO
       5           3017109 01-FEB-15                    2880152 27-JAN-15    PARENT     870550288                  4 NO
       6           3041066 01-FEB-15                    3017109 01-FEB-15    PARENT     870563157                  5 NO
       7           3041350 01-FEB-15                    3041066 01-FEB-15    PARENT     870564201                  6 YES
       8           3111834 03-FEB-15                    3041350 01-FEB-15    ORPHAN     870724654                  7 YES
       9           3111834 03-FEB-15                    3041350 01-FEB-15    ORPHAN     870726369                  7 YES
      10           3114665 03-FEB-15                    3041350 01-FEB-15    ORPHAN     870726883                  7 YES
      11           3114664 03-FEB-15                    3041350 01-FEB-15    CURRENT    870729934                  7 YES

11 rows selected.

RMAN> list incarnation;

List of Database Incarnations
DB Key Inc Key DB Name DB ID STATUS Reset SCN Reset Time

1 1 TEST 2155613261 PARENT 1 30-JUN-05
2 2 TEST 2155613261 PARENT 446075 05-SEP-14
3 3 TEST 2155613261 PARENT 2849317 27-JAN-15
4 4 TEST 2155613261 PARENT 2880152 27-JAN-15
5 5 TEST 2155613261 PARENT 3017109 01-FEB-15
6 6 TEST 2155613261 PARENT 3041066 01-FEB-15
7 7 TEST 2155613261 PARENT 3041350 01-FEB-15
8 8 TEST 2155613261 ORPHAN 3111834 03-FEB-15
9 9 TEST 2155613261 ORPHAN 3111834 03-FEB-15
11 11 TEST 2155613261 CURRENT 3114664 03-FEB-15
10 10 TEST 2155613261 ORPHAN 3114665 03-FEB-15

使用current incarnation来执行按时间点恢复
使用current incarnation执行按时间点恢复时是使用的当前版本的控制文件。当执行按时间点恢复时，可以使用set until命令来设置恢复的目标时间，而不用对restotre和recover命令单独设置until子句从而避免出错。这能确保从备份中还原的数据文件的时间戳早于后续的recover操作。

使用ancestor incarnation执行按时间点恢复
使用ancestor incarnation执行按时间点恢复与使用current incarnation的不同之处在于需要设置数据库的incarnation.并且必须从包含恢复目标SCN的incarnation中还原控制文件。

不使用recover catalog的情况
比如我们要将数据库恢复到scott用户的emp表被删除之后的时间点，对older incarnation执行按时间点恢复的过程如下:

1.判断要使用的incarnation。可以使用list incarnation命令来找到恢复目标时间所对应的incarnation

RMAN> list incarnation;

List of Database Incarnations
DB Key Inc Key DB Name DB ID STATUS Reset SCN Reset Time

1 1 TEST 2155613261 PARENT 1 2005-06-30 19:09:40
2 2 TEST 2155613261 PARENT 446075 2014-09-05 09:13:52
3 3 TEST 2155613261 PARENT 2849317 2015-01-27 15:10:02
4 4 TEST 2155613261 PARENT 2880152 2015-01-27 23:41:06
5 5 TEST 2155613261 PARENT 3017109 2015-02-01 19:31:28
6 6 TEST 2155613261 PARENT 3041066 2015-02-01 23:05:57
7 7 TEST 2155613261 PARENT 3041350 2015-02-01 23:23:21
8 8 TEST 2155613261 ORPHAN 3111834 2015-02-03 19:57:34
9 9 TEST 2155613261 ORPHAN 3111834 2015-02-03 20:26:09
11 11 TEST 2155613261 PARENT 3114664 2015-02-03 21:25:34
10 10 TEST 2155613261 ORPHAN 3114665 2015-02-03 20:34:43
12 12 TEST 2155613261 PARENT 3142278 2015-02-04 11:40:02
13 13 TEST 2155613261 PARENT 3144077 2015-02-04 13:09:03
14 14 TEST 2155613261 CURRENT 3144537 2015-02-04 13:32:41

当前的incarnation的Inc Key为14.通过下面的查询可以找到它之前的incarnation的Inc Key为13：

SQL> select prior_incarnation# from v$database_incarnation where status =‘CURRENT’;

PRIOR_INCARNATION#

2.将数据库启动到mount状态

RMAN> startup mount

Oracle instance started
database mounted

Total System Global Area 327155712 bytes

Fixed Size 1273516 bytes
Variable Size 138412372 bytes
Database Buffers 184549376 bytes
Redo Buffers 2920448 bytes

3.将数据库test的incarnation设置为incarnation号为13,也就是current incarnation的parent incarnation。

RMAN> reset database to incarnation 13;

database reset to incarnation 13

4.执行还原与恢复，设置恢复目标时间，如果没有配置相关通道设置通道。将数据库恢复到表emp删除之后的时间点（2015-02-04 13:30:01）：

RMAN> run
2> {
3> set until time ‘2015-02-04 13:30:01’;
4> restore database;
5> recover database;
6> }

executing command: SET until clause

Starting restore at 2015-02-04 13:54:37
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 /u01/app/oracle/oradata/test/system01.dbf
restoring datafile 00002 to /u01/app/oracle/oradata/test/undotbs01.dbf
restoring datafile 00003 to /u01/app/oracle/oradata/test/sysaux01.dbf
restoring datafile 00004 to /u01/app/oracle/oradata/test/users01.dbf
restoring datafile 00005 to /u01/app/oracle/oradata/test/example01.dbf
restoring datafile 00006 to /u01/app/oracle/oradata/test/test01.dbf
restoring datafile 00007 to /u01/app/oracle/oradata/test/testbak.dbf
channel ORA_DISK_1: reading from backup piece /u02/test_df870779983_s135_s1
channel ORA_DISK_1: restored backup piece 1
piece handle=/u02/test_df870779983_s135_s1 tag=TAG20150204T111943
channel ORA_DISK_1: restore complete, elapsed time: 00:02:37
Finished restore at 2015-02-04 13:57:14

Starting recover at 2015-02-04 13:57:14
using channel ORA_DISK_1

starting media recovery

archive log thread 1 sequence 3 is already on disk as file /u02/1_3_870729934.dbf
archive log thread 1 sequence 4 is already on disk as file /u02/1_4_870729934.dbf
archive log thread 1 sequence 1 is already on disk as file /u02/1_1_870781202.dbf
archive log filename=/u02/1_3_870729934.dbf thread=1 sequence=3
archive log filename=/u02/1_4_870729934.dbf thread=1 sequence=4
archive log filename=/u02/1_1_870781202.dbf thread=1 sequence=1
archive log filename=/u02/1_1_870786543.dbf thread=1 sequence=1
media recovery complete, elapsed time: 00:00:03
Finished recover at 2015-02-04 13:57:18

RMAN> alter database open resetlogs;

database opened

RMAN> list incarnation;

List of Database Incarnations
DB Key Inc Key DB Name DB ID STATUS Reset SCN Reset Time

使用recover catalog的情况
比如我们要将数据库恢复到scott用户的emp表被删除之后的时间点，对older incarnation执行按时间点恢复的过程如下:

1.判断要使用的incarnation。可以使用list incarnation命令来找到恢复目标时间所对应的incarnation

RMAN> list incarnation;

List of Database Incarnations
DB Key Inc Key DB Name DB ID STATUS Reset SCN Reset Time

1 8 TEST 2168949517 PARENT 1 2010-04-19 10:22:46
1 2 TEST 2168949517 PARENT 383537 2015-02-04 17:44:49
1 102 TEST 2168949517 PARENT 415176 2015-02-04 18:22:16
1 188 TEST 2168949517 CURRENT 415481 2015-02-04 18:33:17

当前的incarnation的Inc Key为188.通过下面的查询可以找到它之前的incarnation的Inc Key为102：我们将数据库恢复到2015-02-04 18:22:30，也就是在SCN:415176和SCN:415481之间。

RMAN> list backup of controlfile;

List of Backup Sets

BS Key Type LV Size Device Type Elapsed Time Completion Time

75 Full 6.80M DISK 00:00:01 2015-02-04 18:11:38
BP Key: 77 Status: AVAILABLE Compressed: NO Tag: TAG20150204T181137
Piece Name: /u01/app/oracle/flash_recovery_area/TEST/autobackup/2015_02_04/o1_mf_s_870804697_bf3w2t62_.bkp
Control File Included: Ckp SCN: 415111 Ckp time: 2015-02-04 18:11:37

BS Key Type LV Size Device Type Elapsed Time Completion Time

139 Full 6.80M DISK 00:00:02 2015-02-04 18:22:45
BP Key: 144 Status: AVAILABLE Compressed: NO Tag: TAG20150204T182243
Piece Name: /u01/app/oracle/flash_recovery_area/TEST/autobackup/2015_02_04/o1_mf_s_870805363_bf3wqnyv_.bkp
Control File Included: Ckp SCN: 415288 Ckp time: 2015-02-04 18:22:43

BS Key Type LV Size Device Type Elapsed Time Completion Time

236 Full 6.80M DISK 00:00:03 2015-02-04 18:33:39
BP Key: 242 Status: AVAILABLE Compressed: NO Tag: TAG20150204T183336
Piece Name: /u01/app/oracle/flash_recovery_area/TEST/autobackup/2015_02_04/o1_mf_s_870806016_bf3xd2wl_.bkp
Control File Included: Ckp SCN: 415765 Ckp time: 2015-02-04 18:33:36

从上面的控制文件备份信息可以看到要恢复到2015-02-04 18:22:30这个时间点应该使用控制文件备份是o1_mf_s_870805363_bf3wqnyv_.bkp

2.将数据库强制启动到nomount状态

RMAN> startup force nomount

Oracle instance started

Total System Global Area 327155712 bytes

Fixed Size 1273516 bytes
Variable Size 138412372 bytes
Database Buffers 184549376 bytes
Redo Buffers 2920448 bytes

3.将数据库test的incarnation设置为incarnation号为102,也就是current incarnation的parent incarnation。

RMAN> reset database to incarnation 102;

database reset to incarnation 102

RMAN> list incarnation;

List of Database Incarnations
DB Key Inc Key DB Name DB ID STATUS Reset SCN Reset Time

1 8 TEST 2168949517 PARENT 1 2010-04-19 10:22:46
1 2 TEST 2168949517 PARENT 383537 2015-02-04 17:44:49
1 102 TEST 2168949517 CURRENT 415176 2015-02-04 18:22:16
1 188 TEST 2168949517 ORPHAN 415481 2015-02-04 18:33:17

4.执行还原与恢复，设置恢复目标时间，如果没有配置相关通道设置通道。还原控制文件，将数据库恢复到表emp删除之后的时间点（2015-02-04 18:22:30）：

RMAN> restore controlfile;

Starting restore at 2015-02-04 18:44:23
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=156 devtype=DISK

channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/flash_recovery_area/TEST/autobackup/2015_02_04/o1_mf_s_870805363_bf3wqnyv_.bkp
channel ORA_DISK_1: restored backup piece 1
piece handle=/u01/app/oracle/flash_recovery_area/TEST/autobackup/2015_02_04/o1_mf_s_870805363_bf3wqnyv_.bkp tag=TAG20150204T182243
channel ORA_DISK_1: restore complete, elapsed time: 00:00:03
output filename=/u01/app/oracle/oradata/test/control01.ctl
output filename=/u01/app/oracle/oradata/test/control02.ctl
output filename=/u01/app/oracle/oradata/test/control03.ctl
Finished restore at 2015-02-04 18:44:29

RMAN> alter database mount;

database mounted
released channel: ORA_DISK_1

RMAN> restore database until time ‘2015-02-04 18:22:30’;

Starting restore at 2015-02-04 18:47:15
Starting implicit crosscheck backup at 2015-02-04 18:47:15
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=156 devtype=DISK
Crosschecked 4 objects
Finished implicit crosscheck backup at 2015-02-04 18:47:17

Starting implicit crosscheck copy at 2015-02-04 18:47:17
using channel ORA_DISK_1
Finished implicit crosscheck copy at 2015-02-04 18:47:17

searching for all files in the recovery area
cataloging files…
cataloging done

List of Cataloged Files

File Name: /u01/app/oracle/flash_recovery_area/TEST/autobackup/2015_02_04/o1_mf_s_870805363_bf3wqnyv_.bkp
File Name: /u01/app/oracle/flash_recovery_area/TEST/autobackup/2015_02_04/o1_mf_s_870806016_bf3xd2wl_.bkp

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 /u01/app/oracle/oradata/test/system01.dbf
restoring datafile 00002 to /u01/app/oracle/oradata/test/undotbs01.dbf
restoring datafile 00003 to /u01/app/oracle/oradata/test/sysaux01.dbf
restoring datafile 00004 to /u01/app/oracle/oradata/test/users01.dbf
restoring datafile 00005 to /u01/app/oracle/oradata/test/example01.dbf
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/flash_recovery_area/TEST/backupset/2015_02_04/o1_mf_nnndf_TAG20150204T181037_bf3w0y1f_.bkp
channel ORA_DISK_1: restored backup piece 1
piece handle=/u01/app/oracle/flash_recovery_area/TEST/backupset/2015_02_04/o1_mf_nnndf_TAG20150204T181037_bf3w0y1f_.bkp tag=TAG20150204T181037
channel ORA_DISK_1: restore complete, elapsed time: 00:01:15
Finished restore at 2015-02-04 18:48:32

将数据文件还原到2015-02-04 18:22:30这个时间点，下面执行恢复，要注意的是在执行恢复操作之前还需要执行reset database to incarnation 102否则会报错:

RMAN> recover database until time ‘2015-02-04 18:22:30’;

Starting recover at 2015-02-04 18:49:05
using channel ORA_DISK_1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 02/04/2015 18:49:05
RMAN-06004: ORACLE error from recovery catalog database: RMAN-20011: target database incarnation is not current in recovery catalog

再次执行 reset database to incarnation 102;

RMAN> reset database to incarnation 102;

database reset to incarnation 102

RMAN> recover database until time ‘2015-02-04 18:22:30’;

Starting recover at 2015-02-04 18:49:21
using channel ORA_DISK_1

starting media recovery

archive log thread 1 sequence 3 is already on disk as file /u02/1_3_870803089.dbf
archive log thread 1 sequence 4 is already on disk as file /u02/1_4_870803089.dbf
archive log thread 1 sequence 1 is already on disk as file /u02/1_1_870805336.dbf
archive log filename=/u02/1_3_870803089.dbf thread=1 sequence=3
archive log filename=/u02/1_4_870803089.dbf thread=1 sequence=4
archive log filename=/u02/1_1_870805336.dbf thread=1 sequence=1
media recovery complete, elapsed time: 00:00:01
Finished recover at 2015-02-04 18:49:24

RMAN> alter database open resetlogs;

database opened
new incarnation of database registered in recovery catalog
starting full resync of recovery catalog
full resync complete

RMAN> list incarnation;

List of Database Incarnations
DB Key Inc Key DB Name DB ID STATUS Reset SCN Reset Time

1 8 TEST 2168949517 PARENT 1 2010-04-19 10:22:46
1 2 TEST 2168949517 PARENT 383537 2015-02-04 17:44:49
1 102 TEST 2168949517 PARENT 415176 2015-02-04 18:22:16
1 308 TEST 2168949517 CURRENT 415183 2015-02-04 18:49:41
1 188 TEST 2168949517 ORPHAN 415481 2015-02-04 18:33:17

可以看到恢复完成之后当前的incarnation对应的reset scn号在415176与415481之间，达到了我们所期待的结果。