Oracle 12c : Creating Pluggable Databases

So I’ve started taking my first steps in Oracle 12c.

It is my intention to make myself an indispensable Oracle 12c assist so I can make a fortune as a consultant. I figured the best way to start was to fully understand the new ‘pluggable database’ concepts. It makes sense to use this new feature if your company is limited on the number of instances it can legitimately stand up because of licence restrictions. Where I currently work there are no such licence restrictions so they’ve got no intention of upgrading. But to me it still makes sense that if you are going to have more than one development copy of a database, that they all live together as pluggable databases within a single traditional database.
So I’ve got a small schema with a number of tables in. I want to create a new CDB (Container Database) and several PDB’s (Pluggable databases). I’ll call the CDB jhcdb and the PDB’s will be jhpdb1, jhpdb2 etc.
I’m also using a copy of Oracles Developer Days 12C Virtual linux environment in Virtualbox (see the oracle website).  
The first thing I notice is that the virtual server already has a CDB database with a PDB and they’ve used the $TWO_TASK variable to allow you to access the PDB/CDB. I didn’t like this much, so I unset the TWO_TASK variable in the .bash_profile file, and instead I’ve placed the necessary entries within the tnsnames.ora file, which I will use from now one.
 
Tnsnames.ora
 jhcdb =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = jhcdb)
) 
)

jhpdb1 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = jhpdb1)
)
)

jhpdb2 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = jhpdb2)
)
)

Initjhcdb.ora

I created all of the necessary folders as indicated by the initjhcdb.ora file. I’m sure much of this isn’t necessary, but it’s what I used.
 
audit_file_dest='/u01/app/oracle/admin/jhcdb/adump'
audit_trail ='db'
compatible ='12.1.0.0.0'
control_files = ('/u02/oradata/jhcdb/control_01.dbf','/u02/oradata/jhcdb/control_02.dbf')
db_block_size=8192
db_domain=''
db_name='JHCDB'
db_recovery_file_dest='/u01/app/oracle/fast_recovery_area'
db_recovery_file_dest_size=2G
diagnostic_dest='/u01/app/oracle'
enable_pluggable_database=true
open_cursors=300
pga_aggregate_target=200M
processes = 150
sga_target=600M
remote_login_passwordfile='EXCLUSIVE'
undo_tablespace='UNDOTBS1'

Creation Script

I chose to create the database manually. If I’d used the DBCA utility, I’m sure I wouldn’t have needed to think about it a great deal, but this way helps the understanding better.
You need to be sure you’ve precreated each of the directories included below, including the cdb and seed directories. The SEED directory hold a list of DBFs that will be used when the PDB is created.
 
CREATE DATABASE jhcdb
USER SYS IDENTIFIED BY oracle      
USER SYSTEM IDENTIFIED BY oracle  
LOGFILE GROUP 1 ('/u02/oradata/jhcdb/redo01a.log','/u02/oradata/jhcdb/redo01b.log')
           SIZE 100M BLOCKSIZE 512,
        GROUP 2 ('/u02/oradata/jhcdb/redo02a.log','/u02/oradata/jhcdb/redo02b.log')
           SIZE 100M BLOCKSIZE 512,
        GROUP 3 ('/u02/oradata/jhcdb/redo03a.log','/u02/oradata/jhcdb/redo03b.log')
           SIZE 100M BLOCKSIZE 512
MAXLOGHISTORY 1
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 1024
CHARACTER SET AL32UTF8
NATIONAL CHARACTER SET AL16UTF16
EXTENT MANAGEMENT LOCAL
DATAFILE '/u02/oradata/jhcdb/system01.dbf'
  SIZE 700M REUSE AUTOEXTEND ON NEXT 10240K MAXSIZE UNLIMITED
SYSAUX DATAFILE '/u02/oradata/jhcdb/sysaux01.dbf'
  SIZE 550M REUSE AUTOEXTEND ON NEXT 10240K MAXSIZE UNLIMITED
DEFAULT TABLESPACE deftbs
   DATAFILE '/u02/oradata/jhcdb/deftbs01.dbf'
   SIZE 500M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED
DEFAULT TEMPORARY TABLESPACE tempts1
   TEMPFILE '/u02/oradata/jhcdb/temp01.dbf'
   SIZE 20M REUSE AUTOEXTEND ON NEXT 640K MAXSIZE UNLIMITED
UNDO TABLESPACE undotbs1
   DATAFILE '/u02/oradata/jhcdb/undotbs01.dbf'
   SIZE 200M REUSE AUTOEXTEND ON NEXT 5120K MAXSIZE UNLIMITED
ENABLE PLUGGABLE DATABASE
  SEED
  FILE_NAME_CONVERT = ('/u02/oradata/jhcdb/',
                       '/u02/oradata/jhseed/')
    SYSTEM DATAFILES SIZE 125M AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED
  SYSAUX DATAFILES SIZE 100M
USER_DATA TABLESPACE usertbs
  DATAFILE '/u02/oradata/jhseed/usertbs01.dbf'
  SIZE 200M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED;
 
 
The main CDB can be built in the same way as any other database so I won’t go into the ins and outs, just to say that with the ENABLE PLUGGABLE DATABASE option, you get what you need.
 
Creating the Pluggable database.
 
Important note here. After creating the database, I opened it and ran the usual CATPROC, CATALOG, etc. etc. scripts as usual. But when I tried to create the PDB, it wouldn’t work.
 
  ERROR at line 1:
  ORA-00604: error occurred at recursive SQL level 1
  ORA-00942: table or view does not exist
 
So instead, it  seems you need to run the catalog scripts like this using PERL from the linux command prompt.
 
  PERL5LIB=$ORACLE_HOME/rdbms/admin:$PERL5LIB; export PERL5LIB
  perl $ORACLE_HOME/rdbms/admin/catcon.pl -n 1 -l /u01/oracle -b catalog $ORACLE_HOME/rdbms/admin/catalog.sql;
  perl $ORACLE_HOME/rdbms/admin/catcon.pl -n 1 -l /u01/oracle -b catproc $ORACLE_HOME/rdbms/admin/catproc.sql;
  perl $ORACLE_HOME/rdbms/admin/catcon.pl -n 1 -l /u01/oracle -b pupbld -u SYSTEM/oracle $ORACLE_HOME/sqlplus/admin/pupbld.sql;
 
Now the first PDB must be created from the seed (/u02/oradata/jhseed). My admin user is to be family as that is also the user I will import my tables into. You need to precreate directory jhpdb1.
 
CREATE PLUGGABLE DATABASE jhpdb1 ADMIN USER family IDENTIFIED BY family
  STORAGE (MAXSIZE 2G MAX_SHARED_TEMP_SIZE 100M)
  DEFAULT TABLESPACE familyts1
  DATAFILE '/u02/oradata/jhpdb1/familyts01.dbf' SIZE 250M   AUTOEXTEND ON
  PATH_PREFIX = '/u02/oradata/jhpdb1/'
  FILE_NAME_CONVERT = ('/u02/oradata/jhseed/', '/u02/oradata/jhpdb1/');
 
alter pluggable database open;
 
Now I import the dump file of my schema. I needed to create an oracle directory for the data pump and this is done in the PDB rather than in the CDB.
 
sqlplus system/oracle@jhpdb1 <<EOF
create or replace directory jh_dp as '/u02/oradata/family';
quit
EOF
 
impdp directory=jh_dp dumpfile=jh_family.dmp userid=system/oracle@jhpdb1
 
Creating the second PDB from the first
 
Creating another copy of the PDB is easy, but you must put the first PDB into read only mode before you can execute the copy command.
 
alter pluggable database jhpdb1 close immediate;
alter pluggable database jhpdb1 open read only;
CREATE PLUGGABLE DATABASE jhpdb2 FROM jhpdb1
  PATH_PREFIX = '/u02/oradata/jhpdb2'
  FILE_NAME_CONVERT = ('/u02/oradata/jhpdb1/', '/u02/oradata/jhpdb2/');
alter pluggable database jhpdb2 open;
alter pluggable database jhpdb1 close;
alter pluggable database jhpdb1 open;
 
 
Now both copies of the family schema are present in the same database, but in separate pluggable databases. They can be opened and closed independently, populated independently etc etc.

Use: select pdb_id, pdb_name, con_id, status, open_mode from dba_pdbs, v$pdbs; 
 
 
 
HappyJohn