How To Create Container Database In Oracle 12c Using Dbca
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!
How To Create Container Database In Oracle 12c Using Dbca
Source: https://marvinprudente.wordpress.com/2017/10/10/oracle-12c-create-a-container-database-cdb-manually-with-create-database-script/
Posted by: crowleytrie1968.blogspot.com
0 Response to "How To Create Container Database In Oracle 12c Using Dbca"
Post a Comment