对于“化身”Incarnation概念了解之后,本篇通过手工恢复实验来具体操作演示,加深对Incarnation的理解,来自于博客园AskScuti。你可以点击此处查看《概念理解篇》。
目录
1. 官方图示例
2. 场景模拟
3. 实验步骤
3.1 备份数据库(略)
3.2 查询当前数据库化身版本
3.3 按场景模拟操作
3.4 恢复出B表并打开数据库
3.5 查询当前数据库化身版本
3.6 恢复出A-6(修改当前化身)并打开数据库
3.7 查询当前数据库化身版本
1. 官方图示例
在官方文档 Release 19c Backup and Recovery User's Guide:14.3.2.2 Relationship Among Database Incarnations 中有这么一张示例图片。(注意:每个版本都有,这里只是顺手翻了最新版本)
此图涉及三个版本的化身 Incarnation。
Incarnation 1:
最低位黑色水平线从 SCN1 开始,经历 SCN1000,直到 SCN2000,这个为数据库第一个化身,称之为 Incarnation 1,这时候,化身1 就为当前化身(current incarnation)。
Incarnation 2:
假设在化身1中,我们执行了一个时间点恢复(不完全恢复),且指定的地方是 SCN1000 的位置,然后我们通过使用 Resetlogs 选项打开了数据库,这时,化身2 就出现了(45°倾斜黑色实线),化身2 从SCN1000开始,持续到 SCN3000。这时候,我们称化身1 是 化身2 的父级化身(parent incarnation),化身2 变为当前化身(current incarnation)。
Incarnation 3:
我们观察下向右上角45°倾斜的这条黑色实线,它是化身2。在化身2中,它从 SCN1000开始,经过 SCN2000,持续到 SCN3000。假设在化身2 中,我们执行了一个时间点恢复(不完全恢复),且指定的地方是 SCN2000 的位置,然后通过 Resetlogs 选项打开数据库,这时,化身3 就出现了(位于最高位的黑色水平线),化身3 从 SCN2000开始,持续到黑色水平线的 SCN3000。这时候,我们称化身2 是 化身3 的父级化身,称化身1 是 化身3 的祖辈级化身(ancestor incarnation),化身3 变为当前化身(current incarnation)。
2. 场景模拟
我们不妨来模拟一个场景:下面这张图灰色区块一共8个,这是8个动作。分别代表:表A插入1、表A插入2、表A插入3、表B插入666、Drop删除表B、表A插入4、表A插入5、表A插入6。
我在操作完成表A数据6的插入后,发现了刚才刚才Drop掉了表B(这里不讨论闪回技术),而表B数据很重要,需要做基于时间点的不完全恢复,然后以 Resetlogs 选项打开数据库。这时数据库第二个化身出现,如下图
现在数据库里面的操作都是基于化身2,后面我又想还是算了,重新还原恢复到 A-6 吧。于是还原旧的数据文件,然后进行恢复,试想会成功恢复到A-6吗?前提又是什么呢?是可以成功的,前提是需要在RMAN里面指定使用哪一个化身。因为 A-4、A-5 和 A-6 都是属于第一个原始化身:化身1,而现在数据库是基于化身2的,如果使用现在数据库的默认化身2,那么恢复出来的依然是第二个化身中的操作,就是得提前指定方向,你是想往水平方向(化身1)恢复呢,还是想往右上角方向(化身2)恢复。
同理,如果指定了往水平方向去恢复,恢复到A-6之后,依然需要使用 Resetlogs 选项打开数据库,这时数据库的第三个化身出现,而原第二化身变为孤儿化身(ORPHAN),如下图
3. 实验步骤
3.1 备份数据库(略)
3.2 查询当前数据库化身版本(这里实验环境多了一个化身,忽略)
SQL> select INCARNATION#,RESETLOGS_CHANGE#,RESETLOGS_TIME,STATUS from v$database_incarnation; INCARNATION# RESETLOGS_CHANGE# RESETLOGS_TIME STATUS ------------ ----------------- ------------------ ------- 1 1 2011-09-17 09:46:04 PARENT 2 995548 2019-02-23 16:01:17 CURRENT
切归档,查看归档文件名称
SQL> alter system archive log current; System altered. SQL> / System altered. SQL> / System altered. SQL> col name for a60 SQL> select name from v$archived_log; NAME -------------------------------------------- /u01/app/oracle/archive1/1_54_1001001677.dbf /u01/app/oracle/archive1/1_55_1001001677.dbf /u01/app/oracle/archive1/1_56_1001001677.dbf /u01/app/oracle/archive1/1_57_1001001677.dbf
注意归档日志名称中的 1001001677 ,这是由数据库归档参数 log_archive_format 定义的,默认情况下该参数对应的值为:%t_%s_%r.dbf
%t:thread number 日志线程号,单实例中就是1(这里不探讨RAC环境)
%s:log sequence number 日志序列号,每次日志切换归档后,序列号加1
%r:resetlogs ID 日志重置ID号,这个就是控制化身Incarnation的,每次resetlogs后,该ID都会改变,日志序列号又从1开始。其目的是为了保证数据库在跨多个化身版本时,保证归档日志名称的唯一性。
3.3 按场景模拟操作
创建A表和B表,并按照场景模拟操作
SQL> create table a(id number); Table created. SQL> create table b(id number); Table created. SQL> insert into a values(1); 1 row created. SQL> insert into a values(2); 1 row created. SQL> insert into a values(3); 1 row created. SQL> insert into b values(666); 1 row created. SQL> commit; Commit complete. SQL> select sysdate from dual; SYSDATE ------------------- 2019-05-28 19:57:50 SQL> select current_scn from v$database; CURRENT_SCN ----------- 1520977 SQL> drop table b purge; Table dropped. SQL> insert into a values(4); 1 row created. SQL> insert into a values(5); 1 row created. SQL> insert into a values(6); 1 row created. SQL> commit; Commit complete.
3.4 恢复出B表并打开数据库
这时,准备恢复出B表(B表中只有一条数据666)
还原旧的数据文件
SQL> shutdown immediate; Database closed. Database dismounted. ORACLE instance shut down. SQL> !cp /u01/app/oracle/backup/*.dbf /u01/app/oracle/oradata/PROD1/
做不完全恢复
SQL> startup mount ORACLE instance started. Total System Global Area 830930944 bytes Fixed Size 2232920 bytes Variable Size 591400360 bytes Database Buffers 234881024 bytes Redo Buffers 2416640 bytes Database mounted. SQL> select file#,change# from v$recover_file; FILE# CHANGE# ---------- ---------- 1 1519163 2 1519163 3 1519163 4 1519163 5 1519163 6 1519163 7 1519163 8 1519163 9 1519163 10 1519163 10 rows selected. SQL> recover database until change 1520977; ORA-00279: change 1519163 generated at 05/28/2019 19:29:20 needed for thread 1 ORA-00289: suggestion : /u01/app/oracle/archive1/1_55_1001001677.dbf ORA-00280: change 1519163 for thread 1 is in sequence #55 Specify log: {<RET>=suggested | filename | AUTO | CANCEL} auto Log applied. Media recovery complete. SQL> alter database open resetlogs; Database altered.
3.5 查询当前数据库化身版本
完成基于时间点恢复后,查询当前数据库化身版本,对比3.2小节
SQL> select INCARNATION#,RESETLOGS_CHANGE#,RESETLOGS_TIME,STATUS from v$database_incarnation; INCARNATION# RESETLOGS RESETLOGS_TIME STATUS ------------ --------- ------------------- ------- 1 1 2011-09-17 09:46:04 PARENT 2 995548 2019-02-23 16:01:17 PARENT 3 1520978 2019-05-28 20:35:03 CURRENT
可以看到,当前数据库使用的化身为3,我们尝试切换日志生成归档,对比归档名称
SQL> alter system archive log current; System altered. SQL> / System altered. SQL> / System altered. SQL> select name from v$archived_log; NAME -------------------------------------------- /u01/app/oracle/archive1/1_54_1001001677.dbf /u01/app/oracle/archive1/1_55_1001001677.dbf /u01/app/oracle/archive1/1_56_1001001677.dbf /u01/app/oracle/archive1/1_57_1001001677.dbf /u01/app/oracle/archive1/1_58_1001001677.dbf /u01/app/oracle/archive1/1_1_1009485303.dbf /u01/app/oracle/archive1/1_2_1009485303.dbf /u01/app/oracle/archive1/1_3_1009485303.dbf 8 rows selected.
可看到,当数据库 resetlogs 后,归档日志名的 Incarnation 由原来的 1001001677 变为了现在的1009485303,且日志序列号,重新从1开始。
需要注意,Oracle 11g 是可以跨化身进行恢复的。(可以从化身为1001001677 的54号日志开始,一直应用到化身为1009485303的4号日志)
例如,这是另外一个实验,还原旧的数据文件,然后进行完全恢复。仔细观察归档的应用。
SQL> !cp /u01/app/oracle/backup/*.dbf /u01/app/oracle/oradata/PROD1/ SQL> startup mount ORACLE instance started. Total System Global Area 830930944 bytes Fixed Size 2232920 bytes Variable Size 591400360 bytes Database Buffers 234881024 bytes Redo Buffers 2416640 bytes Database mounted. SQL> recover database; ORA-00279: change 1519163 generated at 05/28/2019 19:29:20 needed for thread 1 ORA-00289: suggestion : /u01/app/oracle/archive1/1_55_1001001677.dbf ORA-00280: change 1519163 for thread 1 is in sequence #55 Specify log: {<RET>=suggested | filename | AUTO | CANCEL} auto ORA-00279: change 1519932 generated at 05/28/2019 19:40:13 needed for thread 1 ORA-00289: suggestion : /u01/app/oracle/archive1/1_56_1001001677.dbf ORA-00280: change 1519932 for thread 1 is in sequence #56 ORA-00279: change 1519936 generated at 05/28/2019 19:40:14 needed for thread 1 ORA-00289: suggestion : /u01/app/oracle/archive1/1_57_1001001677.dbf ORA-00280: change 1519936 for thread 1 is in sequence #57 ORA-00279: change 1519941 generated at 05/28/2019 19:40:18 needed for thread 1 ORA-00289: suggestion : /u01/app/oracle/archive1/1_58_1001001677.dbf ORA-00280: change 1519941 for thread 1 is in sequence #58 ORA-00279: change 1520978 generated at 05/28/2019 20:35:03 needed for thread 1 ORA-00289: suggestion : /u01/app/oracle/archive1/1_1_1009485303.dbf ORA-00280: change 1520978 for thread 1 is in sequence #1 ORA-00279: change 1522809 generated at 05/28/2019 20:55:20 needed for thread 1 ORA-00289: suggestion : /u01/app/oracle/archive1/1_2_1009485303.dbf ORA-00280: change 1522809 for thread 1 is in sequence #2 ORA-00279: change 1522813 generated at 05/28/2019 20:55:22 needed for thread 1 ORA-00289: suggestion : /u01/app/oracle/archive1/1_3_1009485303.dbf ORA-00280: change 1522813 for thread 1 is in sequence #3 ORA-00279: change 1522817 generated at 05/28/2019 20:55:24 needed for thread 1 ORA-00289: suggestion : /u01/app/oracle/archive1/1_4_1009485303.dbf ORA-00280: change 1522817 for thread 1 is in sequence #4 ORA-00279: change 1523255 generated at 05/28/2019 21:01:35 needed for thread 1 ORA-00289: suggestion : /u01/app/oracle/archive1/1_5_1009485303.dbf ORA-00280: change 1523255 for thread 1 is in sequence #5 Log applied. Media recovery complete. SQL> alter database open; Database altered.
3.7 恢复出A-6(修改当前化身)并打开数据库
查询当前数据库化身(这里实验环境多了一个化身,忽略)
SQL> select INCARNATION#,RESETLOGS_CHANGE#,RESETLOGS_TIME,STATUS from v$database_incarnation; INCARNATION# RESETLOGS RESETLOGS_TIME STATUS ------------ --------- ------------------ ------ 1 1 2011-09-17 09:46:04 PARENT 2 995548 2019-02-23 16:01:17 PARENT 3 1520978 2019-05-28 20:35:03 CURRENT
当前数据库正在使用3号 Incarnation,也就意味着,数据的恢复,目前只能走标数字号码的这条线。
如果想要通过旧的数据文件,恢复到A-6,那么需要更改数据库恢复路线,就是更改数据库化身版本Incarnation,修改为2即可。(实验环境,之前操作多了一个化身,可以忽略)
关闭数据库,还原旧的数据文件。
SQL> shutdown immediate; Database closed. Database dismounted. ORACLE instance shut down. SQL> !cp /u01/app/oracle/backup/*.dbf /u01/app/oracle/oradata/PROD1/ SQL> startup mount; ORACLE instance started. Total System Global Area 830930944 bytes Fixed Size 2232920 bytes Variable Size 591400360 bytes Database Buffers 234881024 bytes Redo Buffers 2416640 bytes Database mounted.
连接RMAN,更改当前数据库化身版本。
[oracle@henry ~]$ rman target / Recovery Manager: Release 11.2.0.3.0 - Production on Tue May 28 21:35:33 2019 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. connected to target database: PROD1 (DBID=2222344843, not open) RMAN> reset database to incarnation 2; using target database control file instead of recovery catalog database reset to incarnation 2
我们查看下当前数据库使用的化身版本。
SQL> select INCARNATION#,RESETLOGS_CHANGE#,RESETLOGS_TIME,STATUS from v$database_incarnation; INCARNATION# RESETLOGS RESETLOGS_TIME STATUS ------------ --------- ------------------- ------ 1 1 2011-09-17 09:46:04 PARENT 2 995548 2019-02-23 16:01:17 CURRENT 3 1520978 2019-05-28 20:35:03 ORPHAN
现在,数据库又使用了第一个化身版本,化身号为2,(化身号1为初始化身,忽略),而原第二个化身(化身号为3)状态变为了孤儿化身(ORPHAN)。
现在将遵照下图中标注的数字号这条线来恢复出A-6
然后,恢复数据库,仔细观察前滚的归档日志名称。
SQL> recover database; ORA-00279: change 1519163 generated at 05/28/2019 19:29:20 needed for thread 1 ORA-00289: suggestion : /u01/app/oracle/archive1/1_55_1001001677.dbf ORA-00280: change 1519163 for thread 1 is in sequence #55 Specify log: {<RET>=suggested | filename | AUTO | CANCEL} auto ORA-00279: change 1519932 generated at 05/28/2019 19:40:13 needed for thread 1 ORA-00289: suggestion : /u01/app/oracle/archive1/1_56_1001001677.dbf ORA-00280: change 1519932 for thread 1 is in sequence #56 ORA-00278: log file '/u01/app/oracle/archive1/1_55_1001001677.dbf' no longer needed for this recovery ORA-00279: change 1519936 generated at 05/28/2019 19:40:14 needed for thread 1 ORA-00289: suggestion : /u01/app/oracle/archive1/1_57_1001001677.dbf ORA-00280: change 1519936 for thread 1 is in sequence #57 ORA-00278: log file '/u01/app/oracle/archive1/1_56_1001001677.dbf' no longer needed for this recovery ORA-00279: change 1519941 generated at 05/28/2019 19:40:18 needed for thread 1 ORA-00289: suggestion : /u01/app/oracle/archive1/1_58_1001001677.dbf ORA-00280: change 1519941 for thread 1 is in sequence #58 ORA-00278: log file '/u01/app/oracle/archive1/1_57_1001001677.dbf' no longer needed for this recovery Log applied. Media recovery complete. SQL> alter database open resetlogs; Database altered.
确认A-6数据是否已经恢复
SQL> select * from a; ID ---------- 1 2 3 4 5 6 6 rows selected.
3.8 查询当前数据库化身版本
最后,我们再次查询数据库化身版本。对比3.2和3.7。
SQL> select INCARNATION#,RESETLOGS_CHANGE#,RESETLOGS_TIME,STATUS from v$database_incarnation; INCARNATION# RESETLOGS RESETLOGS_TIME STATUS ------------ --------- ------------------- ------ 1 1 2011-09-17 09:46:04 PARENT 2 995548 2019-02-23 16:01:17 PARENT 3 1520978 2019-05-28 20:35:03 ORPHAN 4 1521714 2019-05-28 21:50:58 CURRENT