Oracle 21c 新特性 PDB 时间点恢复或闪回到最近的任何时间




  • 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.

     

    连接到 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

     

    第 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

    可能的 ORPHAN 化身将来自以前的 PDB 重置日志。

     

    显示 HR.EMPLOYEES 表中的行数。

    SQL> SELECT count(*) FROM hr.employees;

    COUNT(*)
    ----------
    107

     

    用户意外删除了 PDB21 中的 HR.EMPLOYEES 表。

    SQL> DROP TABLE hr.employees CASCADE CONSTRAINTS;

    Table dropped.

     

    第 3 步:恢复表 闪回 PDB 以恢复删除的表。 确保 PDB21 已关闭。 其他 PDB 可以是开放的和可操作的。

    SQL> ALTER PLUGGABLE DATABASE CLOSE;

    Pluggable database altered.

    将数据闪回到删除表之前的点。 如果闪回操作是到指定时间或还原点,则无需设置孤立 PDB 化身。 确定 FLASHBACK DATABASE 命令所需的 SCN 或时间点。 该点必须在当前 CDB 化身或祖先 CDB 化身内。

    SQL> FLASHBACK PLUGGABLE DATABASE TO SCN 7139065;

    Flashback complete.

    使用 RESETLOGS 打开 PDB21。

    SQL> ALTER PLUGGABLE DATABASE OPEN RESETLOGS;

    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

    第 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

    两分钟后,删除员工 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

    第 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

    用户要求重置 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

    此命令仅存在于 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

     

    这个错误是什么意思?

    $ 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.

    当用户增加员工的工资时,使用步骤结束时显示的 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.

    打开 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

    Speak Your Mind

    *

    京ICP备14059771号-2