Manual CDB-PDB database creation steps for oracle 12c

Today I’ll show you how we can create a CDB(With PDB-pdb$seed) database without DBCA(Database Configuration Assistant).

Environment Details:

Database Server OS: Cents OS 6.7 (64 Bit)

Database Software Version: Oracle 12c (12.1.0.2) 64 Bit Enterprise version

Assumption: Oracle database software 12.1.0.2 is installed on the database server with required read/write directory permission to oracle user. 

Steps:

1)      Add below line in .bash_profile of oracle database server Linux user

export TMPDIR=/tmp
export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=$ORACLE_BASE/product/12.1.0/dbhome_1
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib
export PATH=$ORACLE_HOME/bin:$PATH
export ORACLE_SID=orcl
export PATH=$ORACLE_HOME/bin:$ORACLE_HOME/perl/bin/:$HOME/bin:$PATH

2) Make Directories for datafiles, control file, audit_file_dest and fast_recovery area

 $ mkdir –p /u01/app/oracle/fast_recovery_area/orcl

 $ mkdir –p /u01/app/oracle/admin/orcl/adump

 $ mkdir –p  /u01/app/oracle/oradata/orcl/pdbseed 

 $ mkdir –p /u01/app/oracle/oradata/orcl/pdborcl/

3)  Make init.ora file in “/u01/app/oracle/product/12c/db_1/dbs” path with below parameters

Example of init.ora file :

db_block_size=8192

open_cursors=300

db_domain=""

db_name="orcl"

control_files=("/u01/app/oracle/oradata/orcl/control01.ctl", "/u01/app/oracle/fast_recovery_area/orcl/control02.ctl")

db_recovery_file_dest="/u01/app/oracle/fast_recovery_area"

db_recovery_file_dest_size=4560m

compatible=12.1.0.2.0

diagnostic_dest=/u01/app/oracle

enable_pluggable_database=true

memory_target=720m

local_listener=LISTENER

processes=300

audit_file_dest="/u01/app/oracle/admin/orcl/adump"

audit_trail=db

remote_login_passwordfile=EXCLUSIVE

dispatchers="(PROTOCOL=TCP) (SERVICE=orclXDB)"

undo_tablespace=UNDOTBS1

Note: “enable_pluggable_database=true” is the main parameter through that we can create CDB database 

4) Create listener.ora file in “/u01/app/oracle/product/12c/db_1/network/admin/” location with below parameters

LISTENER = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = <OS_HOSTNAME>)(PORT = 1521))))

Note: update <OS_HOSTNAME> with database server OS hostname

5) Start database in no mount mode with the help of  pfile 

$ sqlplus / as sysdba

SQL > startup nomount pfile=’/u01/app/oracle/product/12c/db_1/dbs/init.ora’;

ORACLE instance started.

Total System Global Area  754974720 bytes

Fixed Size                  2928968 bytes

Variable Size             524291768 bytes

Database Buffers          222298112 bytes

Redo Buffers                5455872 bytes

6) Create database cdb database with pdb$seed template with below command

SQL > CREATE DATABASE "orcl"

            MAXINSTANCES 8

            MAXLOGHISTORY 1 

            MAXLOGFILES 16 

            MAXLOGMEMBERS 3 

            MAXDATAFILES 1024

            DATAFILE '/u01/app/oracle/oradata/orcl/system01.dbf' SIZE 1024M REUSE AUTOEXTEND ON EXTENT MANAGEMENT LOCAL

            SYSAUX DATAFILE '/u01/app/oracle/oradata/orcl/sysaux01.dbf' SIZE 1024M REUSE AUTOEXTEND ON

            SMALLFILE DEFAULT TEMPORARY TABLESPACE TEMP TEMPFILE '/u01/app/oracle/oradata/orcl/temp01.dbf' SIZE 20M REUSE AUTOEXTEND ON

            SMALLFILE UNDO TABLESPACE "UNDOTBS1" DATAFILE  '/u01/app/oracle/oradata/orcl/undotbs01.dbf' SIZE 200M REUSE  AUTOEXTEND ON

            CHARACTER SET AL32UTF8

            NATIONAL CHARACTER SET AL16UTF16

             LOGFILE GROUP 1 ('/u01/app/oracle/oradata/orcl/redo01.log') SIZE 50M, 

             GROUP 2 ('/u01/app/oracle/oradata/orcl/redo02.log') SIZE 50M,

             GROUP 3 ('/u01/app/oracle/oradata/orcl/redo03.log') SIZE 50M

             USER SYS IDENTIFIED BY pdbadmin USER SYSTEM IDENTIFIED BY welcome

              enable pluggable database

              seed file_name_convert=('/u01/app/oracle/oradata/orcl/system01.dbf','/u01/app/oracle/oradata/orcl/pdbseed/system01.dbf',         '/u01/app/oracle/oradata/orcl/sysaux01.dbf','/u01/app/oracle/oradata/orcl/pdbseed/sysaux01.dbf',

  '/u01/app/oracle/oradata/orcl/temp01.dbf','/u01/app/oracle/oradata/orcl/pdbseed/temp01.dbf',

  '/u01/app/oracle/oradata/orcl/undotbs01.dbf','/u01/app/oracle/oradata/orcl/pdbseed/undotbs01.dbf'); 

Note: “enable pluggable database” is required command to create CDB database in which we can create PDB database.

7) Your database is up and running now . now rRun below script from cdb database

$ Sqlplus / as sysdba

SQL > @$ORACLE_HOME/rdbms/admin/catalog.sql;

SQL> @$ORACLE_HOME/rdbms/admin/catblock.sql;
SQL>@$ORACLE_HOME/ctx/admin/catctx.sql <password> sysaux temp NOLOCK;

SQL >@$ORACLE_HOME/rdbms/admin/catproc.sql;

SQL > @$ORACLE_HOME/rdbms/admin/catoctk.sql;

SQL >@$ORACLE_HOME/rdbms/admin/owminst.plb;

SQL >@$ORACLE_HOME/sqlplus/admin/pupbld.sql;
SQL>@$ORACLE_HOME/rdbms/admin/utlrp.sql
SQL> @$ORACLE_HOME/rdbms/admin/catcdb.sql

NOTE: update ctxsys user password instead on <password> 

8)  Make Pluggable database from pdb$seed database template with the help of below command: 

Login with cdb database 

$ sqlplus / as sysdba

SQL > CREATE PLUGGABLE DATABASE pdborcl ADMIN USER pdbadmin IDENTIFIED BY welcome FILE_NAME_CONVERT=('/u01/app/oracle/oradata/orcl/pdbseed/','/u01/app/oracle/oradata/orcl/pdborcl/');