recover database until cancel

1.recover database until cancel2023-12-11

数据库演示版本为 12.1.0.2

该系列涉及恢复过程中使用的 5 个语句：

1. recover database

2. recover database until cancel

3. recover database using backup controlfile

4. recover database until cancel using backup controlfile

5. recover database using backup controlfile until cancel

恢复级别一共三个：recover database > recover tablespace > recover datafile ，其中最高级别 database 已经包含了 tablespace 和 datafile 两个级别。

1. 概念解释

　　1.1 基于数据库时间点恢复（Database Point-in-Time Recovery）- RMAN

　　1.2 基于取消的不完全恢复（Cancel-Based Incomplete Recovery）- SQL命令行

　　1.3 基于时间 / SCN 的不完全恢复（Time-Based or Change-Based Incomplete Recovery）- SQL命令行

2. 情况说明

3. 实验过程

　　3.1 备份 CDB

　　3.2 创建测试数据

　　3.3 删除所有数据文件

　　3.4 破坏归档日志文件

　　3.5 重启数据库并进行还原操作

　　3.6 恢复数据库

　　3.7 打开数据库

　　3.8 验证数据

1. 概念解释

先理解什么是 until ：直到...时候，到...为止，只要见到 until 就知道是不完全恢复（注意：归档日志和在线日志都完整的情况下，如果你愿意，也可以使用 until 不完全恢复子句进行数据的完全恢复）

until 子句的类型分为以下三大类：

1.1 基于数据库时间点恢复（Database Point-in-Time Recovery）- RMAN

until time '2022-01-01 12:00:00' 告诉数据库，给我（恢复）应用归档直到 12 点整为止
until scn 1234567 本条命令和上面命令一致，只不过 scn 是用在 RMAN 的，而 change 是用在 SQL 命令行的
until sequence 123 也可以在 RMAN 的 run 代码块里指定恢复到的日志序列号

1.2 基于取消的不完全恢复（Cancel-Based Incomplete Recovery）- SQL命令行

until cancel 仅在 SQL 命令行中有效

1.3 基于时间 / SCN 的不完全恢复（Time-Based or Change-Based Incomplete Recovery）- SQL命令行

until time '2022-01-01 12:00:00'
until change 1234567

其中 1.1 主要用于 RMAN 中的 run 代码块，在进行不完全恢复的时候，可以提前进行 set ，例如：

RUN
{
SET UNTIL SCN 1000;
RESTORE DATABASE;
RECOVER DATABASE;
}

你还可以将第一句替换为：

SET UNTIL TIME '2022-05-01 12:00:00';
SET UNTIL SEQUENCE 123;

1.2 和 1.3 小节指的是基于用户管理（手工）恢复，直接在 SQL 命令行中进行的恢复。这里分类，是为了单独讲解 1.2 小节。个人认为，1.1 小节中基于数据库时间点恢复其实包括了（Cancle-Based / Time-Based / Change-Based）这些，只不过是为了区分哪些是在 RMAN 里面做，哪些是在 SQL 命令行里面做。

recover database until cancel 这个命令只能在 SQL 命令行进行，它可以通过提示归档日志文件的建议名称进行主动恢复。也就是恢复应用到哪个归档，由你自己把控，如果在归档和联机日志都完整的情况下，你甚至可以通过不完全恢复的语句来实现数据的完全恢复。

recover database until cancel 命令默认只会应用归档日志，而不会自动应用在线日志，这是和 recover database 的区别，后者自动应用所有归档和在线日志进行前滚操作。

2. 情况说明

当前系统中，所有数据文件损坏、归档日志不连续、联机日志完好无损，删除 CDB 及 PDB 所有数据文件，并采用 RMAN 对所有数据文件进行还原，使用 recover database until cancel 进行不完全恢复。

3. 实验过程

3.1 备份 CDB

RMAN> backup as compressed backupset tag='full' database  format '/u02/backup/%d_%s_%U.full';

3.2 创建测试数据

创建表，插入数据

SYS@PRODCDB> alter session set container=pdbprod1;

Session altered.

SYS@PRODCDB> show pdbs;

    CON_ID CON_NAME              OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
     3 PDBPROD1              READ WRITE NO
SYS@PRODCDB> create table test02(id number);

Table created.

SYS@PRODCDB> insert into test02 values(1);

1 row created.

SYS@PRODCDB>  insert into test02 values(2);

1 row created.

SYS@PRODCDB>  insert into test02 values(3);

1 row created.

SYS@PRODCDB> commit;

Commit complete.

查看当前使用的日志组状态

SYS@PRODCDB> select group#,sequence#,status from v$log;

    GROUP#  SEQUENCE# STATUS
---------- ---------- ----------------
     1        4 CURRENT
     2        2 ACTIVE
     3        3 ACTIVE

当前表数据 1，2，3 在 SEQUENCE 为 4 的日志组中

将表数据 1，2，3 进行归档操作

SYS@PRODCDB> alter system switch logfile;
alter system switch logfile
*
ERROR at line 1:
ORA-65040: operation not allowed from within a pluggable database


SYS@PRODCDB> alter session set container=cdb$root;

Session altered.

SYS@PRODCDB> alter system switch logfile;

System altered.

SYS@PRODCDB> alter system checkpoint;

System altered.

SYS@PRODCDB>  select group#,sequence#,status from v$log;

    GROUP#  SEQUENCE# STATUS
---------- ---------- ----------------
     1        4 INACTIVE
     2        5 CURRENT
     3        3 INACTIVE

继续插入 4，5，6

SYS@PRODCDB> alter session set container=pdbprod1;

Session altered.

SYS@PRODCDB>   insert into test02 values(4);

1 row created.

SYS@PRODCDB>   insert into test02 values(5);

1 row created.

SYS@PRODCDB>   insert into test02 values(6);

1 row created.

SYS@PRODCDB> commit;

Commit complete.

将表数据 4，5，6 切换日志进行归档操作

SYS@PRODCDB>  alter session set container=cdb$root;

Session altered.

SYS@PRODCDB> alter system archive log current;

System altered.

SYS@PRODCDB> alter system checkpoint;

System altered.

查看当前日志组状态

SYS@PRODCDB> select group#,sequence#,status from v$log;

    GROUP#  SEQUENCE# STATUS
---------- ---------- ----------------
     1        4 INACTIVE
     2        5 INACTIVE
     3        6 CURRENT

表数据 1，2，3 在 4 号归档，4，5，6 在 5号归档，现在数据库正在使用 sequence 为 6 的在线日志。

继续插入 7，8，9，不生成归档，数据 7，8，9 保留至 sequence 为 6 的在线日志里面

SYS@PRODCDB>  alter session set container=pdbprod1;

Session altered.

SYS@PRODCDB>  insert into test02 values(7);

1 row created.

SYS@PRODCDB> insert into test02 values(8);

1 row created.

SYS@PRODCDB> insert into test02 values(9);

1 row created.

SYS@PRODCDB> commit;

Commit complete.

SYS@PRODCDB> select group#,sequence#,status from v$log;

    GROUP#  SEQUENCE# STATUS
---------- ---------- ----------------
     1        4 INACTIVE
     2        5 INACTIVE
     3        6 CURRENT

3.3 删除所有数据文件

SYS@PRODCDB> set pagesize 50;
SYS@PRODCDB> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/PRODCDB/system01.dbf
/u01/app/oracle/oradata/PRODCDB/pdbseed/system01.dbf
/u01/app/oracle/oradata/PRODCDB/sysaux01.dbf
/u01/app/oracle/oradata/PRODCDB/pdbseed/sysaux01.dbf
/u01/app/oracle/oradata/PRODCDB/undotbs01.dbf
/u01/app/oracle/oradata/PRODCDB/users01.dbf
/u01/app/oracle/oradata/PRODCDB/PDBPROD1/system01.dbf
/u01/app/oracle/oradata/PRODCDB/PDBPROD1/sysaux01.dbf
/u01/app/oracle/oradata/PRODCDB/PDBPROD1/PDBPROD1_users01.dbf
/u01/app/oracle/oradata/PRODCDB/PDBPROD2/system01.dbf
/u01/app/oracle/oradata/PRODCDB/PDBPROD2/sysaux01.dbf
/u01/app/oracle/oradata/PRODCDB/PDBPROD2/PDBPROD2_users01.dbf
/u01/app/oracle/oradata/PRODCDB/PDBPROD1/example01.dbf

13 rows selected.

删除

SYS@PRODCDB> !rm -rf /u01/app/oracle/oradata/PRODCDB/PDBPROD1/*

SYS@PRODCDB> !rm -rf /u01/app/oracle/oradata/PRODCDB/pdbseed/*

SYS@PRODCDB> !rm -rf /u01/app/oracle/oradata/PRODCDB/system01.dbf

SYS@PRODCDB>  !rm -rf /u01/app/oracle/oradata/PRODCDB/sysaux01.dbf

SYS@PRODCDB>  !rm -rf /u01/app/oracle/oradata/PRODCDB/undotbs01.dbf

SYS@PRODCDB>  !rm -rf /u01/app/oracle/oradata/PRODCDB/users01.dbf

3.4 破坏归档日志文件

SYS@PRODCDB> set linesize 200;
SYS@PRODCDB> select name from v$archived_log;

NAME
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
/u01/app/oracle/fast_recovery_area/PRODCDB/archivelog/2023_12_01/o1_mf_1_162_lplt479s_.arc
/u01/app/oracle/oradata/PRODCDB/redo01.log
/u01/app/oracle/oradata/PRODCDB/redo02.log
/u01/app/oracle/oradata/PRODCDB/redo03.log
/u01/app/oracle/fast_recovery_area/PRODCDB/archivelog/2023_12_01/o1_mf_1_163_lpm8q668_.arc
/u01/app/oracle/fast_recovery_area/PRODCDB/archivelog/2023_12_01/o1_mf_1_161_lpm8q69b_.arc
/u01/app/oracle/fast_recovery_area/PRODCDB/archivelog/2023_12_01/o1_mf_1_162_lpm8q6xm_.arc
/u01/app/oracle/fast_recovery_area/PRODCDB/archivelog/2023_12_11/o1_mf_1_1_lqfg3jy2_.arc
/u01/app/oracle/fast_recovery_area/PRODCDB/archivelog/2023_12_11/o1_mf_1_2_lqfk3vdt_.arc
/u01/app/oracle/fast_recovery_area/PRODCDB/archivelog/2023_12_11/o1_mf_1_3_lqfk46h8_.arc
/u01/app/oracle/fast_recovery_area/PRODCDB/archivelog/2023_12_11/o1_mf_1_4_lqfkf3vq_.arc
/u01/app/oracle/fast_recovery_area/PRODCDB/archivelog/2023_12_11/o1_mf_1_5_lqflqb49_.arc

12 rows selected.

将 5号归档进行重命名，使其不连续（该归档保存着 4，5，6）

SYS@PRODCDB>!mv  /u01/app/oracle/fast_recovery_area/PRODCDB/archivelog/2023_12_11/o1_mf_1_5_lqflqb49_.arc /u01/app/oracle/fast_recovery_area/PRODCDB/archivelog/2023_12_11/o1_mf_1_5_lqflqb49_.arc.bak

3.5 重启数据库并进行还原操作

SYS@PRODCDB> shutdown immediate;
ORA-01116: error in opening database file 1
ORA-01110: data file 1: '/u01/app/oracle/oradata/PRODCDB/system01.dbf'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
SYS@PRODCDB> shut abort;
ORACLE instance shut down.
SYS@PRODCDB> startup force;
ORACLE instance started.

Total System Global Area  838860800 bytes
Fixed Size            2929936 bytes
Variable Size          570428144 bytes
Database Buffers      260046848 bytes
Redo Buffers            5455872 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 1 - see DBWR trace file
ORA-01110: data file 1: '/u01/app/oracle/oradata/PRODCDB/system01.dbf'

通过 RMAN 进行还原

[oracle@host01 2023_12_11]$ rman target /

Recovery Manager: Release 12.1.0.2.0 - Production on Mon Dec 11 16:59:22 2023

Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.

connected to target database: PRODCDB (DBID=2891862819, not open)

RMAN> restore database from tag='full';

Starting restore at 11-DEC-23
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=240 device type=DISK

skipping datafile 10; already restored to file /u01/app/oracle/oradata/PRODCDB/PDBPROD2/system01.dbf
skipping datafile 11; already restored to file /u01/app/oracle/oradata/PRODCDB/PDBPROD2/sysaux01.dbf
skipping datafile 12; already restored to file /u01/app/oracle/oradata/PRODCDB/PDBPROD2/PDBPROD2_users01.dbf
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to /u01/app/oracle/oradata/PRODCDB/system01.dbf
channel ORA_DISK_1: restoring datafile 00003 to /u01/app/oracle/oradata/PRODCDB/sysaux01.dbf
channel ORA_DISK_1: restoring datafile 00005 to /u01/app/oracle/oradata/PRODCDB/undotbs01.dbf
channel ORA_DISK_1: restoring datafile 00006 to /u01/app/oracle/oradata/PRODCDB/users01.dbf
channel ORA_DISK_1: reading from backup piece /u02/backup/PRODCDB_9_092dp9qv_1_1.full
channel ORA_DISK_1: piece handle=/u02/backup/PRODCDB_9_092dp9qv_1_1.full tag=FULL
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:01:16
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00007 to /u01/app/oracle/oradata/PRODCDB/PDBPROD1/system01.dbf
channel ORA_DISK_1: restoring datafile 00008 to /u01/app/oracle/oradata/PRODCDB/PDBPROD1/sysaux01.dbf
channel ORA_DISK_1: restoring datafile 00009 to /u01/app/oracle/oradata/PRODCDB/PDBPROD1/PDBPROD1_users01.dbf
channel ORA_DISK_1: restoring datafile 00013 to /u01/app/oracle/oradata/PRODCDB/PDBPROD1/example01.dbf
channel ORA_DISK_1: reading from backup piece /u02/backup/PRODCDB_10_0a2dp9vh_1_1.full
channel ORA_DISK_1: piece handle=/u02/backup/PRODCDB_10_0a2dp9vh_1_1.full tag=FULL
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:45
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00002 to /u01/app/oracle/oradata/PRODCDB/pdbseed/system01.dbf
channel ORA_DISK_1: restoring datafile 00004 to /u01/app/oracle/oradata/PRODCDB/pdbseed/sysaux01.dbf
channel ORA_DISK_1: reading from backup piece /u02/backup/PRODCDB_12_0c2dpa2b_1_1.full
channel ORA_DISK_1: piece handle=/u02/backup/PRODCDB_12_0c2dpa2b_1_1.full tag=FULL
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:35
Finished restore at 11-DEC-23

restore

3.6 恢复数据库

SYS@PRODCDB> recover database until cancel;
ORA-00279: change 2407357 generated at 12/11/2023 15:50:55 needed for thread 1
ORA-00289: suggestion :
/u01/app/oracle/fast_recovery_area/PRODCDB/archivelog/2023_12_11/o1_mf_1_2_lqfk3
vdt_.arc
ORA-00280: change 2407357 for thread 1 is in sequence #2


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

程序建议让我们应用 2 号归档：/u01/app/oracle/fast_recovery_area/PRODCDB/archivelog/2023_12_11/o1_mf_1_2_lqfk3vdt_.arc

这里有三个选项：filename AUTO CANCEL

filename 可以进行手工指定归档日志文件名，主动进行，可以随时停止进行 CANCEL（如果最后指定的归档不存在，则报错，在线日志完整情况下，应该尝试手工输入在线日志名称，以达到完全恢复的效果）

AUTO 指定 AUTO 关键字后，会自动应用归档日志，直到最后一个可用归档（如果最后指定的归档不存在，则报错，在线日志完整情况下，应该尝试手工输入在线日志名称，以达到完全恢复的效果）

CANCEL 指定该关键字后，取消当前恢复（这个取消不是回滚所有操作的意思，而是当前取消，恢复到当前这个点）

注意：我们之前将归档 5 进行更名，故意让归档日志不连续。归档 5 里面保存着（4，5，6）

手工指定（filename）建议归档名称

SYS@PRODCDB> recover database until cancel;
ORA-00279: change 2407357 generated at 12/11/2023 15:50:55 needed for thread 1
ORA-00289: suggestion :
/u01/app/oracle/fast_recovery_area/PRODCDB/archivelog/2023_12_11/o1_mf_1_2_lqfk3
vdt_.arc
ORA-00280: change 2407357 for thread 1 is in sequence #2

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/u01/app/oracle/fast_recovery_area/PRODCDB/archivelog/2023_12_11/o1_mf_1_2_lqfk3vdt_.arc
ORA-00279: change 2409039 generated at 12/11/2023 16:12:43 needed for thread 1
ORA-00289: suggestion :
/u01/app/oracle/fast_recovery_area/PRODCDB/archivelog/2023_12_11/o1_mf_1_3_lqfk4
6h8_.arc
ORA-00280: change 2409039 for thread 1 is in sequence #3
ORA-00278: log file
'/u01/app/oracle/fast_recovery_area/PRODCDB/archivelog/2023_12_11/o1_mf_1_2_lqfk
3vdt_.arc' no longer needed for this recovery


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/u01/app/oracle/fast_recovery_area/PRODCDB/archivelog/2023_12_11/o1_mf_1_3_lqfk46h8_.arc
ORA-00279: change 2409044 generated at 12/11/2023 16:12:54 needed for thread 1
ORA-00289: suggestion :
/u01/app/oracle/fast_recovery_area/PRODCDB/archivelog/2023_12_11/o1_mf_1_4_lqfkf
3vq_.arc
ORA-00280: change 2409044 for thread 1 is in sequence #4
ORA-00278: log file
'/u01/app/oracle/fast_recovery_area/PRODCDB/archivelog/2023_12_11/o1_mf_1_3_lqfk
46h8_.arc' no longer needed for this recovery


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/u01/app/oracle/fast_recovery_area/PRODCDB/archivelog/2023_12_11/o1_mf_1_4_lqfkf3vq_.arc
ORA-00279: change 2409190 generated at 12/11/2023 16:17:39 needed for thread 1
ORA-00289: suggestion :
/u01/app/oracle/fast_recovery_area/PRODCDB/archivelog/2023_12_11/o1_mf_1_5_lqflq
b49_.arc
ORA-00280: change 2409190 for thread 1 is in sequence #5
ORA-00278: log file
'/u01/app/oracle/fast_recovery_area/PRODCDB/archivelog/2023_12_11/o1_mf_1_4_lqfk
f3vq_.arc' no longer needed for this recovery

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

这时，4 号归档恢复完毕，不再需要，建议给出继续应用 5 号归档：/u01/app/oracle/fast_recovery_area/PRODCDB/archivelog/2023_12_11/o1_mf_1_5_lqflq/b49_.arc

可是我们当前系统没有这个归档（被重命名），意味着归档无法继续前滚，因此数据将丢失，这时候输入 CANCEL 取消恢复即可。

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/u01/app/oracle/fast_recovery_area/PRODCDB/archivelog/2023_12_11/o1_mf_1_4_lqfkf3vq_.arc
ORA-00279: change 2409190 generated at 12/11/2023 16:17:39 needed for thread 1
ORA-00289: suggestion :
/u01/app/oracle/fast_recovery_area/PRODCDB/archivelog/2023_12_11/o1_mf_1_5_lqflq
b49_.arc
ORA-00280: change 2409190 for thread 1 is in sequence #5
ORA-00278: log file
'/u01/app/oracle/fast_recovery_area/PRODCDB/archivelog/2023_12_11/o1_mf_1_4_lqfk
f3vq_.arc' no longer needed for this recovery


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
CANCEL
Media recovery cancelled.
SYS@PRODCDB>

完成恢复后，表中数据只有（1，2，3），（4，5，6）归档被重命名，因此无法应用，所以数据丢失，包括在线日志里面的（7，8，9）

3.7 打开数据库

因着执行了不完全恢复（即使某种情况下通过不完全恢复语句完成了数据的完全恢复），必须以 resetlogs 打开数据库。重置日志组 sequence 号，从 1 开始，新化身出现。

SYS@PRODCDB> show pdbs;

    CON_ID CON_NAME              OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
     2 PDB$SEED              MOUNTED
     3 PDBPROD1              MOUNTED
     4 PDBPROD2              MOUNTED
SYS@PRODCDB> alter database open resetlogs;

Database altered.

3.8 验证数据

SYS@PRODCDB> show pdbs;

    CON_ID CON_NAME              OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
     2 PDB$SEED              MOUNTED
     3 PDBPROD1              MOUNTED
     4 PDBPROD2              MOUNTED
SYS@PRODCDB> alter database open resetlogs;

Database altered.

SYS@PRODCDB> alter session set container=pdbprod1;

Session altered.


SYS@PRODCDB> show pdbs;

    CON_ID CON_NAME              OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
     3 PDBPROD1              MOUNTED
SYS@PRODCDB> alter database open;

Database altered.

SYS@PRODCDB> select * from test02;

    ID
----------
     1
     2
     3