You can use DBCA or manual scripts to create a container database (CDB).
However in both cases these tools will create a database with all database options like JServer, APEX, etc. I don't think that it is possible to use DBCA in graphical mode to create a database without any option. I don't know if you can use CREATE DATABASE SQL statement with catcdb.sql to create such a database.
Mike Dietrich has published on his upgrade blog a way to create a CDB without any database option using DBCA with a mix of graphical steps and manual editing of generated files: this way of doing is supported by Oracle Corp.
In this blog post I show that it is possible to use DBCA in command line mode to create a CDB without any database option.
For this article I have used Oracle Database Entreprise Edition 12.1.0.2 (without any PSU) on Oracle Linux 6.4 (VirtualBox 5.0.4).
I have coded a script named dbca.sh which does the following:
SYS@CNODB>create spfile='+DATA' FROM pfile='/u01/app/oracle/admin/CNODB/scripts/init.ora'; File created.
At next instance startup fails:
SYS@CNODB>startup ; ORA-01078: failure in processing system parameters ORA-01565: error in identifying file '+DATA/CNODB/spfileCNODB.ora' ORA-17503: ksfdopn:2 Failed to open file +DATA/CNODB/spfileCNODB.ora ORA-15056: additional error message ORA-17503: ksfdopn:2 Failed to open file +DATA/CNODB/spfilecnodb.ora ORA-15173: entry 'spfilecnodb.ora' does not exist in directory 'CNODB' ORA-06512: at line 4
My script dbca.sh has following prerequisites:
Following items are hard-coded:
The script source code is here.
Here is the script output (it has taken about 35 minutes to created the database in my environment):
$ ./dbca.sh -dbname CDB -cset AL32UTF8 -password oracle -dgname DATA -rgname FRA -mempc 20
Database creation script generation
1% complete
3% complete
7% complete
8% complete
10% complete
12% complete
13% complete
14% complete
15% complete
17% complete
23% complete
28% complete
33% complete
35% complete
36% complete
37% complete
39% complete
40% complete
50% complete
53% complete
54% complete
60% complete
67% complete
71% complete
75% complete
82% complete
89% complete
90% complete
93% complete
96% complete
100% complete
Look at the log file "/u01/app/oracle/admin/CDB/scripts/CDB.log" for further details.
You should Add this entry in the /etc/oratab: CDB:/u01/app/12.1.0.2/db:Y
SQL*Plus: Release 12.1.0.2.0 Production on Wed Nov 11 12:00:23 2015
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Connected to an idle instance.
SYS@CDB>spool /u01/app/oracle/admin/CDB/scripts/CreateDB.log append
SYS@CDB>startup nomount pfile="/u01/app/oracle/admin/CDB/scripts/init.ora";
ORACLE instance started.
Total System Global Area 830472192 bytes
Fixed Size 2929840 bytes
Variable Size 624954192 bytes
Database Buffers 197132288 bytes
Redo Buffers 5455872 bytes
SYS@CDB>CREATE DATABASE "CDB"
2 MAXINSTANCES 8
3 MAXLOGHISTORY 1
4 MAXLOGFILES 16
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 GROUP 3 SIZE 50M
17 USER SYS IDENTIFIED BY "&&sysPassword" USER SYSTEM IDENTIFIED BY "&&systemPassword"
18 enable pluggable database;
Database created.
SYS@CDB>set linesize 2048;
SYS@CDB>column ctl_files NEW_VALUE ctl_files;
SYS@CDB>select concat('control_files=''', concat(replace(value, ', ', ''','''), '''')) ctl_files from v$parameter where name ='control_files';
CTL_FILES
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
control_files='+DATA/CDB/CONTROLFILE/current.288.895492833','+FRA/CDB/CONTROLFILE/current.258.895492833'
SYS@CDB>host echo &ctl_files >>/u01/app/oracle/admin/CDB/scripts/init.ora;
SYS@CDB>spool off
SYS@CDB>@/u01/app/oracle/admin/CDB/scripts/CreateDBFiles.sql
SYS@CDB>SET VERIFY OFF
SYS@CDB>connect "SYS"/"&&sysPassword" as SYSDBA
Connected.
SYS@CDB>set echo on
SYS@CDB>spool /u01/app/oracle/admin/CDB/scripts/CreateDBFiles.log append
SYS@CDB>CREATE SMALLFILE TABLESPACE "USERS" LOGGING DATAFILE SIZE 5M AUTOEXTEND ON NEXT 1280K MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;
Tablespace created.
SYS@CDB>ALTER DATABASE DEFAULT TABLESPACE "USERS";
Database altered.
SYS@CDB>spool off
SYS@CDB>@/u01/app/oracle/admin/CDB/scripts/CreateDBCatalog.sql
SYS@CDB>SET VERIFY OFF
SYS@CDB>connect "SYS"/"&&sysPassword" as SYSDBA
Connected.
SYS@CDB>set echo on
SYS@CDB>spool /u01/app/oracle/admin/CDB/scripts/CreateDBCatalog.log append
SYS@CDB>alter session set "_oracle_script"=true;
Session altered.
SYS@CDB>alter pluggable database pdb$seed close;
Pluggable database altered.
SYS@CDB>alter pluggable database pdb$seed open;
Pluggable database altered.
SYS@CDB>host perl /u01/app/12.1.0.2/db/rdbms/admin/catcon.pl -n 1 -l /u01/app/oracle/admin/CDB/scripts -b catalog /u01/app/12.1.0.2/db/rdbms/admin/catalog.sql;
catcon: ALL catcon-related output will be written to /u01/app/oracle/admin/CDB/scripts/catalog_catcon_13257.lst
catcon: See /u01/app/oracle/admin/CDB/scripts/catalog*.log files for output generated by scripts
catcon: See /u01/app/oracle/admin/CDB/scripts/catalog_*.lst files for spool files, if any
catcon.pl: completed successfully
SYS@CDB>host perl /u01/app/12.1.0.2/db/rdbms/admin/catcon.pl -n 1 -l /u01/app/oracle/admin/CDB/scripts -b catproc /u01/app/12.1.0.2/db/rdbms/admin/catproc.sql;
catcon: ALL catcon-related output will be written to /u01/app/oracle/admin/CDB/scripts/catproc_catcon_13342.lst
catcon: See /u01/app/oracle/admin/CDB/scripts/catproc*.log files for output generated by scripts
catcon: See /u01/app/oracle/admin/CDB/scripts/catproc_*.lst files for spool files, if any
catcon.pl: completed successfully
SYS@CDB>host perl /u01/app/12.1.0.2/db/rdbms/admin/catcon.pl -n 1 -l /u01/app/oracle/admin/CDB/scripts -b catoctk /u01/app/12.1.0.2/db/rdbms/admin/catoctk.sql;
catcon: ALL catcon-related output will be written to /u01/app/oracle/admin/CDB/scripts/catoctk_catcon_14163.lst
catcon: See /u01/app/oracle/admin/CDB/scripts/catoctk*.log files for output generated by scripts
catcon: See /u01/app/oracle/admin/CDB/scripts/catoctk_*.lst files for spool files, if any
catcon.pl: completed successfully
SYS@CDB>
SYS@CDB>host perl /u01/app/12.1.0.2/db/rdbms/admin/catcon.pl -n 1 -l /u01/app/oracle/admin/CDB/scripts -b pupbld -u SYSTEM/&&systemPassword /u01/app/12.1.0.2/db/sqlplus/admin/pupbld.sql;
catcon: ALL catcon-related output will be written to /u01/app/oracle/admin/CDB/scripts/pupbld_catcon_14227.lst
catcon: See /u01/app/oracle/admin/CDB/scripts/pupbld*.log files for output generated by scripts
catcon: See /u01/app/oracle/admin/CDB/scripts/pupbld_*.lst files for spool files, if any
catcon.pl: completed successfully
SYS@CDB>connect "SYSTEM"/"&&systemPassword"
Connected.
SYSTEM@CDB>set echo on
SYSTEM@CDB>spool /u01/app/oracle/admin/CDB/scripts/sqlPlusHelp.log append
SYSTEM@CDB>host perl /u01/app/12.1.0.2/db/rdbms/admin/catcon.pl -n 1 -l /u01/app/oracle/admin/CDB/scripts -b hlpbld -u SYSTEM/&&systemPassword -a 1 /u01/app/12.1.0.2/db/sqlplus/admin/help/hlpbld.sql 1helpus.sql;
catcon: ALL catcon-related output will be written to /u01/app/oracle/admin/CDB/scripts/hlpbld_catcon_14320.lst
catcon: See /u01/app/oracle/admin/CDB/scripts/hlpbld*.log files for output generated by scripts
catcon: See /u01/app/oracle/admin/CDB/scripts/hlpbld_*.lst files for spool files, if any
catcon.pl: completed successfully
SYSTEM@CDB>spool off
SYSTEM@CDB>spool off
not spooling currently
SYSTEM@CDB>--
SYSTEM@CDB>--
SYSTEM@CDB>--
SYSTEM@CDB>--
SYSTEM@CDB>--
SYSTEM@CDB>--
SYSTEM@CDB>--
SYSTEM@CDB>--
SYSTEM@CDB>--
SYSTEM@CDB>@/u01/app/oracle/admin/CDB/scripts/CreateClustDBViews.sql
SYSTEM@CDB>SET VERIFY OFF
SYSTEM@CDB>connect "SYS"/"&&sysPassword" as SYSDBA
Connected.
SYS@CDB>set echo on
SYS@CDB>spool /u01/app/oracle/admin/CDB/scripts/CreateClustDBViews.log append
SYS@CDB>host perl /u01/app/12.1.0.2/db/rdbms/admin/catcon.pl -n 1 -l /u01/app/oracle/admin/CDB/scripts -b catclust /u01/app/12.1.0.2/db/rdbms/admin/catclust.sql;
catcon: ALL catcon-related output will be written to /u01/app/oracle/admin/CDB/scripts/catclust_catcon_14387.lst
catcon: See /u01/app/oracle/admin/CDB/scripts/catclust*.log files for output generated by scripts
catcon: See /u01/app/oracle/admin/CDB/scripts/catclust_*.lst files for spool files, if any
catcon.pl: completed successfully
SYS@CDB>spool off
SYS@CDB>connect "SYS"/"&&sysPassword" as SYSDBA
Connected.
SYS@CDB>set echo on
SYS@CDB>spool /u01/app/oracle/admin/CDB/scripts/postDBCreation.log append
SYS@CDB>grant sysdg to sysdg;
Grant succeeded.
SYS@CDB>grant sysbackup to sysbackup;
Grant succeeded.
SYS@CDB>grant syskm to syskm;
Grant succeeded.
SYS@CDB>host echo "SPFILE='+DATA/CDB/spfileCDB.ora'" > /u01/app/12.1.0.2/db/dbs/initCDB.ora
SYS@CDB>@/u01/app/oracle/admin/CDB/scripts/lockAccount.sql
SYS@CDB>SET VERIFY OFF
SYS@CDB>connect "SYS"/"&&sysPassword" as SYSDBA
Connected.
SYS@CDB>set echo on
SYS@CDB>spool /u01/app/oracle/admin/CDB/scripts/lockAccount.log append
SYS@CDB>alter session set "_oracle_script"=true;
Session altered.
SYS@CDB>alter pluggable database pdb$seed close;
Pluggable database altered.
SYS@CDB>alter pluggable database pdb$seed open;
Pluggable database altered.
SYS@CDB>BEGIN
2 FOR item IN ( SELECT USERNAME FROM DBA_USERS WHERE ACCOUNT_STATUS IN ('OPEN', 'LOCKED', 'EXPIRED') AND USERNAME NOT IN (
3 'SYS','SYSTEM') )
4 LOOP
5 dbms_output.put_line('Locking and Expiring: ' || item.USERNAME);
6 execute immediate 'alter user ' ||
7 sys.dbms_assert.enquote_name(
8 sys.dbms_assert.schema_name(
9 item.USERNAME),false) || ' password expire account lock' ;
10 END LOOP;
11 END;
12 /
PL/SQL procedure successfully completed.
SYS@CDB>alter session set container=pdb$seed;
Session altered.
SYS@CDB>BEGIN
2 FOR item IN ( SELECT USERNAME FROM DBA_USERS WHERE ACCOUNT_STATUS IN ('OPEN', 'LOCKED', 'EXPIRED') AND USERNAME NOT IN (
3 'SYS','SYSTEM') )
4 LOOP
5 dbms_output.put_line('Locking and Expiring: ' || item.USERNAME);
6 execute immediate 'alter user ' ||
7 sys.dbms_assert.enquote_name(
8 sys.dbms_assert.schema_name(
9 item.USERNAME),false) || ' password expire account lock' ;
10 END LOOP;
11 END;
12 /
PL/SQL procedure successfully completed.
SYS@CDB>alter session set container=cdb$root;
Session altered.
SYS@CDB>spool off
SYS@CDB>@/u01/app/oracle/admin/CDB/scripts/postDBCreation.sql
SYS@CDB>SET VERIFY OFF
SYS@CDB>spool /u01/app/oracle/admin/CDB/scripts/postDBCreation.log append
SYS@CDB>host perl /u01/app/12.1.0.2/db/rdbms/admin/catcon.pl -n 1 -l /u01/app/oracle/admin/CDB/scripts -b catbundleapply /u01/app/12.1.0.2/db/rdbms/admin/catbundleapply.sql;
catcon: ALL catcon-related output will be written to /u01/app/oracle/admin/CDB/scripts/catbundleapply_catcon_14464.lst
catcon: See /u01/app/oracle/admin/CDB/scripts/catbundleapply*.log files for output generated by scripts
catcon: See /u01/app/oracle/admin/CDB/scripts/catbundleapply_*.lst files for spool files, if any
catcon.pl: completed successfully
SYS@CDB>connect "SYS"/"&&sysPassword" as SYSDBA
Connected.
SYS@CDB>set echo on
SYS@CDB>create spfile='+DATA/CDB/spfileCDB.ora' FROM pfile='/u01/app/oracle/admin/CDB/scripts/init.ora';
File created.
SYS@CDB>connect "SYS"/"&&sysPassword" as SYSDBA
Connected.
SYS@CDB>host perl /u01/app/12.1.0.2/db/rdbms/admin/catcon.pl -n 1 -l /u01/app/oracle/admin/CDB/scripts -b utlrp /u01/app/12.1.0.2/db/rdbms/admin/utlrp.sql;
catcon: ALL catcon-related output will be written to /u01/app/oracle/admin/CDB/scripts/utlrp_catcon_14535.lst
catcon: See /u01/app/oracle/admin/CDB/scripts/utlrp*.log files for output generated by scripts
catcon: See /u01/app/oracle/admin/CDB/scripts/utlrp_*.lst files for spool files, if any
catcon.pl: completed successfully
SYS@CDB>select comp_id, status from dba_registry;
COMP_ID STATUS
------------------------------ --------------------------------------------
XDB VALID
CATALOG VALID
CATPROC VALID
RAC OPTION OFF
SYS@CDB>shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SYS@CDB>connect "SYS"/"&&sysPassword" as SYSDBA
Connected to an idle instance.
SYS@CDB>startup ;
ORACLE instance started.
Total System Global Area 830472192 bytes
Fixed Size 2929840 bytes
Variable Size 633342800 bytes
Database Buffers 188743680 bytes
Redo Buffers 5455872 bytes
Database mounted.
Database opened.
SYS@CDB>spool off
SYS@CDB>exit;
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options
CDB successfully created..
Because /etc/oratab is writable, dbca.sh has updated it:
$ tail -2 /etc/oratab +ASM:/u01/app/12.1.0.2/grid:N # line added by Agent CDB:/u01/app/12.1.0.2/db:N # added by dbca.sh $
login.sql has been restored by dbca.sh:
$ cat login.sql set sqlprompt "&&_USER@&&_CONNECT_IDENTIFIER>"
I have checked the created database with following SQL statements:
SYS@CDB>select name, cdb from v$database;
NAME CDB
--------- ---
CDB YES
SYS@CDB>select comp_id, comp_name, version, status from dba_registry;
COMP_ID COMP_NAME VERSION STATUS
--------------- ---------------------------------------- ---------- ----------
XDB Oracle XML Database 12.1.0.2.0 VALID
CATALOG Oracle Database Catalog Views 12.1.0.2.0 VALID
CATPROC Oracle Database Packages and Types 12.1.0.2.0 VALID
RAC Oracle Real Application Clusters 12.1.0.2.0 OPTION OFF
SYS@CDB>show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
SYS@CDB>select * from v$pwfile_users;
USERNAME SYSDB SYSOP SYSAS SYSBA SYSDG SYSKM CON_ID
------------------------------ ----- ----- ----- ----- ----- ----- ----------
SYS TRUE TRUE FALSE FALSE FALSE FALSE 0
SYSDG FALSE FALSE FALSE FALSE TRUE FALSE 1
SYSBACKUP FALSE FALSE FALSE TRUE FALSE FALSE 1
SYSKM FALSE FALSE FALSE FALSE FALSE TRUE 1
SYS@CDB>
Note that XDB is a mandatory component in Oracle Database 12c.
Database password file has been created in $ORACLE_HOME/dbs:
$ ls -al $ORACLE_HOME/dbs/orapwCDB -rw-r----- 1 oracle oinstall 7680 Nov 11 12:34 /u01/app/12.1.0.2/db/dbs/orapwCDB
Oracle Restart local registry has also been updated by dbca.sh:
$ srvctl config database -d CDB Database unique name: CDB Database name: Oracle home: /u01/app/12.1.0.2/db Oracle user: oracle Spfile: +DATA/CDB/spfileCDB.ora Password file: Domain: Start options: open Stop options: immediate Database role: PRIMARY Management policy: AUTOMATIC Disk Groups: DATA,FRA Services: OSDBA group: OSOPER group: Database instance: CDB
I have created a pluggable database (PDB)with:
SYS@CDB>create pluggable database PDB
2 admin user pdba identified by oracle
3 default tablespace ts_pdb;
Pluggable database created.
SYS@CDB>alter pluggable database pdb open;
Pluggable database altered.
SYS@CDB>show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDB READ WRITE NO
SYS@CDB>
I have also checked that corresponding database service has been registered to Grid Infrastructure listener:
$ . oraenv ORACLE_SID = [CDB] ? +ASM The Oracle base remains unchanged with value /u01/app/oracle $ lsnrctl status LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 11-NOV-2015 12:40:25 Copyright (c) 1991, 2014, Oracle. All rights reserved. Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521))) STATUS of the LISTENER ------------------------ Alias LISTENER Version TNSLSNR for Linux: Version 12.1.0.2.0 - Production Start Date 11-NOV-2015 08:53:35 Uptime 0 days 3 hr. 46 min. 49 sec Trace Level off Security ON: Local OS Authentication SNMP OFF Listener Parameter File /u01/app/12.1.0.2/grid/network/admin/listener.ora Listener Log File /u01/app/oracle/diag/tnslsnr/ol6twsa/listener/alert/log.xml Listening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521))) (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=ol6twsa)(PORT=1521))) Services Summary... Service "+ASM" has 1 instance(s). Instance "+ASM", status READY, has 1 handler(s) for this service... Service "CDB" has 1 instance(s). Instance "CDB", status READY, has 1 handler(s) for this service... Service "CDBXDB" has 1 instance(s). Instance "CDB", status READY, has 1 handler(s) for this service... Service "pdb" has 1 instance(s). Instance "CDB", status READY, has 1 handler(s) for this service... The command completed successfully
Remember that in this container database you cannot create or plug a PDB that is using a database option that is not installed in the root container.