How to create a DBCA template for a 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 container database faster in Oracle Cloud.

DISCLAIMER: Currently the way to create a 12.2.0.1 container database with CREATE DATABASE in Oracle Cloud does not look fully documented or tested: I have encountered some errors with catcdb.sql and the way these errors have been fixed is currently not documented by Oracle as far as I know.

Template creation

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

Here minimum means:

  • only with SYSTEM, SYSAUX and undo tablespaces in root container
  • only 1 pluggable database that has only SYSTEM, SYSAUX and undo tablespaces
  • without any database option.
  • I have used following script:

    #!/bin/sh
    export PATH=$ORACLE_HOME/perl/bin:$PATH
    export PERL5LIB=/var/opt/oracle/perl_lib/DBAAS
    export ORACLE_SID=CTPL
    export PFILE=$ORACLE_HOME/dbs/initCTPL.ora
    rm -rf /u02/CTPL
    rm -rf /u04/CTPL
    rm -f $ORACLE_HOME/dbs/spfileCTPL.ora
    #
    echo "db_name=CTPL" > $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
    echo "enable_pluggable_database=TRUE" >> $PFILE
    #
    export CATCDB_SYS_PASSWD=oracle12C
    export CATCDB_SYSTEM_PASSWD=oracle12C
    export CATCDB_TEMPTS=temp
    #
    sqlplus / as sysdba << EOF
    shutdown abort
    startup nomount
    create spfile from pfile;
    host rm $PFILE
    shutdown abort
    startup nomount
    spool crdb.log
    set echo on
    create database CTPL
    character set al32utf8
    enable pluggable database
    default temporary tablespace TEMP;
    @?/rdbms/admin/catcdb.sql /home/oracle/scripts catcdb
    --
    create pluggable database pdb admin user pdba identified by oracle12C;
    --
    EOF
    

    The above script is using perl executable from $ORACLE_HOME and perl library located in /var/opt/oracle/perl_lib/DBAAS
    in order to avoid following error in catcdb.sql:

    SQL> host perl -I &&rdbms_admin &&rdbms_admin_catcdb --logDirectory &&1 --logFilename &&2
    Can't locate util.pm in @INC (you may need to install the util module) (@INC contains: /u01/app/oracle/product/12
    .2.0/dbhome_1/rdbms/admin /u01/app/oracle/product/12.2.0/dbhome_1/perl/lib/site_perl/5.22.0/x86_64-linux-thread-m
    ulti /u01/app/oracle/product/12.2.0/dbhome_1/perl/lib/site_perl/5.22.0 /u01/app/oracle/product/12.2.0/dbhome_1/pe
    rl/lib/5.22.0/x86_64-linux-thread-multi /u01/app/oracle/product/12.2.0/dbhome_1/perl/lib/5.22.0 .) at /u01/app/or
    acle/product/12.2.0/dbhome_1/rdbms/admin/catcdb.pl line 35.
    BEGIN failed--compilation aborted at /u01/app/oracle/product/12.2.0/dbhome_1/rdbms/admin/catcdb.pl line 35.
    

    The above script is using CATCDB_SYS_PASSWD, CATCDB_SYSTEM_PASSWD and CATCDB_TEMPTS to avoid following errors in catcdb.sql:

    SQL> host perl -I &&rdbms_admin &&rdbms_admin_catcdb --logDirectory &&1 --logFilename &&2
    Enter new password for SYS: Enter new password for SYSTEM: Enter temporary tablespace name: No options to contain
    er mapping specified, no options will be installed in any containers
    user password environment variable CATCDB_SYS_PASSWD specified with -W was undefined
    user password environment variable CATCDB_SYS_PASSWD specified with -W was undefined
    user password environment variable CATCDB_SYS_PASSWD specified with -W was undefined
    user password environment variable CATCDB_SYS_PASSWD specified with -W was undefined
    user password environment variable CATCDB_SYS_PASSWD specified with -W was undefined
    user password environment variable CATCDB_SYS_PASSWD specified with -W was undefined
    user password environment variable CATCDB_SYS_PASSWD specified with -W was undefined
    user password environment variable CATCDB_SYS_PASSWD specified with -W was undefined
    user password environment variable CATCDB_SYS_PASSWD specified with -W was undefined
    user password environment variable CATCDB_SYS_PASSWD specified with -W was undefined
    

    The created database is a container database with following options (Workspace Manager is unexpected for me):

    SQL> select name, cdb, log_mode from v$database;
    
    NAME      CDB LOG_MODE
    --------- --- ------------
    CTPL      YES NOARCHIVELOG
    
    SQL> show pdbs;
    
        CON_ID CON_NAME                       OPEN MODE  RESTRICTED
    ---------- ------------------------------ ---------- ----------
             2 PDB$SEED                       READ ONLY  NO
             3 PDB                            MOUNTED
    SQL> select name from v$datafile;
    
    NAME
    --------------------------------------------------------------------------------
    /u02/CTPL/datafile/o1_mf_system_d4dyln21_.dbf
    /u02/CTPL/42FA6811B2B673FEE05342DCC40A6FD0/datafile/o1_mf_system_d4dylpwy_.dbf
    /u02/CTPL/datafile/o1_mf_sysaux_d4dylw2f_.dbf
    /u02/CTPL/42FA6811B2B673FEE05342DCC40A6FD0/datafile/o1_mf_sysaux_d4dylx89_.dbf
    /u02/CTPL/datafile/o1_mf_sys_undo_d4dylyws_.dbf
    /u02/CTPL/42FA6811B35173FEE05342DCC40A6FD0/datafile/o1_mf_system_d4f46j1w_.dbf
    /u02/CTPL/42FA6811B35173FEE05342DCC40A6FD0/datafile/o1_mf_sysaux_d4f46j26_.dbf
    
    7 rows selected.
    
    SQL> select member from v$logfile;
    
    MEMBER
    --------------------------------------------------------------------------------
    /u02/CTPL/onlinelog/o1_mf_1_d4dylhnj_.log
    /u04/CTPL/onlinelog/o1_mf_1_d4dylkcv_.log
    /u02/CTPL/onlinelog/o1_mf_2_d4dylkjm_.log
    /u04/CTPL/onlinelog/o1_mf_2_d4dyll05_.log
    
    SQL> select name from v$controlfile;
    
    NAME
    --------------------------------------------------------------------------------
    /u02/CTPL/controlfile/o1_mf_d4dylh19_.ctl
    /u04/CTPL/controlfile/o1_mf_d4dylh68_.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 Real Application Clusters         12.2.0.1.0   OPTION OFF
    Oracle Workspace Manager                 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>
    

    Before creating the template the pluggable database must be opened and I have changed its default state so that it is always opened at instance startup:

    SQL> show pdbs  
    
        CON_ID CON_NAME                       OPEN MODE  RESTRICTED
    ---------- ------------------------------ ---------- ----------
             2 PDB$SEED                       READ ONLY  NO
             3 PDB                            MOUNTED
    SQL> alter pluggable database pdb open;
    
    Pluggable database altered.
    
    SQL> alter pluggable database pdb save state;
    
    Pluggable database altered.
    
    SQL> startup force
    ORACLE instance started.
    
    Total System Global Area 2147483648 bytes
    Fixed Size                  8794848 bytes
    Variable Size            1342180640 bytes
    Database Buffers          788529152 bytes
    Redo Buffers                7979008 bytes
    Database mounted.
    Database opened.
    SQL> show pdbs
    
        CON_ID CON_NAME                       OPEN MODE  RESTRICTED
    ---------- ------------------------------ ---------- ----------
             2 PDB$SEED                       READ ONLY  NO
             3 PDB                            READ WRITE NO
    SQL> 
    

    I have created the template with following script:

    dbca -createCloneTemplate \
     -silent \
     -sourceSID CTPL \
     -templateName CDBT
    

    I have run this script:

    + dbca -createCloneTemplate -silent -sourceSID CTPL -templateName CDBT
    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-06_10-39-43-AM" for further details.
    

    The template is made up of 5 files in $ORACLE_HOME/assistants/dbca/templates:

    $ ls $ORACLE_HOME/assistants/dbca/templates/CDBT*
    /u01/app/oracle/product/12.2.0/dbhome_1/assistants/dbca/templates/CDBT.ctl
    /u01/app/oracle/product/12.2.0/dbhome_1/assistants/dbca/templates/CDBT.dbc
    /u01/app/oracle/product/12.2.0/dbhome_1/assistants/dbca/templates/CDBT.dfb62
    /u01/app/oracle/product/12.2.0/dbhome_1/assistants/dbca/templates/CDBT.dfb63
    /u01/app/oracle/product/12.2.0/dbhome_1/assistants/dbca/templates/CDBT.dfb64
    

    Fix 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 CDBT.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/CDB1/tempControl.ctl'
    

    I have replaced in CDBT.dbc file:

     initParam name="compatible" value="12.1.0.2.0"
    

    by

     initParam name="compatible" value="12.2.0.1.0"
    

    Testing the template

    I have used following script to create a database using CDBT template (note that the template is a container database template and it is not needed to specify pluggable database parameters to have only 1 pluggable database):

    dbca -silent \
    -createDatabase \
    -templateName CDBT.dbc \
    -gdbName CDB \
    -sid CDB \
    -SysPassword oracle12c \
    -SystemPassword oracle12c \
    -characterSet AL32UTF8 \
    -emConfiguration NONE \
    -storageType FS \
    -datafileDestination /u02 \
    -recoveryAreaDestination /u04 \
    -initParams sga_target=1536M
    

    Running this script gives following output:

    [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.
    [WARNING] [DBT-06801] Specified Fast Recovery Area size (10,240 MB) is less than the recommended value.
       CAUSE: Fast Recovery Area size should at least be three times the database size (4,312 MB).
       ACTION: Specify Fast Recovery Area Size to be at least three times the database size.
    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/CDB/CDB12.log" for further details.
    

    I have checked the created database:

    SQL> select name, cdb, log_mode from v$database;
    
    NAME      CDB LOG_MODE
    --------- --- ------------
    CDB       YES NOARCHIVELOG
    
    SQL> show pdbs;
    
        CON_ID CON_NAME                       OPEN MODE  RESTRICTED
    ---------- ------------------------------ ---------- ----------
             2 PDB$SEED                       READ ONLY  NO
             3 PDB                            READ WRITE NO
    SQL> select name from v$datafile;
    
    NAME
    --------------------------------------------------------------------------------
    /u02/CDB/datafile/o1_mf_system_d4f5xnt8_.dbf
    /u02/CDB/42FA6811B2B673FEE05342DCC40A6FD0/datafile/o1_mf_system_d4f5yg2j_.dbf
    /u02/CDB/datafile/o1_mf_sysaux_d4f5yx5l_.dbf
    /u02/CDB/42FA6811B2B673FEE05342DCC40A6FD0/datafile/o1_mf_sysaux_d4f5zpbd_.dbf
    /u02/CDB/datafile/o1_mf_sys_undo_d4f605hf_.dbf
    /u02/CDB/42FA6811B35173FEE05342DCC40A6FD0/datafile/o1_mf_system_d4f60ynx_.dbf
    /u02/CDB/42FA6811B35173FEE05342DCC40A6FD0/datafile/o1_mf_sysaux_d4f61frh_.dbf
    
    7 rows selected.
    
    SQL> select member from v$logfile;
    
    MEMBER
    --------------------------------------------------------------------------------
    /u02/CDB/onlinelog/o1_mf_2_d4f633mm_.log
    /u04/CDB/onlinelog/o1_mf_2_d4f63561_.log
    /u02/CDB/onlinelog/o1_mf_1_d4f633m1_.log
    /u04/CDB/onlinelog/o1_mf_1_d4f6354p_.log
    
    SQL> select name from v$controlfile;
    
    NAME
    --------------------------------------------------------------------------------
    /u02/CDB/controlfile/o1_mf_d4f62x81_.ctl
    /u04/CDB/controlfile/o1_mf_d4f62xgc_.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 Real Application Clusters         12.2.0.1.0   OPTION OFF
    Oracle Workspace Manager                 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 changed pluggable database default state so that it is always opened at instance startup:

    SQL> alter pluggable database pdb save state;
    
    Pluggable database altered.
    
    SQL> 
    

    I have already modified tnsnames.ora to have separate Transparent Data Encryption (TDE) 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 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
    --
    administer key management create keystore '/u01/app/oracle/admin/CDB/tde_wallet' identified by xxx;
    administer key management set keystore open identified by xxx container=all;
    administer key management set key identified by xxx with backup container=all;
    select * from v$encryption_wallet;  
    alter session set container=pdb;
    select * from v$encryption_wallet;
    select con_id, key_id, keystore_type from v$encryption_keys;
    alter session set container=cdb$root;
    administer key management create auto_login keystore from keystore '/u01/app/oracle/admin/CDB/tde_wallet' identified by xxx;
    administer key management set keystore close identified by xxx container=all;
    --
    exit
    

    I have run this script:

    SQL> select name from v$database;
    
    NAME
    ---------
    CDB
    
    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> administer key management create keystore '/u01/app/oracle/admin/CDB/tde_wallet' identified by xxx;
    
    keystore altered.
    
    SQL> administer key management set keystore open identified by xxx container=all;
    
    keystore altered.
    
    SQL> administer key management set key identified by xxx with backup container=all;
    
    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/CDB/tde_wallet/    OPEN       PASSWORD        SINGLE    NO                 1
    
    SQL> alter session set container=pdb;
    
    Session altered.
    
    SQL> select * from v$encryption_wallet;
    
    WRL_TYPE   WRL_PARAMETER                            STATUS     WALLET_TYPE     WALLET_OR FULLY_BAC     CON_ID
    ---------- ---------------------------------------- ---------- --------------- --------- --------- ----------
    FILE                                                OPEN       PASSWORD        SINGLE    NO                 3
    
    SQL> select con_id, key_id, keystore_type from v$encryption_keys;
    
        CON_ID KEY_ID                                                       KEYSTORE_TYPE
    ---------- ------------------------------------------------------------ -----------------
             3 Abc7t62yIk8sv7xt4MeffRgAAAAAAAAAAAAAAAAAAAAAAAAAAAAA         SOFTWARE KEYSTORE
    
    SQL> alter session set container=cdb$root;
    
    Session altered.
    
    SQL> administer key management create auto_login keystore from keystore '/u01/app/oracle/admin/CDB/tde_wallet' identified by xxx;
    
    keystore altered.
    
    SQL> administer key management set keystore close identified by xxx container=all;
    
    keystore altered.
    
    SQL>
    

    At this step in case of unexpected error such as ORA-28374: typed master key not found in wallet the simplest thing to do is just to drop, re-create the database and remove all files located in ENCRYPTION_WALLET_DIRECTORY.

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

    SQL> select name from v$database;
    
    NAME
    ---------
    CDB
    
    SQL> show parameter new
    
    NAME                                 TYPE        VALUE
    ------------------------------------ ----------- ------------------------------
    encrypt_new_tablespaces              string      CLOUD_ONLY
    SQL> alter session set container=pdb;
    
    Session altered.
    
    SQL> create tablespace data;
    
    Tablespace created.
    
    SQL> select tablespace_name, encrypted from dba_tablespaces;
    
    TABLESPACE_NAME                ENC
    ------------------------------ ---
    SYSTEM                         NO
    SYSAUX                         NO
    TEMP                           NO
    DATA                           YES
    
    SQL> alter session set container=cdb$root;
    
    Session altered.
    
    SQL> shutdown immediate
    Database closed.
    Database dismounted.
    ORACLE instance shut down.
    SQL> startup;
    ORACLE instance started.
    
    Total System Global Area 1610612736 bytes
    Fixed Size                  8793304 bytes
    Variable Size             520094504 bytes
    Database Buffers         1073741824 bytes
    Redo Buffers                7983104 bytes
    Database mounted.
    Database opened.
    SQL> show pdbs
    
        CON_ID CON_NAME                       OPEN MODE  RESTRICTED
    ---------- ------------------------------ ---------- ----------
             2 PDB$SEED                       READ ONLY  NO
             3 PDB                            READ WRITE NO
    SQL> 
    

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

    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: