Silent Oracle database 12.2.0.1 creation in Oracle Cloud Database As A Service (DBAAS) : with or without DBCA ?

Beginning of November 2016 Oracle Corp. has made available more DBAAS offerings for Oracle 12.2.0.1. and Oracle 12c release 2 database documentation. But you still cannot download Oracle 12.2.0.1 installation media.

I have used free trial to create an Oracle Database Cloud Service: this is a virtual machine where Oracle Database software is pre-installed and a database is created using web interface. This is the only service in free trial at the time of writing that allows to use Oracle 12.2.0.1 (the Oracle Database Cloud Service – Virtual Image currently allows only to work with Oracle 11.2.0.4 and Oracle 12.1.0.2).

In this article I want only to use DBCA to create a new 12.2.0.1 non-container database in order to compare DBCA 12.1 and DBCA 12.2.

Note that are other ways to create a DBAAS database in command line mode for example using CURL and DBAAS REST API thanks to Franck Pachot .

Trying to create a new database with default DBCA templates

I didn’t manage to create a database with default DBCA templates.

If I use the seed template General_Purpose.dbc I get:

ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-28427: cannot create, import or restore unencrypted tablespace: USERS in Oracle Cloud

If I use the non seed template New_Database.dbt I get:

CREATE SMALLFILE UNDO TABLESPACE UNDOTBS1 DATAFILE   '/data/NCDB/undotbs01.dbf' SIZE 200M REUSE AUTOEXTEND ON NEXT  5120K MAXSIZE UNLIMITED

Force tablespace UNDOTBS1 to be encrypted with AES128
ORA-28365 signalled during: CREATE SMALLFILE UNDO TABLESPACE UNDOTBS1 DATAFILE   '/data/NCDB/undotbs01.dbf' SIZE 200M REUSE AUTOEXTEND ON NEXT  5120K MAXSIZE UNLIMITED

...
2016-11-12T19:20:33.193157+00:00
Errors in file /u01/app/oracle/diag/rdbms/ncdb/NCDB/trace/NCDB_ora_16029.trc:
ORA-00604: error occurred at recursive SQL level 1
ORA-28365: wallet is not open
2016-11-12T19:20:33.193289+00:00
Errors in file /u01/app/oracle/diag/rdbms/ncdb/NCDB/trace/NCDB_ora_16029.trc:
ORA-01501: CREATE DATABASE failed
ORA-01519: error while processing file '?/rdbms/admin/dtxnspc.bsq' near line 6
ORA-00604: error occurred at recursive SQL level 1
ORA-28365: wallet is not open

I have checked the default database ORCL created with the VM:

SQL> select name from v$database;

NAME
---------
ORCL

SQL> select * from v$version;

BANNER                                                                               CON_ID
-------------------------------------------------------------------------------- ----------
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production              0
PL/SQL Release 12.2.0.1.0 - Production                                                    0
CORE    12.2.0.1.0      Production                                                                0
TNS for Linux: Version 12.2.0.1.0 - Production                                            0
NLSRTL Version 12.2.0.1.0 - Production                                                    0

SQL> select tablespace_name, encrypted from dba_tablespaces;

TABLESPACE_NAME                ENC
------------------------------ ---
SYSTEM                         NO
SYSAUX                         NO
UNDOTBS1                       NO
TEMP                           NO
USERS                          YES

This is due to a new 12.2 instance parameter ENCRYPT_NEW_TABLESPACES (which was a hidden parameter in 12.1.0.2 according to Pat Shuff’s blog and Mike Dietrich’s blog):

SQL> show parameter encrypt

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
encrypt_new_tablespaces              string      CLOUD_ONLY

I understand that all application tablespaces must be encrypted if database runs in Oracle Cloud.

Trying to create manually a database (i.e. without DBCA)

I have managed to create manually a 12.2.0.1 non-container database with non encrypted tablespaces using following script:

#!/bin/sh
export ORACLE_SID=TEST
export PFILE=$ORACLE_HOME/dbs/initTEST.ora
#
echo "db_name=TEST" > $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
create database;
@?/rdbms/admin/catalog.sql
@?/rdbms/admin/catproc.sql
@?/rdbms/admin/utlrp.sql
EOF 

I have created a non encrypted tablespace with:

create tablespace data;

But at next database instance stop/start, instance does not start even if ENCRYPT_NEW_TABLESPACES is set to DDL:

SQL> startup
ORACLE instance started.

Total System Global Area 2147483648 bytes
Fixed Size                  8622776 bytes
Variable Size            1207962952 bytes
Database Buffers          922746880 bytes
Redo Buffers                8151040 bytes
Database mounted.
ORA-00603: ORACLE server session terminated by fatal error
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-28427: cannot create, import or restore unencrypted tablespace: DATA in Oracle Cloud
Process ID: 9042
Session ID: 162 Serial number: 57458

The right way to create manually a database in Oracle Cloud

I have now understood that the right way to create a database with CREATE DATABASE in Oracle Cloud is to:

  • 1: create a database without any application tablespace because SYSTEM, SYSAUX and undo tablespaces don’t need to be encrypted in Oracle Cloud
  • 2: setup Transparent Data Encryption (TDE)
  • 3: create application tablespace that can be encrypted
  • Step 1: create a minimum database

    I have run following script:

    #!/bin/sh
    export ORACLE_SID=NCDB
    export PFILE=$ORACLE_HOME/dbs/initNCDB.ora
    rm -rf /u02/NCDB
    rm -rf /u04/NCDB
    rm -f $ORACLE_HOME/dbs/spfileNCDB.ora
    #
    echo "db_name=TPL" > $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 NCDB character set al32utf8;
    @?/rdbms/admin/catalog.sql
    @?/rdbms/admin/catproc.sql
    @?/rdbms/admin/utlrp.sql
    EOF
    

    Running this script has created a non-container database with Unicode character set without any database option (except XML database) and with only “system” tablespaces that are not encrypted:

    QL>  select name, cdb from v$database;
    
    NAME      CDB
    --------- ---
    NCDB      NO
    
    SQL> select tablespace_name, encrypted from dba_tablespaces;
    
    TABLESPACE_NAME                ENC
    ------------------------------ ---
    SYSTEM                         NO
    SYSAUX                         NO
    SYS_UNDOTS                     NO
    
    SQL> select comp_name, version, status from dba_registry;
    
    
    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
    

    step 2: setup TDE

    To setup TDE for my new database I have used and adapted Oracle Database 12c: Transparent Data Encryption (TDE) and the world of multitenant database from Toad World by Abu Fazal Abbas.

    First I have modified sqlnet.ora so that each database has its own TDE directories:

    $ grep SID $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 created TDE wallet directory for NCDB database:

    $ mkdir /u01/app/oracle/admin/NCDB/tde_wallet
    

    I have connected to NCDB and run following SQL statements:

    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_MASTER_KEY             PASSWORD             SINGLE    UNDEFINED
             0
    
    
    SQL>  administer key management create auto_login keystore from keystore '/u01/app/oracle/admin/NCDB/tde_wallet' identified by xxx;
    
    keystore altered.
    
    SQL> 
    SQL> administer key management set key identified by mks with backup;
    
    keystore altered.
    
    SQL> 
    
    SQL> select con_id, key_id, keystore_type from v$encryption_keys;
    
        CON_ID
    ----------
    KEY_ID
    ------------------------------------------------------------------------------
    KEYSTORE_TYPE
    -----------------
             0
    AdOGu2sWO085v33seosS01IAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
    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
    
    

    Step 3: create application tablespace

    I have run:

    SQL> show parameter new
    
    NAME                                 TYPE        VALUE
    ------------------------------------ ----------- ------------------------------
    encrypt_new_tablespaces              string      DDL
    SQL> alter system set encrypt_new_tablespaces=cloud_only;
    
    System altered.
    
    SQL> create tablespace data;
    
    Tablespace created.
    
    SQL> select tablespace_name, encrypted from dba_tablespaces;
    
    TABLESPACE_NAME                ENC
    ------------------------------ ---
    SYSTEM                         NO
    SYSAUX                         NO
    SYS_UNDOTS                     NO
    DATA                           YES
    

    To check that everything works as expected I have also tested database instance restart:

    SQL> shutdown immediate
    Database closed.
    Database dismounted.
    ORACLE instance shut down.
    SQL> startup
    ORACLE instance started.
    
    Total System Global Area 2147483648 bytes
    Fixed Size                  8622776 bytes
    Variable Size            1224740168 bytes
    Database Buffers          905969664 bytes
    Redo Buffers                8151040 bytes
    Database mounted.
    Database opened.
    SQL>       
    

    I have noted that alert log says:

    Verifying minimum file header compatibility for tablespace encryption..
    Verifying file header compatibility for tablespace encryption completed for pdb 0
    Database Characterset is AL32UTF8
    No Resource Manager plan active
    Verifying all user tablespaces in pdb 0 are encrypted in Oracle Cloud..
    All user tablespaces in pdb 0 are encrypted
    

    Conclusions

    What I have learned from all this tests:

  • I don’t think that it is possible to create a database using DBCA and its default DBCA templates in Oracle Cloud DBAAS.
  • Each database created in Oracle Cloud MUST have each application tablespace encrypted.
  • Oracle database server code is checking whether it runs on Oracle Cloud or not and behaves differently if instance parameter ENCRYPT_NEW_TABLESPACES is set to CLOUD_ONLY: in other words Oracle server code may not behave the same way in Oracle cloud and on premises.
  • Advertisements
    Post a comment or leave a trackback: Trackback URL.

    Comments

    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: