TDE
Configuring TDE on a PDB with PLUG and UNPLUG Option
Category: Database Author: Andre Luiz Dutra Ontalba (Board Member) Date: 4 years ago Comments: 0

Configuring TDE on a PDB with PLUG and UNPLUG Option

 
This article describes the process of configure a TDE on CDB and unplugging PDB1 from the CDB1 instance and plugging into the CDB2 instance on the same machine with a new name of PDB2.

 

1 – Configure TDE Source – CDB (cdb1)
A) Create directory

 

[root@vm1 ~]# mkdir -p /etc/ORACLE/WALLETS/cdb1
[root@vm1 ~]# mkdir -p /etc/ORACLE/WALLETS/cdb2
[root@vm1 ~]# chown -R oracle:oinstall /etc/ORACLE
[root@vm1 ~]# chmod -R 755 /etc/ORACLE
B) Edit sqlnet.ora and configure the Wallet

 

ENCRYPTION_WALLET_LOCATION=
(SOURCE=
(METHOD=FILE)
(METHOD_DATA=
(DIRECTORY=/etc/ORACLE/WALLETS/$ORACLE_SID/)))
C) Configure Database – cdb1

 

[oracle@vm1 ~]$ . oraenv <<< cdb1
ORACLE_SID = [db01] ? The Oracle base remains unchanged with value /u01/app/oracle
[oracle@vm1 ~]$ sqlplus

SQL*Plus: Release 12.1.0.2.0 Production on Tue Sep 3 20:58:03 2019

Copyright (c) 1982, 2014, Oracle. All rights reserved.

Enter user-name: / as sysdba

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL>
SQL>
SQL>
SQL> show pdbs

CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDB1 READ WRITE NO
4 PDB2 READ WRITE NO


SQL> ADMINISTER KEY MANAGEMENT CREATE KEYSTORE '/etc/ORACLE/WALLETS/cdb1' IDENTIFIED BY oracle;

keystore altered.

SQL> ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY oracle container=all;

keystore altered.


SQL> ADMINISTER KEY MANAGEMENT SET KEY IDENTIFIED BY oracle WITH BACKUP USING 'emp_key_backup'

keystore altered.


SQL> alter session set container=pdb1;

Session altered.

SQL> ADMINISTER KEY MANAGEMENT SET KEY IDENTIFIED BY oracle WITH BACKUP USING 'emp_key_backup';

keystore altered.

SQL> set linesize 500
SQL> select * from v$encryption_wallet;

WRL_TYPE WRL_PARAMETER STATUS WALLET_TYPE WALLET_OR FULLY_BAC CON_ID
-------------------- -------------------------------------------------- ------------------------------ -------------------- --------- --------- ----------
FILE /etc/ORACLE/WALLETS/cdb1/ OPEN PASSWORD SINGLE NO 0


SQL> create tablespace TESTE
datafile '/u01/app/oracle/oradata/cdb1/pdb1/teste.dbf' size 10m
encryption using 'AES256'
default storage (encrypt);

Tablespace created.

D) Export Key from Source – PDB(pdb1)

 


SQL>ADMINISTER KEY MANAGEMENT EXPORT ENCRYPTION KEYS WITH SECRET "oracle" TO '/home/oracle/export.p12' IDENTIFIED BY oracle;

keystore altered.

SQL> !
[oracle@vm1 ~]$ ls -ltr export.p12
-rw-r--r--. 1 oracle oinstall 2612 Sep 3 21:08 export.p12
[oracle@vm1 ~]$ exit
 
E) Unplug and DROP PDB(pdb1)

 

SQL> alter pluggable database close immediate;

Pluggable database altered.

SQL> conn / as sysdba
Connected.

SQL> alter pluggable database pdb1 unplug into '/home/oracle/pdb1.xml';

Pluggable database altered.

SQL> drop pluggable database pdb1 keep datafiles;

Pluggable database dropped.

SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

 

2 – Configure TDE Destination CDB(cdb2)

 

[oracle@vm1 ~]$ . oraenv <<< cdb2
ORACLE_SID = [cdb1] ? The Oracle base remains unchanged with value /u01/app/oracle
[oracle@vm1 ~]$ ls /etc/ORACLE/WALLETS/
cdb1 cdb2
[oracle@vm1 ~]$ sqlplus

SQL*Plus: Release 12.1.0.2.0 Production on Tue Sep 3 21:13:12 2019

Copyright (c) 1982, 2014, Oracle. All rights reserved.

Enter user-name: / as sysdba
Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area 1241513984 bytes
Fixed Size 2923872 bytes
Variable Size 452985504 bytes
Database Buffers 771751936 bytes
Redo Buffers 13852672 bytes
Database mounted.
Database opened.
SQL> show pdbs

CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDB1 MOUNTED
SQL>
SQL> ADMINISTER KEY MANAGEMENT CREATE KEYSTORE '/etc/ORACLE/WALLETS/cdb2' IDENTIFIED BY oracle;

keystore altered.

SQL> ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY oracle;

keystore altered.

SQL> ADMINISTER KEY MANAGEMENT SET KEY IDENTIFIED BY oracle WITH BACKUP USING 'emp_key_backup';

keystore altered.

SQL>
SQL>
F) PLUG PDB(pdb2) using Unplugged pdb1

 

SQL> create pluggable database pdb2 as clone using '/home/oracle/pdb1.xml'
2 file_name_convert=('/u01/app/oracle/oradata/cdb1/pdb1/','/u01/app/oracle/oradata/cdb2/pdb2/');

Pluggable database created.

SQL> alter pluggable database pdb2 open;

Warning: PDB altered with errors.
G) IMPORT KEY from Source PDB(pdb1) to Destination PDB(pdb2)

 

SQL> alter session set container=pdb2;

Session altered.

SQL> ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY oracle;

keystore altered.

SQL> ADMINISTER KEY MANAGEMENT IMPORT ENCRYPTION KEYS WITH SECRET "oracle" FROM '/home/oracle/export.p12' IDENTIFIED BY oracle WITH BACKUP;

keystore altered.

SQL> shut immediate;
Pluggable Database closed.
SQL> startup
Pluggable Database opened.

 

H) Validate PDB is fully integrated with CDB2

 

 

SQL> conn / as sysdba
Connected.

SQL> select message,status from pdb_plug_in_violations where status <> 'RESOLVED';
SQL> /

no rows selected

SQL> show pdbs

CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDB1 MOUNTED
4 PDB2 READ WRITE NO
SQL>

 

I hope this helps you!!!
 
This article was written by Andre Ontalba e Rodrigo Mufalani
 

Disclaimer: “The postings on this site are my own and don’t necessarily represent may actual employer positions, strategies or opinions. The information here was edited  to be useful for general purpose, specific data and identifications was removed to allow reach generic audience and to be useful.”


Configuring Oracle Transparent Data Encryption (TDE) on Oracle 12c multitenant architecture
Category: Database Author: Andre Luiz Dutra Ontalba (Board Member) Date: 5 years ago Comments: 0

Configuring Oracle Transparent Data Encryption (TDE)
on Oracle 12c multitenant architecture

To configure TDE on Oracle 12c multitenant architecture we need to execute some steps in order to
be able to create encrypted tablespaces on Oracle, for example.

 

1) Adjust the sqlnet.ora file to refer your wallet path

 

[oracle@db1 admin]$ cat sqlnet.ora 
ENCRYPTION_WALLET_LOCATION=
(SOURCE=
(METHOD=FILE)
(METHOD_DATA=
(DIRECTORY=/u01/app/oracle/CDB1)
)
)
2) Create the folder on OS side

 

[oracle@db1 admin]$ mkdir -p /u01/app/oracle/CDB1
3) Log into database using the role syskm, create the keystore and the auto login keystore

 

[oracle@db1 admin]$ sqlplus / as syskm




SQL*Plus: Release 12.1.0.2.0 Production on Tue Jul 30 13:42:13 2019

Copyright (c) 1982, 2014, Oracle. All rights reserved.

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> administer key management create keystore '/u01/app/oracle/CDB1' identified by oracle;

keystore altered.

SQL>
SQL>
SQL> ADMINISTER KEY MANAGEMENT CREATE AUTO_LOGIN KEYSTORE FROM KEYSTORE '/u01/app/oracle/CDB1' IDENTIFIED BY oracle;

keystore altered.

SQL> select * from v$encryption_wallet;

WRL_TYPE WRL_PARAMETER STATUS WALLET_TYPE WALLET_OR FULLY_BAC CON_ID
------------ --------------------------------- ---------------------- -------------- ----------------------- -----------
FILE /u01/app/oracle/CDB1/ OPEN_NO_MASTER_KEY AUTOLOGIN SINGLE UNDEFINED 0

4) Bounce the database to see if AUTO_LOGIN keystore is working correctly

 

SQL> connect / as sysdba 
Connected.
SQL> shut immediate;
Database closed.
Database dismounted.
startup
ORACLE instance shut down.
SQL> ORACLE instance started.




Total System Global Area 1660944384 bytes
Fixed Size 2925072 bytes
Variable Size 1040190960 bytes
Database Buffers 603979776 bytes
Redo Buffers 13848576 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 /u01/app/oracle/CDB1/ OPEN_NO_MASTER_KEY AUTOLOGIN SINGLE UNDEFINED 0

Obs.: The first time, you need to set the masterkey to avoid this status and errors with the key not open properly.

 

SQL> alter pluggable database all open;

Pluggable database altered.

SQL> show pdbs

CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO 
3 PDB2 READ WRITE NO 
4 PD3 READ WRITE NO


5) Let’s setup the masterkey on the keystore

 

SQL> conn / as sysdba
Connected.
SQL> grant dba,syskm to C##dba identified by oracle container=ALL;




Grant succeeded.

SQL> administer key management set keystore close container=all;

keystore altered.

SQL> administer key management set keystore open identified by oracle container=all;

keystore altered.

 

6) On pluggable database, connected with common user C##DBA, let’s set the key for all pdbs.
 


SQL> conn c##dba/oracle@db1:1521/PD3 as syskm;
Connected.
SQL>
SQL> conn / as sysdba
Connected.
SQL> administer key management set key identified by oracle with backup using 'OMK' container=ALL;

keystore altered.

SQL> select * from v$encryption_wallet;

WRL_TYPE WRL_PARAMETER STATUS WALLET_TYPE WALLET_OR FULLY_BAC CON_ID
------------ --------------------------------- ---------------------- -------------- ----------------------- -----------
FILE /u01/app/oracle/CDB1/ OPEN PASSWORD SINGLE NO 0

As you can see above, now the status is OPEN (by password)
7) Connected on PD3 as common user C##DBA and now we can create our tablespace

 

SQL> conn c##dba/oracle@db1:1521/PD3; 
Connected.




SQL> create tablespace OCM_CRYP
2 datafile '/u01/app/oracle/oradata/CDB1/8DE664DD797442E9E055000000000001/datafile/OCM.DBF'
3 size 10m
4 encryption using 'AES256'
5 default storage(ENCRYPT);

Tablespace created.

 

So, this is the new way for 12c to management keys for encryption on database.
 
I hope this helps you!!!
 
This article was written by Andre Ontalba e Rodrigo Mufalani
 
Disclaimer: “The postings on this site are my own and don’t necessarily represent may actual employer positions, strategies or opinions. The information here was edited  to be useful for general purpose, specific data and identifications was removed to allow reach generic audience and to be useful.”