How to create a DBCA template for a non-container database with Oracle Database 12.2.0.1 in Oracle Cloud

Here are the steps I have used to create a seed template (i.e a template using RMAN backup) to be able to create a 12.2.0.1 database faster in Oracle Cloud.

Template creation

First I have created a minimum non-container database with Unicode character set.

Here minimum means:

  • only with SYSTEM, SYSAUX and undo tablespaces
  • without any database option.
  • I have used following script:

    #!/bin/sh
    export ORACLE_SID=NCTPL
    export PFILE=$ORACLE_HOME/dbs/initNCTPL.ora
    rm -rf /u02/NCTPL
    rm -rf /u04/NCTPL
    rm -f $ORACLE_HOME/dbs/spfileNCTPL.ora
    #
    echo "db_name=NCTPL" > $PFILE 
    echo "db_create_file_dest=/u02" >> $PFILE
    echo "db_recovery_file_dest=/u04" >> $PFILE
    echo "db_recovery_file_dest_size=10G" >> $PFILE
    echo "memory_target=2G" >> $PFILE
    echo "encrypt_new_tablespaces=DDL" >> $PFILE
    #
    sqlplus / as sysdba << EOF
    shutdown abort
    startup nomount 
    create spfile from pfile;
    host rm $PFILE
    shutdown abort
    startup nomount
    spool crdb.log
    create database NCTPL 
    character set al32utf8
    default temporary tablespace TEMP;
    @?/rdbms/admin/catalog.sql
    @?/rdbms/admin/catproc.sql
    @?/rdbms/admin/utlrp.sql
    EOF
    

    Note that if there is no temporary tablespace there will a syntax error in DBCA template .dbc files for Tempfiles tag that will only trigger an error when using the template:

    [FATAL] [DBT-11004] Invalid template file specified (...).
       CAUSE: The given template is not compliant to the expected schema.
       ACTION: Verify the correctness of the template syntactically and semantically. Alternatively contact Oracle Support Services.
    

    I have created the template with following script:

    dbca -createCloneTemplate \
     -silent \
     -sourceSID NCTPL \
     -templateName NCDBT 
    

    I have run the script:

    + dbca -createCloneTemplate -silent -sourceSID NCTPL -templateName NCDBT
    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_2016-12-01_05-47-31-PM" for further details.
    

    The template is made up of 3 files in $ORACLE_HOME/dbca/templates

    $ ls -al /u01/app/oracle/product/12.2.0/dbhome_1/assistants/dbca/templates/*NCDBT*
    -rw-r----- 1 oracle oinstall   8634368 Dec  1 17:49 /u01/app/oracle/product/12.2.0/dbhome_1/assistants/dbca/templates/NCDBT.ctl
    -rw-r----- 1 oracle oinstall      4245 Dec  1 17:49 /u01/app/oracle/product/12.2.0/dbhome_1/assistants/dbca/templates/NCDBT.dbc
    -rw-r----- 1 oracle oinstall 133398528 Dec  1 17:48 /u01/app/oracle/product/12.2.0/dbhome_1/assistants/dbca/templates/NCDBT.dfb
    

    Fixing DBCA template .dbc file

    Some unexepected errors (DBCA bugs) have occured when trying to use this template.

    To avoid following error:

    [FATAL] [DBT-11211] The Automatic Memory Management option is not allowed when the total physical memory is greater than 4GB.
       CAUSE: The current total physical memory is 7,220GB.
    

    I have removed in NCDBT.dbc file:

     initParam name="memory_target" value="2147483648"
    

    To avoid following error:

    ORA-00201: control file version 12.2.0.0.0 incompatible with ORACLE version 12.1.0.2.0
    ORA-00202: control file: '/u01/app/oracle/cfgtoollogs/dbca/NCDB1/tempControl.ctl'
    

    I have replaced in NCDBT.dbc file:

     initParam name="compatible" value="12.1.0.2.0"
    

    with:

     initParam name="compatible" value="12.2.0.1.0"
    

    Testing the template

    To test the template I have used following script:

    dbca -silent \
    -createDatabase \
    -templateName NCDBT.dbc \
    -gdbName NCDB \
    -sid NCDB \
    -SysPassword oracle12c \
    -SystemPassword oracle12c \
    -characterSet AL32UTF8 \
    -emConfiguration NONE \
    -storageType FS \
    -datafileDestination /u02 \
    -recoveryAreaDestination /u04 \
    -totalMemory 2048
    

    Script output is:

    ++ dbca -silent -createDatabase -templateName NCDBT.dbc -gdbName NCDB -sid NCDB -SysPassword oracle12c -SystemPassword oracle12c -characterSet AL32UTF8 -emConfiguration NONE -storageType FS -datafileDestination /u02 -recoveryAreaDestination /u04 -totalMemory 2048
    [WARNING] [DBT-06208] The 'SYS' password entered does not conform to the Oracle recommended standards.
       CAUSE: 
    a. Oracle recommends that the password entered should be at least 8 characters in length, contain at least 1 uppercase character, 1 lower case character and 1 digit [0-9].
    b.The password entered is a keyword that Oracle does not recommend to be used as password
       ACTION: Specify a strong password. If required refer Oracle documentation for guidelines.
    [WARNING] [DBT-06208] The 'SYSTEM' password entered does not conform to the Oracle recommended standards.
       CAUSE: 
    a. Oracle recommends that the password entered should be at least 8 characters in length, contain at least 1 uppercase character, 1 lower case character and 1 digit [0-9].
    b.The password entered is a keyword that Oracle does not recommend to be used as password
       ACTION: Specify a strong password. If required refer Oracle documentation for guidelines.
    Copying database files
    1% complete
    2% complete
    18% complete
    33% complete
    Creating and starting Oracle instance
    35% complete
    40% complete
    44% complete
    49% complete
    50% complete
    53% complete
    55% complete
    Completing Database Creation
    56% complete
    57% complete
    58% complete
    62% complete
    65% complete
    66% complete
    Executing Post Configuration Actions
    100% complete
    Look at the log file "/u01/app/oracle/cfgtoollogs/dbca/NCDB/NCDB2.log" for further details.
    

    I have checked the created database:

    SQL> select name, cdb, log_mode from v$database;
    
    NAME      CDB LOG_MODE
    --------- --- ------------
    NCDB      NO  NOARCHIVELOG
    
    SQL> show pdbs;
    SQL> select name from v$datafile;
    
    NAME
    --------------------------------------------------------------------------------
    /u02/NCDB/datafile/o1_mf_system_d40sot8p_.dbf
    /u02/NCDB/datafile/o1_mf_sysaux_d40spxgz_.dbf
    /u02/NCDB/datafile/o1_mf_sys_undo_d40sr0n5_.dbf
    
    SQL> select member from v$logfile;
    
    MEMBER
    --------------------------------------------------------------------------------
    /u02/NCDB/onlinelog/o1_mf_2_d40st02o_.log
    /u04/NCDB/onlinelog/o1_mf_2_d40st1s7_.log
    /u02/NCDB/onlinelog/o1_mf_1_d40st01y_.log
    /u04/NCDB/onlinelog/o1_mf_1_d40st1q1_.log
    
    SQL> select name from v$controlfile;
    
    NAME
    --------------------------------------------------------------------------------
    /u02/NCDB/controlfile/o1_mf_d40sst01_.ctl
    /u04/NCDB/controlfile/o1_mf_d40sst5l_.ctl
    
    SQL> --
    SQL> column parameter format a30
    SQL> column value format a10
    SQL> select parameter, value from nls_database_parameters where parameter like '%SET%';
    
    PARAMETER                      VALUE
    ------------------------------ ----------
    NLS_NCHAR_CHARACTERSET         AL16UTF16
    NLS_CHARACTERSET               AL32UTF8
    
    SQL> --
    SQL> column action_time format a15
    SQL> column action format a10
    SQL> column version format a12
    SQL> column description format a50
    SQL> column comp_name format a40
    SQL> set linesize 120
    SQL> --
    SQL> select comp_name, version, status
      2  from dba_registry
      3  order by comp_name;
    
    COMP_NAME                                VERSION      STATUS
    ---------------------------------------- ------------ --------------------------------------------
    Oracle Database Catalog Views            12.2.0.1.0   VALID
    Oracle Database Packages and Types       12.2.0.1.0   VALID
    Oracle XML Database                      12.2.0.1.0   VALID
    
    SQL> --
    SQL> select * from dba_registry_sqlpatch;
    
    no rows selected
    
    SQL> 
    

    I have already modified tnsnames.ora to have separate key stores for each database:

    $ grep wallet $ORACLE_HOME/network/admin/sqlnet.ora
    ENCRYPTION_WALLET_LOCATION = (SOURCE=(METHOD=FILE)(METHOD_DATA=(DIRECTORY=/u01/app/oracle/admin/$ORACLE_SID/tde_wallet)))
    WALLET_LOCATION = (SOURCE=(METHOD=FILE)(METHOD_DATA=(DIRECTORY=/u01/app/oracle/admin/$ORACLE_SID/db_wallet)))
    $ 
    

    I have setup Transparent Data Encryption (TDE) with following SQL script:

    set echo on
    select name from v$database;
    set linesize 120
    column status format a10
    column wrl_parameter format a40
    column wallet_type format a15
    column wrl_type format a10
    column key_id format a60
    --
    host mkdir /u01/app/oracle/admin/NCDB/tde_wallet
    --
    administer key management create keystore '/u01/app/oracle/admin/NCDB/tde_wallet' identified by xxx;  
    administer key management set keystore open identified by xxx; 
    select * from v$encryption_wallet;  
    administer key management create auto_login keystore from keystore '/u01/app/oracle/admin/NCDB/tde_wallet' identified by xxx;
    administer key management set key identified by xxx with backup;
    select con_id, key_id, keystore_type from v$encryption_keys;
    select * from v$encryption_wallet;
    --
    exit
    

    I have run this script:

    SQL> select name from v$database;
    
    NAME
    ---------
    NCDB
    
    SQL> set linesize 120
    SQL> column status format a10
    SQL> column wrl_parameter format a40
    SQL> column wallet_type format a15
    SQL> column wrl_type format a10
    SQL> column key_id format a60
    SQL> --
    SQL> host mkdir /u01/app/oracle/admin/NCDB/tde_wallet
    
    SQL> --
    SQL> administer key management create keystore '/u01/app/oracle/admin/NCDB/tde_wallet' identified by xxx;
    
    keystore altered.
    
    SQL> administer key management set keystore open identified by xxx;
    
    keystore altered.
    
    SQL> select * from v$encryption_wallet;
    
    WRL_TYPE   WRL_PARAMETER                            STATUS     WALLET_TYPE     WALLET_OR FULLY_BAC     CON_ID
    ---------- ---------------------------------------- ---------- --------------- --------- --------- ----------
    FILE       /u01/app/oracle/admin/NCDB/tde_wallet/   OPEN_NO_MA PASSWORD        SINGLE    UNDEFINED          0
                                                        STER_KEY
    
    
    SQL> administer key management create auto_login keystore from keystore '/u01/app/oracle/admin/NCDB/tde_wallet' identified by xxx;
    
    keystore altered.
    
    SQL> administer key management set key identified by xxx with backup;
    
    keystore altered.
    
    SQL> select con_id, key_id, keystore_type from v$encryption_keys;
    
        CON_ID KEY_ID                                                       KEYSTORE_TYPE
    ---------- ------------------------------------------------------------ -----------------
             0 AfWB+ptX8k8xv9XUX4vh7ooAAAAAAAAAAAAAAAAAAAAAAAAAAAAA         SOFTWARE KEYSTORE
    
    SQL> select * from v$encryption_wallet;
    
    WRL_TYPE   WRL_PARAMETER                            STATUS     WALLET_TYPE     WALLET_OR FULLY_BAC     CON_ID
    ---------- ---------------------------------------- ---------- --------------- --------- --------- ----------
    FILE       /u01/app/oracle/admin/NCDB/tde_wallet/   OPEN       PASSWORD        SINGLE    NO                 0
    
    SQL>
    

    At this step in case of unexpected error ORA-28374: typed master key not found in wallet the simplest thing to do is just to drop and re-create the database.

    Reason for this is that encryption keys are stored in tablespace headers, database dictionary and redo logs: if there is some key mismatch it is much easier to drop and re-create the database.

    This is the last test step that must succeed: I have also created an application tablespace and tested that database instance can be restarted without any error:

    SQL> show parameter new
    
    NAME                                 TYPE        VALUE
    ------------------------------------ ----------- ------------------------------
    encrypt_new_tablespaces              string      CLOUD_ONLY
    
    SQL> create tablespace data;
    
    Tablespace created.
    
    SQL> select tablespace_name, encrypted from dba_tablespaces;
    
    TABLESPACE_NAME                ENC
    ------------------------------ ---
    SYSTEM                         NO
    SYSAUX                         NO
    SYS_UNDOTS                     NO
    TEMP                           NO
    DATA                           YES
    
    SQL> shutdown immediate
    Database closed.
    Database dismounted.
    ORACLE instance shut down.
    SQL> startup
    ORACLE instance started.
    
    Total System Global Area 1610612736 bytes
    Fixed Size                  8621232 bytes
    Variable Size             520094544 bytes
    Database Buffers         1073741824 bytes
    Redo Buffers                8155136 bytes
    Database mounted.
    Database opened.
    
    SQL> select * from v$encryption_wallet; 
    
    WRL_TYPE   WRL_PARAMETER                            STATUS     WALLET_TYPE     WALLET_OR FULLY_BAC     CON_ID
    ---------- ---------------------------------------- ---------- --------------- --------- --------- ----------
    FILE       /u01/app/oracle/admin/NCDB/tde_wallet/   OPEN       AUTOLOGIN       SINGLE    NO                 0
    
    SQL> 
    

    Note that the auto_login key store makes sure that it is opened at instance startup.

    This template is now ready to be used to create a non-container database in Oracle Cloud.