How to Restore and Recover Single or multiple datafiles from a PDB database

The Setup

We will be using below setup for the purpose of this post:

CDB name:CDB1
PDB name: pdbtest
CDB1 path:/cdb1/cdb1
PDBTEST path: /cdb1/cdb1/pdbtest
PDBTEST tablespace name: TEST
PDBTEST tablespace test datafile: /cdb1/cdb1/pdbtest/sh.dbf
PDBTEST TNS connect string: pdbtest
RMAN backup location: /cdb1
PDB$SEED path:/cdb1/cdb1/pdbseed

The goal of this post is to restore and recover a data file From PDB when one or a couple of data files associated with PDB are corrupted or lost. Before we start Please understand the following Terms used in 12c.

What is a multitenant container database?

A 12c database is either a non-container Database or a container Database - referred to as non-CDB or CDB respectively. A CDB is an Oracle database that includes zero, one, or many customer-created Containers or Pluggable Databases referred to as PDB.

The CDB has:

  • one ROOT container (CDB$ROOT) containing SYSTEM, SYSAUX, UNDO, and TEMP tablespaces, Controlfiles and Redologs
  • one SEED container (PDB$SEED) containing SYSTEM, SYSAUX, TEMP, EXAMPLE tablespaces, used as a template to create new PDBs

What is a Pluggable Database?

A pluggable database (PDB) is a user-created container holding the data for any application. A PDB would have its SYSTEM, SYSAUX, TEMP tablespaces. It can also contains other user-created tablespaces in it.

Change done to PDB database would be written to the Undo /redo present in the CDB$ROOT. By Default When you Connect as sysdba without any service name you connect to Root Container:

$ rman target /

In this example, we are using CDB1 for the container database name and pdbtest for the pluggable database name.


List of Permanent Datafiles
File Size(MB) Tablespace           RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1    790      SYSTEM               ***     /cdb1/cdb1/system01.dbf   -----------> Root Container database CDB1 datafile
3    1000     SYSAUX               ***     /cdb1/cdb1/sysaux01.dbf   -----------> Root Container database CDB1 datafile
4    240      UNDOTBS1             ***     /cdb1/cdb1/undotbs01.dbf  -----------> Root Container database CDB1 datafile
5    260      PDB$SEED:SYSTEM      ***     /cdb1/cdb1/pdbseed/system01.dbf -----> System datafile for PDB$SEED
6    5        USERS                ***     /cdb1/cdb1/users01.dbf   ------------> User created datafile for Root Container database CDB1
7    670      PDB$SEED:SYSAUX      ***     /cdb1/cdb1/pdbseed/sysaux01.dbf
17   260      PDBTEST:SYSTEM       ***     /cdb1/cdb1/pdbtest/system01.dbf   ---> System datafile for PDBTEST
18   720      PDBTEST:SYSAUX       ***     /cdb1/cdb1/pdbtest/sysaux01.dbf -----> Sysaux datafile for PDBTEST
19   4        PDBTEST:USERS        ***     /cdb1/cdb1/pdbtest/userpdbtest.dbf
20   2        PDBTEST:TEST        ***      /cdb1/cdb1/pdbtest/sh.dbf     ==============> Restoring datafile 20 from PDBTEST
List of Temporary Files
File Size(MB) Tablespace           Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1    88       TEMP                 32767       /cdb1/cdb1/temp01.dbf
2    87       PDB$SEED:TEMP        32767       /cdb1/cdb1/pdbseed/pdbseed_temp01.dbf
3    20       PDBTEST:TEMP         32767       /cdb1/cdb1/pdbtest/pdbtest_temp01.dbf

Step 1 - Check the status of the file

Check the status of the file you want to restore and recovery in the PDB. In this example the file number is 20:

SQL> Connect / as sysdba    ================> Connects to Root Container (CDB1)
SQL> select * from v$recover_file;

----------------------------------------------------------------- ----------
        20 OFFLINE OFFLINE              2334347   01-JUL-13          3

Here we see the datafile belongs to CON_ID=3. Run the below query to find the PDB name for this container:

SQL> select con_id,open_mode,name from v$pdbs ;

---------- ---------- --------------------
         2 READ ONLY  PDB$SEED
         3 READ WRITE PDBTEST             ### Confirmed that this file belongs to PDB database PDBTEST

If the datafile Is online and has some Corruption in it and you plan to restore it from backup then ensure you take the datafile offline before restore.

Datafile can be taken offline only when connecting to the PDB database that it belongs to (PDBTEST in this case) Else it will report the error below:

SQL> alter database datafile 20 offline ;
alter database datafile 20 offline
ERROR at line 1:
ORA-01516: nonexistent log file, data file, or temporary file "20"

Connect to the PDB database pdbtest:

SQL> conn sys/<password>@pdbtest as sysdba
SQL> show parameter con_id
         3     ------------> This confirms we have connect to correct PDB
SQL> alter database datafile 20 offline;

Database altered.

Step 2 - Restore the datafile 20 from backup

Connect to the Root Container database (CDB1):

$ rman target /   ==========> By Default contains to Root container
RMAN> restore datafile 20;
creating datafile file number=20 name=/cdb1/cdb1/pdbtest/sh.dbf

Finished restore at 01-JUL-13

In the above example, NO backup existed for this datafile, so RMAN will create a empty datafile with creations scn of the datafile and apply the archive log from the creation time till current time. If backup exist Rman would restore the datafile from backup and apply the required archivelogs.

Step 3 - Recover the datafile

RMAN> recover datafile 20;

Starting recover at 01-JUL-13
using channel ORA_DISK_1

starting media recovery

media recovery complete, elapsed time: 00:00:01
Finished recover at 01-JUL-13

RMAN> exit

Connect to PDBTEST in sqlplus:

SQL> conn sys/<password>@PDBTEST as sysdba

SQL> alter database datafile 20 online;</password>
SQL>Select * from v$recover_file;

No rows