What happened so far on my Journey to the Cloud?
- Part I – Push a Button (Dec 3, 2015)
- Part II – Switch On/Off and Remove (Dec 4, 2015)
- Part III – Patch, patch, patch (Dec 22, 2015)
- Part IV – Clean Up APEX (Jan 19, 2016)
- You are here! ==> Part V – TDE is wonderful (Jan 28, 2016)
- Part VI – Patching does not work (Apr 18, 2016)
- Part VII – APEX is in CDB$ROOT again (Dec 21, 2016)
Today’s journey:
Learn about TDE (Transparent Data Encryption) and other secrets
What I really really love about my job: Every day I learn something new.
But sometimes learning can be frustrating at the beginning. And so it was for Roy and myself in the past days when we explored the use of TDE (Transparent Data Encryption) in our DBaaS Cloud environments. But many thanks to Brian Spendolini for his continuous 24×7 support 🙂
Never heard of Transparent Data Encryption before? Then please read on here. It’s usually part of ASO (Advanced Security Option) but it is included in the cloud offering.
But first of all before taking care on TDE and PDBs I tried to deploy a new DBaaS VM …
PDB names can’t contain underscores?
Well, one learning experience is that initially you can’t create a PDB in the DBaaS environment with an underscore in the name. I wanted to name my PDB in my new env simply “TDE_PDB1” (8 characters, all should be fine) – but received this nice message:
Don’t worry if you don’t speak German – it basically says that it can’t be longer than 8 characters (ok, I knew that), must begin with a character (mine does of course) and can only contain characters and number (eh?? no underscores???). Hm …?!?
Ok, I’ll name mine “TDEPDB1“.
Of course outside this page you can create PDBs containing an underscore:
SQL> create pluggable database PDB_MIKE admin user mike identified by mike
2 file_name_convert=(‘/oradata/CDB2/pdbseed’, ‘/oradata/CDB2/pdb_mike’);
Pluggable database created.
That’s what happens when application logic tries to superseed database logic.
(Almost) undocumented parameter: encrypt_new_tablespace
Thanks to Robert Pastijn for telling me about this hidden secret. A new parameter which is not in the regular database deployment but only in the cloud.
encrypt_new_tablespaces
First check in MOS:
Interesting.
So let’s check with Google.
And here it is: 7 hits, for example:
- An Oracle blog post unfortunately (for most people) written in German
- The official Oracle Cloud Documentation
- A 266 page PDF from December 2015 (see page 130)
Controlling Default Tablespace Encryption
The
ENCRYPT_NEW_TABLESPACES
initialization parameter controls default encryption of new tablespaces. In Database as a Service databases, this parameter is set toCLOUD_ONLY
.
|
Description |
---|---|
|
Any tablespace created will be transparently encrypted with the AES128 algorithm unless a different algorithm is specified on the ENCRYPTION clause. |
|
Tablespaces created in a Database Cloud Service database will be transparently encrypted with the AES128 algorithm unless a different algorithm is specified on the ENCRYPTION clause. For non-Database Cloud Service databases, tablespaces will only be encrypted if the ENCRYPTION clause is specified. This is the default value. |
|
Tablespaces are not transparently encrypted and are only encrypted if the ENCRYPTION clause is specified. |
What I found really scary is the fact that I couldn’t find it in my spfile/pfile. You can alter it with an “alter system” command but you can’t remove it.
The idea behind this is great as tablespaces should be encrypted, especially when they reside in a cloud environment. TDE is a very useful feature. And this mechanism exists regardless of your edition, whether you have Standard Edition or Enterprise Edition in any sort of flavor in the DBaaS Cloud.
A new tablespace will be encrypted by default:
SQL> CREATE TABLESPACE TS_MIKE DATAFILE ‘ts_mike01.dbf’ SIZE 10M;
Then check:
SQL> select TABLESPACE_NAME, ENCRYPTED from DBA_TABLESPACES;
But we’ll see later if this adds some constraints to our efforts to migrate a database for testing purposes into the DBaaS cloud environment.
Is there anything encrypted yet?
Quick check after setting:
SQL> alter system set exclude_seed_cdb_view=FALSE scope=both;
I tried to find out if any of the tablespaces are encrypted.
SQL> select tablespace_name, encrypted, con_id from CDB_TABLESPACES order by 3;
TABLESPACE_NAME ENC CON_ID
—————————— — ———-
SYSTEM NO 1
USERS NO 1
SYSAUX NO 1
UNDOTBS1 NO 1
TEMP NO 1
SYSTEM NO 2
USERS NO 2
TEMP NO 2
SYSAUX NO 2
EXAMPLE NO 3
USERS NO 3
TEMP NO 3
SYSAUX NO 3
APEX_1701140435539813 NO 3
SYSTEM NO 3
15 rows selected.
Looks good. Nothing encrypted yet.
How does the new parameter ENCRYPT_NEW_TABLESPACES effect operation?
Ok, lets try.
SQL> show parameter ENCRYPT_NEW_TABLESPACES
NAME TYPE VALUE
———————————— ———– —————
encrypt_new_tablespaces string CLOUD_ONLY
And further down the road …
SQL> alter session set container=pdb1;
SQL> create tablespace MIKE_PLAYS_WITH_TDE datafile ‘/u02/app/oracle/oradata/MIKEDB/PDB1/mike_plays_with_tde.dbf’ size 10M;
Tablespace created.
SQL> select tablespace_name, encrypted, con_id from CDB_TABLESPACES order by 3;
TABLESPACE_NAME ENC CON_ID
—————————— — ———-
SYSTEM NO 3
SYSAUX NO 3
TEMP NO 3
USERS NO 3
EXAMPLE NO 3
APEX_1701140435539813 NO 3
MIKE_PLAYS_WITH_TDE YES 3
7 rows selected.
Ah … so my new tablespace is encrypted. Not bad … so far TDE has no influence. I can create objects in this tablespace, query them etc. It is not disturbing at all. Good.
.
How does this key thing work in the DBaaS Cloud?
The documentation in above WP tells us this:
Managing the Software Keystore and Master Encryption Key
Tablespace encryption uses a two-tiered, key-based architecture to transparently encrypt (and decrypt) tablespaces. The master encryption key is stored in an external security module (software keystore). This master encryption key is used to encrypt the tablespace encryption key, which in turn is used to encrypt and decrypt data in the tablespace.
When the Database as a Service instance is created, a local auto-login software keystore is created. The keystore is local to the compute node and is protected by a system-generated password. The auto-login software keystore is automatically opened when accessed.
You can change (rotate) the master encryption key by using the tde rotate masterkey subcommand of the dbaascli utility. When you execute this subcommand you will be prompted for the keystore password. Enter the password specified when the service instance was created.
.
Creating a new PDB
That’s easy, isn’t it?
SQL> alter session set container=cdb$root;
Session altered.
SQL> create pluggable database pdb2 admin user mike identified by mike;
Pluggable database created.
SQL> alter pluggable database pdb2 open;
Pluggable database altered.
Ah, bad thing. As I neither used the file_name_convert option nor changed the PDB_FILE_NAME_CONVERT initialization parameter my new PDB files get created in the “root” path of the CDB. I don’t want this. But isn’t there this cool new feature called ONLINE MOVE OF DATAFILES in Oracle Database 12c? Ok, it’s an EE feature but let me try this after checking the current OMF file names in DBA_DATA_FILES and DBA_TEMP_FILES:
SQL> !mkdir /u02/app/oracle/oradata/MIKEDB/PDB2
SQL> ALTER DATABASE MOVE DATAFILE ‘/u02/app/oracle/oradata/MIKEDB/2A6680A0D990285DE053BA32C40AED53/datafile/o1_mf_system_cbn8fo1s_.dbf’ TO ‘/u02/app/oracle/oradata/MIKEDB/PDB2/system01.dbf’;
SQL> ALTER DATABASE MOVE DATAFILE ‘/u02/app/oracle/oradata/MIKEDB/2A6680A0D990285DE053BA32C40AED53/datafile/o1_mf_sysaux_cbn8fo20_.dbf’ TO ‘/u02/app/oracle/oradata/MIKEDB/PDB2/sysaux01.dbf’;
SQL> ALTER DATABASE MOVE DATAFILE ‘/u02/app/oracle/oradata/MIKEDB/2A6680A0D990285DE053BA32C40AED53/datafile/o1_mf_users_cbn8fo27_.dbf’ TO ‘/u02/app/oracle/oradata/MIKEDB/PDB2/users01.dbf’;
Be prepared:
This will create a 1:1 copy of the file in the designated location and synch afterwards. It may take a minute per file.
And moving the TEMP tablespace(s) file(s) will fail.
SQL> ALTER DATABASE MOVE DATAFILE ‘/u02/app/oracle/oradata/MIKEDB/2A6680A0D990285DE053BA32C40AED53/datafile/o1_mf_temp_cbn8fo25_.dbf’ TO ‘/u02/app/oracle/oradata/MIKEDB/PDB2/temp01.dbf’;
*
ERROR at line 1:
ORA-01516: nonexistent log file, data file, or temporary file
“/u02/app/oracle/oradata/MIKEDB/2A6680A0D990285DE053BA32C40AED53/datafile/o1_mf_temp_cbn8fo25_.dbf”
The temporary tablespace will have to be dropped and recreated. But not a big deal.
Check;
SQL> select file_name from dba_data_files;
FILE_NAME
—————————————————–
/u02/app/oracle/oradata/MIKEDB/PDB2/sysaux01.dbf
/u02/app/oracle/oradata/MIKEDB/PDB2/users01.dbf
/u02/app/oracle/oradata/MIKEDB/PDB2/system01.dbf
Let me fix this so I don’t hit this pitfall again:
SQL> alter system set pdb_file_name_convert=’/u02/app/oracle/oradata/MIKEDB/pdbseed’,’/u02/app/oracle/oradata/MIKEDBPDB2′;
Final verification:
SQL> select name, value from v$system_parameter where con_id=4;
NAME VALUE
———————- ———————————-
resource_manager_plan
pdb_file_name_convert /u02/app/oracle/oradata/MIKEDB/pdbseed, /u02/app/oracle/oradata/MIKEDBPDB2
Now the fun part starts … ORA-28374: typed master key not found in wallet
Remember this command from above in my PDB1? It run fine. But now it fails in PDB2.
SQL> create tablespace MIKE_PLAYS_WITH_TDE datafile ‘/u02/app/oracle/oradata/MIKEDB/PDB2/mike_plays_with_tde.dbf’ size 10M;
create tablespace MIKE_PLAYS_WITH_TDE datafile ‘/u02/app/oracle/oradata/MIKEDB/PDB2/mike_plays_with_tde.dbf’ size 10M
*
ERROR at line 1:
ORA-28374: typed master key not found in wallet
Voodoo in the database? I’m worried – especially as Roy had the same issue days before. But why did the command pass through without issues before in PDB1 – and now it doesn’t in PDB2? Is it because the PDB1 is precreated – and my PDB2 is not?
Kinda strange, isn’t it?
So connecting back to my PDB1 and trying again:
SQL> alter session set container=pdb1;
Session altered.
SQL> create tablespace MIKE_STILL_PLAYS_WITH_TDE datafile ‘/u02/app/oracle/oradata/MIKEDB/PDB1/mike_still_plays_with_tde.dbf’ size 10M;
Tablespace created.
Ok, now I’m worried.
What is the difference between the precreated PDB1 and my new PDB2?
Why do I get an ORA-28374 in my fresh PDB2?
When we compare the wallet status in both PDBs we’ll recognize the difference:
PDB1:
SQL> select wrl_type, wallet_type, status from v$encryption_wallet;
WRL_TYPE WALLET_TYPE STATUS
————— ——————– ———————–
FILE AUTOLOGIN OPEN
PDB2:
SQL> select wrl_type, wallet_type, status from v$encryption_wallet;
WRL_TYPE WALLET_TYPE STATUS
————— ——————– ———————–
FILE AUTOLOGIN OPEN_NO_MASTER_KEY
.
Now thanks to Brian Spendolini I have a working solution. But I’m not convinced that this is an obvious path …
Remember? I just would like to create a tablespace in my new (own, non-precreated) PDB. That’s all …
SQL> alter session set container=cdb$root;
SQL> administer key management set keystore close;
keystore altered.
SQL> administer key management set keystore open identified by <your-sysadmin-pw> container=all;
keystore altered.
SQL> alter session set container=pdb2;
Session altered.
SQL> SELECT WRL_PARAMETER,STATUS,WALLET_TYPE FROM V$ENCRYPTION_WALLET;
WRL_PARAMETER STATUS WALLET_TYPE
—————————————– —————— ———–
/u01/app/oracle/admin/MIKEDB/tde_wallet/ OPEN_NO_MASTER_KEY PASSWORD
SQL> ADMINISTER KEY MANAGEMENT SET KEY USING TAG “tde_dbaas” identified by <your-sysadmin-pw> WITH BACKUP USING “tde_dbaas_bkup”;
keystore altered.
SQL> SELECT WRL_PARAMETER,STATUS,WALLET_TYPE FROM V$ENCRYPTION_WALLET;
WRL_PARAMETER STATUS WALLET_TYPE
—————————————– ——– ———–
/u01/app/oracle/admin/MIKEDB/tde_wallet/ OPEN PASSWORD
And finally …
SQL> create tablespace MIKE_STILL_PLAYS_WITH_TDE datafile ‘/u02/app/oracle/oradata/MIKEDB/PDB2/mike_still_plays_with_tde.dbf’ size 10M;
Tablespace created.
Wow!!!
That was not straight forward at all. Maybe it all happens due to my almost non-existing knowledge about TDE.
Ah … and let me say that I find the missing uppercase letter with all keystore altered. echo messages quite disturbing. But this is a generic one and non-critical of course …
–Mike
PS: Read on about Seth Miller‘s experience here on Seth’s blog:
http://sethmiller.org/oracle-2/oracle-public-cloud-ora-28374-typed-master-key-not-found-in-wallet/
Hi Mike,
Just a short comment on your statement that moving temp files will fail, and that you have to drop and re-create.
Actually, that is not necessary. The new 12c move command does support moving temp files. You just need to alter your syntax a little: ALTER DATABASE MOVE **TEMPFILE** …
Regards,
Daniel
Daniel,
I swear I tried it because I looked it up in the docs when it failed with "datafile". But exchanging "datafile" with "tempfile" does not solve the issue.
Actually when I googled a bit I found that Tim struggled with the same issue:
https://oracle-base.com/articles/12c/online-move-datafile-12cr1
(scroll down to the buttom).
Cheers
Mike
I spent almost a full day dealing with this same issue before figuring it out on my own. Oh, how I wish this blog post had come out a week earlier. Good stuff though.
One thing that I ran into that you apparently did not is that closing the autologin wallet did not work for me. I had to actually delete the cwallet.sso file before I could open the file wallet to re-key.
Here is the blog post I put together.
http://sethmiller.org/oracle-2/oracle-public-cloud-ora-28374-typed-master-key-not-found-in-wallet/
Seth,
sorry to hear/read that – and you have my full understanding as the post does not reflect the effort it took us to find out about all those nice little things …
Cheers
Mike
Hi Mike.
Just a confirmation for Non-CDB – same behavior in the Cloud.
I’ve created a new 12c Non-CDB database ORCL2 (next to default instance ORCL) in the Oracle Public Cloud Database Service and I got ORA-28374 for a new TBS.
I changed keystore (close, open and SET KEY USING TAG "tde_dbaas") and then I was able to create a tablespace.
Regards,
Patrik
Patrik,
as always thanks for the update and the information. I suspected this … 😉
Cheers
Mike