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 .
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.
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
I have now understood that the right way to create a database with CREATE DATABASE in Oracle Cloud is to:
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
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
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
What I have learned from all this tests: