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 In 12.1.0.x.
A. DBCA to create 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“.
2) Select the Advanced Mode.
3) Choose right “Database Type” and “Configuration Type”.
Select the Custom Database from the list of database templates.
4) Give Database Name, PDB name.
5) Select the available nodes.
6) Tick the box.
7) Give database credentials.
8) Specify storage choices.
9) Database options. Installing all options is compulsory.
10) Database Vault and Label Security options.
11) Oracle database parameters.
12) Don’t choose “Create database”.
13) Prerequisites check progressing….
14) Review the SUMMARY, and click the “Close” when it finishes.
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.
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
C. Create CDB database by running shell scripts
$./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
- Rerun the script RACTEST1.sh, and recreated the CDB database successfully.
- Added TEXT option by using DBCA as below:
D. Check the selected options 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: 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
E. 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;
REFERENCES
Creating A Container Database (CDB) With A Subset Of Options (Doc ID 2001512.1)
Customization of Database Options in a Multitenant Setup ( Doc ID: 1616554.1)
Make DBA Life Easy 