Using Fast Online Conversion to Enable Transparent Data Encryption (TDE) for Oracle E-Business Suite with Oracle Database 19c (KA1115).

=======================================================================================================================================

Note : NO Downtime required to perform this activity.

Important Recommendations
————————-

1: Ensure the COMPATIBLE database parameter is set to the appropriate database version, 19.0.0.
2: Take a full backup of your database before starting the procedure.

SQL> sho parameter COMPATIBLE

NAME TYPE VALUE
———————————— ———– ——————————
compatible string 19.0.0

— TEMP tablespaces cannot be encrypted.

— External Large Objects (BFILEs) cannot be encrypted using TDE tablespace encryption. because these files reside outside the database.

########################################################################################################################################

Create the corresponding directory manually:
——————————————–

Example

mkdir -p $ORACLE_BASE/admin/<db_unique_name>/wallet

My location:

mkdir -p /db/prod/PROD/admin/orcl/wallet

#####################################################################################################################################

Add the following entry to the parameter file:
———————————————-

SQL> alter system set WALLET_ROOT=’/db/prod/PROD/admin/orcl/wallet’ scope=spfile sid=’*’;

Restart the database
———————

SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORACLE instance started.
Total System Global Area 3.4360E+10 bytes
Fixed Size 32868856 bytes
Variable Size 5704253440 bytes
Database Buffers 2.8588E+10 bytes
Redo Buffers 34238464 bytes
Database mounted.
Database opened.

SQL> ALTER SYSTEM SET TDE_CONFIGURATION=”KEYSTORE_CONFIGURATION=FILE” scope=both;

######################################################################################################################################

Check the wallet location and status:

$ sqlplus / as sysdba;
SQL> select * from V$encryption_wallet;

WRL_TYPE WRL_PARAMETER STATUS WALLET_TYPE WALLET_OR KEYSTORE FULLY_BAC CON_ID
———- ———————— ——– ——————– ——– ——— ——————————————————————
FILE /db/prod/PROD/admin/orcl/wallet/tde/ NOT_AVAILABLE UNKNOWN SINGLE NONE UNDEFINED 1
FILE NOT_AVAILABLE UNKNOWN SINGLE UNITED UNDEFINED 2
FILE NOT_AVAILABLE UNKNOWN SINGLE UNITED UNDEFINED 3

 

SQL> show parameter wallet_root

NAME TYPE VALUE
———————————— ———– ——————————
wallet_root string /db/prod/PROD/admin/orcl/wallet

SQL>

######################################################################################################################################

First create tde directory in wallet path: (ON CONTAINER FIRST)
——————————————————————-

cd /db/prod/PROD/admin/orcl/wallet

mkdir tde

Set the master encryption key from SQL*Plus:
Source the container database environment:

$ sqlplus / as sysdba;

ADMINISTER KEY MANAGEMENT CREATE KEYSTORE ‘/db/prod/PROD/admin/orcl/wallet/tde/’ IDENTIFIED BY Welcome321#;

keystore altered.

cd /db/prod/PROD/admin/orcl/wallet/tde

[maxtest@maxtest tde]$ ls -alrt
total 4
drwxr-xr-x 3 maxtest dba 17 Apr 28 14:36 ..
-rw——- 1 maxtest dba 2553 Apr 28 14:36 ewallet.p12
drwxr-xr-x 2 maxtest dba 25 Apr 28 14:36 .

 

ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY Welcome321#;

keystore altered.

ADMINISTER KEY MANAGEMENT SET KEY IDENTIFIED BY Welcome321# WITH BACKUP;

keystore altered.

[maxtest@maxtest tde]$ ls -alrt
total 8
drwxr-xr-x 3 maxtest dba 17 Apr 28 14:36 ..
-rw——- 1 maxtest dba 2553 Apr 28 14:41 ewallet_2026042809415427.p12
drwxr-xr-x 2 maxtest dba 61 Apr 28 14:41 .
-rw——- 1 maxtest dba 3993 Apr 28 14:41 ewallet.p12

####################################################################################################################################

SQL> select * from V$encryption_wallet;

WRL_TYPE WRL_PARAMETER STATUS WALLET_TYPE WALLET_OR KEYSTORE FULLY_BAC CON_ID
———- ———————— ——– ——————– ——– ——— ——————————————————————
FILE /db/prod/PROD/admin/orcl/wallet/tde/ open Password SINGLE NONE No 1
FILE NOT_AVAILABLE UNKNOWN SINGLE UNITED UNDEFINED 2
FILE NOT_AVAILABLE UNKNOWN SINGLE UNITED UNDEFINED 3

####################################################################################################################################

NOW ON PDB DATABASE
———————-

Connect to PDB PROD:
alter session set container=”PROD”;
SQL> sho con_name

CON_NAME
——————————
PROD

SQL> ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY Welcome321#;

keystore altered.

SQL> ADMINISTER KEY MANAGEMENT SET KEY IDENTIFIED BY Welcome321# WITH BACKUP;

keystore altered.

#####################################################################################################################################

Create an Auto-Login or a Local Auto-Login Software Keystore: (SKIP THIS STEP BECAUSE I DONT WANT PASSWORD LESS DATABASE):

Source the container database environment
——————————————

ADMINISTER KEY MANAGEMENT CREATE LOCAL AUTO_LOGIN KEYSTORE FROM KEYSTORE ‘/db/prod/PROD/admin/orcl/wallet/tde/’ IDENTIFIED BY Welcome321#;

Note: The keystore location is /db/prod/PROD/admin/orcl/wallet/tde/.

#########################################################################################################################################

FROM CONTAINER
—————-

SQL> shutdown normal;
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> exit

SQL> sqlplus
/as sysdba

SQL> startup mount;
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORACLE instance started.

Total System Global Area 3.4360E+10 bytes
Fixed Size 32868856 bytes
Variable Size 5704253440 bytes
Database Buffers 2.8588E+10 bytes
Redo Buffers 34238464 bytes
Database mounted.

###########################################################################################################################################

OPEN THE WALLET ON CONTAINER
———————————————

SQL> ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY Welcome321#;

keystore altered.

SQL> select * from V$encryption_wallet;

WRL_TYPE WRL_PARAMETER STATUS WALLET_TYPE WALLET_OR KEYSTORE FULLY_BAC CON_ID
——————– —————————— —————————— ——————– ——— ——– ——— ———-
FILE /db/prod/PROD/admin/orcl/wa OPEN PASSWORD SINGLE NONE NO 1
llet/tde/

FILE CLOSED UNKNOWN SINGLE UNITED UNDEFINED 2
FILE

SQL> alter database open

database altered

#############################################################################################################################################
NOW OPEN WALLET ON PDB
————————

SQL> ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY Welcome321#;

keystore altered.

SQL> select * from V$encryption_wallet;

WRL_TYPE WRL_PARAMETER STATUS WALLET_TYPE WALLET_OR KEYSTORE FULLY_BAC CON_ID
——————– —————————— —————————— ——————– ——— ——– ——— ———-
FILE /db/prod/PROD/admin/orcl/wa OPEN PASSWORD SINGLE NONE NO 1

################################################################################################################################################

Identify all the temporary and undo tablespaces in the CDB and PDB:
——————————————————————-

First check on container
————————–

SQL> select tablespace_name from dba_tablespaces where contents=’TEMPORARY’ and STATUS=’ONLINE’;

TABLESPACE_NAME
——————————
TEMP

SQL> select tablespace_name from dba_tablespaces where contents=’UNDO’ and STATUS=’ONLINE’;

TABLESPACE_NAME
——————————
UNDOTBS1

Now check on PDB
—————–

SQL> select tablespace_name from dba_tablespaces where contents=’TEMPORARY’ and STATUS=’ONLINE’;

TABLESPACE_NAME
——————————
TEMP1
TEMP2
TEMP6
MAXORA_TEMP

SQL> select tablespace_name from dba_tablespaces where contents=’UNDO’ and STATUS=’ONLINE’;

TABLESPACE_NAME
——————————
APPS_UNDOTS1

Note : Must check the weather the temp tablespace which is mention in below command , must be available in above online command.

select * from database_properties where property_name like ‘DEFAULT%TABLESPACE’;

##################################################################################################################

Perform the following steps in the CDB or PDB as per your requirement:
———————————————————————–

NOTE: I am not applying policy in container database and not applying policy on (SYSTEM, SYSAUX, UNDO, TEMP)..

Connect to PDB <PDB_NAME>

SQL> alter session set container=”PROD”;

Note: Ensure that there is sufficient space in the file system. If your datafile is 5Gb then 6Gb free space is required to perform the encryption of that datafile.

I HAVE 83 DATAFILE AFTER EXCLUDING CRTICAL DATFILES AND I HAVE MORE 83 GB SPACE
——————————————————————————–

/dev/mapper/ol-database 550G 311G 240G 57% /database

EXCLUDE SYSTEM, SYSAUX, UNDO, TEMP
———————————–

Deference between below both commands.

SQL> ALTER TABLESPACE users ENCRYPTION ONLINE USING ‘AES192’ ENCRYPT FILE_NAME_CONVERT = (‘users1.dbf’, ‘users1_enc.dbf’, ‘users2.dbf’, ‘users2_enc.dbf’);

useful for
————

Storage migration
Rename
Different mount point

ALTER TABLESPACE apps_ts_tx_data ENCRYPTION ONLINE USING ‘AES256’ ENCRYPT;

useful for
————
Oracle existing datafiles ko hi encrypt karta hai
Same file name / same location
Internally temporary copy banata hai, phir replace karta hai

#########################################################################################################################################

LIST OUT THE TABLESPACE EXCEPT (‘SYSTEM’,’SYSAUX’,’TEMP’,UNDO’)
—————————————————————

SELECT tablespace_name
FROM dba_tablespaces
WHERE tablespace_name NOT IN (‘SYSTEM’,’SYSAUX’)
AND contents NOT IN (‘TEMPORARY’)
AND contents NOT IN (‘UNDO’)
ORDER BY tablespace_name;

TABLESPACE_NAME
——————————
APPS_TS_ARCHIVE
APPS_TS_INTERFACE
APPS_TS_MEDIA
APPS_TS_NOLOGGING
APPS_TS_QUEUES
APPS_TS_SEED
APPS_TS_SUMMARY
APPS_TS_TOOLS
APPS_TS_TX_DATA
APPS_TS_TX_IDX
BILLSYS
CTXD
MAXORATEL_DATA
MAXORATN_DATA
MAXORA_DATA
ODM
OLAP
OWAPUB
PORTAL
XX_HFSTBLSPC

20 rows selected.

#########################################################################################################################################

check encryption status
————————-

SQL> select tablespace_name, encrypted from dba_tablespaces;

TABLESPACE_NAME ENC
—————————— —
SYSTEM NO
CTXD NO
OWAPUB NO
TEMP1 NO
ODM NO
PORTAL NO
APPS_UNDOTS1 NO
APPS_TS_TX_DATA NO
APPS_TS_TX_IDX NO
APPS_TS_SEED NO
APPS_TS_INTERFACE NO

TABLESPACE_NAME ENC
—————————— —
APPS_TS_SUMMARY NO
APPS_TS_NOLOGGING NO
APPS_TS_ARCHIVE NO
APPS_TS_QUEUES NO
APPS_TS_MEDIA NO
OLAP NO
SYSAUX NO
APPS_TS_TOOLS NO
TEMP2 NO
TEMP6 NO
XX_HFSTBLSPC NO

TABLESPACE_NAME ENC
—————————— —
BILLSYS NO
MAXORA_DATA NO
MAXORA_TEMP NO
MAXORATEL_DATA NO
MAXORATN_DATA NO

27 rows selected.

##################################################################################################################################

NOTE : PDB WALLET MUST BE OPEN IF YOU ARE ENCRYPTING DATAFILE IN PDB.

PERFORM ENCRYPTION
====
ALTER TABLESPACE MAXORATEL_DATA ENCRYPTION ONLINE USING ‘AES256’ ENCRYPT;
ALTER TABLESPACE APPS_TS_ARCHIVE ENCRYPTION ONLINE USING ‘AES256’ ENCRYPT;
ALTER TABLESPACE APPS_TS_INTERFACE ENCRYPTION ONLINE USING ‘AES256’ ENCRYPT;
ALTER TABLESPACE APPS_TS_MEDIA ENCRYPTION ONLINE USING ‘AES256’ ENCRYPT;
ALTER TABLESPACE APPS_TS_NOLOGGING ENCRYPTION ONLINE USING ‘AES256’ ENCRYPT;
ALTER TABLESPACE APPS_TS_QUEUES ENCRYPTION ONLINE USING ‘AES256’ ENCRYPT;
ALTER TABLESPACE APPS_TS_SEED ENCRYPTION ONLINE USING ‘AES256’ ENCRYPT;
ALTER TABLESPACE APPS_TS_SUMMARY ENCRYPTION ONLINE USING ‘AES256’ ENCRYPT;
ALTER TABLESPACE APPS_TS_TOOLS ENCRYPTION ONLINE USING ‘AES256’ ENCRYPT;
ALTER TABLESPACE APPS_TS_TX_DATA ENCRYPTION ONLINE USING ‘AES256’ ENCRYPT;
ALTER TABLESPACE APPS_TS_TX_IDX ENCRYPTION ONLINE USING ‘AES256’ ENCRYPT;
ALTER TABLESPACE BILLSYS ENCRYPTION ONLINE USING ‘AES256’ ENCRYPT;
ALTER TABLESPACE CTXD ENCRYPTION ONLINE USING ‘AES256’ ENCRYPT;
ALTER TABLESPACE MAXORATN_DATA ENCRYPTION ONLINE USING ‘AES256’ ENCRYPT;
ALTER TABLESPACE MAXORA_DATA ENCRYPTION ONLINE USING ‘AES256’ ENCRYPT;
ALTER TABLESPACE ODM ENCRYPTION ONLINE USING ‘AES256’ ENCRYPT;
ALTER TABLESPACE OLAP ENCRYPTION ONLINE USING ‘AES256’ ENCRYPT;
ALTER TABLESPACE OWAPUB ENCRYPTION ONLINE USING ‘AES256’ ENCRYPT;
ALTER TABLESPACE PORTAL ENCRYPTION ONLINE USING ‘AES256’ ENCRYPT;
ALTER TABLESPACE XX_HFSTBLSPC ENCRYPTION ONLINE USING ‘AES256’ ENCRYPT;

SQL> select tablespace_name, encrypted from dba_tablespaces;

TABLESPACE_NAME ENC
—————————— —
SYSTEM NO
CTXD YES
OWAPUB YES
TEMP1 NO
ODM YES
PORTAL YES
APPS_UNDOTS1 NO
APPS_TS_TX_DATA YES
APPS_TS_TX_IDX YES
APPS_TS_SEED YES
APPS_TS_INTERFACE YES
APPS_TS_SUMMARY YES
APPS_TS_NOLOGGING YES
APPS_TS_ARCHIVE YES
APPS_TS_QUEUES YES
APPS_TS_MEDIA YES
OLAP YES
SYSAUX NO
APPS_TS_TOOLS YES
TEMP2 NO
TEMP6 NO
XX_HFSTBLSPC YES
BILLSYS YES
MAXORA_DATA YES
MAXORA_TEMP NO
MAXORATEL_DATA YES
MAXORATN_DATA YES

27 rows selected.

#########################################################################################################