Upgrade existing TDE to use new unified key management in 12c Upgraded Database (non-cdb)

Written by Suresh Karthikeyan | May 30, 2017 4:00:00 AM

I'm really excited to be writing this post and I'm hoping it serves as helpful content. When reviewing the new unified key management in RDMS 12c, I came across old commands like 'ALTER SYSTEM' to manage the TDE keys that are still supported. As a proof, I didn’t see any pre-reqs that exists on TDE part in the following upgrade related MOS note.

Complete Checklist for Manual Upgrades to Oracle Database 12c Release 1 (12.1) (Doc ID 1503653.1)

I had a test database which was running on RDBMS 11.2.0.4 version along with TDE configured (segments located in dedicated tablespace created with encryption clause). I upgraded this database into version 12.1.0.2 without any issues using the above note. No issues were found while accessing the encrypted segments using the old keys with autologin option existing on the "/etc/oracle/WALLETS/Test/" location on the server. I wanted to use the new unified key management on this upgraded database. I checked MOS and surprisingly, no direct notes to fulfill this purpose. After doing multiple tests, I was able to get this method working. 1. First, I copied my existing keys into "/home/oracle/keys" location as a backup. I confirmed my existing TDE keys work fine. SQL> select count(1) from test.tstbl; COUNT(1) ---------- 261440 2. I brought down my database and removed the keys from "/etc/oracle/WALLETS/Test/" location. I then started the database instance and created the dummy keystore using the new syntax. SQL> startup ORACLE instance started. Total System Global Area 524288000 bytes Fixed Size 2926320 bytes Variable Size 197134608 bytes Database Buffers 318767104 bytes Redo Buffers 5459968 bytes Database mounted. Database opened. SQL> select * from v$encryption_wallet; WRL_TYPE -------------------- WRL_PARAMETER -------------------------------------------------------------------------------- STATUS WALLET_TYPE WALLET_OR FULLY_BAC ------------------------------ -------------------- --------- --------- CON_ID ---------- FILE /etc/oracle/WALLETS/Test/ NOT_AVAILABLE UNKNOWN SINGLE UNDEFINED 0 SQL> select key_id,activation_time from v$encryption_keys; no rows selected SQL> ADMINISTER KEY MANAGEMENT CREATE KEYSTORE '/etc/oracle/WALLETS/Test' IDENTIFIED BY "<keystore_password>"; keystore altered. SQL> ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY "<keystore_password>"; keystore altered. SQL> select key_id,activation_time from v$encryption_keys; no rows selected SQL> select * from v$encryption_wallet; WRL_TYPE -------------------- WRL_PARAMETER -------------------------------------------------------------------------------- STATUS WALLET_TYPE WALLET_OR FULLY_BAC ------------------------------ -------------------- --------- --------- CON_ID ---------- FILE /etc/oracle/WALLETS/Test/ OPEN_NO_MASTER_KEY PASSWORD SINGLE UNDEFINED 0 3. Next, I imported the keys from the files copied as per MOS note 2254960.1, but still I saw the same "OPEN_NO_MASTER_KEY" status on the data dictionary.

How to Merge a TDE Wallet From 11gR2 Into a New 12c Database Keystore? (Doc ID 2254960.1)

SQL> ADMINISTER KEY MANAGEMENT MERGE KEYSTORE '/home/oracle/keys' IDENTIFIED BY "<11g_wallet_password>" INTO EXISTING KEYSTORE '/etc/oracle/WALLETS/Test' IDENTIFIED BY "<keystore_password>" WITH BACKUP; keystore altered. SQL> !ls -ltr /etc/oracle/WALLETS/Test total 16 -rw-r--r-- 1 oracle oinstall 3112 May 4 23:16 ewallet.p12 -rw-r--r-- 1 oracle oinstall 2408 May 4 23:16 ewallet_2017050503161274.p12 SQL> select * from v$encryption_wallet; WRL_TYPE -------------------- WRL_PARAMETER -------------------------------------------------------------------------------- STATUS WALLET_TYPE WALLET_OR FULLY_BAC ------------------------------ -------------------- --------- --------- CON_ID ---------- FILE /etc/oracle/WALLETS/Test/ OPEN_NO_MASTER_KEY PASSWORD SINGLE UNDEFINED 0 SQL> select key_id,activation_time from v$encryption_keys; no rows selected 4. I restarted my database instance and saw CLOSED status, which is expected as autologin was not enabled yet. SQL> shut immediate; Database closed. Database dismounted. ORACLE instance shut down. SQL> startup ORACLE instance started. Total System Global Area 524288000 bytes Fixed Size 2926320 bytes Variable Size 197134608 bytes Database Buffers 318767104 bytes Redo Buffers 5459968 bytes Database mounted. Database opened. SQL> select key_id,activation_time from v$encryption_keys; no rows selected SQL> select * from v$encryption_wallet; WRL_TYPE -------------------- WRL_PARAMETER -------------------------------------------------------------------------------- STATUS WALLET_TYPE WALLET_OR FULLY_BAC ------------------------------ -------------------- --------- --------- CON_ID ---------- FILE /etc/oracle/WALLETS/Test/ CLOSED UNKNOWN SINGLE UNDEFINED 0 SQL> !ls -ltr /etc/oracle/WALLETS/Test/ total 16 -rw-r--r-- 1 oracle oinstall 3112 May 4 23:16 ewallet.p12 -rw-r--r-- 1 oracle oinstall 2408 May 4 23:16 ewallet_2017050503161274.p12 SQL> select count(1) from test.tstbl; select count(1) from test.tstbl * ERROR at line 1: ORA-28365: wallet is not open 5. The only option available was to open the wallet is using the old syntax. SQL> ALTER SYSTEM SET ENCRYPTION WALLET OPEN IDENTIFIED BY "<11g_wallet_password>"; System altered. SQL> select * from v$encryption_wallet; WRL_TYPE -------------------- WRL_PARAMETER -------------------------------------------------------------------------------- STATUS WALLET_TYPE WALLET_OR FULLY_BAC ------------------------------ -------------------- --------- --------- CON_ID ---------- FILE /etc/oracle/WALLETS/Test/ OPEN PASSWORD SINGLE NO 0 SQL> select count(1) from test.tstbl; COUNT(1) ---------- 261440 6.

I configured the AUTOLOGIN feature using the new syntax and restarted the instance many times, no further issues reported with the keys availability. SQL> ADMINISTER KEY MANAGEMENT CREATE AUTO_LOGIN KEYSTORE FROM KEYSTORE '/etc/oracle/WALLETS/Test' IDENTIFIED BY "<keystore_password>"; keystore altered. SQL> shut immediate; Database closed. Database dismounted. ORACLE instance shut down. SQL> startup ORACLE instance started. Total System Global Area 524288000 bytes Fixed Size 2926320 bytes Variable Size 197134608 bytes Database Buffers 318767104 bytes Redo Buffers 5459968 bytes Database mounted. Database opened. SQL> select * from v$encryption_wallet; WRL_TYPE -------------------- WRL_PARAMETER -------------------------------------------------------------------------------- STATUS WALLET_TYPE WALLET_OR FULLY_BAC ------------------------------ -------------------- --------- --------- CON_ID ---------- FILE /etc/oracle/WALLETS/Test/ OPEN

AUTOLOGIN SINGLE NO 0 SQL> select count(1) from test.tstbl; COUNT(1) ---------- 261440 SQL> !ls -ltr /etc/oracle/WALLETS/Test total 24 -rw-r--r-- 1 oracle oinstall 3112 May 4 23:16 ewallet.p12 -rw-r--r-- 1 oracle oinstall 2408 May 4 23:16 ewallet_2017050503161274.p12 -rw-r--r-- 1 oracle oinstall 3157 May 4 23:21 cwallet.sso Verification: ------------------ SQL> select key_id,activation_time from v$encryption_keys; KEY_ID ------------------------------------------------------------------------------ ACTIVATION_TIME --------------------------------------------------------------------------- AQ**************************************AA SQL> select name,utl_raw.cast_to_varchar2( utl_encode.base64_encode('01'||substr(mkeyid,1,4))) || utl_raw.cast_to_varchar2( utl_encode.base64_encode(substr(mkeyid,5,length(mkeyid)))) masterkeyid_base64 FROM (select t.name, RAWTOHEX(x.mkid) mkeyid from v$tablespace t, x$kcbtek x where t.ts#=x.ts#); NAME MASTERKEYID_BASE64 ------------------------------ ------------------------------------------------------------ TDE_TBS AQ********************* The keys generated during older version of Oracle RDMS can be made active using the new TDE unified key management interface on RDMS 12c.

Oracle Database Consulting Services

Ready to optimize your Oracle Database for the future?

View full post