In my previous blog article I have published a script named dbca.sh to create a 12.1.0.2 container database without any option using DBCA generated scripts.
The generated scripts are using CREATE DATABASE SQL statement and related scripts like catalog.sql and catproc.sql to create the database and this takes some time (about 30 minutes in my environment) and can be considered too slow.
DBCA allows to create a database using 2 different kind of database templates:
- nonseed templates: the database is created mainly SQL scripts
- or using a seed template which is more or less a RMAN database backup.
The seed template is the default way to create a database with DBCA and it is the fastest way to create a database.
The purpose of this article is to show how you can create a DBCA seed template for Oracle 12.1.0.2 Entreprise Edition for Linux x86-64.
I have used Oracle Database 12.1.0.2 Entreprise Edition and Oracle Linux 6 64 bits running in 5.0.4 VirtualBox virtual machine on Windows 8.1.
Creating a seed database template needs only 2 steps:
I have created a new 12.1.0.2 container database using dbca.sh with US7ASCII character:
./dbca.sh -dbname CDB -cset US7ASCII -password oracle -dgname DATA -rgname FRA -mempc 20
I have chosen US7ASCII database character set because it is one of the smallest character set and it should be possible to use this template to create database in many database character sets that are supersets of US7SACII. However I have not tested all cases and I recommend that you test that the character set used by the template can really be used for the target database character sets (note that DBCA does not always report an error or warning in some cases if it cannot use the chosen character set: it may simply - and silently- use another character set).
I have created the DBCA seed template using the database CDB I have just created (note that sourceSID parameter is the ORACLE_SID of the database created at step 1):
$ dbca -createCloneTemplate \ -silent \ -sourceSID CDB \ -templateName CDBNO \ -sysDBAUserName SYS \ -sysDBAPassword oracle Gathering information from the source database 4% complete 8% complete 13% complete 17% complete 22% complete Backup datafiles 28% complete 88% complete Creating template file 100% complete Look at the log file "/u01/app/oracle/cfgtoollogs/dbca/silent.log_2015-11-11_08-26-17-PM" for further details.
The DBCA log file contents is:
$ cat "/u01/app/oracle/cfgtoollogs/dbca/silent.log_2015-11-11_08-26-17-PM" Gathering information from the source database DBCA_PROGRESS : 4% DBCA_PROGRESS : 8% DBCA_PROGRESS : 13% DBCA_PROGRESS : 17% DBCA_PROGRESS : 22% Backup datafiles DBCA_PROGRESS : 28% DBCA_PROGRESS : 88% Creating template file DBCA_PROGRESS : 100% The generation of the clone database template "CDBNO" is successful.
The template has been created in $ORACLE_HOME/assistants/dbca/templates:
$ ls -rtl /u01/app/12.1.0.2/db/assistants/dbca/templates/ total 955368 -rw-r--r-- 1 oracle oinstall 11000 Dec 23 2013 New_Database.dbt -rw-r--r-- 1 oracle oinstall 2379776 Jul 7 2014 example.dmp -rw-r--r-- 1 oracle oinstall 22339584 Jul 7 2014 example01.dfb -rw-r--r-- 1 oracle oinstall 178683904 Jul 7 2014 sampleschema.dfb -rw-r--r-- 1 oracle oinstall 5333 Jul 7 2014 sampleschema.xml -rw-r--r-- 1 oracle oinstall 155189248 Jul 7 2014 pdbseed.dfb -rw-r--r-- 1 oracle oinstall 4045 Jul 7 2014 pdbseed.xml -rw-r--r-- 1 oracle oinstall 319102976 Jul 7 2014 Seed_Database.dfb -rw-r--r-- 1 oracle oinstall 17973248 Jul 7 2014 Seed_Database.ctl -rw-r--r-- 1 oracle oinstall 5028 Jul 7 2014 Data_Warehouse.dbc -rw-r--r-- 1 oracle oinstall 4908 Jul 7 2014 General_Purpose.dbc -rw-r----- 1 oracle dba 194174976 Nov 11 20:27 CDBNO.dfb1 -rw-r----- 1 oracle dba 70418432 Nov 11 20:27 CDBNO.dfb2 -rw-r----- 1 oracle dba 17973248 Nov 11 20:27 CDBNO.ctl -rw-r----- 1 oracle oinstall 5945 Nov 11 20:28 CDBNO.dbc
The template I have created is made up of 4 files whose file name start with CDBNO.
I have successfully tested that I can create a new database in ASM using my template with following script (the template named is the .dbc file and DBCA is using its default templates directory):
dbca -silent \ -createDatabase \ -templateName CDBNO.dbc \ -gdbName CDB2 \ -sid CDB2 \ -SysPassword oracle \ -SystemPassword oracle \ -emConfiguration NONE \ -diskGroupName DATA \ -storageType ASM \ -characterSet AL32UTF8 \ -recoveryGroupName FRA \ -memoryPercentage 20
This script duration is 10 minutes in my environment. Here is the output:
Registering database with Oracle Restart 5% complete Copying database files 7% complete 9% complete 41% complete Creating and starting Oracle instance 43% complete 48% complete 53% complete 57% complete 58% complete 59% complete 62% complete 64% complete Completing Database Creation 68% complete 71% complete 75% complete 85% complete 96% complete 100% complete
I have also checked that created database has the expected properties:
SYS@CDB2>set linesize 100
SYS@CDB2>column comp_id format a15
SYS@CDB2>column version format a10
SYS@CDB2>column comp_name format a40
SYS@CDB2>column status format a10
SYS@CDB2>column parameter format a30
SYS@CDB2>column value format a30
SYS@CDB2>--
SYS@CDB2>select name, cdb from v$database;
NAME CDB
--------- ---
CDB2 YES
SYS@CDB2>show pdbs;
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
SYS@CDB2>--
SYS@CDB2>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@CDB2>--
SYS@CDB2>select * from nls_database_parameters;
PARAMETER VALUE
------------------------------ ------------------------------
NLS_RDBMS_VERSION 12.1.0.2.0
NLS_NCHAR_CONV_EXCP FALSE
NLS_LENGTH_SEMANTICS BYTE
NLS_COMP BINARY
NLS_DUAL_CURRENCY $
NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH.MI.SSXFF AM TZR
NLS_TIME_TZ_FORMAT HH.MI.SSXFF AM TZR
NLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI.SSXFF AM
NLS_TIME_FORMAT HH.MI.SSXFF AM
NLS_SORT BINARY
NLS_DATE_LANGUAGE AMERICAN
PARAMETER VALUE
------------------------------ ------------------------------
NLS_DATE_FORMAT DD-MON-RR
NLS_CALENDAR GREGORIAN
NLS_NUMERIC_CHARACTERS .,
NLS_NCHAR_CHARACTERSET AL16UTF16
NLS_CHARACTERSET AL32UTF8
NLS_ISO_CURRENCY AMERICA
NLS_CURRENCY $
NLS_TERRITORY AMERICA
NLS_LANGUAGE AMERICAN
20 rows selected.
SYS@CDB2>--
SYS@CDB2>column member format a80
SYS@CDB2>select name from v$controlfile;
NAME
----------------------------------------------------------------------------------------------------
+DATA/CDB2/CONTROLFILE/current.303.896645377
+FRA/CDB2/CONTROLFILE/current.423.896645379
SYS@CDB2>select name from v$datafile;
NAME
----------------------------------------------------------------------------------------------------
+DATA/CDB2/DATAFILE/system.302.896645259
+DATA/CDB2/244939806755201BE0534738A8C0CEF3/DATAFILE/system.305.896645243
+DATA/CDB2/DATAFILE/sysaux.286.896645219
+DATA/CDB2/244939806755201BE0534738A8C0CEF3/DATAFILE/sysaux.309.896645321
+DATA/CDB2/DATAFILE/undotbs1.306.896645295
+DATA/CDB2/DATAFILE/users.291.896645295
6 rows selected.
SYS@CDB2>select name from v$tempfile;
NAME
----------------------------------------------------------------------------------------------------
+DATA/CDB2/TEMPFILE/temp.294.896645421
+DATA/CDB2/244939806755201BE0534738A8C0CEF3/TEMPFILE/temp.262.896645429
SYS@CDB2>select group#, member from v$logfile order by group#;
GROUP# MEMBER
---------- --------------------------------------------------------------------------------
1 +DATA/CDB2/ONLINELOG/group_1.304.896645383
1 +FRA/CDB2/ONLINELOG/group_1.452.896645385
2 +DATA/CDB2/ONLINELOG/group_2.301.896645387
2 +FRA/CDB2/ONLINELOG/group_2.451.896645391
3 +DATA/CDB2/ONLINELOG/group_3.300.896645393
3 +FRA/CDB2/ONLINELOG/group_3.446.896645395
6 rows selected.
SYS@CDB2>
DBCA has also updated Oracle Restart local registry:
$ srvctl config database -d CDB2 Database unique name: CDB2 Database name: CDB2 Oracle home: /u01/app/12.1.0.2/db Oracle user: oracle Spfile: +DATA/CDB2/PARAMETERFILE/spfile.261.896645587 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: CDB2
I have successfully tested that I can create a new database on file systems with following steps (assuming /u02/oradata and /u03/orareco are existing directories owned by oracle Linux account) with following script:
dbca -silent \ -createDatabase \ -templateName CDBNO.dbc \ -gdbName CDB3 \ -sid CDB3 \ -SysPassword oracle \ -SystemPassword oracle \ -emConfiguration NONE \ -storageType FS \ -datafileDestination /u02/oradata \ -recoveryAreaDestination /u03/orareco \ -characterSet WE8ISO8859P15 \ -memoryPercentage 20
This script duration is 6 minutes in my environment. Here is the output:
Registering database with Oracle Restart 5% complete Copying database files 7% complete 9% complete 41% complete Creating and starting Oracle instance 43% complete 48% complete 53% complete 57% complete 58% complete 59% complete 62% complete 64% complete Completing Database Creation 68% complete 71% complete 75% complete 85% complete 96% complete 100% complete Look at the log file "/u01/app/oracle/cfgtoollogs/dbca/CDB3/CDB30.log" for further details.
I have also checked that created database has the expected properties:
SYS@CDB3>set linesize 100
SYS@CDB3>column comp_id format a15
SYS@CDB3>column version format a10
SYS@CDB3>column comp_name format a40
SYS@CDB3>column status format a10
SYS@CDB3>column parameter format a30
SYS@CDB3>column value format a30
SYS@CDB3>--
SYS@CDB3>select name, cdb from v$database;
NAME CDB
--------- ---
CDB3 YES
SYS@CDB3>show pdbs;
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
SYS@CDB3>--
SYS@CDB3>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@CDB3>--
SYS@CDB3>select * from nls_database_parameters;
PARAMETER VALUE
------------------------------ ------------------------------
NLS_RDBMS_VERSION 12.1.0.2.0
NLS_NCHAR_CONV_EXCP FALSE
NLS_LENGTH_SEMANTICS BYTE
NLS_COMP BINARY
NLS_DUAL_CURRENCY $
NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH.MI.SSXFF AM TZR
NLS_TIME_TZ_FORMAT HH.MI.SSXFF AM TZR
NLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI.SSXFF AM
NLS_TIME_FORMAT HH.MI.SSXFF AM
NLS_SORT BINARY
NLS_DATE_LANGUAGE AMERICAN
PARAMETER VALUE
------------------------------ ------------------------------
NLS_DATE_FORMAT DD-MON-RR
NLS_CALENDAR GREGORIAN
NLS_NUMERIC_CHARACTERS .,
NLS_NCHAR_CHARACTERSET AL16UTF16
NLS_CHARACTERSET WE8ISO8859P15
NLS_ISO_CURRENCY AMERICA
NLS_CURRENCY $
NLS_TERRITORY AMERICA
NLS_LANGUAGE AMERICAN
20 rows selected.
SYS@CDB3>--
SYS@CDB3>column member format a80
SYS@CDB3>select name from v$controlfile;
NAME
----------------------------------------------------------------------------------------------------
/u02/oradata/CDB3/controlfile/o1_mf_c59htrp3_.ctl
/u03/orareco/CDB3/controlfile/o1_mf_c59hts3h_.ctl
SYS@CDB3>select name from v$datafile;
NAME
----------------------------------------------------------------------------------------------------
/u02/oradata/CDB3/datafile/o1_mf_system_c59hqhg1_.dbf
/u02/oradata/CDB3/datafile/o1_mf_system_c59hq07q_.dbf
/u02/oradata/CDB3/datafile/o1_mf_sysaux_c59hp703_.dbf
/u02/oradata/CDB3/datafile/o1_mf_sysaux_c59hsfn5_.dbf
/u02/oradata/CDB3/datafile/o1_mf_undotbs1_c59hrn5j_.dbf
/u02/oradata/CDB3/datafile/o1_mf_users_c59hrlqk_.dbf
6 rows selected.
SYS@CDB3>select name from v$tempfile;
NAME
----------------------------------------------------------------------------------------------------
/u02/oradata/CDB3/datafile/o1_mf_temp_c59hvbhv_.tmp
/u02/oradata/CDB3/244939806755201BE0534738A8C0CEF3/datafile/o1_mf_temp_c59hvfv1_.tmp
SYS@CDB3>select group#, member from v$logfile order by group#;
GROUP# MEMBER
---------- --------------------------------------------------------------------------------
1 /u02/oradata/CDB3/onlinelog/o1_mf_1_c59htw03_.log
1 /u03/orareco/CDB3/onlinelog/o1_mf_1_c59htw2h_.log
2 /u02/oradata/CDB3/onlinelog/o1_mf_2_c59htxb5_.log
2 /u03/orareco/CDB3/onlinelog/o1_mf_2_c59htxcw_.log
3 /u02/oradata/CDB3/onlinelog/o1_mf_3_c59htymt_.log
3 /u03/orareco/CDB3/onlinelog/o1_mf_3_c59htyof_.log
6 rows selected.
SYS@CDB3>
DBCA has also updated Oracle Restart local registry:
$ srvctl config database -d CDB3 Database unique name: CDB3 Database name: CDB3 Oracle home: /u01/app/12.1.0.2/db Oracle user: oracle Spfile: /u01/app/12.1.0.2/db/dbs/spfileCDB3.ora Password file: Domain: Start options: open Stop options: immediate Database role: PRIMARY Management policy: AUTOMATIC Disk Groups: DATA Services: OSDBA group: OSOPER group: Database instance: CDB3
There is an error: CDB3 is not using any ASM disk group but has a dependency on ASM disk group named DATA.
I have fixed this issue with:
$ srvctl modify database -db CDB3 -nodiskgroup $ srvctl config database -d CDB3 Database unique name: CDB3 Database name: CDB3 Oracle home: /u01/app/12.1.0.2/db Oracle user: oracle Spfile: /u01/app/12.1.0.2/db/dbs/spfileCDB3.ora Password file: Domain: Start options: open Stop options: immediate Database role: PRIMARY Management policy: AUTOMATIC Disk Groups: DATA Services: OSDBA group: OSOPER group: Database instance: CDB3
As expected creating a database with a seed template is much faster than using the SQL scripts template (about 3 to 4 times faster in my environment).
However I am a little bit surprised that database creation is faster on file system than on ASM even in my environment.