As you probably know, the multitenant databases is the new feature of Oracle 12c to solve the dilema of applications segregation. Thanks to a multitenant container database, it allows to manage many databases as one taking advantage of resources consolidation. A perfect way to manage several applications in a single container. However are these plugglable databases that isolated as expected? Not exactly: As I will show in this posting, a single PDB can, under certain conditions, generate a complete system downtime.

How it should always work

Let’s take a basic example with a container database CDBPROD1 in which we set up 2 pluggable databases

  • PDBERP1
  • PDBHR1

So we have one database for the production ERP and one for the Human Resources application.

 

12c-architecture.png

 

SQL> select name,open_mode from v$containers;
NAME          OPEN_MODE
------------- ------------------
CDB$ROOT      READ WRITE
PDB$SEED      READ ONLY
PDBERP1       READ WRITE
PDBHR1        READ WRITE

Being sure that an issue on one application won’t impact any other is a key point in such an architecture.
Imagine that for some reasons, a data file of the ERP database gets lost or corrupted, what would happen?

NAME          FILE_ID    STATUS
------------- ---------- ------------------
PDBHR1        12         NOT ACTIVE
PDBHR1        13         NOT ACTIVE
PDBERP1       8          NOT ACTIVE
PDBERP1       9          NOT ACTIVE
PDBERP1       10         CANNOT OPEN FILE
PDBERP1       11         NOT ACTIVE
PDB$SEED      5          NOT ACTIVE
PDB$SEED      7          NOT ACTIVE
CDB$ROOT      1          NOT ACTIVE
CDB$ROOT      3          NOT ACTIVE
CDB$ROOT      4          NOT ACTIVE
CDB$ROOT      6          NOT ACTIVE

So far the ROOT container as well as any other PDBs are still working fine.

SQL> alter system archive log current;
System altered.

Insofar as we have all necessary backup pieces, we can easily restore the file and get everything back to normal again (even several ways are possible).

oracle@vmoratest12c1:/home/oracle/ [CDBPROD1] rman target sys@PDBERP1
Recovery Manager: Release 12.1.0.1.0 - Production on Fri Jun 28 05:47:52 2013
Copyright (c) 1982, 2013, Oracle and/or its affiliates.All rights reserved.
target database Password: 
connected to target database: CDBPROD1 (DBID=1679079389)
RMAN> alter database datafile 10 offline;
using target database control file instead of recovery catalog
Statement processed
RMAN> restore datafile 10;
Starting restore at 28-JUN-13
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=26 device type=DISKchannel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
…
…
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
Finished restore at 28-JUN-13

RMAN> recover datafile 10;
Starting recover at 28-JUN-13
using channel ORA_DISK_1
starting media recovery
archived log for thread 1 with sequence 11 is already on disk as file /u90/fast_recovery_area/CDBPROD1/archivelog/2013_06_28/o1_mf_1_11_8wsptjf4_.arc
archived log for thread 1 with sequence 12 is already on disk as file /u90/fast_recovery_area/CDBPROD1/archivelog/2013_06_28/o1_mf_1_12_8wspv3ph_.arc
archived log for thread 1 with sequence 13 is already on disk as file /u90/fast_recovery_area/CDBPROD1/archivelog/2013_06_28/o1_mf_1_13_8wszvlnc_.arc
...
...
Finished recover at 28-JUN-13

RMAN> alter database datafile 10 online;
Statement processed

That’s it! The ERP database is back to normal while no other PDB has been impacted.

Not always that easy!

Unfortunately, going deeper into our tests, I faced a tricky case where any single pluggable databse (PDB) can generate a downtime on the whole container!
Let’s see what happens if we loose the system tablespace of one of our two PDBs…

SQL> select name,open_mode from v$containers;
NAME          OPEN_MODE
------------- -----------
CDB$ROOT      READ WRITE
PDB$SEED      READ ONLY
PDBERP1       READ WRITE
PDBHR1        READ WRITE

All containers (Root container and Pluggable databases) are currently up and running. But now we are going to delete the SYSTEM data file of the ERP pluggable database / application… yes I know, that’s bad! 🙄

oracle@vmoratest12c1:/u01/oradata/CDBPROD1/PDBERP1/ [CDBPROD1] rm -f system01.dbf

Of course, this has a huge impact on my ERP application.

oracle@vmoratest12c1:/u01/oradata/CDBPROD1/PDBERP1/ [CDBPROD1] sqlplus sys@PDBERP1 as sysdba
SQL*Plus: Release 12.1.0.1.0 Production on Fri Jun 28 13:04:39 2013
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Enter password:
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
SQL> select * from dba_data_files;
select * from dba_data_files
 *
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-01116: error in opening database file 8
ORA-01110: data file 8: '/u01/oradata/CDBPROD1/PDBERP1/system01.dbf'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3

As expected, the data file 8, which is the SYSTEM data file for the PDBERP1, is missing.
However, this is only one pluggable database of my whole production environment. So I expect all other applications not to be impacted.

oracle@vmoratest12c1:/u01/oradata/CDBPROD1/PDBERP1/ [CDBPROD1] sqlplus / as sysdba
SQL*Plus: Release 12.1.0.1.0 Production on Fri Jun 28 13:08:25 2013
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
SQL> show CON_NAME
CON_NAME
--------------
CDB$ROOT

SQL> alter system archive log current;
System altered.
SQL> alter session set container=PDBHR1;
Session altered.
SQL> select count(*) from dba_tables; COUNT(*)
----------
 2316

Basically it looks like that is the case, great!
I’m going to take the same way to solve my issue than before. I will simply try to restore my missing data file and get my pluggable database back to work.

oracle@vmoratest12c1:/u01/oradata/CDBPROD1/PDBERP1/ [CDBPROD1] rman target sys@PDBERP1
Recovery Manager: Release 12.1.0.1.0 - Production on Fri Jun 28 13:12:14 2013
Copyright (c) 1982, 2013, Oracle and/or its affiliates. All rights reserved.
target database Password: 
connected to target database: CDBPROD1 (DBID=1679079389)
RMAN> alter database datafile 8 offline;
using target database control file instead of recovery catalog
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of sql statement command at 06/28/2013 13:12:30
ORA-01541: system tablespace cannot be brought offline; shut down if necessary

Ok it makes sense, I can’t simply set my SYSTEM data file offline while the database is still running. Alright then, I’m going to close my PDB.

Remember that a PDB can’t be shutdown except if the whole container is taken down itself. In fact, with a shutdown command, a pluggable database is set back to mount status.

RMAN> alter pluggable database close;
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of sql statement command at 06/28/2013 13:15:43
RMAN-00600: internal error, arguments [7530] [] [] [] []
 
RMAN> shutdown immediate
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of shutdown command at 06/28/2013 13:15:53
ORA-01116: error in opening database file 8
ORA-01110: data file 8: '/u01/oradata/CDBPROD1/PDBERP1/system01.dbf'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
 
RMAN> shutdown abort
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of shutdown command at 06/28/2013 13:15:59
ORA-01116: error in opening database file 8
ORA-01110: data file 8: '/u01/oradata/CDBPROD1/PDBERP1/system01.dbf'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3

Looks like RMAN can’t do it itself. Whatever I tried, I’m always getting an error. Sometimes, it even could come in RMAN itself.

oracle@vmoratest12c1:/u01/oradata/CDBPROD1/PDBERP1/ [CDBPROD1] rman target sys@PDBERP1
Recovery Manager: Release 12.1.0.1.0 - Production on Fri Jun 28 13:40:16 2013
Copyright (c) 1982, 2013, Oracle and/or its affiliates. All rights reserved.
target database Password: 
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-00554: initialization of internal recovery manager package failed
RMAN-04005: error from target database: 
ORA-01116: error in opening database file 8
ORA-01110: data file 8: '/u01/oradata/CDBPROD1/PDBERP1/system01.dbf'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
ORA-00604: error occurred at recursive SQL level 2
ORA-01116: error in opening database file 8
ORA-01110: data file 8: '/u01/oradata/CDBPROD1/PDBERP1/system01.dbf'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
ORA-00604: error occurr

So I moved to SQLPLUS, on a pluggable level, and took a chance again.

SQL> alter pluggable database close;
alter pluggable database close
*
ERROR at line 1:
ORA-01116: error in opening database file 8
ORA-01110: data file 8: '/u01/oradata/CDBPROD1/PDBERP1/system01.dbf'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3

SQL> shutdown immediate
ORA-01116: error in opening database file 8
ORA-01110: data file 8: '/u01/oradata/CDBPROD1/PDBERP1/system01.dbf'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3

SQL> shutdown abort
ORA-01116: error in opening database file 8
ORA-01110: data file 8: '/u01/oradata/CDBPROD1/PDBERP1/system01.dbf'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3

Same story there, as long as I’m connected to the pluggable database level, I can’t close it to run any restore operation. That’s not really surprising in fact, I have to do it on the ROOT container level.

SQL> alter pluggable database PDBERP1 close;
alter pluggable database PDBERP1 close
*
ERROR at line 1:
ORA-01116: error in opening database file 8
ORA-01110: data file 8: '/u01/oradata/CDBPROD1/PDBERP1/system01.dbf'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3

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

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

Now I’m seriously getting nervous, as I can’t get anything done on my PDB. It looks like the only way is to shutdown the whole production container to get rid of this issue…

SQL> show CON_NAME
CON_NAME
------------------------------
CDB$ROOT

SQL> shutdown immediate
ORA-01116: error in opening database file 8
ORA-01110: data file 8: '/u01/oradata/CDBPROD1/PDBERP1/system01.dbf'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3

Here is the point: I can’t even take my whole container properly done! The only way to bring it down is to run a shutdown abort!

SQL> shutdown abort
ORACLE instance shut down.

This is definitively not a gentle way to get it done. The final point here, is that my container won’t even start anymore until I get the SYSTEM data file from my single PDB back!

SQL> startup
ORACLE instance started.
Total System Global Area 1636814848 bytes
Fixed Size 2288968 bytes
Variable Size 989856440 bytes
Database Buffers 637534208 bytes
Redo Buffers 7135232 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 8 - see DBWR trace file
ORA-01110: data file 8: '/u01/oradata/CDBPROD1/PDBERP1/system01.dbf'

SQL> select name,open_mode from v$database;
NAME      OPEN_MODE
--------- --------------------
CDBPROD1  MOUNTED

At that point I can go forward with the data file 8 restore and recover to get my database back.

  • restore data file 8
  • recover data file 8
RMAN> connect target /
connected to target database: CDBPROD1 (DBID=1679079389, not open)
 
RMAN> restore datafile 8;
Starting restore at 28-JUN-13
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=11 device type=DISKchannel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00008 to /u01/oradata/CDBPROD1/PDBERP1/system01.dbf
…
…
…

RMAN> recover datafile 8;
Starting recover at 28-JUN-13
using channel ORA_DISK_1starting media recoveryarchived log for thread 1 with sequence 11 is already on disk as file /u90/fast_recovery_area/CDBPROD1/archivelog/2013_06_28/o1_mf_1_11_8wsptjf4_.arc
archived log for thread 1 with sequence 12 is already on disk as file /u90/fast_recovery_area/CDBPROD1/archivelog/2013_06_28/o1_mf_1_12_8wspv3ph_.arc
archived log for thread 1 with sequence 13 is already on disk as file /u90/fast_recovery_area/CDBPROD1/archivelog/2013_06_28/o1_mf_1_13_8wszvlnc_.arc
…
…
…
media recovery complete, elapsed time: 00:00:03
Finished recover at 28-JUN-13
 
RMAN> alter database open;
Statement processed
 

oracle@vmoratest12c1:/u01/oradata/CDBPROD1/PDBERP1/ [CDBPROD1] sqlplus / as sysdba
SQL*Plus: Release 12.1.0.1.0 Production on Fri Jun 28 13:33:34 2013
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
 
SQL> select name,open_mode from v$containers;
NAME         OPEN_MODE
------------ ------------
CDB$ROOT     READ WRITE
PDB$SEED     READ ONLY
PDBERP1      MOUNTED
PDBHR1       MOUNTED
 
SQL> alter pluggable database all open;
Pluggable database altered.
 
SQL> select name,open_mode from v$containers;
NAME          OPEN_MODE
------------- -------------
CDB$ROOT      READ WRITE
PDB$SEED      READ ONLY
PDBERP1       READ WRITE
PDBHR1        READ WRITE

Finally, we made it! Everything is back to normal and all databases are working fine.
As a conclusion, I would say that multitenant databases are pretty interesting and offer nice enhancements in terms of consolidation.
However you need to know that a single PDB, even the least important one, can under certain conditions generate a complete system outage!
I hope that this will help!
If you have any remark or experiences on such tests, feel free to comment.
Cheers 😀

David

PS: I also tried to restore the data file without setting anything offline or down as well as unplugging the PDB, but nothing helped. Restore fails as the data file can’t be locked and should be closed to unplug the PDB. Do see the vicious circle???