代码改变世界

Oracle 中的 Incarnation 到底是个什么?实验操作篇

2019-05-28 22:12  askscuti  阅读(2327)  评论(0编辑  收藏  举报

对于“化身”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.
archivelog sequence

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