Home / 12c / Oracle 12c: Create a Container Database (CDB) manually with CREATE DATABASE script

Oracle 12c: Create a Container Database (CDB) manually with CREATE DATABASE script

Note:
Oracle strongly recommends using the Database Configuration Assistant (DBCA) instead of the CREATE DATABASE SQL statement to create a CDB, because using DBCA is a more automated approach, and your CDB is ready to use when DBCA completes.

1. Setup Database environment
Set the ORACLE_SID and ORACLE_HOME environment variable and include the ORACLE_HOME/bin directory to the PATH variable.

CREATE CDB MANUALLY 01

NOTE: You must choose a Database Administrator authentication method
– You must be authenticated and granted appropriate system privileges in order to create a database.
– You can be authenticated as an administrator with the required privileges in the following ways:
a. With a password file (sqlplus sys as sysdba)
Example: orapwd FILE=’/u01/app/oracle/product/12.1.0/db_1/dbs/orapwnewcdb’ ENTRIES=10 FORMAT=12CREATE CDB MANUALLY 02 ORAPWD
b. With operating system authentication (sqlplus / as sysdba)
Ensure that you log in to the host computer with a user account that is a member of the appropriate operating system user group (typically the dba user group).

2. Create the Initialization Parameter File
cd $ORACLE_HOME/dbs
vi initnewcdb.ora
cat initnewcdb.ora

CREATE CDB MANUALLY 03 INITCREATE CDB MANUALLY 03 INIT 2

3. Create needed directories
CONTROL FILES:
mkdir -p /u01/app/oracle/oradata/newcdb
mkdir -p /u01/app/oracle/fast_recovery_area/newcdb
DATA FILES:
# mkdir -p /u01/app/oracle/oradata/newcdb
mkdir -p /u01/app/oracle/oradata/newcdb/pdbseed
REDO LOG FILES:
# mkdir -p /u01/app/oracle/oradata/newcdb
mkdir -p /u02/app/oracle/oradata/newcdb
ADUMP:
mkdir -p /u01/app/oracle/admin/newcdb/adump

CREATE CDB MANUALLY 04 MKDIR

4. Create Database script
To create a CDB, the ENABLE_PLUGGABLE_DATABASE initialization parameter must be set to TRUE.
Note: /home/oracle/bin exists in my environment
vi /home/oracle/bin/create_db_newcdb.sql

CREATE CDB MANUALLY 05 CREATE DB SCRIPT

5. Login to the Database

6. Create a Server Parameter File

7. Start the Instance

CREATE CDB MANUALLY 06 SPFILE

8. Use the CREATE DATABASE statement to create the new CDB.

CREATE CDB MANUALLY 07 CREATE DB RUN

— Validate if the database created is a CDB

— Validate if the seed database is created

CREATE CDB MANUALLY 08 VALIDATE

— Query CDB/PDB name, tablespace name, datafile location, size, and status

CREATE CDB MANUALLY 09 TBS

— Check Control files

— Check Redo log files

CREATE CDB MANUALLY 10 Ctl redo

— Check if the NLS parameters are set as per the response file

CREATE CDB MANUALLY 11 NLS

9. Data Dictionary views

We need to run the Oracle supplied dictionary scripts like catalog.sql, catproc.sql, pupbld.sql to populate the necessary dictionary views when we create a Oracle database manually using CREATE DATABASE statement.

But it’s different for a container database, because we have multiple container databases such as CDB$ROOT and PDB$SEED, we need to run these scripts against each of these containers. To simplify the creation of dictionary views in a CDB, Oracle provides a script called catcdb.sql located under $ORACLE_HOME/rdbms/admin directory.

However, when we run the catcdb.sql script, it creates all the database components like the way DBCA does, it will create database components like Spatial, Oracle Text, XDK, etc, even if we wouldn’t use them. So we would use the catcon.pl located under $ORACLE_HOME/rdbms/admin to run the Oracle supplied scripts (catalog.sql, catproc.sql, etc) against our container databases.

NOTE: We used sys, sys, and system account here

CREATE CDB MANUALLY 12 catcon 1

— Query the status of database components

CREATE CDB MANUALLY 13 dba_registry

— Recompile all the INVALID objects

CREATE CDB MANUALLY 13 dba_registry 2

— Query the status of database components again

CREATE CDB MANUALLY 13 dba_registry 3

10. Create a PDB

— Open the PDB for READ-WRITE

— Validate the PDB

CREATE CDB MANUALLY 14 NEWPDB

— Validate list of datafiles from each of the containers

NOTE: Because we created the CDB manually, if we did not create an entry for it on /etc/oratab, we wouldn’t see its entry on DBCA (let’s say you wanted to delete it).

This is my personal notes and I hope you learned something from it!

Check Also

7cf484ceeae6bc7fc2161eddc603cd15.jpeg

Oracle Database 12c – How to Add / Multiplex Control Files

PURPOSE: – protect your Database in case of a disk failure – reduce the risk …

a2b042fe070145288fbcd8df3f1198ed.png

Oracle Database 12c Release 1 (12.1) Installation on Oracle Linux 7 (OL7.2)

Table of Contents ▼▲ Note from Oracle Database 12c R1 Installation Guide: Starting with Oracle …

Leave a Reply