AKS_Cover

Enable Transparent Data Encryption on Oracle EBS

Abdul Khalique Siddique 2 weeks ago Uncategorized Leave a comment 10 Views

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.

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

 

 

    About Abdul Khalique Siddique

    In addition to my proficiency in Oracle Database, I have also specialized in Oracle E-Business Suite. I have hands-on experience in implementing, configuring, and maintaining EBS applications, enabling organizations to streamline their business processes and achieve operational efficiency. Also I have hands-on experience in Oracle Cloud Infrastructure (OCI). I have worked with OCI services such as compute, storage, networking, and database offerings, leveraging the power of the cloud to deliver scalable and cost-effective solutions. My knowledge of OCI architecture and deployment models allows me to design and implement robust and secure cloud environments for various business requirements. Furthermore, I have specialized in disaster recovery solutions for Oracle technologies. I have designed and implemented comprehensive disaster recovery strategies, including backup and recovery procedures, standby databases, and high availability configurations. My expertise in data replication, failover mechanisms, and business continuity planning ensures that organizations can quickly recover from disruptions and maintain uninterrupted operations.

    Check Also

    AKS_Cover

    Oracle EBS Advanced Row Compression

    Oracle E-Business Suite Release 12.2 with Oracle Database 12c Release 1 Advanced Row Compression (KB443050). …

    Leave a Reply

    Powered by OracleSolutions | Designed by
    © Copyright 2026, https://oraclesolutions.pk All Rights Reserved