11g Transparent Data Encryption (TDE) with Log Miner and Logical Standby Database Oracle 11g New Features Tips by Donald BurlesonJune 29, 2015

Oracle 11g New Features Tips

With Oracle database 10g TDE could not be used by log miner.

Since data gets encrypted in the datafiles including the undo segments as well as the redo logs it is was not possible to use TDE for Logical Standby Database because log miner could not handle encrypted data in prior releases.

Starting with release 11g this functionality has been added to log miner.

This makes it possible now to use TDE for a Logical Standby Database.

The wallet needs to be opened and reachable for log miner to decrypt the column data. The wallet on the standby location is a copy of the wallet on the primary site. The passwords used to open the wallet can be different on primary and standby site.

The master key is only changeable on the primary site. An attempt to change it on the standby site would raise an error. After changing the master key on the primary site the wallet needs to be copied to the standby site.

It is possible to use different column keys and encryption algorithms on the standby and primary site. It could be changed with the following command:

SYSTEM @ orcl SQL> ALTER TABLE my_tab REKEY USING ?3DES168?;

There is no need to change the master key or the wallet password to re-key a table.

To change the encryption algorithm for a table you need to set the Data Guard to NONE.

Log miner uses the dynamic view v$logmnr_contents to populate the decrypted data into it. This view is only accessible if the database is mounted at least.

LUTZ AS SYSDBA @ orcl SQL> desc v$logmnr_contents

 Name                    Null?    Type
 ----------------------- -------- ----------------
 SCN                              NUMBER
 START_SCN                        NUMBER
 COMMIT_SCN                       NUMBER
 TIMESTAMP                        DATE
 START_TIMESTAMP                  DATE
 COMMIT_TIMESTAMP                 DATE
 XIDUSN                           NUMBER
 XIDSLT                           NUMBER
 XIDSQN                           NUMBER
 XID                              RAW(8)
 PXIDUSN                          NUMBER
 PXIDSLT                          NUMBER
 PXIDSQN                          NUMBER
 PXID                             RAW(8)
 TX_NAME                          VARCHAR2(256)
 OPERATION                        VARCHAR2(32)
 OPERATION_CODE                   NUMBER
 ROLLBACK                         NUMBER
 SEG_OWNER                        VARCHAR2(32)
 SEG_NAME                         VARCHAR2(256)
 TABLE_NAME                       VARCHAR2(32)
 SEG_TYPE                         NUMBER
 SEG_TYPE_NAME                    VARCHAR2(32)
 TABLE_SPACE                      VARCHAR2(32)
 ROW_ID                           VARCHAR2(18)
 USERNAME                         VARCHAR2(30)
 OS_USERNAME                      VARCHAR2(4000)
 MACHINE_NAME                     VARCHAR2(4000)
 AUDIT_SESSIONID                  NUMBER
 SESSION#                         NUMBER
 SERIAL#                          NUMBER
 SESSION_INFO                     VARCHAR2(4000)
 THREAD#                          NUMBER
 SEQUENCE#                        NUMBER
 RBASQN                           NUMBER
 RBABLK                           NUMBER
 RBABYTE                          NUMBER
 UBAFIL                           NUMBER
 UBABLK                           NUMBER
 UBAREC                           NUMBER
 UBASQN                           NUMBER
 ABS_FILE#                        NUMBER
 REL_FILE#                        NUMBER
 DATA_BLK#                        NUMBER
 DATA_OBJ#                        NUMBER
 DATA_OBJV#                       NUMBER
 DATA_OBJD#                       NUMBER
 SQL_REDO                         VARCHAR2(4000)
 SQL_UNDO                         VARCHAR2(4000)
 RS_ID                            VARCHAR2(32)
 SSN                              NUMBER
 CSF                              NUMBER
 INFO                             VARCHAR2(32)
 STATUS                           NUMBER
 REDO_VALUE                       NUMBER
 UNDO_VALUE                       NUMBER
 SAFE_RESUME_SCN                  NUMBER
 CSCN                             NUMBER
 OBJECT_ID                        RAW(16)