关于 TDE WALLET在数据库恢复和闪回数据库上的影响

郑全

Impact of Database Recovery and Flashback Database on the TDE Wallet (Doc ID 1327519.1)

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