TDE With Dataguard 11g R2 ( Standby )

I almost finish Data guard Standby 11g R2 which use ordinary file system and i will configure TDE (Transparent Data Encryption) to encrypt Tablespace , you should plan to secure your DR system as well your live data so by tablespace encryption you encrypt data on data files so any one can not capture data with famous linux-unix strings | grep anything and read and search about he or she interest by only access server file system So...

I prefer this oracle feature i can protect my database which is transparent to application and user which encrypted blocks and decrypt within SGA (Database Buffer Cache) completely transparent to any one issue DML and DDL statement against tables resident on encrypted tablespace

First of all we have already standby running on two nodes which we must configure taplespace TDE on two node

1- configure wallet location in sqlnet.ora as this

ENCRYPTION_WALLET_LOCATION =
(SOURCE=
(METHOD=file)
(METHOD_DATA=
(DIRECTORY=/u03/app/oracle/oradata/nf/wallet/)))

2- then restart our listener

$ lsnrctl stop; lsnrctl start

3- create oracle Wallet
Create a new wallet login oracle
$./owm

















Enter the password











Specify the location




















Set as an Auto-login wallet and save to open every database startup



















Create Master Key for TDE:

sqlplus / as sysdba

SQL> alter system set encryption key identified by "my-password";

System altered.

Verify wallet is open

SQL> select * from v$encryption_wallet;

If the wallet is not open:
SQL> alter system set encryption wallet open identified by "password";
System altered.

4- where i run MAXIMUM AVAILABILITY mode so standby recovered redo buffer which now encrypted so recovery will terminated while standby site cant decrypt this buffers and recovery this database

primary database alert log
###################

Redo shipping client performing standby login
*** 2009-12-10 11:09:31.588 4539 krsu.c
Logged on to standby successfully
Client logon and security negotiation successful!
Error 16401 creating standby archive log file at host 'dg2'
ORA-16401: archive log rejected by Remote File Server (RFS)
kcrrwkx: unknown error:16401
ORA-16055: FAL request rejected


standby database alert log
##################
Changing standby controlfile to MAXIMUM AVAILABILITY level
RFS[10]: Selected log 5 for thread 1 sequence 22 dbid 1684707097 branch 703339484
Thu Nov 19 01:46:58 2009
Archived Log entry 14 added for thread 1 sequence 21 ID 0x646a8019 dest 1:
Media Recovery Waiting for thread 1 sequence 22 (in transit)
Recovery of Online Redo Log: Thread 1 Group 5 Seq 22 Reading mem 0
Mem# 0: /u01/app/oradata/DG2/srl02.log
Thu Nov 19 02:20:08 2009
Apply redo for TSE master key re-key failed: wallet error 28365
MRP0: Background Media Recovery terminated with error 28365
Errors in file /u01/app/diag/rdbms/dg2/DG2/trace/DG2_pr00_3687.trc:
ORA-28365: wallet is not open
Managed Standby Recovery not using Real Time Apply
Recovery interrupted!
Recovered data files to a consistent state at change 985537
Errors in file /u01/app/diag/rdbms/dg2/DG2/trace/DG2_pr00_3687.trc:
ORA-28365: wallet is not open
Thu Nov 19 02:20:08 2009

5- so now we will copy follow files to standby site
sqlnet.ora
cwallet.sso
ewallet.p12
stop standby database and startup

6- recovery will continue you must sure wallet is auto open with instance

Thu Nov 19 04:34:41 2009
Media Recovery Log /u01/app/flash_recovery_area/DG2/archivelog/2009_11_19/o1_mf_1_23_5j94pnbv_.arc
Media Recovery Log /u01/app/flash_recovery_area/DG2/archivelog/2009_11_19/o1_mf_1_24_5j94s6db_.arc
Media Recovery Log /u01/app/flash_recovery_area/DG2/archivelog/2009_11_19/o1_mf_1_25_5j94s99c_.arc
Media Recovery Log /u01/app/flash_recovery_area/DG2/archivelog/2009_11_19/o1_mf_1_26_5j98ysxq_.arc
Media Recovery Log /u01/app/flash_recovery_area/DG2/archivelog/2009_11_19/o1_mf_1_27_5j98yvos_.arc
Media Recovery Log /u01/app/flash_recovery_area/DG2/archivelog/2009_11_19/o1_mf_1_28_5j99pwdg_.arc
Media Recovery Log /u01/app/flash_recovery_area/DG2/archivelog/2009_11_19/o1_mf_1_29_5j9b310m_.arc
Thu Nov 19 04:34:52 2009
Media Recovery Log /u01/app/flash_recovery_area/DG2/archivelog/2009_11_19/o1_mf_1_30_5j9bg99n_.arc
Media Recovery Log /u01/app/flash_recovery_area/DG2/archivelog/2009_11_19/o1_mf_1_31_5j9borcc_.arc
Media Recovery Log /u01/app/flash_recovery_area/DG2/archivelog/2009_11_19/o1_mf_1_32_5j9bor1s_.arc
Media Recovery Log /u01/app/flash_recovery_area/DG2/archivelog/2009_11_19/o1_mf_1_33_5j9boy6b_.arc
Media Recovery Log /u01/app/flash_recovery_area/DG2/archivelog/2009_11_19/o1_mf_1_34_5j9bp3tx_.arc
Media Recovery Log /u01/app/flash_recovery_area/DG2/archivelog/2009_11_19/o1_mf_1_35_5j9chrw3_.arc
Media Recovery Log /u01/app/flash_recovery_area/DG2/archivelog/2009_11_19/o1_mf_1_36_5j9chr95_.arc
Media Recovery Log /u01/app/flash_recovery_area/DG2/archivelog/2009_11_19/o1_mf_1_37_5j9chrhb_.arc
Media Recovery Log /u01/app/flash_recovery_area/DG2/archivelog/2009_11_19/o1_mf_1_38_5j9chsfk_.arc
Media Recovery Log /u01/app/flash_recovery_area/DG2/archivelog/2009_11_19/o1_mf_1_39_5j9chy7j_.arc
Media Recovery Log /u01/app/flash_recovery_area/DG2/archivelog/2009_11_19/o1_mf_1_40_5j9cn6wn_.arc


7- now we can create encrypted tablespace and populate our data on it to encrypted

CREATE SMALLFILE TABLESPACE "BACKEND" DATAFILE '/u01/app/oradata/DG1/backend.dbf' SIZE 2G AUTOEXTEND ON NEXT 1024K MAXSIZE UNLIMITED LOGGING EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO DEFAULT STORAGE(ENCRYPT) ENCRYPTION USING 'AES192'

References and Additional Reading

While there is no substitute for direct experience, reading the manual is not a bad idea, either. I have drawn upon the following Oracle 11gR2 documentation for the deeper technical details of this article:

B E10881-03 Oracle Database New Features Guide 11g Release 2 (11.2)

B14231-01 Oracle Database Administrator's Guide 11g Release 2 (11.2)

B E10595-06 Oracle Database Administrator's Guide 11g Release 2 (11.2)

B E10700-01 Oracle Data Guard Concepts and Administration 11g Release 2 (11.2)