Oracle 12c: Create a Container Database manually
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