Efficient Database Change Capture with Integrated Extract

Introduction

Oracle GoldenGate provides two main modes for capturing database changes: Classic Extract and Integrated Extract.

While the classic mode has been around for years, Integrated Extract (IE) was introduced starting with Oracle 11.2.0.3 to better integrate with Oracle Database’s internal log mining infrastructure, offering improved performance and scalability.

In this post, we’ll cover:

• What Integrated Extract is
• How it works
• Key commands
• Sizing requirements
• Benefits & drawbacks
• Common issues and how to avoid them

What is Integrated Extract?

Integrated Extract uses Oracle’s LogMiner Server inside the database to capture changes directly from redo and archive logs. Instead of parsing logs externally, IE works closely with the database for better efficiency, parallelism, and minimal parsing overhead.

Key Characteristics

• Introduced in Oracle 11.2.0.3+
• Requires Streams Pool memory
• Works with parallelism for higher throughput
• Integrated with database checkpoint tables rather than only checkpoint files

Basic Commands for Integrated Extract Setup

Register Extract

$ dblogin USERID gguser@localhost:1521/pdb1, PASSWORD gguser

$ rgister extract ext2 database
 

Add Integrated Extract

-- Mentione integrated in this means its integrated extract otherwise clasic
$ add extract ext2 integrated tranlog, begin now
$ add exttrail ./dirdat/ext2/et, extract ext2

Set Extract Parameters

$ edit params ext2

userid <user>, Password <password>
tranlogoptions integratedparams (max_sga_size 200, parallelism 2)

Note: define max_sga_size in extract parameter because default it is allocated 1 GB.

Sizing for Integrated Extract & Replicat

• MAX_SGA_SIZE: Typically 1 GB per extract process
• PARALLELISM: Usually set to 2 (can adjust based on load)
• Streams Pool Size Formula:

Check Status of Integrated Extract Process

info extract ext2
status ext2

Benefits of Integrated Extract

✅ Better Performance — Uses parallel mining processes

✅ Reduced CPU Load — Leverages database log mining instead of external parsing

✅ Tight DB Integration — Works seamlessly with Oracle checkpoint tables

✅ Easier Log Management — Handles archive logs more efficiently in steady state

Drawbacks / Limitations

❌ Can’t Instantly Skip archive log to New SCN with ALTER BEGIN NOW

If we need to delete millions of rows, it will affect the GoldenGate replication. We can stop replication and start from a new SCN, performing the operation manually on both the source and target databases if we have enough downtime. For integration, we first need to unregister the extract process, register it again, and then use the Begin now option.

• Even if you ALTER to BEGIN NOW, IE still reads old logs until the new SCN is reached.
• To skip, you must UNREGISTER → REGISTER → ALTER extract.

❌ RMAN Won’t Delete Required Archive Logs

Warning in Alert log file: The archive log needed by streams/GoldenGate capture process are being deleted to free space in recovery area.

• Logs needed by IE remain undeleted until the process no longer needs them.
• Can cause archive log area to fill up if not monitored.

If the integrated capture process stops for several days, the register command will lock the archive log. This means we cannot delete the integrated capture extract process until it is restarted. Alternatively, if it is no longer needed, we can unregister the integrated extract process.

Check capture SCN of extract process:

select capture_name,status,captured_scn,scn_to_timestamp(captured_scn) from dba_capture;

-- if it not delete from unregister command from ggsci tool then we have to manually delete from this package:
exec dbms_capture_adm.drop_capture( 'OGG$CAP_<capturename from upper query>');

❌ Extra Memory Requirement

• Needs properly sized Streams Pool, otherwise capture fails.
• The Extract process defaults to 1 GB of memory. For 3 extract processes, 3 GB of memory is used, plus an additional 0.25% extra memory as needed.

❌ RMAN-08137 Errors

Error RMAN-08137: WARNING: archived log not deleted, needed by standby or upstream capture process.

• Happens if old extracts weren’t unregistered properly.
• You can use above package for delete the registered extract process manually but check its status is disabled first from same queries as:

select capture_name,status,captured_scn,scn_to_timestamp(captured_scn) from dba_capture;

-- if it not delete from unregister command from ggsci tool then we have to manually delete from this package:
exec dbms_capture_adm.drop_capture( 'OGG$CAP_<capturename from upper query>');

---