Applies to:

Advanced Networking Option - Version 11.2.0.3 and later
Information in this document applies to any platform.

Goal

This note investigates potential causes for the ORA-28374 error and the investigation path to address and solve these errors.

Solution

Generic information regarding the master key and the master key changes:

The meaning of the error:

28374, 0000, "typed master key not found in wallet"
// *Cause: You attempted to access encrypted tablespace or redo logs with
// a typed master key not existing in the wallet.
// *Action: Copy the correct Oracle Wallet from the instance where the tablespace
// was created.

To understand the potential source of the error, it is important to understand what data is/can be encrypted:
1. tablespaces
2. tables
3. redo logs and archive logs
4. temporary data in temporary tablespaces (when the temporary data comes from encrypted sources)

TDE encrypted data is used, for each type of object above, using an encryption key. The encryption key for each type of object is stored in a different location, as below:
1. tablespaces: datafile headers
2. tables: dictionary table enc$
3. redo logs and archive logs: redo log/archive log file header
4. temporary tablespace: system datafile header.
Each encryption key is, in turn, encrypted using the master encryption key that is stored in the wallet.

When the master encryption key is reset, the encryption key of each of the above objects is decrypted and encrypted back with the new master key.
To generate a new master encryption key, run:
alter system set encryption key identified by "wallet password";
this should perform the following:
+ verify the master encryption key consistency (see below queries).
+ generate a new master encryption key (note the master key id is just a string of characters)
+ decrypt the currently encrypted encryption keys for tables, tablespaces, temporary
+ encrypt the encryption keys for tables, tablespaces and temporary using the new master key.
+ update the master key id into the wallet and above mentioned places (enc$, datafile headers, control file and system datafile header).

Consequently, if the master encryption key is lost, so would be any of the above mentioned objects, if encrypted with the lost master encryption key.
Such a loss is unrecoverable. Encrypted objects that have lost their master encryption key cannot be recovered and should be dropped and recreated.
If there is a master key inconsistency in any of the encrypted objects, the master key change fails.

The master encryption key is identified using a master key ID. The master key ID can be determined using the data dictionary tables:
enc$ - for tables
x$kcbtek - for tablespaces
x$kcbdbk - the controlfile holds a copy of the master key ID as well.

What data to collect in case of ORA-28374.

Retrieve the master key id from all available locations:

1. tables:
select mkeyid from enc$;

2. tablespaces and temporary encryption (master key ID for temporary tablespaces can be seen in the system tablespace data):
set linesize 150
column name format a40
column masterkeyid_base64 format a60
select name,utl_raw.cast_to_varchar2( utl_encode.base64_encode('01'||substr(mkeyid,1,4))) || utl_raw.cast_to_varchar2( utl_encode.base64_encode(substr(mkeyid,5,length(mkeyid)))) masterkeyid_base64 FROM (select t.name, RAWTOHEX(x.mkid) mkeyid from v$tablespace t, x$kcbtek x where t.ts#=x.ts#);

3. control file:
select utl_raw.cast_to_varchar2( utl_encode.base64_encode('01'||substr(mkeyid,1,4))) || utl_raw.cast_to_varchar2( utl_encode.base64_encode(substr(mkeyid,5,length(mkeyid)))) masterkeyid_base64 FROM (select RAWTOHEX(mkid) mkeyid from x$kcbdbk);

4. master key id as it is within the wallet:
mkstore -wrl -viewEntry ORACLE.SECURITY.DB.ENCRYPTION.MASTERKEY

All the results of the above queries must be consistent. The yielded result is the id of the master key.
If all above mentioned statements return consistent results, then the master key change would succeed.
If there is an inconsistency, at any level, it would fail.

How to address the situations when the error ORA-28374 "typed master key not found in wallet" occurs?

There are 2 possible scenarios:
Scenario #1. the wallet contents is not consistent with any of the information in the dictionary/controlfile/file headers.
In such a situation, the wallet of the database has been lost. If the correct wallet cannot be recovered, then all encrypted information is lost.
The solution for such situations is to:
1. drop the encrypted objects.
2. perform a full log switch rotation.
3. recreate the wallet.
4. recreate the encrypted objects.
If the correct wallet can be found, then there is no need to perform any recovery or wallet recreation.

Scenario #2. the wallet content is consistent to some extent with the data in the dictionary/controlfile/file headers, eg: the enc$ matches the wallet contents but does not match the file headers for datafiles or viceversa.
This scenario requires decrypting the encrypted data to whatever extent possible, depending on whether the needed wallets can be found, recreating the wallet and encrypting back the encrypted data. The data for which the master key (ie the correct wallet) cannot be found is lost.
1. as much as possible, find the wallet (or wallets) with the master key that are required by the encrypted objects.
Then, for each of these wallets:
2. open the wallet.
3. decrypt the encrypted data.
After decrypting all encrypted data:
4. perform a full log switch rotation.
5. recreate the wallet.
6. recreate the encrypted objects.
An exception to this scenario is when the system tablespace master key id and the control file master key id still coincide. If the wallet with the correct master key can be found, then all objects with the same master key id can be preserved as such. All the other objects have to be decrypted and encrypted back or recreated.

Note that starting 11.2.0.3.1 , the wallet can be dropped and recreated provided that you are ready to lose the encrypted data. Previous releases were affected by two known bugs and dropping and recreating the wallet resulted in errors. See issues #1 and #2 in Note 1301365.1