Skip to content

Latest commit

 

History

History
904 lines (589 loc) · 45 KB

user-manual.adoc

File metadata and controls

904 lines (589 loc) · 45 KB
Raw

User Manual

Autor: Adam Leszczyński <aleszczynski@bersler.com>, version: 1.5.1, date: 2024-01-30

This document is the main user manual for OpenLogReplicator.

Table of Contents

Quick start

For a quick start with OpenLogReplicator, please refer to the GitHub repository containing tutorials.

Running in Docker container

Development and testing of OpenLogReplicator is done using Docker containers. It is the best environment to run and test the program. For submitting bugs and feature requests, please use the GitHub repository.

Architecture

OpenLogReplicator is written in C++. It uses Oracle client libraries to connect to the database.

Transactions are directly decoded from redo log files. If possible, online redo logs are parsed, but if it is not possible –- archived redo logs are parsed instead. It is crucial that the process has physical access to files and the files aren’t deleted before they’re processed.

HINT: Refer to Oracle Database manual for defining a retention period for archived redo logs.

The architecture of the program is multithreading. The following threads are used:

  1. Main thread: used for program startup and shutdown

  2. N Reader threads: used to read redo log files from disk — there is one thread per redo log file (group). Most of the time just one thread is active, the other is sleeping and not utilizing the CPU. Only during short time when the log switch is performed and the new redo log file is created, there may be more than one active thread. The reader thread uses buffering to read data from disk. The size of the buffer is configurable.

  3. Checkpoint thread: used to write checkpoint file to disk. This is done every 10 seconds. The checkpoint file contains information about the last processed redo log file and the last processed block in this file. This information is used to restart the program from the last processed position. Writing the checkpoint file together with the schema is not blocking the main parser thread.

  4. Parser thread: used to parse redo log files. Parsing is done serially using just one thread. In case of DDL changes, the schema is updated and the checkpoint file is written to disk. The parser thread is also responsible for sending transactions to output buffer in a format which is configured for output (JSON or Protobuf).

  5. Writer thread: responsible for sending the transactions from the output buffer to the output. There might be more threads used to control proper communication with the output depending on the type of the connector and threads used by the external libraries.

Note
 The only CPU-intensive thread is the parser thread. Other threads perform operations which aren’t CPU intensive, however, might be I/O intensive.

To prevent multiple instances of OpenLogReplicator running simultaneously and overwriting checkpoint files ane another, the program creates a lock on the config file after startup. If another instance is already running and the lock can’t be created, the program exits with an error.

Memory allocation

For most data structures, including disk buffers, output buffers and copy of the redo log vectors, the program uses one big pool of memory. This memory pool is fully controlled by two parameters: min-mb and max-mb. Those parameters allow fully controlling the memory usage of the program. Apart from main memory structures, the program uses dynamic memory allocation from heap for storing metadata (table names, types, column names, etc).

Caution
 Currently also LOB data is stored in dynamic memory, but is planned to be moved to the main memory pool. This means that when the redo log stream contains transactions with many large LOB fields, the memory usage may be higher than configured.
Memory management big picture

Memory management big picture

HINT: Use the metrics module to dynamically observe memory usage.

Sizing memory allocation

There is no single rule for calculating the memory size. The size of used memory depends on many factors:

  • characteristics of schema (number of columns, number of rows, size of rows);

  • characteristics of DML operations;

  • size of transactions;

  • length (in time) of transactions;

  • number of transactions;

  • location of OpenLogReplicator, together with oter applications, on the same host;

  • hardware configuration, amount of available memory;

  • etc.

For best results, performance tests should be made.

Database redo logs

All changes in the database — results of DML commands like INSERT, UPDATE, DELETE — are written to database redo logs. The redo log files contain information about what has been changed. It would not contain information about metadata for every transaction — like number of columns in the table, names, types. Such information should be cached in memory and updated when DDL operations are performed.

To operate properly during startup, OpenLogReplicator needs to collect information about schema during the initial run. It would read database system tables to collect all data about schema. This information is stored in a file and used during the following runs.

Important
 OpenLogReplicator DOES NOT perform the task of initial data load. It never connects to the source data and runs SELECT queries. It only reads redo log files and sends information about changes to the output. For a complete replication solution, you need to use another tool like ETL or restore a database from backup.
Caution
 After start, all redo log files must be available in their entirely. Not a single redo log files block may be missing. If a file or even one block is missing, replication needs to be re-initiated. Schema information should be collected from the beginning, and replication re-initiated. Of course, if the source is a live database, the stream of transactions would be constantly constructed and at the same time parsed and sent to output.

Transaction processing

Database redo log files contain both committed and rolled-back transactions. DML operations are written to redo log files as they’re executed. All operations are flushed when the COMMIT record appears. This is a guarantee point for the database client that all changes have been accepted by the database and are durable and visible to other clients (ACID properties).

Note
 DMLs from different transactions are interleaved in the redo log files.
Important
 For Oracle database, unlike PostgreSQL, the redo log files contain also information about transactions that eventually were rolled back. Or are partially rolled back.

The task of OpenLogReplicator is to sort DML commands and send them to output in proper order:

  1. All rolled back transactions are ignored

  2. All partially rolled back DML commands are ignored

  3. All committed transactions are sent to output as soon as the commit record appears

  4. Transactions, which has no DML commands, or had, but were rolled back, are ignored. Like it didn’t happen.

  5. Transactions are sorted by commit time

  6. Transactions are sent to output without any interleaving

A transaction sent to output may be in one message or may be divided into multiple messages — one message for the beginning of the transaction, one for commit and one for each DML command.

The number of details in the message is configurable.

Interleaved transactions

Transform interleaved transactions to stream

Note
 Depending on user configuration, the output stream in the picture above might contain two or six messages. It is up to the user to decide how the output should look like.

Transaction caching & restart

All transactions which are active (started) are cached in memory. They’re cached as long as the transaction is open. After the transaction is committed and data processed, memory is released. If the transaction is big –- the program would need more memory. OpenLogReplicator never writes any additional files to disk beside of checkpoint and schema file.

Caution
 When OpenLogReplicator is restarted –- it would need to go back to the start of the oldest unprocessed transaction location and start reading database redo logs from this position. This point is called Low Watermark. This may mean going back a long time and process again the same redo log files which have already been processed before the restart appeared. Transactions which were sent to output would not be sent again. This operation may be time and resource consuming. It is recommended to restart OpenLogReplicator only when it is necessary.
Tip
 Configure database redo log retention strategy to leave enough redo log files to be able to restart OpenLogReplicator.
Replication start example

Replication start example

Note
 In the example above, Transaction 2 and Transaction 4 have already been processed and would not be processed again. Since OpenLogReplicator doesn’t cache in the checkpoint files transaction DML commands, all redo log data need which would contain it has to be processed again after restart. In the example above, this would include data for Transaction 1 and Transaction 3.

When run for the first time, OpenLogReplicator would start from the beginning of some redo log file. It is up to the user to decide the moment from which the redo log would be parsed. No matter where the start would be located — there could be always some transactions that are not yet committed.

Caution
 When starting, all transactions that started the moment ago of startup are discarded. Although there are debug options that allow to process them, this is not recommended to use them for production data.

Topology

There are two possible scenarios of running OpenLogReplicator: on the database host and on another host.

Running on the database host

This is the easiest and most efficient solution. But it is not recommended for production systems, as the database performance might be affected when CPU or memory is saturated.

Caution
 OpenLogReplicator may be using extensive memory and CPU. Make sure that there are enough resources for the database to work properly. OpenLogReplicator should use only part of the memory, so that there is memory available for the database.
Program architecture

CDC Architecture

Running on another host

This is the recommended solution. For this scenario, you must make sure that the redo log files are possible to read. This may be achieved by:

  • mounting read only remote filesystem, (for example, using SSHFS);

  • reading from SRDF copy;

  • reading from a standby database;

  • reading just archived redo logs copied by batch file.

Remote access to redo log files

Remote access to redo log files

OpenLogReplicator by default would read online redo logs and process transactions as soon as they’re committed, and this information is written to redo log. But it can also read just archived redo logs – in this scenario transactions would be processed when log switch is performed and redo log is archived.

Output format

The output format is fully configurable. There are two formats implemented: JSON and protocol buffer, but the architecture of the program allows implementing any other format in the future.

JSON format

JSON format is the first implemented format with rapid write speed. The stream is directly constructed from the redo log data. The process of constructing the stream does not use dynamic memory allocation. Instead, the json stream is directly constructed and populated while redo log data is parsed. This makes the speed of the output very fast; internal tests show that it is about 2.5 times faster than the protocol buffer format, even though the size of the output might be longer.

Response: scn_val

The field contains the SCN value associated with the payload data.

The value can be stored in:

  • field scn and stored as decimal (default);

  • field scns and stored as a string in hex format "C" styl (example: "scns":"0x0000008a33ac2263"

See: scn parameter for configuration details.

Response: tm_val

The Time field contains the timestamp related to the payload data. If the transaction contains multiple DML operations, then the timestamps of particular DML operations are possible to distinguish. Instead, just the timestamp related to the commit record is used.

The value can be stored in:

  • Field "tm" and stored using a number;

  • Field "tms" and stored as a string.

See: timestamp parameter for configuration details.

Response: xid_val

The field contains the transaction ID associated with the payload data. It is not present in checkpoint messages.

The value can be stored in:

  • Field "xid" and stored as a string in hex (default). An example value would be: "xid":"0x0009.003.0000568e".

  • Field "xid" — like previous but using decimal numbers, for example, "xid":"9.3.22158".

  • Field "xidn" and stored as a decimal number, (for example, "xidn":22158).

See: xid parameter for configuration details.

Note
 Internally, the transaction ID (XID) is stored using a 64-bit number.

Response: db

The db field contains database name.

See: db parameter for configuration details.

Response: payload.op

The op field contains a string describing the type of the operation. The following operation types are supported:

  • "begin" — begin transaction record;

  • "commit" — commit transaction record;

  • "c" — create record — field would represent INSERT DML operation;

  • "u" — update record — field would represent UPDATE DML operation;

  • "d" — delete record — field would represent DELETE DML operation;

  • "ddl" — DDL operation;

  • "chkpt" — checkpoint record.

Response: payload.schema

A schema field is present only in DML operations and contains an object with the information about schema.

Below are listed the fields of the schema object.

  • "owner" — owner of the schema, optional field, may not be present when schemaless mode is used;

  • "table" — name of the table, in case of schemaless mode the value is OBJ_xxx, where xxx is the object identifier;

  • "obj" — object identifier of the table;

  • "columns" — array of columns (described below).

Response: payload.schema.columns

The schema.columns field is an array of objects, each object describing one column.

The following fields are present in the column object:

  • "name" — name of the column;

  • "type" — type of the column;

  • "length" — length of the column, present for varchar2, raw, char, timestamp, timestamp with time zone, interval year to month, interval day to second, urowid, timestamp with local time zone types;

  • "precision" — precision of the column, present for number type;

  • "scale" — scale of the column, present for number type;

  • "nullable" — true if the column is nullable, false otherwise;

Response: payload.rid

The field contains the row identifier (row ID, rid) of the row.

See: rid parameter for configuration details.

Response: payload.before

The before field contains the old values of the columns. It is present only in update and delete operations. The field is an array of objects, each object describing one column.

Caution
 Only data that is present in the redo log is present in the output. For update operations, values may be missing from the list in case the actual value didn’t change.

See: column parameter for configuration details.

Response: payload.after

The before field contains the new values of the columns. It is present only in insert and update operations. The field is an array of objects, each object describing one column.

Caution
 Only data that is present in the redo log is present in the output. For update operations, values may be missing from the list in case the actual value didn’t change.

See: column parameter for configuration details.

Response: payload.ddl

The field contains the text of the DDL statement.

The DDL payload elements are not present by default.

See: flags parameter for configuration details.

Response: payload.seq

The field is only present for checkpoint messages. It contains information about the sequence number of the redo log file.

Response: payload.offset

The field is only present for checkpoint messages. It contains information the byte offset of the redo log file associated with the checkpoint record.

Response: payload.redo

The field is only present for checkpoint messages. It contains value 1 for checkpoint messages which are related to redo log file switch.

Response: payload.num

The field contains a consecutive number of the payload data.

See: message parameter for configuration details.

Protocol buffer format

The Protocol buffer format is the second implemented format. The field types and names are the same as in the JSON format, so there is no need to explain them again. The writer of this format constructs objects table by table, column by column, field by field and then serializes them to the output stream. Because every field is allocated separately, the memory consumption is higher than in the JSON writer, and internal tests show that the time of generating the stream is about 2.5 times slower.

Output target

Kafka target

OpenLogReplicator is a standalone program that connects to Kafka and sends messages to it. The connection parameters are fully controlled from the program parameters.

Note
 The Kafka target connector is not a Kafka Connect module.

Build instructions

By default, OpenLogReplicator doesn’t have the Kafka writer modules compiled in. The Kafka target module needs to be compiled and liked with the code. For Docker images use the parameter --build-arg WITHKAFKA=1. The Kafka module client is written in C/C++ and uses no Java code or runtime.

Limitations

Caution
 OpenLogReplicator uses the librdkafka library to connect to Kafka. The library has a limitation of 1.000.000.000 bytes (953 MB) for maximum message size. OpenLogReplicator has no limit for message size and can process rows containing multiple LOB columns which are up to 4GB in size. A message can theoretically contain multiple LOB columns, which would exceed the maximum message size for the Kafka client library.

Idempotent producer

OpenLogReplicator can act as an idempotent producer, and this is the default behavior. This is default behavior and is controlled by the enable-idempotence parameter.

Caution
 OpenLogReplicator does not support the Kafka transactional API.

Performance

For performance reasons, OpenLogReplicator sends the message asynchronously and doesn’t wait for the confirmation from Kafka. The number of messages sent simultaneously to Kafka is controlled by the max-messages parameter.

Checkpointing

Kafka target uses checkpointing the same way as the File target.

File target

File target is the simplest target. It writes the output to a file. There are no limitations for the file size or the message size.

Tip
 For reproduction cases, whenever possible, use the file target. Such reproduction requires no setup of the Kafka cluster and is easier to set up.

Checkpointing

To keep track of the position in the redo log, OpenLogReplicator writes the checkpoint to series of entities. Those entities are files by default, but future versions might use other storing engines, like Redis.

There are two types of checkpoints:

  • SCN checkpoint — the SCN number defines the position in the redo log; The file contains the SCN number of the last transaction sent to output.

  • Schema checkpoints — represented in the checkpoint directory as files with SCN number in the name. Every file represents schema of the database which was active at the time of the SCN number in the file name.

Caution
 For performance reasons, the schema checkpoints are not written for every transaction. Instead, files are created periodically, and the schema is written to the file only if it changed since the last checkpoint. When the schema did not change, the schema checkpoint would reference some other schema checkpoint earlier in time.
Caution
 By default, the schema checkpoints are deleted when they are not needed anymore. Different checkpoint options decide how many files are created and how many files are kept. It is also possible to disable the deletion of the schema checkpoints.

Network target

The network target is the most sophisticated module and allows to send the output to virtually any type of target. The receiver of the messages can be written in any language (C, C++, GO, Java, Rust, etc.) and can be running on any platform.

The intention of the network module is to allow maximum integration, while keeping the code simple and easy to maintain.

There are no limitations for the message size for the network module.

The network module is flexible when it comes to communication protocol. Currently, 2 protocols are supported: plain TCP/IP and ZeroMQ, but other protocols can be added easily.

Checkpointing

For the network communication protocol, the receiver of the messages controls the position of the checkpoint. This means that the receiver is responsible for saving the checkpoint and for sending the checkpoint to the sender. The receiver (Target) informs the sender (OpenLogReplicator) that certain transactions have been accepted op to the defined SCN position. In case of connection failure, the sender (OpenLogReplicator) would start from the last checkpoint position provided by the target.

Important
 This allows creating an HA configuration!
Important
 It is not possible to retry the transactions once the Low Watermark has been moved and the transactions were removed from memory.

Architecture

Using the Network target allows moving the responsibility regarding keeping HWM to the target. The target decides which transactions it has already processed and which transactions it has not processed yet. The target can also decide to keep the transactions in memory for a longer time, for example, to allow the target to retry the transactions in case of failure. In case of OpenLogReplicator failure or restart, the target would send the information about the last processed SCN to OpenLogReplicator. Based on information about the last processed SCN, OpenLogReplicator would start sending the transactions from the next SCN. OpenLogReplicator keeps a local repository about schema changes and is able to resume replication from any SCN, but not lower than the last confirmed SCN by the target.

Network target architecture

Network target architecture

On the image above notice a distinction between:

  • checkpoint files with schema — are files containing image of the database schema which was present at the time of the SCN number in the file name; those files are created periodically and are not created for every transaction; they are needed during the restart of OpenLogReplicator to be able to resume replication from any SCN; the client is not controlling the process of creating those files; the files are periodically deleted — when the program decides they are not needed anymore;

  • checkpoint SCN position — is an SCN value controlled by the client and is used to inform OpenLogReplicator about the last processed SCN; this value has to be maintained by the client, every time the client reconnects, OpenLogReplicator is expecting the last processed SCN value, so that it is able to resume replication from the last processed SCN;

Caution
 It is up to the target to decide how often to confirm the SCN to OpenLogReplicator. If the target confirms the SCN too seldom, memory usage of OpenLogReplicator might increase since all transactions are cached in memory.

Network layer

The server defines the communication protocol by setting the type parameter of writer element. The following protocols are supported:

  • network

  • zeromq

This is just the communication layer, and all communication is done the same way. Only one user can connect to the server at the same time.

The communication is bidirectional. The client and server can send messages to each other.

Communication protocol

The following steps are performed after the startup:

  1. The server is waiting for client connection.

  2. The client connects to the server and issues one of the following commands:

    • INFO — the client requests information about the server;

    • START — the client requests to start the replication;

    • REDO — the clients requests replication stream;

  3. After receiving the START command, the server tries to initialize redo log parsing starting from the position defined by the client. If this fails, the server sends an error message and waits for further commands.

  4. After receiving the REDO command, the server starts sending the redo log records to the client. Once the redo stream is started, it is not possible to change the position in the redo log.

Supported features

This chapter describes advanced features of OpenLogReplicator.

DML operations

The following operations are supported:

  • INSERT operation (including multiple row INSERT, but not direct path -– INSERT /*+append*/);

  • UPDATE operation;

  • DELETE operation (including multiple row DELETE).

Table parameters

Tables with the following parameters are supported:

  • null/not null columns;

  • invisible (hidden) columns;

  • columns with null and default values;

  • up to 1000 columns (database maximum till 21c);

  • row chaining/migration;

  • partial rollbacks (rollback to savepoint);

  • partitioned tables;

  • tables with rowdependencies;

  • BASICFILE LOBs.

Transactions that are rolled back aren’t processed.

Transactions are processed as soon as they’re committed (not earlier).

Every transaction is tracked since every transaction can eventually contain a DML operation of a tracked table.

Column types

List of supported column types (with internal Oracle codes):

  • 1 –- varchar2/nvarchar2 (including out of row stored as LOB), supported character sets;

  • 2 –- number/float;

  • 12 –- date;

  • 23 –- raw;

  • 58 — xmltype (full for clob storage, experimental status for binary storage, not supported for 23c);

  • 96 –- char/nchar (list of supported character sets);

  • 100 –- binary_float;

  • 101 –- binary_double;

  • 112 — clob;

  • 113 — blob;

  • 180 –- timestamp;

  • 181 –- timestamp with time zone;

  • 182 — interval year to month;

  • 183 — interval day to second;

  • 208 — urowid;

  • 231 –- timestamp with local time zone;

  • 252 — boolean.

For every unsupported type "?" is used as value.

DDL operations

Changes in the schema are supported.

OpenLogReplicator treats DDL operations as DML operations being executed on system tables. Some system tables are also tracked for changes.

Whenever a DDL command appears in the redo log, the transaction contained two elements:

  • the SQL text of the DDL command (which can be sent to output);

  • a sequence of DML commands on system tables (which can be sent to output as well) — which is processed, and the internal copy of system tables is updated.

The DML commands are parsed, and particular columns are extracted from the system tables and updated in the internal dictionaries. After all changes from one system transaction are processed, OpenLogReplicator deletes internal dictionaries and creates them again. If a DDL transaction impacts one of the tables that are tracked for replication, a message would appear in the output stream.

Caution
 Some of the DDL commands can be internally divided into many transactions. For example, there could be one transaction for creating a table and another transaction for creating indexes on that table. This would mean that the table for some time existed without indexes. Or primary key. This could lead to warnings in the OpenLogReplicator log file informing that the table is missing a primary key defined in the configuration files. In such cases — ignore the warnings.

Supported character sets

OpenLogReplicator supports many character sets which can be used in the source Oracle database.

All character fields are read from the source database in respect to the source character set. The output message always uses Unicode as character encoding and UTF-8 format. OpenLogReplicator doesn’t perform any left-to-right Unicode character replacements.

For test purposes, the character set conversion can be disabled. Check the char parameter for details.

Full list of supported character sets is: AL16UTF16, AL32UTF8, AR8ADOS710, AR8ADOS710T, AR8ADOS720, AR8ADOS720T, AR8APTEC715, AR8APTEC715T, AR8ARABICMACS, AR8ASMO708PLUS, AR8ASMO8X, AR8HPARABIC8T, AR8ISO8859P6, AR8MSWIN1256, AR8MUSSAD768, AR8MUSSAD768T, AR8NAFITHA711, AR8NAFITHA711T, AR8NAFITHA721, AR8NAFITHA721T, AR8SAKHR706, AR8SAKHR707, AR8SAKHR707T, AZ8ISO8859P9E, BG8MSWIN, BG8PC437S, BLT8CP921, BLT8ISO8859P13, BLT8MSWIN1257, BLT8PC775, BN8BSCII, CDN8PC863, CEL8ISO8859P14, CL8ISO8859P5, CL8ISOIR111, CL8KOI8R, CL8KOI8U, CL8MACCYRILLICS, CL8MSWIN1251, D7DEC, D7SIEMENS9780X, DK7SIEMENS9780X, E7DEC, E7SIEMENS9780X, EE8ISO8859P2, EE8MACCES, EE8MACCROATIANS, EE8MSWIN1250, EE8PC852, EEC8EUROASCI, EEC8EUROPA3, EL8DEC, EL8ISO8859P7, EL8MACGREEKS, EL8MSWIN1253, EL8PC437S, EL8PC737, EL8PC851, EL8PC869, ET8MSWIN923, HU8ABMOD, HU8CWI2, I7DEC, I7SIEMENS9780X, IN8ISCII, IS8MACICELANDICS, IS8PC861, IW8ISO8859P8, IW8MACHEBREWS, IW8MSWIN1255, IW8PC1507, JA16EUC, JA16EUCTILDE, JA16EUCYEN, JA16SJIS, JA16SJISTILDE, JA16SJISYEN, JA16VMS, KO16KSC5601, KO16KSCCS, KO16MSWIN949, LA8ISO6937, LA8PASSPORT, LT8MSWIN921, LT8PC772, LT8PC774, LV8PC1117, LV8PC8LR, LV8RST104090, N7SIEMENS9780X, N8PC865, NDK7DEC, NE8ISO8859P10, NEE8ISO8859P4, RU8BESTA, RU8PC855, RU8PC866, S7DEC, S7SIEMENS9780X, SE8ISO8859P3, SF7ASCII, SF7DEC, TH8MACTHAIS, TH8TISASCII, TIMESTEN8, TR8DEC, TR8MACTURKISHS, TR8MSWIN1254, TR8PC857, US7ASCII, US8PC437, UTF8, VN8MSWIN1258, VN8VN3, WE8DEC, WE8DG, WE8HP, WE8ISO8859P1, WE8ISO8859P15, WE8ISO8859P9, WE8MACROMAN8S, WE8MSWIN1252, WE8NCR4970, WE8NEXTSTEP, WE8PC850, WE8PC858, WE8PC860, WE8ROMAN8, ZHS16CGB231280, ZHS16GBK, ZHS32GB18030, ZHT16BIG5, ZHT16CCDC, ZHT16HKSCS, ZHT16HKSCS31, ZHT16MSWIN950, ZHT32EUC, ZHT32TRIS.

The target character set is always Unicode and UTF-8 format.

Replication

During the first run of replication OpenLogReplicator creates a local copy of the source database. The data dictionary may be huge, and by default only a subset of the data is gathered. Only schemas which are configured for replication are copied. Other schemas — not.

If the user wants to replicate other schemas as well. For example, the user might stop replication, modify configuration file, add clauses for other schemas, and start replication again. In such a case, replication would not start because the dictionary data for added schemas is missing. A possible workaround would be to delete all checkpoint files and start replication again.

Schema creation

The user defines the list of schemas/tables which are replicated. Both the schema name and username may contain wildcards using regex expressions.

A schema name may not yet exist when replication starts and be created in the future. OpenLogReplicator would start replicating such a schema when it is created.

Startup

Schema

OpenLogReplicator needs a schema of the source database to be able to identify particular objects and know which are relevant for replication. The schema is loaded from the source database at startup. During consecutive runs, the schema is loaded from the local store (currently: from disk, in the future: also from other stores like Redis).

The schema is a copy of database system tables data dictionary. All data from all tables must be consistent and at the same point in time (same SCN). The dictionary tables are read using clause AS OF SCN which uses the database undo data, but allows that all data is consistent.

If the database has a big load and/or UNDO area is small, it may not be possible. For such cases the user can create the schema using provided script gencfg.sql on a copy of the database.

Refer to examples for details.

Checkpoint files

During the first run of OpenLogReplicator, the checkpoint files are created. By default, they are placed in the checkpoint directory in the current directory but this is a parameter that can be configured. Every checkpoint file contains a copy of the schema and is associated with a particular SCN number.

Position by scn

If the user requests the replication to start from a particular SCN. In such a case, OpenLogReplicator would try to read the database chema at that SCN.

Caution
 Do not choose the starting point too far in the past. It may not be possible to read a consistent copy of system tables.

By time

The starting point can also be defined as a time in the past. In such a case, OpenLogReplicator uses the TIMESTAMP_TO_SCN to convert the timestamp value to particular SCN number. This function is not available when the first run is executed on an Active DataGuard Standby database.

By sequence

Another way of defining the starting point is selecting a particular redo log file and offset in that file. In such a case, all transactions started in the preceding redo log files are ignored.

Checkpointing

Checkpointing is a way to save the current position in the redo log. The structure of redo log files is organized as LWN’s (Log Writer Number). Each LWN has a sequence of blocks which is a unit of redo log records and is processed as a whole. Because of this, the position is advanced only after the whole LWN is processed.

Checkpoint messages

After every LWN is processed, OpenLogReplicator emits a special record called checkpoint record. The checkpoint record contains the current position in the redo log.

An example of checkpoint records:

"payload":[{"op":"chkpt","seq":1763,"offset":15872}]

Checkpoint records can be used as a heartbeat signal, and are enabled by default. Sending of the checkpoint record can be disabled by setting of the "flags":4096 parameter of the reader.

The record contains information about the current sequence of the redo log files (seq) and the offset in the current file (offset). Sometimes it also contains information about the fact that the redo log file has been switched ("redo":true).

Using the checkpoint records is the best way of verifying that the replication is working properly. Oracle database writes LWNs every few seconds, so the checkpoint records are emitted every few seconds as well. In case of any problems, the checkpoint records would stop being emitted.

Checkpoint SCN

The SCN value present in the checkpoint record is used as a checkpoint SCN. When OpenLogReplicator is restarted, it starts from the last checkpoint SCN. Transactions which have commit SCN lower than the checkpoint SCN aren’t processed.

Advanced topics

The following chapter describes some advanced topics, which would normally not be used by a typical user.

Schema changes

OpenLogReplicator can handle schema changes.

To work properly, an initial consistent image of the schema needs to be created. This is done using the gencfg.sql script or during the first run of the program.

The image of database system tables is read just for a selected list of schemas. All objects owned by any of the database users from the list would be tracked in the future. Also objects created, moved to bin, recovered, etc.

Important
 In the case of adding new schema to the replication list — the process needs to be restarted, and the schema needs to be recreated.

While OpenLogReplicator is running and a new object is created which would match the filter, the schema is automatically updated. There is no need to restart the process.

Caution
 It is not a good choice to select too many schemas for tracking, as this can impact on performance.

All DDL operations that modify data structures are tracked: adding, removing columns, changing names, extending type length, etc.

For partitioned tables, the schema is automatically updated when a new partition or subpartition is created. There is no need for user intervention or program restart. In fact, restarting the program would have no impact on the schema because it would not cause a new schema image to be created.

Configuration updates

On startup OpenLogReplicator reads the configuration from the configuration file OpenLogReplicator.json. The configuration file defines which tables are replicated, and which not. To minimize the size of the schema, the system tables are only read for the schemas which are replicated.

Once started, the schema file contains a list of schemas for which the metadata was gathered.

It is not possible to add new schemas.

But it is possible to change the list of tables that are replicated, as long as the list of schemas does not change.

The change can be done offline, while OpenLogReplicator is not running, but also online - when OpenLogReplicator is running. To allow online configuration updates, the configuration file is monitored for changes. Once the timestamp of the last file modification is changed, the configuration is reloaded.

Caution
 The configuration changes are not performed in an atomic way. If some long-running transactions are partially processed, the processed data in memory does not change. This can lead to situations like shown below: a transaction can contain data sets from two different configurations.
Memory buffers before configuration change

Memory buffers before configuration change

Memory buffers after configuration change

Memory buffers after configuration change

In the example above, the configuration file is changed and the M1 table is added to the list of tables which are replicated. This operation does not affect the data that is already processed and present in memory. Transaction T2 would only contain in the output the UPDATE operation for M1 table, but would not contain the INSERT operation which happened before the configuration file was changed.

Important
 The operation of updating parameters from the configuration file does not impact all parameters. Only parameters which define the list of replicated tables are updated. This includes only the tags table and owner both in debug and filter sections.

Offline reader

For cases where there is no physical way to connect the database, OpenLogReplicator offers an offline reader mode.

In such a scenario, a replication may be fully functional, even though there is no network connection to the database. This also implies that there is no need to create a user in the database.

The offline reader mode is activated by setting a parameter "type": "offline" in the reader section.

To start work with the offline reader, a schema file is needed. The schema file can be created using the gencfg.sql script or during the first run of the program when running with online reader.

Caution
 The schema file contains also list locations of redo log files. Those parameters are static and aren’t tracked. Thus, if they change, the schema file needs to be edited manually (this is a json file), or the schema needs to be recreated.

Starting as offline reader

To start replication with online reader, first define the type of the reader as online, provide credentials for the database connection and start the program.

Once the program is running, it would create a schema file — which is visible in the checkpoint folder.

Stop OpenLogReplicator, by simply pressing Ctrl+C or killing the process.

Next, edit configuration file and change the type of the reader to offline, remove credentials for database connection and run OpenLogReplicator.

Manually creating schema file

To create a schema file manually, the user needs to run the gencfg.sql script. The script is located in the scripts folder of the distribution.

Before running the script, it must be edited and the following parameters need to be provided:

  • v_NAME — the logical name used in the schema file;

  • v_USERNAME_LIST — list of usernames to be tracked;

  • v_SCN — the starting SCN for replication.

Save the results of the script to a file with name checkpoint/<name>-chkpt-<scn>.json where <name> is the logical name of the database and <scn> is the starting SCN for replication. Read the resetlogs and activation parameters from the first line of the output of the script. Use the read values during the next steps. Create additional file named checkpoint/<name>-chkpt.json with the following content:

{"database":"<name>","scn":<scn>,"resetlogs":<resetlogs>,"activation":<activation>}

Provide the values for parameters <name>, <scn>, <resetlogs> and <activation> from previous step.

After the files are created, the program can be started with the offline reader type.

DataGuard

For a database which is using DataGuard, the program can be configured to read redo logs from the standby database.

During the initial startup, the program needs to be started on the primary database.

Only physical DataGuard is supported.

Whenever a switch occurs, the program would automatically switch to the new primary/standby database redo log files.

There is no need to add extra options for the DataGuard configuration. OpenLogReplicator would automatically detect the DataGuard configuration and start reading appropriate redo log files depending on the current role of the database that it is connecting to.

High availability

OpenLogReplicator is designed to work in HA environment, especially when Network Mode is being used. The program is not stateless, but keeps all checkpoint data organized. The current implementation stores the files in a directory, but future versions might store this data, for example, in Redis or other external storage.

The best configuration for HA is Stream Mode.

Schemaless mode

Sometimes the schema is not available, but in spite of that, the user still wishes to decode the data from redo log. In this case, the user can use the schemaless mode. The schemaless mode is activated by setting parameter "flags": 2.

Important
 Since table names aren’t available, table filtering is not used. The output would contain all tables, even if they aren’t in the filter.

After activation, OpenLogReplicator would ignore the lack of schema and lack of information about column data types. The data would contain column values in the form of hex strings, since the information about type is not available. Column names and table names are missing since the information about the schema is not available.

Example output for schemaless mode:

"payload":[{"op":"c","schema":{"table":"OBJ_87705"},"after":{"COL_0":"53544f50","COL_1":"787708010d2339"}}]
Tip
 Schemaless mode is extremely useful when the user just has the redo log file and nothing more but wishes to decode the data. It may also be useful to use the adaptive schema mode together with schemaless mode.
Important
 The schemaless mode is not intended for production use. It is intended for debugging and analysis of the data in the redo log.

Adaptive schema node

Adaptive schema mode is a special debugging mode used to analyze the data in the redo log even when the schema file is missing. The adaptive schema mode is activated by setting parameter "flags": 4. This mode works best when used together with schemaless mode.

After enabling first OpenLogReplicator would try to use default schema for system tables. A default schema would be schema from the source database but created with no table selection. The default location for default schema file base-<database version>.json.

Caution
 The default schema is not distributed with OpenLogReplicator. The user needs to create the default schema file manually, by Running OpenLogReplicator with an empty database and copying the schema file from the checkpoint directory.

OpenLogReplicator would act in hybrid mode:

  • when a new schema is created: the schema would be extended by the new objects — for such tables, the schema information would be present in the output;

  • for existing tables: the schema information would be missing in the output — output would be in schemaless mode.

Important
 The adaptive schema mode is not intended for production use. It is intended for debugging and analysis of the data in the redo log.
Caution
 This mode is less restrictive when it comes to schema changes. In cases when OpenLogReplicator would normally stop because of schema change, it would continue in the adaptive schema mode.