PDB is Painful to CDB any cost – 12c ?

PDB may be Painful to CDB

It’s been just late to start my 12cjourney with Multitenant, Container, Pluggable database concepts.  I started testing PDB concept how it works in restore, recovery and how impacts with CDB and other PDBs if i have.  Before i start about PDB scenario i would like to explain what is PDB.

What is pluggable database?

• Pluggable database is user created container holding the data and code specific to applications and Still PDB contains SYSTEM, SYSAUX, TEMP tablespaces and as you know Undo and redo logs are common to PDB and also CDB.
• On top of the existing tablespaces you can create any number of user tablespaces as required, But the PDB uses common control file, UNDO tablespace and redo logs of Container database.
• Undo and redo details of pluggable database will be added with the comments that they belongs to which pluggable database.

I have three servers and in each server one database of 11gR2 or older versions are running and each database using by each applications and lets suppose they are SAP, EBS and Peoplesoft, Now its time to think 12c concept that i can have multiple database(pluggable) with in a single container database,  Each pluggable database supports each applications. So i created 3 pluggable databases for each application SAP, EBS and Peoplesoft. Hence i can save server, license and maintenance and so on… Of course client is so happy. So far it looks very good, But when comes to real time we may have many issues, As am currently testing Backup/recovery of PDB to understand PDB behavior more.  I will show a small test how to restore and recover of PDB and how it impacts to Container database and other PDB’s if available.  Please note that at present i have one container database and one pluggable database. Some of the information of my environment, All of the datafiles are available and of course all datafiles are in good status without errors.

SQL> select name,open_mode from v$Pdbs;
NAME                           OPEN_MODE
------------------------------ ----------
PDB$SEED                       READ ONLY
ORAPDB                         READ WRITE
SQL> set line 200
SQL> col name for a100
SQL> select a.con_id,a.file#,a.name,b.error from v$datafile a,v$datafile_header b where a.file#=b.file#;
    CON_ID      FILE# NAME                                                                                                 ERROR
---------- ---------- ---------------------------------------------------------------------------------------------------- --------------
         1          1 /u01/app/oracle/oradata/ORACDB/datafile/o1_mf_system_9c5czbgn_.dbf
         1          3 /u01/app/oracle/oradata/ORACDB/datafile/o1_mf_sysaux_9c5cxm60_.dbf
         1          4 /u01/app/oracle/oradata/ORACDB/datafile/o1_mf_undotbs1_9c5d0rvv_.dbf
         2          5 /u01/app/oracle/oradata/ORACDB/datafile/o1_mf_system_9c5d1gkh_.dbf
         1          6 /u01/app/oracle/oradata/ORACDB/datafile/o1_mf_users_9c5d0qoq_.dbf
         2          7 /u01/app/oracle/oradata/ORACDB/datafile/o1_mf_sysaux_9c5d1gjo_.dbf
         3          8 /u01/app/oracle/oradata/ORACDB/pdb/datafile/system_pdb_01.dbf
         3          9 /u01/app/oracle/oradata/ORACDB/pdb/datafile/sysaux_pdb_01.dbf
         3         10 /u01/app/oracle/oradata/ORACDB/pdb/datafile/users_pdb_01.dbf
9 rows selected.

Now i connected to pluggable database and you can verify by view “v$pdbs” to check which database you connected.

SQL> alter session set container=orapdb;
Session altered.
SQL> col name for a8
SQL> select name,open_mode from v$Pdbs;
NAME     OPEN_MODE
-------- ----------
ORAPDB   READ WRITE
SQL> col name for a100
SQL> select a.con_id,a.file#,a.name,b.error from v$datafile a,v$datafile_header b where a.file#=b.file#;

    CON_ID      FILE# NAME                                                                                                 ERROR
---------- ---------- ---------------------------------------------------------------------------------------------------- ------------------
         0          4 /u01/app/oracle/oradata/ORACDB/datafile/o1_mf_undotbs1_9c5d0rvv_.dbf
         3          8 /u01/app/oracle/oradata/ORACDB/pdb/datafile/system_pdb_01.dbf
         3          9 /u01/app/oracle/oradata/ORACDB/pdb/datafile/sysaux_pdb_01.dbf
         3         10 /u01/app/oracle/oradata/ORACDB/pdb/datafile/users_pdb_01.dbf

Now we delete any one datafile to impact pluggable database consider where application SAP is running, Note that the data files am deleting they are belongs to only Pluggable database.

SQL> !ls -ltr /u01/app/oracle/oradata/ORACDB/pdb/datafile/users_pdb_01.dbf
-rw-r----- 1 oracle oinstall 5251072 Dec 20 12:09 /u01/app/oracle/oradata/ORACDB/pdb/datafile/users_pdb_01.dbf
SQL> !rm /u01/app/oracle/oradata/ORACDB/pdb/datafile/users_pdb_01.dbf
SQL> !ls -ltr /u01/app/oracle/oradata/ORACDB/pdb/datafile/users_pdb_01.dbf
ls: /u01/app/oracle/oradata/ORACDB/pdb/datafile/users_pdb_01.dbf: No such file or directory
SQL> !ps -ef|grep pmon
oracle   24988     1  0 12:08 ?        00:00:00 ora_pmon_oracdb
oracle   25585 25498  0 12:16 pts/2    00:00:00 /bin/bash -c ps -ef|grep pmon
SQL> 

So we have deleted Users tablespace datafile and of course still instance running as normal behavior. Now am going to perform checkpoint from pluggable database because it enforces DWR process of all modified buffers in the SGA buffer cache to the data files, in this case data file headers also updated with the latest checkpoint.

SQL> alter system checkpoint;
 alter system checkpoint
 *
 ERROR at line 1:
 ORA-03113: end-of-file on communication channel
 Process ID: 25499
 Session ID: 46 Serial number: 17
SQL> !ps -ef|grep pmon
 oracle   25632 25498  0 12:17 pts/2    00:00:00 /bin/bash -c ps -ef|grep pmon
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
-bash-3.2$ sqlplus / as sysdba
SQL*Plus: Release 12.1.0.1.0 Production on Fri Dec 20 12:45:29 2013
Copyright (c) 1982, 2013, Oracle.  All rights reserved.
Connected to an idle instance.
SQL> startup
ORACLE instance started.
Total System Global Area 1653518336 bytes
Fixed Size                  2289016 bytes
Variable Size            1056965256 bytes
Database Buffers          587202560 bytes
Redo Buffers                7061504 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 10 - see DBWR trace file
ORA-01110: data file 10: '/u01/app/oracle/oradata/ORACDB/pdb/datafile/users_pdb_01.dbf'
SQL> select name,open_mode from v$Pdbs;
NAME                           OPEN_MODE
------------------------------ ----------
PDB$SEED                       MOUNTED
ORAPDB                         MOUNTED
SQL>

After issuing the checkpoint, session was terminated from pluggable database… When i exit session and connected back to container database it connected to an “IDLE” instance… Now i was thinking if there is any problem with pluggable database and i can still connect back to container database or any other PDB part of the Container any time and also it should not impact to Container database. Because we can unplug or plug the database(PDB) any time. However am accepting still datafile of PDB are still datafiles.

Conclusion:-

Even i can shutdown and start up pluggable database and it doesn’t impact to CDB , So that mean if i lost data file of PDB it is impacting to all remaining Pluggable Database and also the Master Container Database?  Now all remaining applications i.e.  EBS and Peoplesoft are also down unfortunately related to other Pluggable Databases..  So now any application can’t be used until unless you restore/recover belongs to one PDB?  So Pluggable Database is really easy to use, very much enhanced and it really not impacting business? I would like to add even screenshot of the log what i did above.    If anybody would like to add comments…Please always welcome.

I have also some questions to refresh your self on PDB, May be my questions are silly or crazy 🙂

1) if in case of system datafile lost of Pluggable database, what happens to CDB?

2) If i shutdown the PDB, will it impact to any other PDB part of CDB?

3) You can drop PDB anytime, then why CDB can’t stop and startup in case of system or user datafile lost of PDB?

4) If you have 10 PDB’s of one CDB, if there is lost of any single datafile of PDB(pdb1) and i have scenario to startup and shutdown my CDB and other PDBs except damaged(pdb1) why i can’t startup CDB or other PDB’s?

 – Happy Reading —