2021-04-26T21:14:47.432344+05:30 DEVPDB(3):ALTER PLUGGABLE DATABASE OPEN detects that an encrypted tablespace has been restored but the master key has not been set for the database, or the database has been flashback'ed prior to first set key of the master key (pdb 3) . DEVPDB(3): Resetting the master key is required. Please execute ADMINISTER KEY MANAGEMENT SET ENCRYPTION KEY command, or select the latest master key from V$ENCRYPTION_KEYS and execute ADMINISTER KEY MANAGEMENT USE KEY <key_id> command if the SET ENCRYPTION KEY command cannot find and decide the master key to use.
If TDE is not used then no such message is shown.
This error is shown only when an auto login wallet is in place. Doesn't matter if the wallet is local auto login or not message still appears.
Message is not shown when auto login wallet is removed and key store is opened manually after CDB start. Message is shown with respect to the user created PDB.
Even when the above message is shown, there's no issue in accessing the encrypted tablespaces and querying data.
Setting the encryption key or recreating the auto login wallet has no effect on it. Below steps were carried out but still the message remains.
alter session set container=devpdb; Session altered. ADMINISTER KEY MANAGEMENT SET ENCRYPTION KEY IDENTIFIED BY asanga321 WITH BACKUP; keystore altered. SELECT con_id,MASTERKEY_ACTIVATED FROM V$DATABASE_KEY_INFO; CON_ID MAS ---------- --- 3 YES SQL> conn / as sysdba Connected. SQL> ADMINISTER KEY MANAGEMENT CREATE local AUTO_LOGIN KEYSTORE FROM KEYSTORE IDENTIFIED BY asanga321; keystore altered.There was no tablespace restore or flashback on it. It seems when auto login wallet is involved for some reason the correct master key is not identified.
Rolling back the DBRU 19.11 (DB went back to 19.10) resolved the issue. So it appears this is something introduced with the DBRU 19.11 patch. To confirm this a new database was created using 19.3 base release and 19.11 DBRU. Setup TDE and auto login wallet (below output from alert log).
TESTPDB(3):Creating new database key for new master key and wallet TESTPDB(3):Creating new database key with the new master key TESTPDB(3):New database key and new master key created successfully TESTPDB(3):create tablespace enctest datafile size 10m ENCRYPTION USING 'AES256' ENCRYPT TESTPDB(3):Completed: create tablespace enctest datafile size 10m ENCRYPTION USING 'AES256' ENCRYPTThen restarted the database and could see the following on the alert log.
TESTPDB(3):ALTER PLUGGABLE DATABASE OPEN detects that an encrypted tablespace has been restored but the master key has not been set for the database, or the database has been flashback'ed prior to first set key of the master key (pdb 3). TESTPDB(3): Resetting the master key is required. Please execute ADMINISTER KEY MANAGEMENT SET ENCRYPTION KEY command, or select the latest master key from V$ENCRYPTION_KEYS and execute ADMINISTER KEY MANAGEMENT USE KEY <key_id> command if the SET ENCRYPTION KEY command cannot find and decide the master key to use.To further isolate the issue, created a third database was craeted. This time only TDE was setup, no encrypted tablespaces.
2021-05-11T11:23:48.690428+00:00 Creating new database key for new master key and wallet Creating new database key with the new master key Switching out all online logs for the new master key 2021-05-11T11:23:48.746184+00:00 Thread 1 advanced to log sequence 16 (LGWR switch), current SCN: 1288117 Current log# 1 seq# 16 mem# 0: +DATA/TDETEST/ONLINELOG/group_1.297.1072260997 Current log# 1 seq# 16 mem# 1: +FRA/TDETEST/ONLINELOG/group_1.530.1072260999 2021-05-11T11:23:48.748707+00:00 Logfile switch for new master key complete New database key and new master key created successfully TDETESTPDB(3):Creating new database key for new master key and wallet TDETESTPDB(3):Creating new database key with the new master key TDETESTPDB(3):New database key and new master key created successfullyIn this case too the alert log showed the same message as before.
2021-05-11T11:24:58.081692+00:00 TDETESTPDB(3):ALTER PLUGGABLE DATABASE OPEN detects that an encrypted tablespace has been restored but the master key has not been set for the database, or the database has been flashback'ed prior to first set key of the master key (pdb 3). TDETESTPDB(3): Resetting the master key is required. Please execute ADMINISTER KEY MANAGEMENT SET ENCRYPTION KEY command, or select the latest master key from V$ENCRYPTION_KEYS and execute ADMINISTER KEY MANAGEMENT USE KEY <key_id> command if the SET ENCRYPTION KEY command cannot find and decide the master key to use.
The auto login wallet does contain the same key ids which could be checked with
SQL> show pdbs CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- 2 PDB$SEED READ ONLY NO 3 TESTPDB READ WRITE NO SQL> select * from gv$encryption_wallet; INST_ID WRL_TYPE WRL_PARAMETER STATUS WALLET_TYPE WALLET_OR KEYSTORE FULLY_BAC CON_ID ---------- -------------------- ------------------------------ ------------------------------ -------------------- --------- -------- --------- ---------- 1 FILE /opt/app/oracle/wallet/tde/ OPEN AUTOLOGIN SINGLE NONE NO 1 1 FILE OPEN AUTOLOGIN SINGLE UNITED NO 2 1 FILE OPEN AUTOLOGIN SINGLE UNITED NO 3 SQL> select con_id,KEY_ID,CREATION_TIME,activation_time FROM V$ENCRYPTION_KEYS; CON_ID KEY_ID CREATION_TIME ACTIVATION_TIME ---------- ------------------------------------------------------------ ----------------------------------- -------------------- 3 AVQm2JpHDk+jv59DdGa3lz4AAAAAAAAAAAAAAAAAAAAAAAAAAAAA 28-APR-21 04.25.22.932575 PM +05:30 28-APR-21 04.25.23.243620 PM +05:30 1 ATJSoZoecE+jv6iBZKWk2s0AAAAAAAAAAAAAAAAAAAAAAAAAAAAA 28-APR-21 04.25.22.830950 PM +05:30 28-APR-21 04.25.23.064019 PM +05:30 orapki wallet display -wallet /opt/app/oracle/wallet/tde Oracle PKI Tool Release 19.0.0.0.0 - Production Version 19.4.0.0.0 Copyright (c) 2004, 2021, Oracle and/or its affiliates. All rights reserved. Requested Certificates: Subject: CN=oracle User Certificates: Oracle Secret Store entries: ORACLE.SECURITY.DB.ENCRYPTION.ATJSoZoecE+jv6iBZKWk2s0AAAAAAAAAAAAAAAAAAAAAAAAAAAAA ORACLE.SECURITY.DB.ENCRYPTION.AVQm2JpHDk+jv59DdGa3lz4AAAAAAAAAAAAAAAAAAAAAAAAAAAAA ORACLE.SECURITY.DB.ENCRYPTION.MASTERKEY ORACLE.SECURITY.DB.ENCRYPTION.MASTERKEY.C107201B5EA38B36E053FE04A8C049CF ORACLE.SECURITY.ID.ENCRYPTION. ORACLE.SECURITY.KB.ENCRYPTION. ORACLE.SECURITY.KM.ENCRYPTION.ATJSoZoecE+jv6iBZKWk2s0AAAAAAAAAAAAAAAAAAAAAAAAAAAAA ORACLE.SECURITY.KM.ENCRYPTION.AVQm2JpHDk+jv59DdGa3lz4AAAAAAAAAAAAAAAAAAAAAAAAAAAAA ORACLE.SECURITY.KT.ENCRYPTION.ATJSoZoecE+jv6iBZKWk2s0AAAAAAAAAAAAAAAAAAAAAAAAAAAAA ORACLE.SECURITY.KT.ENCRYPTION.AVQm2JpHDk+jv59DdGa3lz4AAAAAAAAAAAAAAAAAAAAAAAAAAAAA Trusted Certificates:The issue was happening on OCI DBCS VM databases after applying the DBRU 19.11.
Raised a P1 24x7 SR and 48 hours later Oracle confirmed this is internal bug 29528184 filed as happening only on exadata. Reason for issue is given as "The lookup of MKID for PDB was done in PDB's keystore which was not OPEN". It seems the bug is now packaged with 19.11 thus seeing it on OCI DBCS and on-prem DBs.
No fix available yet. Oracle is working on a backport for non exadata environments.
Update on 2021-07-02
Patch 29528184: X5-2: OPEN PDB HAD "MASTER KEY NOT SET" MESSAGE IN ALERT.LOG AFTER WE ENABLED TDE is now available to address this issue.
