Creating Oracle Container Database in 12c using SQL commands

Creating a Container Database Using SQL Commands

Let’s us create a container database (CDB) by name ICACDB and one pluggable database ICAPDB1.

We will place all datafiles, logfiles, controlfile of CDB in /u02/oracle/icacdb directory and place seed database pdbseed database datafiles in ‘/u02/oracle/icacdb/pdbseed’ directory and pluggable database PDB1 files /u02/oracle/icacdb/icapdb1 directory

Step 1: Make directories

$mkdir /u02/oracle/icacdb

$cd /u02/oracle/icacdb
$mkdir icapdb1
$mkdir fast_recovery_area
$mkdir pdbseed

Create directory for storing audit files

$mkdir $ORACLE_BASE/admin/icacdb
$cd $ORACLE_BASE/admin/icacdb
$mkdir adump

Create directory for diagnostic destination

$cd /u02/oracle/icacdb
$mkdir diag

Step 2:- Create Parameter File by copying Template

$cd $ORACLE_HOME/dbs
$cp init.ora initicacdb.ora

Now open the PFile in vi editor and edit the require parameters. After editing it should look like as shown below

db_name='icacdb'
memory_target=500M
processes = 150

audit_file_dest=/opt/oracle/product/admin/icacdb/adump
audit_trail ='db'
db_block_size=8192
db_domain=''
db_recovery_file_dest='/u02/oracle/icacdb/fast_recovery_area'
db_recovery_file_dest_size=2G
diagnostic_dest=/u02/oracle/icacdb/diag
dispatchers='(PROTOCOL=TCP) (SERVICE=ICACDBXDB)'
open_cursors=300
remote_login_passwordfile='EXCLUSIVE'
undo_tablespace='UNDOTBS1'
control_files = '/u02/oracle/icacdb/control01.ora'
compatible ='12.1.0'
enable_pluggable_database=TRUE

Step 3- Set the ORACLE_SID environment variable and start the instance

$ export ORACLE_SID=icacdb
$ sqlplus
Enter User: / as sysdba

SQL>

Step 4:- Give Create Database statement as follows

CREATE DATABASE icacdb
LOGFILE
GROUP 1 '/u02/oracle/icacdb/log1.ora' size 10M,
GROUP 2 '/u02/oracle/icacdb/log2.ora' size 10M
EXTENT MANAGEMENT LOCAL
DATAFILE '/u02/oracle/icacdb/system01.dbf'
SIZE 700M REUSE AUTOEXTEND ON NEXT 10240K MAXSIZE UNLIMITED
SYSAUX DATAFILE '/u02/oracle/icacdb/sysaux01.dbf'
SIZE 550M REUSE AUTOEXTEND ON NEXT 10240K MAXSIZE UNLIMITED
DEFAULT TABLESPACE deftbs
DATAFILE '/u02/oracle/icacdb/deftbs01.dbf' SIZE 500M
REUSE AUTOEXTEND ON MAXSIZE UNLIMITED
DEFAULT TEMPORARY TABLESPACE tempts1 TEMPFILE '/u02/oracle/icacdb/temp01.dbf'
SIZE 20M REUSE AUTOEXTEND ON NEXT 640K MAXSIZE UNLIMITED
UNDO TABLESPACE undotbs1
DATAFILE '/u02/oracle/icacdb/undotbs01.dbf'
SIZE 200M REUSE AUTOEXTEND ON NEXT 5120K MAXSIZE UNLIMITED
ENABLE PLUGGABLE DATABASE
SEED
FILE_NAME_CONVERT =
('/u02/oracle/icacdb/',
'/u02/oracle/icacdb/pdbseed/')
SYSTEM DATAFILES SIZE 125M AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED
SYSAUX DATAFILES SIZE 100M
USER_DATA TABLESPACE usertbs
DATAFILE '/u02/oracle/icacdb/pdbseed/usertbs01.dbf'
SIZE 200M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED;

Step 5:- Run the scripts. In Oracle 12c it is recommended to run catalog and catproc scripts through Perl program catcon.pl

Goto ORACLE_HOME/rdbms/admin directory. This is where all the scripts are available.
$cd $ORACLE_HOME/rdbms/admin

Give the following command at O/s prompt

$perl catcon.pl -d $ORACLE_HOME/rdbms/admin -b catalog_output catalog.sql
$perl catcon.pl -d $ORACLE_HOME/rdbms/admin -b catproc_output catproc.sql
$perl catcon.pl -d $ORACLE_HOME/rdbms/admin -b catblock_output catblock.sql
$perl catcon.pl -d $ORACLE_HOME/rdbms/admin -b catoctk_output catoctk.sql
$perl catcon.pl -d $ORACLE_HOME/rdbms/admin -b owminst_output owminst.plb
$perl catcon.pl -d $ORACLE_HOME/sqlplus/admin -b pupbld_output pupbld.sql

Step 6:- See the status of Pluggable databases

SQL> show pdbs

CON_ID CON_NAME OPEN MODE RESTRICTED
------- -------- --------- ----------
2 PDB$SEED READ ONLY NO

Step 7:- Let’s us create the pluggable database ICAPDB1

To create a pluggable database give the following command

SQL>CREATE PLUGGABLE DATABASE icapdb1 ADMIN USER icapdb1adm IDENTIFIED BY tiger
STORAGE (MAXSIZE 2G MAX_SHARED_TEMP_SIZE 100M)
DEFAULT TABLESPACE users
DATAFILE '/u02/oracle/icacdb/icapdb1/users01.dbf'
SIZE 250M AUTOEXTEND ON
FILE_NAME_CONVERT = ('/u02/oracle/icacdb/pdbseed/',
'/u02/oracle/icacdb/icapdb1/');

To view list of PDB’s

SQL> show pdbs

CON_ID CON_NAME OPEN MODE RESTRICTED
------ -------- --------- --------
2 PDB$SEED READ ONLY NO
3 ICAPDB1 MOUNTED

Now open the pluggable database

SQL>alter pluggable database icapdb1 open;

See the status
SQL> show pdbs

CON_ID CON_NAME OPEN MODE RESTRICTED
------ -------- --------- --------
2 PDB$SEED READ ONLY NO
3 ICAPDB1 READ WRITE

To switch to Pluggable database give the following command

SQL>alter session set container=icapdb1

To view current container, give the following command

SQL> show con_name

«« Previous

Index

DB Exam Study