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.

Table of Contents

What is Oracle Transparent Data Encryption (TDE)?

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:

1. Opens the wallet/keystore containing the master encryption key
2. Uses the master key to decrypt the appropriate tablespace or column key
3. Uses the decrypted key to decrypt the actual data
4. 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:

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
• Users: oracle (database owner), grid (ASM owner)
• Database Home: /u01/app/oracle/product/23.26.0/dbhome_1
• Grid Home: /u01/app/23.26.0/grid
• Database Name: orcl (or your chosen SID)

Requirements for TDE

• 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

[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

[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

[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

[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

[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

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

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

Session altered.


TABLESPACE SECURE_DATA created.


TABLESPACE_NAME TS# ENCRYPTIONALG CIPHERMODE ENCRYPTEDTS
_______________ ___ _____________ __________ ___________
SECURE_DATA       7 AES256        XTS        YES

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

[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:

1. Oracle starts encrypting data in the background
2. Existing data is encrypted as it’s accessed (lazy encryption)
3. New data written to the tablespace is encrypted immediately
4. The tablespace remains fully accessible during the process
5. 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

[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

[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

[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

[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:

1. Oracle generates a new master encryption key
2. All existing keys (tablespace keys, column keys) are re-encrypted with the new master key
3. The old master key is retained until all data is re-encrypted
4. 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:

1. Restore the wallet from its separate backup
2. Open the wallet with the correct password
3. Restore the database from backup
4. 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:

1. 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.

2. Verify wallet location:

  SELECT value FROM v$parameter WHERE name = 'wallet_root';

3. 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

4. Verify password: Ensure you’re using the correct wallet password.
5. 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:

1. Double-check the password you’re using
2. If you’ve forgotten the password and don’t have a backup, the encrypted data is permanently inaccessible
3. 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.

Additional Resources

• Oracle TDE Documentation 26ai – Official Oracle documentation
• Install Oracle AI Database 26ai on VirtualBox VM – The installation guide we based our examples on
• Oracle Advanced Security – Product information
• Oracle Blog: Online Tablespace Encryption in 26ai – New features in 26ai

Introduction

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:

1. Ubuntu 26.04 Server VM: Installed and configured. You can follow this guide for a streamlined setup.
2. Oracle 26ai Database with ASM: Running on a separate VM. Refer to this Oracle 26ai setup guide for installation instructions.
3. SSH Access: Root or sudo access to both the Ubuntu 26.04 server and the Oracle 26ai VM.
4. 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 install ./check-mk-community-2.5.0p10_0.resolute_amd64.deb
omd version

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

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:

http://lin3.fritz.box/mysite/

Log in with the default credentials:

• Username: cmkadmin
• Password: as shown in the previous step

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

[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:

1. 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.
2. Enable Oracle Checks:
   • On the Service Discovery Page select: Accept All
   • Then go to Changes > Activate Pending Changes
3. 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

1. 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

[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 ~]#

[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 ~]#

Useful resources

• Checkmk Forum
• Checkmk Knowledge Base
• Oracle-Datenbanken überwachen

Conclusion

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.

Finding Nearby Parks with ST_DWithin()

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:

1. The user clicks somewhere on the map.
2. Leaflet sends the coordinates to your backend.
3. PostgreSQL executes one or more PostGIS queries.
4. The matching geometries are returned as GeoJSON.
5. 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.

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.

Large Language Models (LLMs) no longer require expensive cloud subscriptions or high-end enterprise hardware. Thanks to projects like llama.cpp, it is possible to run modern quantized language models completely offline on a standard Windows PC.

In this article, we’ll build llama.cpp directly from the Git repository using Visual Studio 2022, enable CUDA support for an NVIDIA GeForce GTX 1070 (8 GB), and finally connect the local model to Kilo inside Visual Studio Code.

At the end of this tutorial you’ll have your own local AI coding assistant that never sends your source code to an external service.

]

Prerequisites

Before starting, make sure the following software is installed:

• Windows 10 or Windows 11
• Visual Studio 2022 with Desktop Development with C++
• Git
• CMake
• NVIDIA CUDA 12.4 Toolkit
• Visual Studio Code
• Kilo VS Code extension

For this article the test system uses an NVIDIA GTX 1070 with 8 GB of VRAM. Although this GPU is several generations old, it is still perfectly capable of running modern 7B parameter models using 4-bit quantization.

Clone the llama.cpp Repository

Open PowerShell and clone the official repository.

git clone https://github.com/ggml-org/llama.cpp.git
cd llama.cpp

Using the latest Git repository instead of precompiled binaries ensures that you always have the newest features, performance improvements and bug fixes.

Compile llama.cpp with CUDA Support

Since we want to use our NVIDIA GPU, CUDA support must be enabled during compilation.

The GTX 1070 is based on the Pascal architecture which has Compute Capability 6.1, therefore we explicitly specify the CUDA architecture during configuration.

Generate the build files:

cmake -B build -DGGML_CUDA=ON -DCMAKE_CUDA_ARCHITECTURES="61"

Now compile the project.

cmake --build build --config Release

Depending on your computer this process usually takes several minutes.

After a successful build you’ll find all executables inside

cd build\bin\Release

including

• llama-cli.exe
• llama-server.exe
• llama-bench.exe
• llama-quantize.exe

Download a Model

For this tutorial we’ll use the excellent Qwen2.5-Coder-7B-Instruct model in GGUF format from huggingface.

Download Link (qwen2.5-coder-7b-instruct-q4_k_m.gguf)

Store the model in

C:\sw\models

Using a dedicated model directory makes it easy to switch between different models later.

Start the Local LLM Server

Start the server with the following command:

.\llama-server.exe `
    -m "C:\sw\models\qwen2.5-coder-7b-instruct-q4_k_m.gguf" `
    -c 16384 `
    -ngl 99

Let’s briefly explain these parameters.

When the server starts successfully you should see log messages indicating that CUDA has been initialized and that the model has been loaded.

The server exposes an OpenAI-compatible REST API on port 8080, making it compatible with many AI tools.

Install Kilo Code

Open Visual Studio Code and install the Kilo Code extension from the Marketplace.

Kilo Code is an AI coding assistant capable of communicating with OpenAI-compatible APIs. Instead of connecting to a cloud provider, we’ll point it to our own llama.cpp server running locally.

Configure Kilo Code

Configure the OpenAI-compatible provider so that Kilo connects to your local server instead of an online service. Go to Settings => Providers => Custom provider => Connect

• Provider ID: llamacpp
• Display Name: llamacpp
• Provider API: OpenAI Compatible
• Base URL: http://localhost:8080
• API Key: dummy
• Name of the model: qwen2.5-coder

The API key is ignored by llama.cpp but many clients expect one to be configured.

Open the Kilo configuration file (Global Config / kilo.jsonc) and modify your local model.

{
  "models": {
    "C:\\sw\\models\\qwen2.5-coder-7b-instruct-q4_k_m.gguf": {
      "name": "qwen2.5-coder",
      "limit": {
        "context": 16384,
        "output": 4096
      }
    }
  }
}

Once the configuration has been saved, restart Visual Studio Code if necessary.

Test the Installation

Open Kilo Code and start a new conversation (Make sure the correct model is selected).

As a first test, ask the model:

Generate a simple Hello World program in modern C++.

The generated response should look similar to this:

#include 

int main()
{
    std::cout << "Hello World!" << std::endl;
    return 0;
}

If Kilo Code produces a valid C++ program, your complete local AI environment is working correctly.

Alternative Interfaces

If there are problems with the Client (Kilo Code) there are several other options to test or use the LLM

Using the Llama.cpp web interface

With this URL you can also use the local LLM: http://127.0.0.1:8080

Using Cline as a client

Install cline as a VS Code extension, then open the Cline settings and select OpenAI Compatible as the provider.

Configure the endpoint as follows:

The API key is not validated by llama-server, but Cline requires a value to be entered.

Once the configuration has been saved, Cline immediately connects to the local server.

Performance Notes

The NVIDIA GTX 1070 may not be the newest graphics card, but it still performs remarkably well with quantized 7B models.

The Q4_K_M quantization offers an excellent compromise between model quality, memory consumption and inference speed.

If you encounter CUDA out-of-memory errors, consider lowering the context size or using fewer GPU layers. Conversely, systems with newer GPUs and more VRAM can increase these values for improved performance.

References

• llama.cpp
• willitrun.ai (Which model runs on my GPU)
• Kilo (e.g. Kilo Code)

Why Use Local LLMs?

Running your own language model offers several important advantages.

• Complete privacy
• No monthly API costs
• Offline operation
• Low latency
• Full control over model selection
• No rate limits

For developers working with proprietary source code or confidential customer projects, local inference can be an attractive alternative to cloud-based AI services.

Conclusion

Compiling llama.cpp from source gives you the latest optimizations and full control over the build process. Combined with CUDA acceleration, even an older graphics card such as the GTX 1070 can provide an enjoyable experience with modern coding models like Qwen2.5-Coder-7B-Instruct.

Once the local server is connected to Kilo Code, Visual Studio Code gains a private AI coding assistant that works entirely on your own machine. Whether you’re generating boilerplate code, explaining existing projects or experimenting with new ideas, this setup delivers a fast and secure development environment without relying on external AI providers.

Happy coding!

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.

Read More

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.

Read More

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.

Architecture

Our application consists of four components:

Browser
    │
    ▼
Leaflet (JavaScript)
    │
    ▼
PHP Web Server
    │
    ▼
PostgreSQL 18 + PostGIS

The browser requests GeoJSON data from a PHP script, which executes a SQL query against PostgreSQL and returns the results in JSON format.

Prerequisites

This article assumes you already have:

• PostgreSQL 18 installed
• PostGIS enabled
• OpenStreetMap data imported using osm2pgsql

If not, please read the previous article first.

Installing Apache and PHP

On RHEL 10 install Apache, PHP and the PostgreSQL driver and perform additional tasks.

# install packages
dnf -y install httpd php php-pgsql
# enable Apache
systemctl enable httpd --now
# open the firewall
firewall-cmd --add-service=http --permanent
firewall-cmd --reload

Preparing PostgreSQL

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

with the following content:

<?php

$conn = pg_connect("
host=localhost
dbname=gisdemo
user=leaflet
password=changeme
");

$sql = "
SELECT
  json_build_object(
    'type','FeatureCollection',
    'features', json_agg(feature)
  )
FROM (
  SELECT
    json_build_object(
      'type','Feature',
      'geometry', ST_AsGeoJSON(ST_Transform(way, 4326))::json,
      'properties', json_build_object('name',name,'type',leisure)
    ) feature
  FROM planet_osm_polygon
  WHERE leisure='park'
    AND ST_DWithin(
          ST_Transform(way, 4326)::geography,
          ST_SetSRID(ST_MakePoint(11.576124, 48.137154), 4326)::geography,
          10000   -- 10 km radius
        )
) t;
";

$result = pg_query($conn, $sql);
header("Content-Type: application/json");
echo pg_fetch_result($result, 0, 0);
?>

To test the endpoint point a browser to:

http://lin6.fritz.box/api/parks.php

You should receive a GeoJSON FeatureCollection.

Creating the Leaflet Application

Create the following file as the root user

vi /var/www/html/index.html

with this content:

<!DOCTYPE html>
<html>
<head>
<meta charset="utf-8">
<title>Leaflet + PostGIS</title>
<link rel="stylesheet" href="https://unpkg.com/leaflet/dist/leaflet.css" />
<style>
html, body { height:100%; margin:0; }
#map { height:100%; }
</style>
</head>
<body>
<div id="map"></div>
<script src="https://unpkg.com/leaflet/dist/leaflet.js"></script>
<script>
const map = L.map('map').setView([48.137154, 11.576124], 12);

L.tileLayer('https://tile.openstreetmap.org/{z}/{x}/{y}.png', {
    maxZoom: 19,
    attribution: '© OpenStreetMap contributors'
}).addTo(map);

fetch('api/parks.php')
    .then(response => response.json())
    .then(data => {
        console.log('Received GeoJSON:', data); // Check console
        const layer = L.geoJSON(data, {
            style: { color: 'green', weight: 2, fillOpacity: 0.5 },
            onEachFeature: function(feature, layer) {
                layer.bindPopup(feature.properties.name || 'Unnamed park');
            }
        }).addTo(map);

        // Zoom to the data extent
        const bounds = layer.getBounds();
        if (bounds.isValid()) {
            map.fitBounds(bounds);
            console.log('Zoomed to data bounds');
        } else {
            console.warn('No valid bounds from data');
        }
    })
    .catch(err => console.error('Fetch error:', err));
</script>
</body>
</html>

Viewing the Map

Point your browser to:

http://lin6.fritz.box/

You should now see:

• OpenStreetMap
• Munich
• Parks imported from OpenStreetMap
• Clickable polygons
• Popups displaying park names

Unlike many tutorials, every object displayed comes directly from your OpenStreetMap import.

Understanding the Data Flow

The request follows a simple path.

Browser

↓

Leaflet

↓

parks.php

↓

PostgreSQL

↓

PostGIS

↓

ST_AsGeoJSON()

↓

GeoJSON

↓

Leaflet

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

[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

[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

[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.

Read More

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.

Read More

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.

Read More