Blog - comments

Thanks a lot Franck. I agree to use FRA and RMAN deletion policy to manage standby site archived log...
RIck CHEN
I still say that you don't have to delete archivelogs because they are managed by oracle. That's the...
I don't know any documentation about those EC and ECJ. And I'm sorry I don't know the consequence of...
Hi Franck thanks for clarifying this. I was already wondering about the difference between EC and EC...
Reiner
Sometimes with a group of transactions generating many archived logs, shipped and applied on standby...
Rick Chen
Blog David Hueber Oracle 12c: Pluggable databases not that isolated

dbi services Blog

Welcome to the dbi services Blog! This IT blog focuses on database, middleware, and OS technologies such as Oracle, Microsoft SQL Server & SharePoint, EMC Documentum, MySQL, PostgreSQL, Sybase, Unix/Linux, etc. The dbi services blog represents the view of our consultants, not necessarily that of dbi services. Feel free to comment on our blog postings.

  • Home
    Home This is where you can find all the blog posts throughout the site.
  • Categories
    Categories Displays a list of categories from this blog.
  • Tags
    Tags Displays a list of tags that have been used in the blog.
  • Bloggers
    Bloggers Search for your favorite blogger from this site.

Oracle 12c: Pluggable databases not that isolated

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! Wink

 

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 Laughing

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???

Rate this blog entry:
5

David Hueber is Head of Service Management and Consultant at dbi Services. He has ten years of experience in infrastructure operation & management, engineering, and optimization. He is specialized in Oracle technologies (engineering, backup and recovery, high availability, etc.), Service Management standards and Oracle infrastructure operation processes (Service Desk, Change Management, Capacity Planning, etc.). David Hueber is ITILv3 Service Operation Lifecycle certified and Linux LPIC-1 certified. He received a university degree in Informatics and Networks at the IUT Mulhouse, France. He also studied Information Systems at the Conservatoire National des Arts et Métiers in Mulhouse, France. His branch-related experience covers Financial Services / Banking, Chemicals & Pharmaceuticals, Transport & Logistics, Retail, Food, etc.

Comments

  • Guest
    Kumar Rajamani Monday, 08 July 2013

    Thanks for trying out Oracle Multitenant. We will try to address your concern.

  • Guest
    baskar Wednesday, 25 September 2013

    Hi David, Have you tried the same test with datafiles in ASM? With datafiles in ASM , the removing of files on the go will error out.

  • David Hueber
    David Hueber Wednesday, 25 September 2013

    Hi Baskar,

    thanks for your visit and comment!

    You are right, if we try the same with ASM, ASM is smart enough to avoid the file to be deleted. This even with the force option of the RM command

    ASMCMD> rm -f SYSTEM.288.819918935
    ORA-15032: not all alterations performed
    ORA-15028: ASM file '+DATA/TST12C/E0B3B9E487145A22E043C80116AC5647/DATAFILE/SYSTEM.288.819918935' not dropped; currently being accessed (DBD ERROR: OCIStmtExecute)

    However they are still 2 points. First of all, not all databases are implemented with ASM. For instance here in Switzerland, ASM is not (yet) that popular by customers. We mainly see it for databases in RAC of failover cluster configuration. Single instances are still often on File System. Why? that's a good question :-)
    Secondly the RM command is mainly here to simulate a physical corruption or data loss, which would impact the SYSTEM tablespace. This can still happen.
    Of course on ASM, we have double or triple mirroring. Did you said paranoiac? ;-)

    Cheers

  • Guest
    Amol Bhoite Wednesday, 09 April 2014

    Really a nice article to study.

    Thanks,

  • Guest
    Hayat Khan Wednesday, 16 April 2014

    When I studied Oracle New Feature Guide "Media Failure: PDB SYSTEM Data File"
    , I was surprised that how it is danger, I thought that it is my wrong understanding, but after searching on Google and reading your detail, it is now clear, that Multinational is dangerous when each Pluggable DB have System Table Space problem. Hope Oracle will take care in 12c R2.

    Nice article.... atleast it clears my concept

    Hayat Khan
    Senior Oracle DBA
    PTCL, Pakistan
    +92-333-5193460

  • Guest
    Arshad Rashid Wednesday, 11 June 2014

    I have tried this same exercise with any non-system tablespace of Pluggable database and had the same issue. So as far as recovery is concerned the pluggable database is not that pluggable after all.

Leave your comment

Guest Thursday, 27 November 2014
AddThis Social Bookmark Button
Deutsch (DE-CH-AT)   French (Fr)

Contact

Contact us now!

Send us your request!

Our workshops

dbi FlexService SLA - ISO 20000 certified.

dbi FlexService SLA ISO 20000

Expert insight from insiders!

Fixed Price Services

dbi FlexService SLA - ISO 20000 certified.

dbi FlexService SLA ISO 20000

A safe investment: our IT services at fixed prices!

Your flexible SLA

dbi FlexService SLA - ISO 20000 certified.

dbi FlexService SLA ISO 20000

ISO 20000 certified & freely customizable!

dbi services Newsletter