Oracle TDE Wallet Configuration on Linux – Complete Step-by-Step Guide

A complete production-ready SOP for configuring Oracle Transparent Data Encryption (TDE) on Linux from scratch. Covers wallet creation, master encryption key generation, tablespace and column encryption, auto-open wallet configuration, wallet backup and recovery, TDE in RAC environments, TDE with Data Guard, and full post-configuration validation — with real commands, expected outputs, and consultant-level notes for both standard OFA and enterprise custom path conventions.

1. Document Info

2. TDE — Concepts You Must Know First

📝 What is Oracle TDE? Transparent Data Encryption (TDE) encrypts sensitive data stored in Oracle database datafiles, redo logs, undo tablespaces, and temporary tablespaces at the storage level. The encryption is completely transparent to applications — they do not need any modification. Data is automatically encrypted when written to disk and decrypted when read into memory.

📝 Why use TDE? TDE protects data at rest — meaning if someone physically steals a disk, copies a datafile, or accesses backup tapes, they cannot read the data without the encryption key. TDE is the standard approach for compliance with regulations like PCI-DSS, HIPAA, GDPR that require encryption of sensitive data at rest.

Key TDE Concepts

TDE Data Flow

Application sends query
        │
        ▼
Oracle SGA (data in memory — DECRYPTED — application sees plain text)
        │
        ▼
Database Writer (DBWn) — encrypts data using TEK
        │                (TEK encrypted by MEK in keystore)
        ▼
Disk (datafiles, redo logs, undo — all ENCRYPTED on disk)

If disk is stolen → attacker sees only encrypted bytes → useless without MEK

TDE Keystore Types Comparison

📝 This SOP covers Software Wallet with Auto-Open — the most common production configuration for standalone and RAC environments.

3. Environment Details and Path Conventions

📝 Where should the wallet be stored?

• Must be on a LOCAL filesystem — not NFS, not ASM
• Must be accessible to the oracle OS user
• Must be backed up separately from the database — if you lose the wallet and the database is encrypted, data is permanently lost
• In RAC — wallet must be on SHARED storage accessible from all nodes OR a copy must exist on each node at the same path

⚠️ IMPORTANT: The wallet directory path is hardcoded in sqlnet.ora. Every node in a RAC and the standby server in a Data Guard configuration must have the wallet at the same path (or the path configured in their own sqlnet.ora).

4. Pre-Configuration Checks

⚠️ IMPORTANT: Complete all pre-checks before enabling TDE. Enabling TDE on a production database without proper planning and pre-checks is one of the most dangerous administrative operations — a mistake can make the entire database inaccessible.

4.1 — Verify Oracle Version Supports TDE Features

📝 Why? TDE feature availability varies by Oracle version and edition. Online tablespace encryption (ENCRYPTION ONLINE ENCRYPT) requires Oracle 12.2+. TDE column encryption requires Enterprise Edition.

-- Connect to database
sqlplus / as sysdba

set linesize 150
set pagesize 50
col banner for a80

-- Check version
SELECT banner FROM v$version WHERE banner LIKE 'Oracle%';

-- Check edition
SELECT * FROM v$version WHERE banner LIKE '%Edition%';

TDE feature availability:

4.2 — Check if TDE is Already Configured

📝 Why? In some environments TDE may already be partially configured by a previous DBA. Always check the current state before making changes.

sqlplus / as sysdba

set linesize 200
set pagesize 100
col wrl_type    for a15
col wrl_parameter for a60
col status      for a15
col wallet_type for a20
col con_id      for 999

-- Check current wallet status
SELECT wrl_type, wrl_parameter, status, wallet_type
FROM   v$encryption_wallet;

Wallet status meanings:

-- Check if any tablespaces are already encrypted
set linesize 200
set pagesize 100
col tablespace_name     for a25
col encrypted           for a10
col encryption_alg      for a20

SELECT tablespace_name, encrypted
FROM   dba_tablespaces
ORDER BY tablespace_name;

-- Check if any columns are encrypted
set linesize 200
set pagesize 100
col owner       for a20
col table_name  for a25
col column_name for a25
col encryption_alg for a20

SELECT owner, table_name, column_name, encryption_alg
FROM   dba_encrypted_columns
ORDER BY owner, table_name;

4.3 — Check Database is in ARCHIVELOG Mode

📝 Why? Enabling TDE modifies datafile headers and redo logs. If the database is not in ARCHIVELOG mode and something goes wrong during TDE enablement, you may not be able to recover the database. Always ensure ARCHIVELOG is enabled before configuring TDE.

SELECT name, log_mode FROM v$database;
-- Must show: ARCHIVELOG

4.4 — Take Full RMAN Backup Before Enabling TDE

⚠️ IMPORTANT: Take a full backup BEFORE enabling TDE. Once TDE is enabled and data is encrypted, your pre-TDE backup becomes the last backup that can be restored without the TDE wallet. Keep the pre-TDE backup safely for a long time.

su - oracle
rman target /

RMAN> BACKUP DATABASE PLUS ARCHIVELOG;
RMAN> LIST BACKUP SUMMARY;
RMAN> EXIT;

4.5 — Check Disk Space for Wallet and Encrypted Datafiles

📝 Why? When you encrypt an existing tablespace online, Oracle rewrites every datafile block. The encrypted tablespace may be slightly larger than the unencrypted one. Ensure sufficient disk space before encrypting large tablespaces.

# Check filesystem space
df -hP /u01/app/oracle/admin
df -hP /u01/app/oracle/oradata

# Check size of tablespaces you plan to encrypt

set linesize 200
set pagesize 100
col tablespace_name for a25
col total_gb        for 9999.99

SELECT df.tablespace_name,
       ROUND(SUM(df.bytes)/1024/1024/1024,2) total_gb
FROM   dba_data_files df
GROUP BY df.tablespace_name
ORDER BY total_gb DESC;

4.6 — Document Current Tablespace and Datafile Layout

📝 Why? Document everything BEFORE making changes. This is your reference in case you need to roll back or troubleshoot.

set linesize 200
set pagesize 100
col file#           for 999
col tablespace_name for a25
col bytes_gb        for 9999.99
col name            for a70

SELECT f.file#,
       f.name,
       t.name tablespace_name,
       f.bytes/1024/1024/1024 bytes_gb
FROM   v$datafile f,
       v$tablespace t
WHERE  f.ts# = t.ts#
ORDER BY t.name, f.file#;

5. Configure Wallet Location in sqlnet.ora

📝 Why sqlnet.ora? The sqlnet.ora file tells Oracle where to find the TDE wallet. This must be configured BEFORE creating the wallet. If sqlnet.ora does not have the ENCRYPTION_WALLET_LOCATION parameter, Oracle does not know where to look for the wallet and TDE commands will fail.

⚠️ IMPORTANT: Every server that runs Oracle Database and uses TDE must have this parameter correctly configured in its sqlnet.ora. This includes all RAC nodes and the standby server in a Data Guard configuration.

5.1 — Create Wallet Directory

# On DATABASE SERVER as oracle user
su - oracle

# Create wallet directory
# Convention A
mkdir -p /u01/app/oracle/admin/ORCL/wallet

# Convention B
mkdir -p /oracle/admin/ORCL/wallet

# Set permissions — only oracle user should access this directory
chmod 700 /u01/app/oracle/admin/ORCL/wallet

# Verify
ls -ld /u01/app/oracle/admin/ORCL/wallet
# Expected: drwx------. 2 oracle oinstall 4096 Jan 15 10:00 /u01/app/oracle/admin/ORCL/wallet

⚠️ IMPORTANT: Wallet directory permissions must be 700 (oracle only). If the directory is readable by other OS users, anyone with OS access can copy the wallet file — significantly reducing security.

5.2 — Configure ENCRYPTION_WALLET_LOCATION in sqlnet.ora

📝 Why this specific parameter? ENCRYPTION_WALLET_LOCATION tells Oracle where the TDE wallet files (ewallet.p12 and cwallet.sso) are stored. Without this, Oracle uses a default location which can vary and cause confusion.

# Edit sqlnet.ora
su - oracle
vi $ORACLE_HOME/network/admin/sqlnet.ora

Add the following block — DO NOT remove existing content, just add below it:

# -------------------------------------------------------
# TDE Wallet Configuration
# Added by DBA on <date>
# This tells Oracle where to find the TDE encryption wallet
# -------------------------------------------------------

# SQLNET.ENCRYPTION_SERVER and CLIENT are for network encryption
# (different from TDE — TDE is for data at rest)
# Do not confuse these with TDE settings below

# TDE Wallet location — path to directory containing ewallet.p12
# Must be a local filesystem path (not NFS, not ASM)
# Must be same path on all nodes (RAC) and standby server (Data Guard)
ENCRYPTION_WALLET_LOCATION =
  (SOURCE =
    (METHOD = FILE)
    (METHOD_DATA =
      (DIRECTORY = /u01/app/oracle/admin/ORCL/wallet)
    )
  )

📝 Convention B — change the directory path:

      (DIRECTORY = /oracle/admin/ORCL/wallet)

📝 For CDB (Container Database) environments in 19c, use the unified keystore location parameter instead:

WALLET_LOCATION =
  (SOURCE =
    (METHOD = FILE)
    (METHOD_DATA =
      (DIRECTORY = /u01/app/oracle/admin/ORCL/wallet)
    )
  )

# Verify sqlnet.ora has correct content
cat $ORACLE_HOME/network/admin/sqlnet.ora | grep -A8 "ENCRYPTION_WALLET"

6. Create the TDE Software Keystore (Wallet)

📝 What happens when you create a keystore? Oracle creates two files in the wallet directory:

• ewallet.p12 — the password-protected wallet file (PKCS#12 format)
• Later when you configure auto-open, Oracle creates cwallet.sso — the auto-open wallet

⚠️ IMPORTANT: The wallet password you set here is CRITICAL. Write it down and store it securely in a password vault. If you lose this password AND lose the cwallet.sso file, your encrypted data is permanently inaccessible. There is no Oracle Support procedure to recover a lost wallet password.

6.1 — Create the Software Keystore

-- Connect to database as SYSDBA
sqlplus / as sysdba

-- Create the software keystore
-- Replace 'MyWallet_Password_123' with a strong password
-- Store this password in your organization's password vault IMMEDIATELY
ADMINISTER KEY MANAGEMENT
    CREATE KEYSTORE '/u01/app/oracle/admin/ORCL/wallet'
    IDENTIFIED BY MyWallet_Password_123;

-- Convention B:
-- ADMINISTER KEY MANAGEMENT
--     CREATE KEYSTORE '/oracle/admin/ORCL/wallet'
--     IDENTIFIED BY MyWallet_Password_123;

# Verify ewallet.p12 was created
ls -lh /u01/app/oracle/admin/ORCL/wallet/

Expected output:

-rw-------. 1 oracle oinstall 2555 Jan 15 10:05 ewallet.p12

⚠️ IMPORTANT: If you see ewallet.p12 with only oracle having read permissions (600) — that is correct and expected. Do not change these permissions.

6.2 — Open the Keystore

📝 Why open it? Creating the keystore does not automatically open it. You must open it before you can create master encryption keys or encrypt any data. Think of it as creating a safe (CREATE KEYSTORE) vs unlocking it (OPEN KEYSTORE).

-- Open the keystore
ADMINISTER KEY MANAGEMENT
    SET KEYSTORE OPEN
    IDENTIFIED BY MyWallet_Password_123;

-- Verify keystore is now open
set linesize 200
set pagesize 50
col wrl_type      for a15
col wrl_parameter for a60
col status        for a20
col wallet_type   for a20

SELECT wrl_type, wrl_parameter, status, wallet_type
FROM   v$encryption_wallet;

Expected output:

WRL_TYPE  WRL_PARAMETER                              STATUS              WALLET_TYPE
--------- ------------------------------------------ ------------------- --------------------
FILE      /u01/app/oracle/admin/ORCL/wallet/         OPEN_NO_MASTER_KEY  PASSWORD

📝 OPEN_NO_MASTER_KEY is correct at this stage — we have not yet created the Master Encryption Key. We do that next.

6.3 — Create and Activate Master Encryption Key

📝 What is the Master Encryption Key (MEK)? The MEK is the root key of the TDE encryption hierarchy. All tablespace and column encryption keys are encrypted by the MEK and stored in the database. The MEK itself is stored in the keystore. When the keystore is open, Oracle can decrypt the TEKs and CEKs using the MEK — allowing access to encrypted data.

📝 Why WITH BACKUP? Creating a new MEK automatically backs up the old one (if any) inside the keystore. The WITH BACKUP clause creates an additional backup of the keystore in the wallet directory before generating the new key. Always use this.

-- Create Master Encryption Key
-- This is the most critical TDE step
-- WITH BACKUP creates a backup before generating new key
ADMINISTER KEY MANAGEMENT
    SET KEY
    IDENTIFIED BY MyWallet_Password_123
    WITH BACKUP USING 'initial_mek_backup';

Expected output:

keystore altered.

-- Verify Master Encryption Key is created and keystore is fully open
SELECT wrl_type, wrl_parameter, status, wallet_type
FROM   v$encryption_wallet;

Expected output:

WRL_TYPE  WRL_PARAMETER                              STATUS   WALLET_TYPE
--------- ------------------------------------------ -------- --------------------
FILE      /u01/app/oracle/admin/ORCL/wallet/         OPEN     PASSWORD

📝 Status changed from OPEN_NO_MASTER_KEY to OPEN — the keystore is now fully configured with a Master Encryption Key.

# Check wallet directory — backup file should now exist
ls -lh /u01/app/oracle/admin/ORCL/wallet/

Expected files:

-rw-------. 1 oracle oinstall  2555 Jan 15 10:05 ewallet.p12
-rw-------. 1 oracle oinstall  5120 Jan 15 10:10 ewallet_2024011510100000.p12

📝 The timestamped file (ewallet_<timestamp>.p12) is the backup created by WITH BACKUP. Keep it — it contains the MEK and can restore access to encrypted data.

7. Configure Auto-Open Keystore

📝 Why auto-open? With a password-protected keystore, every time the database restarts (patching, OS reboot, crash recovery), you must manually open the keystore before encrypted tablespaces are accessible. In production environments databases restart unattended (via systemd, after power outage etc.) — if the keystore is not open, the database starts but encrypted tablespaces are inaccessible and applications fail.

📝 How auto-open works? Oracle creates a special wallet file cwallet.sso (SSO = Single Sign-On). This file contains the wallet password encrypted in a way that Oracle can read automatically at startup without human intervention. It is derived from the password-protected wallet.

⚠️ IMPORTANT: cwallet.sso provides convenience but reduces security slightly — anyone who can copy this file can open the wallet on another system. Protect the wallet directory with strict OS permissions (700) and consider encrypting the filesystem.

7.1 — Create Auto-Open Keystore

-- Connect to database as SYSDBA
sqlplus / as sysdba

-- Create auto-open (SSO) keystore from the existing password keystore
-- This creates cwallet.sso in the wallet directory
ADMINISTER KEY MANAGEMENT
    CREATE AUTO_LOGIN KEYSTORE
    FROM KEYSTORE '/u01/app/oracle/admin/ORCL/wallet'
    IDENTIFIED BY MyWallet_Password_123;

# Verify cwallet.sso was created
ls -lh /u01/app/oracle/admin/ORCL/wallet/

Expected files after auto-open configuration:

-rw-------. 1 oracle oinstall  2555 Jan 15 10:05 ewallet.p12
-rw-------. 1 oracle oinstall  5120 Jan 15 10:10 ewallet_2024011510100000.p12
-rw-------. 1 oracle oinstall  3072 Jan 15 10:15 cwallet.sso

7.2 — Test Auto-Open by Bouncing the Database

📝 Why test? Auto-open must be tested BEFORE you encrypt any data. If auto-open is not working and you encrypt a tablespace, the database will start after a reboot but encrypted tablespaces will be inaccessible until someone manually opens the keystore.

-- Close and reopen database to test auto-open
sqlplus / as sysdba

SHUTDOWN IMMEDIATE;
STARTUP;

-- Check wallet status IMMEDIATELY after startup
-- WITHOUT manually opening the wallet
SELECT wrl_type, wrl_parameter, status, wallet_type
FROM   v$encryption_wallet;

Expected output after startup (auto-open working):

WRL_TYPE  WRL_PARAMETER                              STATUS   WALLET_TYPE
--------- ------------------------------------------ -------- --------------------
FILE      /u01/app/oracle/admin/ORCL/wallet/         OPEN     AUTOLOGIN

📝 WALLET_TYPE = AUTOLOGIN confirms auto-open is working. The wallet opened automatically without any manual intervention.

⚠️ IMPORTANT: If status shows CLOSED after startup, auto-open is NOT working. Do NOT encrypt anything. Investigate why cwallet.sso is not being picked up (check sqlnet.ora path, file permissions, file existence).

8. Tablespace Encryption

📝 Two approaches to tablespace encryption:

1. New tablespace — Create a new tablespace with encryption enabled from the start. Simple and clean.
2. Existing tablespace — Encrypt an existing tablespace that already has data. Requires online encryption (12.2+) or offline encryption with downtime.

8.1 — Create a New Encrypted Tablespace

📝 Why create new encrypted tablespaces? For new applications or new data, always create tablespaces with encryption enabled. This is simpler than encrypting existing tablespaces.

-- Connect to database as SYSDBA
sqlplus / as sysdba

-- Verify keystore is open before creating encrypted tablespace
SELECT status, wallet_type FROM v$encryption_wallet;
-- Must show: OPEN

-- Create new encrypted tablespace
-- ENCRYPTION AES256 = use AES 256-bit encryption (recommended)
-- DEFAULT STORAGE (ENCRYPT) = all objects created in this tablespace are encrypted
-- Convention A (filesystem)
CREATE TABLESPACE encrypted_data
    DATAFILE '/u01/app/oracle/oradata/ORCL/encrypted_data01.dbf'
    SIZE 5G
    AUTOEXTEND ON NEXT 1G MAXSIZE UNLIMITED
    EXTENT MANAGEMENT LOCAL
    SEGMENT SPACE MANAGEMENT AUTO
    ENCRYPTION USING 'AES256'
    DEFAULT STORAGE (ENCRYPT);

-- Convention B (ASM)
CREATE TABLESPACE encrypted_data
    DATAFILE '+DATA'
    SIZE 5G
    AUTOEXTEND ON NEXT 1G MAXSIZE UNLIMITED
    ENCRYPTION USING 'AES256'
    DEFAULT STORAGE (ENCRYPT);

-- Verify tablespace was created with encryption
set linesize 200
set pagesize 50
col tablespace_name for a25
col encrypted       for a10
col def_tab_compression for a20

SELECT tablespace_name, encrypted, block_size
FROM   dba_tablespaces
WHERE  tablespace_name = 'ENCRYPTED_DATA';

Expected output:

TABLESPACE_NAME           ENCRYPTED  BLOCK_SIZE
------------------------- ---------- ----------
ENCRYPTED_DATA            YES              8192

8.2 — Encrypt an Existing Tablespace (Online — No Downtime)

📝 What is online encryption? In Oracle 12.2 and 19c, you can encrypt an existing tablespace while it is online and the database is serving users. Oracle rewrites each datafile block-by-block in the background. This is the preferred method for production systems.

📝 How long does it take? Depends on tablespace size and system I/O capacity. Typically 1-5 GB per minute on a healthy system. A 100 GB tablespace could take 20-100 minutes. Plan the timing accordingly — while users can still access the tablespace during encryption, there will be elevated I/O.

⚠️ IMPORTANT: Monitor v$rman_status and v$session_longops during online encryption to track progress and estimated completion time.

-- Verify keystore is open
SELECT status FROM v$encryption_wallet;

-- Check tablespace current state before encryption
SELECT tablespace_name, encrypted
FROM   dba_tablespaces
WHERE  tablespace_name = 'USERS';

-- Encrypt existing USERS tablespace online
-- ENCRYPT = encrypt all existing blocks
-- AES256 = encryption algorithm
-- ONLINE = do not require tablespace to be offline
ALTER TABLESPACE users
    ENCRYPTION ONLINE
    USING 'AES256'
    ENCRYPT;

-- This command returns immediately but encryption runs in background
-- Monitor progress:

-- Monitor online encryption progress
-- Run this repeatedly until no rows returned (encryption complete)
set linesize 200
set pagesize 100
col sid         for 999
col serial#     for 99999
col opname      for a40
col target      for a30
col sofar       for 9999999
col totalwork   for 9999999
col pct_done    for 999.99
col elapsed_sec for 99999

SELECT sid, serial#, opname, target,
       sofar, totalwork,
       ROUND(sofar/DECODE(totalwork,0,1,totalwork)*100,2) pct_done,
       elapsed_seconds elapsed_sec
FROM   v$session_longops
WHERE  opname LIKE '%Encrypt%'
AND    sofar < totalwork
ORDER BY start_time;

-- After encryption completes -- verify tablespace is now encrypted
SELECT tablespace_name, encrypted
FROM   dba_tablespaces
WHERE  tablespace_name = 'USERS';
-- Must show: ENCRYPTED = YES

8.3 — Encrypt Multiple Tablespaces

📝 Best practice: Encrypt tablespaces one at a time in production to avoid overloading the I/O subsystem with multiple concurrent encryption operations.

-- Encrypt USERS tablespace first -- wait for completion
ALTER TABLESPACE users  ENCRYPTION ONLINE USING 'AES256' ENCRYPT;

-- After USERS is done, encrypt EXAMPLE
ALTER TABLESPACE example ENCRYPTION ONLINE USING 'AES256' ENCRYPT;

-- Never encrypt SYSTEM or UNDO tablespaces with TDE
-- These must remain unencrypted for Oracle internal operations
-- You CAN encrypt TEMP tablespace if needed for compliance

-- Encrypt TEMP tablespace (if required by compliance)
ALTER TABLESPACE temp ENCRYPTION ONLINE USING 'AES256' ENCRYPT;

-- Verify all tablespaces encryption status
set linesize 200
set pagesize 100
col tablespace_name for a30
col encrypted       for a10
col status          for a12

SELECT tablespace_name, status, encrypted
FROM   dba_tablespaces
ORDER BY tablespace_name;

8.4 — Encrypt Existing Tablespace Offline (Older Method — Requires Downtime)

📝 When to use? If Oracle version is below 12.2 or if online encryption is not available. This method requires the tablespace to be taken offline during encryption.

-- Take tablespace offline
ALTER TABLESPACE users OFFLINE NORMAL;

-- Encrypt the tablespace
ALTER TABLESPACE users
    ENCRYPTION OFFLINE
    USING 'AES256'
    ENCRYPT;

-- Bring tablespace back online
ALTER TABLESPACE users ONLINE;

-- Verify
SELECT tablespace_name, encrypted
FROM   dba_tablespaces
WHERE  tablespace_name = 'USERS';

9. Column-Level Encryption

📝 When to use column encryption instead of tablespace encryption?

• When you only need to encrypt specific sensitive columns (SSN, credit card numbers, passwords) and not entire tablespaces
• When most of the table data is not sensitive but a few columns are
• Column encryption is more granular but has higher CPU overhead per row compared to tablespace encryption
• For new columns — encrypt at CREATE TABLE time
• For existing columns — use ALTER TABLE to add encryption

⚠️ IMPORTANT: Column encryption and tablespace encryption can be used together. A column in an encrypted tablespace gets double encryption — first by the column encryption key, then by the tablespace encryption key. This is usually unnecessary overhead — use one or the other.

9.1 — Create New Table with Encrypted Columns

-- Create a table with specific columns encrypted
-- Sensitive columns: credit_card_number, ssn, salary
CREATE TABLE hr.sensitive_data (
    employee_id     NUMBER(6)     NOT NULL,
    first_name      VARCHAR2(20),
    last_name       VARCHAR2(25)  NOT NULL,

    -- Encrypt SSN with AES192 - no salt (faster lookups, allows index)
    ssn             VARCHAR2(11)
                    ENCRYPT USING 'AES192' NO SALT,

    -- Encrypt credit card with AES256 - with salt (default, more secure)
    credit_card_num VARCHAR2(20)
                    ENCRYPT USING 'AES256',

    -- Encrypt salary with 3DES168
    salary          NUMBER(10,2)
                    ENCRYPT USING '3DES168',

    CONSTRAINT sensitive_emp_pk PRIMARY KEY (employee_id)
);

-- Verify column encryption
set linesize 200
set pagesize 100
col owner          for a15
col table_name     for a25
col column_name    for a25
col encryption_alg for a20
col salt           for a5

SELECT owner, table_name, column_name,
       encryption_alg, salt
FROM   dba_encrypted_columns
WHERE  owner = 'HR'
ORDER BY table_name, column_name;

📝 SALT vs NO SALT:

• SALT (default) — adds random data to each encrypted value. Two identical plaintext values produce different ciphertext. More secure. BUT you cannot create B-tree indexes on SALTed columns.
• NO SALT — identical plaintext values produce identical ciphertext. Allows B-tree indexes for equality searches. Less secure but needed for searchable encrypted columns.

9.2 — Add Encryption to Existing Column

📝 Why encrypt existing columns? If you have a production table with sensitive data in unencrypted columns and need to add encryption without recreating the table.

⚠️ IMPORTANT: Adding encryption to an existing column rewrites every row in the table. This takes time proportional to table size and generates significant undo/redo. Do this during a maintenance window for large tables.

-- Add encryption to existing column
-- Table: hr.employees, Column: salary (already exists with data)
ALTER TABLE hr.employees
    MODIFY (salary ENCRYPT USING 'AES256' NO SALT);

-- Verify
SELECT owner, table_name, column_name, encryption_alg
FROM   dba_encrypted_columns
WHERE  owner      = 'HR'
AND    table_name = 'EMPLOYEES';

9.3 — Remove Column Encryption

-- Remove encryption from a column (decrypts all existing data)
-- Generates significant undo/redo -- do during maintenance window
ALTER TABLE hr.employees MODIFY (salary DECRYPT);

-- Verify encryption removed
SELECT owner, table_name, column_name
FROM   dba_encrypted_columns
WHERE  owner      = 'HR'
AND    table_name = 'EMPLOYEES';
-- Should return no rows for SALARY column

10. Wallet Operations — Day-to-Day Management

10.1 — Open Wallet Manually (Password-Protected)

📝 When needed? After closing the auto-open wallet manually, or in environments where only password-protected wallet is used (no auto-open).

sqlplus / as sysdba

-- Open the wallet with password
ADMINISTER KEY MANAGEMENT
    SET KEYSTORE OPEN
    IDENTIFIED BY MyWallet_Password_123;

-- Verify
SELECT status, wallet_type FROM v$encryption_wallet;

10.2 — Close Wallet

📝 When to close? Rarely needed in production. Closing the wallet makes encrypted tablespaces inaccessible. Only close if explicitly required by security policy or during wallet maintenance.

-- Close the wallet
-- This makes ALL encrypted tablespaces inaccessible
-- Applications will get ORA-28365 until wallet is reopened
ADMINISTER KEY MANAGEMENT
    SET KEYSTORE CLOSE
    IDENTIFIED BY MyWallet_Password_123;

-- Verify
SELECT status FROM v$encryption_wallet;
-- Shows: CLOSED

10.3 — Change Wallet Password

📝 Why change the password? Regular password rotation is a security best practice. Also change immediately if the wallet password is suspected to be compromised.

⚠️ IMPORTANT: After changing the wallet password, you MUST recreate the auto-open wallet from the new password-protected wallet. The old cwallet.sso will no longer work.

-- Change wallet password
-- Wallet must be OPEN before changing password
ADMINISTER KEY MANAGEMENT
    ALTER KEYSTORE PASSWORD
    IDENTIFIED BY MyWallet_Password_123
    SET NewWallet_Password_456
    WITH BACKUP USING 'before_pwd_change';

-- After password change -- recreate auto-open wallet
ADMINISTER KEY MANAGEMENT
    CREATE AUTO_LOGIN KEYSTORE
    FROM KEYSTORE '/u01/app/oracle/admin/ORCL/wallet'
    IDENTIFIED BY NewWallet_Password_456;

-- Verify auto-open still works by checking wallet status
SELECT status, wallet_type FROM v$encryption_wallet;

10.4 — Rotate Master Encryption Key

📝 Why rotate the MEK? Security best practice recommends rotating encryption keys periodically. Also rotate if there is any suspicion that the key has been compromised. Rotating the MEK re-encrypts all Tablespace Encryption Keys with the new MEK — the actual data in datafiles is NOT re-encrypted (only the key hierarchy changes).

-- Rotate Master Encryption Key
-- WITH BACKUP backs up existing wallet before creating new key
ADMINISTER KEY MANAGEMENT
    SET KEY
    IDENTIFIED BY MyWallet_Password_123
    WITH BACKUP USING 'before_key_rotation';

-- After rotation -- verify new key is active
SELECT status, wallet_type FROM v$encryption_wallet;

-- Check key history
set linesize 200
set pagesize 100
col key_id         for a70
col creation_time  for a25
col activation_time for a25
col tag            for a30

SELECT key_id, creation_time, activation_time, tag
FROM   v$encryption_keys
ORDER BY creation_time;

10.5 — Backup the Wallet

📝 Why backup the wallet separately? The wallet is NOT backed up by RMAN. RMAN backs up datafiles, archivelogs, and control files — not the wallet directory. You must backup the wallet separately. If you lose the wallet and have no backup, ALL encrypted data is permanently inaccessible.

# On DATABASE SERVER as oracle user
su - oracle

# Create a timestamped backup of the wallet directory
WALLET_DIR=/u01/app/oracle/admin/ORCL/wallet
BACKUP_DIR=/u01/app/oracle/admin/ORCL/wallet_backup
BACKUP_DATE=$(date +%Y%m%d_%H%M%S)

# Create backup directory
mkdir -p $BACKUP_DIR

# Copy wallet files with timestamp
cp -p $WALLET_DIR/ewallet.p12  \
      $BACKUP_DIR/ewallet_${BACKUP_DATE}.p12

cp -p $WALLET_DIR/cwallet.sso  \
      $BACKUP_DIR/cwallet_${BACKUP_DATE}.sso

# Verify backup
ls -lh $BACKUP_DIR/

# Optionally -- encrypt the backup of the wallet before copying off-server
# (encrypt a backup of the encryption key -- ironic but important)
openssl enc -aes-256-cbc -in $BACKUP_DIR/ewallet_${BACKUP_DATE}.p12 \
    -out $BACKUP_DIR/ewallet_${BACKUP_DATE}.p12.enc \
    -k BackupEncryptionPassword_789

# Copy encrypted backup to a remote/offsite location
scp $BACKUP_DIR/ewallet_${BACKUP_DATE}.p12.enc \
    oracle@backupserver:/secure/wallet_backups/

⚠️ IMPORTANT: Store the wallet backup in a location completely separate from the database server. If the database server fails completely, you need the wallet backup to restore and decrypt the database on new hardware. A wallet backup on the same server as the database defeats the purpose.

10.6 — Export Wallet (Merge Wallets / Migration)

📝 Why export? When migrating to a new server, you need to export the wallet from the old server and import it on the new server so the new database instance can open the same encrypted tablespaces.

-- Export the wallet to a file
-- Creates a self-contained export file
ADMINISTER KEY MANAGEMENT
    EXPORT KEYS
    WITH SECRET 'ExportSecret_123'
    TO '/u01/app/oracle/admin/ORCL/wallet/wallet_export.exp'
    IDENTIFIED BY MyWallet_Password_123;

# Copy the export file to new server
scp /u01/app/oracle/admin/ORCL/wallet/wallet_export.exp \
    oracle@newserver:/u01/app/oracle/admin/ORCL/wallet/

-- On NEW SERVER -- import the wallet
ADMINISTER KEY MANAGEMENT
    IMPORT KEYS
    WITH SECRET 'ExportSecret_123'
    FROM '/u01/app/oracle/admin/ORCL/wallet/wallet_export.exp'
    IDENTIFIED BY MyWallet_Password_123
    WITH BACKUP;

11. TDE in RAC Environment

📝 What is different about TDE in RAC? In a RAC environment, all nodes must be able to open the same wallet. Since each node runs an independent Oracle instance, each node needs access to the wallet. There are two approaches:

1. Shared wallet on shared filesystem — wallet stored on ACFS (ASM Cluster File System) or NFS, accessible from all nodes
2. Copied wallet on each node — same wallet files copied to the same local path on each node

📝 Shared wallet is preferred because when you rotate the MEK or change the password, the change is immediately reflected on all nodes. With copied wallets, you must manually copy the updated wallet to all nodes after every change.

11.1 — Configure sqlnet.ora on All RAC Nodes

# Configure sqlnet.ora on ALL RAC nodes (node1 and node2)
# Must use the SAME wallet path on all nodes

# On NODE 1
su - oracle
vi $ORACLE_HOME/network/admin/sqlnet.ora

# Add on ALL nodes -- same path
ENCRYPTION_WALLET_LOCATION =
  (SOURCE =
    (METHOD = FILE)
    (METHOD_DATA =
      (DIRECTORY = /u01/app/oracle/admin/ORCL/wallet)
    )
  )

# Copy sqlnet.ora to node2 (same content -- same path)
scp $ORACLE_HOME/network/admin/sqlnet.ora \
    oracle@racnode2:$ORACLE_HOME/network/admin/sqlnet.ora

11.2 — Option A: Shared Wallet on ACFS

# Create wallet on ACFS (shared storage accessible to all nodes)
# ACFS mount point visible on all nodes -- example: /u01/acfsmounts/ggwallet
su - oracle

mkdir -p /u01/acfsmounts/ggwallet/ORCL
chmod 700 /u01/acfsmounts/ggwallet/ORCL

# Update sqlnet.ora on ALL nodes to point to shared location
ENCRYPTION_WALLET_LOCATION =
  (SOURCE =
    (METHOD = FILE)
    (METHOD_DATA =
      (DIRECTORY = /u01/acfsmounts/ggwallet/ORCL)
    )
  )

11.3 — Option B: Copy Wallet to Each Node

# After creating wallet on node1 -- copy to node2
# Must be copied to SAME path as on node1

# On NODE 1 -- copy wallet files to node2
scp /u01/app/oracle/admin/ORCL/wallet/ewallet.p12 \
    oracle@racnode2:/u01/app/oracle/admin/ORCL/wallet/ewallet.p12

scp /u01/app/oracle/admin/ORCL/wallet/cwallet.sso \
    oracle@racnode2:/u01/app/oracle/admin/ORCL/wallet/cwallet.sso

# Set permissions on node2
ssh oracle@racnode2 \
    "chmod 600 /u01/app/oracle/admin/ORCL/wallet/ewallet.p12; \
     chmod 600 /u01/app/oracle/admin/ORCL/wallet/cwallet.sso"

⚠️ IMPORTANT: In Option B, every time you change the wallet password or rotate the MEK, you MUST copy the updated wallet to ALL nodes. Forgetting to copy means some nodes have an outdated wallet — they can still read currently cached data in memory but after an instance restart on that node, the wallet will fail to open.

11.4 — Verify TDE on All RAC Nodes

-- Connect to each instance and verify wallet status
-- On NODE 1
sqlplus / as sysdba

SELECT inst_id, wrl_type, wrl_parameter, status, wallet_type
FROM   gv$encryption_wallet
ORDER BY inst_id;

Expected output:

INST_ID  WRL_TYPE  WRL_PARAMETER                        STATUS   WALLET_TYPE
-------- --------- ------------------------------------ -------- -----------
       1 FILE      /u01/app/oracle/admin/ORCL/wallet/   OPEN     AUTOLOGIN
       2 FILE      /u01/app/oracle/admin/ORCL/wallet/   OPEN     AUTOLOGIN

⚠️ IMPORTANT: Both instances (all nodes) must show OPEN status. If any instance shows CLOSED, encrypted tablespaces are inaccessible from that node.

12. TDE with Data Guard

📝 How does TDE work with Data Guard? In a Data Guard configuration, the standby database applies redo from the primary. The redo logs contain encrypted data. The standby needs the same wallet as the primary to decrypt the data it receives via redo and to keep standby tablespaces encrypted. Both primary and standby must have the same wallet with the same Master Encryption Key.

12.1 — Configure sqlnet.ora on Standby Server

# On STANDBY server
su - oracle

vi $ORACLE_HOME/network/admin/sqlnet.ora

# Add on STANDBY server -- same wallet path structure
# (path may differ if using Convention B)
ENCRYPTION_WALLET_LOCATION =
  (SOURCE =
    (METHOD = FILE)
    (METHOD_DATA =
      (DIRECTORY = /u01/app/oracle/admin/ORCL_STBY/wallet)
    )
  )

12.2 — Copy Wallet from Primary to Standby

📝 Why copy the wallet? The standby must have the SAME wallet as the primary — specifically the same Master Encryption Key. Without this, the standby cannot decrypt the redo it receives from the primary.

⚠️ IMPORTANT: Copy the wallet BEFORE configuring MRP on the standby. If MRP tries to apply encrypted redo without a valid open wallet, it will error and stop.

# On PRIMARY server -- create standby wallet directory
# and copy wallet to standby
su - oracle

# Create wallet directory on standby first
ssh oracle@dbserver02 \
    "mkdir -p /u01/app/oracle/admin/ORCL_STBY/wallet && \
     chmod 700 /u01/app/oracle/admin/ORCL_STBY/wallet"

# Copy both wallet files from primary to standby
scp /u01/app/oracle/admin/ORCL/wallet/ewallet.p12 \
    oracle@dbserver02:/u01/app/oracle/admin/ORCL_STBY/wallet/ewallet.p12

scp /u01/app/oracle/admin/ORCL/wallet/cwallet.sso \
    oracle@dbserver02:/u01/app/oracle/admin/ORCL_STBY/wallet/cwallet.sso

# Set permissions on standby
ssh oracle@dbserver02 \
    "chmod 600 /u01/app/oracle/admin/ORCL_STBY/wallet/ewallet.p12; \
     chmod 600 /u01/app/oracle/admin/ORCL_STBY/wallet/cwallet.sso"

# Verify files on standby
ssh oracle@dbserver02 \
    "ls -lh /u01/app/oracle/admin/ORCL_STBY/wallet/"

12.3 — Open Wallet on Standby and Verify

-- Connect to STANDBY database
sqlplus / as sysdba

-- Check wallet status (auto-open should work if cwallet.sso copied correctly)
SELECT wrl_type, wrl_parameter, status, wallet_type
FROM   v$encryption_wallet;
-- Expected: STATUS=OPEN, WALLET_TYPE=AUTOLOGIN

-- If not auto-open -- open manually
ADMINISTER KEY MANAGEMENT
    SET KEYSTORE OPEN
    IDENTIFIED BY MyWallet_Password_123;

12.4 — Wallet Sync Procedure After MEK Rotation in DG

📝 Why? After rotating the Master Encryption Key on the primary, the standby wallet does not automatically get the new key. You must copy the updated wallet to the standby.

⚠️ IMPORTANT: This must be done PROMPTLY after MEK rotation. If the standby wallet does not have the new MEK, MRP will stop when it tries to apply redo that was encrypted with the new key.

# STEP 1 -- On PRIMARY: rotate MEK (already covered in Section 10.4)
# After rotation -- copy new wallet to standby

# STEP 2 -- Stop MRP on standby before copying wallet
# (on standby)
sqlplus / as sysdba
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

# STEP 3 -- Copy updated wallet from primary to standby
scp /u01/app/oracle/admin/ORCL/wallet/ewallet.p12 \
    oracle@dbserver02:/u01/app/oracle/admin/ORCL_STBY/wallet/ewallet.p12

scp /u01/app/oracle/admin/ORCL/wallet/cwallet.sso \
    oracle@dbserver02:/u01/app/oracle/admin/ORCL_STBY/wallet/cwallet.sso

# STEP 4 -- On standby: close and reopen wallet to pick up new key
# (on standby)
sqlplus / as sysdba

ADMINISTER KEY MANAGEMENT SET KEYSTORE CLOSE;
-- Wallet auto-opens with new key from cwallet.sso
SELECT status, wallet_type FROM v$encryption_wallet;

# STEP 5 -- Restart MRP
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE
    USING CURRENT LOGFILE DISCONNECT FROM SESSION;

# STEP 6 -- Verify MRP is applying redo
SELECT process, status, sequence# FROM v$managed_standby WHERE process='MRP0';

13. TDE Patching Considerations

📝 What to know about TDE during patching? Patching the Oracle Home does not affect the wallet or encrypted data. The wallet stays where it is and the encryption keys remain valid across patches. However there are a few things to be aware of.

13.1 — Verify Wallet Opens After Patching

-- After applying any Oracle patch and restarting the database
-- Verify wallet auto-opened correctly
sqlplus / as sysdba

SELECT wrl_type, wrl_parameter, status, wallet_type
FROM   v$encryption_wallet;
-- Must show: STATUS=OPEN, WALLET_TYPE=AUTOLOGIN

-- Verify encrypted tablespaces are accessible
SELECT tablespace_name, encrypted
FROM   dba_tablespaces
WHERE  encrypted = 'YES';

-- Run a query against an encrypted tablespace to confirm access
SELECT COUNT(*) FROM hr.employees;
-- If wallet is not open this will error with ORA-28365

13.2 — TDE and datapatch

📝 Why mention datapatch? datapatch applies SQL changes to the data dictionary. If the SYSTEM tablespace or any tablespace containing SYSMAN objects is encrypted, datapatch must be able to open the wallet to access those objects. With auto-open wallet this is transparent. With password-protected wallet only, you must open the wallet before running datapatch.

# Ensure wallet is open before running datapatch
sqlplus / as sysdba

-- Confirm wallet is open
SELECT status FROM v$encryption_wallet;
-- Must show: OPEN

EXIT;

# Then run datapatch
$ORACLE_HOME/OPatch/datapatch -verbose

14. Post-Configuration Checks

⚠️ IMPORTANT: Run all post-configuration checks after TDE is enabled and after any wallet operation. These checks confirm TDE is working correctly and encrypted data is accessible.

14.1 — Verify Keystore Status

sqlplus / as sysdba

set linesize 200
set pagesize 50
col wrl_type      for a15
col wrl_parameter for a60
col status        for a20
col wallet_type   for a20

SELECT wrl_type, wrl_parameter, status, wallet_type
FROM   v$encryption_wallet;

Must show: STATUS = OPEN and WALLET_TYPE = AUTOLOGIN

14.2 — Verify All Encrypted Tablespaces Are Accessible

set linesize 200
set pagesize 100
col tablespace_name     for a25
col status              for a12
col encrypted           for a10
col encryption_alg      for a20

SELECT t.tablespace_name,
       t.status,
       t.encrypted
FROM   dba_tablespaces t
WHERE  t.encrypted = 'YES'
ORDER BY t.tablespace_name;

-- Test access to each encrypted tablespace
-- Substitute your actual encrypted tablespace and table names
SELECT COUNT(*) FROM hr.employees;
SELECT COUNT(*) FROM hr.departments;
-- These queries must succeed without ORA-28365 errors

14.3 — Verify Master Encryption Key Information

set linesize 200
set pagesize 100
col key_id          for a70
col tag             for a30
col creation_time   for a25
col activation_time for a25
col key_use         for a15

SELECT key_id,
       tag,
       creation_time,
       activation_time,
       key_use
FROM   v$encryption_keys
ORDER BY creation_time DESC;

What to look for: Most recent key must show ACTIVE in KEY_USE.

14.4 — Verify Wallet Files Exist and Have Correct Permissions

# On DATABASE SERVER
su - oracle

# List wallet files
ls -lh /u01/app/oracle/admin/ORCL/wallet/

# Convention B
ls -lh /oracle/admin/ORCL/wallet/

Expected files and permissions:

-rw-------. 1 oracle oinstall  2555 ewallet.p12
-rw-------. 1 oracle oinstall  3072 cwallet.sso
-rw-------. 1 oracle oinstall  5120 ewallet_<timestamp>.p12  (backup files)

⚠️ IMPORTANT: All wallet files must be owned by oracle with permissions 600 (rw——-). If permissions are wider, fix them immediately:

chmod 600 /u01/app/oracle/admin/ORCL/wallet/ewallet.p12
chmod 600 /u01/app/oracle/admin/ORCL/wallet/cwallet.sso

14.5 — Verify sqlnet.ora Configuration

# Confirm wallet path in sqlnet.ora matches actual wallet location
grep -A6 "ENCRYPTION_WALLET" $ORACLE_HOME/network/admin/sqlnet.ora

# Path in sqlnet.ora must match actual wallet directory
ls /u01/app/oracle/admin/ORCL/wallet/ewallet.p12

14.6 — Verify No Encrypted Tablespaces Are in OFFLINE Status

set linesize 200
set pagesize 100
col tablespace_name for a25
col status          for a12
col encrypted       for a10

-- All encrypted tablespaces must be ONLINE
SELECT tablespace_name, status, encrypted
FROM   dba_tablespaces
WHERE  encrypted = 'YES'
AND    status   != 'ONLINE';
-- Should return no rows

14.7 — Verify Encrypted Columns

set linesize 200
set pagesize 100
col owner          for a20
col table_name     for a25
col column_name    for a25
col encryption_alg for a20
col salt           for a5

SELECT owner, table_name, column_name,
       encryption_alg, salt
FROM   dba_encrypted_columns
ORDER BY owner, table_name, column_name;

14.8 — Test Wallet Auto-Open After Database Restart

📝 This is the most important post-configuration check. Restart the database and verify the wallet auto-opens without any manual intervention.

sqlplus / as sysdba

-- Bounce the database
SHUTDOWN IMMEDIATE;
STARTUP;

-- Immediately check wallet -- should be auto-open
SELECT status, wallet_type FROM v$encryption_wallet;
-- Must show: STATUS=OPEN, WALLET_TYPE=AUTOLOGIN

-- Confirm encrypted data is accessible
SELECT COUNT(*) FROM hr.employees;
-- Must succeed without any ORA-28365 error

14.9 — Check Alert Log for TDE Errors

# Scan alert log for TDE-related messages after enabling TDE
tail -300 /u01/app/oracle/diag/rdbms/orcl/ORCL/trace/alert_ORCL.log \
     | grep -E "ORA-|TDE|wallet|keystore|encrypt|ADMINISTER"

# Convention B
tail -300 /oracle/diag/rdbms/orcl/ORCL/trace/alert_ORCL.log \
     | grep -E "ORA-|TDE|wallet|keystore|encrypt|ADMINISTER"

Common TDE-related errors to watch for:

14.10 — Verify Wallet Backup Exists and Is Current

# Confirm wallet backup exists and is recent
ls -lh /u01/app/oracle/admin/ORCL/wallet_backup/

# Check remote backup location
ssh oracle@backupserver "ls -lh /secure/wallet_backups/ | tail -5"

15. Quick Reference Card

This SOP covers everything you need to configure Oracle TDE on Linux without referring to any other source. Always backup the wallet separately from the database, always test auto-open after configuration by bouncing the database, always copy the wallet to standby before starting MRP in a Data Guard environment, and always keep the wallet password stored securely in your organization’s password vault — losing the wallet password with no backup means permanent and unrecoverable data loss.