Oracle Database 18c XE and Transparent Data Encryption

TDE Setup

The process is slightly different than 12c.

Create pfile

create pfile from spfile;
shutdown immediate;

Add this to /opt/oracle/product/18c/dbhomeXE/dbs/initXE.ora

*.wallet_root=/opt/encrypted_wallet

Create spfile from pfile with WALLET_ROOT

create spfile from pfile;
startup;

Configure the Software Keystore Type

ALTER SYSTEM SET TDE_CONFIGURATION="KEYSTORE_CONFIGURATION=FILE" scope=both;
shutdown immediate;
startup;

Setup auto-login Wallet

ADMINISTER KEY MANAGEMENT CREATE KEYSTORE '/opt/encrypted_wallet/tde/' IDENTIFIED BY "wve6Wq54IUhg39XY";
ADMINISTER KEY MANAGEMENT CREATE AUTO_LOGIN KEYSTORE FROM KEYSTORE '/opt/encrypted_wallet/tde/' IDENTIFIED BY "wve6Wq54IUhg39XY";

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;

Checkout Setting

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

Migrate existing tablespaces to encrypted tablespace

ALTER TABLESPACE users OFFLINE NORMAL;
ALTER TABLESPACE users ENCRYPTION OFFLINE ENCRYPT;
ALTER TABLESPACE users ONLINE;

Checkout tablespace encryption setting

select * from V$ENCRYPTED_TABLESPACES;

Setting Future Tablespaces to be Encrypted

ALTER SYSTEM SET ENCRYPT_NEW_TABLESPACES = ALWAYS;

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 Database 18c Express Edition (XE) RPM Installation On Amazon Oracle Linux 7 (OL7)

Create 3GB Linux swap

# dd if=/dev/zero of=/swapfile bs=1024 count=1048576
# mkswap /swapfile
# swapon /swapfile
# chmod 600 /swapfile
# swapon -s

Add Linux swap to /etc/fstab

/swapfile  none  swap  sw 0  0

Oracle database will be installed under /opt. So, make sure you have enough room under /opt

# yum -y localinstall oracle-database-xe-18c-1.0-1.x86_64.rpm
# /etc/init.d/oracle-xe-18c configure

Test Database under non-root account

$ . oraenv
$ sqlplus sys/xxxxxx@//localhost:1521/XE as sysdba
$ sqlplus sys/xxxxxx@//localhost:1521/XEPDB1 as sysdba

Fix iptables
Allow port 80, 443 and 1521

# iptables -I INPUT -p tcp --dport 1521 -j ACCEPT
# iptables -t eth0 -I PREROUTING -p tcp --dport 80 -j REDIRECT --to-ports 8080
# iptables -t eth0 -I OUTPUT -p tcp --dport 80 -j REDIRECT --to-ports 8080
# iptables -t eth0 -I PREROUTING -p tcp --dport 443 -j REDIRECT --to-ports 8443
# iptables -t eth0 -I OUTPUT -p tcp --dport 443 -j REDIRECT --to-ports 8443
# service iptables save

Configure Oracle to startup on boot

# systemctl daemon-reload
# systemctl enable oracle-xe-18c
# reboot

Oracle user environment setup .bash_profile

export ORAENV_ASK=NO
export ORACLE_SID=XE
. oraenv

sqlplus / as sysdba

Apply Oracle Database Patch

Take screenshot, shutdown all oracle services and change them to MANUAL startup
reboot
open window command, run as administrator

List all the patches applied to database

E:\oracle\db_home\product\12.2.0\dbhome_1\OPatch\opatch lspatches

Apply the patch

cd C:\Users\CyberArkDBA\Downloads\p28574555_122010_MSWIN-x86-64\28574555
E:\oracle\db_home\product\12.2.0\dbhome_1\OPatch\opatch apply

Startup Oracle
cd %ORACLE_HOME%/OPatch
datapatch -verbose

Base on the screenshot, put back the original startup setting
reboot

Recompile all objects

exec utl_recomp.recomp_parallel;

select * from dba_objects where status = 'INVALID' order by 1,2;

Check patch history

select * from dba_registry_sqlpatch;

select dbms_java.get_jdk_version() from dual;

select dbms_qopatch.get_opatch_lsinventory() from dual;

Remove patch (if there is conflict)

E:\oracle\db_home\product\12.2.0\dbhome_1\OPatch\opatch rollback -id 28135129
cd %ORACLE_HOME%/OPatch
datapatch -verbose -rollback 28135129

Oracle APEX Upgrade

Upgrade
I am using Oracle APEX Static Resources on Content Delivery Network. There is no need to copy the images directory to tomcat as /i/

sqlplus / as sysdba
ALTER SESSION SET CONTAINER = PDB01;
@apexins.sql APEX_DATA APEX_DATA TEMP https://static.oracle.com/cdn/apex/20.2.0.00.20/
@apex_rest_config.sql

Install French Language
goto this directory apex/builder/fr

set NLS_LANG=American_America.AL32UTF8
sqlplus / as sysdba
ALTER SESSION SET CONTAINER = PDB01;
ALTER SESSION SET CURRENT_SCHEMA = APEX_200200;
@load_fr.sql

Apply Patch
unzip p32006852_2020_GENERIC.zip, goto patch directory

sqlplus / as sysdba
ALTER SESSION SET CONTAINER = PDB01;
@catpatch.sql

Re-compile all objects

EXEC utl_recomp.recomp_parallel;
SELECT COUNT (*)
  FROM dba_objects
 WHERE status = 'INVALID';