Fix Database Encryption Key Rotation Failure (ORA-28407)

Yeah, key rotation failing is a pain. You run the command, and it just sits there or throws ORA-28407. I've been there more times than I can count. Let's cut to the chase.

The Short Fix

The culprit here is almost always that the old encryption key hasn't been properly deprecated. Oracle TDE (Transparent Data Encryption) won't rotate if the previous key isn't marked as DEPRECATED. Here's the exact command sequence that works on Oracle 12c through 21c:

-- Step 1: Check current key state
SELECT key_id, status FROM v$encryption_keys ORDER BY creation_time;

-- Step 2: Deprecate the old key explicitly
ADMINISTER KEY MANAGEMENT SET KEY
  USING TAG 'new_key_2024'
  IDENTIFIED BY "your_wallet_password"
  WITH BACKUP;

-- Step 3: Verify deprecation happened
SELECT key_id, status FROM v$encryption_keys WHERE status = 'DEPRECATED';

Don't bother with restarting the database or the wallet service first. That rarely helps. The real issue is Oracle's key management system gets stuck on the previous key's status. Run the above, and 9 times out of 10, the rotation completes in under a minute.

Why This Works

Here's the technical bit. When you rotate a TDE key, Oracle creates a new master key but expects the old one to be in a DEPRECATED state – not ACTIVE or USED. If for any reason the old key didn't get deprecated (maybe from a previous failed rotation, or a script that skipped that step), the new rotation command just hangs. The ADMINISTER KEY MANAGEMENT SET KEY command with WITH BACKUP forces a proper deprecation chain. It writes a new key, marks the old one as deprecated, and updates the wallet. No more stuck rotations.

Also worth noting: Oracle 19c and later are pickier about this. In 11g and 12.1, you could sometimes get away with skipping deprecation. Not anymore. The database won't let you create a new master key if the previous one still shows ACTIVE. It's a security thing – prevents a scenario where two keys are both active at the same time.

Less Common Variations

Sometimes the fix above doesn't cut it. Here are the edge cases I've seen:

Wallet Password Hash Mismatch

If you changed the wallet password recently, the hash stored in the keystore might not match what the database expects. Check with:

SELECT wrl_type, wallet_type, status FROM v$encryption_wallet;

If status is OPEN_NO_MASTER_KEY, you have a mismatched password. Fix it by reopening the wallet with the correct password:

ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN
  IDENTIFIED BY "correct_password";

Corrupted Wallet File

Rare, but happens. The ewallet.p12 file can get corrupted if the server crashed mid-write during a previous rotation. Check the file size – it should be at least 2KB. If it's 0 bytes or very small, restore from backup.

Key Already Rotated by Another Session

If two DBAs ran the rotation command at the same time (yeah, I've seen it), one session gets a lock conflict. Check v$session for blocking locks and kill the other session. Then rerun the rotation.

Prevention for Next Time

Stop this happening again with three rules:

Always deprecate the old key before rotating. Make it a habit. Script it. If you're using a tool like OEM, double-check it runs the deprecation step.
Never rotate during peak hours. If the wallet write fails due to high I/O, you get the stuck state. Schedule it during maintenance windows.
Keep a backup of the wallet before any rotation. Use WITH BACKUP every time. It's a safety net. I've restored wallets more times than I want to admit.
Monitor key status weekly. A simple query to check all keys are in ACTIVE or DEPRECATED state. No USED keys should linger.

That's it. The fix is simple once you know the deprecation trick. Save this page, bookmark it, whatever. You'll need it again. I guarantee it.