Encryption

One of the new features of Release 10.2 is about Transparant Data Encryption (TDE). Here is a documentation link ( written by Mr Arup Nanda ) 

http://www.oracle.com/technology/oramag/oracle/05-sep/o55security.html

In pre 10.2 releases you can use this documentation

http://www.oracle-base.com/articles/8i/DataEncryption.php

http://www.cybcon.com/~jkstill/util/encryption/data_obfuscation_and_encryption.html

If you are only interested in a encrypted RMAN backupset whereas the life data hasn' t to be encrypted you can use following documentation

http://download-uk.oracle.com/docs/cd/B19306_01/backup.102/b14191/rcmbackp006.htm#CEGEJABH

Let me show you how one can encrypt a RMAN backupset. I demonstrate two options

1. Transparant Encryption of Backups

2. Password Encryption of Backups

Transparant Encryption of Backups

This is the default mode and the preferred one when backups needs to be restored at the same location that they were backed up from. When using transparent encryption, the Oracle encryption wallet must be created and the wallet should be opened every time the database starts. ( Since high availability is one of the main requirements the shutdown and restart of even one of the 2 RAC instances will be very rare ) However when using the encrypted wallet encrypted backup operations can be done using the autologin password, as such no password should be have to supplied in the backupscripts.

By default, if using transparent encryption the Advance Encryption Standard with 192 bits : AES192 is used. 3DES (168 Bits)  can be used as well a smaller or bigger number of bits for the AES encryption AES256 (256 Bits), AES128 (128 Bits)

We list the RMAN persistent settings

RMAN> show all;

RMAN configuration parameters are:

CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default

CONFIGURE BACKUP OPTIMIZATION OFF; # default

CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default

CONFIGURE CONTROLFILE AUTOBACKUP ON; # default

CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default

CONFIGURE DEVICE TYPE DISK PARALLELISM 8 BACKUP TYPE TO BACKUPSET; # default

CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default

CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default

CONFIGURE MAXSETSIZE TO UNLIMITED; # default

CONFIGURE ENCRYPTION FOR DATABASE ON;

CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default

CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON STANDBY;

CONFIGURE SNAPSHOT CONTROLFILE NAME TO 'C:\ORACLE\10.2.0\DATABASE\SNCFUTF8.ORA'; # default

According to the documentation the Transparent Encryption method will be used, a wallet should have to be configured and should be open

SQL> ALTER SYSTEM SET WALLET OPEN identified by "brussels1";

System altered.

RMAN> backup tablespace sysaux;

Starting backup at 06-OCT-05

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: sid=159 devtype=DISK

channel ORA_DISK_1: starting full datafile backupset

channel ORA_DISK_1: specifying datafile(s) in backupset

input datafile fno=00003 name=C:\ORACLE\ORADATA\UTF8\BRUSSELS\DATAFILE\O1_MF_SYSAUX_1NBBXRH1_.DBF

channel ORA_DISK_1: starting piece 1 at 06-OCT-05

channel ORA_DISK_1: finished piece 1 at 06-OCT-05

piece handle=C:\ORACLE\FLASH_RECOVERY_AREA\BRUSSELS\BACKUPSET\2005_10_06\O1_MF_NNNDF_TAG20051006T165957_1NBGZG0L_.BKP tag=TAG20051006T165957 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:36

Finished backup at 06-OCT-05

Here we show it is not possible to invoke the restore operation without an open wallet.

SQL> alter system set wallet close;

System altered.

RMAN> restore tablespace sysaux;

Starting restore at 06-OCT-05

using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile backupset restore

channel ORA_DISK_1: specifying datafile(s) to restore from backup set

restoring datafile 00003 to C:\ORACLE\ORADATA\UTF8\BRUSSELS\DATAFILE\O1_MF_SYSAUX_1NBBXRH1_.DBF

channel ORA_DISK_1: reading from backup piece C:\ORACLE\FLASH_RECOVERY_AREA\BRUSSELS\BACKUPSET\2005_10_06\O1_MF_NNNDF_TAG20051006T165957_1NBGZG0L_.BKP

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-03002: failure of restore command at 10/06/2005 17:08:11

ORA-19870: error reading backup piece C:\ORACLE\FLASH_RECOVERY_AREA\BRUSSELS\BACKUPSET\2005_10_06\O1_MF_NNNDF_TAG20051006T165957_1NBGZG0L_.BKP

ORA-19913: unable to decrypt backup

ORA-28365: wallet is not open

Since the wallet can be protected by a password ( in the example brussels1 ) we show here it is not possible to open the wallet without supplying the right password.

SQL> ALTER SYSTEM SET WALLET OPEN identified by "Moerzeke";

ALTER SYSTEM SET WALLET OPEN identified by "Moerzeke"

ERROR at line 1:

ORA-28353: failed to open wallet

SQL> ALTER SYSTEM SET WALLET OPEN ;

ALTER SYSTEM SET WALLET OPEN

ERROR at line 1:

ORA-28356: invalid open wallet syntax

SQL> ALTER SYSTEM SET WALLET OPEN identified by " ";

ALTER SYSTEM SET WALLET OPEN identified by " "

ERROR at line 1:

ORA-28353: failed to open wallet

The wallet can only be opened if the right password has been supplied.

SQL> ALTER SYSTEM SET WALLET OPEN identified by "Brussels1";

System altered.

RMAN> restore tablespace sysaux;

Starting restore at 06-OCT-05

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: sid=159 devtype=DISK

channel ORA_DISK_1: starting datafile backupset restore

channel ORA_DISK_1: specifying datafile(s) to restore from backup set

restoring datafile 00003 to C:\ORACLE\ORADATA\UTF8\BRUSSELS\DATAFILE\O1_MF_SYSAUX_1NBBXRH1_.DBF

channel ORA_DISK_1: reading from backup piece C:\ORACLE\FLASH_RECOVERY_AREA\BRUSSELS\BACKUPSET\2005_10_06\O1_MF_NNNDF_TAG20051006T165957_1NBGZG0L_.BKP

channel ORA_DISK_1: restored backup piece 1

piece handle=C:\ORACLE\FLASH_RECOVERY_AREA\BRUSSELS\BACKUPSET\2005_10_06\O1_MF_NNNDF_TAG20051006T165957_1NBGZG0L_.BKP tag=TAG20051006T165957

channel ORA_DISK_1: restore complete, elapsed time: 00:00:35

Finished restore at 06-OCT-05

RMAN> recover tablespace sysaux;

Starting recover at 06-OCT-05

using channel ORA_DISK_1

starting media recovery

media recovery complete, elapsed time: 00:00:03

Finished recover at 06-OCT-05

RMAN> sql "alter tablespace sysaux online" ;

sql statement: alter tablespace sysaux online

Password Encryption of Backups

Password encryption requires a password when creating and restoring encrypted backups. Restoring a password-encrypted backup requires the same password that was used to create the backup. Password encryption is useful for backups that will be restored at remote locations. Password encryption cannot be persistently configured as such the password should have to be set in the backupscripts. There is no need to create a wallet, there is no need to have the wallet opened by the right password in order to restore the backupset. Encryption can be done in AES192, AES256 or AES128 ( the latter one is the default )

We list the RMAN persistent settings

RMAN> show all;

RMAN configuration parameters are:

CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default

CONFIGURE BACKUP OPTIMIZATION OFF; # default

CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default

CONFIGURE CONTROLFILE AUTOBACKUP ON; # default

CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default

CONFIGURE DEVICE TYPE DISK PARALLELISM 8 BACKUP TYPE TO BACKUPSET; # default

CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default

CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default

CONFIGURE MAXSETSIZE TO UNLIMITED; # default

CONFIGURE ENCRYPTION FOR DATABASE ON;

CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default

CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON STANDBY;

CONFIGURE SNAPSHOT CONTROLFILE NAME TO 'C:\ORACLE\10.2.0\DATABASE\SNCFUTF8.ORA'; # default

According to the documentation the Transparent Encryption method will be used, a wallet should have to be configured and should be open

RMAN> backup tablespace sysaux;

Starting backup at 06-OCT-05

allocated channel: ORA_DISK_1

channel ORA_DISK_1: sid=159 devtype=DISK

channel ORA_DISK_1: starting full datafile backupset

channel ORA_DISK_1: specifying datafile(s) in backupset

input datafile fno=00003 name=C:\ORACLE\ORADATA\UTF8\BRUSSELS\DATAFILE\O1_MF_SYSAUX_1NBB2CKJ_.DBF

channel ORA_DISK_1: starting piece 1 at 06-OCT-05

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-03009: failure of backup command on ORA_DISK_1 channel at 10/06/2005 15:39:55

ORA-19914: unable to encrypt backup

ORA-28365: wallet is not open

In order to bypass the wallet , in order to invoke the password encryption we have to set a password 

RMAN> set encryption on identified by my_password only;

executing command: SET encryption

RMAN> backup tablespace sysaux;

Starting backup at 06-OCT-05

using channel ORA_DISK_1

channel ORA_DISK_1: starting full datafile backupset

channel ORA_DISK_1: specifying datafile(s) in backupset

input datafile fno=00003 name=C:\ORACLE\ORADATA\UTF8\BRUSSELS\DATAFILE\O1_MF_SYSAUX_1NBB2CKJ_.DBF

channel ORA_DISK_1: starting piece 1 at 06-OCT-05

channel ORA_DISK_1: finished piece 1 at 06-OCT-05

piece handle=C:\ORACLE\FLASH_RECOVERY_AREA\BRUSSELS\BACKUPSET\2005_10_06\O1_MF_NNNDF_TAG20051006T154643_1NBBP3W4_.BKP tag=TAG20051006T154643 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:35

Finished backup at 06-OCT-05  

What happens when we want to restore an encrypted backupset

RMAN> restore tablespace sysaux;

Starting restore at 06-OCT-05

allocated channel: ORA_DISK_1

channel ORA_DISK_1: sid=148 devtype=DISK

channel ORA_DISK_1: starting datafile backupset restore

channel ORA_DISK_1: specifying datafile(s) to restore from backup set

restoring datafile 00003 to C:\ORACLE\ORADATA\UTF8\BRUSSELS\DATAFILE\O1_MF_SYSAUX_1NBB2CKJ_.DBF

channel ORA_DISK_1: reading from backup piece C:\ORACLE\FLASH_RECOVERY_AREA\BRUSSELS\BACKUPSET\2005_10_06\O1_MF_NNNDF_TAG20051006T154643_1NBBP3W4_.BKP

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-03002: failure of restore command at 10/06/2005 15:50:10

ORA-19870: error reading backup piece C:\ORACLE\FLASH_RECOVERY_AREA\BRUSSELS\BACKUPSET\2005_10_06\O1_MF_NNNDF_TAG20051006T154643_1NBBP3W4_.BKP

ORA-19913: unable to decrypt backup

ORA-28365: wallet is not open

Again the restore operation assumes we are using a  wallet to decrypt. Let ‘s invoke the password encryption.

RMAN> set decryption identified by my_password;

executing command: SET decryption

RMAN> restore tablespace sysaux;

Starting restore at 06-OCT-05

using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile backupset restore

channel ORA_DISK_1: specifying datafile(s) to restore from backup set

restoring datafile 00003 to C:\ORACLE\ORADATA\UTF8\BRUSSELS\DATAFILE\O1_MF_SYSAUX_1NBB2CKJ_.DBF

channel ORA_DISK_1: reading from backup piece C:\ORACLE\FLASH_RECOVERY_AREA\BRUSSELS\BACKUPSET\2005_10_06\O1_MF_NNNDF_TAG20051006T154643_1NBBP3W4_.BKP

channel ORA_DISK_1: restored backup piece 1

piece handle=C:\ORACLE\FLASH_RECOVERY_AREA\BRUSSELS\BACKUPSET\2005_10_06\O1_MF_NNNDF_TAG20051006T154643_1NBBP3W4_.BKP tag=TAG20051006T154643

channel ORA_DISK_1: restore complete, elapsed time: 00:00:35