In one of my previous posts I have described the rationale for using a checkpoint table in the Replicat process. This is a summary of all possible combinations of Replicat process and which of them are able of (not) using checkpoint table.
OGG Version 11.2.1.2
In all versions before 12.1 the Replicat process could work with checkpoint table and without a checkpoint table. You could use either the NODBCHECKPOINT or CHECKPOINTTABLE parameter.
OGG VERSION 12.1, 12.2
Since version 12.1 the legacy mode of Replicat has been called Classic Replicat. And a new Integrated Replicat mode has been introduced (which is using a refreshed version streams API). Both Classic and Integrated Replicat could work in NODBCHECKPOINT and CHECKPOINTTABLE mode.
Additionally there has been introduced another variant of nonintegrated mode called Coordinated Replicat. It could only work in CHECKPOINTTABLE mode. Version 12.2 has brought no changed in this area.
OGG Version 12.3
Since version 12.3 Integrated Replicat could not longer work in NODBCHECKPOINT mode. Two new modes have been introduced: Classic Parallel Replicat and Integrated Parallel Replicat. Both of them required to use CHECKPOINTTABLE.
Compare table
Let’s bring it all together and compare the available modes:
| Version | Classic | Coordinated | Integrated | Parallel Classic | Parallel Integrated |
|---|---|---|---|---|---|
| 11.2 | CHECKPOINTTABLE + NODBCHECKPOINT | – | – | – | – |
| 12.1 | CHECKPOINTTABLE + NODBCHECKPOINT | CHECKPOINTTABLE | CHECKPOINTTABLE + NODBCHECKPOINT | – | – |
| 12.2 | CHECKPOINTTABLE + NODBCHECKPOINT | CHECKPOINTTABLE | CHECKPOINTTABLE + NODBCHECKPOINT | – | – |
| 12.3 | CHECKPOINTTABLE + NODBCHECKPOINT | CHECKPOINTTABLE | CHECKPOINTTABLE | CHECKPOINTTABLE | CHECKPOINTTABLE |
Now let’s go into details and see how checkpoint table is implemented in all those different versions.
For testing I have used the following versions:
- OGG 12.1.2.1.170117 + Oracle 12.1.0.2 + PSU 12.1.0.2.180116,
- OGG 12.2.0.2.2 + Oracle 12.1.0.2 + PSU 12.1.0.2.180116,
- OGG 12.3.0.1.2 + DB 12.2.0.1 + RU 12.2.0.1.180116
All on Linux64. Since OGG behavior might have changed through different patches I am just examining the latest versions.
Classic Replicat NODBCHECKPOINT
Classic Replicat without checkpoint table (NODBCHECKPOINT) is available in all OGG versions and it uses no database checkpointing. It creates no additional tables and no additional DML operations are attached to replicated commands by the Replicat process.
Actually the user which is being used by the Replicat process does not require any create schema rights.
Classic Replicat CHECKPOINTTABLE
After running the ADD CHECKPOINTTABLE command 2 tables are created:
- GGADMIN.CKPTAB
- GGADMIN.CKPTAB_LOX
According to the documentation the GGADMIN.CKPTAB_LOX table should be used only in special circumstances where multiple transactions share the same SCN. This is very untypical. I have never seen any data there.
Of course the name of the checkpoint table can be defined but I will use the name GGADMIN.CKPTAB in this blog post.
Coordinated Replicat (CHECKPOINTTABLE)
Since this is a variant of Classic Replicat it also uses the same checkpoint tables as Classic Replicat. The table stores just one row, and is updated by multiple threads of the Replicat process.
Integrated Replicat CHECKPOINTTABLE
After running the ADD CHECKPOINTTABLE command for Integrated Replicat still the same 2 tables are created:
- GGADMIN.CKPTAB
- GGADMIN.CKPTAB_LOX
There is even one row is inserted in this GGADMIN.CKPTAB and … it seems that it is never used again. At least I have never noticed any activity there. The information in this table is outdated.
Instead all checkpointing information is written to SYS’s tables:
- SYS.STREAMS$_APPLY_PROGRESS – which is also visible through the DBA_GG_INBOUND_PROGRESS (ALL_GG_INBOUND_PROGRESS) view. It represents information about Low and High watermark. This table is updated from time to time. This table is known for anyone who has used Oracle Streams for replication.
- SYS.STREAMS$_APPLY_MILESTONE – which is also visible through the _DBA_APPLY_MILESTONE view. It contains information which transactions are committed (actual transaction id’s from source database). This table is updated together with every committed transaction.
Integrated Replicat NODBCHECKPOINT
The funny thing is that … this mode does not work as one might expect. It is named as a non-checkpointing mode but still checkpointing information is written to the famous SYS.STREAMS$_APPLY_MILESTONE and SYS,STREAMS$_APPLY_PROGRESS tables. The checkpoint table GGADMIN.CKTPTAB is not created, but it was not used anyway in Integrated mode.
It is not recommended by Oracle to use this mode and even in OGG 12.3 this mode is longer available.
Parallel Replicat (CHECKPOINTTABLE)
Running ADD CHECKPOINT TABLE for Parallel Replicat creates exactly the same tables as before:
- GGADMIN.CKPTAB
- GGADMIN.CKPTAB_LOX
But after running the Replicat process additional tables are created like:
- GGADMIN.CKPTAB_REPL
- GGADMIN.CKPTAB_REPL_00A
- GGADMIN.CKPTAB_REPL_00B
- GGADMIN.CKPTAB_REPL_01A
- GGADMIN.CKPTAB_REPL_01B
- GGADMIN.CKPTAB_REPL_02A
- GGADMIN.CKPTAB_REPL_02B
- GGADMIN.CKPTAB_REPL_03A
- GGADMIN.CKPTAB_REPL_03B
- GGADMIN.CKPTAB_REPL_key
Multiple checkpoint tables are used by parallel threads.
The interesting thing is that Integrated Parallel Replicat does not use Streams tables (SYS.STREAMS$_APPLY_PROGRESS and SYS.STREAMS$_APPLY_MILESTONE) for storing checkpointing information. Both modes use only the GGADMIN.CKPTAB* tables.
This is just an introduction about various checkpoint tables. More information about replication checkpointing will appear in the following blog posts.
But if you really really would like to see some very interesting technical internals about checkpointing in Replicat please visit come to POUG 2018 to Sopot on September 7-8th this year for my session about Oracle GoldenGate parallel replication internals.
Pingback: Oracle GoldenGate Coordinated Replicat – does it replicate the transactions atomically? – Bersler