PDB 可以恢复为同一 CDB 化身或祖先化身中的孤立 PDB 化身。
PDB 的可用性得到增强。 将 PDB 恢复为孤立 PDB 化身时，支持闪回和时间点恢复操作。
介绍PDB 时间点恢复或闪回到最近过去的任何时间
此页面提供有关 PDB PITR 或闪回最近过去任何时间的更多详细信息。
Oracle 数据库 21c 允许可插入数据库的时间点恢复或闪回到位于孤立 PDB 分支上的时间点。 这使您可以在一定天数内将数据库恢复到任何时间，从而及时回退数据以纠正由逻辑数据损坏或用户错误引起的任何问题。
1.只要有足够的重做和闪回数据并且没有 CDB 重置日志，就允许 PDB PITR/闪回到任何时间
2.跨多个 DB 化身执行 PDB 时间点恢复/闪回到孤立 PDB 化身上的 PDB 还原点：用户可以将可插入数据库 PITR/闪回到当前数据库化身以外的不同数据库化身上的任何点，如 只要数据库化身在当前数据库祖先路径上并且存在足够的重做/闪回数据。 Oracle 不支持 PDB PITR/闪回到孤立数据库化身的任何点。 原因是用户应该能够在恢复任何备份后通过一次介质恢复来恢复 CDB。
3.允许 DBA 在 PDB PITR/闪回到 SCN 之前发出新的 RMAN 命令来设置 PDB 化身
对特定 PDB 执行闪回操作只会修改该 PDB 的数据文件。 CDB 中的其余 PDB 不受影响。 必须使用特定时间、SCN、CDB 还原点、PDB 还原点、PDB 干净还原点或 PDB 保证还原点来指定 PDB 必须闪回的时间点。
实践：将 PDB 闪回到最近的任何时间
概述 此实践展示了如何在孤立 PDB 实例上执行 PDB PITR/闪回到特定时间，然后执行 PDB PITR/闪回到 PDB 时间。
在开始任何新的实践之前，请参阅实践环境建议。
第 1 步：设置环境 shell 脚本在 CDB 中启用闪回，创建 PDB21 并在 PDB21 中创建 HR 。
$ cd /home/oracle/labs/M104782GC10
$ /home/oracle/labs/M104782GC10/setup_Flashback.sh
...
SQL> ALTER DATABASE FLASHBACK on;
Database altered.
...
SQL> exit
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Connected to:
specify password for HR as parameter 1:
specify default tablespeace for HR as parameter 2:
specify temporary tablespace for HR as parameter 3:
specify log path as parameter 4:
PL/SQL procedure successfully completed.
User created.
ALTER USER hr DEFAULT TABLESPACE tbs_for_users
...
Commit complete.
PL/SQL procedure successfully completed.
$ /home/oracle/labs/M104782GC10/setup_Flashback.sh
...
SQL> ALTER DATABASE FLASHBACK on;
Database altered.
...
SQL> exit
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Connected to:
specify password for HR as parameter 1:
specify default tablespeace for HR as parameter 2:
specify temporary tablespace for HR as parameter 3:
specify log path as parameter 4:
PL/SQL procedure successfully completed.
User created.
ALTER USER hr DEFAULT TABLESPACE tbs_for_users
...
Commit complete.
PL/SQL procedure successfully completed.
连接到 CDB 根并检查 CDB 是否已打开并启用闪回。
$ sqlplus / AS SYSDBA
Copyright (c) 1982, 2020, Oracle. All rights reserved.
Connected to:
SQL> SELECT open_mode, flashback_on FROM v$database;
OPEN_MODE FLASHBACK_ON
-------------------- ------------------
READ WRITE YES
Copyright (c) 1982, 2020, Oracle. All rights reserved.
Connected to:
SQL> SELECT open_mode, flashback_on FROM v$database;
OPEN_MODE FLASHBACK_ON
-------------------- ------------------
READ WRITE YES
第 2 步：在表上生成错误 在 PDB21 中的 HR.EMPLOYEES 表上执行任何 DDL 或 DML 命令之前，显示当前 SCN、其关联的时间戳和 PDB 的化身。
SQL> CONNECT sys@PDB21 AS SYSDBA
Enter password:
Connected.
SQL> COL TIMESTAMP FORMAT A40
SQL> SELECT CURRENT_SCN, SCN_TO_TIMESTAMP(CURRENT_SCN) "TIMESTAMP" from V$DATABASE;
CURRENT_SCN TIMESTAMP
----------- ----------------------------------------
7139065 16-DEC-20 11.20.27.000000000 AM
SQL> SELECT con_id, status, pdb_incarnation# inc#, begin_resetlogs_scn, end_resetlogs_scn
FROM v$pdb_incarnation ORDER BY 3;
CON_ID STATUS INC# BEGIN_RESETLOGS_SCN END_RESETLOGS_SCN
---------- ------- ---------- ------------------- -----------------
6 PARENT 0 2621787 2621787
6 CURRENT 0 4691372 4691372
Enter password:
Connected.
SQL> COL TIMESTAMP FORMAT A40
SQL> SELECT CURRENT_SCN, SCN_TO_TIMESTAMP(CURRENT_SCN) "TIMESTAMP" from V$DATABASE;
CURRENT_SCN TIMESTAMP
----------- ----------------------------------------
7139065 16-DEC-20 11.20.27.000000000 AM
SQL> SELECT con_id, status, pdb_incarnation# inc#, begin_resetlogs_scn, end_resetlogs_scn
FROM v$pdb_incarnation ORDER BY 3;
CON_ID STATUS INC# BEGIN_RESETLOGS_SCN END_RESETLOGS_SCN
---------- ------- ---------- ------------------- -----------------
6 PARENT 0 2621787 2621787
6 CURRENT 0 4691372 4691372
可能的 ORPHAN 化身将来自以前的 PDB 重置日志。
显示 HR.EMPLOYEES 表中的行数。
SQL> SELECT count(*) FROM hr.employees;
COUNT(*)
----------
107
COUNT(*)
----------
107
用户意外删除了 PDB21 中的 HR.EMPLOYEES 表。
SQL> DROP TABLE hr.employees CASCADE CONSTRAINTS;
Table dropped.
Table dropped.
第 3 步：恢复表 闪回 PDB 以恢复删除的表。 确保 PDB21 已关闭。 其他 PDB 可以是开放的和可操作的。
SQL> ALTER PLUGGABLE DATABASE CLOSE;
Pluggable database altered.
Pluggable database altered.
将数据闪回到删除表之前的点。 如果闪回操作是到指定时间或还原点，则无需设置孤立 PDB 化身。 确定 FLASHBACK DATABASE 命令所需的 SCN 或时间点。 该点必须在当前 CDB 化身或祖先 CDB 化身内。
SQL> FLASHBACK PLUGGABLE DATABASE TO SCN 7139065;
Flashback complete.
Flashback complete.
使用 RESETLOGS 打开 PDB21。
SQL> ALTER PLUGGABLE DATABASE OPEN RESETLOGS;
Pluggable database altered.
SQL> SELECT count(*) FROM hr.employees;
COUNT(*)
----------
107
Pluggable database altered.
SQL> SELECT count(*) FROM hr.employees;
COUNT(*)
----------
107
显示 PDB21 的化身。
SQL> SELECT con_id, pdb_incarnation# INC#, status, incarnation_scn, end_resetlogs_scn
FROM v$pdb_incarnation ORDER BY 2;
CON_ID INC# STATUS INCARNATION_SCN END_RESETLOGS_SCN
---------- ---------- ------- --------------- -----------------
6 0 PARENT 4691372 4691372
6 4 CURRENT 7139066 7139993
FROM v$pdb_incarnation ORDER BY 2;
CON_ID INC# STATUS INCARNATION_SCN END_RESETLOGS_SCN
---------- ---------- ------- --------------- -----------------
6 0 PARENT 4691372 4691372
6 4 CURRENT 7139066 7139993
第 4 步：在表上生成第二个错误 为部分员工将 HR.EMPLOYEES 中员工的工资提高 2 倍。
SQL> SELECT min(salary), MAX(salary) FROM hr.employees;
MIN(SALARY) MAX(SALARY)
----------- -----------
2100 24000
SQL> UPDATE hr.employees SET salary=salary*2 WHERE employee_id<200;
100 rows updated.
SQL> COMMIT;
Commit complete.
SQL> SELECT CURRENT_SCN, SCN_TO_TIMESTAMP(CURRENT_SCN) "TIMESTAMP" from V$DATABASE;
CURRENT_SCN TIMESTAMP
----------- ----------------------------------------
7140367 16-DEC-20 11.49.34.000000000 AM
MIN(SALARY) MAX(SALARY)
----------- -----------
2100 24000
SQL> UPDATE hr.employees SET salary=salary*2 WHERE employee_id<200;
100 rows updated.
SQL> COMMIT;
Commit complete.
SQL> SELECT CURRENT_SCN, SCN_TO_TIMESTAMP(CURRENT_SCN) "TIMESTAMP" from V$DATABASE;
CURRENT_SCN TIMESTAMP
----------- ----------------------------------------
7140367 16-DEC-20 11.49.34.000000000 AM
两分钟后，删除员工 206。
SQL> DELETE FROM hr.employees WHERE employee_id=206;
1 rows deleted.
SQL> COMMIT;
Commit complete.
SQL> SELECT count(*) FROM hr.employees;
COUNT(*)
----------
106
SQL> SELECT CURRENT_SCN, SCN_TO_TIMESTAMP(CURRENT_SCN) "TIMESTAMP" from V$DATABASE;
CURRENT_SCN TIMESTAMP
----------- ----------------------------------------
7140382 16-DEC-20 11.50.09.000000000 AM
SQL> SELECT con_id, pdb_incarnation# INC#, status, incarnation_scn, end_resetlogs_scn
FROM v$pdb_incarnation ORDER BY 2;
CON_ID INC# STATUS INCARNATION_SCN END_RESETLOGS_SCN
---------- ---------- ------- --------------- -----------------
6 0 PARENT 4691372 4691372
6 4 CURRENT 7139066 7139993
1 rows deleted.
SQL> COMMIT;
Commit complete.
SQL> SELECT count(*) FROM hr.employees;
COUNT(*)
----------
106
SQL> SELECT CURRENT_SCN, SCN_TO_TIMESTAMP(CURRENT_SCN) "TIMESTAMP" from V$DATABASE;
CURRENT_SCN TIMESTAMP
----------- ----------------------------------------
7140382 16-DEC-20 11.50.09.000000000 AM
SQL> SELECT con_id, pdb_incarnation# INC#, status, incarnation_scn, end_resetlogs_scn
FROM v$pdb_incarnation ORDER BY 2;
CON_ID INC# STATUS INCARNATION_SCN END_RESETLOGS_SCN
---------- ---------- ------- --------------- -----------------
6 0 PARENT 4691372 4691372
6 4 CURRENT 7139066 7139993
第 5 步：将表恢复到删除表之前的位置 您决定将数据闪回到删除表之前的位置。
SQL> ALTER PLUGGABLE DATABASE CLOSE;
Pluggable database altered.
SQL> FLASHBACK PLUGGABLE DATABASE TO SCN 7139065;
Flashback complete.
SQL> ALTER PLUGGABLE DATABASE OPEN RESETLOGS;
Pluggable database altered.
SQL> SELECT count(*) FROM hr.employees;
COUNT(*)
----------
107
SQL> SELECT min(salary), MAX(salary) FROM hr.employees;
MIN(SALARY) MAX(SALARY)
----------- -----------
2100 24000
SQL> SELECT con_id, pdb_incarnation# INC#, status, incarnation_scn, end_resetlogs_scn
FROM v$pdb_incarnation ORDER BY 2;
2
CON_ID INC# STATUS INCARNATION_SCN END_RESETLOGS_SCN
---------- ---------- ------- --------------- -----------------
6 0 PARENT 4691372 4691372
6 4 ORPHAN 7139066 7139993
6 5 CURRENT 7139066 7140876
Pluggable database altered.
SQL> FLASHBACK PLUGGABLE DATABASE TO SCN 7139065;
Flashback complete.
SQL> ALTER PLUGGABLE DATABASE OPEN RESETLOGS;
Pluggable database altered.
SQL> SELECT count(*) FROM hr.employees;
COUNT(*)
----------
107
SQL> SELECT min(salary), MAX(salary) FROM hr.employees;
MIN(SALARY) MAX(SALARY)
----------- -----------
2100 24000
SQL> SELECT con_id, pdb_incarnation# INC#, status, incarnation_scn, end_resetlogs_scn
FROM v$pdb_incarnation ORDER BY 2;
2
CON_ID INC# STATUS INCARNATION_SCN END_RESETLOGS_SCN
---------- ---------- ------- --------------- -----------------
6 0 PARENT 4691372 4691372
6 4 ORPHAN 7139066 7139993
6 5 CURRENT 7139066 7140876
用户要求重置 PDB21，因为它是在工资更新之后和员工 206 被删除之前。 pdb21 的这种状态属于 PDB21 的化身 1。 设置必须执行闪回 PDB 操作的孤立 PDB 化身。 此步骤是必需的，因为闪回操作针对的是 SCN 或孤立 PDB 化身中的特定时间。
SQL> RESET PLUGGABLE DATABASE TO INCARNATION 4;
SP2-0734: unknown command beginning "RESET PLUG..." - rest of line ignored.
SQL> EXIT
SP2-0734: unknown command beginning "RESET PLUG..." - rest of line ignored.
SQL> EXIT
此命令仅存在于 RMAN 中
$ rman TARGET sys@PDB21
target database Password: password
connected to target database: CDB21:PDB21 (DBID=2289122758)
RMAN> LIST INCARNATION OF PLUGGABLE DATABASE pdb21;
using target database control file instead of recovery catalog
List of Pluggable Database Incarnations
DB Key PDB Key PDBInc Key DBInc Key PDB Name Status Inc SCN Inc Time Begin Reset SCN Begin Reset Time
------- ------- -------- --------- ------- -------- --------------- ------------------ --------------- ------------------
2 6 5 2 PDB21 CURRENT 7139066 16-DEC-20 7140876 16-DEC-20
End Reset SCN:7140876 End Reset Time:16-DEC-20 Guid:B693F93690D2CB3BE0530200000A6B6F
2 6 4 2 PDB21 ORPHAN 7139066 16-DEC-20 7139993 16-DEC-20
End Reset SCN:7139993 End Reset Time:16-DEC-20 Guid:B693F93690D2CB3BE0530200000A6B6F
2 6 0 2 PDB21 PARENT 4691372 10-DEC-20 4691372 10-DEC-20
End Reset SCN:4691372 End Reset Time:10-DEC-20 Guid:B693F93690D2CB3BE0530200000A6B6F
RMAN> RESET PLUGGABLE DATABASE pdb21 TO INCARNATION 1;
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of reset database command at 03/13/2020 07:28:33
RMAN-05625: command not allowed when connected to a pluggable database
RMAN> exit
Recovery Manager complete.
$
$ rman TARGET /
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
connected to target database: CDB21 (DBID=2732805675)
RMAN> ALTER PLUGGABLE DATABASE pdb21 CLOSE;
using target database control file instead of recovery catalog
Statement processed
RMAN> RESET PLUGGABLE DATABASE pdb21 TO INCARNATION 4;
using target database control file instead of recovery catalog
pluggable database reset to incarnation 4
RMAN> FLASHBACK PLUGGABLE DATABASE pdb21 TO SCN 7139066;
Starting flashback at 13-JAN-20
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=148 device type=DISK
starting media recovery
media recovery failed
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of flashback command at 12/16/2020 11:55:08
ORA-39889: Specified System Change Number (SCN) or timestamp is in the middle of a previous PDB RESETLOGS operation.
RMAN> exit
target database Password: password
connected to target database: CDB21:PDB21 (DBID=2289122758)
RMAN> LIST INCARNATION OF PLUGGABLE DATABASE pdb21;
using target database control file instead of recovery catalog
List of Pluggable Database Incarnations
DB Key PDB Key PDBInc Key DBInc Key PDB Name Status Inc SCN Inc Time Begin Reset SCN Begin Reset Time
------- ------- -------- --------- ------- -------- --------------- ------------------ --------------- ------------------
2 6 5 2 PDB21 CURRENT 7139066 16-DEC-20 7140876 16-DEC-20
End Reset SCN:7140876 End Reset Time:16-DEC-20 Guid:B693F93690D2CB3BE0530200000A6B6F
2 6 4 2 PDB21 ORPHAN 7139066 16-DEC-20 7139993 16-DEC-20
End Reset SCN:7139993 End Reset Time:16-DEC-20 Guid:B693F93690D2CB3BE0530200000A6B6F
2 6 0 2 PDB21 PARENT 4691372 10-DEC-20 4691372 10-DEC-20
End Reset SCN:4691372 End Reset Time:10-DEC-20 Guid:B693F93690D2CB3BE0530200000A6B6F
RMAN> RESET PLUGGABLE DATABASE pdb21 TO INCARNATION 1;
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of reset database command at 03/13/2020 07:28:33
RMAN-05625: command not allowed when connected to a pluggable database
RMAN> exit
Recovery Manager complete.
$
$ rman TARGET /
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
connected to target database: CDB21 (DBID=2732805675)
RMAN> ALTER PLUGGABLE DATABASE pdb21 CLOSE;
using target database control file instead of recovery catalog
Statement processed
RMAN> RESET PLUGGABLE DATABASE pdb21 TO INCARNATION 4;
using target database control file instead of recovery catalog
pluggable database reset to incarnation 4
RMAN> FLASHBACK PLUGGABLE DATABASE pdb21 TO SCN 7139066;
Starting flashback at 13-JAN-20
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=148 device type=DISK
starting media recovery
media recovery failed
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of flashback command at 12/16/2020 11:55:08
ORA-39889: Specified System Change Number (SCN) or timestamp is in the middle of a previous PDB RESETLOGS operation.
RMAN> exit
这个错误是什么意思？
$ oerr ora 39889
39889, 00000, "Specified System Change Number (SCN) or timestamp is in the middle of a previous PDB RESETLOGS operation."
// *Cause: The specified System Change Number (SCN) or timestamp was in the
// middle of a previous PDB RESETLOGS operation. More specifically,
// each PDB RESETLOGS operation may create a PDB incarnation as shown
// in v$pdb_incarnation. Any SCN between INCARNATION_SCN and
// END_RESETLOGS_SCN or any timestamp between INCARNATION_TIME and
// END_RESETLOGS_TIME as shown in v$pdb_incarnation is considered in
// the middle of the PDB RESETLOGS operation.
// *Action: Flashback the PDB to an SCN or timestamp that is not in the middle
// of a previous PDB RESETLOGS operation. If flashback to a SCN on the
// orphan PDB incarnation is required, then use
// "RESET PLUGGABLE DATABASE TO INCARNATION" RMAN command to specify
// the pluggable database incarnation along which flashback to the
// specified SCN must be performed. Also, ensure that the feature is
// enabled.
39889, 00000, "Specified System Change Number (SCN) or timestamp is in the middle of a previous PDB RESETLOGS operation."
// *Cause: The specified System Change Number (SCN) or timestamp was in the
// middle of a previous PDB RESETLOGS operation. More specifically,
// each PDB RESETLOGS operation may create a PDB incarnation as shown
// in v$pdb_incarnation. Any SCN between INCARNATION_SCN and
// END_RESETLOGS_SCN or any timestamp between INCARNATION_TIME and
// END_RESETLOGS_TIME as shown in v$pdb_incarnation is considered in
// the middle of the PDB RESETLOGS operation.
// *Action: Flashback the PDB to an SCN or timestamp that is not in the middle
// of a previous PDB RESETLOGS operation. If flashback to a SCN on the
// orphan PDB incarnation is required, then use
// "RESET PLUGGABLE DATABASE TO INCARNATION" RMAN command to specify
// the pluggable database incarnation along which flashback to the
// specified SCN must be performed. Also, ensure that the feature is
// enabled.
当用户增加员工的工资时，使用步骤结束时显示的 SCN。
$ rman TARGET /
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
connected to target database: CDB21 (DBID=2732805675)
RMAN> RESET PLUGGABLE DATABASE pdb21 TO INCARNATION 4;
using target database control file instead of recovery catalog
pluggable database reset to incarnation 4
RMAN> FLASHBACK PLUGGABLE DATABASE pdb21 TO SCN 7140367;
Starting flashback at 16-DEC-20
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=434 device type=DISK
starting media recovery
media recovery complete, elapsed time: 00:00:07
Finished flashback at 16-DEC-20
RMAN> EXIT
Recovery Manager complete.
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
connected to target database: CDB21 (DBID=2732805675)
RMAN> RESET PLUGGABLE DATABASE pdb21 TO INCARNATION 4;
using target database control file instead of recovery catalog
pluggable database reset to incarnation 4
RMAN> FLASHBACK PLUGGABLE DATABASE pdb21 TO SCN 7140367;
Starting flashback at 16-DEC-20
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=434 device type=DISK
starting media recovery
media recovery complete, elapsed time: 00:00:07
Finished flashback at 16-DEC-20
RMAN> EXIT
Recovery Manager complete.
打开 PDB 并验证数据是否在员工工资更新和员工 206 的情况下恢复。
$ sqlplus sys@PDB21 AS SYSDBA
Enter password: password
Connected to:
SQL> ALTER PLUGGABLE DATABASE pdb21 OPEN RESETLOGS;
Pluggable database altered.
SQL> CONNECT system@PDB21
Enter password:
Connected.
SQL> SELECT count(*) FROM hr.employees;
COUNT(*)
----------
107
SQL> SELECT min(salary), MAX(salary) FROM hr.employees;
MIN(SALARY) MAX(SALARY)
----------- -----------
4200 48000
SQL> SELECT con_id, pdb_incarnation# INC#, status, incarnation_scn, end_resetlogs_scn
FROM v$pdb_incarnation ORDER BY 2;
CON_ID INC# STATUS INCARNATION_SCN END_RESETLOGS_SCN
---------- ---------- ------- --------------- -----------------
6 0 PARENT 4691372 4691372
6 4 PARENT 7139066 7139993
6 5 ORPHAN 7139066 7140876
6 6 CURRENT 7140368 7142535
SQL> EXIT
Enter password: password
Connected to:
SQL> ALTER PLUGGABLE DATABASE pdb21 OPEN RESETLOGS;
Pluggable database altered.
SQL> CONNECT system@PDB21
Enter password:
Connected.
SQL> SELECT count(*) FROM hr.employees;
COUNT(*)
----------
107
SQL> SELECT min(salary), MAX(salary) FROM hr.employees;
MIN(SALARY) MAX(SALARY)
----------- -----------
4200 48000
SQL> SELECT con_id, pdb_incarnation# INC#, status, incarnation_scn, end_resetlogs_scn
FROM v$pdb_incarnation ORDER BY 2;
CON_ID INC# STATUS INCARNATION_SCN END_RESETLOGS_SCN
---------- ---------- ------- --------------- -----------------
6 0 PARENT 4691372 4691372
6 4 PARENT 7139066 7139993
6 5 ORPHAN 7139066 7140876
6 6 CURRENT 7140368 7142535
SQL> EXIT
Speak Your Mind