Oracle image in Azure - part 2.......

The last post prompted a couple of suggestions (thanks Tim) and talking it through with a colleague at work (thanks John) and it seems i now understand what's going on.

So it seems as i suggested Oracle has released a special 'cloud' version of the database to Azure and some 12.2 features seem to have been backported to it.

The key one being Tim's suggestion

So here is a further demo...

Lets check what parameters related to encryption exist

SQL> select KSPPINM from x$ksppi where upper(KSPPINM) like '%ENCRYPT%';

KSPPINM
--------------------------------------------------------------------------------
encrypt_new_tablespaces
_use_platform_encryption_lib
_use_hybrid_encryption_mode
_db_writer_coalesce_encrypted_buffers
_db_disable_temp_encryption
_backup_encrypt_opt_mode
_kdlxp_lobencrypt
_default_encrypt_alg

8 rows selected.

The main interesting one is encrypt_new_tablespaces - this is not present in a standard 12.1.0.2 download from oracle.com......

Whats the value of that


SQL> sho parameter encrypt_new_tablespaces

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


Lets guess a new setting for that

SQL> alter system set encrypt_new_tablespaces='NEVER';
alter system set encrypt_new_tablespaces='NEVER'
*
ERROR at line 1:
ORA-00096: invalid value NEVER for parameter encrypt_new_tablespaces, must be
from among DDL, ALWAYS, CLOUD_ONLY

OK - none of those sounds like an 'OFF' setting

lets try DDL - whatever that means

SQL> alter system set encrypt_new_tablespaces='DDL';

System altered.

Now try tablespace creation

SQL> create tablespace xx datafile '/oradata/xx01.dbf' size 16M;

Tablespace created.

And it works......

Lets double check its not encrypted

SQL> select TABLESPACE_NAME,ENCRYPTED from dba_tablespaces;

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

6 rows selected.

So looks fine.......

So to make the default image usable seems this setting has to be changed - but i really think this shouldn't be set up like this on this image. I think Oracle were just planning on keeping this auto encrypt in 12.1 just for themselves.........

The image should just work out of the box - the TDE stuff just seems broken in the image and this setting has to be changed to DDL to make things usable...but it's not obvious when the feature its using to do this doesn't even exist in 12.1 officially....