ORA-28374: typed master key not found in wallet (TDE)

basically we are doing TDE configuration for 12c env.
we are facing the below issues

---- while trying to set master key with container=all option
SQL> ADMINISTER KEY MANAGEMENT SET KEY USING TAG 'scope:all pdbs;description:Create Key for ALL PDBS' IDENTIFIED BY enc_keystore WITH BACKUP CONTAINER = ALL ;
ADMINISTER KEY MANAGEMENT SET KEY USING TAG 'scope:all pdbs;description:Create Key for ALL PDBS' IDENTIFIED BY enc_keystore WITH BACKUP CONTAINER = ALL
*
ERROR at line 1:
ORA-46665: master keys not activated for all PDBs during REKEY

--- next tried to manually set it at PDB level

SQL> alter session set container=orcl;

Session altered.

SQL> select * from V$ENCRYPTION_WALLET;

WRL_TYPE WRL_PARAMETER STATUS WALLET_TYPE WALLET_OR FULLY_BAC CON_ID
-------------------- ---------------------------------------- ------------------------------ -------------------- --------- --------- ----------
ASM +DATA/orclt1/WALLET/ CLOSED UNKNOWN SINGLE UNDEFINED 0

SQL> ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY enc_keystore;

keystore altered.

SQL> select * from V$ENCRYPTION_WALLET;

WRL_TYPE WRL_PARAMETER STATUS WALLET_TYPE WALLET_OR FULLY_BAC CON_ID
-------------------- ---------------------------------------- ------------------------------ -------------------- --------- --------- ----------
ASM +DATA/orclt1/WALLET/ OPEN_NO_MASTER_KEY PASSWORD SINGLE UNDEFINED 0

SQL> ADMINISTER KEY MANAGEMENT SET KEY IDENTIFIED BY enc_keystore WITH BACKUP;
ADMINISTER KEY MANAGEMENT SET KEY IDENTIFIED BY enc_keystore WITH BACKUP
*
ERROR at line 1:
ORA-28374: typed master key not found in wallet

==================================================================================================================================================================
now the solution follows (this problem might occure due to repeated deletion of ewallet.pk2/wallet files or retrying a earlier failed trial for TDE configuration)
==================================================================================================================================================================

STEP1 (check in both ROOT & PDBs)
==================================
 Drop and recreate all encrypted tablespaces as unencrypted (except SYSTEM), and this goes for the PDB as well. Check DBA_TABLESPACES;

Only once this is done, proceed with the following, as SYSDBA:

--- to check this use the below queries in both ROOT & PDBs

- decrypt all encrypted columns using a statement like: ALTER TABLE scott.tde MODIFY (c DECRYPT);
or drop all tables that still have an entry in dba_encrypted_columns:
select * from dba_encrypted_columns;
- remove any remaining rows not associated with OBJ$ objects from table sys.ENC$ :
select * from sys.enc$;
- move all tables out of your encrypted tablespaces (including indexes and LOB segments)
select t.owner,t.table_name, t.tablespace_name from dba_tables t,
dba_tablespaces s where t.tablespace_name=s.tablespace_name
and s.encrypted = 'YES';
- drop the encrypted tablespaces
select tablespace_name from dba_tablespaces where encrypted = 'YES';
- run this sql:
update TS$ set flags = flags - 16384 where online$ = 3 and bitand(flags,16384) = 16384 ;
commit;

1) Close the keystore to flush the master keys out of memory:

ADMINISTER KEY MANAGEMENT SET KEYSTORE CLOSE IDENTIFIED BY ... ;

2) Switch all of the REDO log files in the ROOT and PDB:

alter system switch logfile;
alter system switch logfile;
alter system switch logfile;

alter session set container=<PDB>;

alter system switch logfile;
alter system switch logfile;
alter system switch logfile;

3) Move the TDE wallet (ewallet.p12) to a backup location.


NOTE: _db_discard_lost_masterkey this hidden parameter is used below , do create a pfile before proceeding further below
sometimes database will fail to start after using this parameter (end of file error) , then from the pfile remove the entry (_db_discard_lost_masterkey) and restart DB.

STEp2 (in ROOT container)
=============================

SQL> conn / as sysdba
Connected.
SQL>
SQL>
SQL>
SQL> show con_id

CON_ID
------------------------------
1
SQL> show con_name

CON_NAME
------------------------------
CDB$ROOT
SQL> set lines 234 pages 234
SQL> col WRL_PARAMETER for a40
SQL> select WRL_TYPE, WRL_PARAMETER, STATUS from V$ENCRYPTION_WALLET;

WRL_TYPE WRL_PARAMETER STATUS
-------------------- ---------------------------------------- ------------------------------
ASM +DATA/orclt1/WALLET/ NOT_AVAILABLE

SQL> select CON_ID,KEY_ID,KEYSTORE_TYPE,CREATOR_DBNAME,CREATOR_PDBNAME from v$encryption_keys;

no rows selected

SQL> ADMINISTER KEY MANAGEMENT CREATE KEYSTORE '+DATA/orclt1/WALLET/' IDENTIFIED BY enc_keystore;

keystore altered.

SQL> ALTER SYSTEM SET "_db_discard_lost_masterkey"=TRUE SCOPE=MEMORY;

System altered.

SQL> ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY enc_keystore;

keystore altered.

SQL> ADMINISTER KEY MANAGEMENT SET KEY IDENTIFIED BY enc_keystore WITH BACKUP;

keystore altered.

SQL> select WRL_TYPE, WRL_PARAMETER, STATUS from V$ENCRYPTION_WALLET;

WRL_TYPE WRL_PARAMETER STATUS
-------------------- ---------------------------------------- ------------------------------
ASM +DATA/orclt1/WALLET/ OPEN


STEP3
=======

steps done in PDB
==========
SQL> alter session set container=orcl;

Session altered.

SQL> select WRL_TYPE, WRL_PARAMETER, STATUS from V$ENCRYPTION_WALLET;

WRL_TYPE WRL_PARAMETER STATUS
-------------------- ---------------------------------------- ------------------------------
ASM +DATA/orclt1/WALLET/ CLOSED

SQL> alter system set "_db_discard_lost_masterkey"=true;

System altered.

SQL> ADMINISTER KEY MANAGEMENT SET KEY IDENTIFIED BY enc_keystore WITH BACKUP;
ADMINISTER KEY MANAGEMENT SET KEY IDENTIFIED BY enc_keystore WITH BACKUP
*
ERROR at line 1:
ORA-46658: keystore not open in the container


SQL> ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY enc_keystore;

keystore altered.

SQL> ADMINISTER KEY MANAGEMENT SET KEY IDENTIFIED BY enc_keystore WITH BACKUP;

keystore altered.

SQL> select WRL_TYPE, WRL_PARAMETER, STATUS from V$ENCRYPTION_WALLET;

WRL_TYPE WRL_PARAMETER STATUS
-------------------- ---------------------------------------- ------------------------------
ASM +DATA/orclt1/WALLET/ OPEN