Database Point in Time Recovery of a Pluggable Database and Conventional Databases

Starting 12c with the introduction of pluggable databases, we can perform point-in-time recovery either at CDB (container database) level or at individual pluggable databases level, in a multi-container CDB. CDB level recovery is same as conventional database point-in-time recovery that existed prior to 12c. In this document I will explain how to perform point-in-time recovery of a CDB (that could be a non-container database, or a multi-container database with one or more PDBs); and also, recovery of an individual PDB in a multi-container CDB database.  

Point in time recovery is of 3 types: cancel based recovery (when recovery of a corrupted database is done and some redo/archived-redo logs are missing), SCN (System Change Number) based, and time based. Here I will explain how to perform SCN or time-based recovery that is performed to rollback unwanted changes to the database because of human error. 

If you already have flashback feature enabled, it is even easier to perform this recovery using flashback.

If you know either SCN or the time when some unwanted changes were made to the database, you can rollback them using RMAN backup. You have to make sure that all RMAN backups and archived redo logs (or their backup) are available for recovery process. 

The following is an example of time-based point in time recovery. First, shutdown all instances either by using srvctl statement or manually, and then startup only one instance (in case of RAC) in MOUNT mode.

############################

[oracl~]$ srvctl stop database -d my_db 

 

 

SQL> startup mount  

Total System Global Area 1.0201E+11 bytes 

Fixed Size                 23307168 bytes 

Variable Size            6174015488 bytes 

Database Buffers         9.5563E+10 bytes 

Redo Buffers              245125120 bytes 

Database mounted. 

 ############################

Time based point in time recovery

Initiate RMAN session, perform time based recovery and open database with RESETLOGS option. 

###########################

RMAN> run{ 

set until time "to_date('21-03-2023:00:00:00','DD-MM-YYYY:HH24:MI:SS')"; 

restore database; 

recover database; 

} 

###############################

After the recovery, open the database using resetlogs option.

###############################

SQL> alter database open resetlogs; 

###############################

SCN based point in time recovery 

If you know the SCN to rollback the unwanted changes of entire database, open only one instance of the database in MOUNT mode and perform recovery as follows. 

###############################

RMAN> run{ 

set until scn 465475849; 

restore database; 

recover database; 

} 

############################### 

After recovery completes, open database with RESETLOGS.  

###############################

SQL> alter database open resetlogs; 

###############################

Point in Time Recovery of Pluggable Database 

A small change in the syntax does not make PDB level time based or SCN based recovery very different from conventional database point in time recovery. The following is one example of performing a point-in-time recovery of a pluggable database MYPDB. The CDB and other PDBs in the container database would not have any impact. 

Shutdown the pluggable on all instances 

###############################

SQL> alter pluggable database MYPDB close immediate instances=all; 

############################### 

 Perform time-based recovery while contacted to the CDB, as user SYS. 

#############################  

RMAN> run { 

set until time "to_date('17/12/21 13:20:00' , 'dd/mm/yy hh24:mi:ss')"; 

restore pluggable database MYPDB; 

recover pluggable database MYPDB; 

} 

############################# 

Open PDB with resetlogs option. 

###############################

SQL>alter pluggable database MyPDB  open resetlogs instances=all;

###############################