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
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
