Transparent Data Encryption Queries

In this post, we will checking out mostly commonly used oracle wallet queries

Table of Contents

how to open wallet in oracle 12c

We have different command with different versions.

With 12c Non CDB

ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY <wallet password> ;

With 12c CDB

For all PDB in the Container

ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY <wallet pass> CONTAINER=ALL ;

for Root and individual PDB

ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY <wallet pass> ;

how to open wallet in oracle 11g

ALTER SYSTEM SET ENCRYPTION WALLET OPEN IDENTIFIED BY "<wallet password>";

how to close wallet in oracle 12c

With 12c NON CDB

ADMINISTER KEY MANAGEMENT SET KEYSTORE CLOSE IDENTIFIED BY <wallet password>;

With 12c CDB

For all PDB in the Container
ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY <wallet pass> CONTAINER=ALL ;
for Root and individual PDB
ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY <wallet pass> ;

how to close wallet in oracle 11g

ALTER SYSTEM SET ENCRYPTION WALLET CLOSE;

how to check wallet status in oracle

SQL>set linesize 200
SQL>col WALLET_DIR for a40
SQL>col status for a15
SQL>select STATUS,WRL_PARAMETER WALLET_DIR,WALLET_TYPE from V$ENCRYPTION_WALLET;

Query result to check if TDE was implemented prior or TDE was never implemented:

In 11g DB:

SELECT BITAND(FLAGS,8) FROM X$KCBDBK;

If this query returns 8, then TDE is/was implemented. (TDE REKEY/SET KEY is done for this database and the MKID of SYSTEM tablespace is the MKEYID of that previous/current wallet file)

If this query returns 0, then TDE is never implemented (TDE REKEY/SET KEY was never done for this database and the MKID of SYSTEM tablespace is the pre-generated ID)

In 12c DB:

select mkloc from x$kcbdbk;

If this query returns 1, then TDE is/was implemented. (TDE REKEY/SET KEY is done for this database and the MKID of SYSTEM tablespace is the MKEYID of that previous/current wallet file)

If this query returns 0, then TDE is never implemented.(TDE REKEY/SET KEY was never done for this database and the MKID of SYSTEM tablespace is the pre-generated ID)