Wednesday, November 20, 2019

Rekeying Master Key in a Data Guard Setup

Previous post shows steps for rekeying the master key. In a data guard setup rekeying the master key on primary will stop the redo apply on standby. For example running the rekey on primary as below
SQL> SELECT TS#,ENCRYPTIONALG,STATUS FROM V$ENCRYPTED_TABLESPACES;

       TS# ENCRYPT STATUS
---------- ------- ----------
         7 AES256  NORMAL

SQL> ADMINISTER KEY MANAGEMENT SET KEY using tag 'new key' FORCE KEYSTORE IDENTIFIED BY key#st0r3 WITH BACKUP USING 'new key backup';
will result in standby redo apply stopping. Following from the standby alert log
2019-08-13T06:33:58.977394-04:00
Apply redo for database master key re-key failed: new master key does not exist in the keystore
PR00 (PID:20854): MRP0: Background Media Recovery terminated with error 28374
2019-08-13T06:33:58.979796-04:00
Errors in file /opt/app/oracle/diag/rdbms/masterdr/masterdr/trace/masterdr_pr00_20854.trc:
ORA-28374: typed master key not found in wallet
PR00 (PID:20854): Managed Standby Recovery not using Real Time Apply
Recovery interrupted!
Recovered data files to a consistent state at change 8205568
stopping change tracking
2019-08-13T06:33:59.252495-04:00
Errors in file /opt/app/oracle/diag/rdbms/masterdr/masterdr/trace/masterdr_pr00_20854.trc:
ORA-28374: typed master key not found in wallet
2019-08-13T06:33:59.412228-04:00
Background Media Recovery process shutdown (masterdr)
The post shows the steps to copy the new master key to standby. The primary DB is called masterdb and standby DB is called masterdr.
1. Stop the redo apply on standby before the rekeying.
DGMGRL> edit database masterdr set state='apply-off';
Succeeded.
2. Rekey the master key on primary
SELECT TS#,ENCRYPTIONALG,STATUS FROM V$ENCRYPTED_TABLESPACES;

       TS# ENCRYPT STATUS
---------- ------- ----------
         7 AES256  NORMAL

SQL> ADMINISTER KEY MANAGEMENT SET KEY using tag 'rekey new key' FORCE KEYSTORE IDENTIFIED BY key#st0r3 WITH BACKUP USING 'new key backup';                                                                                                  Y key#st0r3 WITH BACKUP USING 'new key backup';

keystore altered.

SQL> SELECT KEY_ID,creation_time,activation_time,tag FROM V$ENCRYPTION_KEYS order by activation_time desc;

KEY_ID                                                  CREATION_TIME                            ACTIVATION_TIME                          TAG
------------------------------------------------------- ---------------------------------------- ---------------------------------------- ----------
AdgH4dK+N09jv+KzefQnzwYAAAAAAAAAAAAAAAAAAAAAAAAAAAAA    13-AUG-19 08.33.56.583283 AM -04:00      13-AUG-19 08.33.56.583291 AM -04:00      rekey new key


3. Export the new master key from the primary. While exporting it's possible to encounter ORA-28417 and ORA-28354 as described in the previous post. Export procedure
SQL> ! mv cwallet.sso.bak cwallet.sso
SQL> ADMINISTER KEY MANAGEMENT SET KEYSTORE close;

keystore altered.

SQL> ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY  key#st0r3;

keystore altered.

SQL> ADMINISTER KEY MANAGEMENT EXPORT KEYS WITH SECRET "exported key" TO '/home/oracle/exportedkey.p12' IDENTIFIED BY key#st0r3 WITH IDENTIFIER IN 'AdgH4dK+N09jv+KzefQnzwYAAAAAAAAAAAAAAAAAAAAAAAAAAAAA';

keystore altered.

SQL> ! mv cwallet.sso.bak cwallet.sso




4. Copy the exported key to the standby server. Open the standby key store using the password.
SQL> ! mv cwallet.sso cwallet.sso.bak

ADMINISTER KEY MANAGEMENT SET KEYSTORE close;
ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY  key#st0r3;
If the standby database is in mount mode then importing the key will give ORA-01109. However, checking wallet keys shows the new key is imported.
SQL>  ADMINISTER KEY MANAGEMENT IMPORT KEYS WITH SECRET "exported key" FROM '/home/oracle/exportedkey.p12' IDENTIFIED BY key#st0r3 with backup;
 ADMINISTER KEY MANAGEMENT IMPORT KEYS WITH SECRET "exported key" FROM '/home/oracle/exportedkey.p12' IDENTIFIED BY key#st0r3 with backup
*
ERROR at line 1:
ORA-01109: database not open

but the key goes in

SQL> SELECT KEY_ID,creation_time,activation_time,tag FROM V$ENCRYPTION_KEYS order by activation_time desc;

KEY_ID                                                  CREATION_TIME                            ACTIVATION_TIME                          TAG
------------------------------------------------------- ---------------------------------------- ---------------------------------------- ----------
AdgH4dK+N09jv+KzefQnzwYAAAAAAAAAAAAAAAAAAAAAAAAAAAAA    13-AUG-19 08.33.56.583283 AM -04:00      13-AUG-19 08.33.56.583291 AM -04:00      rekey new key


On the other hand if the standby database is open read only then import runs without giving any error
SQL>SQL> select open_mode from v$database;

OPEN_MODE
--------------------
READ ONLY


SQL> ADMINISTER KEY MANAGEMENT IMPORT KEYS WITH SECRET "exported key" FROM '/home/oracle/exportedkey.p12' IDENTIFIED BY key#st0r3 with backup;

keystore altered.


SQL> SELECT KEY_ID,creation_time,activation_time,tag FROM V$ENCRYPTION_KEYS order by activation_time desc;

KEY_ID                                                  CREATION_TIME                            ACTIVATION_TIME                          TAG
------------------------------------------------------- ---------------------------------------- ---------------------------------------- ----------
AdgH4dK+N09jv+KzefQnzwYAAAAAAAAAAAAAAAAAAAAAAAAAAAAA    13-AUG-19 08.33.56.583283 AM -04:00      13-AUG-19 08.33.56.583291 AM -04:00      rekey new key


5. Create a new local auto login key store on standby
SQL> ADMINISTER KEY MANAGEMENT CREATE local AUTO_LOGIN KEYSTORE FROM KEYSTORE IDENTIFIED BY key#st0r3;

keystore altered.
6. Enable redo apply.
DGMGRL> edit database masterdr set state='apply-on';
Succeeded.

Related Posts
19c Encryption
12c Encryption