PDB Recovery Isolation

By -

 In Oracle Database version 19c, Data Guard cannot manage operations such as hot cloning or point-in-time recovery (PITR) at the PDB level. If such operations are performed on the primary database, Data Guard will ignore that PDB without exiting the recovery mode and continue applying redo for the remaining PDBs.

–Data Guard 19c

SQL> select OPEN_MODE from v$database;
OPEN_MODE
——————–
READ ONLY WITH APPLY

SQL> show pdbs
    CON_ID CON_NAME                       OPEN MODE  RESTRICTED

———- —————————— ———- ———-
         2 PDB$SEED                       READ ONLY  NO
         3 PDB1                           READ ONLY  NO

–Primary 19c

SQL> create pluggable database pdb2  from pdb1;
Pluggable database created.

SQL> alter pluggable database pdb2 open;
Pluggable database altered.

SQL> show pdbs
    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
———- —————————— ———- ———-
         2 PDB$SEED                       READ ONLY  NO
         3 PDB1                           READ WRITE NO
         5 PDB2                           READ WRITE NO

–Data Guard 19c

SQL> select OPEN_MODE from v$database;
OPEN_MODE
——————–
READ ONLY WITH APPLY

SQL> alter pluggable database pdb2 open;
ORA-01111: name for data file 13 is unknown – rename to correct file

SQL> select name,status from v$datafile;
NAME                                STATUS
———————————– ——-
/oracle19c/home/dbs/UNNAMED00013    SYSOFF
/oracle19c/home/dbs/UNNAMED00014    RECOVER
/oracle19c/home/dbs/UNNAMED00015    RECOVER
/oracle19c/home/dbs/UNNAMED00016    RECOVER

Data files on the standby remain in SYSOFF or RECOVER status, showing that the new PDB is not properly synchronized.

In Oracle 21c, the PDB Recovery Isolation feature was introduced to solve this issue. With this enhancement, a background process is spawned that restores and recovers the newly cloned PDB (or the PDB that has undergone point-in-time recovery).

During this process, the CDB never leaves recovery mode, and for a short time, two types of Managed Recovery Processes (MRP) coexist:

  • One for the CDB-level recovery
  • Another for the specific PDB recovery

Once the PDB is fully synchronized with the CDB, the PDB-level MRP automatically stops, and the two MRPs merge into a single recovery stream.

– Data Guard 21c

SQL> select OPEN_MODE from v$database;
OPEN_MODE
——————–
READ ONLY WITH APPLY

SQL> show pdbs
    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
———- —————————— ———- ———-
         2 PDB$SEED                       READ ONLY  NO
         5 PDB1                           READ ONLY  NO

–Primary 21c

SQL> create pluggable database pdb2  from pdb1;
Pluggable database created.

SQL> alter pluggable database pdb2 open;
Pluggable database altered.

SQL> show pdbs
    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
———- —————————— ———- ———-
         2 PDB$SEED                       READ ONLY  NO
         3 PDB2                           MOUNTED
         5 PDB1                           READ ONLY  NO

SQL> alter pluggable database pdb2 open;
Pluggable database altered.

SQL> show pdbs
    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
———- —————————— ———- ———-
         2 PDB$SEED                       READ ONLY  NO
         4 PDB2                           READ WRITE NO
         5 PDB1                           READ WRITE NO

–Data Guard 21c

SQL> select name,CON_ID,RECOVERY_STATUS from v$pdbs where NAME=’PDB2′;
NAME           CON_ID RECOVERY_STATUS
———- ———- ————————–
PDB2                4 DISABLED AUTOMATIC RECOVER

SQL> !sleep 60

SQL> /
NAME           CON_ID RECOVERY_STATUS
———- ———- ————————–
PDB2                4 ENABLED

SQL> alter pluggable database pdb2 open;
Pluggable database altered.

SQL> show pdbs
    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
———- —————————— ———- ———-
         2 PDB$SEED                       READ ONLY  NO
         4 PDB2                           READ ONLY  NO
         5 PDB1                           READ ONLY  NO

Now PDB2 has been successfully restored and recovered on the standby.
A look at the alert log confirms the automated recovery steps and MRP handoff process.

PDB2(4):File #82 added to control file as ‘/oracle21cR3/base/oradata/ST21C/D10C10B001CB7C42E0530488200A6418/datafile/o1_mf_tbs2_jsddhzs7_.dbf’.

2021-11-18T05:59:07.490261-05:00

PDB2(4):Full restore complete of datafile 82 /oracle21cR3/base/oradata/ST21C/D10C10B001CB7C42E0530488200A6418/datafile/o1_mf_tbs2_jsddhv8o_.dbf.  Elapsed time: 0:00:00

PDB2(4):  checkpoint is 72901012

PDB2(4):  last deallocation scn is 2601845

PDB2(4):Datafile #82 has been copied to the standby.

PDB2(4):PDB Side Media Recovery started for pdbid(4)

PDB2(4):…. (PID:457352): Managed Recovery starting Real Time Apply [krsm.c:15865]

PDB2(4):max_pdb is 6

PDB2(4):…. (PID:457352): Media Recovery Waiting for T-1.S-524 (in transit) [krsm.c:6185]

PDB2(4):Media Recovery of Online Log [Thread=1, Seq=524]

PDB2(4):Recovery of Online Redo Log: Thread 1 Group 5 Seq 524 Reading mem 0

PDB2(4):  Mem# 0: /oracle21cR3/base/oradata/ST21C/onlinelog/o1_mf_5_jscwnzg2_.log

PDB2(4):  Mem# 1: /oracle21cR3/FRA/ST21C/onlinelog/o1_mf_5_jscwnznq_.log

PDB2(4):The merge request has been submitted by side recovery for pdbid 4

PR00 (PID:457274): Background Media Recovery cancelled with status 16037 [krd.c:27039]

PSR: Background Media Recovery will be reactivated in an attempt to take over PDB side recovery sessions

Errors in file /oracle21cR3/base/diag/rdbms/st21c/st21c/trace/st21c_pr00_457274.trc:

ORA-16037: user requested cancel of managed recovery operation

PR00 (PID:457274): Managed Recovery not using Real Time Apply [krsm.c:15876]

Recovery interrupted!

Recovered data files to a consistent state at change 72901067

Errors in file /oracle21cR3/base/diag/rdbms/st21c/st21c/trace/st21c_pr00_457274.trc:

ORA-16037: user requested cancel of managed recovery operation

PDB2(4):…. (PID:457352): Side Recovery Complete [krds.c:1584]

PR00 (PID:457979): Media Recovery Waiting for T-1.S-524 (in transit) [krsm.c:6185]

2021-11-18T05:59:42.565481-05:00

Recovery of Online Redo Log: Thread 1 Group 5 Seq 524 Reading mem 0

  Mem# 0: /oracle21cR3/base/oradata/ST21C/onlinelog/o1_mf_5_jscwnzg2_.log

  Mem# 1: /oracle21cR3/FRA/ST21C/onlinelog/o1_mf_5_jscwnznq_.log

Challenges

Challenge 1: Standby in MOUNT Mode

If the Data Guard standby is in MOUNT mode, the PDB Recovery Isolation process waits until the standby is opened. Once opened, it immediately starts the recovery operation for the affected PDB.

–Data Guard

SQL> startup mount force;

SQL> alter database recover managed standby database disconnect;

–Primary

SQL> create pluggable database pdb3  from pdb1;
Pluggable database created.

SQL> alter pluggable database pdb3 open;
Pluggable database altered.

–Data Guard

SQL> show pdbs
    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
———- —————————— ———- ———-
         2 PDB$SEED                       MOUNTED
         3 PDB3                           MOUNTED
         4 PDB2                           MOUNTED
         5 PDB1                           MOUNTED


SQL> select name,CON_ID,RECOVERY_STATUS from v$pdbs;
NAME           CON_ID RECOVERY_STATUS
———- ———- ————————–
PDB$SEED        2 ENABLED
PDB3            3 DISABLED AUTOMATIC RECOVER
PDB2            4 ENABLED
PDB1            5 ENABLED

After the standby is opened, AUTOMATIC RECOVER for PDB3 starts automatically, and within a short period, its RECOVERY_STATUS changes to ENABLED:

Become a member

–Data Guard

SQL> alter database recover managed standby database cancel;
Database altered.

SQL> alter database open;
Database altered.

SQL> alter database recover managed standby database disconnect;

SQL> select name,CON_ID,RECOVERY_STATUS from v$pdbs;
NAME           CON_ID RECOVERY_STATUS
———- ———- ————————–
PDB$SEED            2 ENABLED
PDB3                3 DISABLED AUTOMATIC RECOVER
PDB2                4 ENABLED
PDB1                5 ENABLED

SQL> /
NAME           CON_ID RECOVERY_STATUS
———- ———- ————————–
PDB$SEED            2 ENABLED
PDB3                3 DISABLED AUTOMATIC RECOVER
PDB2                4 ENABLED
PDB1                5 ENABLED

SQL> !sleep 60

SQL> /
NAME           CON_ID RECOVERY_STATUS
———- ———- ————————–
PDB$SEED            2 ENABLED
PDB3                3 ENABLED
PDB2                4 ENABLED
PDB1                5 ENABLED

Challenge 2: Standby Restart During Recovery

If the standby is restarted during a PDB Recovery Isolation operation, the process automatically resumes once Data Guard returns to recovery mode.
The datafiles are restored only once.

–Primary

SQL> create pluggable database pdb4 from pdb1;
Pluggable database created.

–Data Guard

SQL> startup force;

SQL> alter database recover managed standby database disconnect;

SQL> select name,CON_ID,RECOVERY_STATUS from v$pdbs where NAME=’PDB5′;
NAME           CON_ID RECOVERY_STATUS
———- ———- ————————–
PDB4                6 DISABLED AUTOMATIC RECOVER

SQL> !sleep 60

SQL> /
NAME           CON_ID RECOVERY_STATUS
———- ———- ————————–
PDB4                6 ENABLED

Challenge 3: Manually Controlling PDB Recovery Isolation

You can cancel an ongoing PDB Recovery Isolation process using:

–Primary

SQL> create pluggable database pdb5 from pdb1;

Pluggable database created.

–Data Guard

SQL> ALTER PLUGGABLE DATABASE PDB5 RECOVER MANAGED STANDBY DATABASE CANCEL;
Pluggable database altered.

SQL> select name,CON_ID,RECOVERY_STATUS from v$pdbs where NAME=’PDB5′;
NAME           CON_ID RECOVERY_STATUS
———- ———- ————————–
PDB5                7 DISABLED

To restart it manually:

–Data Guard

SQL> ALTER PLUGGABLE DATABASE PDB5 RECOVER MANAGED STANDBY DATABASE;
Pluggable database altered.

SQL> select name,CON_ID,RECOVERY_STATUS from v$pdbs where NAME=’PDB5′;
NAME           CON_ID RECOVERY_STATUS
———- ———- ————————–
PDB5                7 DISABLED AUTOMATIC RECOVER

SQL> !sleep 60

SQL> /
NAME           CON_ID RECOVERY_STATUS
———- ———- ————————–
PDB5                7 ENABLED

After about a minute, the RECOVERY_STATUS becomes ENABLED.

Challenge 4: Point-In-Time Recovery (PITR)

As mentioned earlier, in Oracle 21c, PITR at the PDB level is automatically handled by the PDB Recovery Isolation feature.
The following examples demonstrate the behavioral difference between Oracle 19c and Oracle 21c.

– Primary 19c

When performing PITR on a PDB, the Data Guard standby fails with errors such as:

– Primary 19c

[oracle@RAC2 ~]$ rman target /

Recovery Manager: Release 19.0.0.0.0 – Production on Thu Nov 18 16:42:34 2021

Version 19.3.0.0.0

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

connected to target database: DB21C (DBID=249297340)

RMAN>   RUN {
  ALTER PLUGGABLE DATABASE pdb1 CLOSE;
  SET UNTIL SCN 2518869;
  RESTORE PLUGGABLE DATABASE pdb1;
  RECOVER PLUGGABLE DATABASE pdb1;
  ALTER PLUGGABLE DATABASE pdb1 OPEN RESETLOGS;
}

SQL> show pdbs
    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
———- —————————— ———- ———-
         2 PDB$SEED                       READ ONLY  NO
         3 PDB1                           READ WRITE NO

–Data Guard_19c

SQL> alter database recover managed standby database;   
ORA-00283: recovery session canceled due to errors
ORA-65138: Data file 9 of pluggable database PDB1 belongs to an orphan PDB
incarnation.
ORA-01110: data file 9: ‘/oracle19c/base/oradata/DB21C/pdb1/system01.dbf’

– Primary 21c

In contrast, Oracle 21c automatically handles this recovery:

–Primary_21c

SQL> ALTER PLUGGABLE DATABASE pdb1 CLOSE immediate;

Pluggable database altered.

SQL> select name from v$datafile where con_id=3;

[oracle@oLinux7 ~]$ rm -rf /oracle21c/base/oradata/DB21C/D1111E988CA95792E0530488200A2486

–Data Guard_21c

SQL> ALTER PLUGGABLE DATABASE pdb1 CLOSE immediate;

SQL> select name from v$datafile where con_id=3;

[oracle@oLinux7 ~]$ rm -rf /oracle21c/base/oradata/DB21C/D1111E988CA95792E0530488200A2486

–Primary_21c

[oracle@oLinux7 ~]$ rman target /

Recovery Manager: Release 21.0.0.0.0 – Production on Thu Nov 18 08:31:59 2021
Version 21.3.0.0.0

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

connected to target database: DB21C (DBID=246486353)

RMAN> RUN {
  SET UNTIL SCN 72957710;
  RESTORE PLUGGABLE DATABASE pdb1;
  RECOVER PLUGGABLE DATABASE pdb1;
  ALTER PLUGGABLE DATABASE pdb1 OPEN RESETLOGS;
}

SQL> show pdbs
    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
———- —————————— ———- ———-
         2 PDB$SEED                       READ ONLY  NO
         3 PDB1                           READ WRITE NO

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
———- ——- ———- ——————- —————–
         3 PARENT           0            27261799          27261799
         3 CURRENT          2            72958406          72958406

–Data Guard_21c

SQL> select OPEN_MODE from v$database;
OPEN_MODE
——————–
READ ONLY WITH APPLY

SQL> alter pluggable database pdb1 open;
Pluggable database altered.

SQL> show pdbs
    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
———- —————————— ———- ———-
         2 PDB$SEED                       READ ONLY  NO
         3 PDB1                           READ ONLY  NO

SQL> alter session set container=pdb1;
Session altered.

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
———- ——- ———- ——————- —————–
         3 PARENT           0            27261799          27261799
         3 CURRENT          2            72958406          72958406
Vahid Yousefzadeh

Written by Vahid Yousefzadeh


I have been a DBA since 2011 and I work with Oracle technology. Linkdin: linkedin.com/in/vahidusefzadeh telegram channel ID:@oracledb vahidusefzadeh@gmail.com



Comments

Post a Comment

Popular posts from this blog

Oracle 21c Enhancements for TTS Export/Import

By -
  In Oracle version 21c, new features have been introduced for transportable tablespace (TTS) export/import. This article explores these capabilities in detail. Improvement 1: Parallel Execution of TTS Export/Import In Oracle 19c, TTS export/import operations could not be executed in parallel, as demonstrated by the following: Connected to: Oracle Database 19c Enterprise Edition Release 19.0 .0 .0 .0 – Production ORA-39002: invalid operation ORA-39047: Jobs of type TRANSPORTABLE cannot use multiple execution streams. Oracle addressed this limitation in version 21c, enabling parallel execution for TTS export/import: [oracle@oLinux7 ~]$ expdp directory=drm dumpfile=TTS_DUMP%U.dmp transport_tablespaces=tbs1,tbs2 transport_full_check= y parallel= 2 Export: Release 21.0 . 0 . 0 . 0 – Production on Tue Oct 12 06 : 48 : 54 2021 Version 21.3 . 0 . 0 . 0 Processing object type TRANSPORTABLE_EXPORT/STATISTICS/TABLE_STATISTICS Processing object type TRANSPORTABLE_EXPOR...
Read more

Oracle 23ai — Speed up IMPDP Using NOVALIDATE Constraints

By -
  When executing the   IMPDP   command, some operations do not support parallel execution. Creating constraints is one such operation. Oracle must validate the current data in the table before creating a constraint, which requires a full table scan for each validation. This process can be very time-consuming. Consider the following constraints: ALTER TABLE VAHID.MYTB ADD CONSTRAINT CHECK1 CHECK (col3 > 50 ); ALTER TABLE VAHID.MYTB ADD CONSTRAINT CHECK2 CHECK (col9 IN ( 5 , 15 )); We drop the  MYTB  table and recreate it using a dump file: SQL > drop table mytb; Table dropped. W -1 Processing object type TABLE_EXPORT / TABLE / TABLE_DATA W -1 . . imported "VAHID"."MYTB" 19 GB 337830912 rows in 83 seconds using external_table W -1 Processing object type TABLE_EXPORT / TABLE / CONSTRAINT / CONSTRAINT W -1 Completed 2 CONSTRAINT objects in 208 seconds As shown, creating the two constraints ta...
Read more

Staging Tables in Oracle 23ai

By -
  As you may know, using   staging tables   is a common practice in   Data Warehouse   environments, primarily during the data loading process into the   Warehouse Database . Typically, raw data received from   source databases   is first loaded into a temporary area, referred to as   staging tables . After cleansing inconsistent and invalid data and transforming it into a standard format, the cleaned data is stored in the final operational tables. In addition to initial data loading,   staging tables   are also used for updates and are often truncated before reuse. In   Oracle 23ai , a new minor feature has been introduced for this purpose, allowing users to create tables specifically designed as   staging tables . This is achieved by adding the   FOR STAGING   clause to the   CREATE TABLE   statement. Creating Staging Tables Using the   FOR STAGING   clause, staging tables can be created as ...
Read more