Monday, February 10, 2014

Oracle Database 12c: What happens during CDB startup if one the PDB has a media problem?

During Oracle Day 2014, which held in Baku/Azerbaijan, my friend Mahir Quluzade told me that, according his test if there is media problem in one of the PDB and if we want to startup CDB, CDB will not start and it is completely NOT possible to start! Interesting, although I did not tested, I was sure it is by design and there should be workaround. Ok, I got free time today and tested:
SQL> select name, open_mode from v$pdbs;

NAME                           OPEN_MODE
------------------------------ ----------
PDB$SEED                       READ ONLY
PDB1                           READ WRITE
PDB2                           READ WRITE


SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/testdb/system01.dbf
/u01/app/oracle/oradata/testdb/sysaux01.dbf
/u01/app/oracle/oradata/testdb/undotbs01.dbf
/u01/app/oracle/oradata/testdb/pdbseed/system01.dbf
/u01/app/oracle/oradata/testdb/users01.dbf
/u01/app/oracle/oradata/testdb/pdbseed/sysaux01.dbf
/u01/app/oracle/oradata/testdb/pdb1/system01.dbf
/u01/app/oracle/oradata/testdb/pdb1/sysaux01.dbf
/u01/app/oracle/oradata/testdb/pdb1/pdb1_users01.dbf
/u01/app/oracle/oradata/testdb/pdb2/system01.dbf
/u01/app/oracle/oradata/testdb/pdb2/sysaux01.dbf
/u01/app/oracle/oradata/testdb/pdb2/pdb2_users01.dbf

12 rows selected.
Now, removing datafile from USERS tablespace in PDB1:
SQL> !rm /u01/app/oracle/oradata/testdb/pdb1/pdb1_users01.dbf

SQL> shutdown immediate
ORA-03113: end-of-file on communication channel
SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
[oracle@12cdb ~]$ sqlplus "/ as sysdba"

SQL*Plus: Release 12.1.0.1.0 Production on Sat Feb 8 22:30:53 2014

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area 2137886720 bytes
Fixed Size                  2290416 bytes
Variable Size             603983120 bytes
Database Buffers         1526726656 bytes
Redo Buffers                4886528 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 10 - see DBWR trace file
ORA-01110: data file 10: '/u01/app/oracle/oradata/testdb/pdb1/pdb1_users01.dbf'

So, CDB does not start, but it is not permanent situation, just connect into PDB1 and make that datafile offline:
SQL> alter session set container=PDB1;

Session altered.

SQL> alter database datafile '/u01/app/oracle/oradata/testdb/pdb1/pdb1_users01.dbf' offline;

Database altered.


SQL> alter database open;

Database altered.

SQL> select name, open_mode from v$pdbs;

NAME                           OPEN_MODE
------------------------------ ----------
PDB$SEED                       READ ONLY
PDB1                           MOUNTED
PDB2                           MOUNTED


SQL> alter pluggable database all open;

Pluggable database altered.

SQL> select name, open_mode from v$pdbs;

NAME                           OPEN_MODE
------------------------------ ----------
PDB$SEED                       READ ONLY
PDB1                           READ WRITE
PDB2                           READ WRITE
Yes, it is working. It would be not logical, if we could not open other PDBs in the same CDB because of having problem in 1 PDB only. If you apply PSU1 on 12c, you will not meet with such problem, CDB will skip opening problematic PDB and will open other PDBs.

3 comments:

Nassyam Basha said...

Yes, if one datafile of PDB is in problem then CDB including remaining PDBs will be in trouble of course, I have written about it with test scenario
http://www.oracle-ckpt.com/pdb-is-painful-to-cdb-any-cost/

Thank you.

Nassyam Basha said...

Yes, if any one datafile of PDB is in trouble then CDB including remaining PDBs will be in trouble, I have written in below website with test scenario.

http://www.oracle-ckpt.com/pdb-is-painful-to-cdb-any-cost/

Thank you

Mahir M. Quluzade said...

Teymur thanks for this blog post. It is really interesting.

You wrote: "If you apply PSU1 on 12c, you will not meet with such problem, CDB will skip opening problematic PDB and will open other PDBs."

Teymur I tested it, results here:
http://www.mahir-quluzade.com/2014/02/oracle-database-12c-what-happens-during.html

Thanks & Regards
Mahir M. Quluzade