--- below was done for a 2node 12C RAC
--- wallet location was instance specific so i did configure for 1st instance then copied all the TDE files to respective location on 2nd instance & restarted 2nd instance
--- pre check for TDE
subhen002:orclt1 > sqlplus / as sysdba
SQL*Plus: Release 12.1.0.2.0 Production on Fri Jul 1 15:53:38 2016
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, Real Application Clusters, Automatic Storage Management, OLAP,
Advanced Analytics, Real Application Testing and Unified Auditing options
SQL> select * from V$ENCRYPTION_WALLET;
WRL_TYPE
--------------------
WRL_PARAMETER
--------------------------------------------------------------------------------
STATUS WALLET_TYPE WALLET_OR FULLY_BAC
------------------------------ -------------------- --------- ---------
CON_ID
----------
FILE
/dboracle/orabase/admin/orcl_tp/wallet
NOT_AVAILABLE UNKNOWN SINGLE UNDEFINED
0
--- take backup of sqlnet.ora and made entry to both nodes as below
subhen002:orclt1 > cp -p sqlnet.ora sqlnet.ora_bkp
subhen002:orclt1 > vi sqlnet.ora
#---------------------------#
# Network Tuning Parameters #
#---------------------------#
default_sdu_size=32767
send_buf_size=1048576
recv_buf_size=1048576
tcp.nodelay=yes
sqlnet.inbound_connect_timeout=180
sqlnet.expire_time=10
#TDE entry
ENCRYPTION_WALLET_LOCATION=
(SOURCE=
(METHOD=FILE)
(METHOD_DATA=
(DIRECTORY=+DATA_DG01/$ORACLE_SID/WALLET)
)
)
~
--- create directories in ASM
SQL> ALTER DISKGROUP DATA_DG01 ADD DIRECTORY '+DATA_DG01/orclt1';
Diskgroup altered.
SQL> ALTER DISKGROUP DATA_DG01 ADD DIRECTORY '+DATA_DG01/orclt1/WALLET/';
Diskgroup altered.
SQL> ALTER DISKGROUP DATA_DG01 ADD DIRECTORY '+DATA_DG01/orclt2';
Diskgroup altered.
SQL> ALTER DISKGROUP DATA_DG01 ADD DIRECTORY '+DATA_DG01/orclt2/WALLET/';
Diskgroup altered.
--- check again it should show ASM path
SQL> set lines 234 pages 234
SQL> col WRL_PARAMETER for a40
SQL> select WRL_TYPE, WRL_PARAMETER, STATUS from V$ENCRYPTION_WALLET;
WRL_TYPE WRL_PARAMETER STATUS
-------------------- ---------------------------------------- ------------------------------
ASM +DATA_DG01/orclt1/WALLET/ NOT_AVAILABLE
-- as root user and in root container execute the below sqls
SQL> show con_name
CON_NAME
------------------------------
CDB$ROOT
SQL> show user
USER is "SYS"
SQL> set lines 234 pages 234
SQL> col WRL_PARAMETER for a40
SQL> select WRL_TYPE, WRL_PARAMETER, STATUS from V$ENCRYPTION_WALLET;
WRL_TYPE WRL_PARAMETER STATUS
-------------------- ---------------------------------------- ------------------------------
ASM +DATA_DG01/orclt1/WALLET/ NOT_AVAILABLE
SQL> select CON_ID,KEY_ID,KEYSTORE_TYPE,CREATOR_DBNAME,CREATOR_PDBNAME from v$encryption_keys;
no rows selected
SQL> ADMINISTER KEY MANAGEMENT CREATE KEYSTORE '+DATA_DG01/orclt1/WALLET/' IDENTIFIED BY enc_keystore;
keystore altered.
SQL> select WRL_TYPE, WRL_PARAMETER, STATUS from V$ENCRYPTION_WALLET;
WRL_TYPE WRL_PARAMETER STATUS
-------------------- ---------------------------------------- ------------------------------
ASM +DATA_DG01/orclt1/WALLET/ CLOSED
SQL> ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY enc_keystore CONTAINER = ALL ;
keystore altered.
SQL> select * from V$ENCRYPTION_WALLET;
WRL_TYPE WRL_PARAMETER STATUS WALLET_TYPE WALLET_OR FULLY_BAC CON_ID
-------------------- ---------------------------------------- ------------------------------ -------------------- --------- --------- ----------
ASM +DATA_DG01/orclt1/WALLET/ OPEN_NO_MASTER_KEY PASSWORD SINGLE UNDEFINED 0
SQL> ADMINISTER KEY MANAGEMENT SET KEY USING TAG 'scope:all pdbs;description:Create Key for ALL PDBS' IDENTIFIED BY enc_keystore WITH BACKUP CONTAINER = ALL ;
keystore altered.
--- verify the creation in root & all PDBs
SQL> select * from V$ENCRYPTION_WALLET;
WRL_TYPE WRL_PARAMETER STATUS WALLET_TYPE WALLET_OR FULLY_BAC CON_ID
-------------------- ---------------------------------------- ------------------------------ -------------------- --------- --------- ----------
ASM +DATA_DG01/orclt1/WALLET/ OPEN PASSWORD SINGLE NO 0
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 orcl READ WRITE NO
SQL> alter session set container=orcl
2 ;
Session altered.
SQL> select * from V$ENCRYPTION_WALLET;
WRL_TYPE WRL_PARAMETER STATUS WALLET_TYPE WALLET_OR FULLY_BAC CON_ID
-------------------- ---------------------------------------- ------------------------------ -------------------- --------- --------- ----------
ASM +DATA_DG01/orclt1/WALLET/ OPEN PASSWORD SINGLE NO 0
SQL> select CON_ID,KEY_ID,KEYSTORE_TYPE,CREATOR_DBNAME,CREATOR_PDBNAME from v$encryption_keys;
CON_ID KEY_ID KEYSTORE_TYPE CREATOR_DBNAME CREATOR_PDBNAME
---------- ------------------------------------------------------------------------------ ----------------- ------------------------------ ------------------------------
0 AY/4VjtNE0+4vxqV8VtEEB4AAAAAAAAAAAAAAAAAAAAAAAAAAAAA SOFTWARE KEYSTORE orcl_tp orcl
--- create a sample user/schema for TDE use
SQL> show con_name
CON_NAME
------------------------------
orcl
SQL> create user tde_admin identified by tde_admin;
User created.
SQL> grant CREATE TABLE, CREATE TABLESPACE to tde_admin;
Grant succeeded.
SQL> grant connect,resource to tde_admin;
Grant succeeded.
SQL> exit
---- create a encrypted tablespace and assign to tde_admin
SQL> alter session set container=orcl
2 ;
Session altered.
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
3 orcl READ WRITE NO
SQL> show con_name
CON_NAME
------------------------------
orcl
SQL> CREATE TABLESPACE encrypt_ts
DATAFILE '+DATA_DG01' SIZE 1M
ENCRYPTION USING 'AES256'
DEFAULT STORAGE (ENCRYPT); 2 3 4
Tablespace created.
SQL> alter user tde_admin default tablespace encrypt_ts;
User altered.
SQL> show user
USER is "SYS"
--- check the tablespace should be created as encrypted
SQL> conn / as sysdba
Connected.
SQL> alter session set container=orcl;
Session altered.
SQL> select TABLESPACE_NAME,ENCRYPTED from dba_tablespaces;
TABLESPACE_NAME ENC
------------------------------ ---
SYSTEM NO
SYSAUX NO
TEMP NO
USERS NO
AUDIT_DATA NO
ENCRYPT_TS YES
6 rows selected.
--- test it by creating a sample table in the encrypted tablespace
subhen002:orclt1 > sqlplus TDE_ADMIN/tde_admin@orcl_any
SQL*Plus: Release 12.1.0.2.0 Production on Fri Jul 1 16:26:52 2016
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, Real Application Clusters, Automatic Storage Management, OLAP,
Advanced Analytics, Real Application Testing and Unified Auditing options
SQL> show user
USER is "TDE_ADMIN"
SQL> create table test (id number(10)) tablespace encrypt_ts;
Table created.
----enable autologin and restart database
SQL> conn / as sysdba
Connected.
SQL> show con_name
CON_NAME
------------------------------
CDB$ROOT
SQL> ADMINISTER KEY MANAGEMENT CREATE AUTO_LOGIN KEYSTORE FROM KEYSTORE '+DATA_DG01/orclt1/WALLET/' IDENTIFIED BY enc_keystore;
keystore altered.
SQL> select * from V$ENCRYPTION_WALLET;
WRL_TYPE
--------------------
WRL_PARAMETER
--------------------------------------------------------------------------------
STATUS WALLET_TYPE WALLET_OR FULLY_BAC
------------------------------ -------------------- --------- ---------
CON_ID
----------
ASM
+DATA_DG01/orclt1/WALLET/
OPEN PASSWORD SINGLE NO
0
SQL> shu immediate
---- for 2nd instance TDE configure
jsut copy the wallet files to respective location for 2nd instance and restart the instance
--- wallet location was instance specific so i did configure for 1st instance then copied all the TDE files to respective location on 2nd instance & restarted 2nd instance
--- pre check for TDE
subhen002:orclt1 > sqlplus / as sysdba
SQL*Plus: Release 12.1.0.2.0 Production on Fri Jul 1 15:53:38 2016
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, Real Application Clusters, Automatic Storage Management, OLAP,
Advanced Analytics, Real Application Testing and Unified Auditing options
SQL> select * from V$ENCRYPTION_WALLET;
WRL_TYPE
--------------------
WRL_PARAMETER
--------------------------------------------------------------------------------
STATUS WALLET_TYPE WALLET_OR FULLY_BAC
------------------------------ -------------------- --------- ---------
CON_ID
----------
FILE
/dboracle/orabase/admin/orcl_tp/wallet
NOT_AVAILABLE UNKNOWN SINGLE UNDEFINED
0
--- take backup of sqlnet.ora and made entry to both nodes as below
subhen002:orclt1 > cp -p sqlnet.ora sqlnet.ora_bkp
subhen002:orclt1 > vi sqlnet.ora
#---------------------------#
# Network Tuning Parameters #
#---------------------------#
default_sdu_size=32767
send_buf_size=1048576
recv_buf_size=1048576
tcp.nodelay=yes
sqlnet.inbound_connect_timeout=180
sqlnet.expire_time=10
#TDE entry
ENCRYPTION_WALLET_LOCATION=
(SOURCE=
(METHOD=FILE)
(METHOD_DATA=
(DIRECTORY=+DATA_DG01/$ORACLE_SID/WALLET)
)
)
~
--- create directories in ASM
SQL> ALTER DISKGROUP DATA_DG01 ADD DIRECTORY '+DATA_DG01/orclt1';
Diskgroup altered.
SQL> ALTER DISKGROUP DATA_DG01 ADD DIRECTORY '+DATA_DG01/orclt1/WALLET/';
Diskgroup altered.
SQL> ALTER DISKGROUP DATA_DG01 ADD DIRECTORY '+DATA_DG01/orclt2';
Diskgroup altered.
SQL> ALTER DISKGROUP DATA_DG01 ADD DIRECTORY '+DATA_DG01/orclt2/WALLET/';
Diskgroup altered.
--- check again it should show ASM path
SQL> set lines 234 pages 234
SQL> col WRL_PARAMETER for a40
SQL> select WRL_TYPE, WRL_PARAMETER, STATUS from V$ENCRYPTION_WALLET;
WRL_TYPE WRL_PARAMETER STATUS
-------------------- ---------------------------------------- ------------------------------
ASM +DATA_DG01/orclt1/WALLET/ NOT_AVAILABLE
-- as root user and in root container execute the below sqls
SQL> show con_name
CON_NAME
------------------------------
CDB$ROOT
SQL> show user
USER is "SYS"
SQL> set lines 234 pages 234
SQL> col WRL_PARAMETER for a40
SQL> select WRL_TYPE, WRL_PARAMETER, STATUS from V$ENCRYPTION_WALLET;
WRL_TYPE WRL_PARAMETER STATUS
-------------------- ---------------------------------------- ------------------------------
ASM +DATA_DG01/orclt1/WALLET/ NOT_AVAILABLE
SQL> select CON_ID,KEY_ID,KEYSTORE_TYPE,CREATOR_DBNAME,CREATOR_PDBNAME from v$encryption_keys;
no rows selected
SQL> ADMINISTER KEY MANAGEMENT CREATE KEYSTORE '+DATA_DG01/orclt1/WALLET/' IDENTIFIED BY enc_keystore;
keystore altered.
SQL> select WRL_TYPE, WRL_PARAMETER, STATUS from V$ENCRYPTION_WALLET;
WRL_TYPE WRL_PARAMETER STATUS
-------------------- ---------------------------------------- ------------------------------
ASM +DATA_DG01/orclt1/WALLET/ CLOSED
SQL> ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY enc_keystore CONTAINER = ALL ;
keystore altered.
SQL> select * from V$ENCRYPTION_WALLET;
WRL_TYPE WRL_PARAMETER STATUS WALLET_TYPE WALLET_OR FULLY_BAC CON_ID
-------------------- ---------------------------------------- ------------------------------ -------------------- --------- --------- ----------
ASM +DATA_DG01/orclt1/WALLET/ OPEN_NO_MASTER_KEY PASSWORD SINGLE UNDEFINED 0
SQL> ADMINISTER KEY MANAGEMENT SET KEY USING TAG 'scope:all pdbs;description:Create Key for ALL PDBS' IDENTIFIED BY enc_keystore WITH BACKUP CONTAINER = ALL ;
keystore altered.
--- verify the creation in root & all PDBs
SQL> select * from V$ENCRYPTION_WALLET;
WRL_TYPE WRL_PARAMETER STATUS WALLET_TYPE WALLET_OR FULLY_BAC CON_ID
-------------------- ---------------------------------------- ------------------------------ -------------------- --------- --------- ----------
ASM +DATA_DG01/orclt1/WALLET/ OPEN PASSWORD SINGLE NO 0
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 orcl READ WRITE NO
SQL> alter session set container=orcl
2 ;
Session altered.
SQL> select * from V$ENCRYPTION_WALLET;
WRL_TYPE WRL_PARAMETER STATUS WALLET_TYPE WALLET_OR FULLY_BAC CON_ID
-------------------- ---------------------------------------- ------------------------------ -------------------- --------- --------- ----------
ASM +DATA_DG01/orclt1/WALLET/ OPEN PASSWORD SINGLE NO 0
SQL> select CON_ID,KEY_ID,KEYSTORE_TYPE,CREATOR_DBNAME,CREATOR_PDBNAME from v$encryption_keys;
CON_ID KEY_ID KEYSTORE_TYPE CREATOR_DBNAME CREATOR_PDBNAME
---------- ------------------------------------------------------------------------------ ----------------- ------------------------------ ------------------------------
0 AY/4VjtNE0+4vxqV8VtEEB4AAAAAAAAAAAAAAAAAAAAAAAAAAAAA SOFTWARE KEYSTORE orcl_tp orcl
--- create a sample user/schema for TDE use
SQL> show con_name
CON_NAME
------------------------------
orcl
SQL> create user tde_admin identified by tde_admin;
User created.
SQL> grant CREATE TABLE, CREATE TABLESPACE to tde_admin;
Grant succeeded.
SQL> grant connect,resource to tde_admin;
Grant succeeded.
SQL> exit
---- create a encrypted tablespace and assign to tde_admin
SQL> alter session set container=orcl
2 ;
Session altered.
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
3 orcl READ WRITE NO
SQL> show con_name
CON_NAME
------------------------------
orcl
SQL> CREATE TABLESPACE encrypt_ts
DATAFILE '+DATA_DG01' SIZE 1M
ENCRYPTION USING 'AES256'
DEFAULT STORAGE (ENCRYPT); 2 3 4
Tablespace created.
SQL> alter user tde_admin default tablespace encrypt_ts;
User altered.
SQL> show user
USER is "SYS"
--- check the tablespace should be created as encrypted
SQL> conn / as sysdba
Connected.
SQL> alter session set container=orcl;
Session altered.
SQL> select TABLESPACE_NAME,ENCRYPTED from dba_tablespaces;
TABLESPACE_NAME ENC
------------------------------ ---
SYSTEM NO
SYSAUX NO
TEMP NO
USERS NO
AUDIT_DATA NO
ENCRYPT_TS YES
6 rows selected.
--- test it by creating a sample table in the encrypted tablespace
subhen002:orclt1 > sqlplus TDE_ADMIN/tde_admin@orcl_any
SQL*Plus: Release 12.1.0.2.0 Production on Fri Jul 1 16:26:52 2016
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, Real Application Clusters, Automatic Storage Management, OLAP,
Advanced Analytics, Real Application Testing and Unified Auditing options
SQL> show user
USER is "TDE_ADMIN"
SQL> create table test (id number(10)) tablespace encrypt_ts;
Table created.
----enable autologin and restart database
SQL> conn / as sysdba
Connected.
SQL> show con_name
CON_NAME
------------------------------
CDB$ROOT
SQL> ADMINISTER KEY MANAGEMENT CREATE AUTO_LOGIN KEYSTORE FROM KEYSTORE '+DATA_DG01/orclt1/WALLET/' IDENTIFIED BY enc_keystore;
keystore altered.
SQL> select * from V$ENCRYPTION_WALLET;
WRL_TYPE
--------------------
WRL_PARAMETER
--------------------------------------------------------------------------------
STATUS WALLET_TYPE WALLET_OR FULLY_BAC
------------------------------ -------------------- --------- ---------
CON_ID
----------
ASM
+DATA_DG01/orclt1/WALLET/
OPEN PASSWORD SINGLE NO
0
SQL> shu immediate
---- for 2nd instance TDE configure
jsut copy the wallet files to respective location for 2nd instance and restart the instance
No comments:
Post a Comment