Autor: Adam Leszczyński <[email protected]>, version: 1.6.1, date: 2024-06-01 |
This document describes installation and configuration guide.
OpenLogReplicator can run together with the database or on a separate host.
The only requirement is that read redo log files must be accessible the whole time when the replication is active. The files can be read through network-mounted filesystem (like SSHFS) when running remotely.
OpenLogReplicator is developed and tested only on a Linux operating system. The preferred Linux distribution is Debian. Official docker images use this distribution. Other distributions aren’t tested.
OpenLogReplicator has no impact and is not affected when other replication engines are being used, including tools using Streams, XStream or LogMiner. Multiple instances of OpenLogReplicator can replicate from the same source database assuming that they don’t share the same path with checkpoints and configuration files.
The following limitations apply to the source database:
-
Supported versions: 11.2, 12.1, 12.2, 18c, 19c, 21c, 23c.
-
Supported editions: XE/DE, SE, SE2, PE, EE.
-
Supported database character set: 138 different, refer to user manual for details.
-
Supported platforms: Linux64, Solaris x64, Solaris Sparc.
-
Supported storage: file system (ext4, btrfs, zfs, xfs, sshfs).
-
Supported database block sizes: 2k, 4k, 8k, 16k, 32k.
-
Required options: none.
-
Both single-tenant and multi-tenant configurations are supported.
-
Supported reading Standby Data Guard databases.
-
Database must be in single instance mode (non RAC).
There are just two strict requirements for the database configuration. In fact, they’re common for absolutely any replication engine.
The database must be working in ARCHIVELOG mode and having enabled MINIMAL SUPPLEMENTAL LOGGING.
Example script to configure database:
SELECT SUPPLEMENTAL_LOG_DATA_MIN, LOG_MODE FROM V$DATABASE;
SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
ALTER DATABASE ARCHIVELOG;
ALTER DATABASE OPEN;
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
ALTER SYSTEM ARCHIVE LOG CURRENT;
SELECT SUPPLEMENTAL_LOG_DATA_MIN, LOG_MODE FROM V$DATABASE;|
Note
|
Minimal supplemental logging is required for OpenLogReplicator to work. It is not a must-have requirement. In case when it is dynamically disabled (i.e., disabled after program startup), OpenLogReplicator might not be able to replicate all transactions. Errors might be printed and behavior might be undefined. Transactions might be missing in the output and replication should be restarted. |
To capture all transactions from the database, it is recommended to have also FORCE LOGGING turned on.
But it is not a must-have requirement.
Having this option turned off results just that the user can dynamically disable logging for some DML operations, leading to transaction loss and data inconsistency.
The following script can be used to configure a database:
SELECT FORCE_LOGGING FROM V$DATABASE;
ALTER DATABASE FORCE LOGGING;
ALTER SYSTEM ARCHIVE LOG CURRENT;
SELECT FORCE_LOGGING FROM V$DATABASE;The default requirements of having enabled MINIMAL SUPPLEMENTAL LOGGING are enough for OpenLogReplicator to work. In such a case, the output contains only modified columns. However, typically this is not enough, and it is required that the output contains also values of columns which haven’t been modified: primary key, unique key or all columns.
|
Important
|
OpenLogReplicator leaves the decision to the user which data is present in the database redo logs. Besides the minimal supplemental logging option, it doesn’t force the user to enable any other supplemental logging options per table. The user can decide which tables should have supplemental logging enabled and which should not. |
A typical configuration requirement is that the primary key is added to supplemental logging.
To add primary key to supplemental logging just for one table, the following code should be executed:
SELECT * FROM ALL_LOG_GROUPS where (LOG_GROUP_TYPE='PRIMARY KEY LOGGING') and OWNER='<SCHEMA>' and TABLE_NAME='<TABLE>';
ALTER TABLE <SCHEMA>.<TABLE> ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY) COLUMNS;
SELECT * FROM ALL_LOG_GROUPS where (LOG_GROUP_TYPE='PRIMARY KEY LOGGING') and OWNER='<SCHEMA>' and TABLE_NAME='<TABLE>';Supplemental logging for primary key columns can be enabled for all tables in the database.
SELECT SUPPLEMENTAL_LOG_DATA_PK FROM V$DATABASE;
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY) COLUMNS;
SELECT SUPPLEMENTAL_LOG_DATA_PK FROM V$DATABASE;|
Tip
|
Adding supplemental logging for primary key columns for all tables in the database has a typically low impact on the performance and redo log size.
Using OpenLogReplicator Performance tracing option can help to determine the impact.
This is a unique feature of OpenLogReplicator, which is not available in any other replication engine.
Setting "trace": 256 in the configuration file will enable tracing.
After finishing redo log file parsing, information is printed with statistics related to the redo log file.
Statistics include a summary of additional space occupied by supplemental log group.
The value is in bytes.
Investigate various options of supplemental logging and choose the one that gives the best performance and redo log size.
|
In cases when the primary key is not defined for a table, it is possible to define a custom key.
SELECT * FROM ALL_LOG_GROUPS where (LOG_GROUP_TYPE='USER LOG GROUP') and OWNER='<SCHEMA>' and TABLE_NAME='<TABLE>';
ALTER TABLE <SCHEMA>.<TABLE> ADD SUPPLEMENTAL LOG GROUP <GROUP NAME> (<COLUMNS>) ALWAYS; SELECT * FROM ALL_LOG_GROUPS where (LOG_GROUP_TYPE='USER LOG GROUP') and OWNER='<SCHEMA>' and TABLE_NAME='<TABLE>';In such a case, it is also needed to define the custom key in the configuration file.
In the filter group in table list add the following entry:
{"owner": "<SCHEMA>", "table": "<TABLE>", "key": "<COLUMNS>"}|
Caution
|
OpenLogReplicator rigorously tests the correctness of the custom key columns. If the defined columns aren’t present in the table, the replication will stop. This setting overrides the defined primary key. |
It is also possible to include all columns in the output for UPDATE operations.
To achieve such behavior, it is required to add all columns to supplemental logging and set column parameter to value 2.
|
Caution
|
Adding all columns to supplemental logging can cause a significant increase of redo log size. This is especially true for tables with many columns. |
For a single table, the following script should be executed in the source database:
SELECT * FROM ALL_LOG_GROUPS where (LOG_GROUP_TYPE='ALL COLUMN LOGGING') and OWNER='<SCHEMA>' and TABLE_NAME='<TABLE>';
ALTER TABLE <SCHEMA>.<TABLE> ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;
SELECT * FROM ALL_LOG_GROUPS where (LOG_GROUP_TYPE='ALL COLUMN LOGGING') and OWNER='<SCHEMA>' and TABLE_NAME='<TABLE>';Supplemental logging for all columns can be enabled for all tables in the database with the following script:
SELECT SUPPLEMENTAL_LOG_DATA_ALL FROM V$DATABASE;
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;
SELECT SUPPLEMENTAL_LOG_DATA_ALL FROM V$DATABASE;Requirements for ONLINE reader type:
-
user in the source database granted with rights to read some system tables (the full list below),
-
physical access to redo log files (online and archived); when running from remote host access through SSHFS is possible.
Requirements for OFFLINE reader type:
-
physical access to read redo log files.
|
Important
|
It is not recommended to run OpenLogReplicator using SYS or SYSTEM account or any account with high privileges to connect to the database. The database user should have JUST the following privileges and absolutely nothing more: |
GRANT SELECT, FLASHBACK ON SYS.CCOL$ TO <USER>;
GRANT SELECT, FLASHBACK ON SYS.CDEF$ TO <USER>;
GRANT SELECT, FLASHBACK ON SYS.COL$ TO <USER>;
GRANT SELECT, FLASHBACK ON SYS.DEFERRED_STG$ TO <USER>;
GRANT SELECT, FLASHBACK ON SYS.ECOL$ TO <USER>;
GRANT SELECT, FLASHBACK ON SYS.LOB$ TO <USER>;
GRANT SELECT, FLASHBACK ON SYS.LOBCOMPPART$ TO <USER>;
GRANT SELECT, FLASHBACK ON SYS.LOBFRAG$ TO <USER>;
GRANT SELECT, FLASHBACK ON SYS.OBJ$ TO <USER>;
GRANT SELECT, FLASHBACK ON SYS.TAB$ TO <USER>;
GRANT SELECT, FLASHBACK ON SYS.TABCOMPART$ TO <USER>;
GRANT SELECT, FLASHBACK ON SYS.TABPART$ TO <USER>;
GRANT SELECT, FLASHBACK ON SYS.TABSUBPART$ TO <USER>;
GRANT SELECT, FLASHBACK ON SYS.TS$ TO <USER>;
GRANT SELECT, FLASHBACK ON SYS.USER$ TO <USER>;
GRANT SELECT ON SYS.V_$ARCHIVED_LOG TO <USER>;
GRANT SELECT ON SYS.V_$DATABASE TO <USER>;
GRANT SELECT ON SYS.V_$DATABASE_INCARNATION TO <USER>;
GRANT SELECT ON SYS.V_$LOG TO <USER>;
GRANT SELECT ON SYS.V_$LOGFILE TO <USER>;
GRANT SELECT ON SYS.V_$PARAMETER TO <USER>;
GRANT SELECT ON SYS.V_$STANDBY_LOG TO <USER>;
GRANT SELECT ON SYS.V_$TRANSPORTABLE_PLATFORM TO <USER>;|
Note
|
Grant script has to be run from SYS account. For multi-tenant databases, the user must be created and given grants in PDB. |
During runtime OpenLogReplicator needs to be able to connect to the source database using OCI connection.
Database connection used by ONLINE reader type is used for:
-
Checking database configuration including location of redo logs,
-
Reading table schema, verifying if schema contains supplemental log groups,
-
Getting archived redo log locations when reading from archived redo logs.
During replication from online redo logs, the connection to the database is idle. No queries are run. During startup, no query is run that would access any data in user tables.
The easiest way to compile source code is to use a docker script from GitHub:
#> git clone https://github.com/bersler/OpenLogReplicator-docker
#> bash build.shYou need at least GCC 4.8 to compile OpenLogReplicator. Refer to Docker images for Ubuntu or CentOS source scripts for details regarding required packages and compilation options.