Applies to:

Advanced Networking Option - Version 11.1.0.7 and later
Information in this document applies to any platform.

Goal

What is the impact of the Database Media Recovery and Flashback Database operations on the Oracle TDE wallet?

Fix

These operations don't have any impact on the TDE wallet. More precisely:

1. if performing a Database Media Recovery and within the recovery period there is a master key change, this operation will not be reflected in the database wallet. The wallet will be left unchanged and only the master keys existing in the wallet at recovery start will be present at recovery end. If a master key change has been done in the database within the recovery interval and the existing wallet does not include the master key generated at that time, the recovery will fail.

2. if performing a Flashback Database and a master key change has been done in the covered interval of time, the operation will not be reflected in the wallet, ie all the master keys in the wallet before the flashback will be present at command completion.

3. the direct consequence of the above is that database media recovery cannot be used to recover wallet losses, when there are older wallet copies, if the master key has been changed within the interval between the wallet backup and the wallet loss. The recovery can be performed in this case until the moment when the master key has been changed. In this scenario if the latest wallet is not available after a rekey operation and the database needs to be recovered until the moment of the rekey operation, make sure to also restore a controlfile that was backed up before the latest rekey operation, since the latest controlfile has info about an unavailable master key which will lead to ORA-28374 errors during recovery.

To prove the above information, the following test has been done:

Impact of database recovery on the oracle wallet.

1. create a database
2. set the database in archivelog and flashback mode

mkdir $ORACLE_BASE/admin/$ORACLE_SID/{wallet,archive,backup}
mkdir $ORACLE_BASE/admin/$ORACLE_SID/backup/wallet
SQL> sqlplus "/ as sysdba"
SQL> alter system set log_archive_dest_1='location=/bugmnt16/em/celclnx14/SR3.3682242451/app/oracle/admin/TARDA1/archive';
SQL> alter system set log_archive_format='archive_TARDA1_%t_%s_%r.dbf' scope=spfile;
SQL> shutdown immediate
SQL> startup mount
SQL> alter database archivelog;
SQL> alter database flashback on;
SQL> alter database open;

3. enable TDE

SQL> alter system set encryption key identified by "welcome1";

4. check wallet contents

cd $ORACLE_BASE/admin/$ORACLE_SID/wallet
mkstore -wrl . -list
Enter wallet password:
Oracle Secret Store entries:
ORACLE.SECURITY.DB.ENCRYPTION.AYRYEpb+cE95v4hVQGhMvOsAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
ORACLE.SECURITY.DB.ENCRYPTION.MASTERKEY
ORACLE.SECURITY.TS.ENCRYPTION.BTJ9EEoIi7O8MokUyaU1SmMCAwAAAAAAAAAAAAAAAAAAAAAAAAAA

5. create an encrypted tablespace and an encrypted table and add some initial data:

SQL> create user test identified by test;
SQL> grant dba to test;
SQL> create table test.test(id number encrypt);
SQL> insert into test.test values(1);
SQL> commit;
SQL> create tablespace test datafile '/bugmnt16/em/celclnx14/SR3.3682242451/app/oracle/oradata/TARDA1/test01.dbf' size 10m
encryption using '3des168'
default storage(encrypt);
SQL> create table test.ts_test(id number) tablespace test;
SQL> insert into test.ts_test values(1);
SQL> commit;

5. take a database backup and a wallet backup

rman target /
RMAN> configure channel device type disk format '/bugmnt16/em/celclnx14/SR3.3682242451/app/oracle/admin/TARDA1/backup/db_backup%U.bkp';
RMAN> backup database plus archivelog;
SQL> alter system set wallet close identified by "welcome1";
cp $ORACLE_BASE/admin/$ORACLE_SID/wallet/* $ORACLE_BASE/admin/$ORACLE_SID/backup/wallet/ewallet.p12.1

6. recreate master key and add some more data

SQL> alter system set encryption key identified by "welcome1";
SQL> insert into test.test values(2);
SQL> commit;
SQL> insert into test.ts_test values(2);
SQL> commit;

7. check wallet contents

mkstore -wrl . -list
Enter wallet password:
Oracle Secret Store entries:
ORACLE.SECURITY.DB.ENCRYPTION.ASQ99v53Jk/nv3XHDxka99cAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
ORACLE.SECURITY.DB.ENCRYPTION.AYRYEpb+cE95v4hVQGhMvOsAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
ORACLE.SECURITY.DB.ENCRYPTION.MASTERKEY
ORACLE.SECURITY.TS.ENCRYPTION.BTJ9EEoIi7O8MokUyaU1SmMCAwAAAAAAAAAAAAAAAAAAAAAAAAAA

8. make a new wallet backup, as ewallet.p12.2

cp $ORACLE_BASE/admin/$ORACLE_SID/wallet/* $ORACLE_BASE/admin/$ORACLE_SID/backup/wallet/ewallet.p12.2

9. remove the current database and the wallet.

SQL> shutdown immediate
rm $ORACLE_BASE/oradata/$ORACLE_SID/*dbf
rm $ORACLE_BASE/admin/$ORACLE_SID/wallet/ewallet.p12

9. restore the backup and the first wallet and recover to present time.

cp $ORACLE_BASE/admin/$ORACLE_SID/backup/wallet/ewallet.p12.1 $ORACLE_BASE/admin/$ORACLE_SID/wallet/ewallet.p12
RMAN> restore database;
SQL> alter system set wallet open identified by "welcome1";
RMAN> recover database;
Fails with:
Starting recover at 03-JUN-11
using channel ORA_DISK_1

starting media recovery
media recovery failed
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 06/03/2011 07:14:31
ORA-00283: recovery session canceled due to errors
RMAN-11003: failure during parse/execution of SQL statement: alter database recover if needed
start
ORA-00283: recovery session canceled due to errors
ORA-01110: data file 1: '/bugmnt16/em/celclnx14/SR3.3682242451/app/oracle/oradata/TARDA1/system01.dbf'
ORA-28374: typed master key not found in wallet

As seen above, the database media recovery didn't affect the wallet.

10. restore the second wallet, including the latest master key:

SQL> alter system set wallet close identified by "welcome1";
cp $ORACLE_BASE/admin/$ORACLE_SID/backup/wallet/ewallet.p12.2 $ORACLE_BASE/admin/$ORACLE_SID/wallet/ewallet.p12
SQL> alter system set wallet open identified by "welcome1";
RMAN> recover database;

Starting recover at 03-JUN-11
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=192 device type=DISK

starting media recovery
media recovery complete, elapsed time: 00:00:01

Finished recover at 03-JUN-11

The solution, as seen above, is to use the latest wallet version.

11. check the table contents:

SQL> alter database open;
SQL> select * from test.test;
ID
----------
1
2
SQL> select * from test.ts_test;
ID
----------
1
2