Oracle 12c database is a Multi-tenant container database in which a seed database called pdb$seed comes with it, Using which we can create pluggable databases with in the container database. In One Container 253 pdbs can be created including pdbseed.

Using the CREATE DATBASE SQL statement is a more manual approach to creating a database. If you use the CREATE DATABASE statement, you must complete additional actions before you have an operational database. These actions include building views on the data dictionary tables and installing standard PL/SQL packages. You perform these actions by running prepared scripts and are called as perl scripts in this version.

1.       Specify an Instance Identifier (SID) & Environment Variables

ORACLE_SID, unique Oracle system identifier (SID) is used to distinguish this instance from other Oracle Database instances that you may create later and run concurrently on the same host computer. The maximum number of characters for ORACLE_SID is 12, and only letters and numeric digits are permitted.

In our case ORACLE_HOME is on /u01/app/oracle/product/12.1.0.2/dbhome_1 path where we have Oracle software Installed.

$ cd

$ vi .bash_profile

export ORACLE_SID=cdb1

export ORACLE_HOME=/u01/app/oracle/product/12.1.0.2/dbhome_1

export PATH=$ORACLE_HOME/bin:$PATH:$ORACLE_HOME/perl/bin

export PERL5LIB=$ORACLE_HOME/rdbms/admin

$ . .bash_profile

2.       Ensure That the Required Environment Variables Are Set

$ echo $ORACLE_SID

$ echo $ORACLE_HOME

$ echo $PATH

3.       Create the Initialization Parameter File

$ cd $ORACLE_HOME/dbs

$ cp init.ora initcdb1.ora (cdb1 is SID here )

Open your pfile and edit with the following parameters

$ vi initcdb1.ora

db_name='cdb1'

enable_pluggable_database=true

audit_trail='db'

control_files='/disk1/oradata/cdb1/control01.ctl'

diagnostic_dest=/disk3/oradata/cdb1

db_block_size=8192

memory_target=429145600

memory_max_target=429145600

open_cursors=300

processes=300

remote_login_passwordfile='EXCLUSIVE'

undo_tablespace='UNDOTBS1'

compatible=12.1.0.2

4.       Create directory structure

$ mkdir -p /disk1/oradata/cdb1/pdbseed

$ mkdir -p /disk2/oradata/cdb1

$ mkdir -p /disk3/oradata/cdb1

5.       Create script for controlfile

$ vi $HOME/cr8cdb.sql

CREATE DATABASE cdb1

LOGFILE

GROUP 1 ('/disk1/oradata/cdb1/redo01a.log') SIZE 10M,

GROUP 2 ('/disk1/oradata/cdb1/redo02a.log') SIZE 10M

EXTENT MANAGEMENT LOCAL

DATAFILE '/disk1/oradata/cdb1/system01.dbf' SIZE 400M

UNDO TABLESPACE undotbs1 DATAFILE '/disk1/oradata/cdb1/undotbs01.dbf' SIZE 150M

SYSAUX DATAFILE '/disk1/oradata/cdb1/sysaux01.dbf' SIZE 400M

DEFAULT TEMPORARY TABLESPACE TEMP TEMPFILE '/disk1/oradata/cdb1/temp01.dbf' SIZE 20M

DEFAULT TABLESPACE USERS DATAFILE '/disk1/oradata/cdb1/users01.dbf' SIZE 20M

ENABLE PLUGGABLE DATABASE

SEED FILE_NAME_CONVERT =

('/disk1/oradata/cdb1','/disk1/oradata/cdb1/pdbseed');

Here in the above script seed database template called “pdbseed” is created along with Container database “cdb1” which is must for a container database.Pdbseed (template) is used to create Pluggable databases within the container database cdb1.

Note: If you want to create a database without seed it is called as non container database, which is similar to 11g database.

There are a few clauses of the CREATE DATABASE statement relevant only to pluggable databases. For instance,the ENABLE PLUGGABLE DATABASE clause is required if you want to create a pluggable database within the CDB.

The USER_DATA TABLESPACE clause specifies that an additional tablespace be created within the seed database; this tablespace will also be replicated to any pluggable databases that are cloned from the seed database. Also, the SEEDFILE_NAME_CONVERT specifies how the seed database files will be named and in what directories they will be located.

Next, ensure you have created any directories referenced in the parameter file and the CREATE DATABASE statement

Now, start up your database in nomount mode, and run the cr8cdb.sql script

6.       Connect and start the Instance

$ sqlplus / as sysdba

SQL> startup NOMOUNT

SQL> @$HOME/cr8cdb.sql

SQL> EXIT

7.       Build Data Dictionary Views

Run the scripts necessary to build data dictionary views, synonyms, and PL/SQL packages, and to support proper functioning of SQL*Plus:

Create the script

$ ls

$ mkdir output

$ cd output

$ vi postcdb.sql

!$ORACLE_HOME/perl/bin/perl $ORACLE_HOME/rdbms/admin/catcon.pl -u sys/oracle -d $ORACLE_HOME/rdbms/admin -b catalog_output -e -l $HOME/output catalog.sql

!$ORACLE_HOME/perl/bin/perl $ORACLE_HOME/rdbms/admin/catcon.pl -u sys/oracle -d $ORACLE_HOME/rdbms/admin -b catproc_output -e -l $HOME/output catproc.sql

!$ORACLE_HOME/perl/bin/perl $ORACLE_HOME/rdbms/admin/catcon.pl -u system/manager -d $ORACLE_HOME/sqlplus/admin -b pupbld_output –e –l $HOME/output pupbld.sql

Execute the script

Terminal 1:

$ sqlplus / as sysdba

SQL> @$HOME/output/postcdb.sql

Open another terminal

Terminal 2:

$ cd output

$ ls

$ tail -f catalog_output0.log

After completion of catalog give Ctl+c

Ctl+c

$ ls

$ tail -f catproc_output0.log

After completion of catproc give Ctl+c

Ctl+c

$ ls

$ tail -f pupbld_output0.log

After completion of pupbld close terminal 2 and continue with terminal 1.

8.       Verifying that a CDB was Created

To verify whether a database was created as a CDB, first connect to the root container as SYS.

You can now confirm that the CDB was successfully created via this query. If a database was created as a CDB,the CDB column of V$DATABASE will contain a YES value:

SQL> SELECT name, cdb from v$database;

SQL> SELECT dbid,con_id,name,open_mode from v$database;

DBID CON_ID NAME OPEN_MODE

---------- ---------- --------- -------------------

635405614 0 cdb1READ WRITE

Notice that the con_id is 0 for the whole container cdb1.

At this point, you should have two containers in your CDB database the root container and the seed pluggabledatabase. You can check with this query:

SQL> SELECT con_id, name from v$containers;

CON_ID NAME

------ ---------------

1 CDB$ROOT

2 PDB$SEED

SQL> EXIT

9.       Build Data Dictionary Views on pdb$seed

Run the scripts necessary to build data dictionary views, synonyms, and PL/SQL packages, and to support proper functioning of SQL*Plus in pdb$seed

$ cd

$ vi postpdb.sql

spool postpdb.log

@$ORACLE_HOME/rdbms/admin/catalog.sql

@$ORACLE_HOME/rdbms/admin/catblock.sql

@$ORACLE_HOME/rdbms/admin/catproc.sql

@$ORACLE_HOME/rdbms/admin/catoctk.sql

@$ORACLE_HOME/rdbms/admin/owminst.plb

Connect system/manager

@$ORACLE_HOME/sqlplus/admin/pupbld.sql

Spool off

10.   Connect to the database and by default pdb$seed is in mount state so open the pdb$seed and execute the script

$ sqlplus / as sysdba

Set the parameter “_oracle_script” is true

SQL> alter session set "_oracle_script"=true;

SQL> SELECT con_id,dbid,NAME,OPEN_MODE from v$pdbs;

SQL> alter pluggable database pdb$seed close;

SQL> alter pluggable database pdb$seed open;

Pluggable database altered.

SQL> @$HOME/postpdb.sql

SQL> alter session set "_oracle_script"=false;

11.   Check the pdb information

SQL> SELECT open_mode from v$database;

OPEN_MODE

--------------------

READ WRITE

SQL> SELECT con_id,dbid,NAME,OPEN_MODE from v$pdbs;

CON_ID DBID NAME OPEN_MODE

---------- --------------- ------------------------- ---------------

2 4062472313 PDB$SEED READ WRITE

SQL> EXIT