In this Document
Goal
Solution
References
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?
SOLUTION
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=<directory>';
SQL> alter system set log_archive_format='archive_<SID>_%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 "<wallet password>";
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.<key 1>
ORACLE.SECURITY.DB.ENCRYPTION.MASTERKEY
ORACLE.SECURITY.TS.ENCRYPTION.<key 2>
5. create an encrypted tablespace and an encrypted table and add some initial data:
SQL> create user <user> identified by <password>;
SQL> grant dba to <user>;
SQL> create table <user>.<table>(id number encrypt);
SQL> insert into <user>.<table> values(1);
SQL> commit;
SQL> create tablespace <tablespace name> datafile '<directory>/<datafile name>' size 10m
encryption using '3des168'
default storage(encrypt);
SQL> create table <user>.<table 1>(id number) tablespace <tablespace name>;
SQL> insert into <user>.<table 1> values(1);
SQL> commit;
5. take a database backup and a wallet backup
rman target /
RMAN> configure channel device type disk format '<backup location>';
RMAN> backup database plus archivelog;
SQL> alter system set wallet close identified by "<wallet password>";
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 "<wallet password>";
SQL> insert into <user>.<table> values(2);
SQL> commit;
SQL> insert into <user>.<table 1> values(2);
SQL> commit;
7. check wallet contents
mkstore -wrl . -list
Enter wallet password:
Oracle Secret Store entries:
ORACLE.SECURITY.DB.ENCRYPTION.<key 3>
ORACLE.SECURITY.DB.ENCRYPTION.<key 1>
ORACLE.SECURITY.DB.ENCRYPTION.MASTERKEY
ORACLE.SECURITY.TS.ENCRYPTION.<key 2>
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 <datfile created in step 5>
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 "<wallet password>";
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: '<datafile name>'
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 "<wallet password>";
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 "<wallet password>";
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
The solution, as seen above, is to use the latest wallet version.
11. check the table contents:
SQL> alter database open;
SQL> select * from <user>.<table>;
ID
----------
1
2
SQL> select * from <user>.<table 1>;
ID
----------
1
2