11.2.0.4 Linux
dropped a wallet using owm and while trying to re-setup the wallet getting the following error
---
SQL> alter system set encryption key authenticated by "******";
alter system set encryption key authenticated by "*****"
*
ERROR at line 1:
ORA-28362: master key not found
SQL> select * from v$encryption_wallet;
WRL_TYPE
--------------------
WRL_PARAMETER
--------------------------------------------------------------------------------
STATUS
------------------
file
/u01/app/oracle/admin/mdmqa2/wallet
CLOSED
SQL> !ls -l /u01/app/oracle/admin/mdmqa2/wallet
total 12
-rw------- 1 oracle oinstall 2923 Jan 26 10:17 cwallet.sso.old
-rw-r--r-- 1 oracle oinstall 2581 Jan 26 11:25 ewallet.p12
-rw-r--r-- 1 oracle oinstall 2845 Jan 13 01:52 ewallet.p12.old
SQL> alter system set encryption wallet open authenticated by "*****";
System altered.
SQL> select * from v$encryption_wallet;
WRL_TYPE
--------------------
WRL_PARAMETER
--------------------------------------------------------------------------------
STATUS
------------------
file
/u01/app/oracle/admin/mdmqa2/wallet
OPEN
SQL> alter system set encryption key authenticated by "****"
2 ;
alter system set encryption key authenticated by "*****"
*
ERROR at line 1:
ORA-28396: rekey of enc$ dictionary table failed
---
Seems the main error is ORA-28362: master key not found so I've been working that angle and this is what I've found so far
---
output as per https://mosemp.us.oracle.com/epmos/faces/ui/km/DocumentDisplay.jspx?id=1206180.1Doc ID 1206180.1
SQL> select * from v$encryption_wallet;
WRL_TYPE
--------------------
WRL_PARAMETER
--------------------------------------------------------------------------------
STATUS
------------------
file
/u01/app/oracle/admin/mdmqa2/wallet
OPEN
SQL> !ls -alrt /u01/app/oracle/admin/mdmqa2/wallet
total 20
drwxr-x--- 6 oracle oinstall 4096 Jan 13 01:52 ..
-rw-r--r-- 1 oracle oinstall 2845 Jan 13 01:52 ewallet.p12.old
-rw------- 1 oracle oinstall 2923 Jan 26 10:17 cwallet.sso.old
-rw-r--r-- 1 oracle oinstall 2845 Jan 26 14:35 ewallet.p12
drwxr-xr-x 2 oracle oinstall 4096 Jan 26 14:36 .
SQL> !mkstore -wrl /u01/app/oracle/admin/mdmqa2/wallet -list
Oracle Secret Store Tool : Version 11.2.0.4.0 - Production
Copyright (c) 2004, 2013, Oracle and/or its affiliates. All rights reserved.
Enter wallet password: ccs
Oracle Secret Store entries:
ORACLE.SECURITY.DB.ENCRYPTION.AU3FGwwD009Rv4YZihFKcDIAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
ORACLE.SECURITY.DB.ENCRYPTION.MASTERKEY
ORACLE.SECURITY.TS.ENCRYPTION.BSf3bB90ikAgSJxHC0YxduACAwAAAAAAAAAAAAAAAAAAAAAAAAAA
SQL> @wallet_details.sql
mkeyid of encrypted ts
no rows selected
mkeyid in controlfile
MKEYID MASTERKEYID_BASE64
-------------------------------- ------------------------------
53B4963BCF84B6F255AE81EB57CE2372 AVO0ljvPhLbyVa6B61fOI3I= <-------- this is what should be listed in the secret store entries(above) correct? but its not
mkeyid of all tablespaces
TS# NAME MASTERKEYID_BASE64
---------- ------------------------------ ------------------------------
0 SYSTEM AVO0ljvPhLbyVa6B61fOI3I=
1 SYSAUX AQAAAAAAAAAAAAAAAAAAAAA=
2 UNDOTBS1 AQAAAAAAAAAAAAAAAAAAAAA=
4 HUB_DATA AQAAAAAAAAAAAAAAAAAAAAA=
5 HUB_EME_DATA AQAAAAAAAAAAAAAAAAAAAAA=
6 HUB_EME_IDX AQAAAAAAAAAAAAAAAAAAAAA=
7 HUB_IDX AQAAAAAAAAAAAAAAAAAAAAA=
8 HUB_LOB AQAAAAAAAAAAAAAAAAAAAAA=
9 HUB_TBS16K AQAAAAAAAAAAAAAAAAAAAAA=
10 HUB_TBS4K AQAAAAAAAAAAAAAAAAAAAAA=
11 HUB_TBS8K AQAAAAAAAAAAAAAAAAAAAAA=
TS# NAME MASTERKEYID_BASE64
---------- ------------------------------ ------------------------------
12 INFO_DATA AQAAAAAAAAAAAAAAAAAAAAA=
13 LAND_DATA AQAAAAAAAAAAAAAAAAAAAAA=
14 LAND_IDX AQAAAAAAAAAAAAAAAAAAAAA=
15 META_DATA AQAAAAAAAAAAAAAAAAAAAAA=
16 STAGE_DATA AQAAAAAAAAAAAAAAAAAAAAA=
17 STAGE_IDX AQAAAAAAAAAAAAAAAAAAAAA=
20 ODM_DATA AQAAAAAAAAAAAAAAAAAAAAA=
18 TEST_DATA AQAAAAAAAAAAAAAAAAAAAAA=
19 USERS AQAAAAAAAAAAAAAAAAAAAAA=
3 TEMP AQAAAAAAAAAAAAAAAAAAAAA=
21 rows selected.
column encryption keys
MKEYID
----------------------------------------------------------------
AU3FGwwD009Rv4YZihFKcDIAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
ASWbT9ZOh084vwh+wi2NqWcAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
SQL>
---
as well as
---
SQL> select * from v$instance;
INSTANCE_NUMBER INSTANCE_NAME
--------------- ----------------
HOST_NAME
----------------------------------------------------------------
VERSION STARTUP_T STATUS PAR THREAD# ARCHIVE LOG_SWITCH_WAIT
----------------- --------- ------------ --- ---------- ------- ---------------
LOGINS SHU DATABASE_STATUS INSTANCE_ROLE ACTIVE_ST BLO
---------- --- ----------------- ------------------ --------- ---
1 mdmqa2
n01dol483.tent.trt.csaa.pri
11.2.0.4.0 26-JAN-16 OPEN NO 1 STARTED
ALLOWED NO ACTIVE PRIMARY_INSTANCE NORMAL NO
SQL> SELECT BITAND(FLAGS,8) FROM X$KCBDBK;
BITAND(FLAGS,8)
---------------
0 <----------------------------------------------------------------- per doc 2046729.1 this would mean a master key was NEVER set
SQL> show parameter compatible;
NAME TYPE
------------------------------------ --------------------------------
VALUE
------------------------------
compatible string
11.2.0.4.0
What I am confused about is how to rectify this situation overall? If the master key was never set can that be done retroactively? What would need to be done to avoid error ORA-28362