Share My Idea from life

Oracle 12c: Create a Container Database manually

ipstor -

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.

export ORACLE_SID=newcdb
export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=$ORACLE_BASE/product/12.1.0/db_1
export PATH=$ORACLE_HOME/bin:$PATH

 

 

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=12

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

sga_target=500M
pga_aggregate_target=260M
audit_file_dest="/u01/app/oracle/admin/newcdb/adump"
audit_trail=db
compatible=12.1.0.2.0
control_files=("/u01/app/oracle/oradata/newcdb/control01.ctl","/u01/app/oracle/fast_recovery_area/newcdb/control02.ctl")
db_block_size=8192
db_domain=localdomain
db_name="newcdb"
db_recovery_file_dest="/u01/app/oracle/fast_recovery_area"
db_recovery_file_dest_size=4560m
diagnostic_dest=/u01/app/oracle
dispatchers="(PROTOCOL=TCP) (SERVICE=newcdbXDB)"
enable_pluggable_database=true
open_cursors=300
processes=300
remote_login_passwordfile=EXCLUSIVE
undo_tablespace=UNDOTBS1

 

 

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

 

 

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 DATABASE newcdb
 USER SYS IDENTIFIED BY sys_password
 USER SYSTEM IDENTIFIED BY system_password
 LOGFILE GROUP 1 ('/u01/app/oracle/oradata/newcdb/redo01a.log','/u02/app/oracle/oradata/newcdb/redo01b.log')
 SIZE 100M BLOCKSIZE 512,
 GROUP 2 ('/u01/app/oracle/oradata/newcdb/redo02a.log','/u02/app/oracle/oradata/newcdb/redo02b.log')
 SIZE 100M BLOCKSIZE 512,
 GROUP 3 ('/u01/app/oracle/oradata/newcdb/redo03a.log','/u02/app/oracle/oradata/newcdb/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 '/u01/app/oracle/oradata/newcdb/system01.dbf'
 SIZE 700M REUSE AUTOEXTEND ON NEXT 10240K MAXSIZE UNLIMITED
 SYSAUX DATAFILE '/u01/app/oracle/oradata/newcdb/sysaux01.dbf'
 SIZE 550M REUSE AUTOEXTEND ON NEXT 10240K MAXSIZE UNLIMITED
 DEFAULT TABLESPACE USERS
 DATAFILE '/u01/app/oracle/oradata/newcdb/users01.dbf'
 SIZE 500M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED
 DEFAULT TEMPORARY TABLESPACE TEMP
 TEMPFILE '/u01/app/oracle/oradata/newcdb/temp01.dbf'
 SIZE 20M REUSE AUTOEXTEND ON NEXT 640K MAXSIZE UNLIMITED
 UNDO TABLESPACE UNDOTBS1
 DATAFILE '/u01/app/oracle/oradata/newcdb/undotbs01.dbf'
 SIZE 200M REUSE AUTOEXTEND ON NEXT 5120K MAXSIZE UNLIMITED
 ENABLE PLUGGABLE DATABASE
 SEED
 FILE_NAME_CONVERT = ('/u01/app/oracle/oradata/newcdb/',
 '/u01/app/oracle/oradata/newcdb/pdbseed/')
 SYSTEM DATAFILES SIZE 125M AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED
 SYSAUX DATAFILES SIZE 100M
;

 

 

5. Login to the Database

$ sqlplus / as sysdba
SQL*Plus: Release 12.1.0.2.0 Production on Mon Oct 9 13:15:24 2017
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Connected to an idle instance.
SQL>

6. Create a Server Parameter File

SQL> create spfile from pfile='/u01/app/oracle/product/12.1.0/db_1/dbs/initnewcdb.ora';
File created.

7. Start the Instance

SQL> startup nomount;
ORACLE instance started.
Total System Global Area 524288000 bytes
Fixed Size 2926320 bytes
Variable Size 268437776 bytes
Database Buffers 247463936 bytes
Redo Buffers 5459968 bytes
SQL>

 

 

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

SQL> set echo on
SQL> set timing on
SQL> @/home/oracle/bin/create_db_newcdb.sql
SQL> CREATE DATABASE newcdb
 2 USER SYS IDENTIFIED BY oracle3
 3 USER SYSTEM IDENTIFIED BY oracle3
 4 LOGFILE GROUP 1 ('/u01/app/oracle/oradata/newcdb/redo01a.log','/u02/app/oracle/oradata/newcdb/redo01b.log')
 5 SIZE 100M BLOCKSIZE 512,
 6 GROUP 2 ('/u01/app/oracle/oradata/newcdb/redo02a.log','/u02/app/oracle/oradata/newcdb/redo02b.log')
 7 SIZE 100M BLOCKSIZE 512,
 8 GROUP 3 ('/u01/app/oracle/oradata/newcdb/redo03a.log','/u02/app/oracle/oradata/newcdb/redo03b.log')
 9 SIZE 100M BLOCKSIZE 512
 10 MAXLOGHISTORY 1
 11 MAXLOGFILES 16
 12 MAXLOGMEMBERS 3
 13 MAXDATAFILES 1024
 14 CHARACTER SET AL32UTF8
 15 NATIONAL CHARACTER SET AL16UTF16
 16 EXTENT MANAGEMENT LOCAL
 17 DATAFILE '/u01/app/oracle/oradata/newcdb/system01.dbf'
 18 SIZE 700M REUSE AUTOEXTEND ON NEXT 10240K MAXSIZE UNLIMITED
 19 SYSAUX DATAFILE '/u01/app/oracle/oradata/newcdb/sysaux01.dbf'
 20 SIZE 550M REUSE AUTOEXTEND ON NEXT 10240K MAXSIZE UNLIMITED
 21 DEFAULT TABLESPACE USERS
 22 DATAFILE '/u01/app/oracle/oradata/newcdb/users01.dbf'
 23 SIZE 500M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED
 24 DEFAULT TEMPORARY TABLESPACE TEMP
 25 TEMPFILE '/u01/app/oracle/oradata/newcdb/temp01.dbf'
 26 SIZE 20M REUSE AUTOEXTEND ON NEXT 640K MAXSIZE UNLIMITED
 27 UNDO TABLESPACE UNDOTBS1
 28 DATAFILE '/u01/app/oracle/oradata/newcdb/undotbs01.dbf'
 29 SIZE 200M REUSE AUTOEXTEND ON NEXT 5120K MAXSIZE UNLIMITED
 30 ENABLE PLUGGABLE DATABASE
 31 SEED
 32 FILE_NAME_CONVERT = ('/u01/app/oracle/oradata/newcdb/',
 33 '/u01/app/oracle/oradata/newcdb/pdbseed/')
 34 SYSTEM DATAFILES SIZE 125M AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED
 35 SYSAUX DATAFILES SIZE 100M
 36 ;
Database created.
Elapsed: 00:01:15.92

 

 

— Validate if the database created is a CDB

SQL> SELECT dbid, name, created, log_mode, open_mode, cdb, con_id FROM v$database;
 DBID NAME CREATED LOG_MODE OPEN_MODE CDB CON_ID
---------- --------- --------- ------------ -------------------- --- ----------
 46140884 NEWCDB 10-OCT-17 NOARCHIVELOG READ WRITE YES 0

— Validate if the seed database is created

SQL> SELECT con_id, dbid, name, open_mode, restricted, TO_CHAR(open_time, 'DD-MON-YY HH:MI:SS AM') OPEN_TIME FROM v$containers;
 CON_ID DBID NAME OPEN_MODE RES OPEN_TIME
---------- ---------- -------- ---------- --- ------------------------------
 1 46140884 CDB$ROOT READ WRITE NO 10-OCT-17 10:36:54 AM
 2 3138122242 PDB$SEED READ ONLY NO 10-OCT-17 10:36:54 AM

 

 

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

SELECT c.name DB_NAME, a.name TABLESPACE_NAME, b.name DATA_FILE_NAME, b.bytes/1024/1024 SIZE_MB, b.status
 -- c.name DB_NAME, a.con_id, a.ts#, a.name TABLESPACE_NAME, b.name DATA_FILE_NAME, b.file#, b.blocks, b.bytes/1024/1024 SIZE_MB, b.status, b.enabled, b.creation_time
 FROM v$tablespace a, v$datafile b, v$containers c
 WHERE a.con_id = b.con_id
 AND a.con_id = c.con_id
 AND a.ts# = b.ts#
 ORDER BY a.con_id, a.TS#;

 

 

— Check Control files

SQL> show parameter control_files
NAME TYPE VALUE
-------------- -------- ------------------------------
control_files string /u01/app/oracle/oradata/newcdb/control01.ctl, /u01/app/oracle/fast_recovery_area/newcdb/control02.ctl

— Check Redo log files

SQL> SELECT a.group#, a.members, a.bytes/1024/1024 SIZE_MB, a.status, b.member
 FROM v$log a, v$logfile b
 WHERE a.group# = b.group#;
 GROUP# MEMBERS SIZE_MB STATUS MEMBER
---------- ---------- ---------- ---------- ----------------------------------------
 1 2 100 CURRENT /u01/app/oracle/oradata/newcdb/redo01a.log
 1 2 100 CURRENT /u02/app/oracle/oradata/newcdb/redo01b.log
 2 2 100 UNUSED /u01/app/oracle/oradata/newcdb/redo02a.log
 2 2 100 UNUSED /u02/app/oracle/oradata/newcdb/redo02b.log
 3 2 100 UNUSED /u01/app/oracle/oradata/newcdb/redo03a.log
 3 2 100 UNUSED /u02/app/oracle/oradata/newcdb/redo03b.log

 

 

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

SQL> SELECT * FROM v$nls_parameters WHERE parameter LIKE '%CHARACTERSET%';
PARAMETER VALUE CON_ID
------------------------- --------------- ------
NLS_CHARACTERSET AL32UTF8 0
NLS_NCHAR_CHARACTERSET AL16UTF16 0

 

 

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.

# running catalog.sql script against CDB containers
$ORACLE_HOME/perl/bin/perl $ORACLE_HOME/rdbms/admin/catcon.pl -u sys -d $ORACLE_HOME/rdbms/admin -b catalog_output -e -l /home/oracle catalog.sql

# running catproc.sql script against CDB containers
$ORACLE_HOME/perl/bin/perl $ORACLE_HOME/rdbms/admin/catcon.pl -u sys -d $ORACLE_HOME/rdbms/admin -b catproc_output -e -l /home/oracle catproc.sql

# running pupbld.sql script against CDB containers
$ORACLE_HOME/perl/bin/perl $ORACLE_HOME/rdbms/admin/catcon.pl -u system -d $ORACLE_HOME/sqlplus/admin -b pupbld_output -e -l /home/oracle pupbld.sql

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

 

 

— Query the status of database components

col comp_name for a40
col version for a15
col status for a10
SELECT name, comp_name, version, status FROM v$database, dba_registry;
NAME COMP_NAME VERSION STATUS
--------- ---------------------------------------- --------------- ----------
newcdb Oracle XML Database 12.1.0.2.0 VALID
newcdb Oracle Database Catalog Views 12.1.0.2.0 INVALID
newcdb Oracle Database Packages and Types 12.1.0.2.0 INVALID

 

 

— Recompile all the INVALID objects

@?/rdbms/admin/utlrp.sql

 

 

— Query the status of database components again

col comp_name for a40
col version for a15
col status for a10
SELECT name, comp_name, version, status FROM v$database, dba_registry;
NAME COMP_NAME VERSION STATUS
--------- ---------------------------------------- --------------- ----------
newcdb Oracle XML Database 12.1.0.2.0 VALID
newcdb Oracle Database Catalog Views 12.1.0.2.0 VALID
newcdb Oracle Database Packages and Types 12.1.0.2.0 VALID

 

 

10. Create a PDB

-- Creating PDB mypdb_01 using seed database (PDB$SEED)
CREATE PLUGGABLE DATABASE newpdb1 ADMIN USER pdbadmin IDENTIFIED BY oracle
FILE_NAME_CONVERT=('/u01/app/oracle/oradata/newcdb/pdbseed/','/u01/app/oracle/oradata/newcdb/newpdb1/');
Pluggable database created.

— Open the PDB for READ-WRITE

ALTER PLUGGABLE DATABASE newpdb1 OPEN;
Pluggable database altered.

— Validate the PDB

SELECT name, open_mode, restricted FROM v$pdbs;
NAME OPEN_MODE RES
------------------------------ ---------- ---
PDB$SEED READ ONLY NO
PDB1 READ WRITE NO

 

 

— Validate list of datafiles from each of the containers

-- Query CDB/PDB name, con_id, tablespace name, datafile location, size, and status
SELECT c.name DB_NAME, b.name DATA_FILE_NAME
 -- a.con_id, a.ts#, a.name TABLESPACE_NAME, b.name DATA_FILE_NAME, b.file#, b.blocks, b.bytes/1024/1024 SIZE_MB, b.status, b.enabled, b.creation_time
 FROM v$tablespace a, v$datafile b, v$containers c
 WHERE a.con_id = b.con_id
 AND a.con_id = c.con_id
 AND a.ts# = b.ts#
 ORDER BY a.con_id, a.TS#;

DB_NAME DATA_FILE_NAME
---------- --------------------------------------------------
CDB$ROOT /u01/app/oracle/oradata/newcdb/system01.dbf
CDB$ROOT /u01/app/oracle/oradata/newcdb/sysaux01.dbf
CDB$ROOT /u01/app/oracle/oradata/newcdb/undotbs01.dbf
CDB$ROOT /u01/app/oracle/oradata/newcdb/users01.dbf
PDB$SEED /u01/app/oracle/oradata/newcdb/pdbseed/system01.dbf
PDB$SEED /u01/app/oracle/oradata/newcdb/pdbseed/sysaux01.dbf
PDB$SEED /u01/app/oracle/oradata/newcdb/pdbseed/users01.dbf
PDB1 /u01/app/oracle/oradata/newcdb/pdb1/system01.dbf
PDB1 /u01/app/oracle/oradata/newcdb/pdb1/sysaux01.dbf
PDB1 /u01/app/oracle/oradata/newcdb/pdb1/users01.dbf

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!

Like Loading…

Measure

Measure