Skip to content

Posts from the ‘20c’ Category

20c- PDB Flashback to Any Point in time

Oracle Database – Enterprise Edition – Version 20.1 Beta and later
Information in this document applies to any platform.

PURPOSE

 Explanation of 20c new feature PDB Point-in-Time Recovery or Flashback to Any Time

SCOPE

 20C came out with new feature “PDB Point-in-Time Recovery or Flashback to Any Time in the Recent Past”

Assumption: You are aware of Flashback in Multitenant Architecture

DETAILS

 PDBs can be recovered to an orphan PDB incarnation within the same CDB incarnation or an ancestor incarnation.

Availability of PDBs is enhanced. Both flashback and point-in-time recovery operations are supported when recovering PDBs to orphan PDB incarnations.

Note: In 20c, As long as CDB incarnation not changes, PDB PITR or Flash back can be performed to any given time without impacting other PDB or CDB operations

Use Case
1/ Before creating restore point, create few tables in existing pdb

2/ Create multiple Restore point on any one of existing pdb

3/ Do flashback back and forth

4/ Check whether pdb opens without any impact to other pdb

Test Case

Assumption: There are two pdbs, pdb2 and pdb3. User crsk exist in both pdbs.

1/ Before creating restore point, create few tables in pdb2 and pdb3

alter session set container=pdb2; alter pluggable database pdb2 open;
create table crsk.crskpdb2_allobj1 as select * from all_objects; 

alter session set container=pdb3; alter pluggable database pdb3 open;
create table crsk.CRSKPDB3_ALLOBJ1 as select * from all_objects; 

 2.1 / Create Restore point on any one pdbCREATE RESTORE POINT rp1_pdb2 FOR PLUGGABLE DATABASE pdb2; (only for pdb2)

2.2 / Create Tables in Restore point

alter session set container=pdb2; alter pluggable database pdb2 open;

create table crsk.allobj2_rp1 as select * from all_objects; 
create table crsk.allobj3_rp1 as select * from all_objects; 
create table crsk.allobj4_rp1 as select * from all_objects; 

CREATE RESTORE POINT rp2_pdb2 FOR PLUGGABLE DATABASE pdb2;

create table crsk.allobj5_rp2 as select * from all_objects; 
@cdb session: alter system switch logfile; 
create table crsk.allobj6_rp2 as select * from all_objects; 
@cdb session: alter system switch logfile;
create table crsk.allobj7_rp2 as select * from all_objects; 
@cdb session: alter system switch logfile;

CREATE RESTORE POINT rp3_pdb2 FOR PLUGGABLE DATABASE pdb2;
create table crsk.allobj8_rp3 as select * from all_objects; 
@cdb session:alter system switch logfile;
create table crsk.allobj9_rp3 as select * from all_objects; 
@cdb session:alter system switch logfile;
create table crsk.allobj10_rp3 as select * from all_objects; 
@cdb session: alter system switch logfile;

2.3 Check existing tables

SQL> @lstpdbtab.sql

PDB_ID PDB_NAME OWNER TABLE_NAME
———- ————— ————— ——————————
3 PDB2 CRSK ALLOBJ2_RP1
3 PDB2 CRSK ALLOBJ3_RP1
3 PDB2 CRSK ALLOBJ4_RP1
3 PDB2 CRSK CRSKPDB2_ALLOBJ1
3 PDB2 CRSK ALLOBJ6_RP2
3 PDB2 CRSK ALLOBJ7_RP2
3 PDB2 CRSK ALLOBJ5_RP2
4 PDB3 CRSK CRSKPDB3_ALLOBJ1
3 PDB2 CRSK ALLOBJ8_RP3
3 PDB2 CRSK ALLOBJ9_RP3
3 PDB2 CRSK ALLOBJ10_RP3

11 rows selected.v$restore_point
SCN   DATABASE_INCARNATION# GUA STORAGE_SIZE TIME      RESTORE_POINT_TIME   PRE NAME     PDB CLE PDB_INCARNATION# REP CON_ID
—-  —— —————— —— —————————————– —— —————- — ————–
6162985 1                    NO 0            19-MAR-20 03.23.41.000000000 AM NO RP1_PDB2 YES NO   0               NO     3
6163674 1                    NO 0            19-MAR-20 03.26.23.000000000 AM NO RP2_PDB2 YES NO   0               NO     3
6164239 1                    NO 0            19-MAR-20 03.27.45.000000000 AM NO RP3_PDB2 YES NO   0               NO     3

 3/ Do flashback to restore point rp2

CONN / AS SYSDBA
ALTER PLUGGABLE DATABASE pdb2 CLOSE;
FLASHBACK PLUGGABLE DATABASE pdb2 TO RESTORE POINT rp2_pdb2; 
ALTER PLUGGABLE DATABASE pdb2 OPEN RESETLOGS;

Again Do flashback to restore point – rp3
CONN / AS SYSDBA
ALTER PLUGGABLE DATABASE pdb2 CLOSE;
FLASHBACK PLUGGABLE DATABASE pdb2 TO RESTORE POINT rp3_pdb2; 
ALTER PLUGGABLE DATABASE pdb2 OPEN RESETLOGS;Again move PDB database to restore point rp1
CONN / AS SYSDBA
ALTER PLUGGABLE DATABASE pdb2 CLOSE;
FLASHBACK PLUGGABLE DATABASE pdb2 TO RESTORE POINT rp1_pdb2; 
ALTER PLUGGABLE DATABASE pdb2 OPEN RESETLOGS;Again move PDB database to restore point rp3
CONN / AS SYSDBA
ALTER PLUGGABLE DATABASE pdb2 CLOSE;
FLASHBACK PLUGGABLE DATABASE pdb2 TO RESTORE POINT rp3_pdb2;
ALTER PLUGGABLE DATABASE pdb2 OPEN RESETLOGS;Again move PDB database to restore point rp2
CONN / AS SYSDBA
ALTER PLUGGABLE DATABASE pdb2 CLOSE;
FLASHBACK PLUGGABLE DATABASE pdb2 TO RESTORE POINT rp2_pdb2;
ALTER PLUGGABLE DATABASE pdb2 OPEN RESETLOGS;

4/ Check tables and observe the recovery .

Expected : As long as pdb with in CDB incarnation, pdb PITR or flashback should complete without issue

Query v$database_incarnation and v$pdb_incarnation to know about current status of pdb and cdb incarnation number.

%d bloggers like this: