By Franck Pachot
.
In Oracle Public Cloud, Transparent Data Encryption is not an option. You can use it because it is included in all database services for all editions. You have to use it because the database won’t allow you to create, or import, non encrypted tablespaces. This is controlled by a new parameter, encrypt_new_tablespaces, which defaults to CLOUD_ONLY;
encrypt_new_tablespaces
In previous versions, we had to ENCRYPT explicitly the tablespace in the CREATE TABLESPACE statement.
Here we don’t need to add this clause in the DDL when we are on the Oracle Public Cloud because the default is:
SQL> show parameter encrypt
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
encrypt_new_tablespaces string CLOUD_ONLY
This parameter can take the following values:
SQL> alter system set encrypt_new_tablespaces=tictactoe;
alter system set encrypt_new_tablespaces=tictactoe
*
ERROR at line 1:
ORA-00096: invalid value TICTACTOE for parameter encrypt_new_tablespaces, must be from among DDL, ALWAYS, CLOUD_ONLY
The CLOUD_ONLY will automatically encrypt new tablespaces when we are on the Oracle Public Cloud only.
The ALWAYS will always encrypt new tablespaces, which is good if you have the Advanced Security Option and want to ensure that all data is encrypted
The DDL will never encrypt new tablespaces, except when explicitely done from the CREATE TABLESPACE. This is similar to the behavior before this parameter was introduced.
encrypt_new_tablespaces=DDL
If you are not on the Oracle Public Cloud, CLOUD_ONLY, the default, behaves as DDL. If you are on the Oracle Public Cloud, CLOUD_ONLY behaves like ALWAYS.
You may think that you can bypass the obligation to encrypt, just by setting encrypt_new_tablespaces=DDL but it’s not a good idea. Let’s test it.
I’ve set encrypt_new_tablespaces=ddl in an init.ora and I’ve created a database manually (CREATE DATABASE, catalog, catproc) and it works.
In 12.2 you can encrypt SYSTEM, SYSAUX and UNDO but this is not mandatory. However, I have created a user tablespace, USERS without any problem thanks to encrypt_new_tablespaces=DDL:
SQL> show parameter encrypt
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
encrypt_new_tablespaces string ddl
SQL> select TABLESPACE_NAME,ENCRYPTED from dba_tablespaces;
TABLESPACE_NAME ENC
------------------------------ ---
SYSTEM NO
SYSAUX NO
SYS_UNDOTS NO
UNDOTBS1 NO
USERS NO
Here I’m able to work without any problem… except when I have to restart the instance.
Startup
SQL> startup
ORACLE instance started.
Total System Global Area 2214592512 bytes
Fixed Size 8622968 bytes
Variable Size 1090522248 bytes
Database Buffers 1107296256 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: USERS in Oracle Cloud
Process ID: 11378
Session ID: 4 Serial number: 14394
It is impossible to open a database with unencrypted user tablespaces in the Oracle Public Cloud, whatever the encrypt_new_tablespaces is. I can only startup mount and there’s nothing to do at that point.
Here is the alert.log related entries:
Database Characterset is US7ASCII
No Resource Manager plan active
Verifying all user tablespaces in pdb 0 are encrypted in Oracle Cloud..
Found unencrypted tablespace USERS (pdb 0). Encrypted tablespace is mandatory in Oracle Cloud.
Errors in file /u01/app/oracle/diag/rdbms/test/TEST/trace/TEST_ora_11378.trc:
ORA-28427: cannot create, import or restore unencrypted tablespace: USERS in Oracle Cloud
Errors in file /u01/app/oracle/diag/rdbms/test/TEST/trace/TEST_ora_11378.trc:
ORA-28427: cannot create, import or restore unencrypted tablespace: USERS in Oracle Cloud
Error 28427 happened during db open, shutting down database
Errors in file /u01/app/oracle/diag/rdbms/test/TEST/trace/TEST_ora_11378.trc (incident=128065):
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: USERS in Oracle Cloud
Incident details in: /u01/app/oracle/diag/rdbms/test/TEST/incident/incdir_128065/TEST_ora_11378_i128065.trc
2017-02-04 22:25:26.612000 +00:00
opiodr aborting process unknown ospid (11378) as a result of ORA-603
ORA-603 : opitsk aborting process
License high water mark = 1
USER (ospid: 11378): terminating the instance due to error 28427
2017-02-04 22:25:27.615000 +00:00
PSP0 (ospid: 11293): terminating the instance due to error 28427
PMON (ospid: 11289): terminating the instance due to error 28427
2017-02-04 22:25:28.621000 +00:00
Instance terminated by PMON, pid = 11289
You don’t find those “Verifying all user tablespaces in pdb 0 are encrypted in Oracle Cloud..” when you are not in the Oracle Public Cloud.
But in Oracle Public Cloud, the instance is forced to stop as soon as an un-encrypted tablespace is found.
Fake it
What I’ll do now is only for academic purpose, to understand what happens and, maybe, troubleshoot if you have created unencrypted tablespaces. But it is not documented, and not supported.
From a cloud instance, you can get metadata about your instance by getting attributes from http://192.0.0.192 (Oracle Cloud uses Nimbula)
When Oracle Database instance starts, it reads the dns domain from http://192.0.0.192/latest/attributes/dns/domain:
[oracle@DBI122 ~]$ curl http://192.0.0.192/latest/attributes/dns/domain
compute-franck.oraclecloud.internal.[oracle@DBI122 ~]$
I suppose that the detection of Oracle Cloud is done from that. If you are not in Oracle Cloud, you will have no answer from http://192.0.0.192 so let’s simulate that by blocking this ip address:
[root@DBI122 opc]# iptables -A OUTPUT -d 192.0.0.192 -j REJECT
[root@DBI122 opc]# iptables -L
Chain INPUT (policy ACCEPT)
target prot opt source destination
Chain FORWARD (policy ACCEPT)
target prot opt source destination
Chain OUTPUT (policy ACCEPT)
target prot opt source destination
REJECT all -- anywhere 192.0.0.192 reject-with icmp-port-unreachable
Now back to my database, I can start it without any problem:
SQL> startup
ORACLE instance started.
Total System Global Area 2214592512 bytes
Fixed Size 8622968 bytes
Variable Size 1090522248 bytes
Database Buffers 1107296256 bytes
Redo Buffers 8151040 bytes
Database mounted.
Database opened.
SQL> select TABLESPACE_NAME,ENCRYPTED from dba_tablespaces;
TABLESPACE_NAME ENC
------------------------------ ---
SYSTEM NO
SYSAUX NO
SYS_UNDOTS NO
UNDOTBS1 NO
IOPS NO
In the alert.log I have nothing about checking encrypted tablespaces:
No Resource Manager plan active
2017-02-04 22:31:34.510000 +00:00
replication_dependency_tracking turned off (no async multimaster replication found)
Starting background process AQPC
So what?
encrypt_new_tablespaces=DDL is not a way to avoid encryption in the Oracle Public Cloud, except temporarily for a test on a database that you create for that and never re-start once you have created user tablespaces. There are still some bugs and issues with TDE (when importing from non-TDE, when using local undo,…) so it is good to know that there can be a workaround. But remember this is not supported. If you need this, please contact My Oracle Support.