What if you could read Oracle's database diary?
Every time Oracle changes data, something important happens behind the scenes: the change is recorded in the redo stream. We normally think of redo as something Oracle uses for crash recovery, but there is much more hiding in there.
In this hands-on lab, I wanted to see what Oracle actually records when we INSERT, UPDATE, DELETE, ROLLBACK, and execute DDL. I also wanted to take it one step further: can we use the redo stream to synchronize another table?
For this experiment, I used Oracle Database 23.26.1 (Oracle AI Database 26ai Free), running in a Docker container, with the FREEPDB1 pluggable database in ARCHIVELOG mode.
The experiments use LogMiner to turn the redo records into SQL_REDO and SQL_UNDO, allowing us to see how Oracle represents the changes.
The short version
- Redo is Oracle's change record used to reproduce changes during recovery and other database operations.
- Undo is used to reverse changes, support
ROLLBACK, and provide read consistency. - A ROLLBACK is itself represented in redo. The original changes are not erased from the redo stream; Oracle records compensating changes.
- DDL cannot be rolled back. DDL implicitly commits the current transaction before it runs.
DROP TABLEcan appear to be an exception, but that is because of the recycle bin and Flashback recovery, not becauseROLLBACKundoes the drop.- With the appropriate supplemental logging, redo can contain enough information to reconstruct row changes using primary keys.
- We can take committed
SQL_REDOand replay it against another table to demonstrate the basic idea behind a change-data feed. - Production replication is much more complicated. Technologies such as Oracle Data Guard, GoldenGate and XStream handle ordering, restart positions, schema changes, failures and many other concerns.
The lab environment
The database used for this experiment is Oracle Database 23.26.1, running as Oracle AI Database 26ai Free in a Docker container.
The experiments are performed inside the FREEPDB1 pluggable database.
I used two SQLcl connections:
- Docker AI — connected as
AIUSERand used to execute the workload. - Local Sys DBA — connected as
SYS AS SYSDBAand used to inspect the database and mine redo.
The workload tables are all prefixed with REDO_DEMO_ so they are easy to identify and clean up later.
Step 1: Check the database configuration
Before running the experiments, I checked the database logging configuration from the SYS session.
🟦 Run as: SYS — SQLcl connection Local Sys DBA
This confirms the database logging mode, force logging setting and supplemental logging state.
select log_mode,
force_logging,
supplemental_log_data_min
from v$database;My environment reported:
ARCHIVELOG | YES | NOThe database is therefore running in ARCHIVELOG mode and FORCE_LOGGING is enabled.
I also checked the online redo log groups.
🟦 Run as: SYS — SQLcl connection Local Sys DBA
select l.group#,
l.sequence#,
l.bytes / 1048576 mb,
l.status,
f.member
from v$log l
join v$logfile f
on f.group# = l.group#
order by 1;My database had three redo log groups, each 200 MB, with one member per group:
/opt/oracle/oradata/FREE/redo01.log
/opt/oracle/oradata/FREE/redo02.log
/opt/oracle/oradata/FREE/redo03.logAt the time of the experiment, group 3 was CURRENT with sequence 15.
Step 2: Create the table we will investigate
Now switch to the AIUSER session. This is the only application schema used for the workload.
🟦 Run as: AIUSER — SQLcl connection Docker AI
create table redo_demo_emp (
emp_id number primary key,
emp_name varchar2(50),
salary number(10,2),
dept varchar2(20)
);AIUSER does not need access to the V$ views used to inspect the redo logs. That is intentional. The workload and the database inspection are kept separate.
Whenever I needed an SCN marker for an experiment, I switched briefly to SYS and recorded the current SCN.
🟦 Run as: SYS — SQLcl connection Local Sys DBA
select current_scn
from v$database;I took SCN markers before and after the individual experiments so that LogMiner could focus on the relevant portion of the redo stream.
Step 3: Redo vs. undo
Before looking at the actual redo records, it is important to separate two concepts that are often confused: redo and undo.
| Redo | Undo | |
|---|---|---|
| Purpose | Reproduce changes during recovery and other database operations | Reverse changes, support ROLLBACK and provide read consistency |
| Stored in | Online and archived redo logs | Undo tablespace |
| Used by | Recovery, Data Guard, LogMiner, GoldenGate and other consumers | ROLLBACK, consistent reads and related features |
A simplified flow looks like this:
Session
|
v
Buffer Cache
|
+---- Undo changes
|
+---- Redo records
|
v
Redo Log Buffer
|
COMMIT
|
v
LGWR
|
v
Online Redo LogsThe important distinction is this:
Redo describes how Oracle can reproduce a change.
Undo describes how Oracle can reverse a change.
There is also an interesting relationship between the two. Undo blocks themselves are protected by redo. This becomes particularly visible when we look at a transaction that is rolled back.
Step 4: How Oracle reads its own redo with LogMiner
Oracle LogMiner provides a way to inspect the contents of redo and reconstruct information about database changes.
The most interesting view for this experiment is V$LOGMNR_CONTENTS.
🟦 Run as: SYS — inside the FREEPDB1 PDB
Once the workload SCNs are known, LogMiner can be started using those SCN boundaries.
begin
dbms_logmnr.start_logmnr(
startscn => &start_scn,
endscn => &end_scn,
options => dbms_logmnr.dict_from_online_catalog
+ dbms_logmnr.no_rowid_in_stmt);
end;
/Then we can inspect the changes for our demonstration table.
select scn,
xid,
operation,
rollback rb,
sql_redo,
sql_undo
from v$logmnr_contents
where seg_owner = 'AIUSER'
and seg_name = 'REDO_DEMO_EMP'
order by scn, rs_id, ssn;When the mining session is finished:
exec dbms_logmnr.end_logmnr;There are a few important things I learned while doing this experiment.
- Inside a PDB, calling
DBMS_LOGMNR.ADD_LOGFILEcan fail withORA-65040. In this experiment, starting LogMiner withSTARTSCNandENDSCNwas sufficient. V$LOGMNR_CONTENTSis available to the session that started LogMiner. It is not a normal view that another session can simply query independently.- Redo contains a lot of database activity that is unrelated to our table. Filtering by
SEG_OWNERandSEG_NAMEmakes the output much easier to understand. XIDhelps identify the transaction associated with a change.- The
ROLLBACKcolumn identifies changes that are part of a rollback. COMMIT_SCNis useful when reconstructing committed transaction order.
On my run, a relatively small workload produced roughly 220 LogMiner rows because of background database activity. Most of those rows were unrelated SYS operations.
Step 5: Experiment A — Is ROLLBACK really an eraser?
This was the first experiment I really wanted to see.
We normally think of ROLLBACK as making the database behave as though the transaction never happened. But what does the redo stream show?
First, I inserted and committed a few employees. Then I started another transaction and deliberately made changes that I would later roll back.
🟦 Run as: AIUSER — SQLcl connection Docker AI
insert into redo_demo_emp
values (99, 'Ghost', 5000, 'TEST');
update redo_demo_emp
set salary = 9999
where emp_name = 'Grace';
rollback;After the rollback, the Ghost row was gone and Grace's salary returned to its original value of 6100.
So far, that is exactly what we expected.
But now look at the redo.
With supplemental logging enabled, the relevant records looked like this:
| SCN | Operation | RB | What it represents |
|---|---|---|---|
| 3893537 | INSERT | 0 | The original Ghost insert |
| 3893538 | UPDATE | 0 | The original update of Grace |
| 3893541 | UPDATE | 1 | Compensating update restoring Grace's salary |
| 3893541 | DELETE | 1 | Compensating delete removing Ghost |
| 3893542 | ROLLBACK | 0 | End-of-transaction marker |
This is the first major takeaway from the experiment:
A rollback does not erase the original redo records.
Oracle records the original changes and then records compensating changes that reverse them.
So the redo stream effectively shows:
INSERT Ghost
UPDATE Grace → 9999
ROLLBACK
DELETE Ghost
UPDATE Grace → 6100
ROLLBACKThe final database state is as though the transaction never happened, but the redo stream still contains the history required to recover that state.
There was also an interesting side discovery during this test: the Ghost insert reused the exact ROWID slot previously occupied by a deleted employee.
That is another useful reminder:
ROWID is a physical locator, not a stable business identity.
Step 6: Experiment B — DDL cannot be rolled back
Now for another classic Oracle behavior.
What happens if we have an uncommitted transaction and then execute DDL?
Let's deliberately create an uncommitted row and then alter the table.
🟦 Run as: AIUSER — SQLcl connection Docker AI
insert into redo_demo_emp
values (4, 'Dave', 3000, 'OPS');
alter table redo_demo_emp
add (email varchar2(80));
rollback;
select *
from redo_demo_emp
where emp_id = 4;The surprising part is that Dave is still there.
The ROLLBACK did not remove the row because the ALTER TABLE implicitly committed the transaction before executing the DDL.
In other words:
INSERT Dave
|
v
ALTER TABLE
|
+---- COMMIT
|
v
ROLLBACK
|
v
Too late — Dave is already committedThis is why running DDL in the middle of a transaction can have consequences that are easy to miss.
TRUNCATE is DDL too
The same principle applies to TRUNCATE.
🟦 Run as: AIUSER — SQLcl connection Docker AI
insert into redo_demo_tmp values (...);
commit;
truncate table redo_demo_tmp;
rollback;
select count(*)
from redo_demo_tmp;The result was:
0The ROLLBACK cannot bring those rows back.
What about DROP TABLE?
This is where Oracle gets slightly more interesting.
If we drop a normal table, Oracle can move it into the recycle bin instead of immediately destroying it permanently.
That means we may be able to recover it using Flashback.
🟦 Run as: AIUSER — SQLcl connection Docker AI
drop table redo_demo_tmp;The table is moved to a recycle-bin name such as:
BIN$...It can then be recovered with:
flashback table redo_demo_tmp
to before drop;The important nuance is that DROP was not rolled back.
The table was recovered because Oracle's recycle bin retained enough information to perform a Flashback recovery.
If the table is dropped with PURGE, or the recycle bin contents are removed, that recovery option is no longer available.
Step 7: What does DDL look like in redo?
DDL is also represented in the redo stream.
Behind the scenes, Oracle has to modify its data dictionary and other internal structures. Those changes themselves generate redo.
For example, creating or altering objects can result in changes involving internal dictionary structures such as OBJ$, TAB$, COL$, SEG$ and RECYCLEBIN$.
Some of these internal changes may appear as UNSUPPORTED operations in LogMiner and may not have useful SQL text.
But LogMiner can also expose the DDL statement itself.
For example, one of my experiments produced:
| SCN | Operation | SQL_REDO |
|---|---|---|
| 3894478 | DDL | alter table redo_demo_emp add (phone varchar2(20)); |
| 3894487 | UPDATE | update "AIUSER"."REDO_DEMO_EMP" set "PHONE" = '555-0100' where "EMP_ID" = '10' and "PHONE" IS NULL; |
| 3894500 | DDL | create index redo_demo_emp_dept_ix on redo_demo_emp(dept); |
Interestingly, the DDL text was preserved as submitted. Even a tracing comment added to the statement by my SQLcl tool was retained.
Step 8: Experiment C — Why supplemental logging matters
At this point we have a way to read redo. But there is an important question:
Does redo always contain enough information to reconstruct a row change?
The answer is: not necessarily in the form you need for downstream processing.
This is where supplemental logging becomes important.
I ran essentially the same workload twice.
The first run was performed without supplemental logging. The second run used supplemental logging and a primary-key log group.
For the second run, I enabled:
🟦 Run as: SYS — SQLcl connection Local Sys DBA
alter database add supplemental log data;
alter table aiuser.redo_demo_emp
add supplemental log data (primary key) columns;The difference in the LogMiner output was significant.
| Without supplemental logging | With supplemental logging | |
|---|---|---|
| Initial INSERTs | Decoded | Decoded |
| Committed UPDATE/DELETE | Not reliably decodable in my test | Decoded with primary-key information |
| Rolled-back changes | Primarily compensating RB=1 rows | Original and compensating changes visible |
| SQL_UNDO | Not consistently usable | Available for the DML rows in my test |
| Row identity | ROWID | Primary key information |
There is an important caveat here.
I changed two things in the second run: database-level minimal supplemental logging and a table-level primary-key log group. Therefore, I did not isolate which individual setting was responsible for every difference I observed.
The Oracle documentation describes minimal supplemental logging as a baseline requirement for LogMiner scenarios, which is consistent with what I observed, but the experiment itself should not be presented as a controlled test of those two settings independently.
Another interesting detail is that V$DATABASE.SUPPLEMENTAL_LOG_DATA_MIN continued to report NO in the PDB view even after the database-level setting was changed. In this environment, the PDB view did not provide the confirmation I initially expected.
For the actual experiment, the LogMiner output and table-level logging configuration were more useful indicators.
A schema-change gotcha
There was another subtle behavior worth documenting.
I mined older redo using DICT_FROM_ONLINE_CATALOG after the table schema had changed.
LogMiner interpreted older records using the current dictionary, which meant that a later-added EMAIL column appeared in the reconstructed output even though the column did not exist when those older changes were originally generated. The value appeared as NULL.
For scenarios where the schema changes between the redo generation and mining, a dictionary captured in the redo can be more appropriate. Oracle provides DBMS_LOGMNR_D.BUILD for building a dictionary and storing it in the redo logs.
Step 9: Experiment D — Can redo synchronize another table?
This was the experiment I was most interested in.
If LogMiner can reconstruct changes as SQL_REDO, can we take those statements and apply them somewhere else?
To keep the experiment controlled, I created a second table with the same structure and seeded it with the state of the source table at the starting SCN.
🟦 Run as: SYS — SQLcl connection Local Sys DBA
create table aiuser.redo_demo_emp_tgt as
select *
from aiuser.redo_demo_emp
where 1 = 0;
alter table aiuser.redo_demo_emp_tgt
add primary key (emp_id);I then seeded the target with the rows that existed at the starting SCN.
1 Alice 5500 IT
3 Carol 6000 IT
4 Dave 3000 OPSNow came the interesting part.
I started LogMiner with COMMITTED_DATA_ONLY and selected only the DML affecting the source table.
begin
dbms_logmnr.start_logmnr(
startscn => 3893508,
endscn => 3893628,
options => dbms_logmnr.dict_from_online_catalog
+ dbms_logmnr.committed_data_only
+ dbms_logmnr.no_rowid_in_stmt);
for r in (
select sql_redo
from v$logmnr_contents
where seg_owner = 'AIUSER'
and seg_name = 'REDO_DEMO_EMP'
and operation in ('INSERT', 'UPDATE', 'DELETE')
order by commit_scn, rs_id, ssn
) loop
execute immediate replace(
rtrim(r.sql_redo, ';'),
'"REDO_DEMO_EMP"',
'"REDO_DEMO_EMP_TGT"'
);
end loop;
commit;
dbms_logmnr.end_logmnr;
end;
/And the result?
The source and target ended up with the same data.
| EMP_ID | Source | Target |
|---|---|---|
| 1 | Alice — 5600 — IT | Alice — 5600 — IT |
| 3 | Carol — 6100 — IT | Carol — 6100 — IT |
| 4 | Dave — 3000 — OPS | Dave — 3000 — OPS |
| 10 | Eve — 7100 — IT | Eve — 7100 — IT |
| 12 | Grace — 6100 — IT | Grace — 6100 — IT |
Frank, who had been inserted and subsequently deleted, was absent from both tables.
The Ghost transaction that had been rolled back never reached the target either, because COMMITTED_DATA_ONLY filtered out the uncommitted transaction.
The experiment also handled the multi-statement changes correctly, including a multi-row update and an insert-update-delete sequence within a transaction.
Why did the synchronization experiment work?
There were several important pieces working together.
- Supplemental logging gave the generated DML enough row identity information to make the statements meaningful outside the original physical location.
- COMMITTED_DATA_ONLY excluded changes from transactions that were ultimately rolled back or never committed.
- The changes were applied in commit order.
- A multi-row update can result in multiple row-level redo records, allowing a downstream consumer to process the individual changes.
- DDL can also be exposed as text, which means schema changes can be part of a larger change-stream design.
This is a useful demonstration of the underlying idea behind redo-based change processing:
Source Database
|
v
Redo Logs
|
v
LogMiner
|
v
SQL_REDO
|
v
Target / ConsumerBut there is a very important distinction.
This is a teaching demonstration, not a replication product.
I would not recommend building production replication by simply looping through V$LOGMNR_CONTENTS and executing the generated SQL.
Production systems need to deal with transaction ordering, restart positions, failures, schema changes, large objects, multiple redo threads, network interruptions, duplicate processing, and many other concerns.
That is where production technologies such as Oracle Data Guard, Oracle GoldenGate and XStream come into the picture.
What else is hiding in redo?
During the experiments, several other observations stood out.
Even an idle database can generate redo
The redo stream is not limited to the SQL statements I manually execute.
Background activity such as scheduler operations, sequence-related activity, statistics and other internal operations can generate redo.
That is why a raw LogMiner query can contain a surprising amount of noise even when the application appears to be doing very little.
Transaction IDs matter
The XID column lets us associate individual change records with their transaction.
This becomes especially useful when trying to understand whether a series of changes belongs to one transaction and whether that transaction eventually committed or rolled back.
SQL_UNDO can be extremely useful
One of the most interesting features exposed by LogMiner is SQL_UNDO.
When sufficient information is available, it can provide SQL that reverses a previously recorded change.
That means the redo stream can potentially be used to reconstruct both the original operation and the SQL required to reverse it.
ARCHIVELOG matters
The online redo logs are continuously reused.
In ARCHIVELOG mode, completed redo log groups can be archived before reuse. This gives Oracle a longer history for recovery and enables technologies that need to consume redo after it has left the current online log.
FORCE_LOGGING matters too
This database had FORCE_LOGGING = YES.
That matters because operations that might otherwise use NOLOGGING behavior are still subject to logging requirements imposed by the database configuration, which is particularly relevant to standby and synchronization scenarios.
What I did not test
This lab intentionally focused on the concepts rather than trying to build a complete replication system.
I did not test:
- Redo volume generated by individual operations.
- Mining across multiple archived log switches.
- Building and using a LogMiner dictionary stored in the redo logs.
- RAC and multiple redo threads.
- LOB-heavy workloads.
- Partitioned tables.
- Index-organized tables.
- Whether table-level primary-key supplemental logging alone, without the database-level setting, would have been sufficient for every observation in the experiment.
These would make interesting follow-up experiments.
Cleanup
Once the experiment was complete, I removed the supplemental logging configuration.
🟦 Run as: SYS — SQLcl connection Local Sys DBA
alter table aiuser.redo_demo_emp
drop supplemental log data (primary key) columns;
alter database drop supplemental log data;Then I removed the demonstration tables.
🟦 Run as: AIUSER — SQLcl connection Docker AI
drop table redo_demo_emp purge;
drop table redo_demo_emp_tgt purge;
drop table redo_demo_tmp purge;The supplemental logging configuration was reverted after the experiment.
Final takeaway
Redo is usually something we think about only when talking about recovery, checkpoints or performance.
But when we actually look inside it, we can see something much more interesting.
Oracle's redo stream records the changes needed to reproduce database state. A rollback doesn't erase the history — it generates compensating changes. DDL generates its own redo and commits the transaction. Supplemental logging adds information that makes row changes much more useful to downstream consumers.
And with LogMiner, we can actually inspect that stream ourselves.
The most interesting part for me was taking the generated SQL_REDO and using it to bring a second table into the same state as the source.
That tiny experiment provides a useful mental model for technologies built around Oracle's change stream:
Application Change
|
v
Oracle Database
|
v
REDO
|
+------------------+
| |
v v
Recovery Change Feed
|
v
CDC / Replication
|
+-----------+-----------+
| |
v v
GoldenGate Other
/ XStream ConsumersSo the next time you run COMMIT or ROLLBACK, remember: Oracle isn't simply changing the database and forgetting what happened.
There is a diary being written underneath it.
No comments: