Learn how to set up comprehensive monitoring with Checkmk, starting with the monitoring of databases like PostgreSQL, Microsoft SQL Server, and IBM DB2, and other services. This step-by-step guide covers everything you need to build a robust monitoring infrastructure.
Checkmk is a powerful, open-source monitoring solution that provides: ✅ End-to-end visibility across servers, applications, and databases ✅ Automatic service discovery to reduce manual configuration ✅ Performance metrics and historical data for trend analysis ✅ Flexible alerting with notifications via email, Slack, PagerDuty, and more ✅ Custom checks for monitoring any service or application ✅ Scalability from small environments to enterprise deployments
Whether you’re monitoring a single server or a complex infrastructure with hundreds of services, Checkmk provides the tools you need to stay on top of your systems.
Prerequisites
You should already have a running Checkmk server. The installation steps are described in this post (Step1).
Adding a Monitored Host to Checkmk
1.1 Install the Checkmk Agent on the Target Host
Linux (RHEL/CentOS/OL)
# run as root
# install the checkmk agent
wget lin3.fritz.box/mysite/check_mk/agents/check-mk-agent-2.5.0p10-1.noarch.rpm
dnf -y install ./check-mk-agent-2.5.0p10-1.noarch.rpm
Linux (Ubuntu/Debian)
# run as root
wget lin3.fritz.box/mysite/check_mk/agents/check-mk-agent_2.5.0p10-1_all.deb
apt -y install ./check-mk-agent_2.5.0p10-1_all.deb
Windows
Run the following in a command prompt to allow ICMP (ping) to the host:
Enter the Host name and click “Save & run service discovery”
Then Click “Accept all” to enable all discovered services
Click on Changes => Activate pending changes to perform the configuration changes
For the next step we need a agent registration password. Let’s create one in the GUI:
Go to Setup => Users => Edit the agent_registration user
Click on Create random secret and copy secret to clipboard and Save.
Activate the pending Changes: Changes => Activate pending Changes
The one time password is now saved to the clipboard (it is needed in the next step)
1.2 Configure TLS Encryption
Run this command on the monitored host to enable TLS:
On Linux:
# run as root
cmk-agent-ctl register --server lin3.fritz.box --site mysite --hostname lin6.fritz.box --user agent_registration --trust-cert
# enter the password from the previous step
C:\Windows\system32>C:\ProgramData\checkmk\agent\bin\cmk-agent-ctl.exe register --server lin3.fritz.box --site mysite --hostname win3.fritz.box --user agent_registration --trust-cert
Password for Checkmk user 'agent_registration':
>
Registration complete.
C:\Windows\system32>
Monitoring PostgreSQL Databases
The PostgreSQL database has been installed as documented in this post.
Install the PostgreSQL Plugin
On the Target Host (Linux)
# run as root
dnf -y install python3
wget -P /usr/lib/check_mk_agent/plugins lin3.fritz.box/mysite/check_mk/agents/plugins/mk_postgres.py
chmod +x /usr/lib/check_mk_agent/plugins/mk_postgres.py
Discover PostgreSQL Services in Checkmk
Perform the following steps to find the new PostgreSQL Checkmk services:
In the Host menu of the monitored host start: Run service discovery then Accept All
Activate the pending Changes: Changes => Activate pending Changes
After these steps the PostgreSQL Services are discovered, and after some time they all have been checked:
Monitoring Microsoft SQL Server
The SQL Server has been installed as documented in this post.
Install the SQL Server Plugin
On the monitored host download the Checkmk SQL Server plugin from the Checkmk server to %ProgramData%\checkmk\agent\plugins . Next create the config file with the following content:
Perform the following steps to find the new Db2 Checkmk services:
In the Host menu of the monitored host start: Run service discovery then Accept All
Activate the pending Changes: Changes => Activate pending Changes
After these steps the Db2 Services are discovered, and after some time they all have been checked:
Conclusion
Setting up comprehensive monitoring with Checkmk gives you end-to-end visibility across your entire infrastructure. By following these steps, you can:
✅ Monitor hosts with secure TLS connections ✅ Track database performance for PostgreSQL, SQL Server, and DB2 ✅ Get alerts before issues impact your users ✅ Analyze trends with historical data and dashboards
Oracle TDE Transparent Data Encryption Database Security
In today’s data-driven world, protecting sensitive information at rest is not just a best practice—it’s a compliance requirement. Oracle Transparent Data Encryption (TDE) provides a robust solution for encrypting database files, ensuring that even if storage media is stolen, your data remains secure and inaccessible to unauthorized users.
This comprehensive guide walks you through Oracle TDE concepts, implementation, and maintenance specifically for Oracle AI Database 26ai running on a VirtualBox VM, as described in this installation guide. We’ll cover everything from foundational concepts to hands-on configuration and ongoing maintenance.
Oracle Transparent Data Encryption (TDE) is a data-at-rest encryption feature that protects sensitive data stored in database files. Unlike application-level encryption, TDE operates at the database layer, making it transparent to applications—data is automatically encrypted when written to disk and decrypted when accessed by authorized users or applications.
TDE is included with Oracle Advanced Security, which is a licensed option for Oracle Database Enterprise Edition. It addresses the critical security gap where database authentication and authorization mechanisms protect data access but not the physical data files where the data is stored.
Key Benefits of Oracle TDE
Compliance Ready: Helps meet regulatory requirements like PCI-DSS, HIPAA, GDPR, and SOX
Transparent Operation: No application changes required—encryption/decryption happens automatically
Granular Control: Encrypt entire tablespaces, specific tables, or individual columns
Performance Optimized: Minimal performance overhead with hardware acceleration support
Comprehensive Protection: Secures data files, redo logs, backups, and temporary files
Key Management: Flexible key storage options including Oracle Wallet, Hardware Security Modules (HSM), and Oracle Key Vault
How Oracle TDE Works
Oracle TDE uses a hierarchical key management system to efficiently encrypt and decrypt data:
TDE Key Hierarchy
Master Encryption Key: The root key stored in an external keystore (Oracle Wallet or HSM). This key encrypts all other keys in the hierarchy.
Tablespace Encryption Keys: Unique keys for each encrypted tablespace, encrypted by the master key
Table/Column Keys: Keys for specific encrypted columns or tables, also encrypted by the master key
Data Encryption: Actual data is encrypted using standard algorithms (AES-128, AES-192, or AES-256)
Important for Oracle AI Database 26ai: Starting with 26ai, AES256 is the new default encryption algorithm for TDE, providing stronger security and aligning with quantum-resistant practices. This means that when you upgrade from previous versions, Oracle will automatically perform rekey operations to upgrade existing encrypted data to AES256.
When a user or application accesses encrypted data, Oracle TDE:
Opens the wallet/keystore containing the master encryption key
Uses the master key to decrypt the appropriate tablespace or column key
Uses the decrypted key to decrypt the actual data
Returns the cleartext data to the authorized user/application
Key TDE Components
1. TDE Wallet (Keystore)
The TDE Wallet is a PKCS#12 container that stores the master encryption key and other encryption keys. It’s essentially a password-protected file that resides on the database server’s filesystem.
Wallet Types:
Password-based Wallet (ewallet.p12): The primary wallet file that requires a password to open
Auto-login Wallet (cwallet.sso): Allows automatic wallet opening when the database starts (derived from the password wallet)
Hardware Security Module (HSM): External hardware device for enhanced security
Oracle Key Vault: Centralized key management solution for enterprise environments
⚠️ Critical Note for upgrading to 26ai: If TDE is enabled but WALLET_ROOT is not configured, you will be blocked from upgrading to 26ai. Always ensure proper wallet configuration before upgrading.
2. Master Encryption Key
The TDE Master Encryption Key is the foundation of the TDE key hierarchy. It’s generated when you first set up TDE and is used to encrypt all other encryption keys in your database.
Best Practice: Always create a backup of your master encryption key when you create it, as losing this key means losing access to all encrypted data.
3. Encryption Algorithms
Oracle TDE supports several encryption algorithms:
Algorithm
Key Size
Default in 26ai
Notes
AES
128, 192, or 256 bits
✅ AES256
FIPS 140-2 compliant
3DES
168 bits
❌ No
Legacy, not recommended
Prerequisites for Our Environment
This guide assumes you have completed the Oracle AI Database 26ai installation on a VirtualBox VM as described in this installation guide. Here’s what you should have:
Environment Setup
Operating System: Oracle Linux 9.5
Virtualization: VirtualBox VM
Database: Oracle AI Database 26ai Enterprise Edition (23.26.0.0.0)
Storage: ASM with DATA (50GB x2) and FRA (25GB x3) diskgroups
Oracle Advanced Security Option: TDE requires this licensed option (included in Enterprise Edition)
Database Compatibility:COMPATIBLE parameter must be set to at least 11.1.0.0
File System Permissions: Oracle user must have read/write access to the wallet directory
Backup Strategy: Ensure you have a backup strategy for both database and wallet files
Note: The installation guide already sets up the necessary infrastructure, including ASM diskgroups and proper user permissions, making it an ideal foundation for implementing TDE.
Step-by-Step TDE Implementation
Now let’s implement TDE on your Oracle AI Database 26ai installation. We’ll follow the official Oracle documentation (Oracle AI Database Transparent Data Encryption Guide) while adapting the steps for our VirtualBox VM environment.
Step 1: Set Initialization Parameters
The first step is to configure the database parameters that tell Oracle where to find the TDE wallet and how to manage encryption.
1.1 Create Wallet Directory
Connect to your VM as the oracle user and create the wallet directory. For our environment, we’ll use /u01/app/oracle/wallet:
# run as the oracle user
# create wallet directory
. ora26.env
mkdir -p /u01/app/oracle/wallet/${ORACLE_SID}
# set proper permissions (critical for security)
chmod -R 700 /u01/app/oracle/wallet
chown -R oracle:oinstall /u01/app/oracle/wallet
⚠️ Security Note: The wallet directory must have 700 permissions (read/write/execute for owner only). Never store wallet files in a location accessible to other users.
1.2 Configure Database Parameters
Set the TDE related parameters in your database. For Oracle AI Database 26ai, the WALLET_ROOT parameter is mandatory:
# Connect to your CDB as SYSDBA (handled by / as sysdba)
. ora26.env
sqlplus -S / as sysdba <<EOF
-- Set WALLET_ROOT to point to your wallet directory
-- This is REQUIRED for Oracle 26ai
ALTER SYSTEM SET WALLET_ROOT='/u01/app/oracle/wallet/${ORACLE_SID}' SCOPE = SPFILE;
-- Enable automatic tablespace encryption
-- This ensures new tablespaces are encrypted by default
ALTER SYSTEM SET TABLESPACE_ENCRYPTION='AUTO_ENABLE' SCOPE=SPFILE;
-- Optional: Set the default encryption algorithm
-- In 26ai, AES256 is the default, but you can explicitly set it
-- ALTER SYSTEM SET "TABLESPACE_ENCRYPTION_DEFAULT_ALGORITHM"='AES256' SCOPE=BOTH SID='*';
-- restart the database
shutdown immediate
startup
-- set the TDE_CONFIGURATION parameter
ALTER SYSTEM SET TDE_CONFIGURATION="KEYSTORE_CONFIGURATION=FILE" SCOPE=BOTH SID = '*';
-- Verify the parameters
show parameter wallet
show parameter encryption
show parameter tde
EXIT;
EOF
Sample Output (click to expand):
[oracle@lin1 ~]$ sqlplus -S / as sysdba <<EOF
>
>
> -- Set WALLET_ROOT to point to your wallet directory
> -- This is REQUIRED for Oracle 26ai
> ALTER SYSTEM SET WALLET_ROOT='/u01/app/oracle/wallet/${ORACLE_SID}' SCOPE = SPFILE;
>
> -- Enable automatic tablespace encryption
> -- This ensures new tablespaces are encrypted by default
> ALTER SYSTEM SET TABLESPACE_ENCRYPTION='AUTO_ENABLE' SCOPE=SPFILE;
>
> -- Optional: Set the default encryption algorithm
> -- In 26ai, AES256 is the default, but you can explicitly set it
> -- ALTER SYSTEM SET "TABLESPACE_ENCRYPTION_DEFAULT_ALGORITHM"='AES256' SCOPE=BOTH SID='*';
>
> -- restart the database
> shutdown immediate
> startup
>
> -- set the TDE_CONFIGURATION parameter
> ALTER SYSTEM SET TDE_CONFIGURATION="KEYSTORE_CONFIGURATION=FILE" SCOPE=BOTH SID = '*';
>
> -- Verify the parameters
> show parameter wallet
> show parameter encryption
> show parameter tde
>
> EXIT;
> EOF
System altered.
System altered.
Database closed.
Database dismounted.
ORACLE instance shut down.
ORACLE instance started.
Total System Global Area 4476610824 bytes
Fixed Size 5015816 bytes
Variable Size 855638016 bytes
Database Buffers 3607101440 bytes
Redo Buffers 8855552 bytes
Database mounted.
Database opened.
System altered.
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
ssl_wallet string
wallet_root string /u01/app/oracle/wallet/orcl
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
tablespace_encryption string AUTO_ENABLE
tablespace_encryption_default_algori string AES256
thm
tablespace_encryption_default_cipher string XTS
_mode
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
one_step_plugin_for_pdb_with_tde boolean FALSE
tde_configuration string KEYSTORE_CONFIGURATION=FILE
tde_key_cache boolean FALSE
[oracle@lin1 ~]$
Important for upgrading to 26ai: The WALLET_ROOT parameter was introduced in Oracle 21c and is required for Oracle 26ai. If this parameter is not set and TDE is enabled, you cannot upgrade to 26ai.
Note for ASM: If you’re using ASM (as configured in the installation guide), the wallet can also be stored in ASM. However, for simplicity and to match common practices, we’re using the filesystem approach here.
Step 2: Create the TDE Wallet (Keystore)
Now we’ll create the actual wallet that will store our encryption keys.
2.1 Create the Wallet
Use the ADMINISTER KEY MANAGEMENT command to create the wallet:
# run as the oracle user on the CDB
sql -S / as sysdba <<EOF
-- Create the wallet with a strong password
-- Replace 'MySecureWalletPass123!' with your own strong password
ADMINISTER KEY MANAGEMENT CREATE KEYSTORE IDENTIFIED BY "MySecureWalletPass123!";
-- Verify the wallet was created
SELECT * FROM V\$ENCRYPTION_WALLET;
EXIT;
EOF
Sample Output (click to expand):
[oracle@lin1 ~]$ sql -S / as sysdba <<EOF
> -- Create the wallet with a strong password
> -- Replace 'MySecureWalletPass123!' with your own strong password
> ADMINISTER KEY MANAGEMENT CREATE KEYSTORE IDENTIFIED BY "MySecureWalletPass123!";
>
> -- Verify the wallet was created
> SELECT * FROM V\$ENCRYPTION_WALLET;
>
> EXIT;
> EOF
Key MANAGEMENT succeeded.
WRL_TYPE WRL_PARAMETER STATUS WALLET_TYPE WALLET_ORDER KEYSTORE_MODE FULLY_BACKED_UP CON_ID
________ ________________________________ ______ ___________ ____________ _____________ _______________ ______
FILE /u01/app/oracle/wallet/orcl/tde/ CLOSED UNKNOWN SINGLE NONE UNDEFINED 1
FILE CLOSED UNKNOWN SINGLE UNITED UNDEFINED 2
FILE CLOSED UNKNOWN SINGLE UNITED UNDEFINED 3
[oracle@lin1 ~]$
💡 Password Requirements:
Minimum 8 characters
At least 1 uppercase letter
At least 1 lowercase letter
At least 1 digit
At least 1 special character
Store this password securely – you’ll need it for wallet operations
2.2 Create Auto-Login Wallet (Optional but Recommended)
For production environments, create an auto-login wallet so the database can open the wallet automatically when it starts:
# run as the oracle user on the CDB
sql -S / as sysdba <<EOF
-- First, open the wallet with the password
ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY "MySecureWalletPass123!" CONTAINER=ALL;
-- Create auto-login wallet
ADMINISTER KEY MANAGEMENT CREATE AUTO_LOGIN KEYSTORE FROM KEYSTORE IDENTIFIED BY "MySecureWalletPass123!";
-- Verify both wallets exist
!ls -la /u01/app/oracle/wallet/orcl/tde
EXIT;
EOF
Sample Output (click to expand):
[oracle@lin1 ~]$ # run as the oracle user on the CDB
[oracle@lin1 ~]$ sql -S / as sysdba <<EOF
> -- First, open the wallet with the password
> ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY "MySecureWalletPass123!";
>
> -- Create auto-login wallet
> ADMINISTER KEY MANAGEMENT CREATE AUTO_LOGIN KEYSTORE FROM KEYSTORE IDENTIFIED BY "MySecureWalletPass123!";
>
> -- Verify both wallets exist
> !ls -la /u01/app/oracle/wallet/orcl/tde
>
> EXIT;
> EOF
Key MANAGEMENT succeeded.
Key MANAGEMENT succeeded.
total 16
drwxr-x--- 2 oracle asmadmin 4096 Aug 5 16:11 .
drwx------ 3 oracle oinstall 4096 Aug 5 16:10 ..
-rw------- 1 oracle asmadmin 2608 Aug 5 16:11 cwallet.sso
-rw------- 1 oracle asmadmin 2563 Aug 5 16:10 ewallet.p12
[oracle@lin1 ~]$
ewallet.p12 is the Password-based wallet cwallet.sso is the Auto-login wallet
⚠️ Important Security Considerations:
Do NOT delete the password-based wallet (ewallet.p12) – you need it to regenerate or rekey the TDE master encryption key in the future
The auto-login wallet (cwallet.sso) is derived from the password wallet and allows automatic opening
Both files must be backed up together
Store wallet backups in a secure, separate location from the database backups
Step 3: Set the TDE Master Encryption Key
Now we’ll create the master encryption key that will protect all other encryption keys in your database.
3.1 Create the Master Key
Execute the following command to create the master encryption key in the wallet:
# run as the oracle user on the CDB
sql -S / as sysdba <<EOF
-- Ensure the wallet is open
ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY "MySecureWalletPass123!" CONTAINER=ALL;
-- Create the master encryption key with backup
-- The WITH BACKUP clause creates a backup of the key
ADMINISTER KEY MANAGEMENT SET KEY IDENTIFIED BY "MySecureWalletPass123!"
WITH BACKUP USING 'tde_master_key_backup' CONTAINER=ALL;
-- Verify the master key was created
SELECT * FROM V\$ENCRYPTION_WALLET;
EXIT;
EOF
Sample Output (click to expand):
[oracle@lin1 ~]$ # run as the oracle user on the CDB
[oracle@lin1 ~]$ sql -S / as sysdba <<EOF
> -- Ensure the wallet is open
> ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY "MySecureWalletPass123!" CONTAINER=ALL;
>
> -- Create the master encryption key with backup
> -- The WITH BACKUP clause creates a backup of the key
> ADMINISTER KEY MANAGEMENT SET KEY IDENTIFIED BY "MySecureWalletPass123!"
> WITH BACKUP USING 'tde_master_key_backup' CONTAINER=ALL;
>
> -- Verify the master key was created
> SELECT * FROM V\$ENCRYPTION_WALLET;
>
> EXIT;
> EOF
Error starting at line : 2 in command -
ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY "MySecureWalletPass123!" CONTAINER=ALL
Error report -
ORA-28354: Encryption wallet, auto login wallet, or HSM is already open.
Help: https://docs.oracle.com/error-help/db/ora-28354/
28354. 0000 - "Encryption wallet, auto login wallet, or HSM is already open."
*Cause: Either the encryption wallet, auto login wallet, or hardware security module (HSM) keystore was already open.
*Action: None.
Key MANAGEMENT succeeded.
WRL_TYPE WRL_PARAMETER STATUS WALLET_TYPE WALLET_ORDER KEYSTORE_MODE FULLY_BACKED_UP CON_ID
________ ________________________________ ______ ___________ ____________ _____________ _______________ ______
FILE /u01/app/oracle/wallet/orcl/tde/ OPEN PASSWORD SINGLE NONE NO 1
FILE OPEN PASSWORD SINGLE UNITED NO 2
FILE OPEN PASSWORD SINGLE UNITED NO 3
[oracle@lin1 ~]$
💡 What just happened:
The WITH BACKUP USING clause creates a backup of the master key with the specified label. This backup is stored in the wallet and can be used for recovery if needed.
3.2 Verify Master Key Creation
Check that the master key exists and is properly stored:
# run as the oracle user on the CDB
sql -S / as sysdba <<EOF
SELECT
b.name AS container_name,
a.wrl_type,
a.wrl_parameter,
a.status,
a.wallet_type
FROM v\$encryption_wallet a, v\$containers b
WHERE a.con_id = b.con_id
ORDER BY a.con_id;
EXIT;
EOF
Output (click to expand):
[oracle@lin1 ~]$ # run as the oracle user on the CDB
[oracle@lin1 ~]$ sql -S / as sysdba <<EOF
> SELECT
> b.name AS container_name,
> a.wrl_type,
> a.wrl_parameter,
> a.status,
> a.wallet_type
> FROM v\$encryption_wallet a, v\$containers b
> WHERE a.con_id = b.con_id
> ORDER BY a.con_id;
> EXIT;
> EOF
CONTAINER_NAME WRL_TYPE WRL_PARAMETER STATUS WALLET_TYPE
______________ ________ ________________________________ ______ ___________
CDB$ROOT FILE /u01/app/oracle/wallet/orcl/tde/ OPEN PASSWORD
PDB$SEED FILE OPEN PASSWORD
PDB1 FILE OPEN PASSWORD
[oracle@lin1 ~]$
Step 4: Restart the Database
For the WALLET_ROOT and TABLESPACE_ENCRYPTION parameters to take full effect, restart your database:
# run as the oracle user on the CDB
sql -S / as sysdba <<EOF
-- Shutdown the database
SHUTDOWN IMMEDIATE;
-- Startup the database
STARTUP;
-- Verify parameters are set
SELECT name, value FROM v\$parameter
WHERE name IN ('wallet_root', 'tablespace_encryption');
EXIT;
EOF
Output (click)
Database closed.
Database dismounted.
ORACLE instance shut down.
ORACLE instance started.
Total System Global Area 4476610824 bytes
Fixed Size 5015816 bytes
Variable Size 855638016 bytes
Database Buffers 3607101440 bytes
Redo Buffers 8855552 bytes
Database mounted.
Database opened.
NAME VALUE
_____________________ ___________________________
tablespace_encryption AUTO_ENABLE
wallet_root /u01/app/oracle/wallet/orcl
Important: After restart, the auto-login wallet should open automatically. Verify this with:
SELECT status FROM V$ENCRYPTION_WALLET WHERE wrl_type = 'FILE';
Expected output: OPEN
Output (click):
SQL> SELECT status FROM V$ENCRYPTION_WALLET WHERE wrl_type = 'FILE';
STATUS
------------------------------
OPEN
OPEN
OPEN
SQL>
Step 5: Encrypt Tablespaces
Now that TDE is configured, let’s encrypt some tablespaces. With TABLESPACE_ENCRYPTION=AUTO_ENABLE, new tablespaces will be encrypted automatically, but we need to handle existing ones.
5.1 Create a New Encrypted Tablespace
Create a new tablespace that will be encrypted by default:
# run as the oracle user on PDB1
sql -S / as sysdba <<EOF
-- Create an encrypted tablespace for sensitive data
alter session set container=pdb1;
CREATE TABLESPACE secure_data
DATAFILE '+DATA' SIZE 40M
AUTOEXTEND ON
ENCRYPTION USING 'AES256' ENCRYPT;
-- Verify the tablespace is encrypted
SELECT t.name AS tablespace_name,
e.ts#,
e.encryptionalg,
e.ciphermode,
e.encryptedts
FROM v\$encrypted_tablespaces e
JOIN v\$tablespace t ON e.ts# = t.ts#;
EXIT;
EOF
Note: Thanks to TABLESPACE_ENCRYPTION=AUTO_ENABLE, you could also create the tablespace without the ENCRYPTION clause, and it would still be encrypted:
-- This will also be encrypted due to AUTO_ENABLE
CREATE TABLESPACE auto_secure_data
DATAFILE '+DATA' SIZE 20M AUTOEXTEND ON;
5.2 Encrypt Existing Tablespaces (Online Method)
For existing tablespaces, Oracle 26ai introduces online tablespace encryption, which allows you to encrypt tablespaces without taking them offline. This is a significant improvement over previous versions.
# run as the oracle user on PDB1
sql -S / as sysdba <<EOF
alter session set container=pdb1;
-- create unencrypted data in the USERS tablespace
create table t tablespace users as select a2.* from all_objects a1, all_objects a2 where rownum<=1e6 order by a1.object_id;
-- Start online encryption for an existing tablespace
-- This example encrypts the USERS tablespace
ALTER TABLESPACE users
ENCRYPTION ONLINE USING 'AES256' ENCRYPT;
-- Check the encryption status
SELECT t.name AS tablespace_name,
e.ts#,
e.encryptionalg,
e.ciphermode,
e.encryptedts
FROM v\$encrypted_tablespaces e
JOIN v\$tablespace t ON e.ts# = t.ts#;
-- The status will show 'ENCRYPTING' initially
SELECT t.name AS tablespace_name,
e.ts#,
e.encryptionalg,
e.ciphermode,
e.encryptedts,
e.status,
e.blocks_encrypted, -- number of new blocks that have been encrypted
e.blocks_decrypted -- number of encrypted blocks that have been decrypted
FROM v\$encrypted_tablespaces e
JOIN v\$tablespace t ON e.ts# = t.ts#;
EXIT;
EOF
Sample Output (click to expand):
[oracle@lin1 ~]$ sql -S / as sysdba <<EOF
> alter session set container=pdb1;
> -- create unencrypted data in the USERS tablespace
> create table t tablespace users as select a2.* from all_objects a1, all_objects a2 where rownum<=1e6 order by a1.object_id;
> -- Start online encryption for an existing tablespace
> -- This example encrypts the USERS tablespace
> ALTER TABLESPACE users
> ENCRYPTION ONLINE USING 'AES256' ENCRYPT;
>
> -- Check the encryption status
> SELECT t.name AS tablespace_name,
> e.ts#,
> e.encryptionalg,
> e.ciphermode,
> e.encryptedts
> FROM v\$encrypted_tablespaces e
> JOIN v\$tablespace t ON e.ts# = t.ts#;
>
> -- The status will show 'ENCRYPTING' initially
> SELECT t.name AS tablespace_name,
> e.ts#,
> e.encryptionalg,
> e.ciphermode,
> e.encryptedts,
> e.status,
> e.blocks_encrypted, -- number of new blocks that have been encrypted
> e.blocks_decrypted -- number of encrypted blocks that have been decrypted
> FROM v\$encrypted_tablespaces e
> JOIN v\$tablespace t ON e.ts# = t.ts#;
>
> EXIT;
> EOF
Session altered.
Table T created.
TABLESPACE USERS altered.
TABLESPACE_NAME TS# ENCRYPTIONALG CIPHERMODE ENCRYPTEDTS
________________ ___ _____________ __________ ___________
USERS 6 AES256 XTS YES
SECURE_DATA 7 AES256 XTS YES
AUTO_SECURE_DATA 8 AES256 XTS YES
TABLESPACE_NAME TS# ENCRYPTIONALG CIPHERMODE ENCRYPTEDTS STATUS BLOCKS_ENCRYPTED BLOCKS_DECRYPTED
________________ ___ _____________ __________ ___________ ______ ________________ ________________
USERS 6 AES256 XTS YES NORMAL 0 0
SECURE_DATA 7 AES256 XTS YES NORMAL 46 0
AUTO_SECURE_DATA 8 AES256 XTS YES NORMAL 46 0
[oracle@lin1 ~]$
Online Encryption Process:
Oracle starts encrypting data in the background
Existing data is encrypted as it’s accessed (lazy encryption)
New data written to the tablespace is encrypted immediately
The tablespace remains fully accessible during the process
Use ALTER TABLESPACE ... ENCRYPTION FINISH; to complete the process if needed
Note for Oracle 26ai: Online tablespace encryption uses tweakable block ciphertext stealing (XTS) instead of the older Cipher Feedback (CFB) mode, providing stronger security.
5.3 Encrypt System Tablespaces (Recommended for Production)
For maximum security, consider encrypting the SYSTEM and SYSAUX tablespaces as well:
# run as the oracle user on PDB1
sql -S / as sysdba <<EOF
alter session set container=pdb1;
-- Encrypt SYSTEM tablespace (requires database in restricted mode)
ALTER SYSTEM ENABLE RESTRICTED SESSION;
-- Disconnect all non-SYS sessions (if needed)
-- ALTER SYSTEM DISCONNECT SESSION 'sid,serial#' POST_TRANSACTION;
-- Encrypt SYSTEM and SYSAUX
ALTER TABLESPACE SYSTEM ENCRYPTION ONLINE USING 'AES256' ENCRYPT;
ALTER TABLESPACE SYSAUX ENCRYPTION ONLINE USING 'AES256' ENCRYPT;
-- Exit restricted mode
ALTER SYSTEM DISABLE RESTRICTED SESSION;
EXIT;
EOF
Sample Output (click to expand):
[oracle@lin1 ~]$ # run as the oracle user on PDB1
[oracle@lin1 ~]$ sql -S / as sysdba <<EOF
> alter session set container=pdb1;
> -- Encrypt SYSTEM tablespace (requires database in restricted mode)
> ALTER SYSTEM ENABLE RESTRICTED SESSION;
>
> -- Disconnect all non-SYS sessions (if needed)
> -- ALTER SYSTEM DISCONNECT SESSION 'sid,serial#' POST_TRANSACTION;
>
> -- Encrypt SYSTEM and SYSAUX
> ALTER TABLESPACE SYSTEM ENCRYPTION ONLINE USING 'AES256' ENCRYPT;
> ALTER TABLESPACE SYSAUX ENCRYPTION ONLINE USING 'AES256' ENCRYPT;
>
> -- Exit restricted mode
> ALTER SYSTEM DISABLE RESTRICTED SESSION;
>
> EXIT;
> EOF
Session altered.
System altered.
TABLESPACE SYSTEM altered.
TABLESPACE SYSAUX altered.
System altered.
[oracle@lin1 ~]$
⚠️ Warning: Encrypting SYSTEM and SYSAUX tablespaces can impact database performance and should be done during a maintenance window. Test this in a non-production environment first.
Step 6: Encrypt Specific Columns
In addition to tablespace-level encryption, TDE supports column-level encryption for more granular control.
6.1 Create a Table with Encrypted Columns
Let’s create a table with specific encrypted columns for sensitive data:
# run as the oracle user on PDB1
sql -S / as sysdba <<EOF
alter session set container=pdb1;
alter session set current_schema=dbsnmp;
-- Create a table in your encrypted tablespace
CREATE TABLE customers (
customer_id NUMBER PRIMARY KEY,
first_name VARCHAR2(50),
last_name VARCHAR2(50),
email VARCHAR2(100),
ssn VARCHAR2(11) ENCRYPT, -- Social Security Number - encrypted
credit_card VARCHAR2(16) ENCRYPT USING 'AES256', -- Credit card - encrypted with AES256
phone VARCHAR2(15)
) TABLESPACE secure_data;
-- Insert some test data
INSERT INTO customers VALUES (
1, 'John', 'Doe', 'john.doe@example.com',
'123-45-6789', '4111111111111111', '555-123-4567'
);
-- Query the data - it will be automatically decrypted for authorized users
SELECT * FROM customers;
EXIT;
EOF
Output (click to expand):
[oracle@lin1 ~]$ sql -S / as sysdba <<EOF
> alter session set container=pdb1;
> alter session set current_schema=dbsnmp;
> -- Create a table in your encrypted tablespace
> CREATE TABLE customers (
> customer_id NUMBER PRIMARY KEY,
> first_name VARCHAR2(50),
> last_name VARCHAR2(50),
> email VARCHAR2(100),
> ssn VARCHAR2(11) ENCRYPT, -- Social Security Number - encrypted
> credit_card VARCHAR2(16) ENCRYPT USING 'AES256', -- Credit card - encrypted with AES256
> phone VARCHAR2(15)
> ) TABLESPACE secure_data;
>
> -- Insert some test data
> INSERT INTO customers VALUES (
> 1, 'John', 'Doe', 'john.doe@example.com',
> '123-45-6789', '4111111111111111', '555-123-4567'
> );
>
> -- Query the data - it will be automatically decrypted for authorized users
> SELECT * FROM customers;
>
> EXIT;
> EOF
Session altered.
Session altered.
Table CUSTOMERS created.
1 row inserted.
CUSTOMER_ID FIRST_NAME LAST_NAME EMAIL SSN CREDIT_CARD PHONE
___________ __________ _________ ____________________ ___________ ________________ ____________
1 John Doe john.doe@example.com 123-45-6789 4111111111111111 555-123-4567
[oracle@lin1 ~]$
6.2 Encrypt Columns in Existing Tables
For existing tables, you can add encrypted columns or modify existing columns to be encrypted:
# run as the oracle user on PDB1
sql -S / as sysdba <<EOF
alter session set container=pdb1;
alter session set current_schema=dbsnmp;
-- Add an encrypted column to an existing table
ALTER TABLE customers ADD (
bank_account VARCHAR2(20) ENCRYPT
);
-- For existing columns, you need to:
-- 1. Add a new encrypted column
-- 2. Copy data from old column to new column
-- 3. Drop the old column
-- 4. Rename the new column to the old name
ALTER TABLE customers ADD (
new_email VARCHAR2(100) ENCRYPT
);
UPDATE customers SET new_email = email;
ALTER TABLE customers DROP COLUMN email;
ALTER TABLE customers RENAME COLUMN new_email TO email;
EXIT;
EOF
Sample Output (click to expand):
[oracle@lin1 ~]$ # run as the oracle user on PDB1
[oracle@lin1 ~]$ sql -S / as sysdba <<EOF
> alter session set container=pdb1;
> alter session set current_schema=dbsnmp;
> -- Add an encrypted column to an existing table
> ALTER TABLE customers ADD (
> bank_account VARCHAR2(20) ENCRYPT
> );
>
> -- For existing columns, you need to:
> -- 1. Add a new encrypted column
> -- 2. Copy data from old column to new column
> -- 3. Drop the old column
> -- 4. Rename the new column to the old name
>
> ALTER TABLE customers ADD (
> new_email VARCHAR2(100) ENCRYPT
> );
>
> UPDATE customers SET new_email = email;
>
> ALTER TABLE customers DROP COLUMN email;
>
> ALTER TABLE customers RENAME COLUMN new_email TO email;
>
> EXIT;
> EOF
Session altered.
Session altered.
Table CUSTOMERS altered.
Table CUSTOMERS altered.
1 row updated.
Table CUSTOMERS altered.
Table CUSTOMERS altered.
[oracle@lin1 ~]$
⚠️ Important Limitations for Column Encryption:
You cannot encrypt columns with data types that have size restrictions (e.g., LONG, LONG RAW)
Encrypted columns cannot be used in certain operations like indexing (unless using virtual columns or using the column NO SALT option)
Column encryption has a small storage overhead (approximately 16-32 bytes per encrypted value)
Not all SQL functions work with encrypted columns
TDE Maintenance and Best Practices
Implementing TDE is just the beginning. Proper maintenance ensures your encrypted data remains secure and accessible. Here are the essential maintenance tasks for your Oracle AI Database 26ai TDE implementation.
1. Wallet Management
1.1 Wallet Backup and Recovery
The wallet file is the most critical component of TDE. Without the wallet and its password, you cannot access your encrypted data, even with full database backups. Losing the wallet means permanent data loss.
Backup the Wallet
Create regular backups of your wallet files:
# As oracle user
su - oracle
# Create a backup directory
mkdir -p /u01/app/oracle/wallet_backups/${ORACLE_SID}
# Copy wallet files to backup location
cp /u01/app/oracle/wallet/${ORACLE_SID}/tde/*.p12 /u01/app/oracle/wallet_backups/${ORACLE_SID}
cp /u01/app/oracle/wallet/${ORACLE_SID}/tde/*.sso /u01/app/oracle/wallet_backups/${ORACLE_SID}
# Set proper permissions on backup
chmod -R 700 /u01/app/oracle/wallet_backups
chown -R oracle:oinstall /u01/app/oracle/wallet_backups
# Create a tarball for off-site backup
cd /u01/app/oracle/wallet_backups
tar czvf wallet_backup_${ORACLE_SID}_$(date +%Y%m%d).tar.gz ${ORACLE_SID}
# Securely transfer to backup server
scp wallet_backup_${ORACLE_SID}_$(date +%Y%m%d).tar.gz backup_server:/backups/oracle/
Wallet Backup Best Practices:
Backup Frequency: Backup the wallet every time you make changes (new keys, rekey operations)
Separate Storage: Store wallet backups in a different location than database backups
Secure Transfer: Use encrypted channels (SCP, SFTP) for transferring wallet backups
Access Control: Restrict access to wallet backups to authorized personnel only
Documentation: Document the wallet password storage procedure (use a secure password manager)
Test Restores: Regularly test wallet restore procedures
Recover from Wallet Loss
If you lose your wallet, you can restore from backup:
-- First, ensure the wallet directory exists
!mkdir -p /u01/app/oracle/wallet/${ORACLE_SID}/tde
-- Copy backup files to wallet directory
!cp /u01/app/oracle/wallet_backups/${ORACLE_SID}/ewallet.p12 /u01/app/oracle/wallet/${ORACLE_SID}/tde/
!cp /u01/app/oracle/wallet_backups/${ORACLE_SID}/cwallet.sso /u01/app/oracle/wallet/${ORACLE_SID}/tde/
-- Set proper permissions
!chmod 700 /u01/app/oracle/wallet/${ORACLE_SID}/tde/
!chown oracle:oinstall /u01/app/oracle/wallet/${ORACLE_SID}/tde/*
-- Open the wallet (if not using auto-login)
ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN
IDENTIFIED BY "MySecureWalletPass123!";
-- Verify wallet status
SELECT * FROM V$ENCRYPTION_WALLET;
⚠️ Critical: If you don’t have a wallet backup and forget the password, your encrypted data is permanently inaccessible. There is no backdoor or recovery mechanism.
2. Key Management
Regular key rotation is a security best practice. Oracle TDE makes this process relatively straightforward.
2.1 Rotate the TDE Master Key
To rotate the master encryption key:
# run as the oracle user on the CDB
sql -S / as sysdba <<EOF
-- Verify the current key
select creator_pdbname, algorithm, creation_time from v\$encryption_keys;
-- next two steps needed to avoid: ORA-28354: Encryption wallet, auto login wallet, or HSM is already open.
-- Close the auto login keystore
ADMINISTER KEY MANAGEMENT SET KEYSTORE CLOSE CONTAINER = ALL;
-- Open the password-based keystore
ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY "MySecureWalletPass123!" CONTAINER=ALL;
-- Create a new master key (this automatically re-encrypts all dependent keys)
ADMINISTER KEY MANAGEMENT SET KEY IDENTIFIED BY "MySecureWalletPass123!"
WITH BACKUP USING 'tde_master_key_rotation_2026' CONTAINER=ALL;
-- Verify the new key
select creator_pdbname, algorithm, creation_time from v\$encryption_keys;
EXIT;
EOF
Sample Output (click to expand):
[oracle@lin1 ~]$ sql -S / as sysdba <<EOF
> -- Verify the current key
> select creator_pdbname, algorithm, creation_time from v\$encryption_keys;
>
> -- next two steps needed to avoid: ORA-28354: Encryption wallet, auto login wallet, or HSM is already open.
> -- Close the auto login keystore
> ADMINISTER KEY MANAGEMENT SET KEYSTORE CLOSE CONTAINER = ALL;
>
> -- Open the password-based keystore
> ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY "MySecureWalletPass123!" CONTAINER=ALL;
>
> -- Create a new master key (this automatically re-encrypts all dependent keys)
> ADMINISTER KEY MANAGEMENT SET KEY IDENTIFIED BY "MySecureWalletPass123!"
> WITH BACKUP USING 'tde_master_key_rotation_2026' CONTAINER=ALL;
>
> -- Verify the new key
> select creator_pdbname, algorithm, creation_time from v\$encryption_keys;
>
> EXIT;
> EOF
CREATOR_PDBNAME ALGORITHM CREATION_TIME
_______________ _________ ___________________________________
CDB$ROOT AES256 05-AUG-26 10.35.17.381149000 AM GMT
PDB1 AES256 05-AUG-26 10.35.17.415059000 AM GMT
Error starting at line : 6 in command -
ADMINISTER KEY MANAGEMENT SET KEYSTORE CLOSE CONTAINER = ALL
Error report -
ORA-28389: Cannot close auto login keystore.
Help: https://docs.oracle.com/error-help/db/ora-28389/
28389. 00000 - "Cannot close auto login keystore."
*Cause: Auto login keystore could not be closed because the keystore type
that is currently open is of password type and not auto-login.
*Action: Close the keystore with a password.
Error starting at line : 9 in command -
ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY "MySecureWalletPass123!" CONTAINER=ALL
Error report -
ORA-28354: Encryption wallet, auto login wallet, or HSM is already open.
Help: https://docs.oracle.com/error-help/db/ora-28354/
28354. 0000 - "Encryption wallet, auto login wallet, or HSM is already open."
*Cause: Either the encryption wallet, auto login wallet, or hardware security module (HSM) keystore was already open.
*Action: None.
Key MANAGEMENT succeeded.
CREATOR_PDBNAME ALGORITHM CREATION_TIME
_______________ _________ ___________________________________
PDB1 AES256 05-AUG-26 11.44.00.331030000 AM GMT
CDB$ROOT AES256 05-AUG-26 11.44.00.286483000 AM GMT
CDB$ROOT AES256 05-AUG-26 10.35.17.381149000 AM GMT
PDB1 AES256 05-AUG-26 10.35.17.415059000 AM GMT
[oracle@lin1 ~]$
💡 What happens during key rotation:
Oracle generates a new master encryption key
All existing keys (tablespace keys, column keys) are re-encrypted with the new master key
The old master key is retained until all data is re-encrypted
Applications continue to work without interruption
2.2 Rekey Tablespace Encryption
To change the encryption key for a specific tablespace:
-- Rekey a tablespace (this creates a new tablespace encryption key)
ALTER TABLESPACE secure_data ENCRYPTION USING 'AES256' REKEY;
-- For online rekey (Oracle 26ai feature)
ALTER TABLESPACE secure_data ENCRYPTION ONLINE USING 'AES256' REKEY;
-- Monitor rekey progress
SELECT t.name AS tablespace_name,
e.ts#,
e.encryptionalg,
e.ciphermode,
e.encryptedts,
e.status,
e.blocks_encrypted,
e.blocks_decrypted
FROM v$encrypted_tablespaces e
JOIN v$tablespace t ON e.ts# = t.ts#;
Note: In Oracle 26ai, you can perform online rekey operations while the tablespace remains accessible, and Data Guard standby recovery can continue uninterrupted.
3. Backup and Recovery Considerations
TDE has important implications for database backup and recovery strategies.
3.1 Backup Strategy
Golden Rule:A database backup without the corresponding wallet backup is useless for recovering encrypted data.
Coordinate Backups: Always backup the wallet at the same time as the database
Separate Media: Store wallet backups on separate media from database backups
Test Recovery: Regularly test recovery procedures including wallet restore
3.2 Recovery with TDE
When recovering a database with TDE:
Restore the wallet from its separate backup
Open the wallet with the correct password
Restore the database from backup
Recover the database as usual
-- Example recovery process
-- restore wallet from backup
SQL> STARTUP NOMOUNT;
SQL> ADMINISTER KEY MANAGEMENT SET KEYSTORE CLOSE CONTAINER = ALL; -- close the autologin wallet
SQL> ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY "MySecureWalletPass123!" CONTAINER=ALL; -- open the password wallet
SQL> alter database mount; -- now the wallet is open and the master key loaded
RMAN> RESTORE DATABASE;
RMAN> RECOVER DATABASE;
RMAN> alter database open;
-- After recovery, ensure wallet is accessible for pdb1
SQL> alter session set container=pdb1;
SQL> ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY "MySecureWalletPass123!";
SQL> select * from v$ENCRYPTION_WALLET;
-- Verify encrypted data is accessible
SELECT COUNT(*) FROM dbsnmp.customers;
4. Monitoring TDE
Regular monitoring ensures your TDE implementation is working correctly and helps identify potential issues.
4.1 Key Views for Monitoring
Use these dynamic performance views to monitor TDE:
-- Wallet status
SELECT * FROM V$ENCRYPTION_WALLET;
-- Encrypted tablespaces
SELECT c.name AS container_name,
t.name AS tablespace_name,
e.ts#,
e.encryptionalg,
e.ciphermode,
e.encryptedts,
e.status,
e.blocks_encrypted, -- Number of new blocks encrypted
e.blocks_decrypted -- Number of encrypted blocks decrypted
FROM v$encrypted_tablespaces e
JOIN v$tablespace t ON e.ts# = t.ts# AND e.con_id = t.con_id
JOIN v$containers c ON e.con_id = c.con_id
ORDER BY c.name, t.name;
-- Encryption keys
select creator_pdbname, algorithm, creation_time from v$encryption_keys;
Common Issues and Troubleshooting
Even with proper implementation, you may encounter issues with TDE. Here are the most common problems and their solutions.
1. Wallet Not Opening
Symptom: ORA-28365: wallet is not open
This error occurs when you try to access encrypted data but the wallet is closed.
Solution:
-- Check wallet status
SELECT * FROM V$ENCRYPTION_WALLET;
-- Open the wallet
ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY "your_wallet_password" CONTAINER=ALL;
-- Verify if it's open
SELECT * FROM V$ENCRYPTION_WALLET;
Common Causes:
Database was restarted and auto-login wallet is not working
Wrong wallet password was provided
Wallet files are missing or corrupted
WALLET_ROOT parameter is not set correctly
2. ORA-28360: master key not yet set in wallet
Symptom: ORA-28360: master key not yet set in wallet
This error indicates that you’re trying to encrypt data but the master encryption key hasn’t been created yet.
Solution:
-- Ensure wallet is open
ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY "your_wallet_password" CONTAINER=ALL;
-- Create the master key
ADMINISTER KEY MANAGEMENT SET KEY IDENTIFIED BY "your_wallet_password" WITH BACKUP CONTAINER=ALL;
3. ORA-28354: cannot open wallet
Symptom: ORA-28354: cannot open wallet
This error typically indicates a problem with the wallet files or permissions.
Solution:
Check file permissions:
ls -la /u01/app/oracle/wallet/${ORACLE_SID}/tde/
The files should be owned by oracle:oinstall with 600 or 700 permissions.
Verify wallet location:
SELECT value FROM v$parameter WHERE name = 'wallet_root';
Check if wallet files exist:
ls -la /u01/app/oracle/wallet/${ORACLE_SID}/tde/ewallet.p12
ls -la /u01/app/oracle/wallet/${ORACLE_SID}/tde/cwallet.sso
Verify password: Ensure you’re using the correct wallet password.
Check for corruption: If the wallet files are corrupted, restore from backup.
4. ORA-28337: the password supplied for the wallet is incorrect
Symptom: ORA-28337: the password supplied for the wallet is incorrect
Self-explanatory – the wallet password you provided doesn’t match.
Solution:
Double-check the password you’re using
If you’ve forgotten the password and don’t have a backup, the encrypted data is permanently inaccessible
Restore the wallet from backup if available
💡 Prevention: Store wallet passwords in a secure password manager and ensure multiple authorized personnel have access.
5. ORA-19908: cannot open wallet for tablespace encryption
Symptom: ORA-19908: cannot open wallet for tablespace encryption
This error occurs when trying to create or encrypt a tablespace but the wallet isn’t accessible.
Solution:
-- Ensure wallet is open
ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY "your_wallet_password" CONTAINER=ALL;
-- Ensure master key exists
ADMINISTER KEY MANAGEMENT SET KEY IDENTIFIED BY "your_wallet_password" CONTAINER=ALL;
-- Retry the tablespace encryption
ALTER TABLESPACE your_tablespace ENCRYPTION ONLINE USING 'AES256' ENCRYPT;
6. Performance Issues with TDE
Symptom: Slow performance after enabling TDE
While TDE has minimal overhead, you might notice performance impact in some scenarios.
Solution:
Check for missing indexes: Encrypted columns normally cannot use regular indexes. Consider virtual columns or function-based indexes, or create the column with the NO SALT option.
This is related to: ORA-28338: Columns cannot be both indexed and encrypted with salt.
Monitor CPU usage: TDE operations are CPU-intensive. Ensure you have adequate CPU resources.
Use appropriate algorithm: AES256 has more overhead than AES128. Use the strongest algorithm you need.
Consider hardware acceleration: Some hardware supports AES acceleration.
Review encryption scope: Only encrypt what’s necessary. Tablespace encryption has less overhead than column encryption.
Conclusion
Oracle Transparent Data Encryption (TDE) is a powerful feature that provides robust protection for your data at rest. With Oracle AI Database 26ai, TDE has become even more powerful with features like online tablespace encryption, AES256 as the default algorithm, and improved Data Guard integration.
In this comprehensive guide, we’ve covered:
TDE Fundamentals: How TDE works, key hierarchy, and components
Implementation: Step-by-step guide to enable TDE on your Oracle AI Database 26ai
Maintenance: Wallet management, key rotation, backup strategies, and monitoring
Troubleshooting: Common issues and their solutions
By implementing TDE on your Oracle AI Database 26ai installation, you’re taking a crucial step toward securing your sensitive data and meeting compliance requirements.
Remember: The security of your encrypted data depends on the security of your wallet. Always follow best practices for wallet backup, password management, and access control.
Monitoring databases is critical for ensuring performance, availability, and troubleshooting. Checkmk, an open-source monitoring tool, is a powerful choice for monitoring Oracle 26ai databases running on Linux. In this guide, we’ll walk through the process of setting up Checkmk Community Edition on Ubuntu 26.04 to monitor an Oracle 26ai database including ASM hosted on another system.
By the end of this post, you’ll have a fully functional Checkmk installation with copy-paste code snippets to get everything running smoothly.
Prerequisites
Before we begin, ensure you have the following:
Ubuntu 26.04 Server VM: Installed and configured. You can follow this guide for a streamlined setup.
Oracle 26ai Database with ASM: Running on a separate VM. Refer to this Oracle 26ai setup guide for installation instructions.
SSH Access: Root or sudo access to both the Ubuntu 26.04 server and the Oracle 26ai VM.
Network Connectivity: Ensure the Ubuntu server can communicate with the Oracle VM (e.g., via IP or hostname).
Step 1: Download and Install Checkmk Community Edition on the monitoring server
Download & Install Checkmk
Download the .deb package and install Checkmk Community Edition on the Ubuntu VM:
# run as root
wget https://download.checkmk.com/checkmk/2.5.0p10/check-mk-community-2.5.0p10_0.resolute_amd64.deb
apt -y install ./check-mk-community-2.5.0p10_0.resolute_amd64.deb
omd version
Sample Output (click to expand):
root@lin3:~# # run as root
wget https://download.checkmk.com/checkmk/2.5.0p10/check-mk-community-2.5.0p10_0.resolute_amd64.deb
apt install ./check-mk-community-2.5.0p10_0.resolute_amd64.deb
omd version
--2026-08-05 14:32:28-- https://download.checkmk.com/checkmk/2.5.0p10/check-mk-community-2.5.0p10_0.resolute_amd64.deb
Resolving download.checkmk.com (download.checkmk.com)... 45.133.11.29
Connecting to download.checkmk.com (download.checkmk.com)|45.133.11.29|:443... connected.
HTTP request sent, awaiting response... 200 OK
Length: 331054226 (316M) [application/vnd.debian.binary-package]
Saving to: ‘check-mk-community-2.5.0p10_0.resolute_amd64.deb’
check-mk-community-2.5.0p10_0 100%[==============================================>] 315.72M 10.7MB/s in 31s
2026-08-05 14:32:59 (10.0 MB/s) - ‘check-mk-community-2.5.0p10_0.resolute_amd64.deb’ saved [331054226/331054226]
Note, selecting 'check-mk-community-2.5.0p10' instead of './check-mk-community-2.5.0p10_0.resolute_amd64.deb'
The following packages were automatically installed and are no longer required:
linux-image-7.0.0-27-generic linux-modules-7.0.0-27-generic linux-tools-7.0.0-27-generic
linux-main-modules-zfs-7.0.0-27-generic linux-tools-7.0.0-27
Use 'apt autoremove' to remove them.
Installing:
check-mk-community-2.5.0p10
Installing dependencies:
apache2 libaprutil1-dbd-sqlite3 libfsverity0 librpmio10 php8.5-cgi rpcbind
apache2-bin libaprutil1-ldap libgvc7 librpmsign10 php8.5-cli rpm
apache2-data libaprutil1t64 libgvplugin-gd8 libsodium23 php8.5-common rpm-common
apache2-utils libargon2-1 libgvplugin-pango8 libxdot4 php8.5-gd rpm2cpio
debugedit libcdt6 libgvpr2 php-cgi php8.5-readline samba-common
dialog libcgraph8 liblua5.3-0 php-cli php8.5-sqlite3 samba-common-bin
freeradius-common libdbi-perl libpathplan4 php-common php8.5-xml smbclient
freeradius-config libdialog15 libpq5 php-gd python3-ldb tdb-tools
freeradius-utils libevent-2.1-7t64 librpm-sequoia-1 php-pear python3-samba traceroute
graphviz libfl2 librpm10 php-sqlite3 python3-talloc xinetd
libapr1t64 libfreeradius3 librpmbuild10 php-xml python3-tdb
Suggested packages:
apache2-doc gsfonts libnet-daemon-perl alien heimdal-clients
apache2-suexec-pristine graphviz-doc libsql-statement-perl elfutils cifs-utils
| apache2-suexec-custom libmldbm-perl libpq-oauth rpmlint
Summary:
Upgrading: 0, Installing: 66, Removing: 0, Not Upgrading: 0
Download size: 23.2 MB / 354 MB
Space needed: 102 MB / 83.0 GB available
Continue? [Y/n]
...
Processing triggers for ufw (0.36.2-9build1)…
Processing triggers for man-db (2.13.1-1build1)…
Processing triggers for dbus (1.16.2-2ubuntu4)…
Processing triggers for libc-bin (2.43-2ubuntu2.3)…
Processing triggers for php8.5-cli (8.5.4-0ubuntu1.2)…
Processing triggers for php8.5-cgi (8.5.4-0ubuntu1.2)…
Notice: Download is performed unsandboxed as root as file '/root/check-mk-community-2.5.0p10_0.resolute_amd64.deb' couldn't be accessed by user '_apt'. - pkgAcquire::Run (13: Permission denied)
OMD - Open Monitoring Distribution Version 2.5.0p10.community
root@lin3:~#
Initialize the Checkmk Site
Create a new Checkmk site (replace mysite with your preferred site name):
# run as root
omd create mysite
# start the site
omd start mysite
Sample Output (click to expand):
root@lin3:~# # run as root
omd create mysite
# start the site
omd start mysite
Adding /opt/omd/sites/mysite/tmp to /etc/fstab.
Creating temporary filesystem /omd/sites/mysite/tmp...OK
Executing post-create script "01_create-sample-config.py"...OK
Executing post-create script "02_message-broker-certs"...OK
Updating core configuration...
Generating configuration for core (type nagios)...
Precompiling host checks...OK
Restarting Apache...OK
Created new site mysite with version 2.5.0p10.community.
The site can be started with omd start mysite.
The default web UI is available at http://lin3.fritz.box/mysite/
The admin user for the web applications is cmkadmin with password: xxx
For command line administration of the site, log in with 'omd su mysite'.
After logging in, you can change the password for cmkadmin with 'cmk-passwd cmkadmin'.
Starting agent-receiver...OK
Starting mkeventd...OK
Starting rrdcached...OK
Starting redis...OK
Starting npcd...OK
Starting automation-helper...OK
Starting ui-job-scheduler...OK
Starting nagios...OK
Starting apache...OK
Starting crontab...OK
root@lin3:~#
Access the Checkmk web interface by navigating to a URL similar to:
The password can be changed now (User => Change password)
Step 2: Configure Checkmk for Oracle 26ai Monitoring
Install Oracle Monitoring Agent and the mk_oracle plugin
Checkmk uses the MKS (Checkmk Agent Plugins) to monitor Oracle databases. Install the agent and the agent plugin:
On the Oracle 26ai VM:
# run as root
# install the checkmk agent
wget lin3.fritz.box/mysite/check_mk/agents/check-mk-agent-2.5.0p10-1.noarch.rpm
dnf -y install ./check-mk-agent-2.5.0p10-1.noarch.rpm
# create monitoring users on the ASM and CDB
su - grid -c '
sqlplus -S / as sysasm <<EOF
create user cmk_asm identified by changeme;
grant sysdba to cmk_asm;
EXIT;
EOF
'
su - oracle -c '
sql -S / as sysdba <<EOF
create user c##checkmk identified by changeme;
alter user c##checkmk set container_data=all container=current;
grant select_catalog_role to c##checkmk container=all;
grant create session to c##checkmk container=all;
EXIT;
EOF
'
# create the Oracle Wallet to store the CDB password
echo -e 'mysecret1\nmysecret1'|/u01/app/oracle/product/23.26.0/dbhome_1/bin/mkstore -wrl /etc/check_mk/oracle_wallet -create
echo mysecret1|/u01/app/oracle/product/23.26.0/dbhome_1/bin/mkstore -wrl /etc/check_mk/oracle_wallet -createCredential orcl c\#\#checkmk changeme
chgrp -R oinstall /etc/check_mk/oracle_wallet
chmod g+x /etc/check_mk/oracle_wallet
chmod -R g+r /etc/check_mk/oracle_wallet
# create checkmk config files (sqlnet.ora, tnsnames.ora and mk_oracle.cfg
cat > /etc/check_mk/sqlnet.ora <<EOF
LOG_DIRECTORY_CLIENT = /var/log/check_mk/oracle_client
DIAG_ADR_ENABLED = OFF
SQLNET.WALLET_OVERRIDE = TRUE
WALLET_LOCATION =
(SOURCE=
(METHOD = FILE)
(METHOD_DATA = (DIRECTORY=/etc/check_mk/oracle_wallet))
)
EOF
mkdir -p /var/log/check_mk/oracle_client
chown -R oracle:oinstall /var/log/check_mk
cat > /etc/check_mk/tnsnames.ora <<EOF
+ASM =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = +ASM)
)
)
orcl =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl.fritz.box)
)
)
EOF
cat >/etc/check_mk/mk_oracle.cfg <<EOF
DBUSER='/:'
ASMUSER='cmk_asm:changeme:SYSDBA'
EOF
# adjust permissions
chgrp oinstall /etc/check_mk/sqlnet.ora /etc/check_mk/tnsnames.ora
# install the checkmk oracle agent plugin
mkdir /usr/lib/check_mk_agent/plugins/60
wget -P /usr/lib/check_mk_agent/plugins/60 lin3.fritz.box/mysite/check_mk/agents/plugins/mk_oracle
chmod +x /usr/lib/check_mk_agent/plugins/60/mk_oracle
Sample Output (click to expand):
[root@lin1 ~]# # run as root
[root@lin1 ~]# # install the checkmk agent
[root@lin1 ~]# wget lin3.fritz.box/mysite/check_mk/agents/check-mk-agent-2.5.0p10-1.noarch.rpm
--2026-08-05 15:35:27-- http://lin3.fritz.box/mysite/check_mk/agents/check-mk-agent-2.5.0p10-1.noarch.rpm
Resolving lin3.fritz.box (lin3.fritz.box)... 11.1.1.177
Connecting to lin3.fritz.box (lin3.fritz.box)|11.1.1.177|:80... connected.
HTTP request sent, awaiting response... 200 OK
Length: 5765323 (5.5M) [application/x-redhat-package-manager]
Saving to: ‘check-mk-agent-2.5.0p10-1.noarch.rpm’
check-mk-agent-2.5.0p10-1.noarch.r 100%[=============================================================>] 5.50M --.-KB/s in 0.05s
2026-08-05 15:35:27 (113 MB/s) - ‘check-mk-agent-2.5.0p10-1.noarch.rpm’ saved [5765323/5765323]
[root@lin1 ~]# dnf -y install ./check-mk-agent-2.5.0p10-1.noarch.rpm
Last metadata expiration check: 1 day, 4:18:07 ago on Tue 04 Aug 2026 11:17:24 AM CEST.
Dependencies resolved.
=========================================================================================================================================
Package Architecture Version Repository Size
=========================================================================================================================================
Installing:
check-mk-agent noarch 2.5.0p10-1 @commandline 5.5 M
Transaction Summary
=========================================================================================================================================
Install 1 Package
Total size: 5.5 M
Installed size: 5.5 M
Downloading Packages:
Running transaction check
Transaction check succeeded.
Running transaction test
Transaction test succeeded.
Running transaction
Preparing : 1/1
Running scriptlet: check-mk-agent-2.5.0p10-1.noarch 1/1
Installing : check-mk-agent-2.5.0p10-1.noarch 1/1
Running scriptlet: check-mk-agent-2.5.0p10-1.noarch 1/1
Deploying agent controller: /usr/bin/cmk-agent-ctl
Deploying systemd units: cmk-agent-ctl-daemon.service check-mk-agent-async.service check-mk-agent@.service check-mk-agent.socket
Deployed systemd
Creating/updating 'cmk-agent' user account ...
WARNING: The agent controller is operating in an insecure mode! To secure the connection run `cmk-agent-ctl register`.
Activating systemd unit 'cmk-agent-ctl-daemon.service'...
Created symlink /etc/systemd/system/multi-user.target.wants/cmk-agent-ctl-daemon.service → /usr/lib/systemd/system/cmk-agent-ctl-daemon.service.
Activating systemd unit 'check-mk-agent-async.service'...
Created symlink /etc/systemd/system/multi-user.target.wants/check-mk-agent-async.service → /usr/lib/systemd/system/check-mk-agent-async.service.
Activating systemd unit 'check-mk-agent.socket'...
Created symlink /etc/systemd/system/sockets.target.wants/check-mk-agent.socket → /usr/lib/systemd/system/check-mk-agent.socket.
Verifying : check-mk-agent-2.5.0p10-1.noarch 1/1
Installed:
check-mk-agent-2.5.0p10-1.noarch
Complete!
[root@lin1 ~]#
[root@lin1 ~]# # create monitoring users on the ASM and CDB
[root@lin1 ~]# su - grid -c '
> sqlplus -S / as sysasm <<EOF
> create user cmk_asm identified by changeme;
> grant sysdba to cmk_asm;
> EXIT;
> EOF
> '
User created.
Grant succeeded.
[root@lin1 ~]# su - oracle -c '
> sql -S / as sysdba <<EOF
> create user c##checkmk identified by changeme;
> alter user c##checkmk set container_data=all container=current;
> grant select_catalog_role to c##checkmk container=all;
> grant create session to c##checkmk container=all;
> EXIT;
> EOF
> '
User C##CHECKMK created.
User C##CHECKMK altered.
Grant succeeded.
Grant succeeded.
[root@lin1 ~]#
[root@lin1 ~]# # create the Oracle Wallet to store the CDB password
[root@lin1 ~]# echo -e 'mysecret1\nmysecret1'|/u01/app/oracle/product/23.26.0/dbhome_1/bin/mkstore -wrl /etc/check_mk/oracle_wallet -create
Oracle Secret Store Tool Release 23.0.0.0.0 - Production
Version 23.0.0.0.0
Copyright (c) 2004, 2025, Oracle and/or its affiliates. All rights reserved.
Enter password:
Enter password again:
[root@lin1 ~]# echo mysecret1|/u01/app/oracle/product/23.26.0/dbhome_1/bin/mkstore -wrl /etc/check_mk/oracle_wallet -createCredential orcl c\#\#checkmk changeme
Oracle Secret Store Tool Release 23.0.0.0.0 - Production
Version 23.0.0.0.0
Copyright (c) 2004, 2025, Oracle and/or its affiliates. All rights reserved.
Enter wallet password:
[root@lin1 ~]# chgrp -R oinstall /etc/check_mk/oracle_wallet
[root@lin1 ~]# chmod g+x /etc/check_mk/oracle_wallet
[root@lin1 ~]# chmod -R g+r /etc/check_mk/oracle_wallet
[root@lin1 ~]#
[root@lin1 ~]# # create checkmk config files (sqlnet.ora, tnsnames.ora and mk_oracle.cfg
[root@lin1 ~]# cat > /etc/check_mk/sqlnet.ora <<EOF
> LOG_DIRECTORY_CLIENT = /var/log/check_mk/oracle_client
> DIAG_ADR_ENABLED = OFF
>
> SQLNET.WALLET_OVERRIDE = TRUE
> WALLET_LOCATION =
> (SOURCE=
> (METHOD = FILE)
> (METHOD_DATA = (DIRECTORY=/etc/check_mk/oracle_wallet))
> )
> EOF
[root@lin1 ~]# mkdir -p /var/log/check_mk/oracle_client
[root@lin1 ~]# chown -R oracle:oinstall /var/log/check_mk
[root@lin1 ~]# cat > /etc/check_mk/tnsnames.ora <<EOF
> +ASM =
> (DESCRIPTION =
> (ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1521))
> (CONNECT_DATA =
> (SERVER = DEDICATED)
> (SERVICE_NAME = +ASM)
> )
> )
>
> orcl =
> (DESCRIPTION =
> (ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1521))
> (CONNECT_DATA =
> (SERVER = DEDICATED)
> (SERVICE_NAME = orcl.fritz.box)
> )
> )
> EOF
[root@lin1 ~]# cat >/etc/check_mk/mk_oracle.cfg <<EOF
> DBUSER='/:'
> ASMUSER='cmk_asm:changeme:SYSDBA'
> EOF
[root@lin1 ~]# # adjust permissions
[root@lin1 ~]# chgrp oinstall /etc/check_mk/sqlnet.ora /etc/check_mk/tnsnames.ora
[root@lin1 ~]#
[root@lin1 ~]# # install the checkmk oracle agent plugin
[root@lin1 ~]# mkdir /usr/lib/check_mk_agent/plugins/60
[root@lin1 ~]# wget -P /usr/lib/check_mk_agent/plugins/60 lin3.fritz.box/mysite/check_mk/agents/plugins/mk_oracle
--2026-08-05 15:35:52-- http://lin3.fritz.box/mysite/check_mk/agents/plugins/mk_oracle
Resolving lin3.fritz.box (lin3.fritz.box)... 11.1.1.177
Connecting to lin3.fritz.box (lin3.fritz.box)|11.1.1.177|:80... connected.
HTTP request sent, awaiting response... 200 OK
Length: 145178 (142K) [text/plain]
Saving to: ‘/usr/lib/check_mk_agent/plugins/60/mk_oracle’
mk_oracle 100%[=============================================================>] 141.78K --.-KB/s in 0.001s
2026-08-05 15:35:52 (131 MB/s) - ‘/usr/lib/check_mk_agent/plugins/60/mk_oracle’ saved [145178/145178]
[root@lin1 ~]# chmod +x /usr/lib/check_mk_agent/plugins/60/mk_oracle
[root@lin1 ~]#
On the Checkmk Server:
Add the Oracle VM as a Host:
Log in to the Checkmk web interface.
Navigate to Setup > Hosts > Add host.
Enter the IP address or hostname of the Oracle VM.
Click Save & run Service Discovery.
Enable Oracle Checks:
On the Service Discovery Page select: Accept All
Then go to Changes > Activate Pending Changes
Secure Agent Communication:
Create a registration password in the GUI (Setup>Users>Edit agent_registration>Create random secret > save)
on the Oracle host run: cmk-agent-ctl register --server lin3.fritz.box --site mysite --hostname lin1.fritz.box --user agent_registration --trust-cert (and enter the password from the clipboard)
Step 3: Verify Oracle 26ai Monitoring
Check Host Status:
Navigate to Monitor > All hosts in the Checkmk web interface.
Select your Oracle VM host. You should see services related to Oracle (e.g., Oracle Tablespaces, Oracle Performance).
Troubleshooting
The following commands can be used to test the connection from mk_oracle to the databases:
# run as root ion the database server
export MK_CONFDIR="/etc/check_mk/"; export MK_VARDIR="/var/lib/check_mk_agent/"
/usr/lib/check_mk_agent/plugins/60/mk_oracle -t --no-spool
Sample Failure Output (click to expand):
[root@lin1 ~]# /usr/lib/check_mk_agent/plugins/60/mk_oracle -t --no-spool
<<<oracle_instance>>>
<<<oracle_sessions>>>
<<<oracle_logswitches>>>
<<<oracle_undostat>>>
<<<oracle_recovery_area>>>
<<<oracle_processes>>>
<<<oracle_recovery_status>>>
<<<oracle_longactivesessions>>>
<<<oracle_dataguard_stats>>>
<<<oracle_performance>>>
<<<oracle_locks>>>
<<<oracle_systemparameter>>>
<<<oracle_tablespaces>>>
<<<oracle_rman>>>
<<<oracle_jobs>>>
<<<oracle_resumable>>>
<<<oracle_iostats>>>
<<<oracle_instance>>>
<<<oracle_processes>>>
<<<oracle_asm_diskgroup>>>
Logindetails: /@orcl
---checking permissions-------------------------------------------------
see https://checkmk.atlassian.net/wiki/spaces/KB/pages/70582273/Troubleshooting+mk+oracle+for+Windows+and+Linux
* sqlplus binary: /u01/app/oracle/product/23.26.0/dbhome_1/bin/sqlplus
* sqlplus binary owner: oracle
* change user: true
* $TNS_ADMIN: /etc/check_mk/
* user "oracle" can read /etc/check_mk//sqlnet.ora
* user "oracle" can read /etc/check_mk//tnsnames.ora
* test-login does not work!
Could not login. In case you are using a wallet to connect, there might be a permission error.
Make sure that the wallet folder can be read and executed by user "oracle" and
the files inside the wallet can be read by the user.
Consult your ora files for hints where the wallet is located:
/etc/check_mk//sqlnet.ora
/etc/check_mk//tnsnames.ora
------------------------------------------------------------------------
---login----------------------------------------------------------------
Operating System: Linux
ORACLE_HOME (GI): /u01/app/oracle/product/23.26.0/dbhome_1
Logincheck to Instance: orcl
Version:
Error Message: ORCL|FAILURE|ERROR: ORA-01017: invalid credential or not authorized; logon denied Help: https://docs
SYNC_SECTIONS: instance sessions logswitches undostat recovery_area processes recovery_status longactivesessions dataguard_stats performance locks systemparameter
ASYNC_SECTIONS: tablespaces rman jobs resumable iostats
------------------------------------------------------------------------
[root@lin1 ~]#
Sample Output Success (click to expand):
[root@lin1 ~]# export MK_CONFDIR="/etc/check_mk/"; export MK_VARDIR="/var/lib/check_mk_agent/"
[root@lin1 ~]# /usr/lib/check_mk_agent/plugins/60/mk_oracle -t --no-spool
<<<oracle_instance>>>
<<<oracle_sessions>>>
<<<oracle_logswitches>>>
<<<oracle_undostat>>>
<<<oracle_recovery_area>>>
<<<oracle_processes>>>
<<<oracle_recovery_status>>>
<<<oracle_longactivesessions>>>
<<<oracle_dataguard_stats>>>
<<<oracle_performance>>>
<<<oracle_locks>>>
<<<oracle_systemparameter>>>
<<<oracle_tablespaces>>>
<<<oracle_rman>>>
<<<oracle_jobs>>>
<<<oracle_resumable>>>
<<<oracle_iostats>>>
<<<oracle_instance>>>
<<<oracle_processes>>>
<<<oracle_asm_diskgroup>>>
---checking permissions-------------------------------------------------
see https://checkmk.atlassian.net/wiki/spaces/KB/pages/70582273/Troubleshooting+mk+oracle+for+Windows+and+Linux
* sqlplus binary: /u01/app/23.26.0/grid/bin/sqlplus
* sqlplus binary owner: grid
* change user: true
* $TNS_ADMIN: /etc/check_mk/
* user "grid" can read /etc/check_mk//sqlnet.ora
* user "grid" can read /etc/check_mk//tnsnames.ora
* test login works
------------------------------------------------------------------------
---login----------------------------------------------------------------
Operating System: Linux
ORACLE_HOME (GI): /u01/app/23.26.0/grid
Logincheck to Instance: +ASM
Version:
Login ok User: SYS on lin1.fritz.box Instance +ASM
SYNC_SECTIONS: instance processes
ASYNC_SECTIONS: asm_diskgroup
------------------------------------------------------------------------
---checking permissions-------------------------------------------------
see https://checkmk.atlassian.net/wiki/spaces/KB/pages/70582273/Troubleshooting+mk+oracle+for+Windows+and+Linux
* sqlplus binary: /u01/app/oracle/product/23.26.0/dbhome_1/bin/sqlplus
* sqlplus binary owner: oracle
* change user: true
* $TNS_ADMIN: /etc/check_mk/
* user "oracle" can read /etc/check_mk//sqlnet.ora
* user "oracle" can read /etc/check_mk//tnsnames.ora
* test login works
------------------------------------------------------------------------
---login----------------------------------------------------------------
Operating System: Linux
ORACLE_HOME (GI): /u01/app/oracle/product/23.26.0/dbhome_1
Logincheck to Instance: orcl
Version:
Login ok User: C##CHECKMK on lin1.fritz.box Instance orcl
SYNC_SECTIONS: instance sessions logswitches undostat recovery_area processes recovery_status longactivesessions dataguard_stats performance locks systemparameter
ASYNC_SECTIONS: tablespaces rman jobs resumable iostats
------------------------------------------------------------------------
[root@lin1 ~]#
You’ve now successfully set up Checkmk Community Edition on Ubuntu 26.04 to monitor an Oracle 26ai database running on a separate VM. This setup ensures you can track database performance, storage, and availability in real time.
Next Steps:
Explore Checkmk’s dashboards and alerting rules to customize your monitoring.
Set up notifications (e.g., email or Slack) for critical alerts.
Consider integrating Checkmk with other tools like Grafana for advanced visualization.
Call to Action: Have you tried monitoring Oracle 26ai with Checkmk? Share your experiences or questions in the comments below!
In the previous articles of this PostgreSQL 18 series, we installed PostGIS, imported OpenStreetMap data for Bavaria, created GiST indexes, and finally visualized our data with Leaflet.
Having your data stored in PostGIS is only the first step. The real power comes from being able to ask spatial questions such as:
Which parks are within walking distance?
How far away is the nearest park?
Which park contains a specific landmark?
Which roads intersect a park?
These are the kinds of queries used by navigation systems, tourism websites, logistics platforms, and city information systems every day.
In this article, we’ll continue using the OpenStreetMap dataset for Bavaria that we imported in the first article. All examples work directly on the planet_osm_* tables created by osm2pgsql, so you can execute them without importing any additional data.
We’ll focus on the most commonly used PostGIS spatial functions:
ST_DWithin()
ST_Distance()
ST_Contains()
ST_Within()
ST_Intersects()
Let’s start with one of the most common GIS queries: finding nearby objects.
Imagine you’re building a tourism application for Munich.
A visitor standing on Marienplatz wants to find nearby parks within a radius of 10 kilometres.
First, we create a geometry representing Marienplatz. Since our OpenStreetMap data was imported with the default osm2pgsql settings, the geometries are stored in EPSG:3857. Therefore, we transform the WGS84 coordinates into the same coordinate reference system before performing the spatial query.
WITH munich AS (
SELECT ST_Transform(
ST_SetSRID(
ST_MakePoint(11.57549,48.13743),
4326
),
3857
) AS geom
)
SELECT
p.name,
ROUND(ST_Distance(p.way,m.geom)) AS distance_m
FROM planet_osm_polygon p
CROSS JOIN munich m
WHERE p.leisure='park'
AND p.name IS NOT NULL
AND ST_DWithin(
p.way,
m.geom,
10000
)
ORDER BY distance_m
LIMIT 10;
The result should look similar to this:
name | distance_m
--------------------------+------------
Hofgarten | 835
Maximiliansplatz | 839
Herzog-Wilhelm-Park | 998
Alter Botanischer Garten | 1175
Dichtergarten | 1203
Nußbaumpark | 1299
Auf der Insel | 1453
Posthof | 1484
Englischer Garten | 1498
Königsplatz | 1604
(10 rows)
Your exact results may vary slightly depending on the OpenStreetMap data version you imported.
The function ST_DWithin() returns all geometries whose distance from the specified point is less than or equal to the given radius. Since our data is stored in EPSG:3857, the distance is measured in metres, making it ideal for proximity searches.
Typical use cases include finding:
nearby parks
restaurants
hospitals
charging stations
bus stops
hotels
Whenever you need to answer the question “What’s nearby?”, ST_DWithin() is usually the right function.
Why Not Use ST_Distance()?
Many developers start with a query like this:
SELECT
name
FROM planet_osm_polygon
WHERE leisure='park'
AND ST_Distance(
way,
ST_Transform(
ST_SetSRID(
ST_MakePoint(11.57549,48.13743),
4326
),
3857
)
) < 10000;
Although the query produces the correct result, it is not the most efficient solution.
ST_Distance() calculates the exact distance between the search point and every matching geometry before PostgreSQL can determine whether the row belongs in the result set.
For small tables this may not matter, but once your database contains hundreds of thousands or even millions of geometries, these calculations become expensive.
ST_DWithin() is specifically optimized for this use case. Together with a GiST index, PostgreSQL can first eliminate geometries that are clearly outside the search radius and calculate exact distances only for the remaining candidates.
If you followed the previous article on GiST indexes, you’ve already prepared your database for this optimization.
As a general rule:
Use ST_DWithin() to filter nearby objects.
Use ST_Distance() to display or sort by the exact distance.
Calculating Exact Distances
The most common pattern in PostGIS applications is to combine both functions.
ST_DWithin() limits the search to nearby geometries, while ST_Distance() calculates the exact distance for the remaining results.
WITH munich AS (
SELECT ST_Transform(
ST_SetSRID(
ST_MakePoint(11.57549,48.13743),
4326
),
3857
) AS geom
)
SELECT
p.name,
ROUND(ST_Distance(p.way,m.geom)) AS distance_m
FROM planet_osm_polygon p
CROSS JOIN munich m
WHERE p.leisure='park'
AND p.name IS NOT NULL
AND ST_DWithin(
p.way,
m.geom,
10000
)
ORDER BY distance_m
LIMIT 10;
This approach has several advantages:
the GiST index reduces the number of candidate geometries,
the exact distance is calculated only for nearby parks,
and the results can easily be sorted from nearest to farthest.
This is the pattern you’ll encounter in many production GIS applications.
Which Park Contains the Chinesischer Turm?
Another common task is determining which polygon contains a particular object.
Suppose a visitor clicks on the Chinesischer Turm in your Leaflet application.
Rather than searching manually, PostGIS can determine which park contains this landmark.
SELECT
p.name
FROM planet_osm_polygon p
JOIN planet_osm_point o
ON ST_Contains(
p.way,
o.way
)
WHERE p.leisure='park'
AND o.name='Chinesischer Turm';
The query returns:
Englischer Garten
The ST_Contains() function returns TRUE when one geometry completely contains another.
Typical applications include:
finding the city for a GPS coordinate,
determining the district containing a building,
assigning customers to sales territories,
locating landmarks inside parks,
or identifying administrative regions for an address.
In combination with interactive maps such as Leaflet, this function makes it easy to answer the question:
“What is located here?”
Instead of maintaining complex lookup tables, PostGIS performs the spatial relationship directly using the stored geometries.
Is the Olympiaturm Inside Olympiapark?
The opposite of ST_Contains() is ST_Within().
Instead of asking:
Which park contains this landmark?
we ask:
Is this landmark located inside the park?
Both functions describe the same spatial relationship, but from different perspectives.
The following example checks whether the Olympiaturm is located inside the Olympiapark.
SELECT
ST_Within(tower.way, park.way)
FROM planet_osm_polygon tower
JOIN planet_osm_polygon park
ON park.name='Olympiapark'
WHERE tower.name='Olympiaturm';
The result is:
st_within
-----------
t
Whether you choose ST_Contains() or ST_Within() is mostly a matter of readability.
For example, these two questions are logically equivalent:
Does Olympiapark contain the Olympiaturm?
Is the Olympiaturm within Olympiapark?
Choose whichever makes your SQL easier to understand.
Typical applications include:
checking whether a building lies inside a municipality,
determining whether a GPS position is inside a geofence,
verifying that a customer belongs to a delivery zone,
validating whether an object is inside a protected area.
Which Roads Cross the Englischer Garten?
So far we’ve looked at relationships between points and polygons.
PostGIS can also compare entire geometries.
Suppose we want to know which roads intersect the Englischer Garten.
SELECT DISTINCT
l.name
FROM planet_osm_line l
JOIN planet_osm_polygon p
ON ST_Intersects(
l.way,
p.way
)
WHERE p.name='Englischer Garten'
AND l.highway IS NOT NULL
AND l.name IS NOT NULL
ORDER BY l.name
LIMIT 20;
# Sample Output:
name
------------------------
Alte Lastenstraße
Alte Parkstraße
Am Englischen Garten
Am Hirschanger
Aumeisterbrücke
Baumschulstraße
Baumschulweg
Blaue Brücke
Burgfriedenweg
Carl-August-Sckell-Weg
Carl-Theodor-Brücke
Carl-Theodor-Straße
Dianabadbrücke
Dianabadweg
Diermayerweg
Dietlindenstraße
Effnerbrücke
Effnerstraße
Englischer Garten
Entenfallbrücke
(20 rows)
Unlike ST_Contains(), ST_Intersects() only checks whether two geometries share at least one point.
That makes it one of the most useful functions for spatial joins.
Typical examples include:
roads crossing parks,
rivers crossing administrative boundaries,
buildings intersecting flood zones,
railway lines crossing municipalities,
utility lines crossing streets.
Spatial joins are one of the biggest strengths of PostGIS and often replace complicated application logic with a single SQL statement.
Combining PostGIS with Leaflet
In the previous article, we displayed our OpenStreetMap data using Leaflet.
By combining Leaflet with the spatial queries introduced here, you can build surprisingly powerful web applications with only a few SQL statements.
For example:
show parks near the user’s location,
display hospitals within 5 km,
highlight roads crossing a selected park,
determine which district a clicked point belongs to,
find cafés located inside a park.
A typical workflow looks like this:
The user clicks somewhere on the map.
Leaflet sends the coordinates to your backend.
PostgreSQL executes one or more PostGIS queries.
The matching geometries are returned as GeoJSON.
Leaflet highlights the results on the map.
The combination of PostgreSQL, PostGIS and Leaflet provides an excellent foundation for interactive GIS applications without requiring proprietary software.
Summary
In this article, we used the OpenStreetMap dataset imported in the first part of this series to answer practical spatial questions around Munich.
We looked at the PostGIS functions you’ll use most often in day-to-day applications.
Function
Purpose
ST_DWithin()
Find nearby objects
ST_Distance()
Calculate the exact distance
ST_Contains()
Determine which polygon contains an object
ST_Within()
Check whether an object lies inside another
ST_Intersects()
Find overlapping geometries
Although PostGIS offers hundreds of functions, these five cover a large percentage of everyday GIS tasks.
Once you become comfortable with them, you’ll be able to build applications such as:
tourism and city guides,
route planners,
logistics platforms,
delivery services,
asset management systems,
emergency response applications,
interactive web maps.
Conclusion
With only a few SQL statements, PostGIS allows PostgreSQL to answer complex spatial questions that would otherwise require significant application logic.
Using the OpenStreetMap dataset imported earlier in this series, we’ve seen how to:
search for nearby parks,
calculate distances,
determine which park contains a landmark,
and identify roads intersecting a park.
These same techniques can be applied to virtually any type of geographic data, making PostGIS one of the most powerful spatial databases available today.
In the previous articles we built a production-ready PostGIS environment on PostgreSQL 18 and created an interactive Leaflet map that displays parks from OpenStreetMap data. While the application works, spatial queries can quickly become slow as the amount of data grows.
In this article we will investigate why certain spatial queries are slow, create our first GiST index, compare execution plans with EXPLAIN ANALYZE, and measure the real impact on map loading times.
PostGIS extends PostgreSQL with powerful spatial capabilities, enabling you to store, query, and analyze geographic data. It is the foundation for many Geographic Information Systems (GIS) used by municipalities, utility companies, logistics providers, and public authorities. In this article, we build a production-ready PostGIS environment that will serve as the basis for future articles covering performance tuning, indexing, maintenance, monitoring, backup strategies, and high availability.
In the previous article, Building a Production-Ready PostGIS Environment on PostgreSQL 18, we installed PostGIS, created a spatial database, and imported OpenStreetMap data. While querying the database with SQL is useful, spatial data becomes much more valuable once it can be visualized on an interactive map.
In this article, we’ll build a simple web application using Leaflet, the most popular open-source JavaScript mapping library. The application will retrieve data directly from PostgreSQL/PostGIS and display it on an interactive OpenStreetMap layer. This environment will become the foundation for future articles covering performance tuning, GiST indexes, query optimization, monitoring, and database maintenance.
Create a dedicated database user for the web application.
# run as the postgres user
psql gisdemo <<EOF
-- create role
CREATE ROLE leaflet LOGIN PASSWORD 'changeme';
-- grant access
GRANT CONNECT ON DATABASE gisdemo TO leaflet;
GRANT USAGE ON SCHEMA public TO leaflet;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO leaflet;
\q
EOF
Creating the GeoJSON API
Create the following file as root
mkdir /var/www/html/api
vi /var/www/html/api/parks.php
The browser never communicates directly with PostgreSQL. Instead, PHP acts as a lightweight API that retrieves spatial data and converts it into GeoJSON.
Why GeoJSON?
GeoJSON has become the standard format for exchanging geographic information between databases and web applications.
Advantages include:
Native Leaflet support
Human-readable JSON
Generated directly by PostGIS
Supported by most GIS software
Lightweight and easy to debug
Exploring the Database
Let’s execute a few SQL statements to understand the imported dataset.
How many parks are available?
psql gisdemo <<EOF
SELECT count(*)
FROM planet_osm_polygon
WHERE leisure='park';
EOF
Sample Output (click to expand):
[postgres@lin6 ~]$ psql gisdemo <<EOF
SELECT count(*)
FROM planet_osm_polygon
WHERE leisure='park';
EOF
count
-------
6247
(1 row)
[postgres@lin6 ~]$
List some park names.
psql gisdemo <<EOF
SELECT
name
FROM planet_osm_polygon
WHERE leisure='park'
ORDER BY name
LIMIT 20;
EOF
Sample Output (click to expand):
[postgres@lin6 ~]$ psql gisdemo <<EOF
SELECT
name
FROM planet_osm_polygon
WHERE leisure='park'
ORDER BY name
LIMIT 20;
EOF
name
--------------------------------------------------------
1A Hundegarten Kompetenzzentrum und Hundenasenparadies
Abdurrahim-Özüdoğru-Park
Aberdeenpark
Abloner-Garten
Achenpark
Achenseeplatz
Achenseeplatz
Adamiwiese
Adelheidpark
AGFA-Park
Ägidienplatz
Ägidiuspark
Agricolaplatz
Aktiv Park
Aktiv-Park
Albert-Schweitzer-Park
Albert-Schweitzer-Seniorenzentrum
Albin-Lang-Stadtpark Landshut
Alfons Halbig Platz
Alfred-Dick-Park
(20 rows)
[postgres@lin6 ~]$
Calculate the area of the English Garden.
psql gisdemo <<EOF
SELECT
name,
round(ST_Area(ST_Transform(way, 4326)::geography)) AS square_meters
FROM planet_osm_polygon
WHERE name = 'Englischer Garten'
ORDER BY square_meters DESC
LIMIT 1;
EOF
Output (click to expand):
[postgres@lin6 ~]$ psql gisdemo <<EOF
SELECT
name,
round(ST_Area(ST_Transform(way, 4326)::geography)) AS square_meters
FROM planet_osm_polygon
WHERE name = 'Englischer Garten'
ORDER BY square_meters DESC
LIMIT 1;
EOF
name | square_meters
-------------------+---------------
Englischer Garten | 3931062
(1 row)
[postgres@lin6 ~]$
These examples demonstrate that PostGIS is much more than a storage engine—it provides advanced spatial analysis directly within PostgreSQL.
Preparing for Performance Tuning
The application is intentionally simple, but it already gives us a realistic workload that we can optimize.
In the upcoming articles we’ll use this same application to demonstrate:
GiST indexes
Bounding box queries
ST_DWithin
ST_Intersects
EXPLAIN ANALYZE
VACUUM
REINDEX
Autovacuum tuning
Streaming Replication
Backup and recovery
Monitoring
Every optimization will be measured by observing how quickly the map loads.
What’s Next?
In the next article we’ll investigate why some spatial queries become slow as the amount of OpenStreetMap data increases.
We’ll create our first GiST index, compare execution plans with EXPLAIN ANALYZE, and measure the impact on map loading times.
Conclusion
By combining PostgreSQL, PostGIS, OpenStreetMap, and Leaflet, we’ve built the foundation of a real-world GIS application. Rather than relying on artificial sample data, our application visualizes actual OpenStreetMap features stored in PostgreSQL, making it an ideal environment for exploring spatial queries, database administration, and performance tuning.
This architecture closely resembles those used by municipalities, utility companies, logistics providers, and many enterprise GIS platforms. Throughout the remainder of this series, we’ll continue extending and optimizing this application while learning how PostgreSQL and PostGIS handle large-scale spatial workloads.
Whether you’re learning PostgreSQL, testing a new application, or experimenting with SQL queries, setting up a database server can take time. To make getting started easier, we now offer free hosted PostgreSQL databases for development, testing, and educational purposes.
The following are the steps for an unattended installation of Ubuntu 26.04 on VirtualBox using autoinstall and cloud-init. The host system in my case is Windows 11.
PostgreSQL continues to be one of the most popular open-source database platforms for enterprise workloads, and the release of PostgreSQL 18 brings further improvements in performance, scalability and reliability. When deploying PostgreSQL in production environments, high availability and data protection are key considerations, making streaming replication one of the most important technologies to implement.
In this blog post, we will walk through the installation of PostgreSQL 18 on two Red Hat Enterprise Linux 10 virtual machines and configure a primary/standby architecture using PostgreSQL streaming replication. The guide covers the complete setup process, including package installation, database initialization, replication configuration, creation of a standby server and validation of the replication environment.
By the end of this tutorial, you will have a fully functional PostgreSQL replication setup that can serve as the foundation for high-availability and disaster-recovery solutions in enterprise environments.
