Skip to content
≡

Administrator

Developer

Tools

External Tools

3. How to Use Replication#

Considerations#

There are several conditions apply when establishing replication. If these conditions are not met, replication cannot be used.

Prerequisites#

  • If a conflict occurs during an INSERT, UPDATE, or DELETE operation, the operation is skipped, and a message is written to an error file.
  • If an error occurs during replication, partial rollback is performed. For example, if a duplicate row is found while inserting rows into a table, only the insertion of the duplicate row is canceled, while the remainder of the task is completed as usual.
  • Replication is much slower than the main data provision service

Data Constraints#

  • The table to be replicated must have a primary key.

  • The primary key of the table to be replicated must not be modified.

  • If the column types, NOT NULL constraints, CHECK constraints, unique key indexes, or function-based indexes of the replicated table differ between the two servers, and REPLICATION_SQL_APPLY_ENABLE is set to 1, the replication will operate in SQL Apply Mode, which may result in reduced performance.

Connection Constraints#

  • The maximum number of replication connections possible from one Altibase database is determined by the REPLICATION_MAX_COUNT property.
  • The database character sets and the national character sets must be the same on both servers in order for replication to be possible. Which character set is currently in use can be checked by viewing the values of NLS_CHARACTERSET and NLS_NCHAR_CHARACTERSET in the V$NLS_PARAMETERS performance view.

Replication Target Column Constraints#

  • When an INSERT transaction is replicated, columns that are not replication targets will be filled with NULL values.

  • When configuring unique indexes and function-based indexes of a replicated column and a non-replicated column, it operates in the SQL Apply Mode.

Replication Constraints in EAGER Mode#

The following constraints apply to replication in EAGER mode.

  • To ensure data consistency, replication in EAGER mode is not recommended for more than three nodes.

  • Data is not synchronized unless replication is performed in EAGER mode on both the remote and local servers.

  • If a network failure occurs while replication is being performed in EAGER mode (and even if the server manages to service properly), data consistency cannot be guaranteed. This is because when a network failure occurs, each node interprets the failure as an error on the other node, and both nodes update data.

  • A table can only be replicated as a single corresponding table when replication is performed in EAGER mode. If a table is replicated into two or more tables in EAGER mode, data will be inconsistent and incremental synchronization will fail.

  • Servers on which replication is being performed must have their time synchronized. If an error occurs and the time has not been synchronized, replication can be defective due to the time difference at error detection.

  • Data can be lost if the server abnormally terminates before a committed XLog is applied on disk in EAGER mode. To prevent data loss, specify the recovery option or adjust the values for commit-related properties (COMMIT_WRITE_WAIT_MODE, REPLICATION_COMMIT_WRITE_WAIT_MODE, and REPLICATION_SYNC_LOG)

  • SQL Apply Mode is not available for replication in the EAGER mode.

Partitioned Table Constraints#

The following conditions must be met in order to successfully replicate partitioned tables.

  • The partitioning method must be the same on both the remote server and the local server.

  • For range or list partitions, the partitioning conditions must be the same. If only some partitions are to be replicated, the constraints on only those partitions need to be the same. The same applies to default partitions.

  • For hash partitions, the number of partitions must be the same.

Constraints on Using Replication for Data Recovery#

In order to use replication to perform data recovery, the following restrictions apply:

  • If both the local server and the remote server shut down abnormally, recovery using replication will not be possible.
  • Conflicting data cannot be recovered.
  • A single table cannot be recovered using two or more replication objects.
  • If transactions that have not been transferred are lost, the data cannot be recovered.

Additional Considerations when Using Replication for Data Recovery#

  • If different update operations are performed on the same record on two replicated systems in an Active-Active replication environment, data may be mismatched between the systems.
  • If a network error occurs or replication is stopped according to the setting of the REPLICATION_RECOVERY_MAX_TIME property by the user, data might not be recovered.


CREATE REPLICATION#

Before starting replication, corresponding replication objects must be created on two servers first.

Syntax#

CREATE [LAZY|EAGER] REPLICATION replication_name 
[FOR ANALYSIS | FOR PROPAGABLE LOGGING | FOR PROPAGATION | FOR ANALYSIS PROPAGATION] 
[AS MASTER|AS SLAVE] 
[OPTIONS options_name [option_name ... ] ] 
[WITH { 'remote_host_ip' | 'remote_host_name', remote_host_ port_no [USING conn_type [ib_latency]]}]
...
FROM user_name.table_name [PARTITION partition_name] TO user_name.table_name [PARTITION partition_name]
[,FROM user_name.table_name [PARTITION partition_name] TO user_name.table_name [PARTITION partition_name]
...;

Prerequisites#

Only the SYS user can execute replication-related statements.

Description#

Before replication can be performed, a so-called "replication pair", comprising of a pair of replication objects between which a connection is established, must be set up.

Replication is conducted on a table-by-table or a partition-by-partition basis. Tables or partitions are matched one-to-one.

When creating a replication object, one of the LAZY and EAGER modes can be selected as the default mode. If the replication mode is not specified for a session, this default mode will be used. If no default mode is specified, replication will be performed in LAZY mode.

  • replication_name

    This specifies the name of the replication object to be created. The same name must be used on both the local server and the remote server.

  • FOR ANALYSIS | FOR ANALYSIS PROPAGATION

    This creates the Xlog Sender. For more detailed information about properties, please refer to the Log Analyzer User's Manual.

  • FOR PROPAGABLE LOGGING | FOR PROPAGATION

    Replication receiver writes the logs received with FOR PROPAGABLE LOGGING. FOR PROPAGATION is used to send propagable logs to other target server.

    This function cannot be used with recovery option.

  • AS MASTER or AS SLAVE

    This specifies whether the server is the Master or the Slave. If not specified, the value specified using the REPLICATION_INSERT_REPLACE or REPLICATION_UPDATE_REPLACE property will be used. When attempting to perform handshaking, the following combinations of values will be successful: 0 with 0, 1 with 2, and 2 with 1. Other combinations will fail. (0 = not set; 1 = Master; 2 = Slave)

  • remote_host_ip

    This is the IP address of the remote server.

  • remote_host_port_no

    This is the port number at which the remote server Receiver thread listens. More specifically, this is the port number specified in REPLICATION_PORT_NO in the altibase.properties file on the remote server.

  • conn_type

    This is the communication method with a remote server (TCP/InfiniBand). The default value is TCP.

  • ib_latency

    This is the RDMA_LATENCY option value for rsocket. It can be only inserted when conn_type is IB

  • user_name

    This is the name of the owner of the table to be replicated.

  • table_name

    This is the name of the table to be replicated.

  • partition_name

    This is the name of the partition to be replicated.

  • option_name

    This is the name of the additional functions pertaining to the replication object. For more detailed information, please refer to Extra Features

Error Codes#

Please refer to the Altibase Error Message Reference.

Example#

Suppose that the IP address and port number of the local server are 192.168.1.60 and 25524, and that the IP address and port number of the remote server are 192.168.1.12 and 35524. To replicate a table called employees and one called departments between the two servers, the required replication definition would be as follows:

  • Local server (IP: 192.168.1.60)

    iSQL> CREATE REPLICATION rep1
    WITH '192.168.1.12', 35524
    FROM SYS.employees TO SYS.employees,
    FROM SYS.departments TO SYS.departments;
Create success.

  • Remote Server (IP: 192.168.1.12)

    iSQL> CREATE REPLICATION rep1
    WITH '192.168.1.60', 25524
    FROM SYS.employees TO SYS.employees,
    FROM SYS.departments TO SYS.departments;
Create success.


Starting, Stopping and Modifying Replication using "ALTER REPLICATION"#

Syntax#

ALTER REPLICATION replication_name START [RETRY];

ALTER REPLICATION replication_name QUICKSTART [RETRY];

ALTER REPLICATION replication_name STOP; 

ALTER REPLICATION replication_name RESET;

ALTER REPLICATION replication_name DROP TABLE 
FROM user_name.table_name [PARTITION partition_name] TO user_name.table_name [PARTITION partition_name];

ALTER REPLICATION replication_name ADD TABLE 
FROM user_name.table_name [PARTITION partition_name] TO user_name.table_name [PARTITION partition_name]

ALTER REPLICATION replication_name FLUSH [ALL] [WAIT timeout_sec];

ALTER REPLICATION replication_name SET PROPAGABLE LOGGING [ENABLE|DISABLE];

Prerequisites#

Only the SYS user can execute replication-related statements.

Description#

  • SYNC

    After all of the records in the table to be replicated have been transmitted from the local server to the remote server, replication starts from the current position in the log. In order to prevent another transaction from changing data in the table on which synchronization is to be performed right at the time of determination of the log from which replication will start after synchronization, the Replication Sender Thread obtains an S Lock on the table on which synchronization is to be performed for a short time before synchronization. Therefore, if a synchronization attempt is made while another transaction is updating data in the table to be synchronized, the Replication Sender Thread will wait for the amount of time specified in the REPLICATION_SYNC_LOCK_TIMEOUT property, and will then start replication at the time at which the change transaction ends. If the change transaction is not completed within the amount of time specified in the REPLICATION_SYNC_LOCK_TIMEOUT property, synchronization will fail. If, during synchronization, records on the local server are found to have the same primary key values as records on the remote server, any conflicts are eliminated according to the rules for conflict resolution.

  • TABLE

    This specifies the table that is the target for SYNC replication.

  • PARTITION

    This specifies the partition that is the target for SYNC replication.

  • PARALLEL

    Parallel_factor may be omitted, in which case a value of 1 is used by default. The maximum possible value of parallel_factor is the number of CPUs * 2. If it is set higher than this number, the maximum number of threads that can be created is still equal to the number of CPUs * 2. If it is set to 0 or a negative number, an error message results.

  • SYNC ONLY

    All records in replication target tables are sent from the local server to the remote server. (In this case the Sender thread is not created.) If the same records exist on both the local server and the remote server, sources of conflict are eliminated according to the rules for conflict resolution.

    Because only a single thread is responsible for handling SYNC or SYNC ONLY on disk tables, when some of the tables on which SYNC replication is to be performed are disk tables, setting parallel_factor higher than the number of disk tables confers a performance advantage.

  • START

    Replication will start from the time point of the most recent replication.

  • QUICKSTART

    Replication will start from the current position in the log.

  • START/QUICKSTART RETRY

    When starting or quickstarting replication with the RETRY option, even if handshaking fails, a Sender Thread is created on the local server. Afterwards, once handshaking between the local server and the remote server is successful, replication starts.

    iSQL shows a success message even if the first handshake attempt fails. Therefore, the user must check the result of execution of this command by checking the trace logs or the V$REPSENDER performance view.

    When starting replication without the RETRY option, if the first handshake attempt fails, an error is raised and execution is stopped.

  • STOP

    This stops replication. If a SYNC task is stopped, the transmission of all data to be replicated to the remote server cannot be guaranteed. If a SYNC replication that is underway is stopped, in order to perform SYNC again, all records must be deleted from all replication target tables, and then the SYNC is performed again.

  • RESET

    This command resets replication information (such as the restart SN (Sequence Number)). It can only be executed while replication is stopped, and can be used instead of executing DROP REPLICATION followed by CREATE REPLICATION.

  • DROP TABLE

    A specific table or partition is excluded from the replication target. If the primary transaction log or table metadata log of the target table is within a replication gap at the time DROP TABLE is executed, the replication gap will be skipped, which may result in data inconsistency.

  • ADD TABLE

    This command adds a table or a partition to a replication object. It can only be executed while replication is stopped.

  • FLUSH

    The current session waits for the number of seconds specified by timeout_sec so that the replication Sender thread can send logs up to the log at the time at which the FLUSH statement is executed to the other server. If used together with the ALL keyword, the current session waits until the most recent log, rather than the log at the time at which the FLUSH statement is executed, is sent to the other server

Error Codes#

Please refer to the Error Message Reference.

Example#

Assuming that the name of a replication is rep1, replication can be started in one of the following three ways:

  • Replication is started after the data on the local server are transferred to the remote server.

    iSQL> ALTER REPLICATION rep1 SYNC;
Alter success.

  • Replication is started from the time point at which the replication rep1 was most recently executed.

    iSQL> ALTER REPLICATION rep1 START;
Alter success.

  • Replication is started from the current time point.

    iSQL> ALTER REPLICATION rep1 QUICKSTART;
Alter success.

    Use the following commands to check the status of replication after it has started.

    (Executing on a local server)

    iSQL> SELECT REP_NAME
     , STATUS
     , NET_ERROR_FLAG
     , SENDER_IP
     , SENDER_PORT
     , PEER_IP
     , PEER_PORT
 FROM V$REPSENDER;
REP_NAME              STATUS               NET_ERROR_FLAG       SENDER_IP             SENDER_PORT PEER_IP               PEER_PORT   
--------------------------------------------------------------------------------------------------------------------------------------------
REP1                  1                    0                    192.168.1.33          11477       192.168.1.34          21300     
1 row selected.

    (Executing on a remote server)

    iSQL> SELECT REP_NAME
           , MY_IP
           , MY_PORT
           , PEER_IP
           , PEER_PORT
        FROM V$REPRECEIVER;
REP_NAME              MY_IP                 MY_PORT     PEER_IP               PEER_PORT   
------------------------------------------------------------------------------------------------
REP1                  192.168.1.33          21300       192.168.1.34          7988       
1 row selected.

  • Assuming that the name of a replication is rep1, use the following command to stop replication.

    iSQL> ALTER REPLICATION rep1 STOP;
Alter success.

  • Assuming that the name of a replication is rep1, use the following commands to drop a table from a replication object.

    iSQL> ALTER REPLICATION rep1 STOP;
Alter success.
iSQL> ALTER REPLICATION rep1 DROP TABLE FROM SYS.employees TO SYS.employees;
Alter success.

  • Assuming that the name of a replication is rep1, use the following commands to add a table to a replication object.

    iSQL> ALTER REPLICATION rep1 STOP;
Alter success.
iSQL> ALTER REPLICATION rep1 ADD TABLE FROM SYS.employees TO SYS.employees;
Alter success.

  • If it is desired to check the cumulative time that each Sender replication object has spent waiting for WAIT_NEW_LOG events, execute the following query. This example assumes that the TIMER_THREAD_RESOLUTION property has been set to 1,000,000 microseconds.

    SELECT REP_NAME
     , AVG(WAIT_NEW_LOG)/1000000
  FROM X$REPSENDER_STATISTICS
 WHERE WAIT_NEW_LOG > 0
 GROUP BY REP_NAME
 ORDER BY REP_NAME;

  • If it is desired to check the cumulative time that each Receiver replication object has spent waiting for INSERT_ROW events, execute the following query. This example assumes that the TIMER_THREAD_RESOLUTION property has been set to 1,000,000 microseconds

    SELECT REP_NAME
     , AVG(INSERT_ROW)/1000000
  FROM X$REPRECEIVER_STATISTICS
 WHERE RECV_XLOG > 0
 GROUP BY REP_NAME
 ORDER BY REP_NAME;


DROP REPLICATION#

Syntax#

DROP REPLICATION replication_name;

Prerequisites#

Only the SYS user can execute replication-related statements.

Description#

This command is used to remove a replication object.

However, once a replication has been dropped, it cannot be executed using ALTER REPLICATION START. Additionally, in order to drop a replication object, it is first necessary to stop it using ALTER REPLICATION STOP.

Error Codes#

Please refer to the Error Message Reference.

Example#

  • In the following example, a replication object named rep1 is removed.

    iSQL> ALTER REPLICATION rep1 STOP;
Alter success.
iSQL> DROP REPLICATION rep1;
Drop success.

  • If an attempt is made to remove a replication object without first stopping it, the following error message appears.

    iSQL> DROP REPLICATION rep1;
[ERR-610FE : Replication has already started.]


Extra Features#

Altibase provides the following extra replication features:

  • Recovery Option
  • Replication Gapless Option
  • Parallel Applier Option
  • Replicated Transaction Grouping Option
  • Meta Logging Option
  • Offline Option

The status of replication option can be confirmed by the value of the OPTIONS column in SYS_REPLICATIONS_ meta table. Please refer to the General Reference > Chapter 3. The Data Dictionary for in-depth information.

Recovery Option#

Syntax#

CREATE REPLICATION replication_name OPTIONS RECOVERY ...;
ALTER REPLICATION replication_name SET RECOVERY {ENABLE|DISABLE};

Description#

If abnormal server termination occurs during the replication, the user can recover data by using the main transaction which was executed in normally operating server or replicated transaction logs.

This feature is highly efficient if transaction logs are specified not to be written to disk in COMMIT_WRITE_WAIT_MODE or REPLICATION_COMMIT_WRITE_WAIT_MODE property. For example, a committed transaction can be lost if the system is abnormally shut down. However, in that case, the lost data can be consistent by the replication recovery option.

However, the recovery option cannot be changed while the replication is being processed. In case of not using the recovery option, the recovery related materials the system retains are all released.

Please refer to the General Reference > Chapter 2. Altibase Properties for in-depth information on properties.

Restriction#

The recovery option cannot be used at the same time with the offline option.

Example#

Assuming that the name of a replication object is rep1, the replication recovery option is used as follows:

  • To enable the replication recovery option:

    iSQL> ALTER REPLICATION rep1 SET RECOVERY ENABLE;
Alter success.

  • To disable the replication recovery option:

    iSQL> ALTER REPLICATION rep1 SET RECOVERY DISABLE;
Alter success.

Replication Gapless Option#

Syntax#

CREATE REPLICATION replication_name OPTIONS GAPLESS ...;
ALTER REPLICATION replication_name SET GAPLESS [ENABLE|DISABLE};

Description#

The replication gapless option dissolves replication gaps. If this option is specified and the sender expects the replication gap to still exist after the amount of time set for the REPLICATION_GAPLESS_ALLOW_TIME property, the transaction commit is delayed to buy time for the replication gap to dissolve. The user can set an appropriate value for the REPLICATION_GAPLESS_MAX_WAIT_TIME property to prevent too much time being spent waiting for the replication gap to dissolve before committing the transaction. However, the user should be reminded that delaying transaction commits can degrade service performance.

For more detailed information about properties, please refer to the General Reference > Chapter 2. Altibase Properties.

Restriction#

The replication gapless option can only be specified when replication is being performed in LAZY mode.

Example#

Assume that there is a replication object named rep1. Specify the replication gapless option to dissolve the replication gap for rep1.

  • Specify the replication gapless option.

    iSQL> CREATE REPLICATION rep1 OPTION GAPLESS;
WITH '192.168.1.12', 35524
FROM SYS.employees TO SYS.employees,
FROM SYS.departments TO SYS.departments;;
CREATE success.

  • Enable the gapless option.

    iSQL> ALTER REPLICATION rep1 SET GAPLESS ENABLE;
Alter success.

Parallel Applier Option#

Syntax#

CREATE REPLICATION replication_name OPTIONS PARALLEL receiver_applier_count [buffer_size]...;
ALTER REPLICATION replication_name SET PARALLEL receiver_applier_count [buffer_size];

Description#

The parallel applier creates several appliers that are to apply XLogs to the Storage Manager. The parallel receiver applier option enhances replication performance.

XLogs that the sender sends to the receiver are distributed to the applier in the unit of transactions, so that the XLogs can be applied in parallel. DML statements are executed in parallel and this enhances replication performance.

Parallel execution requires the synchronization of transaction commits among the parallel appliers to ensure data consistency. During this synchronization process, all threads other than the appliers that are committing transactions wait; the user can anticipate more performance enhancement with a shorter synchronization process. Likewise, the user can anticipate performance degradation if the number of concurrently running transactions is smaller than the number of appliers, because appliers can only execute DML statements under concurrently running transactions, and this would incur unnecessary applier management.

The parallel receiver applier option is suitable for replications with long-running transactions. Replications with short-running transactions encounter frequent synchronization processes for transaction commits; specifying this option would naturally degrade performance.

receiver_applier_count indicates the number of parallel appliers and can take a value between 0~512. If this value is set to 0, there will be no parallel appliers; in this case, receivers will do the appliers' job.

Receivers and appliers use queues to pass XLogs. The REPLICATION_RECEIVER_APPLIER_QUEUE_SIZE property determines the maximum number of XLogs that can be sent. If no value is entered, the value is set according to the following property.

The buffer_size property specifies the initial size of the queue. Values range from 0 to 1 TB. If this value is not specified or if the number of parallel applier queues is less than the property REPLICATION_RECEIVER_APPLIER_QUEUE_SIZE value, the number of parallel applier queues is set to the value specified in the property REPLICATION_RECEIVER_APPLIER_QUEUE_SIZE.

If the unit (K, M, G) is not input, it is recognized in units of Megabytes.

For more detailed information about properties, please refer to the General Reference > Chapter 2. Altibase Properties.

Example#

iSQL> CREATE REPLICATION replication_name OPTIONS PARALLEL receiver_applier_count 100M...;
iSQL> ALTER REPLICATION replication_name SET PARALLEL receiver_applier_count 100;

Restriction#

The parallel receiver applier option can only be specified when replication is being performed in LAZY mode.

Replicated Transaction Grouping Option#

Syntax#

CREATE REPLICATION replication_name OPTIONS GROUPING...;
ALTER REPLICATION replication_name SET GROUPING [ENABLE|DISABLE];

Description#

The replication transaction grouping option accumulates multiple transactions into single groups to reduce the number of transactions to be replayed.

If the replication transaction grouping option has been specified and a replication gap occurs, the Ahead Analyzer which analyzes logs (before the sender does) and creates replication transaction groups, is created. The Ahead Analyzer analyzes as many XLogs as the value set for the REPLICATION_GROUPING_AHEAD_READ_NEXT_LOG_FILE property and starts with the file of the second largest number to the log file being analyzed by the sender. The REPLICATION_GROUPING_TRANSACTION_MAX_COUNT property determines the maximum number of transactions that can be accumulated into single replication transaction groups.

Replication transactions are accumulated into two types of groups: committed transactions and rolled back transactions. The sender converts groups of committed transactions into a single transaction, whereas the sender does not send the XLogs for rolled-back transactions.

For more detailed information about properties, please refer to the General Reference > Chapter 2. Altibase Properties.

Restriction#

  • The replication transaction grouping option can only be specified when replication is being performed in LAZY mode.

Meta Logging Option#

Syntax#

CREATE REPLICATION replication_name OPTIONS META_LOGGING ...;
CREATE REPLICATION replication_name FOR ANALYSIS OPTIONS META_LOGGING...;

Description#

The Meta Logging Option is a feature that saves the sender's metadata and restart SN information to a file.

This option is configured on the Active server's replication object. When performing offline replication on the replication server where the receiver thread is running, the saved file can be referenced to retrieve the Active server's metadata.

Explanation of the Syntax#

  • OPTIONS META_LOGGING

    When creating a replication object, the Meta Logging Option is configured. The sender's metadata and restart SN (Sequence Number) information are saved to the repl_meta_files directory in the log file path.

  • FOR ANALYSIS OPTIONS META_LOGGING

    When creating a replication object with the Log Analyzer role, the Meta Logging Option is also configured. The sender's metadata and restart SN information are saved to the ala_meta_files directory in the log file path.

    Note

    For detailed information about the offline option used with the Log Analyzer role replication object in Adapter for JDBC and Adapter for Oracle, please refer to the following document.

Offline Option#

Syntax#

CREATE REPLICATION replication_name OPTIONS OFFLINE 'log_dir' ...;

ALTER REPLICATION replication_name SET OFFLINE ENABLE WITH 'log_dir';
ALTER REPLICATION replication_name BUILD OFFLINE META [AT SN(sn)];
ALTER REPLICATION replication_name START WITH OFFLINE;
ALTER REPLICATION replication_name RESET OFFLINE META;
ALTER REPLICATION replication_name SET OFFLINE DISABLE;

Description#

The Offline Option is a feature that retrieves untransmitted logs from the Active server and applies the change transactions to the remote server. This function is set and performed on the replication server where the receiver thread is active, and it is also referred to as offline replication.

If a failure occurs on the Altibase server, preventing the replication of change transactions to the remote server, offline replication can be performed to retrieve the untransmitted logs and apply the changes.

To perform offline replication, the following conditions must be met:

  • The Active server must have a history of starting replication to the remote server.
  • The Meta Logging Option must be set on the replication object of the Active server.
  • The log files of the Active server and the sender's metadata files must be accessible.

Explanation of the Syntax#

  • OPTIONS OFFLINE

    When creating the replication object, set the offline option. In log_dir, enter the log file path of the Active server.

  • SET OFFLINE ENABLE WITH

    Set the offline option on the existing replication object. This can only be done when replication is stopped. In log_dir, enter the log file path of the Active server.

  • BUILD OFFLINE META

    Configure the metadata required for offline replication.

    When executing this command, it reads the sender's metadata and restart SN files from the repl_meta_files directory in the log_dir path to configure the metadata. After restarting the Altibase server, the metadata will be lost, so you will need to execute BUILD OFFLINE META again.

  • START WITH OFFLINE

    Perform offline replication. Offline replication is a one-time operation that retrieves the logs not sent by the Active server and applies them, then automatically terminates.

    Before performing offline replication, SQL Apply Mode must be enabled, and it should be disabled after the operation is complete.

    When offline replication is executed, both the sender and receiver threads are automatically stopped, so replication must be restarted afterward.

  • RESET OFFLINE META

    The RESET OFFLINE META command is used to reset the metadata of offline replication after executing BUILD OFFLINE META. It can be performed in the following situations:

    • When an error occurs during offline replication and the metadata needs to be reconfigured.
    • When offline replication is no longer needed and the metadata is no longer required.

  • SET OFFLINE DISABLE

    Disable the offline option on the replication object. This can only be done when replication is stopped.

Below is a diagram illustrating the process of offline replication.

[Figure 3-1] Data Accordance Between Both Servers

Restrictions#

  • The offline option can only be set on replication objects in LAZY mode.
  • The offline option cannot be set on replication objects that include compressed tables.
  • The offline option cannot be set simultaneously with the recovery option.
  • The server performing offline replication and the Active server must have the same OS, CPU type, and CPU bit architecture. Heterogeneous offline replication is not supported.
  • The server performing offline replication and the Active server must have the same three-part binary database version (Major, Minor, Patch).
  • The server performing offline replication and the Active server must have the same log file size (LOG_FILE_SIZE).

Cautions#

If the user arbitrarily changes the log files or sender metadata files, issues such as abnormal termination of the Altibase server may occur.

Examples of incorrect changes:

  • Renaming files
  • Copying log files to another system
  • Deleting files

Offline Replication Procedure Example#

This procedure describes how to perform offline replication on the Standby server when the Active server experiences a failure.

  • Active Server: The replication server where change transactions occur.
  • Standby Server: The replication server that performs offline replication.

  • Log file path of the Active server: /active_server/altibase_home/logs

  • Offline Replication on a Replication Object with Offline Option Set

    In this example, the Active server has Meta Logging Option set, and the Standby server has the Offline option enabled.

    Active Standby
    1. Schema Creation CREATE TABLE t1 (i1 INTEGER PRIMARY KEY, i2 CHAR(20)); CREATE TABLE t1 (i1 INTEGER PRIMARY KEY, i2 CHAR(20));
    2. Replication Creation CREATE REPLICATION rep1 OPTIONS META_LOGGING WITH 'standby_ip', standby_port FROM SYS.t1 to SYS.t1; CREATE REPLICATION rep1 OPTION OFFLINE '/ active_server/altibase_home/logs' WITH 'active_ip', active_port FROM SYS.t1 to SYS.t1;
    3. Start Replication on Active Server ATER REPLICATION START rep1;
    4. Active Server Failure Failure Occurrence
    5. Enable SQL Apply Mode ALTER SYSTEM SET REPLICATION_SQL_APPLY_ENABLE = 1;
    6. Configure Offline Metadata ALTER REPLICATION rep1 BUILD OFFLINE META;
    7. Start Offline Replication ALTER REPLICATION rep1 START WITH OFFLINE;
    8. Disable SQL Apply Mode ALTER SYSTEM SET REPLICATION_SQL_APPLY_ENABLE = 0;

  • Offline Replication on a Replication Object Without Offline Option Set

    This example uses an Active server with the Meta Logging Option enabled and a Standby server without the offline option enabled.

    Active Standby
    1. Schema Creation CREATE TABLE t1 (i1 INTEGER PRIMARY KEY, i2 CHAR(20)); CREATE TABLE t1 (i1 INTEGER PRIMARY KEY, i2 CHAR(20));
    2. Replication Creation CREATE REPLICATION rep1 OPTIONS META_LOGGING WITH 'standby_ip', standby_port FROM SYS.t1 to SYS.t1; CREATE REPLICATION rep1 WITH 'active_ip', active_port FROM SYS.t1 to SYS.t1;
    3. Start Replication on Active Server ATER REPLICATION START rep1;
    4. Active Server Failure Failure Occurrence
    5. Enable SQL Apply Mode ALTER SYSTEM SET REPLICATION_SQL_APPLY_ENABLE = 1;
    6. Setting the Offline Option ALTER REPLICATION rep1 SET OFFLINE ENABLE WITH '/active_server/altibase_home/logs';
    7. Configure Offline Metadata ALTER REPLICATION rep1 BUILD OFFLINE META;
    8. Start Offline Replication ALTER REPLICATION rep1 START WITH OFFLINE;
    9. Disabling the Offline Option ALTER REPLICATION rep1 SET OFFLINE DISABLE;
    10. Disable SQL Apply Mode ALTER SYSTEM SET REPLICATION_SQL_APPLY_ENABLE = 0;

Note

For examples of using the offline option in Adapter for JDBC and Adapter for Oracle, refer to the following document.


Replication in a Multiple IP Network Environment#

Replication is supported in a multiple IP network environment. In other words, it is possible to perform replication between two hosts having two or more physical network connections.

Syntax#

CREATE REPLICATION replication_name {AS MASTER|AS SLAVE} 
WITH 'remotehostip', remoteportno 'remotehostip', remoteportno …
FROM user.localtableA TO user.remotetableA,
FROM user.localtableB TO user.remotetableB, …, 
FROM user.localtableC TO user.remotetableC;

ALTER REPLICATION replication_name
ADD HOST 'remote_host_ip', remote_port_no [USING conn_type [ib_latency]];

ALTER REPLICATION replication_name
DROP HOST 'remotehostip', remoteportno;

ALTER REPLICATION replication_name
SET HOST 'remotehostip', remoteportno;

Description#

In order to ensure high system performance and quickly overcome faults, systems can have multiple physical IP addresses assigned to them when a replication object is created. In such an environment, the Sender thread uses the first IP address to access peers and perform replication tasks when replication starts, but if a problem occurs while this task is underway, the Sender thread stops using this connection, connects using another IP address, and tries again.

  • CREATE REPLICATION

    The name of the replication object is first specified, and then in the WITH clause, the IP addresses and reception ports of multiple remote servers are specified, with commas between each IP address and port, and with spaces between address/port pairs defining each host. The owner and name of the target table(s) on the local server are specified in the FROM clause and the owner and name of the corresponding target table(s) on the remote server are specified in the TO clause, with commas between multiple table specifications.

  • ALTER REPLICATION (ADD HOST)

    This adds a host. A host can be added to a replication object after the replication object has been stopped. When ADD HOST is executed, before the Sender thread actually adds the host, the connection must be re-established using the IP address that was previously being used

  • conn_type

    This is the communication method with a remote server (TCP/InfiniBand). The default is TCP.

  • ib_latency

    This is the RDMA_LATENCY option value for rsocket. It can only be entered when conn_type is IB.

  • ALTER REPLICATION (DROP HOST)

    This drops a host. A host can be dropped from a replication object after the replication object has been stopped. When DROP HOST is executed, the Sender thread attempts to reconnect using the very first IP address.

  • ALTER REPLICATION (SET HOST)

    This means setting a particular host as the current host. The current host can be specified after the replication object has been stopped. After execution, the Sender thread attempts to connect using the currently designated IP address.

Examples#

In the following double-IP network environment, a replication object having a table called employees and one called departments as its target objects is created, and then replication in Active-Standby mode is executed on the local server (IP: 192.168.1.51, PORT NO: 30570) and the remote server ('IP: 192.168.1.154, PORT NO: 30570', 'IP: 192.168.2.154, PORT NO: 30570').

  • On the remote (standby) server:

    iSQL> CREATE REPLICATION rep1   
WITH '192.168.1.51', 30570  
FROM sys.employees TO sys.employees,
FROM sys.departments TO sys.departments;
Create success.

  • On the local (active) server:

    iSQL> CREATE REPLICATION rep1
       WITH '192.168.1.154',30570 '192.168.2.154',30570
FROM sys.employees TO sys.employees,
FROM sys.departments TO sys.departments;
Create success.                               

iSQL> SELECT * FROM system_.sys_replications_;

  • The meta table enables the user to view the number of registered hosts, the number of replication target tables, and other related information.

    iSQL> SELECT * FROM SYSTEM_.SYS_REPLICATIONS_;
REPLICATION_NAME         : REP1  
LAST_USED_HOST_NO        : 2 
HOST_COUNT               : 2 
IS_STARTED               : 0 
XSN                      : -1 
ITEM_COUNT               : 2 
CONFLICT_RESOLUTION      : 0 
REPL_MODE                : 0 
ROLE                     : 0 
OPTIONS                  : 0 
INVALID_RECOVERY         : 0 
REMOTE_FAULT_DETECT_TIME :  
GIVE_UP_TIME             :  
GIVE_UP_XSN              :  
PARALLEL_APPLIER_COUNT   : 0 
APPLIER_INIT_BUFFER_SIZE : 0 
REMOTE_XSN               : -1 
PEER_REPLICATION_NAME    :   
REMOTE_LAST_DDL_XSN      : -1 

1 row selected.

  • Information about remote server by using meta table.

    iSQL> SELECT * FROM SYSTEM_.SYS_REPL_HOSTS_;
HOST_NO          : 2 
REPLICATION_NAME : REP1  
HOST_IP          : 192.168.1.154  
PORT_NO          : 30570 
CONN_TYPE        : TCP  
IB_LATENCY       : N/A  

HOST_NO          : 3 
REPLICATION_NAME : REP1  
HOST_IP          : 192.168.2.154  
PORT_NO          : 30570 
CONN_TYPE        : TCP  
IB_LATENCY       : N/A  

2 rows selected.

  • Replication starts.

    iSQL> ALTER REPLICATION rep1 START;
Alter success.

  • The status of replication is checked after replication starts. The Sender thread connects to the peer using the first IP and PORT.

    iSQL> SELECT REP_NAME
     , STATUS
     , NET_ERROR_FLAG
     , SENDER_IP
     , SENDER_PORT
     , PEER_IP
     , PEER_PORT
  FROM V$REPSENDER;
REP_NAME              STATUS               NET_ERROR_FLAG       SENDER_IP             SENDER_PORT PEER_IP               PEER_PORT   
--------------------------------------------------------------------------------------------------------------------------------------------
REP1                  1                    0                    192.168.1.51          13718       192.168.1.154         30570   
1 row selected.

  • Network line disconnection

  • The status of replication is checked after replication starts. The Sender thread connects to the peer using the first IP and PORT.

    iSQL> SELECT REP_NAME
     , STATUS
     , NET_ERROR_FLAG
     , SENDER_IP
     , SENDER_PORT
     , PEER_IP
     , PEER_PORT
  FROM V$REPSENDER;
REP_NAME              STATUS               NET_ERROR_FLAG       SENDER_IP             SENDER_PORT PEER_IP               PEER_PORT   
--------------------------------------------------------------------------------------------------------------------------------------------
REP1                  1                    0                    192.168.1.51          40009       192.168.1.154         30570        
1 row selected.

  • Replication is stopped.

    iSQL> ALTER REPLICATION rep1 STOP;
Alter success.

  • Replication starts.

    iSQL> ALTER REPLICATION rep1 START;
Alter success.

  • When replication is started again after having been stopped, it can be verified to have been reconnected to the same IP and PORT to which it was connected before being stopped.

    iSQL> SELECT REP_NAME
     , STATUS
     , NET_ERROR_FLAG
     , SENDER_IP
     , SENDER_PORT
     , PEER_IP
     , PEER_PORT
  FROM V$REPSENDER;
REP_NAME              STATUS               NET_ERROR_FLAG       SENDER_IP             SENDER_PORT PEER_IP               PEER_PORT   
--------------------------------------------------------------------------------------------------------------------------------------------
REP1                  1                    0                    192.168.1.51          64351       192.168.1.154         30570    
1 row selected.

  • Add host: Can be executed after replication.

    iSQL> ALTER REPLICATION rep1 ADD HOST '192.168.3.154',30570;
Alter success.

  • remove host: Can be executed after replication.

    iSQL> ALTER REPLICATION rep1 DROP HOST '192.168.3.154',30570;
Alter success.

  • Designate the host: Can be executed after replication.

    iSQL> ALTER REPLICATION rep1 SET HOST '192.168.1.154',30570;
Alter success.

  • Replication is restarted after setting the new host. The replication operation first attempts to connect using the currently designated IP and PORT.

    iSQL> ALTER REPLICATION rep1 START;
Alter success.

  • Connection to the peer using the newly designated IP 192.168.1.154 and PORT number 30570 can be confirmed.

    iSQL> SELECT REP_NAME
     , STATUS
     , NET_ERROR_FLAG
     , SENDER_IP
     , SENDER_PORT
     , PEER_IP
     , PEER_PORT
  FROM V$REPSENDER;
REP_NAME              STATUS               NET_ERROR_FLAG       SENDER_IP             SENDER_PORT PEER_IP               PEER_PORT   
--------------------------------------------------------------------------------------------------------------------------------------------
REP1                  1                    0                    192.168.1.51          11477       192.168.1.154         30570        
1 row selected.


To use replication, the Altibase properties file should be modified to suit the purposes of the user. The following properties are described in the General Reference > Chapter 2. Altibase Properties.

  • REPLICATION_ACK_XLOG_COUNT
  • REPLICATION_ALLOW_DUPLICATE_HOSTS
  • REPLICATION_BEFORE_IMAGE_LOG_ENABLE
  • REPLICATION_COMMIT_WRITE_WAIT_MODE
  • REPLICATION_CONNECT_RECEIVE_TIMEOUT
  • REPLICATION_CONNECT_TIMEOUT
  • REPLICATION_DDL_ENABLE
  • REPLICATION_DDL_ENABLE_LEVEL
  • REPLICATION_DDL_SYNC
  • REPLICATION_DDL_SYNC_TIMEOUT
  • REPLICATION_EAGER_PARALLEL_FACTOR
  • REPLICATION_EAGER_RECEIVER_MAX_ERROR_COUNT
  • REPLICATION_FAILBACK_INCREMENTAL_SYNC
  • REPLICATION_GAP_UNIT
  • REPLICATION_GAPLESS_ALLOW_TIME
  • REPLICATION_GAPLESS_MAX_WAIT_TIME
  • REPLICATION_GROUPING_TRANSACTION_MAX_COUNT
  • REPLICATION_GROUPING_AHEAD_READ_NEXT_LOG_FILE
  • REPLICATION_HBT_DETECT_HIGHWATER_MARK
  • REPLICATION_HBT_DETECT_TIME
  • REPLICATION_IB_LATENCY
  • REPLICATION_IB_PORT_NO
  • REPLICATION_INSERT_REPLACE
  • REPLICATION_KEEP_ALIVE_CNT
  • REPLICATION_LOCK_TIMEOUT
  • REPLICATION_LOG_BUFFER_SIZE
  • REPLICATION_MAX_COUNT
  • REPLICATION_MAX_LISTEN
  • REPLICATION_MAX_LOGFILE
  • REPLICATION_POOL_ELEMENT_COUNT
  • REPLICATION_POOL_ELEMENT_SIZE
  • REPLICATION_PORT_NO
  • REPLICATION_PREFETCH_LOGFILE_COUNT
  • REPLICATION_RECEIVE_TIMEOUT
  • REPLICATION_RECEIVER_APPLIER_ASSIGN_MODE
  • REPLICATION_RECEIVER_APPLIER_QUEUE_SIZE
  • REPLICATION_RECOVERY_MAX_LOGFILE
  • REPLICATION_RECOVERY_MAX_TIME
  • REPLICATION_SENDER_AUTO_START
  • REPLICATION_SENDER_COMPRESS_XLOG
  • REPLICATION_SENDER_ENCRYPT_XLOG
  • REPLICATION_SENDER_SEND_TIMEOUT
  • REPLICATION_SENDER_SLEEP_TIME
  • REPLICATION_SENDER_SLEEP_TIMEOUT
  • REPLICATION_SENDER_START_AFTER_GIVING_UP
  • REPLICATION_SERVER_FAILBACK_MAX_TIME
  • REPLICATION_SQL_APPLY_ENABLE
  • REPLICATION_SYNC_APPLY_METHOD
  • REPLICATION_SYNC_LOCK_TIMEOUT
  • REPLICATION_SYNC_LOG
  • REPLICATION_SYNC_TUPLE_COUNT
  • REPLICATION_TIMESTAMP_RESOLUTION
  • REPLICATION_TRANSACTION_POOL_SIZE
  • REPLICATION_UPDATE_REPLACE