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.
CDB: CDB1
PDB: PDBTEST
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;
FILE# ONLINE ONLINE_STATUS ERROR CHANGE# TIME CON_ID
----------------------------------------------------------------- ----------
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 ;
CON_ID OPEN_MODE NAME
---------- ---------- --------------------
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
Connected.</password>
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