Oracle 12.2 and Transparent Data Encryption

TDE Setup

Add these lines to sqlnet.ora

ENCRYPTION_WALLET_LOCATION=
(SOURCE=(METHOD=FILE)(METHOD_DATA=
(DIRECTORY=E:\encrypted_wallet\)))

Bounce Database

shutdown immediate;
startup;

Setup auto-login Wallet

ADMINISTER KEY MANAGEMENT CREATE KEYSTORE 'E:\encrypted_wallet\' IDENTIFIED BY "wve6Wq54IUhg39XY";
ADMINISTER KEY MANAGEMENT CREATE AUTO_LOGIN KEYSTORE FROM KEYSTORE 'E:\encrypted_wallet\' IDENTIFIED BY "wve6Wq54IUhg39XY";

Checkout Setting

select * FROM V_$ENCRYPTION_WALLET;
select * FROM V_$ENCRYPTION_KEYS;
SELECT * FROM v$rman_encryption_algorithms ORDER BY algorithm_name;

Create the master key in all containers

ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY "wve6Wq54IUhg39XY" CONTAINER=ALL;
ADMINISTER KEY MANAGEMENT SET KEY IDENTIFIED BY "wve6Wq54IUhg39XY" WITH BACKUP CONTAINER=ALL;

Migrate existing tablespaces to encrypted tablespace

Ensure compatibility is 12.2.0

alter system set compatible = '12.2.0' scope=spfile;
show parameter compatible;

Do this for each tablespace. After all tablespaces are migrated to the encrypted one, we need to manual delete the unencrypted datafiles. Oracle supposed to remove them, but they are not doing a good job.

SELECT
    'ALTER TABLESPACE '
    || tablespace_name
    || ' ENCRYPTION ONLINE USING ''AES256'' ENCRYPT;' v_sql
FROM
    dba_tablespaces
WHERE
    encrypted = 'NO'
    AND contents IN (
        'PERMANENT'
    )
    AND tablespace_name NOT IN (
        'SYSTEM',
        'SYSAUX'
    )
ORDER BY
    1;

RMAN Encrypted Backup

RMAN Configuration

CONFIGURE DEVICE TYPE DISK PARALLELISM 2 BACKUP TYPE TO COMPRESSED BACKUPSET;
CONFIGURE ENCRYPTION FOR DATABASE ON;
CONFIGURE ENCRYPTION ALGORITHM 'AES256';

RCV file

show all;
set encryption on;
crosscheck backupset;
crosscheck archivelog all;
delete noprompt expired backupset;
delete noprompt expired archivelog all;
delete noprompt obsolete;
run {  
allocate channel d1 type disk;
allocate channel d2 type disk;
allocate channel d3 type disk;
allocate channel d4 type disk;
BACKUP INCREMENTAL LEVEL 0 DATABASE PLUS ARCHIVELOG;
release channel d1;
release channel d2;
release channel d3;
release channel d4;
}
delete noprompt expired backupset;
delete noprompt expired archivelog all;
delete noprompt obsolete;
list backupset;
exit

Datapump Encrypted Backup

Add these options to expdp

ENCRYPTION=ALL
ENCRYPTION_MODE=TRANSPARENT 
ENCRYPTION_ALGORITHM=AES256

Oracle RMAN Cold Backup

rman target /

CONFIGURE BACKUP OPTIMIZATION ON;
CONFIGURE CONTROLFILE AUTOBACKUP ON;
configure controlfile autobackup format for device type disk to 'F:\ora_backup\%F';
configure channel device type disk format 'F:\ora_backup\%U' maxpiecesize 8 G;
run 
{
  shutdown immediate
  startup mount
  backup database;
  alter database open;
}

Restore Oracle database from RMAN backup

In SQL*Plus, sqlplus / as sysdba

ALTER SYSTEM SET DB_RECOVERY_FILE_DEST_SIZE = 200G SID='*';
ALTER SYSTEM SET DB_RECOVERY_FILE_DEST = '/disk1/flash_recovery_area' SID='*';

In RMAN, rman target /
DBID is the number from the source database

rman target /
SET DBID 2659406081;
startup nomount;
RESTORE SPFILE TO PFILE '/u01/app/oracle/initorcl.ora' FROM AUTOBACKUP;
SHUTDOWN ABORT;

STARTUP FORCE NOMOUNT PFILE='/u01/app/oracle/initorcl.ora';
RESTORE CONTROLFILE FROM AUTOBACKUP;
ALTER DATABASE MOUNT;
catalog recovery area;

In SQL*Plus, sqlplus / as sysdba

create spfile from pfile='/u01/app/oracle/initorcl.ora';
alter database rename file 'C:\oradb\oradata\orcl\redo01.log' to 'C:\oradb\oradata\orcl\xredo01.log';
alter database rename file 'C:\oradb\oradata\orcl\redo02.log' to 'C:\oradb\oradata\orcl\xredo02.log';
alter database rename file 'C:\oradb\oradata\orcl\redo03.log' to 'C:\oradb\oradata\orcl\xredo03.log';

Back to RMAN, rman target /
Perform a SET UNTIL to limit recovery to the end of the archived redo logs. Note that recovery stops with an error if no SET UNTIL is specified.

run {
set until time "to_date('2017-03-30 13:00:00', 'yyyy-mm-dd hh24:mi:ss')";
restore database;
recover database;
}
ALTER DATABASE OPEN RESETLOGS;

Clone Production database to Demo

1. Stop DEMO database

set ORACLE_SID=demo
set ORACLE_HOME=C:\app\oracle\product\11.2.0\dbhome_1
C:\app\oracle\product\11.2.0\dbhome_1\BIN\sqlplus / as sysdba
shutdown abort;

2. Purge DEMO database

del /F /Q /S C:\app\oracle\fast_recovery_area\demo\
del /F /Q /S C:\app\oracle\admin\demo\
del /F /Q /S F:\oradata\demo\
del /F /Q /S E:\fast_recovery_area\demo\
del /F /Q /S F:\fast_recovery_area\demo\

mkdir C:\app\oracle\fast_recovery_area\demo
mkdir C:\app\oracle\admin
mkdir C:\app\oracle\admin\demo
mkdir C:\app\oracle\admin\demo\adump
mkdir C:\app\oracle\admin\demo\bdump
mkdir C:\app\oracle\admin\demo\dpdump
mkdir C:\app\oracle\admin\demo\pfile
mkdir F:\oradata\demo
mkdir F:\oradata\demo\datafile
mkdir E:\fast_recovery_area\demo
mkdir E:\fast_recovery_area\demo\archivelog
mkdir F:\fast_recovery_area\demo
mkdir F:\fast_recovery_area\demo\archivelog

3. Create Password file - SYS password needs to be the same on both databases

cd /D C:\app\oracle\product\11.2.0\dbhome_1\database
ORAPWD FILE=PWDdemo.ora ENTRIES=10

4. Listener.ora
Add a static entry for demo

5. Fix E:\periodic\oracle_rman_clone.rcv
Run this against Production database to get a list of latest datafiles

SELECT 'SET NEWNAME FOR DATAFILE ' || file# || ' TO ''' || REPLACE (name, 'E:\ORADATA\PROD', 'F:\ORADATA\DEMO') || ''';' sqlline
  FROM v$datafile
 ORDER BY file#;

Refresh the datafile list in oracle_rman_clone.rcv, and we need to fix the archivelog sequence # base on Production RMAN output

#
# SVN Revision Information:
# $Revision: 2021 $
# $Author: pchiu $
# $Date: 2016-12-10 00:45:01 -0500 (Sat, 10 Dec 2016) $
#
#
#  SELECT 'SET NEWNAME FOR DATAFILE ' || file# || ' TO ''' || REPLACE (name, 'E:\ORADATA\PROD', 'F:\ORADATA\DEMO') || ''';' sqlline
#    FROM v$datafile
#ORDER BY file#;
#
run {  
SET NEWNAME FOR DATAFILE 1 TO 'F:\ORADATA\DEMO\SYSTEM01.DBF';
SET NEWNAME FOR DATAFILE 2 TO 'F:\ORADATA\DEMO\SYSAUX01.DBF';
SET NEWNAME FOR DATAFILE 3 TO 'F:\ORADATA\DEMO\UNDOTBS01.DBF';
SET NEWNAME FOR DATAFILE 4 TO 'F:\ORADATA\DEMO\USERS01.DBF';
SET NEWNAME FOR DATAFILE 5 TO 'F:\ORADATA\DEMO\EXAMPLE01.DBF';
SET NEWNAME FOR DATAFILE 6 TO 'F:\ORADATA\DEMO\DATAFILE\O1_MF_ACC_LOB_BHWQS94L_.DBF';
SET NEWNAME FOR DATAFILE 7 TO 'F:\ORADATA\DEMO\DATAFILE\O1_MF_HCS_STAG_BHWQVY73_.DBF';
SET NEWNAME FOR DATAFILE 8 TO 'F:\ORADATA\DEMO\DATAFILE\O1_MF_HCS_STAG_BHWQX19T_.DBF';
SET NEWNAME FOR DATAFILE 9 TO 'F:\ORADATA\DEMO\DATAFILE\O1_MF_ACC_DATA_BHWQXTD2_.DBF';
SET NEWNAME FOR DATAFILE 10 TO 'F:\ORADATA\DEMO\DATAFILE\O1_MF_APEX_DAT_BHWR0HHJ_.DBF';
SET NEWNAME FOR DATAFILE 11 TO 'F:\ORADATA\DEMO\DATAFILE\O1_MF_ACC_INDE_BHWR18JT_.DBF';
SET NEWNAME FOR DATAFILE 12 TO 'F:\ORADATA\DEMO\DATAFILE\O1_MF_BT_DATA_BHWR2CLM_.DBF';
SET NEWNAME FOR DATAFILE 13 TO 'F:\ORADATA\DEMO\DATAFILE\O1_MF_ACC_HIST_BHWR2GNF_.DBF';
SET NEWNAME FOR DATAFILE 14 TO 'F:\ORADATA\DEMO\DATAFILE\O1_MF_ACC_LOB_BHWR37PG_.DBF';
SET NEWNAME FOR DATAFILE 15 TO 'F:\ORADATA\DEMO\DATAFILE\O1_MF_LECXE_DA_BHWR5WSR_.DBF';
SET NEWNAME FOR DATAFILE 16 TO 'F:\ORADATA\DEMO\DATAFILE\O1_MF_LECXE_LO_BHWR6CVX_.DBF';
SET NEWNAME FOR DATAFILE 17 TO 'F:\ORADATA\DEMO\DATAFILE\O1_MF_ACC_LOB_BHWR7GY4_.DBF';
SET NEWNAME FOR DATAFILE 18 TO 'F:\ORADATA\DEMO\DATAFILE\O1_MF_ACC_LOB_BHWR9J1Q_.DBF';
SET NEWNAME FOR DATAFILE 19 TO 'F:\ORADATA\DEMO\DATAFILE\O1_MF_ACC_LOB_BQOXCZNQ_.DBF';
SET NEWNAME FOR DATAFILE 20 TO 'F:\ORADATA\DEMO\DATAFILE\O1_MF_ACC_LOB_C2OH4RCN_.DBF';
SET NEWNAME FOR DATAFILE 21 TO 'F:\ORADATA\DEMO\DATAFILE\O1_MF_ACC_LOB_CB028886_.DBF';
SET NEWNAME FOR DATAFILE 22 TO 'F:\ORADATA\DEMO\DATAFILE\O1_MF_ACC_LOB_CF0WL1ML_.DBF';
SET NEWNAME FOR DATAFILE 23 TO 'F:\ORADATA\DEMO\DATAFILE\O1_MF_ACC_LOB_CNCN5RBS_.DBF';
SET NEWNAME FOR DATAFILE 24 TO 'F:\ORADATA\DEMO\DATAFILE\O1_MF_ACC_LOB_CQ5ZWVP8_.DBF';
SET NEWNAME FOR DATAFILE 25 TO 'F:\ORADATA\DEMO\DATAFILE\O1_MF_ACC_INDE_CT7MCBWW_.DBF';
SET NEWNAME FOR DATAFILE 26 TO 'F:\ORADATA\DEMO\DATAFILE\O1_MF_ACC_LOB_CT7MN0X5_.DBF';
SET NEWNAME FOR DATAFILE 27 TO 'F:\ORADATA\DEMO\DATAFILE\O1_MF_ACC_LOB_CXRZ4VD6_.DBF';
SET NEWNAME FOR DATAFILE 28 TO 'F:\ORADATA\DEMO\DATAFILE\O1_MF_ACC_LOB_D14533D2_.DBF';
SET NEWNAME FOR DATAFILE 29 TO 'F:\ORADATA\DEMO\DATAFILE\O1_MF_ACC_LOB_D4KCSQ85_.DBF';
SET NEWNAME FOR TEMPFILE 1 TO 'F:\oradata\demo\temp01.dbf';
DUPLICATE TARGET DATABASE TO 'DEMO'
FROM ACTIVE DATABASE
SPFILE 
PARAMETER_VALUE_CONVERT 'PROD','DEMO'
SET DB_CREATE_FILE_DEST 'F:\ORADATA\DEMO'
SET dispatchers '(PROTOCOL=TCP) (SERVICE=demoXDB)'
SET control_files 'F:\ORADATA\DEMO\CONTROL01.CTL'
SET LOG_ARCHIVE_DEST_1 'location=E:\fast_recovery_area\demo\archivelog'
SET LOG_ARCHIVE_DEST_10 'location=USE_DB_RECOVERY_FILE_DEST'
NOFILENAMECHECK
OPEN RESTRICTED
SKIP READONLY;
}

6. Clone database
Startup database

set ORACLE_SID=demo
set ORACLE_HOME=C:\app\oracle\product\11.2.0\dbhome_1
C:\app\oracle\product\11.2.0\dbhome_1\BIN\sqlplus / as sysdba
STARTUP NOMOUNT PFILE='C:\app\oracle\product\11.2.0\dbhome_1\database\initdemo.ora';

C:\app\oracle\product\11.2.0\dbhome_1\database\initdemo.ora

DB_NAME=demo

Start cloning

C:\app\oracle\product\11.2.0\dbhome_1\bin\rman target sys/xxxxx@prod auxiliary sys/xxxxx@demo CMDFILE E:\periodic\oracle_rman_clone.rcv LOG F:\oracle_backup\dmp\oracle_rman_clone_test.log

7. Post cloning

C:\app\oracle\product\11.2.0\dbhome_1\BIN\sqlplus / as sysdba
ALTER SYSTEM SET DB_CREATE_FILE_DEST='F:\ORADATA'

C:\app\oracle\product\11.2.0\dbhome_1\BIN\sqlplus accdb/xxxxx@benecaid_demo
BEGIN
  migration_pkg.migration_demo;
END;
/
@E:\periodic\demo_patch01.sql
-- @E:\periodic\demo_patch02.sql

8. Refresh all Oracle Forms, Oracle Reports and Jasper Reports from Production