Setting Flashback Query

To process certain update records, Extract fetches additional row data from the source database.

Oracle GoldenGate fetches data for the following:

User-defined types
Nested tables
XMLType objects

By default, Oracle GoldenGate uses Flashback Query to fetch the values from the undo (rollback) tablespaces. That way, Oracle GoldenGate can reconstruct a read-consistent row image as of a specific time or SCN to match the redo record.

For best fetch results, configure the source database as follows:

Set a sufficient amount of redo retention by setting the Oracle initialization parameters UNDO_MANAGEMENT and UNDO_RETENTION as follows (in seconds).
```
UNDO_MANAGEMENT=AUTO

UNDO_RETENTION=86400 

UNDO_RETENTION can be adjusted upward in high-volume environments.
```
Calculate the space that is required in the undo tablespace by using the following formula.
```
undo_space = UNDO_RETENTION * UPS + overhead
```
Where:
- undo_space is the number of undo blocks.
- UNDO_RETENTION is the value of the UNDO_RETENTION parameter (in seconds).
- UPS is the number of undo blocks for each second.
- overhead is the minimal overhead for metadata (transaction tables, etc.).
Use the system view V$UNDOSTAT to estimate UPS and overhead.
For tables that contain LOBs, do one of the following:
- Set the LOB storage clause to RETENTION. This is the default for tables that are created when UNDO_MANAGEMENT is set to AUTO.
- If using PCTVERSION instead of RETENTION, set PCTVERSION to an initial value of 25. You can adjust it based on the fetch statistics that are reported with the STATS EXTRACT command. If the value of the STAT_OPER_ROWFETCH CURRENTBYROWID or STAT_OPER_ROWFETCH_CURRENTBYKEY field in these statistics is high, increase PCTVERSION in increments of 10 until the statistics show low values.

Grant either of the following privileges to the Oracle GoldenGate Extract user:

GRANT FLASHBACK ANY TABLE TO db_user

GRANT FLASHBACK ON schema.table TO db_user

Oracle GoldenGate provides the following parameters to manage fetching.

Parameter or Command	Description
`STATS EXTRACT` command with `REPORTFETCH` option	Shows Extract fetch statistics on demand.
`STATOPTIONS` parameter with `REPORTFETCH` option	Sets the `STATS EXTRACT` command so that it always shows fetch statistics.
`MAXFETCHSTATEMENTS` parameter	Controls the number of open cursors for prepared queries that Extract maintains in the source database, and also for `SQLEXEC` operations.
`MAXFETCHSTATEMENTS` parameter	Controls the default fetch behavior of Extract: whether Extract performs a flashback query or fetches the current image from the table.
`FETCHOPTIONS` parameter with the `USELATESTVERSION` or `NOUSELATESTVERSION` option	Handles the failure of an Extract flashback query, such as if the undo retention expired or the structure of a table changed. Extract can fetch the current image from the table or ignore the failure.
`REPFETCHEDCOLOPTIONS` parameter	Controls the response by Replicat when it processes trail records that include fetched data or column-missing conditions.