Creating a DBCA template for an Oracle 12.1.0.2 container database without any database option

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.

Overview

Creating a seed database template needs only 2 steps:

  • step 1: creating a database which must have the properties you want to store in the DBCA seed database template
  • step 2: creating the DBCA seed database template using DBCA and the newly created database a step 1.
  • Step 1

    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).

    Step 2

    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.

    Using the DBCA seed template to create a database in ASM

    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
    

    Using the DBCA seed template to create a database on file system

    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
    

    Conclusion

    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.

    Advertisements
    Post a comment or leave a trackback: Trackback URL.

    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

    %d bloggers like this: