Manually Create a Container Database (CDB) With Selected Options

Creating a container database ( CDB) by using DBCA  will install all the options by default. There is no way to install selective options.

Here is a practical example of manually creating a CDB with selective options — Oracle JVM, TEXT, SPATIAL.

A. Using DBCA to create the CDB creation scripts.

Add $ORACLE_HOME/perl/bin to the beginning of the PATH variable.
Otherwise there may be compilation errors in catcon.pm because the default perl installation doesn’t have enough packages.

export PATH=$ORACLE_HOME/perl/bin:$PATH 

1) Kick off $ORACLE_HOME/bin/dbca, choose “Create Database”

c-1

2) Select the Advanced Mode.

c-2

3) Choose right “Database Type” and “Configuration Type”. Select the Custom Database from the list of database templates.

c-3

4) Give Database Name, PDB name.

c-4

5) Select the available nodes.

c-5

6) Tick the box.

c-6

7) Give database credentials.

c-7

8) Specify storage choices.

c-8

9) Database options. Installing all options is compulsory.

c-9

10) Database Vault and Label Security options.

109

11) Oracle database parameters.

c-11_1

c-11_3

12) Don’t choose “Create database”.

c-12

13) Prerequisites check progressing….

c-13

14) Review the SUMMARY, and click the “close” when it finishes.

c-15

B. Review the scripts.

1)Shell scripts and sql scripts generated by DBCA.

$ cd /u01/app/oracle/admin/RACTEST/scripts
$ ls -ltr
-rw-r----- 1 oracle oinstall 329 Oct 24 11:25 spatial.sql
-rw-r----- 1 oracle oinstall 354 Oct 24 11:25 ordinst.sql
-rw-r----- 1 oracle oinstall 1154 Oct 24 11:25 lockAccount.sql
-rwxr-xr-x 1 oracle oinstall 699 Oct 24 11:25 RACTEST1.sh
-rw-r----- 1 oracle oinstall 338 Oct 24 11:25 labelSecurity.sql
-rw-r----- 1 oracle oinstall 1145 Oct 24 11:25 JServer.sql
-rw-r----- 1 oracle oinstall 336 Oct 24 11:25 interMedia.sql
-rw-r----- 1 oracle oinstall 359 Oct 24 11:25 datavault.sql
-rw-r----- 1 oracle oinstall 347 Oct 24 11:25 cwmlite.sql
-rw-r----- 1 oracle oinstall 1636 Oct 24 11:25 CreateDBCatalog.sql
-rw-r----- 1 oracle oinstall 543 Oct 24 11:25 CreateClustDBViews.sql
-rw-r----- 1 oracle oinstall 982 Oct 24 11:25 context.sql
-rw-r----- 1 oracle oinstall 380 Oct 24 11:25 apex.sql
-rw-r----- 1 oracle oinstall 392 Oct 24 11:25 postPDBCreation_RACTESTPDB.sql
-rw-r----- 1 oracle oinstall 1899 Oct 24 11:25 postDBCreation.sql
-rw-r----- 1 oracle oinstall 453 Oct 24 11:25 plug_RACTESTPDB.sql
-rw-r----- 1 oracle oinstall 94 Oct 24 11:25 PDBCreation.sql
-rwxr-xr-x 1 oracle oinstall 2310 Oct 24 17:25 RACTEST1.sql
-rw-r----- 1 oracle oinstall 1166 Oct 24 22:26 CreateDB.sql
-rw-r----- 1 oracle oinstall 2904 Oct 24 23:24 init.ora
-rw-r----- 1 oracle oinstall 675 Oct 26 14:46 CreateDBFiles.sql

2) Review the options and their related scripts.

capture

3) Review the parent sql script RACTEST1.sql. Comments out the unwanted options.

$ cat RACTEST1.sql
set verify off
ACCEPT sysPassword CHAR PROMPT 'Enter new password for SYS: ' HIDE
ACCEPT systemPassword CHAR PROMPT 'Enter new password for SYSTEM: ' HIDE
ACCEPT dbsnmpPassword CHAR PROMPT 'Enter new password for DBSNMP: ' HIDE
host /u01/app/oracle/product/12.1.0/dbhome_2/bin/orapwd file=+DATA2/RACTEST/orapwRACTEST force=y format=12 dbuniquename=RACTEST
host /u01/app/12.1.0.2/grid/bin/setasmgidwrap o=/u01/app/oracle/product/12.1.0/dbhome_2/bin/oracle
host /u01/app/oracle/product/12.1.0/dbhome_2/bin/srvctl add database -d RACTEST -pwfile +DATA2/RACTEST/orapwRACTEST -o /u01/app/oracle/product/12.1.0/dbhome_2 -p +DATA2/RACTEST/spfileRACTEST.ora -n RACTEST -a "DATA2,FRA"
host /u01/app/oracle/product/12.1.0/dbhome_2/bin/srvctl add instance -d RACTEST -i RACTEST1 -n racnode1
host /u01/app/oracle/product/12.1.0/dbhome_2/bin/srvctl add instance -d RACTEST -i RACTEST2 -n racnode2
host /u01/app/oracle/product/12.1.0/dbhome_2/bin/srvctl add instance -d RACTEST -i RACTEST3 -n racnode3
host /u01/app/oracle/product/12.1.0/dbhome_2/bin/srvctl add instance -d RACTEST -i RACTEST4 -n racnode4
host /u01/app/oracle/product/12.1.0/dbhome_2/bin/srvctl disable database -d RACTEST
@/u01/app/oracle/admin/RACTEST/scripts/CreateDB.sql
@/u01/app/oracle/admin/RACTEST/scripts/CreateDBFiles.sql
@/u01/app/oracle/admin/RACTEST/scripts/CreateDBCatalog.sql
@/u01/app/oracle/admin/RACTEST/scripts/JServer.sql
@/u01/app/oracle/admin/RACTEST/scripts/context.sql
@/u01/app/oracle/admin/RACTEST/scripts/ordinst.sql
@/u01/app/oracle/admin/RACTEST/scripts/interMedia.sql
--@/u01/app/oracle/admin/RACTEST/scripts/cwmlite.sql
@/u01/app/oracle/admin/RACTEST/scripts/spatial.sql
--@/u01/app/oracle/admin/RACTEST/scripts/labelSecurity.sql
--@/u01/app/oracle/admin/RACTEST/scripts/apex.sql
--@/u01/app/oracle/admin/RACTEST/scripts/datavault.sql
@/u01/app/oracle/admin/RACTEST/scripts/CreateClustDBViews.sql
host echo "SPFILE='+DATA2/RACTEST/spfileRACTEST.ora'" > /u01/app/oracle/product/12.1.0/dbhome_2/dbs/initRACTEST1.ora
@/u01/app/oracle/admin/RACTEST/scripts/lockAccount.sql
@/u01/app/oracle/admin/RACTEST/scripts/postDBCreation.sql
@/u01/app/oracle/admin/RACTEST/scripts/PDBCreation.sql
@/u01/app/oracle/admin/RACTEST/scripts/plug_RACTESTPDB.sql
@/u01/app/oracle/admin/RACTEST/scripts/postPDBCreation_RACTESTPDB.sql

B. Create CDB database by running shell script RACTEST1.sh.

$./RACTEST1.sh
You should Add this entry in the /etc/oratab: RACTEST:/u01/app/oracle/product/12.1.0/dbhome_1:Y

SQL*Plus: Release 12.1.0.2.0 Production on Mon Oct 24 11:35:48 2016

Copyright (c) 1982, 2014, Oracle. All rights reserved.

Enter new password for SYS:
Enter new password for SYSTEM:
Enter new password for DBSNMP:

Enter password for SYS:

OPW-00019: Failed to update the CRS resource with DB password file location

Connected to an idle instance.
SQL> spool /u01/app/oracle/admin/RACTEST/scripts/CreateDB.log append
SQL> startup nomount pfile="/u01/app/oracle/admin/RACTEST/scripts/init.ora";
ORACLE instance started.

Total System Global Area 1.7180E+10 bytes
Fixed Size 7663544 bytes
Variable Size 3053453384 bytes
Database Buffers 1.4093E+10 bytes
Redo Buffers 25890816 bytes
SQL> CREATE DATABASE "RACTEST"
 2 MAXINSTANCES 32
 3 MAXLOGHISTORY 1
 4 MAXLOGFILES 192
 5 MAXLOGMEMBERS 3
 6 MAXDATAFILES 1024
 7 DATAFILE SIZE 700M AUTOEXTEND ON NEXT 10240K MAXSIZE UNLIMITED
 8 EXTENT MANAGEMENT LOCAL
 9 SYSAUX DATAFILE SIZE 550M AUTOEXTEND ON NEXT 10240K MAXSIZE UNLIMITED
 10 SMALLFILE DEFAULT TEMPORARY TABLESPACE TEMP TEMPFILE SIZE 20M AUTOEXTEND ON NEXT 640K MAXSIZE UNLIMITED
 11 SMALLFILE UNDO TABLESPACE "UNDOTBS1" DATAFILE SIZE 200M AUTOEXTEND ON NEXT 5120K MAXSIZE UNLIMITED
 12 CHARACTER SET AL32UTF8
 13 NATIONAL CHARACTER SET AL16UTF16
 14 LOGFILE GROUP 1 SIZE 50M,
 15 GROUP 2 SIZE 50M
 16 USER SYS IDENTIFIED BY "&&sysPassword" USER SYSTEM IDENTIFIED BY "&&systemPassword"
 17 enable pluggable database;

Database created.

......
.....
....
...
..
.

SQL> alter user CTXSYS account unlock identified by "CTXSYS";

User altered.

SQL> connect "CTXSYS"/"CTXSYS"
Connected.
SQL> host perl /u01/app/oracle/product/12.1.0/dbhome_2/rdbms/admin/catcon.pl -n 1 -l /u01/app/oracle/admin/RACTEST/scripts -b dr0defin -u CTXSYS/CTXSYS -a 1 /u01/app/oracle/product/12.1.0/dbhome_2/ctx/admin/defaults/dr0defin.sql 1\"AMERICAN\";
catcon: ALL catcon-related output will be written to /u01/app/oracle/admin/RACTEST/scripts/dr0defin_catcon_24303.lst
catcon: See /u01/app/oracle/admin/RACTEST/scripts/dr0defin*.log files for output generated by scripts
catcon: See /u01/app/oracle/admin/RACTEST/scripts/dr0defin_*.lst files for spool files, if any
catconInit: database is not open on the default instance
Unexpected error encountered in catconInit; exiting

SQL> connect "SYS"/"&&sysPassword" as SYSDBA
Connected.
SQL> alter user CTXSYS password expire account lock;

User altered.
.....
...
..
.

Received two errors, and they are resolved as below:

a) Error “OPW-00019” Creating Password File In ASM For 12c Database (Doc ID 2021520.1).

Old Scripts:

$ /u01/app/oracle/product/12.1.0/dbhome_1/bin/orapwd file=+DATA2/RACTEST/orapwRACTEST force=y format=12 dbuniquename=RACTEST
$ /u01/app/12.1.0.2/grid/bin/setasmgidwrap o=/u01/app/oracle/product/12.1.0/dbhome_1/bin/oracle
$ /u01/app/oracle/product/12.1.0/dbhome_1/bin/srvctl add database -d RACTEST -pwfile +DATA2/RACTEST/orapwRACTEST -o /u01/app/oracle/product/12.1.0/dbhome_1 -p +DATA2/RACTEST/spfileRACTEST.ora -n RACTEST -a "DATA2,FRA"
$ /u01/app/oracle/product/12.1.0/dbhome_1/bin/srvctl add instance -d RACTEST -i RACTEST1 -n racnode1
$ /u01/app/oracle/product/12.1.0/dbhome_1/bin/srvctl add instance -d RACTEST -i RACTEST2 -n racnode2
$ /u01/app/oracle/product/12.1.0/dbhome_1/bin/srvctl add instance -d RACTEST -i RACTEST3 -n racnode3
$ /u01/app/oracle/product/12.1.0/dbhome_1/bin/srvctl add instance -d RACTEST -i RACTEST4 -n racnode4
$ /u01/app/oracle/product/12.1.0/dbhome_1/bin/srvctl disable database -d RACTEST

New Scripts:

$ /u01/app/oracle/product/12.1.0/dbhome_1/bin/srvctl add database -d RACTEST -pwfile +DATA2/RACTEST/orapwRACTEST -o /u01/app/oracle/product/12.1.0/dbhome_1 -p +DATA2/RACTEST/spfileRACTEST.ora -n RACTEST -a "DATA2,FRA"
$ /u01/app/oracle/product/12.1.0/dbhome_1/bin/srvctl add instance -d RACTEST -i RACTEST1 -n racnode1
$ /u01/app/oracle/product/12.1.0/dbhome_1/bin/srvctl add instance -d RACTEST -i RACTEST2 -n racnode2
$ /u01/app/oracle/product/12.1.0/dbhome_1/bin/srvctl add instance -d RACTEST -i RACTEST3 -n racnode3
$ /u01/app/oracle/product/12.1.0/dbhome_1/bin/srvctl add instance -d RACTEST -i RACTEST4 -n racnode4
$ /u01/app/oracle/product/12.1.0/dbhome_1/bin/orapwd file=+DATA2/RACTEST/orapwRACTEST force=y format=12 dbuniquename=RACTEST
$ /u01/app/12.1.0.2/grid/bin/setasmgidwrap o=/u01/app/oracle/product/12.1.0/dbhome_1/bin/oracle
$ /u01/app/oracle/product/12.1.0/dbhome_1/bin/srvctl disable database -d RACTEST

b)  Error when Creating Seed Database – CatconInit: Database Is Not Open On The Default Instance (Doc ID 2149323.1)

SQL> alter user CTXSYS account unlock identified by "CTXSYS";

User altered.

SQL> connect "CTXSYS"/"CTXSYS"
Connected.
SQL> host perl /u01/app/oracle/product/12.1.0/dbhome_2/rdbms/admin/catcon.pl -n 1 -l /u01/app/oracle/admin/RACTEST/scripts -b dr0defin -u CTXSYS/CTXSYS -a 1 /u01/app/oracle/product/12.1.0/dbhome_2/ctx/admin/defaults/dr0defin.sql 1\"AMERICAN\";
catcon: ALL catcon-related output will be written to /u01/app/oracle/admin/RACTEST/scripts/dr0defin_catcon_24303.lst
catcon: See /u01/app/oracle/admin/RACTEST/scripts/dr0defin*.log files for output generated by scripts
catcon: See /u01/app/oracle/admin/RACTEST/scripts/dr0defin_*.lst files for spool files, if any
catconInit: database is not open on the default instance
Unexpected error encountered in catconInit; exiting

Applied patch 20501388, still not working. then

  • Dropped the database.
  • Comment below sql script.
-- @/u01/app/oracle/admin/RACTEST/scripts/context.sql
  • Reran the script  RACTEST1.sh, and recreated the CDB database successfully.
  • Added TEXT option by using DBCA as below:

add_test-1

add_test-3

add_test-4add_test-6

add_test-5

add_test-7

add_test-8

C. Check the required option installed.

SQL> select comp_name,status,version from dba_registry;

COMP_NAME STATUS VERSION
---------------------------------------- -------------------------------------------- ------------------------------
Oracle Text VALID 12.1.0.2.0
Spatial VALID 12.1.0.2.0
Oracle Multimedia VALID 12.1.0.2.0
Oracle Workspace Manager VALID 12.1.0.2.0
Oracle XML Database VALID 12.1.0.2.0
Oracle Database Catalog Views VALID 12.1.0.2.0
Oracle Database Packages and Types VALID 12.1.0.2.0
JServer JAVA Virtual Machine VALID 12.1.0.2.0
Oracle XDK VALID 12.1.0.2.0
Oracle Database Java Packages VALID 12.1.0.2.0
Oracle Real Application Clusters VALID 12.1.0.2.0

11 rows selected.

SQL> select CON_ID,NAME from v$pdbs;

 CON_ID NAME
---------- ------------------------------
 2 PDB$SEED
 3 RACTESTPDB

Note: Although these are part of the default database installation the following options can also be disabled:

– Oracle Workspace Manager:  then comment the execution of owminst.plb in CreateDBCatalog.sql

– Cluster Views (required for the Support of Oracle RAC): then comment the execution of CreateClustDBViews.sql in the file <sid>.sql

D Manually Run “datapatch” utility

SQL> alter system set cluster_database=false scope=spfile;
SQL> shutdown immediate;
SQL> startup upgrade
SQL> alter pluggable database all open upgrade;

$ cd $ORACLE_HOME/OPatch
$ ./datapatch -verbose

SQL Patching tool version 12.1.0.2.0 on Fri Mar 31 10:43:35 2017
Copyright (c) 2015, Oracle. All rights reserved.

Log file for this invocation: /u01/app/oracle/cfgtoollogs/sqlpatch/sqlpatch_43963_2017_03_31_10_43_35/sqlpatch_invocation.log

Connecting to database...OK
Note: Datapatch will only apply or rollback SQL fixes for PDBs
 that are in an open state, no patches will be applied to closed PDBs.
 Please refer to Note: Datapatch: Database 12c Post Patch SQL Automation
 (Doc ID 1585822.1)
Bootstrapping registry and package to current versions...done
Determining current state...done

Current state of SQL patches:
Patch 22139226 (Database PSU 12.1.0.2.160119, Oracle JavaVM Component (Jan2016)):
 Installed in the binary registry only
Bundle series PSU:
 ID 160419 in the binary registry and not installed in any PDB

Adding patches to installation queue and performing prereq checks...
Installation queue:
 For the following PDBs: CDB$ROOT PDB$SEED RACTESTPDB
 Nothing to roll back
 The following patches will be applied:
 22139226 (Database PSU 12.1.0.2.160119, Oracle JavaVM Component (Jan2016))
 22291127 (Database Patch Set Update : 12.1.0.2.160419 (22291127))

Installing patches...
Patch installation complete. Total patches installed: 6

Validating logfiles...
Patch 22139226 apply (pdb CDB$ROOT): SUCCESS
 logfile: /u01/app/oracle/cfgtoollogs/sqlpatch/22139226/19729684/22139226_apply_RACTEST_CDBROOT_2017Mar31_10_45_12.log (no errors)
Patch 22291127 apply (pdb CDB$ROOT): SUCCESS
 logfile: /u01/app/oracle/cfgtoollogs/sqlpatch/22291127/19694308/22291127_apply_RACTEST_CDBROOT_2017Mar31_10_45_28.log (no errors)
Patch 22139226 apply (pdb PDB$SEED): SUCCESS
 logfile: /u01/app/oracle/cfgtoollogs/sqlpatch/22139226/19729684/22139226_apply_RACTEST_PDBSEED_2017Mar31_10_45_47.log (no errors)
Patch 22291127 apply (pdb PDB$SEED): SUCCESS
 logfile: /u01/app/oracle/cfgtoollogs/sqlpatch/22291127/19694308/22291127_apply_RACTEST_PDBSEED_2017Mar31_10_45_56.log (no errors)
Patch 22139226 apply (pdb RACTESTPDB): SUCCESS
 logfile: /u01/app/oracle/cfgtoollogs/sqlpatch/22139226/19729684/22139226_apply_RACTEST_RACTESTPDB_2017Mar31_10_45_47.log (no errors)
Patch 22291127 apply (pdb RACTESTPDB): SUCCESS
 logfile: /u01/app/oracle/cfgtoollogs/sqlpatch/22291127/19694308/22291127_apply_RACTEST_RACTESTPDB_2017Mar31_10_45_55.log (no errors)
SQL Patching tool complete on Fri Mar 31 10:46:13 2017
$/u01/app/oracle/product/12.1.0/dbhome_1/OPatch$

SQL> select * from dba_registry_sqlpatch;

 

 

Advertisements
This entry was posted in 12c new features, CDB/PDB and tagged , , , , , , , , , , , , , , , . Bookmark the permalink.

2 Responses to Manually Create a Container Database (CDB) With Selected Options

  1. Pingback: Install Oracle Revenue Management and Billing ( ORMB ) 2.5.0.1.0 Database | James Huang Oracle DBA Blog

  2. Pingback: Install Oracle Revenue Management and Billing ( ORMB ) 2.5.0.3.0 Database | James Huang Oracle DBA Blog

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s