Understanding Bounded Recovery, long-running transactions, archive-log retention, LogMiner, Logdump, and Oracle-to-PostgreSQL replication
By Michael R. Culp — MRC Consulting LLC
September 21, 2026
One of the more interesting Oracle GoldenGate failures occurs when Extract attempts to recover and suddenly requests an Oracle archived redo log from several days ago — but the database only retains one day of archive logs.
Consider this scenario:
Oracle Database
|
| Integrated Extract / LogMiner
v
Oracle GoldenGate Extract
|
v
GoldenGate Trail
|
v
GoldenGate Replicat
|
v
PostgreSQL
GoldenGate stops with an error similar to:
ORA-01292: LogMiner for upstream capture cannot find log file
Further investigation shows that GoldenGate wants redo from approximately seven days ago, while the Oracle database only retains archived redo for one day.
At first glance, this may look like a PostgreSQL replication problem.
It isn't.
The failure is occurring on the Oracle source side, before the changes can even be written to the GoldenGate trail.
For current Oracle Database releases, Oracle defines ORA-01292 as LogMiner being unable to locate a log file it requires in V$ARCHIVED_LOG. Oracle recommends identifying the required thread and SCN from the alert log and trace information and restoring the missing log if it has been deleted or expired.
That tells us something important about the architecture.
With Oracle Integrated Extract:
Oracle Online Redo / Archived Redo
|
v
LogMiner
|
v
GoldenGate Extract
|
v
GoldenGate Trail
If LogMiner cannot obtain the redo, Extract cannot capture those transactions.
And if Extract never captures them, they never enter the trail.
Changing something on the PostgreSQL target cannot correct that problem.
This is where the investigation gets interesting.
If the database retains only one day of archived redo but Extract requests a log from seven days ago, I would investigate three areas immediately:
The important question is not simply:
Why is GoldenGate looking backward?
The better question is:
What recovery state requires GoldenGate to return to that SCN?
One of the first commands I would run is:
INFO EXTRACT <extract_name>, SHOWCH
Oracle documents SHOWCH as displaying detailed Extract checkpoint information, including read and write checkpoint positions.
For example:
INFO EXTRACT EXTORA, SHOWCH
I want to understand the relationship between the current processing position and the recovery position.
Conceptually:
Current Oracle Redo
|
|
| Current Extract position
|
|
|----------------------------
|
|
| Recovery Checkpoint
|
|
|----------------------------
|
| Old transaction began
|
v
Older Redo
If that recovery position is seven days old, I need to determine why.
Next I would check what transactions Extract is tracking.
SEND EXTRACT <extract_name>, SHOWTRANS
For example:
SEND EXTRACT EXTORA, SHOWTRANS
SHOWTRANS is particularly useful because GoldenGate can show information such as transaction ID, redo thread, SCN, redo sequence, RBA and the oldest redo log required to restart Extract.
An example may conceptually look like:
Oldest redo log file necessary to restart Extract:
Redo Thread: 1
Redo Sequence: 12543
SCN: 984562341
XID: 7.14.48657
Start Time: 2026-09-14 04:13:21
Status: Running
Now we have something important.
Today may be September 21, but GoldenGate is tracking a transaction whose captured activity goes back to September 14.
That can explain why recovery needs older redo.
Oracle GoldenGate includes a mechanism called Bounded Recovery specifically to make Extract recovery from long-running transactions more manageable.
Oracle describes Bounded Recovery as part of Extract's checkpointing facility. Its purpose is to place an upper boundary on how much recovery work Extract needs after a planned or unplanned stop, regardless of the number or age of open transactions.
The default Bounded Recovery interval is:
BRINTERVAL = 4 hours
Oracle states that GoldenGate requires at least twice the BRINTERVAL in log retention to guarantee Bounded Recovery. With the default four-hour interval, that means at least eight hours of required redo availability for that mechanism.
The architecture is roughly:
Long-running transaction starts
|
|
| GoldenGate maintains
| transaction state
|
BR Checkpoint
|
|
BR Checkpoint
|
|
Extract stops
X
|
v
Extract recovers using
Bounded Recovery state
Without Bounded Recovery, recovery could require GoldenGate to return all the way back to the beginning of the transaction.
With BR, GoldenGate can persist recovery information periodically and avoid rereading the entire transaction history.
This is the dangerous situation.
Oracle documents that during Extract recovery, GoldenGate may either recover using Bounded Recovery checkpoint information plus redo, or recover from its normal recovery checkpoint in the archived and online redo logs.
You can check recovery status with:
SEND EXTRACT <extract_name>, STATUS
You may see something like:
In recovery[1]
Conceptually:
Extract Restart
|
v
Bounded Recovery
available?
/ \
YES NO
| |
v v
BR checkpoint Normal recovery
+ recent redo |
v
Recovery checkpoint
|
v
Older Oracle redo
If normal recovery needs redo from seven days ago, but Oracle only retains one day, recovery stops.
And eventually LogMiner reports that the required log cannot be found.
This is the operational lesson.
Archive-log retention should not be based solely on normal database backup requirements.
It also has to account for replication recovery requirements.
For example:
Archive Retention
1 day
<-------->
GoldenGate Recovery Requirement
7 days
<------------------------------------------------>
Those two policies are incompatible.
Under normal operation, everything may appear healthy.
Then Extract stops.
GoldenGate attempts recovery.
It needs sequence 12345 from six or seven days earlier.
Oracle says:
That archive no longer exists.
And GoldenGate cannot continue safely.
If the archived redo still exists in RMAN backup or another archive repository, the safest recovery path is generally to restore it.
From the GoldenGate error, alert log and Extract information, determine:
Thread
Sequence
SCN
Timestamp
Then query Oracle:
SELECT
thread#,
sequence#,
first_change#,
next_change#,
name,
archived,
deleted,
status
FROM v$archived_log
WHERE <required_scn> >= first_change#
AND <required_scn> < next_change#
ORDER BY thread#, sequence#;
In RAC environments the thread number is critical, because each instance generates its own redo thread.
If the required archive can be restored and made visible to Oracle/LogMiner, Extract may be able to resume from its existing recovery position.
This is where I would be extremely cautious.
It may be tempting to simply move Extract forward:
ALTER EXTRACT ...
and get replication running again.
That can solve the process availability problem while creating a much worse data consistency problem.
Imagine that Oracle transactions occurred here:
Day 1 Day 2 Day 3 Day 4 Day 5 Day 6 Day 7
|--------- REQUIRED ORACLE REDO ---------------------------|
X X X X X archive logs deleted X X X X X
|
v
Extract restarted
If Extract is repositioned beyond the missing redo:
Oracle
|
| Transaction A
| Transaction B
| Transaction C
|
X <-- missing redo
|
| Transaction D
| Transaction E
|
v
PostgreSQL
Transactions A, B and C could be missing from PostgreSQL forever.
Replication may show:
RUNNING
while the databases are no longer synchronized.
That is why I consider this a data-integrity incident, not merely an Extract restart problem.
If the missing redo cannot be recovered, the appropriate solution may be to establish a new consistent synchronization point and reinstantiate the affected PostgreSQL data.
Conceptually:
Oracle
|
|
Establish consistent
SCN / point
|
+----------+----------+
| |
v v
Initial/Reload Data GoldenGate Capture
| from sync point
v |
PostgreSQL |
| |
+----------+----------+
|
v
Normal Replication
Depending on the size of the environment and the scope of the missing transactions, this could mean synchronizing particular tables, schemas, or the entire target.
The exact recovery strategy should be determined from the known-good source and target positions rather than simply trying to make Extract show RUNNING.
Logdump is extremely useful here, but it is important to understand what it does.
Logdump reads GoldenGate trail and extract files. It does not read Oracle online redo logs or Oracle archived redo logs.
Oracle's current documentation describes OPEN as opening a GoldenGate trail file or extract file.
For example:
logdump
Then:
GHDR ON
DETAIL ON
DETAIL DATA
OPEN ./dirdat/aa000123
NEXT
Logdump can help determine what actually made it into the GoldenGate trail. Oracle documents commands such as OPEN, NEXT, POSITION, DETAIL DATA, and NEXTTRAIL for navigating and examining trail contents.
This distinction is critical:
Oracle Archive Redo
|
| LogMiner reads this
|
v
GoldenGate Extract
|
v
GoldenGate Trail
|
| Logdump reads this
|
v
GoldenGate Replicat
|
v
PostgreSQL
If the missing transaction never made it through Extract, Logdump cannot recover it.
However, Logdump can be extremely valuable in determining the last transaction, SCN, RBA, table operation or transaction boundary that successfully entered the trail.
That information can help establish exactly where the replication gap begins.
I like to divide an Oracle-to-PostgreSQL GoldenGate incident into four positions:
Oracle Redo Position
|
v
Extract Capture Position
|
v
Trail Position
|
v
Replicat Apply Position
|
v
PostgreSQL
Ask four separate questions:
| Layer | Question |
|---|---|
| Oracle redo | Does the required redo still exist? |
| Extract | What SCN/checkpoint is Extract trying to recover from? |
| Trail | What transactions actually made it into GoldenGate? |
| Replicat | How far did the target actually apply? |
Those four answers provide a much clearer picture than simply asking why Replicat is down.
Once service is restored, I would review the entire archive-retention strategy.
The environment should be designed so that a temporary GoldenGate outage does not immediately put recoverability at risk.
At minimum, I would review:
Archive log retention
RMAN deletion policy
FRA capacity
Extract checkpoint lag
GoldenGate heartbeat lag
Long-running transactions
Bounded Recovery status
BR checkpoint location and health
RAC redo-thread availability
Extract outage history
Trail retention
Monitoring and alert thresholds
One day of redo retention may technically work while everything is healthy, but it leaves very little operational margin when replication stops, infrastructure fails, or a long-running transaction complicates recovery.
When GoldenGate requests an archive log from seven days ago and Oracle retains only one day, don't immediately try to force Extract forward.
First determine why GoldenGate requires that redo.
Check:
INFO EXTRACT <extract>, SHOWCH
SEND EXTRACT <extract>, STATUS
SEND EXTRACT <extract>, SHOWTRANS
Look for the recovery checkpoint, Bounded Recovery state, long-running transactions, Extract lag, required redo thread and required SCN.
If the archive can be restored, restore it.
If it cannot, determine whether the target must be resynchronized.
And use Logdump where it is strongest: determining exactly what GoldenGate successfully wrote to the trail before the failure.
The goal isn't simply:
Extract = RUNNING
Replicat = RUNNING
The real goal is:
Oracle Source
=
GoldenGate Capture
=
GoldenGate Trail
=
PostgreSQL Target
A running replication process is not necessarily a synchronized replication environment.
© 2026 MRC Consulting LLC. All rights reserved.
Oracle, Oracle Database, Oracle GoldenGate, and related product names are trademarks or registered trademarks of Oracle Corporation and/or its affiliates. PostgreSQL is a trademark or registered trademark of the PostgreSQL Community Association of Canada and is used here solely for identification purposes.
Sometimes you just need a quick answer to a deceptively simple question:
How big is this Oracle database?
That question can actually mean several different things:
I originally wrote a small script for this purpose years ago. The following is an updated version that provides a much more complete picture while remaining simple enough to run from the command line.
db_overall_size.ksh#!/bin/ksh
###############################################################################
# Script Name..: db_overall_size.ksh
# Description..: Displays Oracle database storage usage by component
# Author.......:
# Original Date: 04/10/2013
# Version......: 2.0
# Modified.....: Modernized database sizing / capacity version
#
# Reports:
# - Database identification
# - SYSTEM / SYSAUX / UNDO / USERS-TOOLS / Application Data
# - Allocated, used and free datafile space
# - TEMP allocation
# - Online redo physical footprint
# - Control file physical footprint
# - Current database physical footprint
# - Datafile autoextend growth ceiling
# - FRA total, used, free and reclaimable space
# - FRA usage by file type
#
# Notes:
# "Used" datafile space is calculated as allocated space minus free extents.
#
# The physical database footprint shown here does NOT include the FRA.
# The FRA is reported separately because it can contain backups,
# archived logs, flashback logs and other recovery-related files.
###############################################################################
RUN_TS=$(date '+%Y%m%d_%H%M%S')
OUTFILE="db_size_${ORACLE_SID}_${RUN_TS}.txt"
echo "Creating database size report: ${OUTFILE}"
sqlplus -s "/ as sysdba" <<EOF
set linesize 220
set pagesize 200
set trimspool on
set tab off
set feedback off
set verify off
spool ${OUTFILE}
prompt
prompt ================================================================================
prompt DATABASE IDENTIFICATION
prompt ================================================================================
column db_name format a14
column db_unique_name format a25
column database_role format a20
column open_mode format a20
column instance_name format a16
column host_name format a35
column version format a15
select d.name db_name,
d.db_unique_name,
d.database_role,
d.open_mode,
i.instance_name,
i.host_name,
i.version
from v\$database d,
v\$instance i;
prompt
prompt ================================================================================
prompt DATAFILE SPACE BY DATABASE COMPONENT
prompt ================================================================================
column component format a25 heading "Database Component"
column alloc_gb format 999,999,990.99 heading "Allocated|GB"
column used_gb format 999,999,990.99 heading "Used|GB"
column free_gb format 999,999,990.99 heading "Free|GB"
column pct_used format 990.99 heading "Used|%"
with
df as
(
select tablespace_name,
sum(bytes) / power(1024,3) alloc_gb
from dba_data_files
group by tablespace_name
),
fs as
(
select tablespace_name,
sum(bytes) / power(1024,3) free_gb
from dba_free_space
group by tablespace_name
),
ts as
(
select d.tablespace_name,
d.alloc_gb,
d.alloc_gb - nvl(f.free_gb,0) used_gb,
nvl(f.free_gb,0) free_gb
from df d
left join fs f
on f.tablespace_name = d.tablespace_name
),
components as
(
select case
when tablespace_name = 'SYSTEM'
then 'SYSTEM'
when tablespace_name = 'SYSAUX'
then 'SYSAUX'
when tablespace_name like 'UNDO%'
then 'UNDO'
when tablespace_name in ('USERS','TOOLS')
then 'USERS / TOOLS'
else 'APPLICATION DATA'
end component,
sum(alloc_gb) alloc_gb,
sum(used_gb) used_gb,
sum(free_gb) free_gb
from ts
group by
case
when tablespace_name = 'SYSTEM'
then 'SYSTEM'
when tablespace_name = 'SYSAUX'
then 'SYSAUX'
when tablespace_name like 'UNDO%'
then 'UNDO'
when tablespace_name in ('USERS','TOOLS')
then 'USERS / TOOLS'
else 'APPLICATION DATA'
end
)
select component,
alloc_gb,
used_gb,
free_gb,
case
when alloc_gb > 0
then used_gb / alloc_gb * 100
end pct_used
from components
order by
case component
when 'SYSTEM' then 1
when 'SYSAUX' then 2
when 'UNDO' then 3
when 'USERS / TOOLS' then 4
when 'APPLICATION DATA' then 5
else 6
end;
prompt
prompt ================================================================================
prompt OVERALL DATAFILE UTILIZATION
prompt ================================================================================
column current_gb format 999,999,990.99 heading "Current|Allocated GB"
column used_gb format 999,999,990.99 heading "Used|GB"
column free_gb format 999,999,990.99 heading "Free|GB"
column max_gb format 999,999,990.99 heading "Autoextend|Maximum GB"
column growth_gb format 999,999,990.99 heading "Potential|Growth GB"
column pct_used format 990.99 heading "Used|%"
with
df as
(
select sum(bytes) current_bytes,
sum(
case
when autoextensible = 'YES'
then greatest(bytes,maxbytes)
else bytes
end
) max_bytes
from dba_data_files
),
fs as
(
select nvl(sum(bytes),0) free_bytes
from dba_free_space
)
select df.current_bytes / power(1024,3) current_gb,
(df.current_bytes-fs.free_bytes) / power(1024,3) used_gb,
fs.free_bytes / power(1024,3) free_gb,
df.max_bytes / power(1024,3) max_gb,
(df.max_bytes-df.current_bytes) / power(1024,3) growth_gb,
case
when df.current_bytes > 0
then (df.current_bytes-fs.free_bytes) /
df.current_bytes * 100
end pct_used
from df, fs;
prompt
prompt ================================================================================
prompt PHYSICAL DATABASE FOOTPRINT
prompt ================================================================================
column component format a30
column size_gb format 999,999,990.99 heading "Size|GB"
with
datafiles as
(
select nvl(sum(bytes),0) bytes
from dba_data_files
),
tempfiles as
(
select nvl(sum(bytes),0) bytes
from dba_temp_files
),
redo as
(
/*
* MEMBERS is included so multiplexed redo members are represented
* in the physical footprint.
*/
select nvl(sum(bytes * members),0) bytes
from v\$log
),
controlfiles as
(
/*
* V\$CONTROLFILE contains each control file copy.
*/
select nvl(sum(block_size * file_size_blks),0) bytes
from v\$controlfile
)
select 'Datafiles' component,
datafiles.bytes / power(1024,3) size_gb
from datafiles
union all
select 'Tempfiles',
tempfiles.bytes / power(1024,3)
from tempfiles
union all
select 'Online Redo - Physical',
redo.bytes / power(1024,3)
from redo
union all
select 'Control Files - Physical',
controlfiles.bytes / power(1024,3)
from controlfiles
union all
select 'TOTAL DB FOOTPRINT',
(datafiles.bytes +
tempfiles.bytes +
redo.bytes +
controlfiles.bytes) / power(1024,3)
from datafiles,
tempfiles,
redo,
controlfiles;
prompt
prompt ================================================================================
prompt TEMPORARY TABLESPACE
prompt ================================================================================
column temp_gb format 999,999,990.99 heading "TEMP|Total GB"
column temp_used_gb format 999,999,990.99 heading "TEMP|Used GB"
column temp_free_gb format 999,999,990.99 heading "TEMP|Free GB"
column pct_used format 990.99 heading "Used|%"
select sum(tablespace_size) / power(1024,3) temp_gb,
sum(tablespace_size-free_space) / power(1024,3) temp_used_gb,
sum(free_space) / power(1024,3) temp_free_gb,
case
when sum(tablespace_size) > 0
then sum(tablespace_size-free_space) /
sum(tablespace_size) * 100
end pct_used
from dba_temp_free_space;
prompt
prompt ================================================================================
prompt FAST RECOVERY AREA
prompt ================================================================================
column fra_name format a60 heading "FRA Location"
column fra_total_gb format 999,999,990.99 heading "FRA|Limit GB"
column fra_used_gb format 999,999,990.99 heading "Used|GB"
column fra_free_gb format 999,999,990.99 heading "Available|GB"
column reclaimable_gb format 999,999,990.99 heading "Reclaimable|GB"
column pct_used format 990.99 heading "Used|%"
select name fra_name,
space_limit / power(1024,3) fra_total_gb,
space_used / power(1024,3) fra_used_gb,
greatest(space_limit-space_used,0) /
power(1024,3) fra_free_gb,
space_reclaimable / power(1024,3) reclaimable_gb,
case
when space_limit > 0
then space_used / space_limit * 100
end pct_used
from v\$recovery_file_dest;
prompt
prompt ================================================================================
prompt FRA CONTENT BY FILE TYPE
prompt ================================================================================
column file_type format a28
column pct_used format 990.99 heading "FRA|Used %"
column pct_reclaim format 990.99 heading "FRA|Reclaimable %"
column number_of_files format 999,999 heading "Files"
select file_type,
percent_space_used pct_used,
percent_space_reclaimable pct_reclaim,
number_of_files
from v\$recovery_area_usage
where percent_space_used > 0
or percent_space_reclaimable > 0
or number_of_files > 0
order by percent_space_used desc;
prompt
prompt ================================================================================
prompt REPORT COMPLETE
prompt ================================================================================
select 'Generated: ' ||
to_char(sysdate,'MM/DD/YYYY HH24:MI:SS')
from dual;
spool off
exit
EOF
echo
echo "Database size report complete."
echo "Report: ${OUTFILE}"
The original script answered the basic sizing question, but this version separates several concepts that are often mistakenly treated as the same number.
Allocated datafile space tells us how large the current Oracle datafiles are.
Used datafile space estimates how much of that allocation is occupied by Oracle segments by subtracting available free extents.
Maximum autoextend size is especially important for capacity planning. A 4 TB database may currently occupy 4 TB while its datafiles are configured to grow to 9 TB. The second number is often more important when determining whether the underlying ASM disk group has sufficient capacity.
Physical database footprint includes datafiles, tempfiles, redo members, and control-file copies. The Fast Recovery Area is intentionally kept separate because adding the FRA to the database size can double-count storage associated with archived logs, backups, flashback logs, and recovery-related files.
The script also shows FRA reclaimable space. This can be extremely useful when investigating storage pressure because "FRA used" does not necessarily mean all of that space must remain permanently occupied.
With one report a DBA can quickly determine:
That makes the script useful not only for troubleshooting, but also for capacity planning, database migrations, Exadata sizing, cloud migrations, storage forecasting, and environment comparisons.
A single database-size report is useful.
A history of those reports is considerably more valuable.
If the results are collected periodically into a central repository, it becomes possible to calculate:
At that point this little DBA utility becomes the foundation of a simple database capacity-management system rather than merely a one-time sizing script.
Active-active database architectures solve one of the most difficult availability problems in enterprise computing: maintaining two or more databases that are simultaneously available for read and write activity while keeping their data synchronized.
Oracle GoldenGate has supported bidirectional and active-active replication for many years. The fundamental challenge has not changed. Because GoldenGate is an asynchronous logical replication technology, two applications can modify the same logical data at different locations before either change has reached the other database. The resulting collision must either be prevented, detected and resolved, or escalated for human intervention.
Oracle GoldenGate Automatic Conflict Detection and Resolution—commonly referred to as ACDR—provides database-integrated mechanisms for handling many of these situations automatically.
The original Oracle GoldenGate Auto Conflict Detection and Resolution architecture introduced database-maintained timestamp columns, delete tombstones, supplemental logging, delta resolution, and column groups to simplify active-active deployments. Those concepts remain fundamental today. Oracle GoldenGate 26ai extends the operational model around them with modern Microservices Architecture, improved ACDR lifecycle management, enhanced observability, REST-based administration, stronger bidirectional controls, and tighter integration with Oracle AI Database 26ai.
The most important lesson, however, remains unchanged:
Conflict resolution should be the safety net. Good active-active application architecture should prevent as many conflicts as possible before GoldenGate ever needs to resolve them.
GoldenGate supports several replication patterns:
A live-standby architecture is relatively straightforward because only one database normally accepts writes. Replication keeps the secondary database synchronized, but concurrent modification of the same row at both locations is generally avoided.
Active-active is fundamentally different.
Both databases may accept writes:
Application Traffic
/ \
/ \
v v
+---------+ +---------+
| Oracle A| | Oracle B|
| R / W | | R / W |
+----+----+ +----+----+
| |
Extract Extract
| |
+---- OGG ------+
| |
Replicat Replicat
| |
+----+----+ +----+----+
| Oracle A|<--->| Oracle B|
+---------+ +---------+
GoldenGate moves transactions asynchronously between the databases. There is therefore always a finite interval during which each database may contain a slightly different view of the data.
Oracle describes active-active GoldenGate as a bidirectional architecture in which writes may occur against two or more active databases. Because replication is asynchronous, conflict management is required whenever the same data can be changed at multiple sites.
That asynchronous window is the source of the conflict problem.
The best conflict-resolution mechanism is one that rarely has to execute.
Several application and database design techniques substantially reduce the probability of collisions.
Applications can establish logical ownership of data.
Examples include routing writes according to:
A customer located in North America might normally write to Database A while a European customer writes to Database B.
Both systems remain capable of assuming the other's workload during an outage, but routine transaction ownership dramatically reduces simultaneous modification of the same rows.
Duplicate key generation is one of the most avoidable active-active problems.
If Database A and Database B independently generate the same primary key, both transactions may be completely legitimate locally while becoming incompatible when replicated.
For sequence-generated keys, designs can partition the sequence space.
For two databases:
Database A: 1, 3, 5, 7, 9 ...
Database B: 2, 4, 6, 8, 10 ...
For additional sites, an N-way allocation strategy can be used.
Modern applications can alternatively use globally unique identifiers or application-generated keys.
The principle is simple:
A new row created at any active site should receive an identifier that no other active site can independently generate.
Unique constraints beyond the primary key require similar thought. A globally unique primary key does not prevent two users from simultaneously creating the same supposedly unique email address, account number, reservation number, or other business identifier.
Replication latency does more than affect Recovery Point Objective.
It directly affects the size of the conflict window.
If a transaction reaches the second site in milliseconds or seconds, there is relatively little opportunity for another transaction to modify the same row before replication arrives.
If GoldenGate is minutes behind, the probability increases dramatically.
For active-active systems, lag should therefore be treated as a data-consistency risk indicator, not merely an operational performance statistic.
Administrators should monitor at least:
GoldenGate 26ai Microservices Architecture exposes these areas through Administration Service and Performance Metrics Service, as well as REST-based interfaces.
One of the biggest changes since the original GoldenGate active-active material is architectural.
GoldenGate Microservices Architecture was relatively new when the original material was produced. It is now the normal architecture for a new GoldenGate deployment.
In fact, Oracle desupported GoldenGate Classic Architecture beginning in the 23.x generation.
A GoldenGate 26ai deployment contains services such as:
The watchdog and top-level deployment management component.
Manages Extract, Replicat, credentials, parameters, supplemental logging, checkpoint objects, tasks, and process lifecycle.
Moves trail data from source deployments toward destination deployments.
Accepts or retrieves incoming trail files.
Collects operational and performance information across the deployment.
The resulting flow resembles:
Oracle Database A
|
Integrated
Extract
|
Local Trail
|
Distribution
Service
|
HTTPS /
WebSocket
|
Receiver
Service
|
Remote Trail
|
Parallel Replicat
Integrated Mode
|
Oracle Database B
The same architecture operates in the opposite direction for active-active replication.
GoldenGate 26ai also consolidates Microservices monitoring into a common web console rather than requiring administrators to log into the individual services separately.
Another important architectural evolution concerns Replicat.
Traditional Integrated Replicat continues to appear in compatibility requirements for ACDR, but Oracle has deprecated Integrated Replicat as a standalone deployment choice and recommends Parallel Replicat in Integrated mode as the modern alternative.
For a new GoldenGate 26ai Oracle active-active implementation, the logical design is therefore generally:
Integrated Extract
|
Trail
|
Distribution / Receiver
|
Parallel Replicat
Integrated Mode
|
Oracle AI Database
Automatic CDR requires Extract capture and an integrated apply mechanism. Oracle's current documentation explicitly supports Integrated Replicat or Parallel Replicat in Integrated mode and recommends leaving LOGALLSUPCOLS at its default behavior.
Conflict resolution is only part of bidirectional replication.
Another fundamental requirement is preventing this sequence:
Database A
|
| transaction X
v
Database B
|
| Extract captures X again
v
Database A
|
| Extract captures it again
v
Database B
Without loop detection, the same transaction can continuously circulate.
Modern Oracle-to-Oracle GoldenGate accomplishes loop prevention by tagging Replicat-applied transactions.
For example, the Replicat may apply:
DBOPTIONS SETTAG 01
The local Extract then ignores transactions carrying that tag:
TRANLOGOPTIONS EXCLUDETAG 01
Replicat uses tag 00 by default, although deliberate tags can make multi-site architectures easier to understand and troubleshoot.
Oracle specifically recommends EXCLUDETAG as the Oracle bidirectional mechanism.
This is particularly important in GoldenGate 26ai because several older loop-detection parameters associated with earlier architectures have been removed or desupported.
Five broad conflict categories remain useful when designing active-active systems.
| Conflict | Example |
|---|---|
| Insert/Insert | Same logical key created independently at two sites |
| Update/Update | Same row changed differently at two sites |
| Delete/Delete | Same row deleted independently |
| Update/Delete | One site changes a row while another deletes it |
| Column-Level | Different logical portions of the same row are changed |
Not all conflicts should be handled identically.
Consider these examples:
Customer address
Two sites change an address. Latest timestamp might be appropriate.
Inventory
Site A subtracts three items while Site B subtracts two. Choosing one transaction would be wrong; a delta calculation may be required.
Airline seat
Two customers purchase seat 12A. Automatically selecting one transaction may have business consequences requiring additional application processing.
Account status
Headquarters may be authoritative regardless of which transaction happened last. Site-priority resolution may be more appropriate.
Conflict-resolution rules are therefore business rules implemented by replication technology.
Oracle ACDR moves significant conflict-management intelligence into the Oracle Database itself.
ACDR remains specifically an Oracle-to-Oracle capability. GoldenGate provides manual CDR mechanisms for non-Oracle targets and sources.
When ACDR is enabled, Oracle automatically manages much of the metadata needed to determine which change should survive.
Key features include:
This allows conflict management to occur without adding application-visible columns to business tables.
A table can be enabled using DBMS_GOLDENGATE_ADM.
For example:
BEGIN
DBMS_GOLDENGATE_ADM.ADD_AUTO_CDR(
SCHEMA_NAME => 'APP',
TABLE_NAME => 'ORDERS'
);
END;
/
The procedure must be run in the appropriate PDB and should be executed by a properly privileged GoldenGate administrator.
ACDR configuration must exist consistently across the databases participating in replication.
Once enabled, Oracle creates the internal infrastructure necessary for automatic conflict processing.
The most intuitive resolution policy is:
The newest change wins.
ACDR adds an invisible timestamp that represents the modification time of the row.
Assume both databases initially contain:
CUSTOMER_ID = 100
CITY = Atlanta
Timestamp = T0
Database A changes:
CITY = Charlotte
T1
Database B changes:
CITY = Greenville
T2
where:
T2 > T1
When the changes cross, GoldenGate determines that the Database B version is newer.
Eventually both databases converge on:
CITY = Greenville
Timestamp = T2
In current ACDR, latest-timestamp processing handles INSERT, UPDATE, and DELETE conflicts and uses tombstone information where necessary to determine whether a deleted row represents a legitimate later state.
GoldenGate also supports the inverse policy:
The first accepted transaction wins.
Earliest-timestamp resolution is useful when the first successful claim should retain ownership.
Examples might include:
Because deletes and reinserts can otherwise make timestamp reasoning ambiguous, Oracle uses key-version tracking through an internal KEYVER$$ value when this resolution method is enabled.
Deletes create an interesting distributed-data problem.
Suppose Database A deletes a row.
Moments later, an older UPDATE for that row arrives from Database B.
Without additional metadata, Database A cannot distinguish:
"The row never existed"
from:
"The row existed but was deliberately deleted after
the incoming transaction was created."
ACDR solves this with a delete tombstone.
Conceptually:
Base Table
CUSTOMER_ID | NAME
------------+------
101 | Alice
After deletion:
Base Table
<no row>
while internal tombstone metadata retains something like:
CUSTOMER_ID | DELETE_TIMESTAMP
------------+-----------------
101 | T12
An incoming update with timestamp T8 can therefore be correctly recognized as older than the delete at T12 and ignored.
Oracle's 26ai documentation also describes key versioning and primary-key-update tracking in the tombstone infrastructure, allowing ACDR to distinguish different generations of what appears to be the same logical key.
Tombstone tables are operational metadata, and they can grow.
Oracle provides:
DBMS_GOLDENGATE_ADM.PURGE_TOMBSTONES
to remove sufficiently old tombstone entries.
A production active-active implementation should therefore include a deliberate tombstone retention policy.
Retention should always exceed any realistic interval during which an old transaction might arrive.
Purging aggressively merely to save space can undermine the reason the tombstone exists.
GoldenGate lag, outage duration, recovery procedures, retained trails, and replication restart scenarios should all be considered when establishing the policy.
Some data cannot be safely handled using "winner takes all."
Consider inventory:
Initial Quantity = 100
Database A sells ten:
100 -> 90
Database B sells five:
100 -> 95
Latest timestamp would produce either:
90
or:
95
Both answers are wrong.
The correct answer is:
85
Delta conflict resolution captures the mathematical difference associated with each transaction and applies both deltas during convergence.
Configuration uses:
BEGIN
DBMS_GOLDENGATE_ADM.ADD_AUTO_CDR_DELTA_RES(
SCHEMA_NAME => 'APP',
TABLE_NAME => 'INVENTORY',
COLUMN_NAME => 'QUANTITY'
);
END;
/
Oracle describes delta resolution as particularly appropriate for values where concurrent increments and decrements must all survive, including financial-style balances and similar numeric accumulations.
Row-level conflict detection can sometimes be too coarse.
Consider:
EMPLOYEE
--------------------------------
EMPLOYEE_ID
OFFICE
TITLE
SALARY
PHONE
Database A changes:
OFFICE
while Database B changes:
TITLE
Technically, the same row changed at both locations.
Semantically, however, the two transactions do not conflict.
ACDR allows related columns to be divided into column groups.
For example:
LOCATION_GROUP
OFFICE
PHONE
COMPENSATION_GROUP
TITLE
SALARY
Each group receives independent conflict metadata.
Configuration might resemble:
BEGIN
DBMS_GOLDENGATE_ADM.ADD_AUTO_CDR_COLUMN_GROUP(
SCHEMA_NAME => 'HR',
TABLE_NAME => 'EMPLOYEES',
COLUMN_LIST => 'OFFICE, PHONE',
COLUMN_GROUP_NAME => 'LOCATION_CG'
);
END;
/
Now Database A can change the employee's office while Database B changes compensation information, and both changes can converge rather than one unnecessarily overwriting the other.
Current Oracle documentation specifically identifies column groups as a mechanism for allowing different sites to update independent portions of the same row concurrently.
Not every business rule should be determined by time.
An organization may establish an authoritative site.
For example:
Headquarters > Regional System
for selected data.
GoldenGate supports site-priority handling in which a source-target relationship can be configured to overwrite or ignore conflicting changes based upon the designated site.
This enables business policies such as:
Customer demographics:
Most recent update wins.
Credit status:
Headquarters wins.
Inventory:
Delta resolution.
Employee compensation:
HR master site wins.
This illustrates an important architectural point:
There does not need to be one universal conflict-resolution strategy for an entire enterprise.
Resolution should correspond to the semantics of the data.
GoldenGate also supports a policy where a legitimate delete takes precedence over conflicting modifications.
This is useful for data where deletion represents a strong business decision—for example, deactivation or removal of an object that should not be unintentionally resurrected by a late-arriving update.
Oracle uses tombstone key versioning with this strategy so that a truly new generation of the same key can still be distinguished from the row version that was deleted.
One weakness of traditional column-comparison approaches was handling LOB values.
Modern ACDR provides special handling for LOB columns. Each LOB can be managed as its own column group with timestamp information used to determine whether an incoming piecewise LOB update should be applied or discarded.
That allows ACDR to manage data types that are difficult to include directly in ordinary SQL predicates.
A particularly useful 26ai enhancement is not a new conflict algorithm—it is operational maintainability.
Historically, removing ACDR metadata from a very large table could involve costly physical column removal.
With Oracle AI Database 26ai, when ACDR is removed, its internal columns can instead be marked UNUSED.
Administrators can physically remove those columns later during an appropriate maintenance window.
Oracle also supports DBMS_REDEFINITION for ACDR-enabled tables, allowing online reorganization activities such as:
while preserving ACDR hidden timestamp handling during redefinition.
This matters considerably for large enterprise tables where immediately dropping internal columns might otherwise create a lengthy maintenance event.
ACDR should not simply be added indiscriminately to an existing Replicat parameter file.
Oracle specifically notes that Automatic CDR cannot be combined for the same table with certain MAP-level exception/error mechanisms such as:
REPERROR
MAPEXCEPTION
The design must choose the appropriate conflict-handling approach for that table.
This is another reason ACDR should be treated as application architecture rather than merely a Replicat tuning parameter.
DML conflict resolution does not eliminate the need for disciplined DDL deployment.
A good active-active model continues to designate a controlled source for schema changes.
Examples include:
ALTER TABLE
CREATE INDEX
DROP INDEX
ADD COLUMN
RENAME COLUMN
Partition changes
Constraint changes
Schema modifications should not independently originate from multiple sites.
GoldenGate 26ai also changed default handling of tagged DDL. Beginning with the 23.26 release stream, tagged DDL is filtered from capture by default; Oracle Data Pump DDL uses tag 00.
That behavior can be overridden, but Oracle explicitly cautions that tag inclusion in bidirectional configurations can create duplicate DDL or replication loops.
For active-active environments, DDL therefore deserves the same change-control discipline as application deployment.
GoldenGate Studio 26ai provides an additional orchestration option for designing replication pipelines.
For an Active-Active pipeline, Studio can enable Automatic Conflict Detection and Resolution for selected tables and allows configuration of resolution choices including:
This does not eliminate the need to understand ACDR.
It does, however, reduce the amount of manual configuration required for standardized replication deployments.
A production active-active architecture should detect abnormal conditions before they turn into data-quality incidents.
GoldenGate 26ai has expanded its diagnostic interfaces, including an Integrated Diagnostics REST API for performance data associated with integrated Extract and integrated/parallel-integrated Replicat.
Useful operational measurements include:
Extract status
Replicat status
Checkpoint position
End-to-end lag
Transactions/sec
Operations/sec
Trail generation rate
Trail disk utilization
Distribution backlog
Receiver backlog
Discard activity
Replicat errors
Tombstone growth
Conflict frequency
Conflict counts themselves should be monitored.
A correctly functioning ACDR implementation that suddenly starts resolving thousands of conflicts per hour may not be "healthy."
It may be successfully hiding an application-routing problem.
For a modern Oracle-to-Oracle implementation, a strong baseline architecture is:
SITE A SITE B
+----------------+ +----------------+
| Oracle AI DB | | Oracle AI DB |
| Active R/W | | Active R/W |
+-------+--------+ +--------+-------+
| |
Integrated Extract Integrated Extract
| |
Local Trail Local Trail
| |
Distribution Service Distribution Service
| |
+----------- TLS ----------------+
| |
Receiver Service Receiver Service
| |
Remote Trail Remote Trail
| |
Parallel Replicat Parallel Replicat
Integrated Mode Integrated Mode
| |
+--------------------------------+
Bidirectional
ACDR Enabled
SETTAG / EXCLUDETAG Loop Control
Security should use TLS, with GoldenGate 26ai supporting modern TLS capabilities for Microservices communication. Oracle recommends TLS 1.3 where practical.
A production deployment should proceed in deliberate stages.
For every replicated table determine:
Can writes occur at both sites?
Can the same row be updated at both?
What uniquely identifies a row?
Are business unique keys present?
Can deletes occur?
Can primary keys change?
Are LOBs involved?
Classify each table as:
Conflict prevented by routing
Latest timestamp
Earliest timestamp
Site priority
Delta
Column groups
Delete always wins
Manual/business escalation
Establish Replicat tags and corresponding Extract exclusions.
Configure DBMS_GOLDENGATE_ADM.ADD_AUTO_CDR and optional column groups or delta resolution consistently on participating databases.
Use:
Integrated Extract
Parallel Replicat in Integrated mode
Microservices Distribution/Receiver architecture
for new 26ai implementations.
Do not merely test ordinary replication.
Deliberately create:
Insert/Insert
Update/Update
Update/Delete
Delete/Insert
Concurrent delta changes
Concurrent column-group updates
Primary-key updates
Network delay
Replicat outage and recovery
and prove the expected final state.
After every test:
Database A == Database B
should be demonstrated, not assumed.
Implement:
Lag alerting
Trail management
Tombstone purge policy
ACDR configuration auditing
DDL governance
Conflict reporting
Replication health monitoring
The database exposes metadata views for inspecting ACDR configuration.
For example, Oracle documents ALL_GG_AUTO_CDR_COLUMNS as a way to see which columns and column groups participate in automatic conflict resolution.
A mature GoldenGate environment should periodically inventory:
Database
PDB
Schema
Table
ACDR enabled?
Resolution strategy
Column groups
Delta columns
Tombstone table
Replicat
Extract
Trail
Loop-prevention tag
This configuration should be treated as production metadata and preferably tracked through version control or an enterprise configuration repository.
The greatest mistake in an active-active project is assuming that enabling ACDR makes every application automatically active-active safe.
It does not.
ACDR can decide which transaction survives.
It cannot always decide which transaction should survive.
A transaction involving:
an airline seat
a hotel room
a securities trade
a payment
a medical order
an inventory allocation
a legal status change
may require business semantics that no generic timestamp algorithm can infer.
The correct architectural order remains:
1. Prevent conflicts where possible.
2. Detect conflicts reliably.
3. Resolve deterministic conflicts automatically.
4. Escalate business-sensitive conflicts where necessary.
5. Monitor why conflicts occurred.
The underlying ACDR concepts remain remarkably durable.
What has changed is the environment surrounding them.
| Earlier GoldenGate Environment | GoldenGate 26ai Direction |
|---|---|
| Classic and Microservices both common | Microservices Architecture is the strategic architecture |
| Traditional Integrated Replicat | Parallel Replicat in Integrated mode preferred for new designs |
| Individual service administration | Unified Microservices console |
| Basic REST administration | Broader REST-based management and diagnostics |
| ACDR internal-column removal potentially disruptive | 26ai can mark ACDR columns UNUSED |
| Limited table-maintenance options around ACDR | DBMS_REDEFINITION supported with ACDR |
| Older loop-control techniques | SETTAG / EXCLUDETAG is the recommended Oracle pattern |
| Parameter-centric deployment | Web UI, REST API, Admin Client and GoldenGate Studio options |
| Conflict resolution largely operational | Increasingly managed as an architectural policy |
The transition is significant.
The replication engine is no longer the entire product.
GoldenGate has become a managed distributed-data platform in which replication, observability, security, orchestration, and conflict management operate together.
Oracle GoldenGate 26ai provides a mature platform for building active-active Oracle architectures, but the technology works best when conflict handling is designed rather than improvised.
The architecture should begin by determining which site owns which data, ensuring globally unique keys, minimizing replication latency, controlling DDL, and preventing replication loops.
Automatic Conflict Detection and Resolution then provides a powerful second layer of protection.
Through invisible timestamp metadata, delete tombstones, key versioning, column groups, delta resolution, timestamp policies, site priority, and delete handling, Oracle can automatically resolve many conflicts that historically required custom Replicat logic or application code.
GoldenGate 26ai strengthens that model through a modern Microservices Architecture, Parallel Replicat, improved diagnostics, REST-based administration, unified monitoring, and significantly improved ACDR maintenance capabilities.
The final objective is not merely replication.
It is deterministic convergence:
Regardless of where legitimate transactions originate, every participating database must eventually reach the correct and consistent business state.
That is the real measure of a successful GoldenGate active-active architecture.
I recently worked through an interesting Oracle GoldenGate situation during a large enterprise migration from on-premises Oracle databases to OCI.
Names, system identifiers, and some implementation details have been changed, but the technical lessons are real.
The environment had grown to dozens of GoldenGate deployments supporting replication between legacy on-premises systems and OCI. As the migration expanded, the team began seeing very large trail-file storage consumption and concerns about whether additional CPU capacity was required for the GoldenGate infrastructure.
The natural reaction was to ask:
Do we need to scale the GoldenGate deployment?
But before adding CPUs, memory, or infrastructure, we started looking more closely at what GoldenGate was actually doing.
One of the first things that stood out was trail-file retention.
Some deployments had accumulated hundreds of gigabytes of trail files. The immediate assumption could easily have been that the replication workload itself was simply generating enormous volumes of data.
But trail-file size alone doesn't tell you that.
The more important questions are:
PURGEOLDEXTRACTS configured appropriately?If automatic trail cleanup isn't configured correctly, adding CPU doesn't solve the underlying problem.
It just gives an improperly managed environment more resources.
The environment also illustrated another issue I've seen increasingly with infrastructure automation.
Automation is great.
But automation can also automate architectural decisions that should have been reviewed first.
In this case, GoldenGate deployments had multiplied as replication requirements were added. Infrastructure-as-code made creating new deployments easy, but over time the result was a fairly large collection of deployments with varying configurations, authentication states, processes, and operational requirements.
Some deployments contained active Extracts or Replicats.
Others contained little or no active processing.
That creates an important distinction:
Provisioning automation is not the same thing as lifecycle management.
A mature GoldenGate implementation needs both.
For every deployment, I want to be able to answer:
If those questions can't be answered easily, scaling the environment should probably not be the first action.
Cloud interfaces and managed GoldenGate services change how the product is administered, but the fundamentals haven't disappeared.
Extract behavior still matters.
Replicat behavior still matters.
Checkpoints still matter.
Trail-file management still matters.
Error handling still matters.
Sequence handling still matters.
And parameter management absolutely still matters.
During migrations, it is especially important to establish ownership of GoldenGate configuration changes. Application teams may understand the business requirements for the data, while DBAs understand replication mechanics and operational risks.
Both groups need to participate, but someone needs to own the final configuration.
Otherwise, GoldenGate can slowly become a collection of individually reasonable changes that collectively form a difficult environment to operate.
Another lesson involved database sequences.
When replication becomes bidirectional, blindly replicating sequence behavior can create problems. Sequence strategy has to be intentionally designed for the topology.
Depending on the architecture, that may involve excluding sequences from replication, allocating different ranges, using different increment strategies, or resetting sequences appropriately when environments are cloned or transitioned.
The important point is that GoldenGate replicating data successfully does not automatically mean the overall application architecture is safe for bidirectional operation.
The biggest takeaway from this experience is simple:
Don't use infrastructure scaling as the first response to a GoldenGate operational problem.
Before increasing CPU or storage, determine whether the environment actually has a capacity problem.
I would normally check:
Replication
Infrastructure
Trail management
Architecture
Only after those are understood does scaling become an informed decision.
This wasn't really just a GoldenGate issue.
It's a common cloud-migration pattern.
Modern platforms make it incredibly easy to provision infrastructure.
That is valuable.
But the ability to create resources quickly can lead to environments where provisioning grows faster than operational governance.
The solution isn't less automation.
It's better automation.
Ideally, every GoldenGate deployment should be created with:
That's where I think the real opportunity lies.
Instead of treating GoldenGate deployments as individual infrastructure objects, treat the entire replication estate as a managed system.
Provision it. Inventory it. Monitor it. Audit it. Clean it up. And only then decide whether it needs more horsepower.
#Oracle #GoldenGate #OCI #OracleDatabase #CloudMigration #DatabaseAdministration #Exadata #DevOps #Automation #DatabaseArchitecture
How Developers Can Help DBAs Identify and Prevent SQL Performance Problems
Database performance should not be viewed as the responsibility of the DBA alone. Developers often understand the application, business processes, and SQL better than anyone else, while DBAs understand database behavior, optimizer decisions, statistics, indexing, and system resources.
When developers and DBAs share some basic performance information, many problems can be identified before they become production incidents.
One of the most useful skills a developer can have is the ability to read a basic Oracle SQL execution plan.
An execution plan shows how Oracle intends to retrieve the data required by a SQL statement.
For example, Oracle might choose:
Developers do not necessarily need to understand every optimizer operation. They should, however, become familiar enough with plans to recognize major differences.
For example, suppose a query normally uses an index:
INDEX RANGE SCAN
TABLE ACCESS BY INDEX ROWID
After a change, the same SQL begins using:
TABLE ACCESS FULL
That does not automatically mean the new plan is wrong. However, it is something worth investigating, particularly if the table contains millions of rows.
The developer can bring this information to the DBA and say:
"This query previously used an index and now appears to be performing a full table scan."
That gives the DBA a much better starting point than simply reporting:
"The database is slow."
Oracle's optimizer makes many of its decisions based upon statistics.
One of the simplest comparisons developers and DBAs can make involves the number of rows Oracle believes an object contains.
For example:
SELECT owner,
table_name,
num_rows,
last_analyzed
FROM dba_tables
WHERE owner = 'APPLICATION_OWNER'
AND table_name = 'ORDERS';
The NUM_ROWS value represents the approximate number of rows recorded when statistics were gathered.
This can be compared with the actual table:
SELECT COUNT(*)
FROM application_owner.orders;
On very large production tables, running COUNT(*) may itself be expensive, so it should not be performed casually. DBAs may use other techniques to estimate the current row count.
The important concept is the comparison.
For example:
Optimizer Statistics NUM_ROWS: 250,000
Approximate Actual Rows: 12,000,000
Last Analyzed: 45 days ago
This would immediately raise questions about whether the optimizer statistics accurately represent the current data.
Developers should also understand the importance of LAST_ANALYZED.
For example:
SELECT owner,
table_name,
num_rows,
blocks,
last_analyzed
FROM dba_tables
WHERE owner = 'APPLICATION_OWNER'
ORDER BY last_analyzed;
If an application rapidly loads or deletes large amounts of data, statistics can become unrepresentative of the current data.
That can cause Oracle to make decisions based upon an inaccurate picture of the table.
Developers can help considerably by telling DBAs when an application performs activities such as:
This information can help the DBA determine whether statistics need to be gathered differently or whether the normal automatic statistics process is sufficient.
Tables are not the only objects that matter.
Developers and DBAs can also review indexes associated with important SQL.
For example:
SELECT owner,
index_name,
table_name,
num_rows,
distinct_keys,
clustering_factor,
last_analyzed
FROM dba_indexes
WHERE table_owner = 'APPLICATION_OWNER'
AND table_name = 'ORDERS';
Again, the developer does not need to become an Oracle optimizer expert.
The objective is simply to collect enough information so that developers and DBAs can have a meaningful discussion about what Oracle is seeing.
Another very useful technique is to compare execution plans from periods when an application performs well with periods when it performs poorly.
Suppose SQL normally finishes in two seconds but occasionally takes 90 seconds.
Rather than immediately changing the SQL, the DBA and developer should determine whether the execution plan changed.
Useful information includes:
If the fast executions use one PLAN_HASH_VALUE and the slow executions use another, there may be a plan stability issue.
This type of evidence can dramatically shorten the troubleshooting process.
One of the best things a development team can provide a DBA is a collection of SQL statements that represent the application's important workloads.
These might include:
The objective is not necessarily to collect every SQL statement in the application.
Instead, developers and DBAs can identify a manageable set of representative SQL that describes the important workload.
For example:
Project: Customer Billing
SQL-01 Retrieve customer account
SQL-02 Calculate current balance
SQL-03 Retrieve invoice history
SQL-04 Generate monthly statement
SQL-05 Post customer payment
SQL-06 Nightly billing calculation
SQL-07 Aging report
These statements can effectively become a performance test set for the project.
Once representative SQL has been identified, the team can establish a basic performance baseline.
The baseline might contain information such as:
SQL Normal Elapsed Time Plan Hash Value Buffer Gets Typical Rows
SQL-01 0.10 sec 123456789 1,250 1
SQL-02 0.35 sec 345678912 8,500 12
SQL-03 1.20 sec 567891234 45,000 350
SQL-04 8.50 sec 789123456 850,000 12,000
The exact numbers are less important than having a known point of comparison.
Later, if SQL-03 suddenly takes 20 seconds instead of approximately one second, the DBA has something concrete to investigate.
The team can ask:
Without a baseline, everyone is trying to determine whether the current behavior is actually abnormal.
It is important to distinguish between a performance baseline and an Oracle SQL Plan Baseline.
A performance baseline is simply documented historical behavior.
For example:
This SQL normally executes in 1.5 seconds and performs approximately
20,000 logical reads.
An Oracle SQL Plan Baseline is an Oracle optimizer feature that can be used to control or stabilize which execution plans Oracle considers acceptable.
The first should generally come before the second.
The DBA should understand how the SQL normally behaves before deciding whether a particular execution plan should be preserved.
Representative SQL can also be used when making significant changes.
Application Release
|
v
Run Representative SQL
|
v
Compare With Baseline
|
+------ Execution Time
+------ Execution Plan
+------ Buffer Gets
+------ Row Estimates
+------ Object Statistics
|
v
Investigate Significant Differences
|
v
Release to Production
This can be particularly valuable before:
Instead of waiting until users complain, the team can identify regressions during testing.
A DBA looking at the database may see:
SQL_ID: abc123
Executions: 150,000
Average Time: 3.2 seconds
But the DBA may not know what that SQL actually does for the business.
The developer might immediately recognize it as:
"That SQL runs every time a customer opens the order screen."
That changes the conversation considerably.
Similarly, a developer may know:
"This table normally has 100,000 rows, but after month-end processing it temporarily contains 15 million."
That is extremely valuable information for a DBA investigating optimizer behavior.
The DBA may see things developers normally cannot.
For example:
Application
|
v
SQL Statement
|
v
Optimizer
|
+---- Object Statistics
+---- Histograms
+---- Index Statistics
+---- System Statistics
+---- Optimizer Parameters
|
v
Execution Plan
|
v
Database Resources
|
+---- CPU
+---- Memory
+---- I/O
+---- Concurrency
+---- Locks
A SQL statement that appears perfectly reasonable from the application's perspective may behave very differently depending upon the database environment.
That is why performance troubleshooting works best when developers and DBAs investigate together.
A project could adopt a very simple process.
Developers identify the SQL that represents the application's critical workload.
DBAs capture execution plans and basic performance statistics.
Record important tables, indexes, approximate row counts, and statistics information.
Document approximate execution times and resource usage.
Run representative SQL after application, database, statistics, or infrastructure changes.
Developers and DBAs investigate significant regressions together.
Where justified, the DBA can evaluate Oracle capabilities such as SQL Plan Baselines, SQL Profiles, SQL Patches, or other optimizer-management techniques.
Developers do not need to become Oracle DBAs.
DBAs do not need to become application developers.
The objective is to create a common language.
Instead of:
Developer: "The database is slow."
DBA: "The database looks fine."
The conversation becomes:
Developer:
"This SQL normally runs in about two seconds.
It is now taking approximately 40 seconds.
The plan hash value changed.
The ORDERS table has grown significantly,
and the statistics appear to have been gathered before the latest load."
DBA:
"Now we have something specific to investigate."
That type of collaboration can dramatically reduce the amount of time required to diagnose performance problems.
SQL performance is rarely improved by treating the application and the database as completely separate systems.
Developers understand what the application is trying to accomplish.
DBAs understand how Oracle is attempting to accomplish it.
By teaching developers to understand basic execution plans, compare object statistics, recognize changes in row counts, identify important SQL, and establish representative workloads, the development and DBA teams can build a shared performance baseline.
That baseline gives both teams something extremely valuable:
Evidence of what good performance looks like before something goes wrong.
That changes SQL tuning from a reactive exercise into a cooperative and proactive process.
--------------------------------------------------------
Oracle Database 26ai Free Edition Released
Complete installation guide, new AI features,
vector search, and Docker deployment.
--------------------------------------------------------
https://www.oracle.com/downloads/
Oracle Database 21.3.0.0.0
GoldenGate Downloads
https://docs.oracle.com/en/database/goldengate/index.html
Oracle Documentation
https://docs.oracle.com/en/database/index.html
When developing software applications, it's often considered best practice for application vendors to avoid writing code that is specific to a single type of database. Here's why:
1. Portability: One of the primary reasons is to ensure that the application remains database-agnostic. This means that the application can be easily ported to work with different database systems without requiring major code changes.
2. Broader Market Appeal: By keeping applications database-agnostic, vendors can cater to a wider range of customers. Different organizations might have different database preferences based on their existing infrastructure, licensing costs, expertise, and other factors. If an application only works with one type of database, it could limit its potential customer base.
3. Future-Proofing: Technologies and industry trends change. Today's leading database system might not be the leader in a few years. By keeping the code independent of a specific database, vendors ensure that their applications remain relevant and adaptable to future technological shifts.
4. Maintenance: Writing code for a specific database system can introduce complexities in the codebase. If a vendor supports multiple database-specific code paths, it can increase the maintenance overhead, as each database-specific implementation might need updates, bug fixes, or enhancements.
5. Licensing and Cost Concerns: Tying an application to a specific database can introduce licensing complexities and potential additional costs for the end-users. By remaining neutral, application vendors give customers the flexibility to choose a database that fits their budget and licensing preferences.
6. Avoiding Vendor Lock-in: Relying heavily on one database vendor's specific features can lead to a situation known as vendor lock-in. This is where an organization becomes overly dependent on a single supplier's products, leading to reduced flexibility and potentially higher costs in the long run.
7. Use of ORM (Object-Relational Mapping) Libraries: Modern applications often use ORM libraries, such as Hibernate in Java or Entity Framework in .NET, which provide an abstraction over the database. These libraries allow developers to work with databases in an object-oriented manner, often without needing to write SQL code directly. This not only makes the code more database-independent but also reduces the need for database-specific optimizations in many cases.
8. Performance Considerations: While there's a belief that database-specific code can be optimized better, it's often the case that generalized solutions, when done right, can offer performance that is more than adequate for most applications.
9. Database Features: Most relational databases support the SQL standard to some extent. While there are proprietary extensions and features in each database system, most common tasks can be achieved using standard SQL, negating the need for database-specific code.
10. Complexity: Introducing database-specific logic can increase the complexity of deployment, configuration, and testing. By keeping the codebase consistent across all supported databases, developers can ensure a smoother user experience.
In summary, while there are occasional valid reasons for writing database-specific code, for most application vendors, the benefits of maintaining a database-agnostic stance often outweigh the potential advantages of optimizing for a specific database system.
If you're working in an Oracle Real Application Clusters (RAC) environment and you have multiple instances for a single database, each instance has its own unique name. When you want to modify parameters specific to an instance in a shared SPFILE, you can use the `sid` qualifier in the parameter name.
Here's how you can modify the shared SPFILE for a specific instance:
1. **Backup the SPFILE**:
Always backup the SPFILE before making any changes.
```sql
CREATE PFILE='/path_to_backup/init.ora' FROM SPFILE;
```
2. **Connect to the database**:
Start SQL*Plus and connect to the database as a user with SYSDBA privileges.
```bash
sqlplus / as sysdba
```
3. **Modify the SPFILE for a specific instance**:
If you want to modify or add a parameter specific to an instance, use the `sid` qualifier. The format is `parameter_name.sid_instance_name`.
For example, let's assume you have an instance named "ORCL1" and you want to set the `pga_aggregate_target` parameter only for that instance to 200M in the shared SPFILE:
```sql
ALTER SYSTEM SET pga_aggregate_target.ORCL1=200M SCOPE=SPFILE;
```
4. **Restart the specific instance**:
To apply the changes to the specific instance, restart it. If you're connected to the instance you're changing:
```sql
SHUTDOWN IMMEDIATE;
STARTUP;
```
If you're in a RAC environment and connected to a different instance, you'll need to use Oracle's cluster management tools or scripts specific to your setup to restart the desired instance.
5. **Verify your changes**:
Connect to the instance for which you made the changes and verify by querying the `V$PARAMETER` view:
```sql
SELECT NAME, VALUE FROM V$PARAMETER WHERE NAME LIKE 'parameter_name%';
```
Always make sure to thoroughly test any changes in a development or test environment before applying to production. This ensures that you understand the impact of your changes and can avoid potential issues.
This is a GIT guide