Some programs are part of Oracle ISV – Independent Software Vendors – program and include various types of Oracle licenses. This allows to install the application database in various Oracle configurations. At my customer, the software includes Advance Security Option Oracle license. This was the reason we decided, even before moving to the cloud, to encrypt the database.
Here is a summary on how to perform full online encryption (TDE) of a database. The process is quite simple, but there are known surprises you might want to avoid.
Check space requirements
Online encryption is done datafile by datafile. It is necessary to have enough space for copying the biggest datafile of the database. This can be a problem when using bigfile tablespaces.
SQL> select con_id,tablespace_name, max(bytes/1024/1024/1024) max_gb
from cdb_data_files
group by con_id,tablespace_name
order by 3 desc nulls last
fetch first 10 rows only;
While the encryption happens one datafile at the time, on the RHEL8 installation at my client, the process did not release the file descriptor at the end of each datafile encryption. This meant that space was still being used and filesystem was almost full. At Troubleshooting section it is described how to clean up this space during the encryption.
Configure TDE Wallet
Create Wallet and unified master key
First, we need to setup a TDE Wallet in the database. Few points to remind:
- This step requires a restart of database!
- We will define a new password to be used for keystore. Make sure to save it,
- Make sure the files created under the path of
WALLET_ROOTparameter are part of a filesystem backup. These files are required to open the database! - If there is Dataguard, the parameters below need also to be set on standby and the two generated wallet files copied.
- If you plan to clone this database, you need to export and import the keys on the future DB when cloning.
$ mkdir -p /u00/app/oracle/wallet_root/tde
SQL> alter system set tablespace_encryption=AUTO_ENABLE; -- available from 19.16
SQL> alter system set wallet_root='/u00/app/oracle/wallet_root' scope=spfile;
- plan restart and restart DB -
SQL> alter system set tde_configuration='keystore_configuration=FILE' scope=both;
SQL> ADMINISTER KEY MANAGEMENT CREATE KEYSTORE IDENTIFIED BY "xxxx"; /* Add to Password Safe */
SQL> ! ls -l /u00/app/oracle/wallet_root/tde
total 4
-rw-------. 1 oracle dba 2553 Apr 17 11:55 ewallet.p12
SQL> ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY "xxx" container=all;
SQL> ADMINISTER KEY MANAGEMENT SET KEY IDENTIFIED BY "xxx" with backup container=all;
SQL> col wrl_parameter for a40
SQL> select con_id,WRL_PARAMETER, WRL_TYPE,WALLET_TYPE, status from V$ENCRYPTION_WALLET;
CON_ID WRL_PARAMETER WRL_TYPE WALLET_TYPE STATUS
---------- ---------------------------------------- -------------------- -------------------- ------------------------------
1 /u00/app/oracle/wallet_root/tde/ FILE PASSWORD OPEN
2 FILE PASSWORD OPEN
3 FILE PASSWORD OPEN
Create autologin wallet
This ensures the DB will automatically open the wallet during startup. The WALLET_TYPE in the V$ENCRYPTION_WALLET will change from PASSWORD to AUTOLOGIN.
SQL> ADMINISTER KEY MANAGEMENT CREATE AUTO_LOGIN KEYSTORE FROM KEYSTORE '/u00/app/oracle/wallet_root/tde/' IDENTIFIED BY "xxx";
SQL> ADMINISTER KEY MANAGEMENT SET KEYSTORE CLOSE IDENTIFIED BY "xxx" container=all;
SQL> col wrl_parameter for a40
SQL> select con_id,WRL_PARAMETER, WRL_TYPE,WALLET_TYPE, status from V$ENCRYPTION_WALLET;
CON_ID WRL_PARAMETER WRL_TYPE WALLET_TYPE STATUS
---------- ---------------------------------------- -------------------- -------------------- ------------------------------
1 /u00/app/oracle/wallet_root/tde/ FILE AUTOLOGIN OPEN
2 FILE AUTOLOGIN OPEN
3 FILE AUTOLOGIN OPEN
Encrypt tablespaces
Check if OMF is configured and used
It is recommended to enable OMF if not yet done. To enable OMF, the db_create_file_dest needs to be set.
SQL> show parameter db_create_file_dest
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_create_file_dest string /u01/oradata
Get list of commands to run
On each of the PDBs, including CDB$ROOT, encrypt the tablespaces. To speed up, you can run different tablespace encryption in parallel sessions. Each session uses about one CPU and some IO.
SQL> select '/* PDB: '||con_id_to_con_name(con_id)||' */ ALTER TABLESPACE '||tablespace_name||' ENCRYPTION ONLINE ENCRYPT; /* '||count(*)||' files - '||round(sum(bytes)/1024/1024/1024) ||' GB */' cmd
from cdb_tablespaces join cdb_data_files using (con_id, tablespace_name)
where con_id!=2 and encrypted='NO'
group by con_id, tablespace_name
order by con_id,sum(bytes);
CMD
------------------------------------------------------------------------------------------------------------------------------------------------------
/* PDB: CDB$ROOT */ ALTER TABLESPACE USERS ENCRYPTION ONLINE ENCRYPT; /* 1 - 1 GB */
/* PDB: CDB$ROOT */ ALTER TABLESPACE ABC_ENCRYPT ENCRYPTION ONLINE ENCRYPT; /* 1 - 5 GB */
...
Run the output from above command in right container.
Follow-up TDE encryption
SELECT to_char(sysdate,'DD-MON-YY HH24:MI:SS') time, con_id, tablespace_name,
COUNT(*) as "#FILES", SUM(CASE WHEN encrypted = 'YES' THEN 1 ELSE 0 END) as "#ENC_FILES",
ROUND(SUM(bytes)/1024/1024/1024, 2) as size_gb,
TO_CHAR(ROUND( (SUM(CASE WHEN encrypted = 'YES' THEN bytes ELSE 0 END) / SUM(bytes)) * 100, 2 ),'90.00')||' %' as enc_perc
FROM v$datafile_header
WHERE con_id != 2
GROUP BY rollup (con_id, tablespace_name)
HAVING SUM(CASE WHEN encrypted = 'YES' THEN bytes ELSE 0 END) / SUM(bytes) < 1 /* only not finished */
ORDER BY con_id, tablespace_name;
--
or via Alertlog: tail -100f alert*.log | grep -B1 ":TDE converting datafile"
TDE encryption throughput
On a single node VM, the average throughput was 4 GB/minute – one 32GB datafile in a bit more than 8 minutes.On the standby side was a ExaCC, it took less than 5 minutes per 32GB datafile.
Having 4 parallel sessions encrypting the database on primary side did not change the timing per datafile, meaning: it was 4 times faster. On the standby side only one process is doing the encryption.
Resume TDE encryption
In case the encryption stops or is killed in the middle, you can continue the encryption operation by calling:
SQL> ALTER TABLESPACE <tbs_name> ENCRYPTION ONLINE FINISH ENCRYPT;
Re-create Temporary tablespaces
In each of the containers, re-create the temporary tablespace (add more tempfiles if needed with ALTER TABLESPACE xxx ADD TEMPFILE;):
SQL> SELECT '/* PDB: '||con_id_to_con_name(con_id)||' */ CREATE TEMPORARY TABLESPACE ' ||tablespace_name||'_ENC; /* tempfiles to be added: '||count(*)||' */' cmd
FROM CDB_TEMP_FILES
group by con_id, tablespace_name;
In each container – change default temporary tablespace to the new one:
SQL> ALTER DATABASE DEFAULT TEMPORARY TABLESPACE <old_name>_enc;
Drop the old temporary tablespace
SQL> DROP TABLESPACE TEMP INCLUDING CONTENTS AND DATAFILES;
Rename the temporary tablespace back (optional)
SQL> ALTER TABLESPACE <old_name>_enc RENAME TO <old_name>;
Check all tablespaces are encrypted
SQL> SELECT CON_ID, CONTENTS, TABLESPACE_NAME, ENCRYPTED
FROM CDB_TABLESPACES
ORDER BY 1,2,3;
Dataguard impact of TDE Encryption
When your database has a standby, it is usually better to use the following:
- Stop the apply process and make sure standby is in mount mode
- Perform OFFLINE encryption of tablespaces on standby
- Resume the apply process
- Switchover the database to the standby
- Repeat the OFFLINE encryption on the other side
If doing ONLINE TDE encryption on the primary side, then the encryption is simultaneous done on standby side.
The “ALTER TABLESPACE” DDL commands are sent to the standby and the TDE conversion in standby is run independently from the conversion in the primary. It can that the TDE conversion for some tablespaces finishes before on standby than on primary.
During the single tablespace encryption the the apply is paused (transport continues). The lag will increase until all datafiles of the tablespace are encrypted. On alertlog is possible to see this:
SWITCHOVER VERIFY: standby database's reccovery lags behind. The apply lag is 11781
Troubleshooting
Backups conflicts
If backups are running during the encryption, it can also happen that some files cannot be deleted, as they are still being read by rman. You get this in alertlog:
ANJO_PDB1(3):Successfully zero'ed out original file "+DATA/ANJOCDB/374BFC384F82D5B2E063DC62A10AB914/DATAFILE/sysaux.678.1205510495"
ANJO_PDB1(3):WARNING: Cannot delete old file +DATA/ANJOCDB/374BFC384F82D5B2E063DC62A10AB914/DATAFILE/sysaux.678.1205510495 left after datafile TDE conversion
2025-07-15T11:18:26.238729+02:00
ANJO_PDB1(3):Errors in file /u02/app/oracle/diag/rdbms/anjocdb/ANJOCDB/trace/ANJOCDB_pr00_179903.trc:
ORA-15028: Oracle Automatic Storage Management (Oracle ASM) file '+DATA/ANJOCDB/374BFC384F82D5B2E063DC62A10AB914/DATAFILE/sysaux.678.1205510495' not dropped; currently being accessed
Recover space from deleted datafiles
While the encryption happens datafile after datafile, it was seen that the “deleted” files did not have the space released until the sqlplus session was closed. The file descriptors remained there:
$ lsof +aL1 /u01/oradata/ANJO_CDB
COMMAND PID USER FD TYPE DEVICE SIZE/OFF NLINK NODE NAME
oracle_17 17729 oracle 276u REG 253,4 1 0 24015839280 /u01/oradata/ANJO_CDB/374BFC384F82D5B2E063DC62A10AB914/datafile/o1_mf_sysaux_n4lxr8gf_.dbf (deleted)
oracle_17 17729 oracle 278u REG 253,4 1 0 23734824823 /u01/oradata/ANJO_CDB/374BFC384F82D5B2E063DC62A10AB914/datafile/o1_mf_sysaux_n4lxr8gx_.dbf (deleted)
oracle_17 17729 oracle 279u REG 253,4 1 0 24015839269 /u01/oradata/ANJO_CDB/374BFC384F82D5B2E063DC62A10AB914/datafile/o1_mf_sysaux_n4lxr8h4_.dbf (deleted)
oracle_17 17729 oracle 280u REG 253,4 1 0 23734824830 /u01/oradata/ANJO_CDB/374BFC384F82D5B2E063DC62A10AB914/datafile/o1_mf_sysaux_n4lxr8hh_.dbf (deleted)
As I was encrypting a 10 TB tablespace and did not have enough disk space to wait, I did wrote a small script which is called by crontab every 30 minutes to empty the file descriptors and giving back the disk space.
$ cat rm_deleted_files.sh
#!/bin/bash
echo $(date) >> /tmp/log.txt
/sbin/lsof +aL1 /u01/oradata/ANJOCDB | awk 'NR>1 && $7 > 100000 {gsub(/[^0-9]/, "", $4); print $2, $4, $7, $10}' | while read pid fd size file; do
echo "/proc/$pid/fd/$fd" >> /tmp/log.txt
echo > "/proc/$pid/fd/$fd"
done