Wallet strangeness follow up

Hot on the heels of the last post we also had a very strange issue when we tried to do a datapump import into the TDE tablespaces i created in the previous post.

99% of the import worked fine - apart from these two statements

CREATE UNIQUE INDEX "xx"."APK_ICC_APPL_ARG" ON "xx"."ICC_APPL_ARG" ("APP_KEY", "RCOUNT", "ARG_NUMBER")TABLESPACE "xx" PARALLEL 1

ALTER TABLE "xx"."TRANSACTION_OBJECTS" ADD CONSTRAINT "CD_TRANSACTION_OBJECTS" FOREIGN KEY ("MSG_ID", "SEQNO") REFERENCES "xx"."TRANSACTION_DETAILS" ("MSG_ID", "SEQNO") ON DELETE CASCADE ENABLE

Which resulted in the all too familiar error (at least for me this week)

ORA-28374: typed master key not found in wallet

This initially to me made absolutely no sense - every other object, including other indexes and constraints had created fine with no errors - including stuff in the same tablespace - what the hell is going on.......

Any guesses?

Well - i tried running the statement manually and it still failed - is there some sort of intermittent problem with the wallet not working - surely not?

Then i had a more detailed look at the objects in question - both of them were on very big tables which gave me a clue - they would likely be spilling to temp when the commands ran - so i tried something.

alter system set pga_aggregate_target=4G;

and re-ran them manually - and guess what it worked - so temp is the problem bizarrely.

This got me worried for a minute - i didn't encrypt TEMP - did i need to? Well after a quick read of the docs you should not encrypt temp (or UNDO,SYSTEM etc for that matter) - in fact the encryption is handled for you here and you don;t have to do anything.

So what was wrong?

I found some code on metalink again to see what keys were in use for each tablespace

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

This revealed all tablespaces were using the same key apart from TEMP - which showed as different.....

How did that happen?

Well i suspect its because initially a wallet was incorrectly created on the standby and somehow the encryption key from this wallet is associated with the temp tablespace.

Very odd - however the fix was then simple

create default temporary tablespace temp2;

drop tablespace temp;

rename tablespace temp2 to temp;

job done - then all the keys line up and sorts which spill to disk now work without error!