One of the projects I worked on last week was improving our processes around encryption in Oracle. I spent a lot of time becoming intimate friends with the database wallet. Late in the week while attempting to create an encrypted tablespace on an 11.2.0.3 (PSU6) system, we mysteriously ran into ORA-28374. Oddly enough we were building two identical databases in parallel using a scripted approach but only one of the databases ran into this problem. And no matter how many times I rolled back and re-ran the wallet setup script, I kept getting this same error! There are a handful of informative Oracle Support notes related to this error and one of them had the solution to my problem. However it wasn’t immediately obvious why, so I thought it would be worthwhile to write a description based on my experience.

Root Cause

The root cause of this error is simple: there were traces of two different master encryption keys in the database. That means there may be something somewhere in the database which cannot be unencrypted – which is a problem!!

There’s more than one way to get into this situation. I’m not sure exactly how our database got there last week, but my guess is that after the first execution of the wallet script someone (maybe me) backed it out and re-ran it for some other reason – thus they erased the master wallet and encryption keys and had the script generate new ones. What’s interesting is that I had repeatedly deleted the master wallet and re-created keys while testing updates to the script last week and I didn’t hit the error at that time – I guess the stars weren’t aligned correctly until we were actually deploying systems on the newly updated scripts!

Troubleshooting

Support Note 1541818.1 is a great place to get started with troubleshooting this. According to that note, traces of the master encryption key can appear at three places in the database – which must all match the key in the wallet:

1. control file
2. data dictionary for encrypted columns
3. system tablespace header and encrypted tablespace headers
4. online redo log file headers

The note helpfully gives commands to check three of these locations. I’ll reproduce the commands here but if you’re using them then you need to first read note 1541818.1 itself.

• To check the 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);

• To check the data dictionary:

  select mkeyid from enc$;

• To check the tablespace headers:

  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#);

• To check the master wallet itself:

  mkstore -wrl <wallet directory> -viewEntry ORACLE.SECURITY.DB.ENCRYPTION.MASTERKEY

Our problem last week was that these three locations were all consistent. Eventually we realized that the problem was our redo logs.  No query was given to check the redo logs, but Support Note 988022.1 does give a simple troubleshooting suggestion. If, like me, you are encountering ORA-28374 and all four commands above are consistent then try this:

• select count(*) from v$log where thread#=(select thread# from v$instance);
• alter system switch logfile;    ← repeat this N times (N is output from the query above)

My database was on a cluster and running the switch logfile commands on only the local node where we were running our scripts solved the problem. However you might need to switch logfiles on all the nodes in your cluster.

Hope this is helpful! Even if you’re not actively troubleshooting I hope you learned a little something about now encryption works in Oracle databases.