Oracle Public Cloud Database Service – ORA-28374: typed master key not found in wallet

by Seth Miller

While prepping for the Cloud Attack! workshop at COLLABORATE 16, I spent some quality (maybe not so much quality as quantity) time on MOS and a number of blogs troubleshooting an issue on a 12c multitenant database in Oracle’s Public Cloud Database Service.

I was running through the labs for the workshop when I came across an error I had never seen before. One of the very first steps in the first lab is to migrate a local PDB to the public cloud CDB using standard unplug and plug. The migration of the PDB worked fine and I was able to open the migrated PDB in the public cloud CDB. However, I forgot to create the extra tablespace in the PDB for the next step in the lab while the PDB was still plugged into my local CDB.

It didn’t seem like a big deal to try to create a standard tablespace in the PDB post public cloud migration. But, when I executed the command, I received the error, ORA-28374: typed master key not found in wallet.

Since I didn’t take an screen shots while it was happening, I decided to recreate the error without having to redo the migration. In fact, any PDB created in an Oracle Public Cloud CDB (other than the one created with the CDB creation) will have the same behavior.

For this demonstration, I have already created a new 12c multitenant database in the Oracle Public Cloud Database Service. Included with the database creation is one PDB called PDB1.

Creating tablespaces in the root container works fine.

SQL> connect / as sysdba
Connected.
SQL> create tablespace test;

Tablespace created.

Creating tablespaces in the PDB that was installed with the database works fine.

SQL> conn pdbadmin/pdbadmin@localhost/pdb1.smiller.oraclecloud.internal
Connected.
SQL> create tablespace test;

Tablespace created.

Since the error is related to the encryption wallet, the following query executed in both the root container and PDB1 show that the autologin wallet is open.

SQL> select status, wallet_type, con_id from v$encryption_wallet;

STATUS                         WALLET_TYPE              CON_ID
------------------------------ -------------------- ----------
OPEN                           AUTOLOGIN                     0

Whether you import a PDB from a non-cloud CDB or create a new one post installation, the behavior is the same.

Now I will create a new pluggable database called PDB2, open it, and connect to it.

SQL> create pluggable database pdb2 admin user pdbadmin identified by pdbadmin roles=(DBA);

Pluggable database created.

SQL> alter pluggable database pdb2 open;

Pluggable database altered.

SQL> connect pdbadmin/pdbadmin@localhost/pdb2.smiller.oraclecloud.internal
Connected.

When I try to create a tablespace in PDB2, I receive the ORA-28374 error.

SQL> create tablespace test;
create tablespace test
*
ERROR at line 1:
ORA-28374: typed master key not found in wallet

The wallet is maintained at the root container and I know it is autologin so it should be open.

SQL> select status, wallet_type, con_id from v$encryption_wallet;

STATUS                         WALLET_TYPE              CON_ID
------------------------------ -------------------- ----------
OPEN_NO_MASTER_KEY             AUTOLOGIN                     0

The wallet is open, but the status of OPEN_NO_MASTER_KEY tells me that the master key hasn’t yet been created for this pluggable database.

The next step then is to try to create the master key from PDB2.

SQL> connect sys/oracle_4U@localhost/pdb2.smiller.oraclecloud.internal as sysdba
Connected.
SQL> administer key management set key identified by oracle_4U;
administer key management set key identified by oracle_4U
*
ERROR at line 1:
ORA-46658: keystore not open in the container

The error message seems to indicate that the keystore (another term for the wallet) is not open but this error is actually a bit misleading. We know the keystore is open from the previous query. What it actually means is that keys cannot be modified in an autologin wallet.

So, we need to close the autologin wallet (cwallet.sso) and explicity open the file wallet (ewallet.p12) from PDB2.

SQL> administer key management set keystore close;
administer key management set keystore close
*
ERROR at line 1:
ORA-28365: wallet is not open

This is misleading as well because we know the wallet is open. However, the wallet is controlled from the root container and needs to be closed from there.

Connect to the root container and close the autologin wallet.

SQL> conn / as sysdba
Connected.
SQL> select status, wallet_type, con_id from v$encryption_wallet;

STATUS                         WALLET_TYPE              CON_ID
------------------------------ -------------------- ----------
OPEN                           AUTOLOGIN                     0

SQL> administer key management set keystore close;

keystore altered.

SQL> select status, wallet_type, con_id from v$encryption_wallet;

STATUS                         WALLET_TYPE              CON_ID
------------------------------ -------------------- ----------
OPEN                           AUTOLOGIN                     0

Huh? When we issue the keystore close command, the result message seems to indicate it was successfully closed, but it still shows as open.

MOS Note 1944507.1 indicates that this behavior is a limitation (read bug) and an enhancement request has already been filed.

The workaround is to delete the autologin wallet file (cwallet.sso) from the directory on the OS.

SQL> select '!rm ' || wrl_parameter || 'cwallet.sso' from v$encryption_wallet;

'!RM'||WRL_PARAMETER||'CWALLET.SSO'
--------------------------------------------------------------------------------
!rm /u01/app/oracle/admin/ORCL/tde_wallet/cwallet.sso

SQL> !rm /u01/app/oracle/admin/ORCL/tde_wallet/cwallet.sso

SQL> administer key management set keystore close;

keystore altered.

SQL> select status, wallet_type, con_id from v$encryption_wallet;

STATUS                         WALLET_TYPE              CON_ID
------------------------------ -------------------- ----------
CLOSED                         UNKNOWN                       0

That’s better. Now we should be able to open the wallet file explicitly.

SQL> administer key management set keystore open identified by Oracle_4U;

keystore altered.

SQL> select status, wallet_type, con_id, wrl_type from v$encryption_wallet;

STATUS                         WALLET_TYPE              CON_ID WRL_TYPE
------------------------------ -------------------- ---------- --------------------
OPEN                           PASSWORD                      0 FILE

The wallet file is open so we should be able to connect to PDB2 and set/create the master key.

SQL> connect sys/oracle_4U@localhost/pdb2.smiller.oraclecloud.internal as sysdba
Connected.
SQL> administer key management set key identified by oracle_4U;
administer key management set key identified by oracle_4U
*
ERROR at line 1:
ORA-46658: keystore not open in the container


SQL> select status, wallet_type, con_id, wrl_type from v$encryption_wallet;

STATUS                         WALLET_TYPE              CON_ID WRL_TYPE
------------------------------ -------------------- ---------- --------------------
CLOSED                         UNKNOWN                       0 FILE

The error message and the query show that the wallet is still closed in PDB2 even after opening the wallet from the root container. Opening the wallet in the root container does not open the wallet in the pluggable containers.

SQL> administer key management set keystore open identified by oracle_4U;

keystore altered.

SQL> select status, wallet_type, con_id, wrl_type from v$encryption_wallet;

STATUS                         WALLET_TYPE              CON_ID WRL_TYPE
------------------------------ -------------------- ---------- --------------------
OPEN_NO_MASTER_KEY             PASSWORD                      0 FILE

Almost there. Although the wallet is open on PDB2, we still don’t have a master key but the WALLET_TYPE and WRL_TYPE columns indicate this is a file wallet, not an autologin wallet which is what want. Now, we should be able to create the master key for PDB2.

SQL> administer key management set key identified by Oracle_4U;
administer key management set key identified by Oracle_4U
*
ERROR at line 1:
ORA-46631: keystore needs to be backed up

It is a requirement to back up the wallet before creating a new master key to keep you from losing your current master key (even if one does not yet exist).

Why might you need your old master keys? If you encrypt your RMAN backups with transparent or dual mode encryption (which both use keys from the wallet), you need the keys you used to encrypt those backups in order to decrypt them if you need to restore those backups. In order to keep those keys around, the wallet needs to be backed up.

Fortunately, you can create the new key and create the backup with a single command.

SQL> administer key management set key identified by Oracle_4U with backup;

keystore altered.

SQL> select status, wallet_type, con_id, wrl_type from v$encryption_wallet;

STATUS                         WALLET_TYPE              CON_ID WRL_TYPE
------------------------------ -------------------- ---------- --------------------
OPEN                           PASSWORD                      0 FILE

Now that we have an open wallet and a fresh new master key in PDB2, we should be able to create a tablespace.

SQL> create tablespace test;

Tablespace created.

Success!

I don’t want to have to open the wallet every time I restart the instance, so I want to put the autologin wallet back in place. To do that, we simply create a new autologin wallet from the file wallet.

SQL> select 'administer key management create auto_login keystore from keystore ''' ||  wrl_parameter || ''' identified by Oracle_4U;' from v$encryption_wallet;

'ADMINISTERKEYMANAGEMENTCREATEAUTO_LOGINKEYSTOREFROMKEYSTORE'''||WRL_PARAMETER||'''IDENTIFIEDBYORACLE4U;'
------------------------------------------------------------------------------------------------------------------------------------------------------
administer key management create auto_login keystore from keystore '/u01/app/oracle/admin/ORCL/tde_wallet/' identified by Oracle_4U;

SQL> administer key management create auto_login keystore from keystore '/u01/app/oracle/admin/ORCL/tde_wallet/' identified by Oracle_4U;
administer key management create auto_login keystore from keystore '/u01/app/oracle/admin/ORCL/tde_wallet/' identified by Oracle_4U
*
ERROR at line 1:
ORA-65040: operation not allowed from within a pluggable database

The error message indicates that the operation of creating the autologin wallet cannot be done from a pluggable database which makes sense since the wallet is maintained from the root container.

SQL> conn / as sysdba
Connected.
SQL> administer key management create auto_login keystore from keystore '/u01/app/oracle/admin/ORCL/tde_wallet/' identified by Oracle_4U;

keystore altered.

I found out later that all of the steps shown above can be done from the root container, which makes it a lot easier if you have more (especially if you have a lot more) than one PDB to re-key. Just make sure you are aware that you are re-keying all of the PDBs, regardless of whether they currently have a master key.

To demonstrate, I’ll follow all of the steps to fix the ORA-28374 issue from only the root container. I’m using PDB3 as the new pluggable database instead of PDB2 from the previous demonstration.

SQL> show con_name

CON_NAME
------------------------------
CDB$ROOT
SQL> select status, wallet_type, con_id from v$encryption_wallet;

STATUS                         WALLET_TYPE              CON_ID
------------------------------ -------------------- ----------
OPEN                           AUTOLOGIN                     0

SQL> create pluggable database pdb3 admin user pdbadmin identified by pdbadmin;

Pluggable database created.

SQL> alter pluggable database pdb3 open;

Pluggable database altered.

SQL> conn sys/oracle_4U@localhost/pdb3.smiller.oraclecloud.internal as sysdba
Connected.
SQL> select status, wallet_type, con_id from v$encryption_wallet;

STATUS                         WALLET_TYPE              CON_ID
------------------------------ -------------------- ----------
OPEN_NO_MASTER_KEY             AUTOLOGIN                     0

SQL> conn / as sysdba
Connected.
SQL> select '!rm ' || wrl_parameter || 'cwallet.sso' from v$encryption_wallet;

'!RM'||WRL_PARAMETER||'CWALLET.SSO'
------------------------------------------------------------------------------------------------------------------------------------------------------
!rm /u01/app/oracle/admin/ORCL/tde_wallet/cwallet.sso

SQL> !rm /u01/app/oracle/admin/ORCL/tde_wallet/cwallet.sso

SQL> administer key management set keystore close;

keystore altered.

SQL> select status, wallet_type, con_id from v$encryption_wallet;

STATUS                         WALLET_TYPE              CON_ID
------------------------------ -------------------- ----------
CLOSED                         UNKNOWN                       0

The next step is to open the wallet file but instead of opening it just for the root container, we can open it for all of the PDBs. We can also re-key all of the PDBs at the same time.

SQL> administer key management set keystore open identified by Oracle_4U container=all;

keystore altered.

SQL> administer key management set key identified by Oracle_4U with backup container=all;

keystore altered.

SQL> administer key management create auto_login keystore from keystore '/u01/app/oracle/admin/ORCL/tde_wallet/' identified by Oracle_4U;

keystore altered.

SQL> conn sys/oracle_4U@localhost/pdb3.smiller.oraclecloud.internal as sysdba
Connected.
SQL> create tablespace test;

Tablespace created.

The day after going through this waste of time valuable learning experience, I saw a tweet from one of my favorite Germans (don’t worry @Brost, you’re still number one) about a blog post dealing with this very same issue.

It turns out that this is intentional behavior and unique to Oracle Public Cloud databases. Read Mike Dietrich’s blog post for more details.