Creating Encrypted Backups Overview - Practice 12-2: Restoring the Control File Overview

Practice 12-2: Restoring the Control File Overview

Practice 12-6: Creating Encrypted Backups Overview

In this practice you create an encrypted backup that is protected against data breach if the backup media is lost. In this example you will be using transparent encryption which depends on an encryption wallet. If the encryption wallet is lost, the backup is not recoverable. To mitigate the loss of a wallet or to allow the backup to be recovered on a different machine you can use password encryption instead of transparent encryption, or use both so that either the wallet or the password will allow the backup to be recovered.

Assumptions

Two terminal windows are open and you are logged in as the oracle OS user. $LABS is the current directory. Environment variables are set for the orcl instance.

Tasks

1. Prepare the database for encryption.

a. Set the environment variables for your orcl database instance. If it does not exist, create a directory named $ORACLE_BASE/admin/orcl/wallet for the Oracle wallet.

$ ls $ORACLE_BASE/admin/orcl/wallet

ls: cannot access /u01/app/oracle/admin/orcl/wallet: No such file or directory

$ mkdir -p $ORACLE_BASE/admin/orcl/wallet $

b. Edit the $ORACLE_HOME/network/admin/sqlnet.ora file to add the following lines: ENCRYPTION_WALLET_LOCATION= (SOURCE = (METHOD = FILE) (METHOD_DATA = (DIRECTORY = /u01/app/oracle/admin/orcl/wallet))) $ gedit /u01/app/oracle/product/12.1.0/dbhome_1/network/admin/sqlnet.ora

Save the sqlnet.ora file and exit from gedit.

2. Create a password-based keystore and back it up.

a. Connect to the orcl database instance as SYSDBA or as a user with the SYSKM privilege.

$ sqlplus / as sysdba SQL>

b. Create a password-based keystore.

SQL> ADMINISTER KEY MANAGEMENT CREATE KEYSTORE

'/u01/app/oracle/admin/orcl/wallet' IDENTIFIED BY secret; keystore altered.

SQL>

c. Confirm that the wallet exists.

SQL> ! ls -l /u01/app/oracle/admin/orcl/wallet total 4

-rw-r--r-- 1 oracle oinstall 2408 Dec 17 12:30 ewallet.p12 SQL>

d. Open the keystore.

SQL> ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY secret;

keystore altered. SQL>

e. Optionally, view information about the wallet in the data dictionary. SQL> SELECT WRL_PARAMETER, STATUS, WALLET_TYPE FROM V$ENCRYPTION_WALLET;

WRL_PARAMETER STATUS WALLET_TYPE --- --- --- /u01/app/oracle/admin/orcl/wallet OPEN_NO_MASTER_KEY PASSWORD SQL>

f. Generate the master encryption key.

SQL> ADMINISTER KEY MANAGEMENT SET KEY IDENTIFIED BY secret WITH BACKUP USING 'test';

keystore altered. SQL>

g. Verify that the keystore has been backed up before the master key generation. SQL> !ls -l /u01/app/oracle/admin/orcl/wallet

total 8

Notice that if you regenerate the master key, the file is growing. All previous master keys are kept for data which could have used the previous master keys.

h. Generate another key and view the keystore file. SQL> ADMINISTER KEY MANAGEMENT SET KEY IDENTIFIED BY secret WITH BACKUP;

keystore altered.

SQL> !ls -l /u01/app/oracle/admin/orcl/wallet total 16

-rw-r--r-- 1 oracle oinstall 2408 Dec 17 12:36 ewallet_2014121712362544_test.p12

-rw-r--r-- 1 oracle oinstall 3848 Dec 17 12:38 ewallet_2014121712382193.p12

-rw-r--r-- 1 oracle oinstall 6048 Dec 17 12:38 ewallet.p12 SQL>

i. Back up the keystore containing the current master key. Exit from SQL*Plus.

SQL> ADMINISTER KEY MANAGEMENT BACKUP KEYSTORE IDENTIFIED BY secret;

keystore altered.

SQL> !ls -l /u01/app/oracle/admin/orcl/wallet total 24

-rw-r--r-- 1 oracle oinstall 2408 Dec 17 12:36 ewallet_2014121712362544_test.p12

-rw-r--r-- 1 oracle oinstall 3848 Dec 17 12:38 ewallet_2014121712382193.p12

-rw-r--r-- 1 oracle oinstall 6048 Dec 17 12:39 ewallet_2014121712395820.p12

-rw-r--r-- 1 oracle oinstall 6048 Dec 17 12:39 ewallet.p12 SQL>

SQL> exit

Notice that both the current keystore and the backup files have the same size. 3. Using RMAN, create a transparent encrypted backup with a password. Use the

lab_12_06_02.rman script.

a. As always, it is a good idea to review a script before executing it. $ cat lab_12_06_02.rman

set encryption on for all tablespaces algorithm 'AES128'; run {

allocate channel enc_backup_disk1 type disk format '/u01/backup/orcl/%U';

backup as BACKUPSET tag 'TRANSPARENT' database;

backup as BACKUPSET tag 'TRANSPARENT' archivelog all not backed up;

release channel enc_backup_disk1; }

b. Execute the lab_12_06_02.rman script.

$ rman target "'/ as sysbackup'" @lab_12_06_02.rman

Recovery Manager: Release 12.1.0.2.0 - Production on Wed Dec 17 12:44:36 2014

connected to target database: ORCL (DBID=1393010434)

RMAN> set encryption on for all tablespaces algorithm 'AES128'; 2> run {

3> allocate channel enc_backup_disk1 type disk format '/u01/backup/orcl/%U';

4> backup as BACKUPSET tag 'TRANSPARENT' database;

5> backup as BACKUPSET tag 'TRANSPARENT' archivelog all not backed up;

6> release channel enc_backup_disk1; 7> }

executing command: SET encryption

using target database control file instead of recovery catalog

allocated channel: enc_backup_disk1

channel enc_backup_disk1: SID=41 device type=DISK

Starting backup at 2014-12-17:12:44:38

channel enc_backup_disk1: starting full datafile backup set channel enc_backup_disk1: specifying datafile(s) in backup set input datafile file number=00005

name=/u01/app/oracle/oradata/orcl/example01.dbf input datafile file number=00003

name=/u01/app/oracle/oradata/orcl/sysaux01.dbf input datafile file number=00001

input datafile file number=00006

name=/u01/app/oracle/oradata/orcl/users01.dbf

channel enc_backup_disk1: starting piece 1 at 2014-12- 17:12:44:38

channel enc_backup_disk1: finished piece 1 at 2014-12- 17:12:45:13

piece handle=/u01/backup/orcl/38pqd1lm_1_1 tag=TRANSPARENT comment=NONE

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

Finished backup at 2014-12-17:12:45:13

Starting backup at 2014-12-17:12:45:14 current log archived

channel enc_backup_disk1: starting archived log backup set channel enc_backup_disk1: specifying archived log(s) in backup set

input archived log thread=1 sequence=3 RECID=84 STAMP=866543377 input archived log thread=1 sequence=4 RECID=85 STAMP=866550986 input archived log thread=1 sequence=5 RECID=86 STAMP=866551515 channel enc_backup_disk1: starting piece 1 at 2014-12-

17:12:45:15

channel enc_backup_disk1: finished piece 1 at 2014-12- 17:12:45:16

piece handle=/u01/backup/orcl/39pqd1mr_1_1 tag=TRANSPARENT comment=NONE

channel enc_backup_disk1: backup set complete, elapsed time: 00:00:01

Finished backup at 2014-12-17:12:45:16

Starting Control File and SPFILE Autobackup at 2014-12- 17:12:45:16

piece

handle=/u01/app/oracle/fast_recovery_area/ORCL/autobackup/2014_1 2_17/o1_mf_s_866551516_b92ypx3r_.bkp comment=NONE

Finished Control File and SPFILE Autobackup at 2014-12- 17:12:45:19

released channel: enc_backup_disk1

Recovery Manager complete. $

4. Verify that the backup pieces were encrypted. Use the lab_12_06_03.sql script. $ cat lab_12_06_03.sql

-- DISCLAIMER:

-- This script is provided for educational purposes only. It is -- NOT supported by Oracle World Wide Technical Support.

-- The script has been tested and appears to work as intended. -- You should always run new scripts on a test instance

initially.

/* display the backup pieces and the encryption status of each */

/* Where the Tag is 'TRANSPARENT' */ SET PAGES 99

COL_BS_REC FORMAT 99999 COL BP_REC FORMAT 99999 COL ENCRYPTED FORMAT A7 COL TAG FORMAT A12

COL MEDIA_HANDLE FORMAT a40

SELECT s.recid as BS_REC, P.RECID as BP_REC, P.ENCRYPTED, P.TAG, p.HANDLE as MEDIA_HANDLE

from v$BACKUP_PIECE P, V$BACKUP_SET S WHERE P.SET_STAMP=S.SET_STAMP

and P.SET_COUNT = S.SET_COUNT and P.TAG='TRANSPARENT'

/ exit $

$ sqlplus / as sysdba @lab_12_06_03.sql

BS_REC BP_REC ENCRYPT TAG MEDIA_HANDLE

--- --- --- --- --- 73 73 YES TRANSPARENT /u01/backup/orcl/38pqd1lm_1_1 74 74 YES TRANSPARENT /u01/backup/orcl/39pqd1mr_1_1 $

Practice 12-7: Recovering by Using an Encrypted Backup

In document D78850GC20_ag (Page 186-192)