Oracle DBA

Oracle Database 12c Release 1 (12.1) introduced a modern and unified way to manage encryption keys, keystores, and secrets through the ADMINISTER KEY MANAGEMENT command.
This replaced the legacy commands:

• ALTER SYSTEM SET ENCRYPTION KEY
• ALTER SYSTEM SET ENCRYPTION WALLET

The Oracle documentation often uses both wallet and keystore interchangeably, but the preferred and forward-looking term is keystore, aligning with Java standards.

Understanding TDE in a Multitenant Environment

With the advent of the Multitenant Architecture, key management became more sophisticated.

• The root container (CDB$ROOT) requires an open keystore with an active master encryption key.
• The CDB’s keystore stores encryption keys for all Pluggable Databases (PDBs), but each PDB needs its own master key.
• When unplugging a PDB, you must export its master encryption key to re-import it after plugging into another container.

This guide walks you through the essential TDE key management tasks in an Oracle 12c multitenant setup.

Keystore Location

A keystore stores encryption keys used for Transparent Data Encryption (TDE). Oracle searches for the keystore in the following order:

1. The location specified by ENCRYPTION_WALLET_LOCATION in sqlnet.ora.
2. The location specified by WALLET_LOCATION in sqlnet.ora.
3. The default location:
   • If $ORACLE_BASE is set: $ORACLE_BASE/admin/DB_UNIQUE_NAME/wallet
   • Otherwise: $ORACLE_HOME/admin/DB_UNIQUE_NAME/wallet

Note: Keystores should never be shared between CDBs.
If you run multiple CDBs from the same ORACLE_HOME, isolate them using one of the following:

Option 1 – Use the Default Keystore Path

Each CDB automatically gets its own keystore.

Option 2 – Define a Custom Location

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

Option 3 – Separate sqlnet.ora for Each Database

Ensure the TNS_ADMIN environment variable is properly set for each database instance.

⚠️ Caution:
Losing the keystore means losing access to encrypted data.
Always back it up safely.

Create a Keystore

Edit the sqlnet.ora file:

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

Create the directory:

mkdir -p /u01/app/oracle/admin/$ORACLE_SID/encryption_keystore

Connect to the root container and create the keystore:

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

Verify the keystore file:

HOST ls /u01/app/oracle/admin/cdb1/encryption_keystore/
ewallet.p12

Open and Close the Keystore

-- Open
ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY myPassword CONTAINER=ALL;

-- Close
ADMINISTER KEY MANAGEMENT SET KEYSTORE CLOSE IDENTIFIED BY myPassword CONTAINER=ALL;

If CONTAINER=ALL is omitted, only the current container is affected.

Create and Activate Master Encryption Keys

Each container (CDB and PDB) requires its own master encryption key.

ADMINISTER KEY MANAGEMENT SET KEY IDENTIFIED BY myPassword WITH BACKUP CONTAINER=ALL;

Check existing keys:

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

Check Keystore Status

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

Use the Keystore for TDE

Once the keystore is active, you can encrypt columns or entire tablespaces.

Example: Encrypted Column

CONN test/test@pdb1

CREATE TABLE tde_test (
  id    NUMBER(10),
  data  VARCHAR2(50) ENCRYPT
);

INSERT INTO tde_test VALUES (1, 'This is a secret!');
COMMIT;

Example: Encrypted Tablespace

CONN sys@pdb1 AS SYSDBA

CREATE TABLESPACE encrypted_ts
DATAFILE SIZE 128K
AUTOEXTEND ON NEXT 64K
ENCRYPTION USING 'AES256'
DEFAULT STORAGE(ENCRYPT);

ALTER USER test QUOTA UNLIMITED ON encrypted_ts;

CONN test/test@pdb1

CREATE TABLE tde_ts_test (
  id    NUMBER(10),
  data  VARCHAR2(50)
) TABLESPACE encrypted_ts;

INSERT INTO tde_ts_test VALUES (1, 'This is also a secret!');
COMMIT;

Reopening Keystore After Restart

After a PDB restart, open the keystore again before accessing encrypted data.

CONN sys@pdb1 AS SYSDBA
SHUTDOWN IMMEDIATE;
STARTUP;
ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY myPassword;

If the CDB is restarted:

CONN / AS SYSDBA
SHUTDOWN IMMEDIATE;
STARTUP;
ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY myPassword CONTAINER=ALL;

---

Unplug/Plugin PDBs with TDE

Let’s unplug PDB1 from CDB1 and plug it into CDB2 as PDB2.

1. Export the Encryption Keys

ORAENV_ASK=NO
export ORACLE_SID=cdb1
. oraenv
ORAENV_ASK=YES
sqlplus /nolog

CONN sys@pdb1 AS SYSDBA
ADMINISTER KEY MANAGEMENT EXPORT ENCRYPTION KEYS WITH SECRET "mySecret" TO '/tmp/export.p12' IDENTIFIED BY myPassword;

2. Unplug the PDB

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

3. Plug the PDB into a New CDB

ORAENV_ASK=NO
export ORACLE_SID=cdb2
. oraenv
ORAENV_ASK=YES
sqlplus /nolog

CONN / AS SYSDBA
CREATE PLUGGABLE DATABASE pdb2 USING '/tmp/pdb1.xml';
ALTER PLUGGABLE DATABASE pdb2 OPEN READ WRITE;

If CDB2 does not yet have a keystore:

HOST mkdir -p /u01/app/oracle/admin/cdb2/encryption_keystore/

ADMINISTER KEY MANAGEMENT CREATE KEYSTORE '/u01/app/oracle/admin/cdb2/encryption_keystore/' IDENTIFIED BY myPassword;
ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY myPassword;

4. Import the Encryption Keys

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;

SHUTDOWN;
STARTUP;
ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY "myPassword";

Now the encrypted data is available:

CONN test/test@pdb2
SELECT * FROM tde_test;
SELECT * FROM tde_ts_test;

Auto-Login Keystores

An auto-login keystore eliminates the need to open the keystore manually after a restart.
Once configured, the keystore opens automatically on the first reference to an encryption key.

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;

SYSKM Role

Oracle introduced the SYSKM privilege in 12c specifically for key management operations.
TDE administration can be performed by users with either:

• SYSDBA, or
• SYSKM privileges.