In this post, we will be checking out the most commonly used oracle tde wallet queries.
Table of Contents
How to open a wallet in Oracle 12c
Here is how to open a wallet in Oracle 12c for both CDB and Non-CDB Databases
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 a wallet in Oracle 11g
ALTER SYSTEM SET ENCRYPTION WALLET OPEN IDENTIFIED BY "<wallet password>";
How to close a 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 was never implemented. (TDE REKEY/SET KEY was never done for this database and the MKID of SYSTEM tablespace is the pre-generated ID)
How to Configure Auto Login Wallet
We can configure the auto-login wallet also, In that you don’t need to explicitly open the wallet., the database will automatically opens it. It will open for all the PDBs in the Non-CDB database
Command to do it
ADMINISTER KEY MANAGEMENT CREATE AUTO_LOGIN KEYSTORE FROM KEYSTORE '<wallet location>' IDENTIFIED BY <Wallet password>;
This will generate an extra file cwallet. sso in the wallet directory. Now you can just restart the database and the Database will start using Autologin Wallet
How To Export TDE Master Encryption Key
When you are migrating PDB from one database to another and the source PDB is encrypted, then you need to export the keys in the source database in order for the PDB to successfully migrate. This can be done using the below command
alter session set container=TESTPDB ADMINISTER KEY MANAGEMENT EXPORT ENCRYPTION KEYS WITH SECRET "my_secret" TO '<location of export file>' IDENTIFIED BY "<wallet password>";
This command requires the wallet to be opened in password mode. Else it will fail.
You can also export the keys if the wallet is opened in Autologin mode. It requires a certain patch level. Also, We need to add FORCE KEYSTORE
ADMINISTER KEY MANAGEMENT EXPORT ENCRYPTION KEYS WITH SECRET "my_secret" TO '<location of export file>' FORCE KEYSTORE IDENTIFIED BY "<wallet password>";
How To Import TDE Master Encryption Key
Now you import the exported keys into the target database after the Pluggable database is created
alter session set container=TESTPDB; ADMINISTER KEY MANAGEMENT IMPORT KEYS WITH SECRET "my_secret" from '<location of export file>' IDENTIFIED BY "<Wallet password" with backup;
This command requires the wallet to be opened in password mode. Else it will fail.
You can also import the keys if the wallet is opened in Autologin mode. But It requires a certain patch level. We also need to add FORCE KEYSTORE
alter session set container=TESTPDB;
ADMINISTER KEY MANAGEMENT IMPORT KEYS WITH SECRET "my_secret" from '<location of export file>' FORCE KEYSTORE IDENTIFIED BY "<Wallet password" with backup;
Related Articles
How To Restore TDE Wallet Files From Backup in Oracle Database
how to check if oracle database is encrypted
TDE encryption in Oracle 12c step by step
TDE encryption in oracle 11g step by step
https://docs.oracle.com/en/database/oracle/oracle-database/18/asoag/managing-keystore-and-tde-master-encryption-key.html
Leave a Reply