DEV Community: Vahid Yousefzadeh

Duplicating a PDB into Another CDB

Vahid Yousefzadeh — Wed, 30 Sep 2026 08:25:09 +0000

In Oracle Database 12c, it is possible to use the DUPLICATE command at the PDB level. However, there is a limitation: a new CDB must also be created as part of the duplication process. Otherwise, the operation fails with an error.

[oracle@cdb2 ~]$ rman target sys/sys@cdb1  auxiliary sys/sys@cdb2
connected to target database: cdb1 (DBID=4178530773)
connected to auxiliary database: cdb2 (DBID=839691519)

RMAN> DUPLICATE DATABASE TO CDB2 PLUGGABLE DATABASE pdb12c;
RMAN-05500: the auxiliary database must be not mounted when issuing a DUPLICATE command

Starting with Oracle Database 18c, a PDB can be duplicated into an existing CDB. This is a new capability in Oracle Database 18c. The following example demonstrates how to perform this operation.

In this example, the PDB18C PDB in CDB1 is duplicated into CDB2.

During the duplication process, changes may occur in the source PDB (CDB1). At the end of the duplication, these changes are applied to the target PDB (CDB2) through archived redo logs.

Therefore, before starting the duplication, you must specify the location where the archived redo logs transferred from CDB1 to CDB2 will be stored. This can be configured using the REMOTE_RECOVERY_FILE_DEST parameter.

CDB2

[oracle@cdb2 ~]$ mkdir /18c/remote_arch

SQL> alter system set REMOTE_RECOVERY_FILE_DEST=’/18c/remote_arch’;

System altered.

After setting this parameter, use RMAN to duplicate PDB18C by running the DUPLICATE PLUGGABLE DATABASE command.

CDB2

[oracle@cdb2 ~]$ rman target sys/sys@CDB1 auxiliary sys/sys@cdb2
Recovery Manager: Release 18.0.0.0.0 – Production on Wed Jun 27 10:14:44 2018
Version 18.1.0.0.0
Copyright (c) 1982, 2018, Oracle and/or its affiliates.  All rights reserved.
connected to target database: CDB1 (DBID=961316182)
connected to auxiliary database: CDB2 (DBID=1453270123)

RMAN>DUPLICATE PLUGGABLE DATABASE Pdb18c TO CDB2 FROM ACTIVE DATABASE;

Starting Duplicate PDB at 27-JUN-18
contents of Memory Script:
{
   set newname for clone datafile  60 to new;
   set newname for clone datafile  61 to new;
   set newname for clone datafile  62 to new;
   restore  from  nonsparse   clone foreign pluggable database “PDB18C”  from service  ‘CDB1’   ;
}
channel ORA_AUX_DISK_1: using network backup set from service CDB1
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring foreign file 62 to /u02/18c/base/oradata/CDB2/6F99DAD756624AF9E0530488200A4A05/datafile/o1_mf_undotbs1_fjt6gfbd_.dbf
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
Finished restore at 27-JUN-18
contents of Memory Script:
{
   set archivelog destination to  ‘/18c/remote_arch’;
   restore clone force from service  ‘CDB1’ foreign archivelog from scn  19590084;
}
executing command: SET ARCHIVELOG DESTINATION
Starting restore at 27-JUN-18
archived log destination=/18c/remote_arch
channel ORA_AUX_DISK_1: using network backup set from service CDB1
channel ORA_AUX_DISK_1: restoring archived log
archived log thread=1 sequence=203
Finished Duplicate PDB at 27-JUN-18

Changes in the Source CDB

The alert log of CDB1 shows that redo was generated and archived while the PDB duplication was in progress:

CDB1 — Alert Log

2018-06-27T13:12:33.969208+04:30

ALTER SYSTEM ARCHIVE LOG

2018-06-27T13:13:10.553587+04:30

NET  (PID:24220): Archived Log entry 263 added for T-1.S-202 ID 0x3959dcd8 LAD:1

2018-06-27T13:13:10.582768+04:30

ALTER SYSTEM ARCHIVE LOG

2018-06-27T13:13:10.588942+04:30

Thread 1 advanced to log sequence 204 (LGWR switch)

  Current log# 2 seq# 204 mem# 0: /u01/18c_oradata/CDB1/onlinelog/o1_mf_2_fd0wlk11_.log

2018-06-27T13:13:10.591765+04:30

NET  (PID:24220): Archived Log entry 264 added for T-1.S-203 ID 0x3959dcd8 LAD:1

These archived redo logs are transferred to CDB2 and used during the duplication process.

Changes in the Target CDB

The alert log of CDB2 shows the creation and initialization of the cloned PDB.

CDB2 — Alert Log

Full restore complete of datafile 62 to datafile copy /u02/18c/base/oradata/CDB2/6F99DAD756624AF9E0530488200A4A05/datafile/o1_mf_undotbs1_fjt6gfbd_.dbf.  Elapsed time: 0:00:00

  checkpoint is 19590167

  last deallocation scn is 19577386

  Undo Optimization current scn is 19587396

2018-06-27T13:13:24.011604+04:30

CREATE PLUGGABLE DATABASE Pdb18c AS CLONE USING ‘/u02/18c/home/dbs/_rm_pdb_pitr_7_cdb2.xml’        SOURCE_FILE_NAME_CONVERT = ( ‘/u01/18c_oradata/CDB1/6F99DAD756624AF9E0530488200A4A05/datafile/o1_mf_temp_fm67zx39_.dbf’ , ‘/u02/18c/base/oradata/CDB2/6F99DAD756624AF9E0530488200A4A05/datafile/cl_Bm_mf_temp_fm67zx39_.dbf’,‘/u01/18c_oradata/CDB1/6F99DAD756624AF9E0530488200A4A05/datafile/o1_mf_system_fm67zx30_.dbf’ , ‘/u02/18c/base/oradata/CDB2/6F99DAD756624AF9E0530488200A4A05/datafile/o1_mf_system_l0t6gfam_.dbf’  ,  ‘/u01/18c_oradata/CDB1/6F99DAD756624AF9E0530488200A4A05/datafile/o1_mf_sysaux_fm67zx38_.dbf’ , ‘/u02/18c/base/oradata/CDB2/6F99DAD756624AF9E0530488200A4A05/datafile/o1_mf_sysaux_lvt6gfau_.dbf’  ,  ‘/u01/18c_oradata/CDB1/6F99DAD756624AF9E0530488200A4A05/datafile/o1_mf_undotbs1_fm67zx39_.dbf’ , ‘/u02/18c/base/oradata/CDB2/6F99DAD756624AF9E0530488200A4A05/datafile/o1_mf_undotbs1_fjt6gfbd_.dbf’ ) NOCOPY REFRESH MODE MANUAL

2018-06-27T13:13:24.226142+04:30

PDB18C(5):ALTER SYSTEM SET remote_recovery_file_dest=’/18c/remote_arch’ SCOPE=SPFILE PDB=’PDB18C’;

PDB18C(5):alter pluggable database refresh

PDB18C(5):Completed: alter pluggable database refresh

PDB18C(5):alter pluggable database refresh mode none

PDB18C(5):Undo initialization recovery: err:0 start: 351874245 end: 351874276 diff: 31 ms (0.0 seconds)

PDB18C(5):[7385] Successfully onlined Undo Tablespace 2.

PDB18C(5):Undo initialization online undo segments: err:0 start: 351874276 end: 351874287 diff: 11 ms (0.0 seconds)

PDB18C(5):Database Characterset for PDB18C is AL32UTF8

PDB18C(5):JIT: pid 7385 requesting stop

PDB18C(5):While transitioning the pdb 5 to clean state, clearing all its abort bits in the control file.

PDB18C(5):Completed: alter pluggable database refresh mode none

PDB18C(5):alter pluggable database open

PDB18C(5):Undo initialization recovery: err:0 start: 351875056 end: 351875060 diff: 4 ms (0.0 seconds)

PDB18C(5):[7385] Successfully onlined Undo Tablespace 2.

PDB18C(5):Undo initialization online undo segments: err:0 start: 351875061 end: 351875085 diff: 24 ms (0.0 seconds)

PDB18C(5):Undo initialization finished serial:0 start:351875056 end:351875086 diff:30 ms (0.0 seconds)

Opatch validation is skipped for PDB PDB18C (con_id=5)

PDB18C(5):Adding new file#37 to file$(old file#60).             fopr-0, newblks-32000, oldblks-19200

PDB18C(5):Adding new file#38 to file$(old file#61).             fopr-0, newblks-47360, oldblks-15360

PDB18C(5):Adding new file#39 to file$(old file#62).             fopr-0, newblks-12800, oldblks-12800

PDB18C(5):Successfully created internal service PDB18C at open

Pluggable database PDB18C opened in upgrade mode

PDB18C(5):Completed: alter pluggable database open

PDB18C(5):alter pluggable database close immediate

Pluggable database PDB18C closed

PDB18C(5):Completed: alter pluggable database close immediate

PDB18C(5):alter pluggable database open

PDB18C(5):[7385] Successfully onlined Undo Tablespace 2.

PDB18C(5):Database Characterset for PDB18C is AL32UTF8

Pluggable database PDB18C opened read write

PDB18C(5):Completed: alter pluggable database open

Verify the Duplicated PDB

You can connect to CDB2 and verify that PDB18C has been created successfully and is open in read/write mode:

CDB2
SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED

———- —————————— ———- ———-

         2 PDB$SEED                       READ ONLY  NO

         5 PDB18C                         READ WRITE NO

As shown above, PDB18C is available in CDB2 and is open in READ WRITE mode.

Remove the Archived Redo Logs

After the duplication is complete, you can remove the archived redo logs that were transferred to CDB2 for the duplication operation.

[root@cdb2 ~]# cd /18c/remote_arch
[root@cdb2 remote_arch]# ls -l
-rw-r—– 1 oracle oinstall 24064 Jun 27 13:13 1_202_972296216.dbf
-rw-r—– 1 oracle oinstall  1024 Jun 27 13:13 1_203_972296216.dbf

[root@cdb2 remote_arch]# rm -rf /18c/remote_arch/*

Changing the PDB Name During Duplication

You can also specify a different name for the duplicated PDB in CDB2.

For example, the following command duplicates PDB18C from CDB1 and creates the PDB as PDB18C_NEW in CDB2:

DUPLICATE PLUGGABLE DATABASE Pdb18c as pdb18c_new TO CDB2 FROM ACTIVE DATABASE;

Oracle AI Database 26ai(23.26.3) - Controlling PDB Redo Generation with REDO_GENERATION_KBPS_MAX

Vahid Yousefzadeh — Mon, 28 Sep 2026 19:29:01 +0000

In a container database environment, one PDB can sometimes generate a huge amount of redo in a short period of time. This excessive redo generation can consume shared resources and potentially affect the performance of other PDBs.

In other words, one PDB can become a resource bully, while other PDBs become the victims.

Oracle AI Database 26ai introduces REDO_GENERATION_KBPS_MAX, a parameter that allows us to control the target redo generation rate of an individual PDB.

When a PDB exceeds the configured target, Oracle can throttle its sessions to keep the redo generation rate close to the configured limit.

To demonstrate this behavior, I used a simple CTAS operation and compared its execution time with and without a redo-generation limit.

First, I ran the operation without any redo-generation limit.

SQL> create table tb2 as select * from tb1;
Table created
Executed in 18.315 seconds

The operation completed in only 18.315 seconds. This session had generated more than 719,802 MB of redo:

SELECT round(value / 1024 / 1024) REDO_SIZE_KB
FROM v$sesstat st
JOIN v$statname sn
ON sn.statistic# = st.statistic#
WHERE st.sid = 221
AND sn.name = 'redo size';

REDO_SIZE_KB
 - - - - - - 
719802

This gives us a baseline for the workload.

Limiting Redo Generation
To demonstrate this feature, I wanted to strictly limit the PDB so that the CTAS operation would take at least three minutes to complete. I configured the PDB with a target maximum redo generation rate of 4,000 KB/s:

SQL> ALTER SYSTEM SET redo_generation_kbps_max = 4000;
System altered

I removed the table and ran exactly the same CTAS operation again:

SQL> drop table tb2;
Table dropped

SQL> create table tb2 as select * from tb1;
Table created
Executed in 190.017 seconds

The result was very different. The same operation that previously required:

18.315 seconds

now required:

190.017 seconds

That’s more than 10 times slower.

While the second CTAS was running, I checked the session wait event:

SQL> select event from v$session_wait where sid=221;
EVENT
 - - - - - - - - - - - - -
resmgr: redo throttle

This is the key observation in this experiment.

I I generated an AWR report, and the results were as follows:

84.7% of the database time was spent on resmgr: redo throttle.

This clearly shows that the session was spending most of its database time being throttled by Resource Manager because it exceeded the configured redo generation limit.

PostgreSQL Table Bloat and Defragmentation: Autovacuum vs VACUUM vs VACUUM FULL

Vahid Yousefzadeh — Mon, 28 Sep 2026 19:20:37 +0000

When rows are updated or deleted in PostgreSQL, the database does not immediately remove the old row versions from the table. Instead, PostgreSQL uses MVCC (Multi-Version Concurrency Control) and creates new row versions when necessary.

As a result, a table can contain dead tuples and occupy significantly more disk space than the amount required by its current live data.

In this article, we will use the same table and workload to demonstrate how PostgreSQL handles this situation with:

Autovacuum: Automatically removes dead tuples in the background and makes their space reusable. It does not require an exclusive table lock and normally does not shrink the table on disk.
Manual VACUUM: Does the same cleanup as autovacuum, but you start it manually. It removes dead tuples and makes space reusable, but normally does not shrink the table.It does not require an exclusive table lock.
VACUUM FULL: Rebuilds the table using only live rows and physically releases unused disk space. It requires an ACCESS EXCLUSIVE lock, blocking concurrent access to the table while it runs.
The important point is that these operations do not have the same effect on table size.

1. Initial Table

First, we create a table containing 2 million rows.


postgres=# CREATE TABLE tbl_fragmentation (
postgres(#     id          BIGSERIAL PRIMARY KEY,
postgres(#     name        TEXT,
postgres(#     description TEXT,
postgres(#     amount      NUMERIC(12,2),
postgres(#     created_at  TIMESTAMP DEFAULT now()
postgres(# );
CREATE TABLE


postgres=# INSERT INTO tbl_fragmentation (name, description, amount)
postgres-# SELECT
postgres-#     'User_' || gs,
postgres-#     repeat('X', 500),
postgres-#     (random() * 10000)::numeric(12,2)
postgres-# FROM generate_series(1, 2000000) AS gs;
INSERT 0 2000000
Time: 48542.364 ms (00:48.542)


postgres=# SELECT count(*) FROM tbl_fragmentation;
  count
---------
 2000000
(1 row)

The table initially occupies approximately 1116 MB.


postgres=# \dt+
                                           List of tables
 Schema |       Name        | Type  |  Owner   | Persistence | Access method |  Size   | Description
--------+-------------------+-------+----------+-------------+---------------+---------+-------------
 public | tbl_fragmentation | table | postgres | permanent   | heap          | 1116 MB |
(1 row)

At this point, the table contains 2 million live rows and there is no significant dead-tuple accumulation.

2. Table Defragmentation Using Autovacuum

PostgreSQL enables autovacuum by default.

postgres=# SHOW autovacuum;
 autovacuum
------------
 on

Now we delete 1.8 million rows.

postgres=# DELETE FROM tbl_fragmentation WHERE id <= 1800000;
DELETE 1800000
Time: 27884.584 ms (00:27.885)

Only 200,000 rows remain:

postgres=# SELECT count(*)FROM tbl_fragmentation;
 count
--------
 200000
(1 row)

However, the physical table size is still approximately 1116 MB.

postgres=# \dt+
                                           List of tables
 Schema |       Name        | Type  |  Owner   | Persistence | Access method |  Size   | Description
--------+-------------------+-------+----------+-------------+---------------+---------+-------------
 public | tbl_fragmentation | table | postgres | permanent   | heap          | 1116 MB |
(1 row)

A DELETE does not immediately return the occupied pages to the operating system. The deleted rows become dead tuples, and the space can later be reused by PostgreSQL.

Autovacuum detects the dead tuples and starts a vacuum operation.


postgres=# SELECT    pid,    backend_type,    state,    wait_event_type,    wait_event,    now() - query_start AS running_for,    query FROM pg_stat_activity WHERE backend_type LIKE 'autovacuum%' and state='active';
  pid  |   backend_type    | state  | wait_event_type | wait_event  |   running_for   |                    query
-------+-------------------+--------+-----------------+-------------+-----------------+---------------------------------------------
 14080 | autovacuum worker | active | Timeout         | VacuumDelay | 00:07:26.277602 | autovacuum: VACUUM public.tbl_fragmentation

We can check the table statistics:


SELECT
    n_live_tup,
    n_dead_tup,
    last_autovacuum,
    autovacuum_count
FROM pg_stat_user_tables
WHERE relname = 'tbl_fragmentation';
 n_live_tup | n_dead_tup |         last_autovacuum          | autovacuum_count
------------+------------+----------------------------------+------------------
      19889 |          0 | 2026-09-15 22:17:42.548493+03:30 |                2

The important observation is that n_dead_tup is now zero after autovacuum.

However, the table size has not decreased.


postgres=# \dt+
                                           List of tables
 Schema |       Name        | Type  |  Owner   | Persistence | Access method |  Size   | Description
--------+-------------------+-------+----------+-------------+---------------+---------+-------------
 public | tbl_fragmentation | table | postgres | permanent   | heap          | 1116 MB |
(1 row)

This illustrates an important PostgreSQL behavior:

VACUUM makes space occupied by dead tuples reusable, but normally does not shrink the physical table file.

We can demonstrate this by inserting another 1.8 million rows.


 INSERT INTO tbl_fragmentation (name, description, amount)
 SELECT
     'User_' || gs,
     repeat('X', 500),
     (random() * 10000)::numeric(12,2)
 FROM generate_series(2000001, 3800000) AS gs;
INSERT 0 1800000


postgres=# select count(*) from tbl_fragmentation;
  count
---------
 2000000


postgres=# \dt+
                                           List of tables
 Schema |       Name        | Type  |  Owner   | Persistence | Access method |  Size   | Description
--------+-------------------+-------+----------+-------------+---------------+---------+-------------
 public | tbl_fragmentation | table | postgres | permanent   | heap          | 1116 MB |

The space that previously belonged to deleted rows was made available for reuse, so PostgreSQL could insert new rows without requiring the table file to grow.

The table statistics also show that the dead tuples were cleaned:


 SELECT
     n_live_tup,
     n_dead_tup,
     last_autovacuum,
     autovacuum_count
 FROM pg_stat_user_tables
 WHERE relname = 'tbl_fragmentation';
 n_live_tup | n_dead_tup |         last_autovacuum          | autovacuum_count
------------+------------+----------------------------------+------------------
    1999955 |          0 | 2026-09-15 22:27:41.696749+03:30 |                3
(1 row)

What happens with UPDATE?

UPDATE is particularly important because PostgreSQL normally implements an update by creating a new row version rather than modifying the existing row in place.

We now update 1.8 million rows:


postgres=# select count(*) from tbl_fragmentation;
  count
---------
 2000000
(1 row)



postgres=# \dt+
                                           List of tables
 Schema |       Name        | Type  |  Owner   | Persistence | Access method |  Size   | Description
--------+-------------------+-------+----------+-------------+---------------+---------+-------------
 public | tbl_fragmentation | table | postgres | permanent   | heap          | 1116 MB |
(1 row)



postgres=# UPDATE tbl_fragmentation
postgres-# SET description = repeat('Y', 500)
postgres-# WHERE id <= 3600000;
UPDATE 1800000
Time: 76530.884 ms (01:16.531)


postgres=# \dt+
                                           List of tables
 Schema |       Name        | Type  |  Owner   | Persistence | Access method |  Size   | Description
--------+-------------------+-------+----------+-------------+---------------+---------+-------------
 public | tbl_fragmentation | table | postgres | permanent   | heap          | 2121 MB |
(1 row)



postgres=# select count(*) from tbl_fragmentation;
  count
---------
 2000000
(1 row)



postgres=# SELECT    pid,    backend_type,    state,    wait_event_type,    wait_event,    now() - query_start AS running_for,    query FROM pg_stat_activity WHERE backend_type LIKE 'autovacuum%' and state='active';
  pid  |   backend_type    | state  | wait_event_type | wait_event  |   running_for   |                        query
-------+-------------------+--------+-----------------+-------------+-----------------+-----------------------------------------------------
 12604 | autovacuum worker | active | Timeout         | VacuumDelay | 00:08:47.232972 | autovacuum: VACUUM ANALYZE public.tbl_fragmentation
(1 row)



postgres=# \dt+
                                           List of tables
 Schema |       Name        | Type  |  Owner   | Persistence | Access method |  Size   | Description
--------+-------------------+-------+----------+-------------+---------------+---------+-------------
 public | tbl_fragmentation | table | postgres | permanent   | heap          | 2121 MB |
(1 row)



SELECT
     n_live_tup,
     n_dead_tup,
     last_autovacuum,
     autovacuum_count
 FROM pg_stat_user_tables
 WHERE relname = 'tbl_fragmentation';
 n_live_tup | n_dead_tup |         last_autovacuum          | autovacuum_count
------------+------------+----------------------------------+------------------
    2005317 |      53580 | 2026-09-15 22:45:08.785006+03:30 |                4
(1 row)

The key observation is that the update generated dead tuples. Autovacuum can clean these dead tuples and make their space reusable, but it does not normally compact the entire table and return the unused space to the operating system.

3. Manual VACUUM

Now we disable autovacuum for the test table so that we can manually control when vacuum occurs.


postgres=# DROP TABLE IF EXISTS tbl_fragmentation;
DROP TABLE

postgres=# CREATE TABLE tbl_fragmentation (
postgres(#     id          BIGSERIAL PRIMARY KEY,
postgres(#     name        TEXT,
postgres(#     description TEXT,
postgres(#     amount      NUMERIC(12,2),
postgres(#     created_at  TIMESTAMP DEFAULT now()
postgres(# );
CREATE TABLE

postgres=# ALTER TABLE tbl_fragmentation SET (autovacuum_enabled = false);
ALTER TABLE


postgres=# INSERT INTO tbl_fragmentation (name, description, amount)
postgres-# SELECT
postgres-#     'User_' || gs,
postgres-#     repeat('X', 500),
postgres-#     (random() * 10000)::numeric(12,2)
postgres-# FROM generate_series(1, 2000000) AS gs;
INSERT 0 2000000
Time: 49524.589 ms (00:49.525)


postgres=# SELECT count(*) FROM tbl_fragmentation;
  count
---------
 2000000
(1 row)

The table is approximately 1116 MB:


postgres=# \dt+
                                           List of tables
 Schema |       Name        | Type  |  Owner   | Persistence | Access method |  Size   | Description
--------+-------------------+-------+----------+-------------+---------------+---------+-------------
 public | tbl_fragmentation | table | postgres | permanent   | heap          | 1116 MB |
(1 row)

Now we update 1.8 million rows.


postgres=# UPDATE tbl_fragmentation
postgres-# SET description = repeat('Y', 500)
postgres-# WHERE id <= 1800000;
UPDATE 1800000
Time: 69229.523 ms (01:09.230)


postgres=# SELECT count(*) FROM tbl_fragmentation;
  count
---------
 2000000
(1 row)

The table grows to 2121 MB:


postgres=# \dt+
                                           List of tables
 Schema |       Name       | Type  |  Owner   | Persistence | Access method |  Size   | Description
--------+------------------+-------+----------+-------------+---------------+---------+-------------
 public | tbl_fragmentation | table | postgres | permanent   | heap          | 2121 MB |
(1 row)

The statistics show approximately 1.79 million dead tuples:


postgres=# SELECT
postgres-#     n_live_tup,
postgres-#     n_dead_tup,
postgres-#     last_vacuum,
postgres-#     vacuum_count
postgres-# FROM pg_stat_user_tables
postgres-# WHERE relname = 'tbl_fragmentation';
 n_live_tup | n_dead_tup | last_vacuum | vacuum_count
------------+------------+-------------+--------------
    2006910 |    1793096 |             |            0
(1 row)

Now we manually execute:


postgres=# VACUUM tbl_fragmentation;
VACUUM
Time: 24971.367 ms (00:24.971)
After vacuum:

postgres=# SELECT
postgres-#     n_live_tup,
postgres-#     n_dead_tup,
postgres-#     last_vacuum,
postgres-#     vacuum_count
postgres-# FROM pg_stat_user_tables
postgres-# WHERE relname = 'tbl_fragmentation';
 n_live_tup | n_dead_tup |           last_vacuum            | vacuum_count
------------+------------+----------------------------------+--------------
    1904225 |          0 | 2026-09-15 23:05:24.632805+03:30 |            1
(1 row)

The dead tuples have been cleaned. However, the table is still 2121 MB:


postgres=# \dt+
                                           List of tables
 Schema |       Name        | Type  |  Owner   | Persistence | Access method |  Size   | Description
--------+-------------------+-------+----------+-------------+---------------+---------+-------------
 public | tbl_fragmentation | table | postgres | permanent   | heap          | 2121 MB |
(1 row)

This is the same fundamental behavior we observed with autovacuum.

Manual VACUUM and autovacuum both reclaim dead-tuple space for reuse; neither is intended to compact the table file and return all unused space to the operating system.

Now we delete 1.8 million rows:

postgres=# DELETE FROM tbl_fragmentation WHERE id <= 1800000;
DELETE 1800000
Time: 29234.591 ms (00:29.235)
postgres=# \dt+
                                           List of tables
 Schema |       Name        | Type  |  Owner   | Persistence | Access method |  Size   | Description
--------+-------------------+-------+----------+-------------+---------------+---------+-------------
 public | tbl_fragmentation | table | postgres | permanent   | heap          | 2121 MB |
(1 row)


postgres=# SELECT
postgres-#     n_live_tup,
postgres-#     n_dead_tup,
postgres-#     last_vacuum,
postgres-#     vacuum_count
postgres-# FROM pg_stat_user_tables
postgres-# WHERE relname = 'tbl_fragmentation';
 n_live_tup | n_dead_tup |           last_vacuum            | vacuum_count
------------+------------+----------------------------------+--------------
     105605 |    1800000 | 2026-09-15 23:22:02.167342+03:30 |            1
(1 row)


postgres=# VACUUM tbl_fragmentation;
VACUUM
Time: 25668.475 ms (00:25.668)

postgres=# \dt+
                                           List of tables
 Schema |       Name        | Type  |  Owner   | Persistence | Access method |  Size   | Description
--------+-------------------+-------+----------+-------------+---------------+---------+-------------
 public | tbl_fragmentation | table | postgres | permanent   | heap          | 1116 MB |
(1 row)

postgres=# SELECT count(*) FROM tbl_fragmentation;
 count
--------
 200000
(1 row)

The table size is shown as 1116 MB after this operation in the test output:

2121 MB →1116 MB

4. VACUUM FULL

VACUUM FULL has a fundamentally different purpose.

It physically rewrites the table and creates a compact version containing the live rows.

Again, we create the test table with autovacuum disabled and repeat the scenario:


postgres=# DROP TABLE IF EXISTS tbl_fragmentation;
DROP TABLE
Time: 269.546 ms
postgres=# CREATE TABLE tbl_fragmentation (
postgres(#     id          BIGSERIAL PRIMARY KEY,
postgres(#     name        TEXT,
postgres(#     description TEXT,
postgres(#     amount      NUMERIC(12,2),
postgres(#     created_at  TIMESTAMP DEFAULT now()
postgres(# );
CREATE TABLE
Time: 7.112 ms

postgres=# INSERT INTO tbl_fragmentation (name, description, amount)
postgres-# SELECT
postgres-#     'User_' || gs,
postgres-#     repeat('X', 500),
postgres-#     (random() * 10000)::numeric(12,2)
postgres-# FROM generate_series(1, 2000000) AS gs;
INSERT 0 2000000
Time: 48542.364 ms (00:48.542)
postgres=# SELECT count(*) FROM tbl_fragmentation;
  count
---------
 2000000
(1 row)


Time: 28598.005 ms (00:28.598)
postgres=# \dt+
                                           List of tables
 Schema |       Name        | Type  |  Owner   | Persistence | Access method |  Size   | Description
--------+-------------------+-------+----------+-------------+---------------+---------+-------------
 public | tbl_fragmentation | table | postgres | permanent   | heap          | 1116 MB |
(1 row)


postgres=# UPDATE tbl_fragmentation
postgres-# SET description = repeat('Y', 500)
postgres-# WHERE id <= 1800000;
UPDATE 1800000
Time: 67174.851 ms (01:07.175)

postgres=# SELECT count(*) FROM tbl_fragmentation;
  count
---------
 2000000
(1 row)


Time: 54238.243 ms (00:54.238)
postgres=# \dt+
                                           List of tables
 Schema |       Name        | Type  |  Owner   | Persistence | Access method |  Size   | Description
--------+-------------------+-------+----------+-------------+---------------+---------+-------------
 public | tbl_fragmentation | table | postgres | permanent   | heap          | 2121 MB |
(1 row)


postgres=# SELECT
postgres-#     n_live_tup,
postgres-#     n_dead_tup,
postgres-#     last_vacuum,
postgres-#     vacuum_count
postgres-# FROM pg_stat_user_tables
postgres-# WHERE relname = 'tbl_fragmentation';
 n_live_tup | n_dead_tup | last_vacuum | vacuum_count
------------+------------+-------------+--------------
    1990950 |    1809056 |             |            0
(1 row)


Time: 1.124 ms

Now we execute VACUUM FULL command:

postgres=# VACUUM FULL tbl_fragmentation;
VACUUM
Time: 34663.258 ms (00:34.663)
After VACUUM FULL, the table is physically compacted:

postgres=# SELECT
postgres-#     n_live_tup,
postgres-#     n_dead_tup,
postgres-#     last_vacuum,
postgres-#     vacuum_count
postgres-# FROM pg_stat_user_tables
postgres-# WHERE relname = 'tbl_fragmentation';
 n_live_tup | n_dead_tup | last_vacuum | vacuum_count
------------+------------+-------------+--------------
    1990950 |    1809056 |             |            0
(1 row)



postgres=# \dt+
                                           List of tables
 Schema |       Name        | Type  |  Owner   | Persistence | Access method |  Size   | Description
--------+-------------------+-------+----------+-------------+---------------+---------+-------------
 public | tbl_fragmentation | table | postgres | permanent   | heap          | 1116 MB |
(1 row)

In this particular test, the table returns to approximately its original size.

Now we delete 1.8 million rows again:


postgres=# DELETE FROM tbl_fragmentation WHERE id <= 1800000;
DELETE 1800000
Time: 29704.323 ms (00:29.704)
postgres=# SELECT count(*)FROM tbl_fragmentation;
 count
--------
 200000
(1 row)


Time: 25574.595 ms (00:25.575)
postgres=# \dt+
                                           List of tables
 Schema |       Name        | Type  |  Owner   | Persistence | Access method |  Size   | Description
--------+-------------------+-------+----------+-------------+---------------+---------+-------------
 public | tbl_fragmentation | table | postgres | permanent   | heap          | 1116 MB |
(1 row)


postgres=# VACUUM FULL tbl_fragmentation;
VACUUM
Time: 4336.966 ms (00:04.337)
postgres=# \dt+
                                           List of tables
 Schema |       Name        | Type  |  Owner   | Persistence | Access method |  Size  | Description
--------+-------------------+-------+----------+-------------+---------------+--------+-------------
 public | tbl_fragmentation | table | postgres | permanent   | heap          | 112 MB |
(1 row)

The table has now shrunk from approximately 1116 MB to 112 MB.

This is the main difference between normal VACUUM and VACUUM FULL.

VACUUM FULL physically rebuilds the table using only the remaining live rows, allowing PostgreSQL to release the unused disk space associated with the old table.

Space Reclamation and Execution Time Comparison

In this test, autovacuum took several minutes because it runs in the background and intentionally throttles its work to reduce its impact on normal database activity. Manual VACUUM completed much faster because it was started directly without waiting for autovacuum scheduling. VACUUM FULL also completed quickly in this test and physically compacted the table, significantly reducing its size.

Migrating PostgreSQL 14 to 18 Using pg_dumpall

Vahid Yousefzadeh — Mon, 28 Sep 2026 18:55:11 +0000

A major PostgreSQL version upgrade is not only a matter of installing the new server. The migration also requires a clear understanding of the existing environment, a verified backup, careful restoration, and post-upgrade validation. In this article, I walk through a PostgreSQL 14 to PostgreSQL 18 migration using pg_dumpall, based on a real environment.

1. Review the Current PostgreSQL 14 Environment

Before starting the upgrade, I record the current PostgreSQL version, data directory, databases, database sizes, tables, indexes, and installed extensions. These checks give me a reference point for validating the PostgreSQL 18 environment later.


--Current version:
postgres=# select version();
                                                  version
-----------------------------------------------------------------------------------------------------------
 PostgreSQL 14.19 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 11.5.0 20240719 (Red Hat 11.5.0-5), 64-bit
(1 row)


--data directory:
postgres=# show data_directory;
     data_directory
------------------------
 /var/lib/pgsql/14/data
(1 row)

--list of databases:
postgres=# \l
                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 usef      | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 vahid     | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
(5 rows)

The total database size is useful for estimating the amount of data involved in the migration and for checking whether the restored environment is in the expected range.

At first, I want to verify the total size of all databases:


postgres=# SELECT
    ROUND(SUM(pg_database_size(datname)) / 1024.0 / 1024.0, 2) AS total_size_mb
FROM pg_database;
 total_size_mb
---------------
       3494.09
(1 row)

It’s approximately 3.5GB, in addition, the following query shows exact size of each database:

postgres=# SELECT
    datname AS database_name,
    ROUND(pg_database_size(datname) / 1024.0 / 1024.0, 2) AS size_mb
FROM pg_database
ORDER BY pg_database_size(datname) DESC;
 database_name | size_mb
---------------+---------
 vahid         | 3460.68
 usef          |    8.49
 postgres      |    8.38
 template1     |    8.23
 template0     |    8.23
(5 rows)

I then drill into the largest database and record the size of its tables and indexes. This also helps identify the objects that should be checked most carefully after the restore.

The biggest database is VAHID and we check the tables which are in this database:

postgres=# \c vahid
You are now connected to database "vahid" as user "postgres".
vahid=# \d+
                                                List of relations
 Schema |           Name           |   Type   |  Owner   | Persistence | Access method |    Size    | Description
--------+--------------------------+----------+----------+-------------+---------------+------------+-------------
 public | tb_emsal                 | table    | postgres | permanent   | heap          | 2233 MB    |
 public | tbl_fragmentation        | table    | postgres | permanent   | heap          | 1116 MB    |
 public | tbl_fragmentation_id_seq | sequence | postgres | permanent   |               | 8192 bytes |
(3 rows)

vahid=# \di+
                                                     List of relations
 Schema |          Name          | Type  |  Owner   |       Table       | Persistence | Access method | Size  | Description
--------+------------------------+-------+----------+-------------------+-------------+---------------+-------+-------------
 public | indname                | index | postgres | tbl_fragmentation | permanent   | btree         | 60 MB |
 public | tbl_fragmentation_pkey | index | postgres | tbl_fragmentation | permanent   | btree         | 43 MB |
(2 rows)

Moreover, we can examine tables and indexes which are in USEF database:

vahid=# \c usef
You are now connected to database "usef" as user "postgres".
usef=# \dt+
                                  List of relations
 Schema | Name | Type  |  Owner   | Persistence | Access method | Size  | Description
--------+------+-------+----------+-------------+---------------+-------+-------------
 public | tbl  | table | postgres | permanent   | heap          | 16 kB |
(1 row)

usef=# \di+
                                        List of relations
 Schema |   Name   | Type  |  Owner   | Table | Persistence | Access method | Size  | Description
--------+----------+-------+----------+-------+-------------+---------------+-------+-------------
 public | indname  | index | postgres | tbl   | permanent   | btree         | 16 kB |
 public | tbl_pkey | index | postgres | tbl   | permanent   | btree         | 16 kB |
(2 rows)

usef=# select * from tbl;
 id | fname |    lname
----+-------+-------------
  1 | Vahid | Yousefzadeh
(1 row)

Finally, I check the POSTGRES database:

postgres=# \dt+
                                    List of relations
 Schema |  Name   | Type  |  Owner   | Persistence | Access method | Size  | Description
--------+---------+-------+----------+-------------+---------------+-------+-------------
 public | person  | table | postgres | permanent   | heap          | 16 kB |
 public | tb_1405 | table | postgres | permanent   | heap          | 16 kB |
(2 rows)

postgres=# \di+
                                          List of relations
 Schema |    Name     | Type  |  Owner   | Table  | Persistence | Access method | Size  | Description
--------+-------------+-------+----------+--------+-------------+---------------+-------+-------------
 public | person_pkey | index | postgres | person | permanent   | btree         | 16 kB |
(1 row)

postgres=# select * from person;
 id |       name
----+-------------------
  1 | Vahid Yousefzadeh
(1 row)

2.Check Installed Extensions

Extensions deserve a separate check because they are installed per database. A successful database restore does not remove the need to verify that the required extension objects are available in the target PostgreSQL installation.

[postgres@OEL98-PG ~]$ psql
psql (14.19)
Type "help" for help.

postgres=# \dx
                 List of installed extensions
  Name   | Version |   Schema   |         Description
---------+---------+------------+------------------------------
 plpgsql | 1.0     | pg_catalog | PL/pgSQL procedural language
(1 row)

postgres=# \c vahid
You are now connected to database "vahid" as user "postgres".
vahid=# \dx
                 List of installed extensions
  Name   | Version |   Schema   |         Description
---------+---------+------------+------------------------------
 plpgsql | 1.0     | pg_catalog | PL/pgSQL procedural language
(1 row)

vahid=# \c usef
You are now connected to database "usef" as user "postgres".
usef=# \dx
                 List of installed extensions
  Name   | Version |   Schema   |         Description
---------+---------+------------+------------------------------
 plpgsql | 1.0     | pg_catalog | PL/pgSQL procedural language
(1 row)

3. Take the Logical Backup

Once the baseline has been recorded, I stop application activity and take the final logical backup. For this migration, pg_dumpall is used to capture all databases and cluster-level objects supported by the logical dump.

pg_dumpall --verbose >>/backup/ALL_Databases_14.sql

During the backup process, pg_dumpall displays the objects and data being processed. The following is a sample of the output:

pg_dump: creating DATABASE "vahid"
pg_dump: connecting to new database "vahid"
pg_dump: creating TABLE "public.tb_emsal"
pg_dump: creating TABLE "public.tbl_fragmentation"
pg_dump: creating SEQUENCE "public.tbl_fragmentation_id_seq"
pg_dump: creating SEQUENCE OWNED BY "public.tbl_fragmentation_id_seq"
pg_dump: creating DEFAULT "public.tbl_fragmentation id"
pg_dump: processing data for table "public.tb_emsal"
pg_dump: dumping contents of table "public.tb_emsal"
pg_dump: processing data for table "public.tbl_fragmentation"
pg_dump: dumping contents of table "public.tbl_fragmentation"

The backup was successfully created, and its size is approximately 3.2 GB:

[postgres@OEL98-PG ~]$ ls -lh /backup/ALL_Databases_14.sql
-rw-r--r--. 1 postgres postgres 3.2G Sep 28 18:57 /backup/ALL_Databases_14.sql

4.Stop PostgreSQL 14

After the backup is complete, PostgreSQL 14 is stopped. Keeping the old data directory untouched at this stage is important because it provides a rollback point while PostgreSQL 18 is being validated.

There are two ways to stop the PostgreSQL 14 instance. The first is to use pg_ctl directly with the PostgreSQL 14 data directory:

[postgres@OEL98-PG ~]$ pg_ctl -D /var/lib/pgsql/14/data -l logfile stop
waiting for server to shut down.... done
server stopped

Alternatively, if PostgreSQL 14 is managed by systemd, we can stop the service using:

[root@OEL98-PG ~]# systemctl stop postgresql-14
[root@OEL98-PG ~]# systemctl status postgresql-14
○ postgresql-14.service - PostgreSQL 14 database server
     Loaded: loaded (/usr/lib/systemd/system/postgresql-14.service; disabled; preset: disabled)
     Active: inactive (dead)
       Docs: https://www.postgresql.org/docs/14/static/

Sep 28 19:01:11 OEL98-PG systemd[1]: Starting PostgreSQL 14 database server...
Sep 28 19:01:11 OEL98-PG postmaster[2163]: 2026-09-28 19:01:11.296 +03 [2163] LOG:  redirecting log output to logging collector process
Sep 28 19:01:11 OEL98-PG postmaster[2163]: 2026-09-28 19:01:11.296 +03 [2163] HINT:  Future log output will appear in directory "log".
Sep 28 19:01:11 OEL98-PG systemd[1]: Started PostgreSQL 14 database server.
Sep 28 19:01:15 OEL98-PG systemd[1]: Stopping PostgreSQL 14 database server...
Sep 28 19:01:15 OEL98-PG systemd[1]: postgresql-14.service: Killing process 2164 (postmaster) with signal SIGKILL.
Sep 28 19:01:15 OEL98-PG systemd[1]: postgresql-14.service: Deactivated successfully.
Sep 28 19:01:15 OEL98-PG systemd[1]: Stopped PostgreSQL 14 database server.

5.Install and Initialize PostgreSQL 18

PostgreSQL 18 was installed and initialized beforehand. At this stage, I switch to the PostgreSQL 18 environment and verify the configuration, including the PostgreSQL version, data directory, and listening port.

This confirms that the new PostgreSQL 18 instance is ready for the migration and subsequent restoration of the backup.


[postgres@OEL98-PG ~]$ export PG_VERSION=18
[postgres@OEL98-PG ~]$ export PGHOME=/usr/pgsql-18
[postgres@OEL98-PG ~]$ export PGDATA=/var/lib/pgsql/18/data
[postgres@OEL98-PG ~]$ export PGPORT=5418
[postgres@OEL98-PG ~]$ export PATH=$PGHOME/bin:/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin


[postgres@OEL98-PG ~]$ psql
psql (18.6)
Type "help" for help.

postgres=# select version();
                                                  version
-----------------------------------------------------------------------------------------------------------
 PostgreSQL 18.6 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 11.5.0 20240719 (Red Hat 11.5.0-14), 64-bit
(1 row)


postgres=# show data_directory;
     data_directory
------------------------
 /var/lib/pgsql/18/data
(1 row)


postgres=# show port;
 port
------
 5418
(1 row)

6.Restore the PostgreSQL 14 Backup

The backup is now restored into PostgreSQL 18. Because this is a logical migration, PostgreSQL 18 recreates the databases, tables, sequences, and indexes from the dump rather than reusing the PostgreSQL 14 data directory.

Note: Server configuration files such as postgresql.conf and pg_hba.conf are separate from the logical dump. They should therefore be reviewed and migrated deliberately rather than assumed to be included in pg_dumpall.

The backup is restored using the PostgreSQL 18 psql executable:

/usr/pgsql-18/bin/psql -f /backup/ALL_Databases_14.sql

During the restore, psql executes the SQL statements generated by pg_dumpall. The following is a sample of the output:

You are now connected to database "vahid" as user "postgres".
SET
SET
SET
SET
SET
 set_config
------------

(1 row)

SET
SET
SET
SET
SET
SET
CREATE TABLE
ALTER TABLE
CREATE TABLE
ALTER TABLE
CREATE SEQUENCE
ALTER TABLE
ALTER SEQUENCE
ALTER TABLE
COPY 4000000
COPY 2000000
 setval
---------
 2000000
(1 row)

ALTER TABLE
CREATE INDEX

7. Validate PostgreSQL 18

After restoring the backup, I verify the PostgreSQL 18 environment against the information collected from PostgreSQL 14. This includes checking the PostgreSQL version, confirming that the expected databases exist, and comparing their sizes.

[postgres@OEL98-PG ~]$ psql
psql (18.6)
Type "help" for help.


postgres=# select version();
                                                  version
-----------------------------------------------------------------------------------------------------------
 PostgreSQL 18.6 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 11.5.0 20240719 (Red Hat 11.5.0-14), 64-bit
(1 row)


postgres=# \l
                                                     List of databases
   Name    |  Owner   | Encoding | Locale Provider |   Collate   |    Ctype    | Locale | ICU Rules |   Access privileges
-----------+----------+----------+-----------------+-------------+-------------+--------+-----------+-----------------------
 postgres  | postgres | UTF8     | libc            | en_US.UTF-8 | en_US.UTF-8 |        |           |
 template0 | postgres | UTF8     | libc            | en_US.UTF-8 | en_US.UTF-8 |        |           | =c/postgres          +
           |          |          |                 |             |             |        |           | postgres=CTc/postgres
 template1 | postgres | UTF8     | libc            | en_US.UTF-8 | en_US.UTF-8 |        |           | =c/postgres          +
           |          |          |                 |             |             |        |           | postgres=CTc/postgres
 usef      | postgres | UTF8     | libc            | en_US.UTF-8 | en_US.UTF-8 |        |           |
 vahid     | postgres | UTF8     | libc            | en_US.UTF-8 | en_US.UTF-8 |        |           |
(5 rows)

The expected databases are present in PostgreSQL 18. I then check their sizes:

postgres=# SELECT
    datname AS database_name,
    ROUND(pg_database_size(datname) / 1024.0 / 1024.0, 2) AS size_mb
FROM pg_database
ORDER BY pg_database_size(datname) DESC;
 database_name | size_mb
---------------+---------
 vahid         | 3460.72
 postgres      |    7.59
 usef          |    7.59
 template1     |    7.56
 template0     |    7.49
(5 rows)

Finally, I verify the actual objects and sample data. For a production migration, this validation should also include representative row counts, application connectivity, critical queries, permissions, and scheduled jobs.

postgres=# \d
              List of relations
 Schema |     Name      |   Type   |  Owner
--------+---------------+----------+----------
 public | person        | table    | postgres
 public | person_id_seq | sequence | postgres
 public | tb_1405       | table    | postgres
(3 rows)

postgres=# select * from person;
 id |       name
----+-------------------
  1 | Vahid Yousefzadeh
(1 row)


postgres=# \c vahid
You are now connected to database "vahid" as user "postgres".
vahid=# \dt+
                                           List of tables
 Schema |       Name        | Type  |  Owner   | Persistence | Access method |  Size   | Description
--------+-------------------+-------+----------+-------------+---------------+---------+-------------
 public | tb_emsal          | table | postgres | permanent   | heap          | 2233 MB |
 public | tbl_fragmentation | table | postgres | permanent   | heap          | 1117 MB |
(2 rows)

vahid=# \di+
                                                      List of indexes
 Schema |          Name          | Type  |  Owner   |       Table       | Persistence | Access method | Size  | Description
--------+------------------------+-------+----------+-------------------+-------------+---------------+-------+-------------
 public | indname                | index | postgres | tbl_fragmentation | permanent   | btree         | 60 MB |
 public | tbl_fragmentation_pkey | index | postgres | tbl_fragmentation | permanent   | btree         | 43 MB |
(2 rows)

vahid=# \c usef
You are now connected to database "usef" as user "postgres".
usef=# \dt+
                                    List of tables
 Schema | Name | Type  |  Owner   | Persistence | Access method | Size  | Description
--------+------+-------+----------+-------------+---------------+-------+-------------
 public | tbl  | table | postgres | permanent   | heap          | 16 kB |
(1 row)

usef=# \di+
                                         List of indexes
 Schema |   Name   | Type  |  Owner   | Table | Persistence | Access method | Size  | Description
--------+----------+-------+----------+-------+-------------+---------------+-------+-------------
 public | indname  | index | postgres | tbl   | permanent   | btree         | 16 kB |
 public | tbl_pkey | index | postgres | tbl   | permanent   | btree         | 16 kB |
(2 rows)

usef=# select * from tbl;
 id | fname |    lname
----+-------+-------------
  1 | Vahid | Yousefzadeh
(1 row)

Oracle Deep Data Security: Data Grants

Vahid Yousefzadeh — Mon, 28 Sep 2026 05:20:25 +0000

Last week, I published an article about Oracle Deep Data Security (Deep Sec) in Oracle AI Database 26ai, where I covered End Users and Data Roles and demonstrated how they work together. In this article, I want to continue from that point and focus on Data Grants.

1. What is a Data Grant?

A Data Grant is one of the main mechanisms in Oracle Deep Data Security for defining fine-grained access to data.

Instead of simply granting access to an entire table:

GRANT SELECT ON table TO user;

a Data Grant can define which rows a user can access, which columns are available, and which operations the user can perform, such as SELECT, UPDATE, or DELETE.

2. Test Environment

For the practical tests, I created an ADMIN.DATABASE_ACCESS_REQUESTS table containing access requests from several end users. This is the main table used throughout the examples.

SQL> select * from admin.database_access_requests;

REQUEST_ID USERNAME   DATABASE_NAME PDB_NAME        ENVIRONMENT          ACCESS_TYPE       REQUEST_STATUS       REQUESTED
---------- ---------- ------------- --------------- -------------------- ----------------- -------------------- ---------
      1001 VAHID_EU      PRODDB        PARS_PDB        PRODUCTION           DBA               APPROVED             01-SEP-26
      1002 VAHID_EU      PRODDB        DERAZKASH_PDB   PRODUCTION           SELECT ANY TABLE  APPROVED             02-SEP-26
      1003 RAMZON_EU     PRODDB        MAZANDARAN_PDB  PRODUCTION           RESOURCE          APPROVED             02-SEP-26
      1004 SARA_EU       TESTDB        NEKA_PDB        TEST                 DEVELOPER_ROLE    APPROVED             03-SEP-26
      1005 REZA_EU       PRODDB        EZZARON_PDB     PRODUCTION           READ ONLY         PENDING              04-SEP-26
      1006 VAHID_EU      TESTDB        DERAZKASH_PDB   TEST                 DEVELOPER_ROLE    APPROVED             05-SEP-26
      1007 ALI_EU        PRODDB        MARZIKOLA_PDB   PRODUCTION           READ WRITE        PENDING              06-SEP-26
      1008 RAMZON_EU     PRODDB        BABOL_PDB       PRODUCTION           DBA               APPROVED             07-SEP-26
      1009 REZA_EU       TESTDB        MAZANDARAN_PDB  TEST                 DEVELOPER_ROLE    REJECTED             08-SEP-26
      1010 VAHID_EU      PRODDB        DERAZKOLAH_PDB  PRODUCTION           READ ONLY         APPROVED             09-SEP-26

3. Create the End Users and Roles

I first create VAHID_EU, the Data Role, and the standard database role used in the examples. The same Data Role is then assigned to the other end users.

SQL> create end user vahid_eu identified by a;
End user created.

SQL> CREATE DATA ROLE db_data_role_vahid;
Data role created.

SQL> CREATE ROLE db_standard_role;
Role created.

SQL> GRANT CREATE SESSION TO db_standard_role;
Grant succeeded.

SQL> GRANT db_standard_role TO db_data_role_vahid;
Grant succeeded.

SQL> GRANT DATA ROLE db_data_role_vahid TO vahid_EU;
Grant succeeded.

In addition, I will create three other users that will be useful for some of the examples later in this article:

SQL> create end user RAMZON_EU identified by r;
End user created.
SQL> GRANT DATA ROLE db_data_role_vahid TO RAMZON_EU;
Grant succeeded.

SQL> create end user ALI_EU identified by a;
End user created.
SQL> GRANT DATA ROLE db_data_role_vahid TO ALI_EU;
Grant succeeded.

SQL> create end user SARA_EU identified by s;
End user created.
SQL> GRANT DATA ROLE db_data_role_vahid TO SARA_EU;
Grant succeeded.

4. Row-Level Access

4.1 Start with No Data Grant

Before creating a Data Grant, VAHID_EU cannot access the table:

SQL> conn vahid_eu/a@OL10:1521/pdb1
Connected.

SQL> select * from admin.database_access_requests;
ERROR at line 1:
ORA-00942: table or view "ADMIN"."DATABASE_ACCESS_REQUESTS" does not exist
Help: https://docs.oracle.com/error-help/db/ora-00942/

This gives us a clear starting point: the end user has the Data Role, but there is no Data Grant allowing access to this table.

4.2 Restrict Access to a User’s Rows

Now, I will give VAHID_EU SELECT access only to the rows related to this user.

SQL> conn admin/a@OL10:1521/pdb1
Connected.

SQL> CREATE OR REPLACE DATA GRANT admin.Dgran_dar_tbl
AS SELECT
ON admin.database_access_requests
WHERE username='VAHID_EU'
TO VAHID_EU;  
Data grant created.

When VAHID_EU queries the table, only rows where USERNAME is VAHID_EU are returned.

SQL> conn admin/a@OL10:1521/pdb1
Connected.

SQL> CREATE OR REPLACE DATA GRANT admin.Dgran_dar_tbl
AS SELECT
ON admin.database_access_requests
WHERE username='VAHID_EU'
TO VAHID_EU;  
Data grant created.

After creating the Data Grant, I will connect again as VAHID_EU and query the table:

SQL> conn vahid_eu/a@OL10:1521/pdb1
Connected.

SQL> select * from admin.database_access_requests;

REQUEST_ID USERNAME   DATABASE_N PDB_NAME        ENVIRONMENT     ACCESS_TYPE          REQUEST_STATUS       REQUESTED
---------- ---------- ---------- --------------- --------------- -------------------- -------------------- ---------
      1001 VAHID_EU   PRODDB     PARS_PDB        PRODUCTION      DBA                  APPROVED             01-SEP-26
      1002 VAHID_EU   PRODDB     DERAZKASH_PDB   PRODUCTION      SELECT ANY TABLE     APPROVED             02-SEP-26
      1006 VAHID_EU   TESTDB     DERAZKASH_PDB   TEST            DEVELOPER_ROLE       APPROVED             05-SEP-26
      1010 VAHID_EU   PRODDB     DERAZKOLAH_PDB  PRODUCTION      READ ONLY            APPROVED             09-SEP-26

4.3 Add More Conditions

The Data Grant can be made more restrictive by combining conditions. For example, the following grant allows VAHID_EU to see only its rows from the TEST environment.

SQL> conn admin/a@OL10:1521/pdb1
Connected.

SQL> drop DATA GRANT admin.Dgran_dar_tbl;
Data grant dropped.

SQL> CREATE OR REPLACE DATA GRANT admin.Dgran_dar_tbl
AS SELECT
ON admin.database_access_requests
WHERE username='VAHID_EU' and ENVIRONMENT='TEST'
TO VAHID_EU;

Data grant created.

After connecting as VAHID_EU again:

SQL> conn vahid_eu/a@OL10:1521/pdb1
Connected.

SQL> select * from admin.database_access_requests;

REQUEST_ID USERNAME   DATABASE_N PDB_NAME        ENVIRONMENT     ACCESS_TYPE          REQUEST_STATUS       REQUESTED
---------- ---------- ---------- --------------- --------------- -------------------- -------------------- ---------
      1006 VAHID_EU   TESTDB     DERAZKASH_PDB   TEST            DEVELOPER_ROLE       APPROVED             05-SEP-26

Now, only the row that satisfies both conditions is returned.

5. Column-Level Access

Data Grants can also control which columns are available to an end user. For example, we can exclude specific columns while also applying row conditions.

SQL> conn admin/a@OL10:1521/pdb1
Connected.

SQL>  drop DATA GRANT admin.Dgran_dar_tbl;
Data grant dropped.

SQL> CREATE OR REPLACE DATA GRANT admin.Dgran_dar_tbl
AS SELECT(ALL COLUMNS EXCEPT ACCESS_TYPE,REQUEST_STATUS)
ON admin.database_access_requests
WHERE REQUEST_ID>=1006 and REQUESTED_DATE >= '04-SEP-26'
TO VAHID_EU;

Data grant created.

When VAHID_EU queries the table, the Data Grant controls both the rows and the columns available to the user.

6. Controlling UPDATE and DELETE

Data Grants are not limited to SELECT. They can also control DML operations such as UPDATE and DELETE.

SQL> drop DATA GRANT admin.Dgran_dar_tbl;
Data grant dropped.


SQL> CREATE OR REPLACE DATA GRANT admin.Dgran_dar_tbl
AS delete,update,select
ON admin.database_access_requests
WHERE username='VAHID_EU' and ENVIRONMENT='TEST'
TO VAHID_EU;
Data grant created.

6.1 UPDATE

The following UPDATE has no WHERE clause, but only the row permitted by the Data Grant is affected:

SQL> update admin.database_access_requests set REQUEST_STATUS='REJECTED' ;

1 row updated.

We can verify the result:

SQL> select * from admin.database_access_requests;

REQUEST_ID USERNAME   DATABASE_N PDB_NAME        ENVIRONMENT     ACCESS_TYPE          REQUEST_STATUS       REQUESTED
---------- ---------- ---------- --------------- --------------- -------------------- -------------------- ---------
      1006 VAHID_EU   TESTDB     DERAZKASH_PDB   TEST            DEVELOPER_ROLE       REJECTED             05-SEP-26

The REQUEST_STATUS of the accessible row has changed to REJECTED.

Press enter or click to view image in full size

6.2 DELETE

The same principle applies to DELETE:

SQL> delete admin.database_access_requests;

1 row deleted.

Only the row accessible through the Data Grant is deleted. Rows outside that scope are not affected.

SQL>  select * from admin.database_access_requests;

no rows selected

7. Column-Specific DML Privileges

A Data Grant can also specify different column permissions for different operations. In this example, VAHID_EU can update ENVIRONMENT and ACCESS_TYPE, while SELECT can expose ENVIRONMENT, ACCESS_TYPE, and REQUEST_STATUS.

SQL>  conn admin/a@OL10:1521/pdb1
Connected.

SQL> CREATE OR REPLACE DATA GRANT admin.Dgran_dar_tbl
AS UPDATE (ENVIRONMENT, ACCESS_TYPE), 
SELECT(ENVIRONMENT,ACCESS_TYPE,REQUEST_STATUS)
ON admin.database_access_requests
WHERE username='VAHID_EU'
TO VAHID_EU;
Data grant created.

For example, updating ACCESS_TYPE is allowed because that column is included in the UPDATE portion of the Data Grant:

UPDATE admin.database_access_requests
SET ACCESS_TYPE='DBA';

4 rows updated.

This example demonstrates that row-level and column-level restrictions can be combined with operation-specific permissions.

8. Using ORA_END_USER_CONTEXT for Multiple End Users

In the previous examples, the Data Grant was created specifically for VAHID_EU. However, we can also create a single Data Grant that works for multiple end users.

For this purpose, I will use ORA_END_USER_CONTEXT.USERNAME. This allows the Data Grant to identify the current end user and return only the rows that belong to that user.

First, I will connect as ADMIN and verify the original data:

SQL> conn admin/a@OL10:1521/pdb1
Connected.
SQL> select * from admin.database_access_requests;

REQUEST_ID USERNAME   DATABASE_N PDB_NAME        ENVIRONMENT     ACCESS_TYPE          REQUEST_STATUS       REQUESTED
---------- ---------- ---------- --------------- --------------- -------------------- -------------------- ---------
      1001 VAHID_EU   PRODDB     PARS_PDB        PRODUCTION      DBA                  APPROVED             01-SEP-26
      1002 VAHID_EU   PRODDB     DERAZKASH_PDB   PRODUCTION      SELECT ANY TABLE     APPROVED             02-SEP-26
      1003 RAMZON_EU  PRODDB     MAZANDARAN_PDB  PRODUCTION      RESOURCE             APPROVED             02-SEP-26
      1004 SARA_EU    TESTDB     NEKA_PDB        TEST            DEVELOPER_ROLE       APPROVED             03-SEP-26
      1005 REZA_EU    PRODDB     EZZARON_PDB     PRODUCTION      READ ONLY            PENDING              04-SEP-26
      1006 VAHID_EU   TESTDB     DERAZKASH_PDB   TEST            DEVELOPER_ROLE       APPROVED             05-SEP-26
      1007 ALI_EU     PRODDB     MARZIKOLA_PDB   PRODUCTION      READ WRITE           PENDING              06-SEP-26
      1008 RAMZON_EU  PRODDB     BABOL_PDB       PRODUCTION      DBA                  APPROVED             07-SEP-26
      1009 REZA_EU    TESTDB     MAZANDARAN_PDB  TEST            DEVELOPER_ROLE       REJECTED             08-SEP-26
      1010 VAHID_EU   PRODDB     DERAZKOLAH_PDB  PRODUCTION      READ ONLY            APPROVED             09-SEP-26

10 rows selected.

The table contains requests for different end users.

Now, I will create the Data Grant:

SQL> drop DATA GRANT admin.Dgran_dar_tbl;
Data grant dropped.

SQL> CREATE OR REPLACE DATA GRANT admin.Dgran_dar_tbl
AS DELETE,SELECT, 
UPDATE (ACCESS_TYPE,REQUEST_STATUS)
ON admin.database_access_requests
WHERE username = ORA_END_USER_CONTEXT.username
TO VAHID_EU,RAMZON_EU,SARA_EU,ALI_EU;  

Data grant created.

The important part of this Data Grant is:

WHERE username = ORA_END_USER_CONTEXT.username

Instead of specifying one particular username, the condition uses the username of the currently connected end user.

Now, I will connect as each end user and query the table.

For VAHID_EU:

SQL> conn vahid_eu/a@OL10:1521/pdb1
Connected.

SQL> select * from admin.database_access_requests;

REQUEST_ID USERNAME   DATABASE_N PDB_NAME        ENVIRONMENT     ACCESS_TYPE          REQUEST_STATUS       REQUESTED
---------- ---------- ---------- --------------- --------------- -------------------- -------------------- ---------
      1001 VAHID_EU   PRODDB     PARS_PDB        PRODUCTION      DBA                  APPROVED             01-SEP-26
      1002 VAHID_EU   PRODDB     DERAZKASH_PDB   PRODUCTION      SELECT ANY TABLE     APPROVED             02-SEP-26
      1006 VAHID_EU   TESTDB     DERAZKASH_PDB   TEST            DEVELOPER_ROLE       APPROVED             05-SEP-26
      1010 VAHID_EU   PRODDB     DERAZKOLAH_PDB  PRODUCTION      READ ONLY            APPROVED             09-SEP-26

VAHID_EU can see only the rows belonging to VAHID_EU.

For SARA_EU:

Press enter or click to view image in full size

For RAMZON_EU:

The two rows belonging to RAMZON_EU are returned.

Finally, for ALI_EU:

9. When Standard Database Privileges Can Bypass the Data Grant(USE DATA GRANTS ONLY)

By default, Data Grants are applied in addition to standard database privileges. This means that if a privilege is granted directly to an end user through a database role, the user can use that privilege even when a Data Grant imposes a more restrictive condition.

For example, I remove the existing Data Grant and grant SELECT ANY TABLE through the standard role:

SQL> drop DATA GRANT admin.Dgran_dar_tbl;
Data grant dropped.

SQL> GRANT select any table TO db_standard_role;
Grant succeeded.

SQL> GRANT db_standard_role TO db_data_role_vahid;
Grant succeeded.

SQL> GRANT DATA ROLE db_data_role_vahid TO vahid_EU;
Grant succeeded.

VAHID_EU can now query ADMIN.DATABASE_ACCESS_REQUESTS without a row-level Data Grant restriction:

SQL> conn vahid_eu/a@OL10:1521/pdb1
Connected.
SQL> select * from admin.database_access_requests;

REQUEST_ID USERNAME   DATABASE_NAME PDB_NAME        ENVIRONMENT          ACCESS_TYPE       REQUEST_STATUS       REQUESTED
---------- ---------- ------------- --------------- -------------------- ----------------- -------------------- ---------
      1001 VAHID_EU      PRODDB        PARS_PDB        PRODUCTION           DBA               APPROVED             01-SEP-26
      1002 VAHID_EU      PRODDB        DERAZKASH_PDB   PRODUCTION           SELECT ANY TABLE  APPROVED             02-SEP-26
      1003 RAMZON_EU     PRODDB        MAZANDARAN_PDB  PRODUCTION           RESOURCE          APPROVED             02-SEP-26
      1004 SARA_EU       TESTDB        NEKA_PDB        TEST                 DEVELOPER_ROLE    APPROVED             03-SEP-26
      1005 REZA_EU       PRODDB        EZZARON_PDB     PRODUCTION           READ ONLY         PENDING              04-SEP-26
      1006 VAHID_EU      TESTDB        DERAZKASH_PDB   TEST                 DEVELOPER_ROLE    APPROVED             05-SEP-26
      1007 ALI_EU        PRODDB        MARZIKOLA_PDB   PRODUCTION           READ WRITE        PENDING              06-SEP-26
      1008 RAMZON_EU     PRODDB        BABOL_PDB       PRODUCTION           DBA               APPROVED             07-SEP-26
      1009 REZA_EU       TESTDB        MAZANDARAN_PDB  TEST                 DEVELOPER_ROLE    REJECTED             08-SEP-26
      1010 VAHID_EU      PRODDB        DERAZKOLAH_PDB  PRODUCTION           READ ONLY         APPROVED             09-SEP-26

The query returns all rows from the table, including rows belonging to other end users.

Now, let’s create a Data Grant that restricts VAHID_EU to rows where the USERNAME matches the current end user:

SQL> conn admin/a@OL10:1521/pdb1
Connected.

SQL> CREATE OR REPLACE DATA GRANT admin.Dgran_dar_tbl
AS DELETE,SELECT, UPDATE (ACCESS_TYPE,REQUEST_STATUS)
ON admin.database_access_requests
WHERE username = ORA_END_USER_CONTEXT.username
TO VAHID_EU;
Data grant created.

After creating the Data Grant, I will connect again as VAHID_EU and query the table:

SQL> conn vahid_eu/a@OL10:1521/pdb1
Connected.

SQL> select * from admin.database_access_requests;

REQUEST_ID USERNAME   DATABASE_NAME PDB_NAME        ENVIRONMENT          ACCESS_TYPE       REQUEST_STATUS       REQUESTED
---------- ---------- ------------- --------------- -------------------- ----------------- -------------------- ---------
      1001 VAHID_EU      PRODDB        PARS_PDB        PRODUCTION           DBA               APPROVED             01-SEP-26
      1002 VAHID_EU      PRODDB        DERAZKASH_PDB   PRODUCTION           SELECT ANY TABLE  APPROVED             02-SEP-26
      1003 RAMZON_EU     PRODDB        MAZANDARAN_PDB  PRODUCTION           RESOURCE          APPROVED             02-SEP-26
      1004 SARA_EU       TESTDB        NEKA_PDB        TEST                 DEVELOPER_ROLE    APPROVED             03-SEP-26
      1005 REZA_EU       PRODDB        EZZARON_PDB     PRODUCTION           READ ONLY         PENDING              04-SEP-26
      1006 VAHID_EU      TESTDB        DERAZKASH_PDB   TEST                 DEVELOPER_ROLE    APPROVED             05-SEP-26
      1007 ALI_EU        PRODDB        MARZIKOLA_PDB   PRODUCTION           READ WRITE        PENDING              06-SEP-26
      1008 RAMZON_EU     PRODDB        BABOL_PDB       PRODUCTION           DBA               APPROVED             07-SEP-26
      1009 REZA_EU       TESTDB        MAZANDARAN_PDB  TEST                 DEVELOPER_ROLE    REJECTED             08-SEP-26
      1010 VAHID_EU      PRODDB        DERAZKOLAH_PDB  PRODUCTION           READ ONLY         APPROVED             09-SEP-26

The result is still the complete table. The Data Grant does not restrict the rows because VAHID_EU already has the SELECT ANY TABLE database privilege.

This demonstrates an important point: by default, Data Grants are additive with standard database privileges. A direct database privilege can therefore provide access beyond the restrictions defined in a Data Grant.

Enforcing Data Grants
USE DATA GRANTS ONLY is particularly useful when we want to ensure that the row- and column-level restrictions defined by Data Grants cannot be bypassed by other database privileges or alternative access paths such as views.

If we want the Data Grant to be mandatory for a specific table or view, we can enable USE DATA GRANTS ONLY.

I will enable it on ADMIN.DATABASE_ACCESS_REQUESTS:

SQL> conn admin/a@OL10:1521/pdb1
Connected.

SQL> SET USE DATA GRANTS ONLY ON admin.database_access_requests ENABLED;
Use data grants only set.

With this setting enabled, access to the table by Deep Sec end users must be authorized through a Data Grant. Standard database privileges such as SELECT ANY TABLE are no longer sufficient for accessing this object.

Now, I will connect again as VAHID_EU:

SQL> conn vahid_eu/a@OL10:1521/pdb1
Connected.

SQL> select * from admin.database_access_requests;
REQUEST_ID USERNAME   DATABASE_N PDB_NAME        ENVIRONMENT     ACCESS_TYPE          REQUEST_STATUS       REQUESTED
---------- ---------- ---------- --------------- --------------- -------------------- -------------------- ---------
      1001 VAHID_EU   PRODDB     PARS_PDB        PRODUCTION      DBA                  APPROVED             01-SEP-26
      1002 VAHID_EU   PRODDB     DERAZKASH_PDB   PRODUCTION      SELECT ANY TABLE     APPROVED             02-SEP-26
      1006 VAHID_EU   TESTDB     DERAZKASH_PDB   TEST            DEVELOPER_ROLE       APPROVED             05-SEP-26
      1010 VAHID_EU   PRODDB     DERAZKOLAH_PDB  PRODUCTION      READ ONLY            APPROVED             09-SEP-26

As you can see, even though VAHID_EU has the SELECT ANY TABLE privilege, the access to this table is now controlled by the Data Grant.

This is the main purpose of USE DATA GRANTS ONLY: it enables mandatory access control for the specified table or view. For Deep Sec users, the database privilege alone is no longer enough; the required access must be provided through a Data Grant.

We can disable this feature when it is no longer required:

SQL>  SET USE DATA GRANTS ONLY ON admin.database_access_requests DISABLED;
Use data grants only set.

After disabling the setting, the normal access behavior is restored, and standard database privileges can again provide access to the object.

PDB switchover

Vahid Yousefzadeh — Fri, 25 Sep 2026 18:47:54 +0000

The procedure for remotely refreshing a PDB in Oracle Database 12c was previously discussed in Creating and Refreshing a PDB Remotely. Oracle Database 18c introduced a new capability in this area that enables the roles of the source and destination PDBs to be switched, in other words, it facilitates a switchover between the two PDBs.

In this article, we first create a PDB remotely and then demonstrate how to perform a switchover between the source and destination PDBs.

1. Create a User, PDB, and Database Link on the Source and Destination

cdb1 (source)

SQL> create pluggable database pdbsource admin user u identified by u;

Pluggable database created.

SQL> alter pluggable database PDBSOURCE  open;

Pluggable database altered.

SQL> create user db_usef  identified by pass;

User created.

SQL> grant sysoper,dba,sysdba to db_usef container=all;

Grant succeeded.

SQL> create public database link LINKclone  connect to DB_USEF identified by pass  using ‘cdb18c’;

Database link created.

cdb2 (Destination)

SQL> create user db_usef  identified by pass;

User created.

SQL> grant sysdba,dba to db_usef container=all;

Grant succeeded.

SQL> create public database link LINK_SOURCE  connect to DB_USEF identified by pass  using ‘db18c’;

Database link created.

2. Create the PDB Remotely on cdb2

cdb2 (Destination)

SQL> CREATE PLUGGABLE DATABASE pdbclone FROM PDBSOURCE@LINK_SOURCE CREATE_FILE_DEST=’/u01/18c_oradata/CDB18C’  REFRESH MODE EVERY 1 MINUTES;

Pluggable database created.

3. Create a User and Table in PDBSOURCE for Testing

cdb1 (source)

SQL> alter session set container=PDBSOURCE;

Session altered.

SQL> create user usef identified by a;

User created.

SQL> grant dba to usef;

Grant succeeded.

SQL> create table usef.tbl1(last_name varchar2(9));

Table created.

SQL> insert into usef.tbl1 values(‘USEFZADEH’);

1 row created.

SQL> commit;

Commit complete.

The data in this table is also available in PDBCLONE after the refresh operation, which is performed every minute.

cdb2 (Destination)

SQL> alter pluggable database pdbclone open read only;

Pluggable database altered.

SQL> alter session set container=pdbclone;

Session altered.

SQL>  select last_name from usef.tbl1;

LAST_NAME

———

USEFZADEH

SQL>  alter session set container=cdb$root;

Session altered.

SQL> alter pluggable database pdbclone close;

Pluggable database altered.

4. Perform a Switchover Between PDBSOURCE and PDBCLONE

The goal is to switch the roles of PDBSOURCE and PDBCLONE so that PDBCLONE becomes the primary PDB and can accept changes in read/write mode, while PDBSOURCE can be used for reporting purposes when required.

Perform the following steps.

cdb2 (Destination)


SQL> alter pluggable database pdbclone open read only;

Pluggable database altered.

cdb1 (source)

SQL> alter session set container=PDBSOURCE;

Session altered.

The general syntax of the SWITCHOVER operation is:

ALTER PLUGGABLE DATABASE refresh_mode FROM clonepdb@dblink SWITCHOVER;

In this environment, execute the following command:

SQL> ALTER PLUGGABLE DATABASE REFRESH MODE EVERY 1 MINUTES FROM pdbclone@linkclone SWITCHOVER;

Pluggable database altered.

The switchover completes successfully. The following commands demonstrate the resulting roles of the two PDBs.

cdb2 (clone)

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED

———- —————————— ———- ———-

         7 PDBCLONE                       READ WRITE NO

cdb1 (source)

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED

———- —————————— ———- ———-

         4 PDBSOURCE                      MOUNTED

During the switchover operation, the following messages can also be observed in the alert log.

cdb1 (source)

2018-04-29T16:46:31.417366+04:30

PDBSOURCE(4):ALTER PLUGGABLE DATABASE REFRESH MODE EVERY 1 MINUTES FROM pdbclone@LINKclone SWITCHOVER

2018-04-29T16:46:31.809754+04:30

PDBSOURCE(4):JIT: pid 22466 requesting stop

PDBSOURCE(4):Buffer Cache flush started: 4

PDBSOURCE(4):Buffer Cache flush finished: 4

2018-04-29T16:46:32.170496+04:30

PDBSOURCE(4):While transitioning the pdb 4 to clean state, clearing all its abort bits in the control file.

Pluggable database PDBSOURCE closed

2018-04-29T16:46:35.836492+04:30

Deleted Oracle managed file /u01/18c_oradata/DB18C/6AFC495838351172E0530288200A1577/datafile/o1_mf_temp_fgcfdd7k_.dbf

Deleted Oracle managed file /u01/18c_oradata/DB18C/6AFC495838351172E0530288200A1577/datafile/o1_mf_undotbs1_fgcfdd7j_.dbf

Deleted Oracle managed file /u01/18c_oradata/DB18C/6AFC495838351172E0530288200A1577/datafile/o1_mf_sysaux_fgcfdd7h_.dbf

Deleted Oracle managed file /u01/18c_oradata/DB18C/6AFC495838351172E0530288200A1577/datafile/o1_mf_system_fgcfdd7f_.dbf

2018-04-29T16:46:38.386672+04:30

Opatch validation is skipped for PDB PDBSOURCE (con_id=4)

2018-04-29T16:46:40.648340+04:30

PDBSOURCE(4):Endian type of dictionary set to little

****************************************************************

Pluggable Database PDBSOURCE with pdb id – 4 is created as UNUSABLE.

If any errors are encountered before the pdb is marked as NEW,

then the pdb must be dropped

local undo-1, localundoscn-0x00000000000000fb

****************************************************************

2018-04-29T16:46:41.872741+04:30

Applying media recovery for pdb-4099 from SCN 7824847 to SCN 7825420

Remote log information: count-1

thr-1, seq-52, logfile-/u01/arch/parlog_1_52_6363a705_974028799.arc, los-7791131, nxs-18446744073709551615

PDBSOURCE(4):Media Recovery Start

2018-04-29T16:46:41.875469+04:30

PDBSOURCE(4):Serial Media Recovery started

PDBSOURCE(4):max_pdb is 8

2018-04-29T16:46:41.922623+04:30

PDBSOURCE(4):Media Recovery Log /u01/arch/parlog_1_52_6363a705_974028799.arc

2018-04-29T16:46:42.249189+04:30

PDBSOURCE(4):Incomplete Recovery applied until change 7825420 time 04/29/2018 16:45:01

2018-04-29T16:46:42.250989+04:30

PDBSOURCE(4):Media Recovery Complete (db18c)

PDBSOURCE(4):Completed: ALTER PLUGGABLE DATABASE REFRESH MODE EVERY 1 MINUTES FROM pdbclone@LINKclone SWITCHOVER

2018-04-29T16:46:42.310450+04:30

PDBSOURCE(4):alter pluggable database refresh

cdb2 (clone)

2018-04-29T16:44:43.238462+04:30

PDBCLONE(7):alter pluggable database refresh

PDBCLONE(7):Completed: alter pluggable database refresh

2018-04-29T16:44:52.219206+04:30

PDBCLONE(7):Opatch XML is skipped for PDB PDBCLONE (conid=7)

PDBCLONE(7): AUDSYS.AUD$UNIFIED (SQL_TEXT) – CLOB populated

PDBCLONE(7):JIT: pid 6896 requesting stop

PDBCLONE(7):Buffer Cache flush started: 7

PDBCLONE(7):Buffer Cache flush finished: 7

Pluggable database PDBCLONE closed

2018-04-29T16:44:55.325901+04:30

Applying media recovery for pdb-4099 from SCN 7824084 to SCN 7824621

Remote log information: count-1

thr-1, seq-158, logfile-/u01/arch/parlog_1_158_26eb8840_972296216.arc, los-7815768, nxs-18446744073709551615

PDBCLONE(7):Media Recovery Start

2018-04-29T16:44:55.327301+04:30

PDBCLONE(7):Serial Media Recovery started

PDBCLONE(7):max_pdb is 7

PDBCLONE(7):WARNING: process USER (ospid: 6896) was unable to attach SMR.

2018-04-29T16:44:55.371061+04:30

PDBCLONE(7):ORA-27300: OS system dependent operation:open failed with status: 2

ORA-27301: OS failure message: No such file or directory

ORA-27302: failure occurred at: sskgmsmr_7

2018-04-29T16:44:55.384489+04:30

PDBCLONE(7):Media Recovery Log /u01/arch/parlog_1_158_26eb8840_972296216.arc

2018-04-29T16:44:55.891033+04:30

PDBCLONE(7):Incomplete Recovery applied until change 7824621 time 04/29/2018 16:46:33

2018-04-29T16:44:55.893566+04:30

PDBCLONE(7):Media Recovery Complete (cdb18c)

PDBCLONE(7):Undo initialization recovery: err:0 start: 1567231498 end: 1567231507 diff: 9 ms (0.0 seconds)

PDBCLONE(7):[6896] Successfully onlined Undo Tablespace 2.

PDBCLONE(7):Undo initialization online undo segments: err:0 start: 1567231508 end: 1567231520 diff: 12 ms (0.0 seconds)

PDBCLONE(7):Undo initialization finished serial:0 start:1567231498 end:1567231522 diff:24 ms (0.0 seconds)

PDBCLONE(7):Database Characterset for PDBCLONE is AL32UTF8

PDBCLONE(7):Buffer Cache flush started: 7

PDBCLONE(7):Buffer Cache flush finished: 7

2018-04-29T16:44:56.270556+04:30

PDBCLONE(7):While transitioning the pdb 7 to clean state, clearing all its abort bits in the control file.

2018-04-29T16:44:56.756133+04:30

PDBCLONE(7):Undo initialization recovery: err:0 start: 1567232232 end: 1567232233 diff: 1 ms (0.0 seconds)

PDBCLONE(7):[6896] Successfully onlined Undo Tablespace 2.

PDBCLONE(7):Undo initialization online undo segments: err:0 start: 1567232233 end: 1567232286 diff: 53 ms (0.1 seconds)

PDBCLONE(7):Undo initialization finished serial:0 start:1567232232 end:1567232289 diff:57 ms (0.1 seconds)

Opatch validation is skipped for PDB PDBCLONE (con_id=7)

PDBCLONE(7):Deleting old file#43 from file$

PDBCLONE(7):Deleting old file#44 from file$

PDBCLONE(7):Deleting old file#45 from file$

PDBCLONE(7):Adding new file#28 to file$(old file#43).             fopr-1, newblks-32000, oldblks-19200

PDBCLONE(7):Adding new file#29 to file$(old file#44).             fopr-1, newblks-44800, oldblks-15360

PDBCLONE(7):Adding new file#30 to file$(old file#45).             fopr-1, newblks-12800, oldblks-12800

PDBCLONE(7):Successfully created internal service PDBCLONE at open

****************************************************************

Post plug operations are now complete.

Pluggable database PDBCLONE with pdb id – 7 is now marked as NEW.

****************************************************************

PDBCLONE(7):Database Characterset for PDBCLONE is AL32UTF8

2018-04-29T16:44:57.367254+04:30

PDBCLONE(7):JIT: pid 6896 requesting full stop

2018-04-29T16:44:58.275048+04:30

Violations: Type: 2, Count: 3

PDBCLONE(7):***************************************************************

PDBCLONE(7):WARNING: Pluggable Database PDBCLONE with pdb id – 7 is

PDBCLONE(7):         altered with errors or warnings. Please look into

PDBCLONE(7):         PDB_PLUG_IN_VIOLATIONS view for more details.

PDBCLONE(7):***************************************************************

2018-04-29T16:44:58.788594+04:30

PDBCLONE(7):Opening pdb with no Resource Manager plan active

Pluggable database PDBCLONE opened read write

2018-04-29T16:44:59.007884+04:30

PDBCLONE(7):Opatch XML is skipped for PDB PDBCLONE (conid=7)

PDBCLONE(7): AUDSYS.AUD$UNIFIED (SQL_TEXT) – CLOB populated

Oracle Deep Data Security in Oracle AI Database 26ai: End Users and Data Roles

Vahid Yousefzadeh — Mon, 14 Sep 2026 08:01:21 +0000

Deep Sec is a new feature in Oracle AI Database 26ai that provides a database-enforced authorization framework: instead of hoping application code, middleware, or an AI agent behaves correctly, you declare exactly which rows, columns, and even individual cells a given user, role, or application is allowed to touch — and the database enforces that rule on every single query, no matter who or what sent it. It works alongside familiar tools such as Oracle Label Security and Data Masking , but it introduces a cleaner, SQL-native way to define fine-grained access that scales far better than row-level security written in PL/SQL.

The easiest way to understand Deep Sec is through three new building blocks that this article will use hands-on:

●Local End users - lightweight identities for application users that do not own schemas or objects, unlike traditional database users.

● Data roles — roles created specifically to carry fine-grained privileges, either mapped to roles in an external identity provider (Microsoft Entra ID, OCI IAM) or managed entirely inside the database.

● Data grants — the actual policy objects. A data grant says who can SELECT, INSERT, UPDATE, or DELETE which rows and which columns, using ordinary, readable SQL instead of hidden procedural logic.

Together, these pieces let you enforce least-privilege access at the row, column, and cell level directly at the source of the data — so the same protection applies whether the request comes from a human user, a reporting tool, or an autonomous AI agent. The rest of this article walks through a first, practical example: creating a local end user, seeing why it behaves differently from a normal database user, and using a data role and a data grant to control what that user can actually do.

Setting the Stage: End Users vs. Standard Users

Oracle AI Database 26ai introduces a second kind of user alongside the traditional database (“standard”) user: the local end user. A local end user is created and managed inside the database, but — unlike a standard user — it owns no schema and no objects. It exists purely as an identity that Deep Sec can authorize through data roles and data grants.

Creating a Local End User

You create a local end user with the new CREATE END USER statement, which mirrors the familiar CREATE USER syntax:

SQL> CREATE END USER vahid IDENTIFIED BY vahid;
End user created.

Oracle AI Database also adds a dedicated dictionary view, DBA_END_USERS, so administrators can monitor end users the same way they would monitor DBA_USERS for standard accounts:

SQL> desc dba_end_users;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 USERNAME                                           VARCHAR2(128)
 USER_ID                                   NOT NULL NUMBER
 ACCOUNT_STATUS                            NOT NULL VARCHAR2(32)
 LOCK_DATE                                          DATE
 EXPIRY_DATE                                        DATE
 CREATED_DATE                                       DATE
 PROFILE                                            VARCHAR2(128)
 AUTHENTICATION_TYPE                                VARCHAR2(8)
 PASSWORD_CHANGE_DATE                               DATE
 SCHEMA                                             VARCHAR2(128)
 START_TIME                                         VARCHAR2(37)
 END_TIME                                           VARCHAR2(37)
 MANDATORY_PROFILE_VIOLATION                        BOOLEAN
 MFA                                                VARCHAR2(8)

SQL> select username,user_id,account_status,created_date,profile from dba_end_users;
USERNAME      USER_ID ACCOUNT_STATUS  CREATED_D PROFILE
---------- ---------- --------------- --------- ----------
VAHID      2147493788 OPEN            13-SEP-26 DEFAULT

This is the first behavior that trips people up coming from traditional Oracle security: creating an end user does not grant it the ability to connect. End users are not full database sessions with their own privilege set; they need to be authorized through a data role before they can do anything, including logging on.

SQL> conn vahid/vahid@OL10:1521/pdb1
ERROR:
ORA-01045: Login denied. User VAHID does not have CREATE SESSION privilege.
Help: https://docs.oracle.com/error-help/db/ora-01045/
Warning: You are no longer connected to ORACLE.

This is expected, not a bug: an end user starts with no privileges at all. To give VAHID the ability to connect and to do useful work, we need to hand out privileges the Deep Sec way — through a data role and a data grant — rather than granting privileges to *VAHID * directly.

Authorizing an End User with Data Roles

A data role is a role created specifically for Deep Sec’s fine-grained model. You can grant it standard database roles (like CREATE SESSION) and data grants, then assign the data role to one or more end users. This keeps authorization declarative and centralized, instead of scattering privilege grants across individual users.

Creating and Granting a Data Role:

SQL> CREATE DATA ROLE db_data_role_vahid;

Data role created.

SQL> CREATE ROLE db_standard_role;

Role created.

SQL> GRANT CREATE SESSION TO db_standard_role;

Grant succeeded.

SQL> GRANT db_standard_role TO db_data_role_vahid;

Grant succeeded.

SQL> GRANT DATA ROLE db_data_role_vahid TO vahid;

Grant succeeded.

Notice the pattern: the standard database role db_standard_role carries an ordinary privilege (CREATE SESSION), that standard role is granted to the data role, and the data role is what actually gets assigned to the end user with GRANT DATA ROLE. Now *VAHID * can connect:

SQL> CONNECT vahid/vahid@OL10:1521/pdb1
Connected.

Once connected, *VAHID * can query the current end user’s own name through USER_END_USERS, but notice what USER_USERS reports instead:

SQL> SELECT username FROM user_end_users;

 USERNAME
 ---------------
 VAHID

SQL> SELECT username FROM user_users;

 USERNAME
 ----------------
 XS$NULL

This is a direct consequence of end users not owning a schema: under the covers, the session runs as the reserved, schema-less account XS$NULL, and the end-user security context layered on top of it (VAHID, plus whatever data roles are enabled) is what actually determines data access. You can see the same thing by inspecting the active session and the enabled data role directly:

SQL> SELECT username, server, type FROM v$session WHERE username = 'XS$NULL';

 USERNAME   SERVER     TYPE
 ---------- ---------- ----------
 XS$NULL    DEDICATED  USER

SQL> SELECT * FROM v$end_user_data_role;

 ROLE_NAME             CON_ID
 ---------------------- ------
 DB_DATA_ROLE_VAHID     4

Data Roles Can Still Over-Grant

Deep Sec’s data grants are designed for fine-grained, row/column-level access, but data roles can still be given broad, traditional privileges — including DBA — and that privilege flows down to every end user who holds the role. This is worth demonstrating precisely because it is dangerous:

SQL> GRANT dba TO db_data_role_vahid;
Grant succeeded.

With this single grant, VAHID now inherits DBA privileges indirectly and can run any DML against any table in the database — exactly the kind of “excessive agency” risk that Deep Sec exists to prevent. In a real environment, never grant powerful system roles like DBA to a data role; instead, use narrowly scoped data grants (SELECT/UPDATE/INSERT/DELETE on specific rows and columns) so that *VAHID * — or an AI agent acting as VAHID — can only touch the data it genuinely needs. The example below shows just how much that one grant exposes:

SQL> select * from admin.database_access_requests;

REQUEST_ID USERNAME   DATABASE_NAME PDB_NAME        ENVIRONMENT          ACCESS_TYPE       REQUEST_STATUS       REQUESTED
---------- ---------- ------------- --------------- -------------------- ----------------- -------------------- ---------
      1001 VAHID      PRODDB        PARS_PDB        PRODUCTION           DBA               APPROVED             01-SEP-26
      1002 VAHID      PRODDB        DERAZKASH_PDB   PRODUCTION           SELECT ANY TABLE  APPROVED             02-SEP-26
      1003 RAMZON     PRODDB        MAZANDARAN_PDB  PRODUCTION           RESOURCE          APPROVED             02-SEP-26
      1004 SARA       TESTDB        NEKA_PDB        TEST                 DEVELOPER_ROLE    APPROVED             03-SEP-26
      1005 REZA       PRODDB        EZZARON_PDB     PRODUCTION           READ ONLY         PENDING              04-SEP-26
      1006 VAHID      TESTDB        DERAZKASH_PDB   TEST                 DEVELOPER_ROLE    APPROVED             05-SEP-26
      1007 ALI        PRODDB        MARZIKOLA_PDB   PRODUCTION           READ WRITE        PENDING              06-SEP-26
      1008 RAMZON     PRODDB        BABOL_PDB       PRODUCTION           DBA               APPROVED             07-SEP-26
      1009 REZA       TESTDB        MAZANDARAN_PDB  TEST                 DEVELOPER_ROLE    REJECTED             08-SEP-26
      1010 VAHID      PRODDB        DERAZKOLAH_PDB  PRODUCTION           READ ONLY         APPROVED             09-SEP-26

Being over-privileged, VAHID can freely modify or remove data that a properly scoped data grant would have blocked:

SQL> DELETE admin.database_access_requests;
10 rows deleted.

SQL> ROLLBACK;
Rollback complete.

SQL> DROP TABLE admin.dg_test;
Table dropped.

SQL> CREATE TABLE admin.tb (id NUMBER, name VARCHAR2(100));
Table created.

SQL> INSERT INTO admin.tb VALUES (1, 'Vahid Yousefzadeh');
1 row created.

SQL> DROP USER usef;
User dropped.

SQL> CREATE USER usef IDENTIFIED BY usef;
User created.

SQL> GRANT dba TO usef;
Grant succeeded.

What End Users Still Cannot Do

Even with DBA privileges inherited through a data role, an end user still cannot create objects, because it has no schema to create them in. Deep Sec deliberately blocks DDL that would require object ownership for the reserved XS$NULL principal:

SQL> CREATE TABLE tb (id NUMBER, name VARCHAR2(100));
*
ERROR at line 1:
ORA-28222: Operations cannot be performed on Oracle reserved user or
role "XS$NULL".
Help: https://docs.oracle.com/error-help/db/ora-28222/

This single error captures the core idea of Deep Sec end users well: they can be authorized to read and write data through data roles and data grants, but they never become schema owners, and the database keeps that boundary even when a role has been over-granted.

Coming Up Next

This walkthrough covered the basics: creating a local end user, understanding why it starts with zero privileges, connecting it through a data role, and seeing why broad grants like DBA defeat the purpose of fine-grained authorization. In the next part, we will replace that all-or-nothing DBA grant with proper data grants — restricting VAHID to specific rows and columns using CREATE DATA GRANT, so access is scoped exactly the way Deep Sec is designed to be used.

PostgreSQL vs Oracle: When Higher Cost Means Better Performance

Vahid Yousefzadeh — Sat, 05 Sep 2026 06:32:01 +0000

Database optimizers don’t see the future. They make decisions based on statistics, estimates, and cost models — and most of the time, those decisions work remarkably well.

But what if the optimizer says:

“The Sequential Scan is cheaper.”

…and reality says:

“The Index Only Scan is almost twice as fast.”

That is exactly what I wanted to investigate.

For this experiment, I created a deliberately skewed PostgreSQL dataset containing 100 million rows, where almost every row has the same customer_type and only a single row represents a rare value.

This creates the perfect environment to challenge the optimizer’s assumptions and compare estimated cost with actual execution time.

The question is simple:

Does PostgreSQL’s estimated cost really tell us which execution plan will be faster?

The results were surprising.

Not always.

And the difference between what PostgreSQL estimated and what actually happened reveals an important lesson about reading and troubleshooting execution plans.

1.Creating the Test Table

postgres=# CREATE TABLE tbl_test (
    id             BIGINT,
    customer_type  VARCHAR(20),
    city           VARCHAR(30),
    status         VARCHAR(20)
);
CREATE TABLE

The important column for this experiment is customer_type.

I wanted to create an extremely skewed data distribution:

· 99,999,999 rows → REGULAR

· 1 row → VIP

The other columns were added to make the table more representative of a real-world table.

2. Generating 100 Million Rows

I used PostgreSQL’s generate_series() to generate 100 million rows:

postgres=#INSERT INTO tbl_test (id, customer_type, city, status)
SELECT
    gs,
    CASE
        WHEN gs = 100000000 THEN 'VIP'
        ELSE 'REGULAR'
    END,
    CASE (gs % 10)
        WHEN 0 THEN 'Babol'
        WHEN 1 THEN 'Amol'
        WHEN 2 THEN 'Behshahr'
        WHEN 3 THEN 'MarziKola'
        WHEN 4 THEN 'Bandpay'
        WHEN 5 THEN 'DerazKesh'
        WHEN 6 THEN 'Sari'
        WHEN 7 THEN 'Rasht'
        WHEN 8 THEN 'Zanjan'
        ELSE 'Tel Aviv'
    END,
    CASE
        WHEN gs % 100 < 95 THEN 'ACTIVE'
        WHEN gs % 100 < 99 THEN 'INACTIVE'
        ELSE 'SUSPENDED'
    END
FROM generate_series(1, 100000000) AS gs;
INSERT 0 100000000

This is useful because it allows us to compare two very different situations.

3. Creating the Index

I created a B-tree index on customer_type:

postgres=# CREATE INDEX idx_customer_type ON tbl_test(customer_type);
CREATE INDEX

Then I checked the size of both objects:

postgres=# SELECT    'TABLE' AS object_type,    'tbl_test' AS object_name,    pg_relation_size('tbl_test') / 1024 / 1024 AS size_mb UNION ALL SELECT    'INDEX',    'idx_customer_type',    pg_relation_size('idx_customer_type') / 1024 / 1024;
 object_type |    object_name    | size_mb
-------------+-------------------+---------
 TABLE       | tbl_test          |    5918
 INDEX       | idx_customer_type |     660
(2 rows)

The index is significantly smaller than the table. This will become important later.

4. Collecting PostgreSQL Statistics

Before asking the optimizer to make decisions, I collected statistics:

postgres=# ANALYZE VERBOSE tbl_test;
INFO:  analyzing "public.tbl_test"
INFO:  "tbl_test": scanned 30000 of 757576 pages, containing 3959968 live rows and 0 dead rows; 30000 rows in sample, 99999224 estimated total rows
INFO:  finished analyzing table "postgres.public.tbl_test"
avg read rate: 96.315 MB/s, avg write rate: 0.010 MB/s
buffer usage: 874 hits, 29255 reads, 3 dirtied
WAL usage: 10 records, 3 full page images, 10583 bytes, 0 buffers full
system usage: CPU: user: 0.59 s, system: 0.07 s, elapsed: 2.37 s
ANALYZE

5. Querying the Common Value

Now let’s ask PostgreSQL to count the REGULAR customers:

postgres=# explain SELECT COUNT(*) FROM tbl_test WHERE customer_type = 'REGULAR';
                                           QUERY PLAN
------------------------------------------------------------------------------------------------
 Finalize Aggregate  (cost=1383572.41..1383572.42 rows=1 width=8)
   ->  Gather  (cost=1383572.20..1383572.41 rows=2 width=8)
         Workers Planned: 2
         ->  Partial Aggregate  (cost=1382572.20..1382572.21 rows=1 width=8)
               ->  Parallel Seq Scan on tbl_test  (cost=0.00..1278406.17 rows=41666413 width=0)
                     Filter: ((customer_type)::text = 'REGULAR'::text)
(6 rows)

PostgreSQL chooses a Sequential Scan instead of the index because it estimates that reading most of the table directly will be cheaper than using the index.

Now, let’s execute the query and see how long PostgreSQL actually takes:

postgres=# SELECT COUNT(*) FROM tbl_test WHERE customer_type = 'REGULAR';
  count
----------
 99999999
(1 row)

Time: 15362.798 ms (00:15.363)

Execution time: 15.36 seconds to count nearly 100 million rows.

6.Forcing PostgreSQL to Use the Index

So far, PostgreSQL had a clear preference: Sequential Scan.

But I wanted to see what would happen if I took that option away.

For investigation purposes, I disabled sequential scans:

postgres=# SET enable_seqscan = off;
SET
Then I asked PostgreSQL to show me the plan again:

postgres=# explain SELECT COUNT(*) FROM tbl_test WHERE customer_type = 'REGULAR';
                                                          QUERY PLAN                                                    
-------------------------------------------------------------------------------------------------------------------------------
 Finalize Aggregate  (cost=1610210.39..1610210.40 rows=1 width=8)
   ->  Gather  (cost=1610210.17..1610210.38 rows=2 width=8)
         Workers Planned: 2
         ->  Partial Aggregate  (cost=1609210.17..1609210.18 rows=1 width=8)
               ->  Parallel Index Only Scan using idx_customer_type on tbl_test  (cost=0.57..1505044.14 rows=41666413 width=0)
                     Index Cond: (customer_type = 'REGULAR'::text)
(6 rows)

PostgreSQL switched to a Parallel Index Only Scan And now we see something very interesting. The estimated cost increased:

Sequential Scan      ≈ 1,383,572
Index Only Scan      ≈ 1,610,210

In other words, PostgreSQL’s own cost model still considers the Index Only Scan to be the more expensive option.

But I wasn’t interested in what PostgreSQL thought would happen.

I wanted to know what would actually happen.

So I executed the query:

--Forcing PostgreSQL to Use the Index

postgres=# SET enable_seqscan = off;
SET

postgres=# SELECT COUNT(*) FROM tbl_test WHERE customer_type = 'REGULAR';
  count
----------
 99999999
(1 row)

Time: 7883.189 ms (00:07.883)
Execution time: ~7.88 seconds

And this is where the experiment gets interesting.

PostgreSQL’s cost model says:

Index Only Scan → More expensive

But the real execution says:

Index Only Scan → 7.88 seconds

This is the exact gap I wanted to investigate:

Estimated cost and actual execution time are not the same thing.

Metric           Sequential Scan     Index Only Scan
Estimated Cost       1.38M               1.61M
Execution Time       15 sec              7.88 sec
Actual Result        Slower               1.9× faster

7. What Happens With the Rare Value?

Now let’s ask for the opposite value: customer_type = ‘VIP’

postgres=# explain SELECT COUNT(*) FROM tbl_test WHERE customer_type = 'VIP';
                                         QUERY PLAN
---------------------------------------------------------------------------------------------
 Aggregate  (cost=4.59..4.60 rows=1 width=8)
   ->  Index Only Scan using idx_customer_type on tbl_test  (cost=0.57..4.58 rows=1 width=0)
         Index Cond: (customer_type = 'VIP'::text)
(3 rows)

This is exactly what we would expect.

There is only one VIP row in 100 million rows.

Using the index allows PostgreSQL to locate that row without scanning the entire table. So we have two extreme cases:

REGULAR → 99,999,999 rows → Sequential Scan

VIP     → 1 row           → Index Only Scan

This demonstrates the importance of selectivity.

8. Removing the Aggregate

To make the comparison more realistic, I removed the COUNT(*) aggregate and selected the indexed column directly.

First, with sequential scans enabled:

postgres=# SET enable_seqscan = on;
SET

postgres=# EXPLAIN (ANALYZE, BUFFERS)SELECT customer_type FROM tbl_test WHERE customer_type = 'REGULAR';
                                                          QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------
 Seq Scan on tbl_test  (cost=0.00..2007576.40 rows=100000032 width=8) (actual time=2.577..22438.130 rows=99999999.00 loops=1)
   Filter: ((customer_type)::text = 'REGULAR'::text)
   Rows Removed by Filter: 1
   Buffers: shared hit=1192 read=756384
 Planning Time: 0.169 ms
 Execution Time: 26854.922 ms
(6 rows)

PostgreSQL chose a Sequential Scan. Execution time: 26.9 seconds

Now I forced PostgreSQL to use the index:

postgres=# SET enable_seqscan = off;
SET

EXPLAIN (ANALYZE, BUFFERS)SELECT customer_type FROM tbl_test WHERE customer_type = 'REGULAR';
SET
Time: 0.302 ms
                                                                         QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------
 Index Only Scan using idx_customer_type on tbl_test  (cost=0.57..2088385.13 rows=100000032 width=8) (actual time=0.163..9471.775 rows=99999999.00 loops=1)
   Index Cond: (customer_type = 'REGULAR'::text)
   Heap Fetches: 0
   Index Searches: 1
   Buffers: shared read=84203
 Planning Time: 0.170 ms
 Execution Time: 13451.658 ms
(7 rows)
Execution time: 13.45 seconds.

                   Sequential Scan         Index Only Scan
Execution Time       26.9 sec                 13.45 sec
Buffers Read           756,384                84,203

The Index Only Scan was roughly 2× faster, despite PostgreSQL estimating its cost as higher.

This is where the difference between planner cost and real-world execution becomes impossible to ignore.

Oracle Database: A Different Optimizer Decision

I repeated a similar experiment in Oracle using the same logical data distribution.

SQL> CREATE TABLE tbl_test (
  2      id             NUMBER(19),
  3      customer_type  VARCHAR2(20),
  4      city           VARCHAR2(30),
  5      status         VARCHAR2(20)
  6  );
Table created

BEGIN
    FOR i IN 0..99 LOOP

        INSERT /*+ APPEND */ INTO tbl_test
            (id, customer_type, city, status)
        SELECT
            i * 1000000 + LEVEL,
            CASE
                WHEN i = 99 AND LEVEL = 1000000 THEN 'VIP'
                ELSE 'REGULAR'
            END,
            CASE MOD(i * 1000000 + LEVEL, 10)
                WHEN 0 THEN 'Babol'
                WHEN 1 THEN 'Amol'
                WHEN 2 THEN 'Behshahr'
                WHEN 3 THEN 'MarziKola'
                WHEN 4 THEN 'Bandpay'
                WHEN 5 THEN 'DerazKesh'
                WHEN 6 THEN 'Sari'
                WHEN 7 THEN 'Rasht'
                WHEN 8 THEN 'Zanjan'
                ELSE 'Tel Aviv'
            END,
            CASE
                WHEN MOD(i * 1000000 + LEVEL, 100) < 95
                    THEN 'ACTIVE'
                WHEN MOD(i * 1000000 + LEVEL, 100) < 99
                    THEN 'INACTIVE'
                ELSE 'SUSPENDED'
            END
        FROM dual
        CONNECT BY LEVEL <= 1000000;

        COMMIT;

    END LOOP;
END;
/

SQL> CREATE INDEX idx_customer_type ON tbl_test(customer_type);
Index created

The Oracle object sizes were:

SQL> select segment_name,segment_type ,bytes/1024/1024 from dba_segments where owner='VAHID';
SEGMENT_NAME         SEGMENT_TYPE       BYTES/1024/1024
-------------------- ------------------ ---------------
TBL_TEST             TABLE                         3744
IDX_CUSTOMER_TYPE    INDEX                         2112

Now let’s run the same test in Oracle:

set autotrace traceonly explain

SQL> SELECT COUNT(*) FROM vahid.tbl_test WHERE customer_type = 'REGULAR';

Execution Plan
----------------------------------------------------------
Plan hash value: 1445291348

-------------------------------------------------------------------------------------------
| Id  | Operation             | Name              | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |                   |     1 |    12 | 72420   (1)| 00:00:03 |
|   1 |  SORT AGGREGATE       |                   |     1 |    12 |            |          |
|*  2 |   INDEX FAST FULL SCAN| IDX_CUSTOMER_TYPE |   107M|  1225M| 72420   (1)| 00:00:03 |
-------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("CUSTOMER_TYPE"='REGULAR')

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)

Oracle chooses an INDEX FAST FULL SCAN!

Despite almost every row matching REGULAR, Oracle scans the index because the query only needs COUNT(*).

In the actual test, this plan completed in approximately 10 seconds.

SQL> SELECT COUNT(*) FROM vahid.tbl_test WHERE customer_type = 'REGULAR';
  COUNT(*)
----------
  99999999

Elapsed: 00:00:10.11

Auto Redo Prioritization in Oracle AI Database 23.26.3: A Practical Test with OLTP and Batch Workloads

Vahid Yousefzadeh — Thu, 03 Sep 2026 07:27:20 +0000

Oracle AI Database 26ai introduces Auto Redo Prioritization, a new capability available from Release Update 23.26.3 that helps protect OLTP workloads during periods of heavy redo activity.

When redo buffer pressure becomes critical, Oracle can identify sessions generating large amounts of redo with longer-running calls as batch-like workloads. These sessions may wait on redo prioritization for log write lag, slowing their redo generation and giving latency-sensitive OLTP transactions more room to progress.

The goal is simple: protect OLTP responsiveness by automatically controlling batch-like redo generation when redo pressure builds.

                    REDO PRESSURE
                         │
                         ▼
              ┌─────────────────────┐
              │ Redo buffer pressure │
              └──────────┬──────────┘
                         │
                         ▼
               Auto Redo Prioritization
                         │
                ┌────────┴────────┐
                │                 │
                ▼                 ▼
          OLTP-behaving      Batch-behaving
             sessions           sessions
                │                 │
                ▼                 ▼
         Continue normally    Redo generation
                              is reduced/throttled

1. Enabling Auto Redo Prioritization

The feature can be enabled using the dynamic initialization parameter:

SQL> alter system set log_redo_prioritization=true;
System altered.

The parameter is dynamic, so a database restart is not required. In an Oracle RAC environment, it can also be configured independently on individual instances.

2. Designing the Test

The test uses five tables.

The first two represent the OLTP workload:

--OLTP

SQL> CREATE TABLE vahid.tb1 (
    id        NUMBER PRIMARY KEY,
    name      VARCHAR2(1000),
    amount    NUMBER
);
Table created.

SQL> CREATE TABLE vahid.tb2 (
    id        NUMBER PRIMARY KEY,
    name      VARCHAR2(1000),
    amount    NUMBER
);
Table created.

Each table was populated with 100,000 rows:

SQL> INSERT INTO vahid.tb1
SELECT
    level,
    'Vahid Yousefzadeh',
    level * 10
FROM dual
CONNECT BY level <= 100000;
100000 rows created.


SQL>COMMIT;
Commit complete.

SQL>INSERT INTO vahid.tb2
SELECT
    level,
    'Vahid Yousefzadeh',
    level * 10
FROM dual
CONNECT BY level <= 100000;
100000 rows created.


SQL>COMMIT;
Commit complete.

For the batch workload, three additional tables were created:

-- TB3,TB4,TB5 = Batch

SQL> CREATE TABLE vahid.tb3
(

  id NUMBER PRIMARY KEY,

  c1 VARCHAR2(4000),

  c2 VARCHAR2(4000),

  c3 VARCHAR2(4000),

  c4 VARCHAR2(4000)

);
Table created.

SQL> CREATE TABLE vahid.tb4
(

  id NUMBER PRIMARY KEY,

  c1 VARCHAR2(4000),

  c2 VARCHAR2(4000),

  c3 VARCHAR2(4000),

  c4 VARCHAR2(4000)

);
Table created.

SQL> CREATE TABLE vahid.tb5
(

  id NUMBER PRIMARY KEY,

  c1 VARCHAR2(4000),

  c2 VARCHAR2(4000),

  c3 VARCHAR2(4000),

  c4 VARCHAR2(4000)

);
Table created.

The batch tables deliberately contain substantially more data per row, creating a significantly more redo-intensive workload.

3. Running the Workloads Concurrently

The most important part of this test is that all five sessions are executed concurrently.

The workload consists of:

Session 1 → OLTP on TB1
Session 2 → OLTP on TB2
Session 3 → Batch on TB3
Session 4 → Batch on TB4
Session 5 → Batch on TB5
In other words:

2 OLTP sessions + 3 batch sessions = 5 concurrent redo-generating sessions

Session 1 — OLTP

SQL> BEGIN
    FOR i IN 1..3000000 LOOP

        UPDATE vahid.tb1
        SET amount = amount + 1
        WHERE id = MOD(i - 1, 100000) + 1;

        COMMIT;

    END LOOP;
END;
/

Session 2 — OLTP

SQL> BEGIN
    FOR i IN 1..3000000 LOOP

        UPDATE vahid.tb2
        SET amount = amount + 1
        WHERE id = MOD(i - 1, 100000) + 1;

        COMMIT;

    END LOOP;
END;
/

Session 3 — Batch

SQL> INSERT INTO vahid.tb3
SELECT
    level,
    RPAD('A',4000,'A'),
    RPAD('B',4000,'B'),
    RPAD('C',4000,'C'),
    RPAD('D',4000,'D')
FROM dual
CONNECT BY level <= 70000;

Session 4 — Batch

SQL> INSERT INTO vahid.tb4
SELECT
    level,
    RPAD('A',4000,'A'),
    RPAD('B',4000,'B'),
    RPAD('C',4000,'C'),
    RPAD('D',4000,'D')
FROM dual
CONNECT BY level <= 70000;

Session 5 — Batch

SQL> INSERT INTO vahid.tb5
SELECT
    level,
    RPAD('A',4000,'A'),
    RPAD('B',4000,'B'),
    RPAD('C',4000,'C'),
    RPAD('D',4000,'D')
FROM dual
CONNECT BY level <= 70000;

The five sessions above are started concurrently, intentionally creating contention between OLTP and batch workloads for redo generation and redo log buffer resources.

execution time for each session:

--session 1:
PL/SQL procedure successfully completed.
Elapsed: 00:08:53.76

--session 2:
PL/SQL procedure successfully completed.
Elapsed: 00:08:47.98

--session 3:
70000 rows created.
Elapsed: 00:07:48.15

--session 4:
70000 rows created.
Elapsed: 00:07:45.00

--session 5:
70000 rows created.
Elapsed: 00:07:44.48

After the concurrent workload completed, the following query was used to check two important wait events:

SELECT event,
       total_waits,
       ROUND(time_waited / 100, 2) AS time_waited_sec,
       ROUND(
           time_waited / NULLIF(total_waits, 0) * 10,
           4
       ) AS avg_wait_ms
FROM   v$system_event
WHERE  event IN (
       'log buffer space',
       'redo prioritization for log write lag'
       )
ORDER BY event;

EVENT                               TOTAL_WAITS       TIME_WAITED_SEC   AVG_WAIT_MS
log buffer space                       23497               197             8
redo prioritization for log write lag   1058               134            127

In addition, the AWR report for the test showed the following results:

The most interesting observation is the appearance of:

redo prioritization for log write lag

This provides visibility into sessions being delayed by the redo prioritization mechanism.

In this test:

23,497 log buffer space waits
1,058 redo prioritization for log write lag waits
Average log buffer space wait: *8.4143 ms *
Average redo prioritization for log write lag wait: 127.3629 ms

Moreover, The *batch-workload session trace * provides direct evidence of repeated redo prioritization for log write lag waits. Each wait lasted approximately 105–110 ms, as indicated by the ela value in microseconds.

WAIT #140202184698400: nam='redo prioritization for log write lag' ela=109507 p1=0 p2=0 p3=0 obj#=4294967295 tim=20022112908
WAIT #140202184698400: nam='redo prioritization for log write lag' ela=106961 p1=0 p2=0 p3=0 obj#=4294967295 tim=20022223931
WAIT #140202184698400: nam='redo prioritization for log write lag' ela=108165 p1=0 p2=0 p3=0 obj#=4294967295 tim=20022336260
WAIT #140202184698400: nam='redo prioritization for log write lag' ela=104621 p1=0 p2=0 p3=0 obj#=4294967295 tim=20022445083

These events represent different aspects of the workload: log buffer space reflects redo buffer pressure, while redo prioritization for log write lag reflects sessions being delayed by redo prioritization.

4. Disabling Redo Prioritization

For comparison, the feature was disabled:

SQL> alter system set log_redo_prioritization=false;
System altered.

The same queries were then executed to compare the results.

The output was:

EVENT             TOTAL_WAITS   TIME_WAITED_SEC AVG_WAIT_MS
log buffer space   30408             304            10

The AWR report:

With redo prioritization enabled, log buffer space waits were lower, decreasing from 30,408 to 23,497, while total wait time dropped from 304 to 197 seconds and average wait time from 10 ms to 8 ms.

At the same time,** 1,058** redo prioritization for log write lag waits were recorded, with an average wait of** 127 ms**, indicating that redo prioritization was actively delaying affected sessions.

These results show that redo prioritization was actively engaged, reducing log buffer space waits while introducing controlled delays for batch-like sessions. This demonstrates how Oracle can protect **OLTP **activity under heavy redo pressure.

Installing Oracle AI Database 26ai on Oracle Linux 10

Vahid Yousefzadeh — Sun, 23 Aug 2026 17:37:49 +0000

Starting with Oracle AI Database 26ai Release Update 23.26.2, the Linux x86–64 operating system is supported with the following minimum supported versions:

Oracle Linux 10 with the Unbreakable Enterprise Kernel 8: 6.12.0–104.43.4.2.el10uek.x86_64 or later
Oracle Linux 10 with the Red Hat Compatible Kernel: 6.12.0–55.40.1.0.1.el10_0.x86_64 or later

In this article, we will walk through the installation of Oracle AI Database 26ai on Oracle Linux 10.2.

The installation process can be divided into two main parts. First, we prepare the operating system for Oracle Database by configuring the hostname, local package repository, required packages, Oracle user and groups, kernel parameters, resource limits, directories, and environment variables.

In the second part, we install the Oracle Database software, apply the required Release Update (RU), execute the required root scripts, and finally create a database using Database Configuration Assistant (DBCA).

1. Preparing Oracle Linux 10 for Oracle Database Installation

Before installing Oracle AI Database 26ai, we need to prepare the operating system.

The following sections describe the main preparation steps.

Step 1: Configure the Hostname

The first step is to configure the hostname of the Oracle Linux server.

the hostname is set to OL10.

First, edit /etc/hostname:

[root@localhost ~]# vi /etc/hostname
OL10

We also configure /etc/hosts:

[root@localhost ~]# vi /etc/hosts
# Loopback entries; do not change.
# For historical reasons, localhost precedes localhost.localdomain:
127.0.0.1   localhost localhost.localdomain localhost4 localhost4.localdomain4
::1         localhost localhost.localdomain localhost6 localhost6.localdomain6
# See hosts(5) for proper format and other examples:
# 192.168.1.10 foo.example.org foo
# 192.168.1.13 bar.example.org bar

192.168.1.40 OL10

The hostname can be changed immediately without rebooting:

[root@localhost ~]# hostname OL10
[root@localhost ~]# su -
Last login: Sat Aug 22 15:36:26 EDT 2026 from 192.168.1.35 on pts/1
[root@OL10 ~]#

Step 2: Configure a Local Oracle Linux 10 Repository

The next step is to make the Oracle Linux installation media available as a local package repository.

This is particularly useful in environments where the server does not have direct Internet access.

First, create a mount point:

[root@OL10 ~]# mkdir /dvd

Then mount the Oracle Linux installation media:

[root@OL10 ~]# mount /dev/sr0 /dvd
mount: /dvd: WARNING: source write-protected, mounted read-only.

Now, we can configure YUM/DNF to use it.

First, remove the existing repository definitions:

[root@OL10 ~]# rm -rf /etc/yum.repos.d/*

Create a new repository file:

[root@OL10 ~]# vi /etc/yum.repos.d/OEL10.repo
[InstallMedia-BaseOS]
name=OEL10 - BaseOS
metadata_expire=-1
gpgcheck=0
enabled=1
baseurl=file:///dvd/BaseOS/

[InstallMedia-AppStream]
name=OEL10 - AppStream
metadata_expire=-1
gpgcheck=0
enabled=1
baseurl=file:///dvd/AppStream/

Step 3: Install the Oracle AI Database Preinstallation Package

Oracle provides a preinstallation RPM for Oracle AI Database 26ai. This package performs the OS-level preparation required by Oracle, including:

Installing the required RPM dependencies.
Configuring the required kernel parameters.
Configuring the required user limits.
Creating and configuring the required Oracle groups and users.
Applying other OS settings required for Oracle AI Database 26ai.

In this lab, the following package is used:

oracle-ai-database-preinstall-26ai-1.0-1.el10.x86_64.rpm

We install it using:

[root@OL10 source]# yum -y localinstall oracle-ai-database-preinstall-26ai-1.0-1.el10.x86_64.rpm

The package automatically identifies and installs many of the packages required by Oracle Database 26ai.

For example, the installation pulls packages such as:

bc
binutils
elfutils-libelf
fontconfig
glibc
glibc-devel
glibc-headers
ksh
libaio
libgcc
libibverbs
libstdc++
libvirt-libs
libxcb
libX11
libXau
libXi
libXrender
libXtst
libxcrypt-compat
make
policycoreutils
policycoreutils-python-utils
smartmontools
sysstat

and their required dependencies.

The preinstallation package also performs important Oracle operating-system preparation tasks.

For example, after installing it, the following files are available:

[root@OL10 ~]# ls -l /etc/security/limits.d
total 4
-rw-r--r--. 1 root root 1245 Aug 22 15:48 oracle-ai-database-preinstall-26ai.conf

The corresponding sysctl configuration file is also created:

[root@OL10 ~]# ls -lh /etc/sysctl.d/99-oracle-ai-database-preinstall-26ai-sysctl.conf
-rw-r--r--. 1 root root 2.4K Aug 22 15:48 /etc/sysctl.d/99-oracle-ai-database-preinstall-26ai-sysctl.conf

Step 4: Configure Firewall and SELinux

The next step concerns the operating-system security services.

Whether these services should be disabled depends on the environment and security requirements.

Firewall

In a test or lab environment, the firewall can be stopped and disabled:

[root@OL10 ~]# systemctl stop firewalld
 [root@OL10 ~]# systemctl disable firewalld
Removed '/etc/systemd/system/multi-user.target.wants/firewalld.service'.
Removed '/etc/systemd/system/dbus-org.fedoraproject.FirewallD1.service'.

The Oracle Database software does not inherently require the firewall to be disabled.

In a production environment, it is generally preferable to keep the firewall enabled and allow only the required Oracle Database and listener ports.

SELinux

The lab configuration also sets SELinux to disabled:

[root@OL10 ~]# vi /etc/sysconfig/selinux
SELINUX=disabled

Again, disabling SELinux is not inherently required simply to install Oracle Database 26ai. The appropriate configuration depends on the organization’s security policies and the Oracle software components being deployed.

Step 5: Create the Oracle Directories

Next, we create the directories that will be used for the Oracle software and Oracle base.

[root@OL10 ~]# mkdir /oracle/26.2 /oracle/base /oraInventory -p

Then change their ownership:

[root@OL10 ~]# chown -R oracle:oinstall /oracle /oraInventory

The /oraInventory directory stores information about installed Oracle products and their configuration.

Step 6: Configure Oracle Environment Variables

The Oracle environment variables are configured in the Oracle user’s login profile.

[oracle@OL10 ~]$ vi .bash_profile
export ORACLE_SID=db26ai
export ORACLE_BASE=/oracle/base
export ORACLE_HOME=/oracle/26.2
export PATH=${PATH}:$HOME/bin:$ORACLE_HOME/bin:/oracle/26.2/bin/
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib
export CLASSPATH=$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib
export TMP=/tmp
export TMPDIR=$TMP

2. Installing Oracle AI Database 26ai

After completing the operating-system preparation, we can start installing the Oracle Database software.

Extract the Oracle Database Software and Patches

First, extract the Oracle AI Database 26ai software into the Oracle Home directory:

 [oracle@OL10 ~]$ unzip /source/LINUX.X64_2326100_db_home.zip -d /oracle/26.2/

The Oracle Home is now ready for the installation.

Next, extract the required OPatch utility for 23.26.2:

[oracle@OL10 ~]$ unzip /source/p6880880_236R2_Linux-x86-64.zip -d /oracle/26.2/

Then, extract the 23.26.2 Release Update patch into a separate directory:

[oracle@OL10 ~]$ unzip /source/p39088031_230000_26R2_Linux-x86-64.zip -d /patch26.2/

Applying the Release Update and Launching the Installer

Before starting the Oracle Database installation, apply the 23.26.2 Release Update (RU) to the Oracle Home.

[oracle@OL10 ~]$ cd /oracle/26.2/
[oracle@OL10 26.2]$ ./runInstaller -applyRU /patch26.2/39088031/39093711/

Two operations will be performed: first the home will be patched, and then the installer will be launched.

Operation 1:

Preparing the home to patch...
Applying the patch /patch26.2/39088031/39093711/...
Successfully applied the patch.
The log can be found at: /tmp/InstallActions2026-08-22_04-02-40PM/installerPatchActions_2026-08-22_04-02-40PM.log

Operation 2:

Launching Oracle AI Database Setup Wizard...

The installer first patches the Oracle Home and, after successfully applying the RU, automatically launches the Oracle AI Database Setup Wizard to continue the installation.

Step 1: Choose the Installation Option

The first step determines whether the Oracle software should be installed only or whether a database should also be created as part of the installation.

For a software-only installation, select the option that installs the database software without creating a database.

In this lab, the Oracle software is installed first and the database is created afterward using DBCA.

Step 2: Select Single Instance Database Installation

“Single instance database installation” is appropriate when Oracle Database is being installed on a standalone server rather than an Oracle RAC environment.

Oracle RAC requires additional Grid Infrastructure and cluster configuration, while a single-instance installation does not.

Step 3: Specify Oracle Base

The Oracle Home is already determined by the location from which the installer is launched.

The Oracle Base can be reviewed or changed during installation.

Step 4: Specify the Oracle Inventory

The installer asks for the location of the Oracle Inventory. The Oracle Inventory contains information about Oracle products installed on the server.

Step 5: Operating System Groups

The installer automatically identifies the operating-system groups created for Oracle. These groups provide different levels of administrative privileges for Oracle software and database administration.

The preinstallation package prepares the required operating-system configuration automatically.

Step 6: Root Script Configuration

At the end of the installation, Oracle requires root-level scripts to be executed.

The installer can either request the root password and execute the scripts automatically or allow the DBA to execute them manually.

Step 7: Prerequisite Checks

The installer performs a series of prerequisite checks before starting the installation.

These checks can identify problems with:

Operating-system packages
Memory
Swap
Disk space
Kernel configuration
Resource limits
Operating-system configuration

Step 8: Installation Summary

The installer displays a summary of the selected configuration.

Review the settings carefully before starting the installation.

Step 9: Install the Oracle Software

After confirming the configuration, the installer begins copying and configuring the Oracle software.

At this point, the Oracle Home is populated and the required Oracle components are installed.

Executing the Root Scripts

Write on Medium
After the Oracle software installation finishes, two root scripts must be executed.

[root@OL10 ~]# /oraInventory/orainstRoot.sh
Changing permissions of /oraInventory.
Adding read,write permissions for group.
Removing read,write,execute permissions for world.
Changing groupname of /oraInventory to oinstall.
The execution of the script is complete.

[root@OL10 ~]# /oracle/26.2/root.sh
Performing root user operation.
The following environment variables are set as:
ORACLE_OWNER= oracle
ORACLE_HOME= /oracle/26.2
Enter the full pathname of the local bin directory: [/usr/local/bin]:
Copying dbhome to /usr/local/bin …
Copying oraenv to /usr/local/bin …
Copying coraenv to /usr/local/bin …
Creating /etc/oratab file…
Entries will be added to the /etc/oratab file as needed by
Database Configuration Assistant when a database is created
Finished running generic part of root script.
Now product-specific root actions will be performed.

Verifying the Oracle Software Installation

After the installation is complete, we can test SQL*Plus.

[oracle@OL10 ~]$ sqlplus "/as sysdba"
SQL*Plus: Release 23.26.2.0.0 - Production
Version 23.26.2.0.0

This confirms that the Oracle AI Database 26ai software has been installed successfully.

At this stage, however, the software installation and database creation are separate tasks. The next step is to create the database.

3. Creating the Database with DBCA

Oracle Database Configuration Assistant (DBCA) is used to create and configure the database.

[oracle@OL10 ~]$ dbca

Step 1: Create Database

DBCA can also be used to manage existing databases, but in this section our objective is to create a new database.

Step 2: Configuration Mode

DBCA provides different configuration modes.

If detailed control over the database configuration is required, select:

Advanced Configuration

For a straightforward installation, the typical configuration can be sufficient.

Advanced configuration is useful when the DBA needs to explicitly configure items such as storage, memory, database components, character set, connection mode, or other database properties.

Step 3: Database Type and Template

The next screen determines the database type and template.

For a standalone database, select:

Single Instance Database

For an Oracle RAC environment, the corresponding RAC option should be selected.

The template determines the initial database configuration.

Common templates include:

Custom Database
General Purpose or Transaction Processing
Data Warehouse A Custom Database provides the greatest control over database configuration.

For a typical OLTP environment, General Purpose or Transaction Processing is normally an appropriate starting point.

In this lab, the default template is used.

Step 4: Database Identification

The database name and SID are configured on this screen.

For example, the lab environment uses:

ORACLE_SID=db26ai

The database name identifies the database, while the SID identifies the instance.

DBCA also provides an option to specify the PDB name.

If multiple PDBs are created through the relevant DBCA option, the specified PDB name can be used as the basis for their names.

There is also an option to create an empty container database if no user PDB is required during database creation.

Step 5: Database Storage

This screen determines where the database files will be stored.

Database files include:

Data files
Control files
Online redo logs

Oracle Managed Files (OMF) can also be enabled.

The storage layout should be designed according to the workload and availability requirements of the database.

Step 6: Fast Recovery Area and ARCHIVELOG

DBCA provides the option to configure the Fast Recovery Area (FRA).

The FRA provides a managed location for files such as:

Archived redo logs
RMAN backup-related files
Flashback logs
The database can also be configured to operate in ARCHIVELOG mode.

For production databases, ARCHIVELOG mode is generally important when backup and recovery requirements include online backups, point-in-time recovery, or other recovery features.

Step 7: Listener Configuration

The listener configuration can be performed during database creation.

DBCA can create a new listener or use an existing listener.

The Oracle listener is responsible for accepting incoming client connection requests and directing them to the appropriate database service.

Step 8: Database Security

DBCA also provides options for additional database security components, such as:

Database Vault
Oracle Label Security

These features should be enabled according to the organization’s security requirements.

Step 9: Memory Configuration

DBCA provides several approaches for configuring database memory.

The main approaches are:

1. Manual Shared Memory Management

With manual shared memory management, the DBA explicitly configures the individual memory components.

2. Automatic Shared Memory Management

With Automatic Shared Memory Management, Oracle manages the sizes of individual SGA components based on the configured memory settings.

3. Automatic Memory Management

Automatic Memory Management uses MEMORY_TARGET and allows Oracle to manage the SGA and PGA within the configured memory target.

The appropriate memory-management method depends on the operating system, Oracle version, workload, and DBA requirements.

Step 10: Database Sizing

The Sizing section allows the DBA to configure parameters such as:

Database block size
PROCESSES

The block size is an important database-level decision. If a non-default block size is required, the database should be created using an appropriate custom configuration.

The PROCESSES parameter controls the maximum number of operating-system user processes that can connect to the database.

Step 11: Character Set

The character set determines how character data is stored and interpreted.

The appropriate character set should be selected based on the applications and languages that will use the database.

Step 12: Connection Mode

The connection mode determines how sessions are handled by the Oracle server.

For this installation, select:

Dedicated Server

With dedicated server architecture, each client session is associated with a dedicated server process.

Step 13: Cloud Control Registration

DBCA can also provide configuration options for registering the database with Oracle Enterprise Manager Cloud Control.

Step 14: Database Passwords

DBCA requires passwords for administrative database accounts such as:

SYS
SYSTEM
PDBADMIN

Step 15: Create the Database

After reviewing all configuration options, select:

Create Database

DBCA now begins creating the database using the configuration selected in the previous steps.

Step 16: Configuration Summary

Before or during the database creation process, DBCA provides a summary of the selected configuration.

Step 17: Database Creation in Progress

DBCA now creates the database.

During this stage, Oracle creates and configures the required database components, including the container database and the configured PDB.

The duration of this process depends on the server’s CPU, memory, and storage performance.

Step 18: Database Creation Completed

When DBCA completes successfully, the wizard displays a confirmation that the database has been created.

4. Verifying the Database

The final step is to verify the database from SQL*Plus.

[oracle@OL10 ~]$ sqlplus "/as sysdba"

SQL*Plus: Release 23.26.2.0.0 - Production

Version 23.26.2.0.0

Connected to:
Oracle AI Database 26ai Enterprise Edition Release 23.26.2.0.0 - Production
Version 23.26.2.0.0

Now check the PDBs:

SQL> show pdbs

CON_ID CON_NAME OPEN MODE RESTRICTED
 - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - 

2 PDB$SEED READ ONLY NO

3 VAHID READ WRITE NO

This confirms that:

PDB$SEED is available in READ ONLY mode.
The user PDB VAHID is open in READ WRITE mode.

Incremental Backup Using pg_basebackup in PostgreSQL 18

Vahid Yousefzadeh — Wed, 19 Aug 2026 18:02:06 +0000

In this article, I will show how to create a full backup and an incremental backup using pg_basebackup in PostgreSQL 18. I will then use pg_combinebackup to combine the backups and restore the database.

Preparing PostgreSQL

First, we need to enable the required parameters in postgresql.conf.

Add or modify the following parameters:

[postgres@OL8 ~]$ vi /var/lib/pgsql/18/data/postgresql.conf
wal_level = replica
archive_mode = on
archive_command = 'cp "%p" "/archive/%f"'
summarize_wal = on

The summarize_wal parameter enables WAL summarization, which is required for incremental backups.

After changing these parameters, restart PostgreSQL:

[postgres@OL8 ~]$ /usr/pgsql-18/bin/pg_ctl -D /var/lib/pgsql/18/data -l logfile restart
waiting for server to shut down.... done
server stopped
waiting for server to start.... done
server started

Creating Backup Directories

Next, create separate directories for the full and incremental backups:

[root@OL8 ~]# mkdir /PGbackup/Full /PGbackup/Incremental -p
[root@OL8 ~]# chown -R postgres.postgres /PGbackup

Now the environment is ready to create the full backup.

Creating the Full Backup

Run pg_basebackup as the postgres user:

[postgres@OL8 ~]$ pg_basebackup -D /PGbackup/Full/ -P
23648/23648 kB (100%), 1/1 tablespace

The backup directory contains a complete PostgreSQL data directory:

[postgres@OL8 ~]$ ls -l /PGbackup/Full/
-rw-------. 1 postgres postgres    229 Aug 17 20:55 backup_label
-rw-r-----. 1 postgres postgres    227 Aug 17 20:55 backup_label.old
-rw-------. 1 postgres postgres 138089 Aug 17 20:55 backup_manifest
drwxr-x---. 5 postgres postgres     33 Aug 17 20:55 base
-rw-------. 1 postgres postgres     30 Aug 17 20:55 current_logfiles
drwxr-x---. 2 postgres postgres   4096 Aug 17 20:55 global
drwxr-x---. 2 postgres postgres     32 Aug 17 20:55 log
drwxr-x---. 2 postgres postgres      6 Aug 17 20:55 pg_commit_ts
drwxr-x---. 2 postgres postgres      6 Aug 17 20:55 pg_dynshmem
-rw-r-----. 1 postgres postgres   5721 Aug 17 20:55 pg_hba.conf
-rw-r-----. 1 postgres postgres   2681 Aug 17 20:55 pg_ident.conf
drwxr-x---. 4 postgres postgres     68 Aug 17 20:55 pg_logical
drwxr-x---. 4 postgres postgres     36 Aug 17 20:55 pg_multixact
drwxr-x---. 2 postgres postgres      6 Aug 17 20:55 pg_notify
drwxr-x---. 2 postgres postgres      6 Aug 17 20:55 pg_replslot
drwxr-x---. 2 postgres postgres      6 Aug 17 20:55 pg_serial
drwxr-x---. 2 postgres postgres      6 Aug 17 20:55 pg_snapshots
drwxr-x---. 2 postgres postgres      6 Aug 17 20:55 pg_stat
drwxr-x---. 2 postgres postgres      6 Aug 17 20:55 pg_stat_tmp
drwxr-x---. 2 postgres postgres      6 Aug 17 20:55 pg_subtrans
drwxr-x---. 2 postgres postgres      6 Aug 17 20:55 pg_tblspc
drwxr-x---. 2 postgres postgres      6 Aug 17 20:55 pg_twophase
-rw-r-----. 1 postgres postgres      3 Aug 17 20:55 PG_VERSION
drwxr-x---. 4 postgres postgres     77 Aug 17 20:55 pg_wal
drwxr-x---. 2 postgres postgres     18 Aug 17 20:55 pg_xact
-rw-r-----. 1 postgres postgres     88 Aug 17 20:55 postgresql.auto.conf
-rw-r-----. 1 postgres postgres  32453 Aug 17 20:55 postgresql.conf

The backup_manifest file is particularly important because it is used to identify the contents of the backup when creating an incremental backup.

Making Changes to the Database

Now, let’s make some changes to the database after the full backup has been created.

First, create a new database:

postgres=# create database vahid;
CREATE DATABASE

postgres=# \c vahid
You are now connected to database "vahid" as user "postgres".

vahid=# create table tb(id int,name varchar(100));
CREATE TABLE

vahid=# insert into tb values(1,'Vahid Yousefzadeh');
INSERT 0 1

At this point, the full backup does not contain these changes because they were made after the full backup was created.

Creating the Incremental Backup

Now we can create an incremental backup based on the full backup.

Use the backup_manifest from the full backup as the reference:

[postgres@OL8 ~]$ pg_basebackup -D /PGbackup/Incremental/ --incremental /PGbackup/Full/backup_manifest -P
11477/31360 kB (36%), 1/1 tablespace

The incremental backup is much smaller than the full backup because it contains only the data required to update the referenced backup.

The resulting directory contains another backup manifest and the required PostgreSQL files:

[postgres@OL8 ~]$ ls -lh /PGbackup/Incremental/
total 252K
-rw-------. 1 postgres postgres  286 Aug 17 20:59 backup_label
-rw-r-----. 1 postgres postgres  227 Aug 17 20:59 backup_label.old
-rw-------. 1 postgres postgres 184K Aug 17 20:59 backup_manifest
drwxr-x---. 6 postgres postgres   46 Aug 17 20:59 base
-rw-------. 1 postgres postgres   30 Aug 17 20:59 current_logfiles
drwxr-x---. 2 postgres postgres 4.0K Aug 17 20:59 global
drwxr-x---. 2 postgres postgres   32 Aug 17 20:59 log
drwxr-x---. 2 postgres postgres    6 Aug 17 20:59 pg_commit_ts
drwxr-x---. 2 postgres postgres    6 Aug 17 20:59 pg_dynshmem
-rw-r-----. 1 postgres postgres 5.6K Aug 17 20:59 pg_hba.conf
-rw-r-----. 1 postgres postgres 2.7K Aug 17 20:59 pg_ident.conf
drwxr-x---. 4 postgres postgres   68 Aug 17 20:59 pg_logical
drwxr-x---. 4 postgres postgres   36 Aug 17 20:59 pg_multixact
drwxr-x---. 2 postgres postgres    6 Aug 17 20:59 pg_notify
drwxr-x---. 2 postgres postgres    6 Aug 17 20:59 pg_replslot
drwxr-x---. 2 postgres postgres    6 Aug 17 20:59 pg_serial
drwxr-x---. 2 postgres postgres    6 Aug 17 20:59 pg_snapshots
drwxr-x---. 2 postgres postgres    6 Aug 17 20:59 pg_stat
drwxr-x---. 2 postgres postgres    6 Aug 17 20:59 pg_stat_tmp
drwxr-x---. 2 postgres postgres    6 Aug 17 20:59 pg_subtrans
drwxr-x---. 2 postgres postgres    6 Aug 17 20:59 pg_tblspc
drwxr-x---. 2 postgres postgres    6 Aug 17 20:59 pg_twophase
-rw-r-----. 1 postgres postgres    3 Aug 17 20:59 PG_VERSION
drwxr-x---. 4 postgres postgres   77 Aug 17 20:59 pg_wal
drwxr-x---. 2 postgres postgres   18 Aug 17 20:59 pg_xact
-rw-r-----. 1 postgres postgres   88 Aug 17 20:59 postgresql.auto.conf
-rw-r-----. 1 postgres postgres  32K Aug 17 20:59 postgresql.conf

Restoring the Full and Incremental Backups

Now let’s test the backup by completely removing the existing PostgreSQL data directory and rebuilding it from the full and incremental backups.

First, stop PostgreSQL:

[postgres@OL8 ~]$ /usr/pgsql-18/bin/pg_ctl -D /var/lib/pgsql/18/data -l logfile stop
waiting for server to shut down.... done
server stopped

Remove the contents of the existing data directory:

[postgres@OL8 ~]$ rm -rf  /var/lib/pgsql/18/data/*

At this point, the original data directory is empty.

Combining the Backups with pg_combinebackup

PostgreSQL provides the pg_combinebackup utility, which can combine a base backup and one or more incremental backups into a new PostgreSQL data directory.

[postgres@OL8 ~]$ pg_combinebackup -o /var/lib/pgsql/18/data/ /PGbackup/Full/ /PGbackup/Incremental/

The command creates the restored data directory:

[postgres@OL8 data]$ ll
total 248
-rw-r-----. 1 postgres postgres    229 Aug 17 21:03 backup_label
-rw-r-----. 1 postgres postgres    227 Aug 17 21:03 backup_label.old
-rw-r-----. 1 postgres postgres 182110 Aug 17 21:03 backup_manifest
drwxr-x---. 6 postgres postgres     46 Aug 17 21:03 base
-rw-r-----. 1 postgres postgres     30 Aug 17 21:03 current_logfiles
drwxr-x---. 2 postgres postgres   4096 Aug 17 21:03 global
drwxr-x---. 2 postgres postgres     32 Aug 17 21:03 log
drwxr-x---. 2 postgres postgres      6 Aug 17 21:03 pg_commit_ts
drwxr-x---. 2 postgres postgres      6 Aug 17 21:03 pg_dynshmem
-rw-r-----. 1 postgres postgres   5721 Aug 17 21:03 pg_hba.conf
-rw-r-----. 1 postgres postgres   2681 Aug 17 21:03 pg_ident.conf
drwxr-x---. 4 postgres postgres     68 Aug 17 21:03 pg_logical
drwxr-x---. 4 postgres postgres     36 Aug 17 21:03 pg_multixact
drwxr-x---. 2 postgres postgres      6 Aug 17 21:03 pg_notify
drwxr-x---. 2 postgres postgres      6 Aug 17 21:03 pg_replslot
drwxr-x---. 2 postgres postgres      6 Aug 17 21:03 pg_serial
drwxr-x---. 2 postgres postgres      6 Aug 17 21:03 pg_snapshots
drwxr-x---. 2 postgres postgres      6 Aug 17 21:03 pg_stat
drwxr-x---. 2 postgres postgres      6 Aug 17 21:03 pg_stat_tmp
drwxr-x---. 2 postgres postgres      6 Aug 17 21:03 pg_subtrans
drwxr-x---. 2 postgres postgres      6 Aug 17 21:03 pg_tblspc
drwxr-x---. 2 postgres postgres      6 Aug 17 21:03 pg_twophase
-rw-r-----. 1 postgres postgres      3 Aug 17 21:03 PG_VERSION
drwxr-x---. 4 postgres postgres     77 Aug 17 21:03 pg_wal
drwxr-x---. 2 postgres postgres     18 Aug 17 21:03 pg_xact
-rw-r-----. 1 postgres postgres     88 Aug 17 21:03 postgresql.auto.conf
-rw-r-----. 1 postgres postgres  32453 Aug 17 21:03 postgresql.conf

The output directory now contains the combined contents of the full and incremental backups.

Starting PostgreSQL

The data directory has been restored, so we can start PostgreSQL:

[postgres@OL8 ~]$ /usr/pgsql-18/bin/pg_ctl -D /var/lib/pgsql/18/data -l logfile start
waiting for server to start.... done
server started

Verifying the Restore

Finally, let’s verify that the database and table created after the full backup are available.

Connect to PostgreSQL:

[postgres@OL8 ~]$ psql
psql (18.0)
Type "help" for help.

postgres=# \l
                                                     List of databases
   Name    |  Owner   | Encoding | Locale Provider |   Collate   |    Ctype    | Locale | ICU Rules |   Access privileges
-----------+----------+----------+-----------------+-------------+-------------+--------+-----------+-----------------------
 postgres  | postgres | UTF8     | libc            | en_US.UTF-8 | en_US.UTF-8 |        |           |
 template0 | postgres | UTF8     | libc            | en_US.UTF-8 | en_US.UTF-8 |        |           | =c/postgres          +
           |          |          |                 |             |             |        |           | postgres=CTc/postgres
 template1 | postgres | UTF8     | libc            | en_US.UTF-8 | en_US.UTF-8 |        |           | =c/postgres          +
           |          |          |                 |             |             |        |           | postgres=CTc/postgres
 vahid     | postgres | UTF8     | libc            | en_US.UTF-8 | en_US.UTF-8 |        |           |
(4 rows)

postgres=# \c vahid
You are now connected to database "vahid" as user "postgres".

vahid=# \d
        List of relations
 Schema | Name | Type  |  Owner
--------+------+-------+----------
 public | tb   | table | postgres
(1 row)

vahid=# select * from tb;
 id |       name
----+-------------------
  1 | Vahid Yousefzadeh
(1 row)

The table and its data are available after restoring the full backup together with the incremental backup.

Point-in-Time Recovery (PITR) with pg_basebackup in PostgreSQL 18

Vahid Yousefzadeh — Mon, 17 Aug 2026 17:51:59 +0000

pg_basebackup is a useful tool for creating a base backup of a PostgreSQL cluster. When combined with continuous WAL archiving, it can be used to perform Point-in-Time Recovery (PITR).

In this article, I will demonstrate how to create a base backup using pg_basebackup and then perform PITR to a specific point in time.

1. Check the Current PostgreSQL Cluster

First, let’s check the current state of the PostgreSQL cluster.

psql (18.0)
WARNING: Console code page (720) differs from Windows code page (1256)
         8-bit characters might not work correctly. See psql reference
         page "Notes for Windows users" for details.
Type "help" for help.

postgres=# \l
                                                                    List of databases
   Name    |  Owner   | Encoding | Locale Provider |          Collate           |           Ctype            | Locale | ICU Rules |   Access privileges
-----------+----------+----------+-----------------+----------------------------+----------------------------+--------+-----------+-----------------------
 postgres  | postgres | UTF8     | libc            | English_United States.1256 | English_United States.1256 |        |           | =Tc/postgres         +
           |          |          |                 |                            |                            |        |           | postgres=CTc/postgres
 template0 | postgres | UTF8     | libc            | English_United States.1256 | English_United States.1256 |        |           | =c/postgres          +
           |          |          |                 |                            |                            |        |           | postgres=CTc/postgres
 template1 | postgres | UTF8     | libc            | English_United States.1256 | English_United States.1256 |        |           | =c/postgres          +
           |          |          |                 |                            |                            |        |           | postgres=CTc/postgres
 usefdb    | postgres | UTF8     | libc            | English_United States.1256 | English_United States.1256 |        |           |

2. Configure WAL Archiving

Before creating the base backup, we need to configure PostgreSQL for WAL archiving.

The following parameters are configured in:

D:\postgres18_windows_installed\data\postgresql.conf
wal_level = replica
archive_mode = on
archive_command = 'copy "%p" "E:\\Archive\\%f"'

for Linux:

[postgres@OL8 ~]$ vi /var/lib/pgsql/18/data/postgresql.conf

wal_level = replica
archive_mode = on
archive_command = 'cp "%p" "/archive/%f"'

The wal_level setting determines how much information is written to the WAL. The replica level is sufficient for WAL archiving and physical replication.

archive_mode = on enables WAL archiving, while archive_command specifies how completed WAL segments are copied to the archive location.

In this example, the WAL archive directory is:

E:\Archive

Because archive_mode cannot be changed without restarting the server, we restart PostgreSQL:

C:\Users\Win10-991005> pg_ctl restart
waiting for server to shut down....   
 done
server stopped
waiting for server to start....2026-08-12 22:32:04 +0330 LOG:  redirecting log output to logging collector process
2026-08-12 22:32:04 +0330 HINT:  Future log output will appear in directory "log".
 done
server started

3. Generate a WAL Segment

We can force PostgreSQL to switch to a new WAL segment by using

pg_switch_wal():

postgres=# select pg_switch_wal();
 pg_switch_wal
---------------
 0/1D0020C0
(1 row)

We can then verify that the WAL segment has been archived:

C:\Users\Win10-991005>dir e:\Archive\*
08/12/2026  10:32 PM    <DIR>          .
08/12/2026  10:32 PM    <DIR>          ..
08/12/2026  10:32 PM        16,777,216 00000005000000000000001D
               1 File(s)     16,777,216 bytes
               2 Dir(s)   7,594,000,384 bytes free

At this point, WAL archiving is working and we can create the base backup.

4. Create a Base Backup with pg_basebackup

The base backup can be created with the following command:

C:\Users\Win10-991005> pg_basebackup -D "E:\backup" -Ft -P -U postgres
Password:

32844/32844 kB (100%), 1/1 tablespace

In this example:

-D “E:\backup” specifies the destination directory.
-Ft tells pg_basebackup to use tar format.
-P displays progress information.
-U postgres specifies the PostgreSQL user used to connect to the server.
The backup directory contains the following files:

C:\Users\Win10-991005> dir E:\backup\*
08/12/2026  10:34 PM           184,439 backup_manifest
08/12/2026  10:34 PM        33,632,768 base.tar
08/12/2026  10:34 PM        33,558,528 pg_wal.tar
               3 File(s)     67,375,735 bytes
               2 Dir(s)   7,476,281,344 bytes free

5. Generate Some Test Data

Now we will create a table and insert some test data.

postgres=# create table tb_2026_08_12(id serial primary key,name varchar(50));

CREATE TABLE

postgres=# insert into tb_2026_08_12 values(1,’Vahid Yousefzadeh’);

INSERT 0 1

6. Determine the PITR Target Time

Now I want to restore the database to a point after the first row was inserted but before the second row was inserted.

First, I switch WAL and record the current time:

postgres=# select pg_switch_wal();
 pg_switch_wal
---------------
 0/214ADFF0
(1 row)
postgres=# select now();
               now
----------------------------------
 2026-08-12 22:36:26.779349+03:30
(1 row)

Next, I insert another row:

postgres=# insert into tb_2026_08_12 values(2,'Payan Rafat');
INSERT 0 1

Then I switch WAL again:

postgres=# select pg_switch_wal();
 pg_switch_wal
---------------
0/22000160
(1 row)

At this point, the WAL archive contains the WAL required to replay the changes between the base backup and our target time.

7. Prepare the Environment for Recovery

Now we stop PostgreSQL:

C:\Users\Win10-991005>pg_ctl  stop
waiting for server to shut down....        1 file(s) copied.
.. done
server stopped

For this demonstration, I remove the existing data directory:

C:\Users\Win10-991005>rmdir /s /q "D:\postgres18_windows_installed\data"
Then I create an empty data directory again:

C:\Users\Win10-991005>mkdir "D:\postgres18_windows_installed\data"

8. Restore the Base Backup

Because the base backup was created in tar format, we can extract into the base.tar PostgreSQL data directory:

C:\Users\Win10-991005>tar -xvf "E:\backup\base.tar" -C "D:\postgres18_windows_installed\data"

9. Create recovery.signal

To tell PostgreSQL to enter archive recovery, we create an empty file named recovery.signal in the data directory:

C:\Users\Win10-991005> type nul > "D:\postgres18_windows_installed\data\recovery.signal"

When PostgreSQL starts with recovery.signal present, it enters recovery mode and uses the recovery configuration parameters to determine how recovery should proceed.

10. Configure PITR

The following parameters are added to postgresql.conf:

restore_command = 'copy "E:\\Archive\\%f" "%p"'

recovery_target_time = '2026-08-12 22:36:26'

recovery_target_action = 'promote'

The restore_command tells PostgreSQL how to retrieve archived WAL files. %f is replaced with the requested WAL file name, while %p is replaced with the path where PostgreSQL expects the file to be restored.

The recovery_target_time specifies the point in time at which recovery should stop.

Finally, recovery_target_action = ‘promote’ tells PostgreSQL to end recovery and promote the server to a normal read-write state once the recovery target has been reached.

11. Start PostgreSQL and Perform PITR

Now we can start PostgreSQL:

C:\Users\Win10-991005> pg_ctl start
waiting for server to start....2026-08-12 22:47:03 +0330 LOG:  redirecting log output to logging collector process
2026-08-12 22:47:03 +0330 HINT:  Future log output will appear in directory "log".
done
server started

The PostgreSQL log shows that archive recovery has started:

2026-08-12 22:47:03 +0330 LOG:  starting PostgreSQL 18.0 on x86_64-windows, compiled by msvc-19.44.35217, 64-bit
2026-08-12 22:47:03 +0330 LOG:  listening on IPv6 address "::", port 5432
2026-08-12 22:47:03 +0330 LOG:  listening on IPv4 address "0.0.0.0", port 5432
2026-08-12 22:47:03 +0330 LOG:  database system was interrupted; last known up at 2026-08-12 22:34:34 +0330
2026-08-12 22:47:06 +0330 LOG:  starting backup recovery with redo LSN 0/20000028, checkpoint LSN 0/20000080, on timeline ID 5
2026-08-12 22:47:06 +0330 LOG:  restored log file "000000050000000000000020" from archive
2026-08-12 22:47:06 +0330 LOG:  starting point-in-time recovery to 2026-08-12 22:36:26+03:30
2026-08-12 22:47:06 +0330 LOG:  redo starts at 0/20000028
2026-08-12 22:47:06 +0330 LOG:  completed backup recovery with redo LSN 0/20000028 and end LSN 0/20000120
2026-08-12 22:47:06 +0330 LOG:  consistent recovery state reached at 0/20000120
2026-08-12 22:47:06 +0330 LOG:  database system is ready to accept read-only connections
2026-08-12 22:47:06 +0330 LOG:  restored log file "000000050000000000000021" from archive
2026-08-12 22:47:06 +0330 LOG:  restored log file "000000050000000000000022" from archive
2026-08-12 22:47:06 +0330 LOG:  recovery stopping before commit of transaction 850, time 2026-08-12 22:36:53.547442+03:30
2026-08-12 22:47:06 +0330 LOG:  redo done at 0/220000E8 system usage: CPU: user: 0.01 s, system: 0.07 s, elapsed: 0.36 s
2026-08-12 22:47:06 +0330 LOG:  last completed transaction was at log time 2026-08-12 22:36:02.679092+03:30
2026-08-12 22:47:06 +0330 LOG:  selected new timeline ID: 6
2026-08-12 22:47:06 +0330 LOG:  archive recovery complete
2026-08-12 22:47:06 +0330 LOG:  checkpoint starting: end-of-recovery immediate wait
2026-08-12 22:47:08 +0330 LOG:  checkpoint complete: wrote 1024 buffers (5.6%), wrote 3 SLRU buffers; 0 WAL file(s) added, 0 removed, 2 recycled; write=0.486 s, sync=0.968 s, total=1.499 s; sync files=335, longest=0.009 s, average=0.003 s; distance=32768 kB, estimate=32768 kB; lsn=0/220000E8, redo lsn=0/220000E8
2026-08-12 22:47:08 +0330 LOG:  database system is ready to accept connections
The new timeline ID is important. After PITR completes and PostgreSQL is promoted, it creates a new timeline so that the recovered database can continue generating WAL independently from the original timeline.

Finally, PostgreSQL becomes available for normal connections:

LOG: database system is ready to accept connections

12. Verify the Recovery

Now we can connect to the database and check the table:

postgres=#  select * from tb_2026_08_12;
 id |       name
----+-------------------
  1 | Vahid Yousefzadeh
(1 row)

Only the first row exists.

The second row:

2 | Payan Rafat

is not present because it was inserted after the selected PITR target.

Therefore, the recovery successfully restored the PostgreSQL cluster to the requested point in time.