Due to so many people asking me other methods besides SQLplus for provisioning PDBs; such as OEM, DBCA, etc. In this blog entry I’ll DBCA, just because its simple to show. As I mentioned in my last PDB blog,
the installer DBCA (initial DBA invocation) looks different than the subsequent (post initial db creation).

The main DBCA screen shows the following pages. We will choose Manage Pluggable Database

PDB12c 2013 08 20 17 46 20

Choose the CDB, Note you could have many CDBs on the same Node or RAC cluster

PDB12c 2013 08 30 17 51 59

We choose our PDB that we created in Part 1 of the blog

PDB12c 2013 08 30 17 52 39

Ahh..we gotta open the PDB first. As before:

CDB$ROOT@YODA> alter session set container=pdbobi;
Session altered.

CDB$ROOT@YODA> alter pluggable database pdbobi open;

Pluggable database altered.

or CDB$ROOT@YODA> alter pluggable database all open;

PDB12c 2013 08 30 17 54 22

Now we can Add support for and configure Database Vault. Additionally, Label Security can be configured.
It would have been nice to enable and modify Resource Manager as well other PDB tasks.
But I get the fact that this DBCA is really driven for the PDB operations (plug,unplug, create and destroy PDB).
Bulk of the PDB admin tasks are provided in EM

PDB12c 2013 08 30 18 14 54

Let’s do a new PDB creation for grins 🙂

PDB12c 2013 08 30 18 21 01

Specify the PDB name, storage location, and a default tablespace. Again, it would have been nice to specify a TEMP tablespace too, but that was left out

PDB12c 2013 08 30 18 22 26

Progress ….

PDB12c 2013 08 30 18 23 18

And Completion….Pretty Straightforward

PDB12c 2013 08 30 18 22 53


Once we have installed 12.1 Database Software, we can create the Container Database and the Pluggable Databases. In my case I did a software only install then manually executed DBCA

In this blog entry I’ll show the screens that walk-thru the configuration of the “first” database. I noticed that once DBCA is used to create the initial database, the capability and options (screens) for DBCA are different; i.e., it much more aligned to create/manage additional databases. I’ll show those screens in Part 3 of PDB

So let’s get started by executing
$ $ORACLE_HOME/bin/dbca

Rac01 2013 09 15 22 39 12

Choose Advanced mode for Policy Managed Database or use “Default Configuration”. Being a big promoter of Policy Managed Databases and since I have 4 RAC nodes (my best practice threshold to choose Policy Managed), I’ll choose, that.

Rac01 2013 09 15 22 39 44

I’ll pick a Global Database name and choose PDB option, and also option to choose how many PDBs to create (with prefix)

Rac01 2013 09 15 22 40 30

Pick a Server Pool name, I chose a cardinality of 2

Rac01 2013 09 15 22 40 58

Define the Management Options

Rac01 2013 09 15 22 41 20

Choose the Storage locations

Rac01 2013 09 15 22 43 31

Define Database Vault Owner and also the Separate Account Manager. Note the user name definitions

Rac01 2013 09 15 22 45 31

And now the finish

Rac01 2013 09 15 22 56 41


Consolidate where possible …Isolate where necessary

In the last blog I mentioned the benefits of schema consolidation and how it dove tails directly into a 12c Oracle Database PDB implementation.
In this part 2 of the PDB blog, we will get a little more detailed and do a basic walk-through, from  "cradle to grave" of a PDB.  We'll use SQlPlus as the tool of choice, next time I'll show w/ DBCA


First verify that we are truly on 12c Oracle database

SQL> select instance_name, version, status, con_id from v$instance;

INSTANCE_NAME	 VERSION	        STATUS	    CON_ID
---------------- ----------------- ------------ ----------
yoda		      12.1.0.1.0	   OPEN 		 0



The v$database view tells us that we are dealing with a CDB based database
 
CDB$ROOT@YODA> select cdb, con_id from v$database;

CDB	CON_ID
--- ----------
YES	     0


or a more elegant way:

CDB$ROOT@YODA> select NAME, DECODE(CDB, 'YES', 'Multitenant Option enabled', 'Regular 12c Database: ') "Multitenant Option ?" , OPEN_MODE, CON_ID from V$DATABASE;

NAME	  Multitenant Option ?	     OPEN_MODE	              CON_ID
--------- -------------------------- -------------------- ----------
YODA	  Multitenant Option enabled READ ONLY	                  0


There are alot of new views and tables to support PBD/CDB. But we'll focus on the v$PDBS and CDB_PDBS views:

CDB$ROOT@YODA> desc v$pdbs
 Name                            
 --------
 CON_ID                             
 DBID                                   
 CON_UID                              
 GUID                                   
 NAME                                   
 OPEN_MODE                             
 RESTRICTED                              
 OPEN_TIME                              
 CREATE_SCN                             
 TOTAL_SIZE     

CDB$ROOT@YODA> desc cdb_pdbs
 Name					  
 --------
 PDB_ID 				    
 PDB_NAME				    
 DBID					    
 CON_UID				 
 GUID						  
 STATUS 					  
 CREATION_SCN		
 CON_ID 				
                        

The SQlPlus command con_name (container name) shows the container and the con_id we are connected to:

CDB$ROOT@YODA> show con_name


CON_NAME
------------------------------
CDB$ROOT



CDB$ROOT@YODA> show con_id

CON_ID
------------------------------
1


Let's see what PDBs that are created in this CDB and their current state:

CDB$ROOT@YODA> select CON_ID,DBID,NAME,TOTAL_SIZE from v$pdbs;


    CON_ID      DBID NAME                    	     TOTAL_SIZE
---------- ---------- ------------------------------ ----------
      2   4066465523 PDB$SEED                          283115520
      3    483260478 PDBOBI                                    0


CDB$ROOT@YODA> select con_id, name, open_mode from v$pdbs;


    CON_ID NAME                   OPEN_MODE
---------- --------------------  ----------
      2    PDB$SEED                 READ ONLY
      3    PDBOBI           	    MOUNTED


Recall from part 1 of the blog series, that we created a PDB (pdbobi) when we specified the Pluggable Database Feature on install, and that a PDB$SEED got created as part of that Install process


Now lets's connect to the two different PDBs and see what they got!!  You really shouldn't ever connect to PDB$SEED, since its just used as a template, but we're just curious :-)

CDB$ROOT@YODA> alter session set container=PDB$SEED;
Session altered.


CDB$ROOT@YODA> select name from v$datafile;


NAME
--------------------------------------------------------------------------------
+PDBDATA/YODA/DATAFILE/undotbs1.260.823892155
+PDBDATA/YODA/DD7C48AA5A4404A2E04325AAE80A403C/DATAFILE/system.271.823892297
+PDBDATA/YODA/DD7C48AA5A4404A2E04325AAE80A403C/DATAFILE/sysaux.270.823892297


As you can see that PDB$SEED houses the template tablespaces -> System, Sysaux, and Undo tablespaces


If we connect back to the root-CDB, we see that it houses essentially the traditional database tablespaces (like in pre-12c days).  

CDB$ROOT@YODA> alter session set container=cdb$root;
Session altered.


CDB$ROOT@YODA> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
+PDBDATA/YODA/DATAFILE/system.258.823892109
+PDBDATA/YODA/DATAFILE/sysaux.257.823892063
+PDBDATA/YODA/DATAFILE/undotbs1.260.823892155
+PDBDATA/YODA/DD7C48AA5A4404A2E04325AAE80A403C/DATAFILE/system.271.823892297
+PDBDATA/YODA/DATAFILE/users.259.823892155
+PDBDATA/YODA/DD7C48AA5A4404A2E04325AAE80A403C/DATAFILE/sysaux.270.823892297
+PDBDATA/YODA/DD7D8C1D4C234B38E04325AAE80AF577/DATAFILE/system.276.823892813
+PDBDATA/YODA/DD7D8C1D4C234B38E04325AAE80AF577/DATAFILE/sysaux.274.823892813
+PDBDATA/YODA/DD7D8C1D4C234B38E04325AAE80AF577/DATAFILE/users.277.823892813
+PDBDATA/YODA/DD7D8C1D4C234B38E04325AAE80AF577/DATAFILE/example.275.823892813



BTW, the datafiles listed in V$datafiles differs from cbd_data_files.  cdb_data_files only shows datafiles from "open" PDB, so just be careful if you're looking for correct datafile

Let's connect to our user PDB (pdbobi) and see what we can see :-)

CDB$ROOT@YODA> alter session set container=pdbobi;
Session altered.


CDB$ROOT@YODA> select con_id, name, open_mode from v$pdbs;


    CON_ID NAME                  OPEN_MODE
---------- -----------------   -----------
      3    PDBOBI                 MOUNTED


Place PDBOBI in Read Write mode.  Note, that when you create the PDB, it is initially in mounted mode with a status of NEW. 
View the OPEN MODE status of a PDB by querying the OPEN_MODE column in the V$PDBS view or view the status of a PDB by querying the STATUS column of the CDB_PDBS or DBA_PDBS view


CDB$ROOT@YODA> alter pluggable database pdbobi open;

Pluggable database altered.

or CDB$ROOT@YODA> alter pluggable database all open;



And let's create a new tablespace in this PDB


CDB$ROOT@YODA> create tablespace obiwan datafile size 500M;

Tablespace created.


CDB$ROOT@YODA> select file_name from cdb_data_files;

FILE_NAME
--------------------------------------------------------------------------------
+PDBDATA/YODA/DD7D8C1D4C234B38E04325AAE80AF577/DATAFILE/example.275.823892813
+PDBDATA/YODA/DD7D8C1D4C234B38E04325AAE80AF577/DATAFILE/users.277.823892813
+PDBDATA/YODA/DD7D8C1D4C234B38E04325AAE80AF577/DATAFILE/sysaux.274.823892813
+PDBDATA/YODA/E456D87DF75E6553E043EDFE10AC71EA/DATAFILE/obiwan.284.824683339
+PDBDATA/YODA/DD7D8C1D4C234B38E04325AAE80AF577/DATAFILE/system.276.823892813


PDBOBI only has scope for its own PDB files.  We will illustrate this further down below.



Let's create a new clone from an existing PDB, but with a new path

CDB$ROOT@YODA> create pluggable database PDBvader from PDBOBI FILE_NAME_CONVERT=('+PDBDATA/YODA/DD7D8C1D4C234B38E04325AAE80AF577/DATAFILE','+PDBDATA');
create pluggable database PDBvader from PDBOBI FILE_NAME_CONVERT=('+PDBDATA/YODA/DD7D8C1D4C234B38E04325AAE80AF577/DATAFILE','+PDBDATA')
*
ERROR at line 1:
ORA-65040: operation not allowed from within a pluggable database


CDB$ROOT@YODA> show con_name                     


CON_NAME
------------------------------
PDBOBI


Hmm…..remember we were still connected to PDBOBI.  You can only create PDBs from root (and not even from pdb$seed).  So connect to CDBROOT


CDB$ROOT@YODA> create pluggable database PDBvader from PDBOBI FILE_NAME_CONVERT=('+PDBDATA/YODA/DD7D8C1D4C234B38E04325AAE80AF577/DATAFILE','+PDBDATA');


Pluggable database created.


CDB$ROOT@YODA> select pdb_name, status from cdb_pdbs;

PDB_NAME   STATUS
---------- -------------
PDBOBI	   NORMAL
PDB$SEED   NORMAL
PDBVADER   NORMAL

And

CDB$ROOT@YODA> select CON_ID,DBID,NAME,TOTAL_SIZE from v$pdbs;

    CON_ID	 DBID     NAME                     TOTAL_SIZE
---------- ---------- -------------          -------------
	 2 4066465523 PDB$SEED                      283115520
	 3  483260478 PDBOBI                        917504000
	 4  994649056 PDBVADER                              0


Hmm……the TOTAL_SIZE column shows 0 bytes.  Recall that all new PDBs are created and placed in MOUNTED stated 

CDB$ROOT@YODA> alter session set container=pdbvader;

Session altered.

CDB$ROOT@YODA> alter pluggable database open;

Pluggable database altered.



CDB$ROOT@YODA> select file_name from cdb_data_files;

FILE_NAME
--------------------------------------------------------------------------------
+PDBDATA/YODA/E46B24386A131109E043EDFE10AC6E89/DATAFILE/system.280.823980769
+PDBDATA/YODA/E46B24386A131109E043EDFE10AC6E89/DATAFILE/sysaux.279.823980769
+PDBDATA/YODA/E46B24386A131109E043EDFE10AC6E89/DATAFILE/users.281.823980769
+PDBDATA/YODA/E46B24386A131109E043EDFE10AC6E89/DATAFILE/example.282.823980769

Viola…. size is now reflected !!

CDB$ROOT@YODA> select CON_ID,DBID,NAME,TOTAL_SIZE from v$pdbs;

    CON_ID	 DBID     NAME	             		     TOTAL_SIZE
---------- ---------- ------------------------------ ----------
	    4   994649056 PDBVADER			 		     393216000


Again, the scope of PDBVADER is to its own container files; it can't see PDBOBI files at all.  If we connect back to cdb$root and look at v$datafile, we see that cdb$root has scope for all the datafiles in the CDB database

Incidentally, that long identifier, "E46B24386A131109E043EDFE10AC6E89", in the OMF name is the GUID or Global Identifier for that PDB.  This is not the same as container unique identifier (CON_UID).  The con_uid is a local
identifier; whereas the GUID is universal. Keep in mind that we can unplug a PDB from one CDB into another CDB, so the GUID provides this uniqueness and streamlines portability.

CDB$ROOT@YODA> select name, con_id from v$datafile order by con_id


NAME                                                                                    CON_ID
----------------------------------------------------------------------------------- ----------
+PDBDATA/YODA/DATAFILE/undotbs1.260.823892155	                                             1
+PDBDATA/YODA/DATAFILE/sysaux.257.823892063                                                  1
+PDBDATA/YODA/DATAFILE/system.258.823892109                                                  1
+PDBDATA/YODA/DATAFILE/users.259.823892155                                                   1
+PDBDATA/YODA/DD7C48AA5A4404A2E04325AAE80A403C/DATAFILE/system.271.823892297                 2
+PDBDATA/YODA/DD7C48AA5A4404A2E04325AAE80A403C/DATAFILE/sysaux.270.823892297                 2
+PDBDATA/YODA/DD7D8C1D4C234B38E04325AAE80AF577/DATAFILE/example.275.823892813                3
+PDBDATA/YODA/DD7D8C1D4C234B38E04325AAE80AF577/DATAFILE/users.277.823892813                  3
+PDBDATA/YODA/E456D87DF75E6553E043EDFE10AC71EA/DATAFILE/obiwan.284.824683339                 3
+PDBDATA/YODA/DD7D8C1D4C234B38E04325AAE80AF577/DATAFILE/system.276.823892813                 3
+PDBDATA/YODA/DD7D8C1D4C234B38E04325AAE80AF577/DATAFILE/sysaux.274.823892813                 3
+PDBDATA/YODA/E46B24386A131109E043EDFE10AC6E89/DATAFILE/sysaux.279.823980769                 4
+PDBDATA/YODA/E46B24386A131109E043EDFE10AC6E89/DATAFILE/users.281.823980769                  4
+PDBDATA/YODA/E46B24386A131109E043EDFE10AC6E89/DATAFILE/example.282.823980769                4
+PDBDATA/YODA/E46B24386A131109E043EDFE10AC6E89/DATAFILE/system.280.823980769                 4


Now that we are done testing with PDBVADER PDB, we can shutdown and drop this PDB

CDB$ROOT@YODA> alter session set container=cdb$root;

Session altered.

CDB$ROOT@YODA> drop pluggable database pdbvader including datafiles;
drop pluggable database pdbvader including datafiles
*
ERROR at line 1:
ORA-65025: Pluggable database PDBVADER is not closed on all instances.


CDB$ROOT@YODA> alter pluggable database pdbvader close;

Pluggable database altered.

CDB$ROOT@YODA> drop pluggable database pdbvader including datafiles;

Pluggable database dropped.


Just for completeness, I'll illustrate couple different ways to create a PDB

The beauty of PDB is not mobility (plug and unplug), which we'll show later, but that we can create/clone a new PDB from a "gold-image PDB" .  That's real agility and a Database as a Service (DbaaS) play. 


So let's create a new PDB in a couple of different ways.

Method #1: Create a PDB from SEED
CDB$ROOT@YODA> alter session set container=cdb$root;


Session altered.

CDB$ROOT@YODA> CREATE PLUGGABLE DATABASE pdbhansolo admin user hansolo identified by hansolo roles=(dba);

Pluggable database created.


CDB$ROOT@YODA> alter pluggable database pdbhansolo open;

Pluggable database altered.


CDB$ROOT@YODA> select file_name from cdb_data_files;

FILE_NAME
--------------------------------------------------------------------------------
+PDBDATA/YODA/E51109E2AF22127AE043EDFE10AC1DD9/DATAFILE/system.280.824693889
+PDBDATA/YODA/E51109E2AF22127AE043EDFE10AC1DD9/DATAFILE/sysaux.279.824693893


Notice that it just contains the basic files to enable a PDB.  The CDB will copy from the PDB$SEED the System and Sysaux tablesapces and instantiate them in the new PDB.




Method #2: Clone from an existing PDB (PDBOBI in our case)

CDB$ROOT@YODA> alter session set container=cdb$root;

Session altered.

CDB$ROOT@YODA> alter pluggable database pdbobi close;

Pluggable database altered.

CDB$ROOT@YODA> alter pluggable database pdbobi open read only;

Pluggable database altered.

CDB$ROOT@YODA> CREATE PLUGGABLE DATABASE pdbleia from pdbobi;

Pluggable database created.

CDB$ROOT@YODA> alter pluggable database  pdbleia open;

Pluggable database altered.

CDB$ROOT@YODA> select file_name from cdb_data_files;

FILE_NAME
--------------------------------------------------------------------------------
+PDBDATA/YODA/E51109E2AF23127AE043EDFE10AC1DD9/DATAFILE/system.281.824694649
+PDBDATA/YODA/E51109E2AF23127AE043EDFE10AC1DD9/DATAFILE/sysaux.282.824694651
+PDBDATA/YODA/E51109E2AF23127AE043EDFE10AC1DD9/DATAFILE/users.285.824694661
+PDBDATA/YODA/E51109E2AF23127AE043EDFE10AC1DD9/DATAFILE/example.286.824694661
+PDBDATA/YODA/E51109E2AF23127AE043EDFE10AC1DD9/DATAFILE/obiwan.287.824694669

Notice, that the OBI tablespace that we created in PDBOBI came over as part of this Clone process!!


You can also create a PDB as a snapshot (COW) from another PDB.  I'll post this test on the next blog report.  But essentially you'll need a NAS Appliannce, or any technology that will provide you with COW snapshot.
I plan on using ACFS as the storage container and ACFS RW Snapshot for the snapshot PDB.