Most people I have spoken to are are absolutely convinced that the Extract process has to be running on the source database host. This thinking comes from all Oracle diagrams where the Extract process is located on the source database host. Till OGG version 11.1.1 this was indeed true. Some have heard that the Extract process can be removed from the source host but it can only done with the Integrated Extract process.
There is however the TRANLOGOPTIONS DBLOGREADER parameter which allows the Classic Extract can be run remotely without any need of accessing the redo/archive logs file system (or ASM).
The remote Extract architecture
Many people are convinced that placing the Extract process on the database host is the only legal approach. They claim so, contrary to the fact that Oracle officially advertises the Cloud Architecture whey they provide a separate host for OGG. Thus, remote Extract and remote Replicat.
But if you remove the fixation and look at the arguments then you see that there are many advantages for placing the Extract remotely:
- you don’t eat expensive database licensed CPU,
- the recovery scenarios are more simpler,
- the host where OGG is installed (HUB architecture) is easier to administer,
- no additional software installed on the database host.
Placing the Extract remotely does not require to use the Integrated Extract architecture. Also Classic Extract can be used in this approach. Placing also the Replicat process on the OGG host could give the following HUB Architecture:
Now let’s look what are the requirements to use remote Classic Extract.
OGG version 11.1.1.1
Oracle GoldenGate version 11.1.1.1 which was release around the year 2011 has brought a very useful new feature: the ability to read from ASM file system (where the redo and archive logs could reside) through the database API.
There were 2 requirements to use this feature:
- The version of source database had to be 10.2.0.5 (for 10g) or 11.2.0.2 for (11g),
- The database login had to have the SELECT ANY TRANSACTION role.
To use this functionality TRANLOGOPTIONS DBLOGREADER option had to be added to the Extract param file. And that’s all.
According to the documentation this option was supposed to work only for ASM file system – not for redo and archive logs created on raw disk / file system. But I have not tested it yet.
OGG version 12.1.2
The documentation for OGG 12.1.2 has been updated with the note that the option works also for raw disk and file system. There is event a large note in the documentation that says:
Note: DBLOGREADER also can be used when the redo and archive logs are on regular disk or on a raw device.
Traces
The documentation does not explain how the Extract process manages to read the redo/archive logs files. It just says that this is possible with the “newer ASM API”. I have run Extract process with database tracing turned on to see how what changes are visible when the DBLOGREADER option is active. I have tested if the option is also available for OGG 11.2 I have used for tracing:
- OGG 11.2.1.0.33
- Database 11.2.0.4
It appears to be working with classic file system.
I have tested 2 configurations using Classic Extract:one with DBLOGREADER and one without.
Results
The differences in 2 configurations I have noticed are:
- Using the TRANLOGOPTIONS DBLOGREADER causes OGG to create 3 database connections instead of 2,
- Using TRANLOGOPTIONS DBLOGREADER does not to appear any more when running the Clasic Extract remotely:
2018-02-10 21:26:11 ERROR OGG-00446 Oracle GoldenGate Capture for Oracle, extr.prm: error 13 (Permission denied) opening redo log xxx.log for sequence 5 Not able to establish initial position for sequence 5, rba 102849552.
- With DBLOGREADER there here were some additional SQL queries in traces of one of the connections:
SELECT DECODE(archived, 'YES', 1, 0), status FROM v$log WHERE thread# = :ora_thread AND sequence# = :ora_seq_no
SELECT MAX(sequence#) FROM v$log WHERE thread# = :ora_thread AND archived = 'NO' AND status in ('INVALIDATED', 'CURRENT', 'ACTIVE')
SELECT DECODE(status, 'STALE', 1, 0) FROM v$logfile WHERE member = :log_name
SELECT members FROM v$log where THREAD# = :ora_thread and SEQUENCE# = :ora_seq_no
SELECT 1 FROM V$LOGFILE WHERE(STATUS NOT IN ('STALE', 'INVALID') OR STATUS IS NULL) AND MEMBER <> :log_name AND EXISTS ( SELECT 1 FROM V$LOG WHERE GROUP# = V$LOGFILE.GROUP# AND THREAD# = :ora_thread AND SEQUENCE# = :ora_seq_no ) AND ROWNUM = 1
But unfortunately the “new ASM API” can not be traced with classic SQL tracing. It must be using some low level OCI calls which do not appear in the trace files.
The bottom line is that this configuration works well also with OGG 11.2. Still Classic Extract might be used in favor of Integrated Extract because it has many advantages. And it also works when configured remotely.