Oracle 12cR1 TDE Setting

Microsoft 의 Azure 에서 지원하는 Oracle 12cR1(12.1.0.2) Enterprise 에 PDB의 USERS Tablespace 만들다 DBCA 에서 wallet key not open 으로 에러가 나서 찾아 봤다. 12cR1의 마지막 Release 에서는 PDB 의 일반 Tablespace 는 기본적으로 Force tablespace USERS to be encrypted with AES128 되나 보다.

--
-- 00. Wallet Key location
-- 

sqlnet.ora

ENCRYPTION_WALLET_LOCATION =
  (SOURCE =(METHOD = FILE)(METHOD_DATA =
    (DIRECTORY = /u01/app/oracle/admin/$ORACLE_SID/encryption_keystore/)
    
    

--
-- 01. Create TDE Wallet Key
--

Root Container (CDB)
sqlplus / as sysdba

ADMINISTER KEY MANAGEMENT CREATE KEYSTORE '/u01/app/admin/KOREA/encryption_keystore/' identified by audtlr2;

/* 
      Key Open and Close

    if container = ALL is ommit then
        current container
    end if
 */
 
ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY audtlr2 container=ALL;
ADMINISTER KEY MANAGEMENT SET KEYSTORE CLOSE IDENTIFIED BY audtlr2 container=ALL;
ADMINISTER KEY MANAGEMENT SET KEY IDENTIFIED BY audtlr2 WITH BACKUP CONTAINER=ALL;

SET LINESIZE 100
SELECT con_id, key_id FROM v$encryption_keys;

   CON_ID KEY_ID
---------------- ------------------------------------------------------------------------------
        0 AZVTr7stFE8Gvyn8Yxt7dLMAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
        0 AfszURVAMk/5v1Eq3znzMMkAAAAAAAAAAAAAAAAAAAAAAAAAAAAA


SET LINESIZE 200
COLUMN wrl_parameter FORMAT A50
SELECT * FROM v$encryption_wallet;
   
     

WRL_TYPE      WRL_PARAMETER     STATUS          WALLET_TYPE     WALLET_OR FULLY_BAC    CON_ID
-------------------- -------------------------------------------------- ------------------------------ -------------------- --------- --------- ----------------
FILE       /u01/app/admin/KOREA/encryption_keystore/  OPEN          PASSWORD      SINGLE    NO         0


-- if not using CONTAONER=ALL then make pdb key

ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY audtlr2 ;
ADMINISTER KEY MANAGEMENT SET KEYSTORE CLOSE IDENTIFIED BY audtlr2;
ADMINISTER KEY MANAGEMENT SET KEY IDENTIFIED BY audtlr2 WITH BACKUP ;


SET LINESIZE 100
SELECT con_id, key_id FROM v$encryption_keys;

   CON_ID KEY_ID
---------------- ------------------------------------------------------------------------------
        0 AZVTr7stFE8Gvyn8Yxt7dLMAAAAAAAAAAAAAAAAAAAAAAAAAAAAA

 --
 -- 02. unplug and plug pdb with tde
 --

/* unplug */

CONN sys@pdb1 AS SYSDBA

ADMINISTER KEY MANAGEMENT EXPORT ENCRYPTION KEYS WITH SECRET "mySecret" TO '/tmp/export.p12' IDENTIFIED BY myPassword;

CONN / AS SYSDBA

ALTER PLUGGABLE DATABASE pdb1 CLOSE;
ALTER PLUGGABLE DATABASE pdb1 UNPLUG INTO '/tmp/pdb1.xml';



/* plug */

CONN / AS SYSDBA

CREATE PLUGGABLE DATABASE pdb2 USING '/tmp/pdb1.xml';

-- If you are not using OMF, you will have to convert the paths manually.
--CREATE PLUGGABLE DATABASE pdb2 USING '/tmp/pdb1.xml'
--  FILE_NAME_CONVERT=('/u01/app/oracle/oradata/cdb1/pdb1/','/u01/app/oracle/oradata/cdb2/pdb2/');

ALTER PLUGGABLE DATABASE pdb2 OPEN READ WRITE;

if cdb2 is not key then
    ADMINISTER KEY MANAGEMENT CREATE KEYSTORE '/u01/app/oracle/admin/cdb2/encryption_keystore/' IDENTIFIED BY myPassword;
    ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY myPassword;
end

ONN / AS SYSDBA
ALTER SESSION SET CONTAINER=pdb2;

ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY "myPassword";
ADMINISTER KEY MANAGEMENT IMPORT ENCRYPTION KEYS WITH SECRET "mySecret" FROM '/tmp/export.p12' IDENTIFIED BY "myPassword" WITH BACKUP;

-- Restart the PDB and open the keystore.
SHUTDOWN;
STARTUP;
ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY "myPassword";


-- auto login open setting

CONN / AS SYSDBA
ADMINISTER KEY MANAGEMENT CREATE LOCAL AUTO_LOGIN KEYSTORE FROM KEYSTORE '/u01/app/oracle/admin/cdb1/encryption_keystore/' IDENTIFIED BY myPassword;

SHUTDOWN IMMEDIATE;
STARTUP