Key Management in Oracle: The Core Issue: Missing Master Key

Here is the step-by-step summary of the issue and the resolution, including the exact commands used.

1. The Core Issue: Missing Master Key

In an Oracle Cloud (OCI) environment, every Pluggable Database (PDB) must have its own “Master Encryption Key” stored in a secure file called a Wallet. When you tried to clone YOURPDB, the operation failed because this specific PDB had never been assigned its own key.

How we checked for the missing key:

SQL

-- Run in Source CDB
SELECT CON_ID, KEY_ID FROM V$ENCRYPTION_KEYS 
WHERE CON_ID = (SELECT CON_ID FROM V$PDBS WHERE NAME = 'YOURPDB');
-- Result was "no rows selected," confirming the key was missing.

---

2. The Hurdle: Auto-Login vs. Password

OCI databases usually run with an Auto-Login wallet. This is a “read-only” mode that allows the database to start up automatically. However, you cannot create a new key while the wallet is in this read-only mode. We had to manually “log in” with the administrative password to gain write access.

How we gained write access (at the CDB level):

SQL

-- Step A: Close the "Read-Only" session
ADMINISTER KEY MANAGEMENT SET KEYSTORE CLOSE;

-- Step B: Open the "Write" session using your password
ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY "Your_Password";

-- If this is giving error for ORA-28354: encrypt or decrypt output file cannot be created (or the related ORA-28374) occurs because you are attempting to perform a write operation—opening a keystore with a password—while the database is already using an AUTOLOGIN wallet.

---

3. The Fix: Creating the PDB Key

Once we had write access at the top level, we had to go specifically into the PDB and tell Oracle to generate a new unique key for it.

How we created the new key:

SQL

-- Step C: Move into the PDB
ALTER SESSION SET CONTAINER = YOURPDB;

-- Step D: Open the PDB's specific "drawer" in the wallet
ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY "Your_Password";

-- Step E: Create and activate the new Master Key
ADMINISTER KEY MANAGEMENT SET KEY IDENTIFIED BY "Your_Password" WITH BACKUP;

---

4. The Result: Success

After running these, the PDB finally had a “Master Key ID.” You verified this by running the query again and seeing a long string of numbers and letters (the KEY_ID) assigned to your PDB.

The final verification query:

SQL

SELECT CON_ID, KEY_ID, ACTIVATION_TIME FROM V$ENCRYPTION_KEYS;

Summary for your records:

• The Error: ORA-28374 meant the PDB was “unlocked” but had no key to move.
• The Fix: We used the admin password to create a unique key for the PDB.