• December 29, 2014

PDB Recovery - Your PDB won't open because a datafile is missing!

Guest Author

So, you start your CDB...

SQL> startup
ORACLE instance started.

Total System Global Area 2566914048 bytes
Fixed Size                  3048920 bytes
Variable Size             671091240 bytes
Database Buffers         1879048192 bytes
Redo Buffers               13725696 bytes
Database mounted.
Database opened.

So good, so far. Now, you try to open one of the PDB's called TPLUG with the alter pluggable database command and get the following error:

SQL> alter pluggable database tplug open;
alter pluggable database tplug open

ERROR at line 1:

ORA-01157: cannot identify/lock data file 12 - see DBWR trace file

ORA-01110: data file 12:


Now, normally you would just use the alter database command and take the datafile offline. Once that was done you would open the database and then you could restore the datafile with RMAN (or manually) in the background. However, when you try this on a datafile assigned to a PDB, we have a problem:

SQL> alter database datafile 12 offline;

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

Boy, that file 12 is causing us problems. It seems that the root of the CDB does not know about file 12, and we can confirm this by querying DBA_DATA_FILES:

SQL> select file_id from dba_data_files;


Yet, the file DOES appear in v$datafile:

SQL> select file# from v$datafile where file#=12;


So, what's up doc? Well, this is one of the upshots of the separation of PDB's within a CDB. Kind of like separation of church and state here in the US, if not more global in nature. What is a person to do? I can't open the PDB, I can't  offline the datafile - do I really have to wait until the datafile is restored to be able to get the database is open? Thankfully, the answer is no. What you need to do is scurry over to the PDB in question and offline that datafile.

You might rush to try to connect to the PDB via SQL*Plus using it's service name, like this:

sqlplus robert/robert@//myhost:1522/tplug

but, this gets us nowhere: 

ORA-01033: ORACLE initialization or shutdown in progress
Process ID: 0
Session ID: 0 Serial number: 0

No, what we need to do is log into the root container, and then use the alter session set container command to move us properly into the tplug container. Here is an example (I added a quick query against v$pdbs just to show you that the PDB was indeed only mounted:

 SQL> select name, open_mode from v$pdbs;

NAME                           OPEN_MODE
------------------------------ ----------
PDB$SEED                       READ ONLY
ROBERTPDB                      MOUNTED
TPLUG                          MOUNTED

SQL> alter session set container=tplug;

Session altered.

Now, we are connected to the tplug PDB - even though it's only mounted. We can now take the datafile offline and open the PDB:

SQL> alter database datafile 12 offline;

Now, I can open the database:

RMAN> alter pluggable database tplug open;

Statement processed

This is better now! The database is open! Your users can now access all of the data in it except for that in the missing (or corrupted datafile).

It seems likely that the other datafiles are ok, and online but we can certainly check that

SQL> select file#, online_status, error from v$recover_file;

---------- ------- -------------------------

 Now, all I need to do now is fire up RMAN, connect it to the TPLUG PDB and restore and recover the datafile:

rman target

restore datafile 12;

recover datafile 12;

alter database datafile 12 online;

and the datafile is now back online and we can query against any objects in it! So, just remember when dealing with CDB/PDB's that some of the ways you do things have changed slightly, but in general, they still stay the same.

Be the first to comment

Comments ( 0 )
Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.