This was first published on https://blog.dbi-services.com/oracle-public-cloud-12cr2-tde-is-not-an-option (2017-02-05)
Republishing here for new followers. The content is related to the the versions available at the publication date
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;
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_ONLYThis 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.
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.
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.
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 NOIn 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
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.