The error as explained by Oracle mentions that the this happens when your key is stored in an external store - such as Hardware Security Module (HSM) or OKV
Of course, this blog is not related to HSM but the error received when handling TDE key stored in a software key store (Oracle wallet on a filesystem)
I got this error when migrating a non-CDB to PDB on an existing container.
The original TDE keys on the system looks like below [I have masked the keys for obvious reasons ;)]
set lines 250
col key_id for a55
col creation_time for a35
col activation_time for a35
col creator_pdbname for a15
col creator_dbname for a15
col ACTIVATING_PDBNAME for a15
col ACTIVATING_DBNAME for a15
select key_id, creator_dbname, ACTIVATING_DBNAME, creation_time, key_use, keystore_type
from v$encryption_keys order by creation_time;
KEY_ID CREATOR_DBNAME CREATION_TIME KEYSTORE_TYPE
----------------------------------------------------- --------------- --------------------------------- -----------------
XXXXXXXXXXXXXXXXXXXXXXXwAAAAAAAAAAAAAAAAAAAAAAAAAAAAA PONPREM1_VUL 26-SEP-16 12.21.18.601380 +01:00 SOFTWARE KEYSTORE
YYYYYYYYYYYYYYYYYYYYYYYuAAAAAAAAAAAAAAAAAAAAAAAAAAAAA PONPREM1_VUL 15-JUN-17 22.07.35.397077 +01:00 HSM
The key which is set as primary here was created in HSM and the wallet when migrated to SOFTWARE KEYSTORE has the key set as secondary.
I am skipping the actual plug-in steps here - where the PDB was plugged-in and TDE keys have already been imported once.
The first time this non-CDB was plugged in to the CDB. It could not set the TDE master key for the PDB. The wallet status remained in OPEN_NO_MASTER_KEY status, thus the error and thus this blog post for you.
ALTER SESSION SET CONTAINER=CDB$ROOT;
set linesize 180
column wrl_parameter format a40
column wrl_type heading 'Type' format a10
column status heading 'Status' format a20
column fully_backed_up heading 'Backed Up' format a15
column pdb_name heading 'PDB Name' format a15
select b.name pdb_name,wrl_type,wrl_parameter,status,wallet_type,keystore_mode,fully_backed_up from v$encryption_wallet a,v$containers b where a.con_id = b.con_id(+);
PDB Name Type WRL_PARAMETER Status
--------------- ---------- --------------------------- ----------
CDB$ROOT ASM +DATAC1/PCDB_ATN/tde/ OPEN
PDB$SEED ASM OPEN
PONPREM1 ASM OPEN_NO_MASTER_KEY
Any attempts to USE or SET key fails with ORA-28414: specified keys are in HSM
ADMINISTER KEY MANAGEMENT USE KEY '<key_id_for_software_keystore>' identified by "walletPass" WITH BACKUP USING 'USEKEY';
OR
ADMINISTER KEY MANAGEMENT SET KEY identified by "walletPass" WITH BACKUP USING 'SETKEY';
Resolution:
After troubleshooting, I was able to resolve the problem by following the below steps:
CDB creates services for a PDB, so if you have already attempted plugin operation - you will have to delete the services otherwise you cannot plugin the database with the same PDB name.
# Delete any PDB services from OCR, if added explicitly
srvctl remove service -d PCDB_ATN -s PONPREM1_P
# Delete existing services from PDB to prepare PDB for unplug/plug-in operation
ALTER SESSION SET CONTAINER=PONPREM1;
select 'exec dbms_service.delete_service(''' || name || ''');' src from dba_services;
exec dbms_service.delete_service('PONPREM1_P');
exec dbms_service.delete_service('PONPREM1_RO');
-- Internal services cannot be stopped/deleted, so no worries about these
exec dbms_service.stop_service('PONPREM1');
exec dbms_service.delete_service('PONPREM1');
# Export TDE keys from PDB
ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY walletPass;
ADMINISTER KEY MANAGEMENT EXPORT ENCRYPTION KEYS WITH SECRET "walletPass" TO '/tmp/PONPREM1_pdb_enckeys.exp' IDENTIFIED BY walletPass;
# Unplug PDB and drop PDB while keeping the datafiles
ALTER SESSION SET CONTAINER=CDB$ROOT;
alter pluggable database PONPREM1 close instances=all;
alter pluggable database PONPREM1 unplug into '/tmp/PONPREM1_pdb.xml';
drop pluggable database PONPREM1 keep datafiles;
# Plug the PDB back to CDB
create pluggable database PONPREM1 using '/tmp/PONPREM1_pdb.xml' NOCOPY TEMPFILE REUSE;
alter pluggable database PONPREM1 open read write;
# Perform import of TDE keys again
ALTER SESSION SET CONTAINER=PONPREM1;
ADMINISTER KEY MANAGEMENT SET KEYSTORE CLOSE;
ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY walletPass;
ADMINISTER KEY MANAGEMENT IMPORT ENCRYPTION KEYS WITH SECRET "SecRet" FROM '/tmp/PONPREM1_pdb_enckeys.exp' IDENTIFIED BY "walletPass" WITH BACKUP USING 'PRE_PONPREM1';
# Bounce the PDB and save state
ALTER PLUGGABLE DATABASE PONPREM1 CLOSE instances=all;
ALTER PLUGGABLE DATABASE PONPREM1 OPEN instances=all;
col name for a20
select name, inst_id, restricted, open_mode from gv$pdbs order by 1,2;
ALTER PLUGGABLE DATABASE PONPREM1 SAVE STATE instances=all;
# Add PDB services again to the CDB
srvctl add service -d PCDB_ATN -s PONPREM1_P -pdb PONPREM1 -r PCDB1,PCDB2 -tafpolicy BASIC -failovertype SELECT -l "PRIMARY"
The key is now set successfully automatically and wallet status is OPEN
ALTER SESSION SET CONTAINER=CDB$ROOT;
set linesize 180
column wrl_parameter format a40
column wrl_type heading 'Type' format a10
column status heading 'Status' format a20
column fully_backed_up heading 'Backed Up' format a15
column pdb_name heading 'PDB Name' format a15
select
b.name
pdb_name,wrl_type,wrl_parameter,status,wallet_type,keystore_mode,fully_backed_up
from v$encryption_wallet a,v$containers b where a.con_id =
b.con_id(+);
PDB Name Type WRL_PARAMETER Status
--------------- ---------- --------------------------- ----------
CDB$ROOT ASM +DATAC1/PCDB_ATN/tde/ OPEN
PDB$SEED ASM OPEN
PONPREM1 ASM OPEN
Enjoy.
