CYCLONE

CYCLONE is a replication tool which uses the Change Data Capture (CDC) method.

Overview

Database records the data changes which occur during the operation in the redo log file for the recovery. CDC performs the replication by analyzing the information of the recorded redo log file.

CYCLONE is driven being divided into master and slave. Master recognizes the changes of the redo log file in the original database and analyzes it, then transfers it to the slave. Slave analyzes the received data and performs the replication by using ODBC.

Operational Features

• It is divided into master and slave and is operated as master/slave in group unit.

• Master and slave of CYCLONE uses TCP/ IP communication.

• It can be executed/ terminated in group unit.

• The replication is executed in table unit, and a group may include one or more tables.

• A single table may be operated being included in several groups.

• The original database should have the redo log files, so DATA_STORE_MODE should be operated in TDS.

• The original database should have SUPPLEMENTAL LOGGING.

  • SUPPLEMENTAL LOGGING adds an additional information to the redo log files for the replication of CYCLONE.

• The original database should be operated in ARCHIVE LOG mode.

  • GOLDILOCKS recursively reuses the redo log files. When the redo log files are reused before the replication is completed, the replication becomes aborted and the existing replication from the current point is canceled then restarted. Therefore, the redo log files should be operated in ARCHIVE LOG mode to be archived.

• It does not affect GOLDILOCKS even when it fails to replicate because it is operated as an independent process.

Operational Restrictions

• The table participated in the replication should have a PRIMARY KEY.

• Only the committed transaction is allowed to be replicated. Therefore, the content is unknown to the slave before committing the transaction.

• The primary key update is not supported.

  • When the primary key is updated, the table is given up and it is not replicated any more.

• The table participated in the replication can not use the column which has Generated Always As Identity property.

• When Data Definition Language (DDL) is performed on the table in which the replication is being operated, it could be given up.

  • For more information, refer to The occurrence of give up and whether to allow DDL statement according to DDL category in table1.

  • If it does not comply with the processing procedure of table DDL of CYCLONE, then even the allowable DDL is given up.

  • It does not affect the replication of other tables.

  • It is same in case of the truncated table.

• The given-up table can be reset only when it was given up with --reset TABLE_NAME

• The columns configuring the table participating in the replication should have the same structure.(data type, order)

• The database performing the replication should have the same character encoding.

• The table stored in the recycle bin is not a replication target.

DDL Processing during Replication

It should comply with the following procedure when the allowable DDL is performed for the table in which the replication is being performed.

1. Terminate all operating CYCLONE in master and slave before executing DDL in master.

   • The process in progress does not need to be terminated.

   • e.g. cyclone --master --stop / cyclone --slave --stop

2. Execute DDL in both master and slave. (It should be the allowable DDL.)

   • For more information, refer to The occurrence of give up and whether to allow DDL statement according to DDL category

3. Restart CYCLONE of master and slave.

   • cyclone --master --start ... / cyclone --slave --start ...

   • It does not require --reset option.

   • It performs the recovery from the termination of the first stage and process DDL when restarting. (Refer to trace log)

Datatype Compatibility When Interworking with Other DBMS

• When target database of CYCLONE slave interworks with other DBMS instead of GOLDILOCKS, the column datatype of GOLDILOCKS table, source database, should be compatible with that of other DBMS table so that it can prevent the data loss or an error.

• Currently, it can interwork with Oracle, MySQL and DB2.

• When interworking with other DBMS, then SYNC and allowable DDL features are not available.

Oracle

• The following four datatypes can not be replicated as described in a table above.

  • BOOLEAN

  • TIME

  • TIMESTAMP WITH TIMEZONE

  • INTERVAL

MySQL

• The following four datatypes can not be replicated as described in a table above.

  • FLOAT

  • NUMBER

  • TIMESTAMP WITH TIMEZONE

  • INTERVAL

DB2

Others

The replication moments are as follows.

• At the first run, the replication starts after master and slave are performed and the initialization is terminated.

• Even if it is restarted after terminated during the replication, the replication is continuously performed from the termination point. (Recovery feature)

• It should be restarted by using --reset option when giving up the existing replication and restarting from the current point.

Requirements

Original GOLDILOCKS: It is required to perform GOLDILOCKS preparations, user registration and privilege setting all.
Remote GOLDILOCKS: It is required to perform user registration and privilege setting only.

GOLDILOCKS Requirements

The followings should be set in GOLDILOCKS before starting the replication using CYCLONE.

SUPPLEMENTAL LOGGING

SUPPLEMENTAL LOGGING stores additional information in the redo log file for the replication of CYCLONE. The database restart is required to change the settings of the database in operation, but the database restart is not required to set SUPPLEMENTAL LOGGING for a particular table.

Setting SUPPLEMENTAL LOGGING in Database

• If the GOLDILOCKS property is set as SUPPLEMENTAL LOGGING, SUPPLEMENTAL LOGGING is recorded for every table.

• GOLDILOCKS restart is required.

• The appropriate information is added or updated in the property file.

  • Property file: goldilocks.properties.conf

  • Property setting: SUPPLEMENTAL LOG_DATA_PRIMARY_KEY = YES

Setting SUPPLEMENTAL LOGGING in Specific Table Participating in Replication

<add table supplemental log statement> ::=
    ALTER TABLE table_name 
        ADD SUPPLEMENTAL LOG DATA ( PRIMARY KEY ) COLUMNS
    ;

ARCHIVE LOG

GOLDILOCKS reuses the redo log files recursively. When GOLDILOCKS reuses the redo log file being processed by CYCLONE, then CYCLONE does not proceed and is terminated. GOLDILOCKS should be operated in ARCHIVE LOG mode to ensure the continuous replication operation.

Changing Database in Operation to ARCHIVE LOG Mode

• Restart GOLDILOCKS.

• After database is shutdown, connect with sysdba and change it to ARCHIVE LOG mode in MOUNT phase.

gSQL> \startup mount

Startup success

gSQL> alter database archivelog;

Database altered.

Setting ARCHIVE LOG Mode When Creating Database

• Update the property file before creating the database.

  • Property file: goldilocks.properties.conf

  • Property setting: ARCHIVELOG_MODE = 1

DATA_STORE_MODE

CYCLONE performs the replication by reading the redo log files of GOLDILOCKS. Therefore, GOLDILOCKS should be operated in Transactional Data Store (TDS) mode.

Changing DATA_STORE_MODE

• Restart the database.

• Add or update the corresponding information in the property file.

  • Property file: goldilocks.properties.conf

  • Property setting: DATA_STORE_MODE = 2

Registering User and Setting Privileges

CYCLONE retrieves and manipulates the required information during the operation. The user operating CYCLONE and the proper privileges for the user are required.

Creating Database User

A specific user should be added to operate CYCLONE, and the corresponding user should be added for all GOLDILOCKS of which CYCLONE is operated in master, slave mode.

<user definition> ::=
    CREATE USER user_identifier IDENTIFIED BY password
    [ DEFAULT TABLESPACE tablespace_name ]
    [ TEMPORARY TABLESPACE tablespace_name ]
    [ INDEX TABLESPACE {tablespace_name|NULL} ]
    [ <schema clause> ]
    ;

<schema clause> ::=
      WITH SCHEMA [schema_name]
    | WITHOUT SCHEMA

The following is an example of creating the user cdc_user with the password cdc_password.

gSQL> CREATE USER cdc_user IDENTIFIED BY cdc_password;

Database Privileges

Granting User Access Privilege

The following is an example for granting the access privilege to cdc_user. It should be set on all GOLDILOCKS which is operated in both master/ slave mode.

gSQL> GRANT CREATE SESSION ON DATABASE TO cdc_user;

Granting Privilege for Altering Table

The following is an example of granting the table updating privilege to cdc_user. It is set on GOLDILOCKS which is operated in slave mode.

gSQL> GRANT INSERT ANY TABLE, DELETE ANY TABLE, UPDATE ANY TABLE ON
DATABASE TO cdc_user;

Tablespace Privileges

The privileges on the data tablespace and temporary tablespace should be set. The following is an example of granting the privilege for using the default tablespace to cdc_user. It is set on GOLDILOCKS which is operated in slave mode.

gSQL> GRANT CREATE OBJECT ON TABLESPACE mem_data_tbs TO cdc_user;
gSQL> GRANT CREATE OBJECT ON TABLESPACE mem_temp_tbs TO cdc_user;

Schema Privileges

The schema privileges for creating and managing meta managed in CYCLONE should be set. The following is an example granting the schema privilege to cdc_user. It is set on GOLDILOCKS which is operated in slave mode.

gSQL> GRANT CREATE TABLE, CREATE INDEX, CREATE SEQUENCE, CREATE VIEW,
ADD CONSTRAINT ON SCHEMA cdc_user TO cdc_user;

Configuration

Configuration File

When performing CYCLONE, the information and options required for operating are set by using the configuration file.

• When a specific configuration file is not set by using the --conf option, a specific file in the $GOLDILOCKS_DATA/conf directory is read. cyclone.master.conf file is read when it is operated in master mode and cyclone.slave.conf file is read when it is operated in slave mode.

Configuration Option

COMM_CHUNK_COUNT

• It sets the number of buffers(chunk) used in data communication between master and slave of CYCLONE.

• It allocates the resource with 16M * N (the set value).

• The default value is 32, and the actual size is 16M * 32 = 512 M.

• The minimum value is 10.

• It can be set in master and slave.

  • If too small value is set so the buffer is not enough, the performance becomes poor.

• Settings applied to all groups

COMM_CHUNK_COUNT = 10

• Settings applied to a specific group

GROUP_NAME = testGROUP
{
    COMM_CHUNK_COUNT=20
    ....
    ....
}

DSN

• It sets the data source name which is required when connecting to GOLDILOCKS.

• It can be set in master and slave.

• The default value is GOLDILOCKS.

• Settings applied to all groups

DSN=GOLDILOCKS

• Settings applied to a specific group

GROUP_NAME = testGROUP
{
    DSN=GOLDILOCKS
    ....
    ....
}

GROUP_NAME

• It is necessary for distinguishing the CYCLONE operation within the equipment and it is a unit of generating the operation process.

• It is the delimiter of when starting or terminating CYCLONE in group unit.

• After it is set, it should not be changed. If changed, it is regarded as a new group.

• It can be set in master and slave.

  • The connection between master and slave is separated not by GROUP_NAME but by PORT.

  • GROUP_NAME should be unique in the same equipment.

• The braces { } should be used.

GROUP_NAME = testGROUP
{
    ....
    ....
}

HOST_IP

• It sets the IP address of GOLDILOCKS to be connected by CYCLONE.

• It can be set in master and slave.

  • It is valid only when PROTOCOL is set to TCP.

• Settings applied to all groups

HOST_IP = 127.0.0.1

• Settings applied to a specific group

GROUP_NAME = testGROUP
{
    HOST_IP = 127.0.0.1
    ....
    ....
}

HOST_EXTERNAL_IP

• It can be set in slave.

• CYCLONE master access GOLDILOCKS of the slave's side when it is operated in sync, and it is used if slave GOLDILOCKS IP to which the cyclone master is to be connected is different from HOST_IP.

  • It is used when IP on lan and IP on wan is different because master and slave are on wan section.

  • CYCLONE slave transfers this value to CYCLONE master, and it is used in CYCLONE master.

• Settings applied to all groups

HOST_EXTERNAL_IP = 192.168.0.120

• Settings applied to a specific group

GROUP_NAME = testGROUP
{
    HOST_EXTERNAL_IP = 192.168.0.120
    ....
    ....
}

HOST_PORT

• It sets the port of GOLDILOCKS to be connected by CYCLONE.

• It should be set together with HOST_IP.

• It can be set in master and slave.

• Settings applied to all groups

HOST_PORT = 22531

• Settings applied to a specific group

GROUP_NAME = testGROUP
{
    HOST_PORT = 22531
    ....
    ....
}

PORT

• It sets the PORT used in the communication between master and slave.

• The duplicated PORT should not be set between the groups, and the unique value should be used for each GROUP.

• It is mandatory be set and it can be set only within GROUP_NAME.

• It can be set only within a group.

GROUP_NAME = testGROUP
{
    PORT = 21102
    ....
    ....
}

USER_ID

• It sets the user ID required to access GOLDILOCKS.

• It can be set in master and slave.

• Settings applied to all groups

USER_ID = testID

• Settings applied to a specific group

GROUP_NAME = testGROUP
{
    USER_ID = testID
    ....
    ....
}

USER_PW

• It sets the user password required to access GOLDILOCKS.

• It can be set in master and slave.

• Settings applied to all groups

USER_PW = testPW

• Settings applied to a specific group

GROUP_NAME = testGROUP
{
    USER_PW = testPW
    ....
    ....
}

USER_ENCRYPT_PW

• It sets the user password which is required for the access to GOLDILOCKS by encrypting.

• It is used instead of USER_PW.

• Encrypted user password is created by [cyclone --encrypt user password --key the key to be encrypted].

• If this value is used, then --key option should be used when executing cyclone. (In this case, the key value as same as that created with --encrypt should be used.

• Settings applied to all groups

USER_ENCRYPT_PW = 't33KImiqvhqNyfN+uZmFrw=='

• Settings applied to a specific group

GROUP_NAME = testGROUP
{
    USER_ENCRYPT_PW = 't33KImiqvhqNyfN+uZmFrw=='
    ....
    ....
}

CAPTURE_TABLE

• It sets the table to be replicated.

  • It is set in schema_name.table_name format.

• It can set only in master.

• It can be set only within a group.

• When specifying several tables, the parentheses ( ) should be used.

• The table stored in the recycle bin can not be set to be replicated.

GROUP_NAME = testGROUP
{
    CAPTURE_TABLE = 
    (
        testSchema1.testTable1,
        testSchema1.testTable2,
        testSchema2.testTable1
    )
}

LOG_PATH

• It is used when interworking with LOG MIRROR.

• It sets the path of the redo log files stored by LOGMIRROR.

  • The absolute path should be used.

  • The path should be specified by using single quote (').

• Settings applied to all groups

LOG_PATH = '/data/wal/'

• Settings applied to a specific group

GROUP_NAME = testGROUP
{
    LOG_PATH = '/data/wal/'
    ....
    ....
}

PROTOCOL

• It sets the type to connect to GOLDILOCKS in operation.

• It can be set to DA or TCP.

• If PROTOCOL is set to DA, neither HOST_IP nor is HOST_PORT used when accessing to GOLDILOCKS.

  • However, in a slave, HOST_IP and HOST_PORT can be used for SYNC even when PROTOCOL is DA.

• The default value is DA.

• Settings applied to all groups

PROTOCOL = DA

• Settings applied to a specific group

GROUP_NAME = testGROUP
{
    PROTOCOL = DA
    ....
    ....
}

READ_LOG_BLOCK_COUNT

• It sets the number of the log blocks to be read at a time when capturing redo log file.

• It can be set only in master.

• The size of a log block is 512 bytes.

• The default value is 40960, and the actual read size is 20 MBytes.

  • The minimum value is 100.

• Settings applied to all groups

READ_LOG_BLOCK_COUNT = 1024

• Settings applied to a specific group

GROUP_NAME = testGROUP
{
    READ_LOG_BLOCK_COUNT = 1024
    ....
    ....
}

TRANS_SORT_AREA_SIZE

• It sets the memory space required for capturing the redo log files.

• It can be set only in master.

• The unit is MB (megabytes).

• The default value is 500 MB.

  • The minimum value is 10 MB.

• If the value is set too small, the performance becomes poor.

• Settings applied to all groups

TRANS_SORT_AREA_SIZE = 300

• Settings applied to a specific group

GROUP_NAME = testGROUP
{
    TRANS_SORT_AREA_SIZE = 300
    ....
    ....
}

TRANS_FILE_PATH

• If a space bigger than TRANS_SORT_AREA_SIZE is required, the temporary file is created. It sets the path in which the temporary file is to be stored.

  • The absolute path should be used.

  • The path should be specified by using single quote (').

• It can be set only in master.

• Settings applied to all groups

TRANS_FILE_PATH = '/data/TmpTrans'

• Settings applied to a specific group

GROUP_NAME = testGROUP
{
    TRANS_FILE_PATH = '/data/TmpTrans'
    ....
    ....
}

SYNCHER_COUNT

• It is used for synchronization.

  • It sets the number of SYNCHERs participating in synchronization.

  • It is input in the number unit.

• It is set only in master.

• Settings applied to all groups

SYNCHER_COUNT = 8

• Settings applied to a specific group

GROUP_NAME = testGROUP
{
    SYNCHER_COUNT = 8
    ....
    ....
}

SYNC_ARRAY_SIZE

• It is used for syncronization.

  • It sets the unit of record to be inserted at a time when performing synchronization.

  • It is input in the number unit.

• It can be set only in master.

• Settings applied to all groups

SYNC_ARRAY_SIZE = 1000

• Settings applied to a specific group

GROUP_NAME = testGROUP
{
    SYNC_ARRAY_SIZE = 1000
    ....
    ....
}

GIVEUP_INTERVAL

• It gives up the replication and terminates CYCLONE if the INTERVAL between GOLDILOCKS and CYCLONE is bigger than the set value.

  • It is input in the number of REDO LOG BLOCK unit.

• It can be set only in master.

• Settings applied to all groups

GIVEUP_INTERVAL = 10000

• Settings applied to a specific group

GROUP_NAME = testGROUP
{
    GIVEUP_INTERVAL = 10000
    ....
    ....
}

APPLIER_COUNT

• It sets the number of APPLIER executing replication.

  • It indicates a parallel factor.

• The default value is 6, and 6 sessions are created.

  • The maximum value is not limited, but too high value can cause the contention between APPLIERs.

  • The set value significantly affects on performance.

  • The session is created according to the set value and the replication is simultaneously performed.

• It can be set only in slave.

• Settings applied to all groups

APPLIER_COUNT = 16

• Settings applied to a specific group

GROUP_NAME = testGROUP
{
    APPLIER_COUNT = 16
    ....
    ....
}

APPLY_COMMIT_SIZE

• It sets the number of transaction executing COMMIT when the replication is performed.

  • It executes COMMIT after performing the transaction once when replicating transactions committed in the original database to the remote database.

  • The set value indicates the maximum value.

• The set value affects on performance.

• It can be set only in slave.

• Settings applied to all groups

APPLY_COMMIT_SIZE = 1000

• Settings applied to a specific group

GROUP_NAME = testGROUP
{
    APPLY_COMMIT_SIZE = 1000
    ....
    ....
}

APPLY_TABLE

• It sets the table to which the replicated table is to be applied.

  • It is set in replicating table name TO table name to which the replicated table is applied format.

  • The table names are not necessary to be same.

• It can be set only in slave.

• It can be set only within a group.

• The parentheses ( ) should be used when specifying several tables.

GROUP_NAME = testGROUP
{
    APPLY_TABLE =
    (
        testSchema1.testTable1 TO testSchema1.testTable1,
        testSchema1.testTable2 TO testSchema2.testTable3,
        testSchema2.testTable3 TO testSchema2.testTable4
    )
}

MASTER_IP

• It sets the IP address of the device which CYCLONE master operates.

• It can be set only in slave.

• Settings applied to all groups

MASTER_IP = 192.168.0.100

• Settings applied to a specific group

GROUP_NAME = testGROUP
{
    MASTER_IP = 192.168.0.100
    ....
    ....
}

PROPAGATE_MODE

• If CYCLONE is circularly configured, it sets whether or not another CYCLONE applies the transactions of which a CYCLONE applied.

• It can be set only in slave.

  • The default value is '0' and it does not PROPAGATE.

  • If it is set to 1, it is PROPAGATEd. If it is set to 0, it is not PROPAGATEd.

• Settings applied to all groups

PROPAGATE_MODE = 1

• Settings applied to a specific group

GROUP_NAME = testGROUP
{
    PROPAGATE_MODE = 1
    ....
    ....
}

SUPPLEMENTAL_LOG_FORCE_MODE

• To perform replication in CYCLONE, supplemental logging must be enabled on the target table.

• When this option is set to 1 (Enable), supplemental logging is forcibly enabled if it is disabled on the target table.

• This option can be set only on the master.

  • The default value is 0 (Disable).

  • Set this option to 1 (Enable) to enable it, or to 0 to disable it.

  • When this option is set to 1 (Enable), the user must have the privilege to enable supplemental logging.

• Settings applied to all groups

SUPPLEMENTAL_LOG_FORCE_MODE = 1

• Settings applied to a specific group

GROUP_NAME = testGROUP
{
    SUPPLEMENTAL_LOG_FORCE_MODE = 1
    ....
    ....
}

SEPARATE_CONFLICT_LOG

• It sets whether to separate the conflict log from the trace log when storing it.

• It can be set only in slave.

  • Set it to 1 to separate it, or set it to 0 not to separate it.

    • The default value is 0.

    • If it is set to 1, then it is stored in cyclone_conflict_GROUPNAME.log file.

• It can not be set to apply to a specific group.

• Settings applied to all groups

SEPARATE_CONFLICT_LOG = 1

UPDATE_APPLY_MODE

• It is used for the update operation in slave.

  • 0: If only the primary keys are same, the update operation is performed. (The default value)

  • 1: If the primary key is same with the value of before updating, the update operation is performed.

  • 2: If only the primary keys are same, the update operation is performed. The values before and after updating are compared and if they are different, then it leaves the logs.

• If it is set to 0, the previous value is not checked and the update is performed.

• If it is set to 1 and the previous value is different, the update is failed and leaves the conflict log.

• If it is set to 2 and the previous value is different, the update is succeeded and leaves the conflict log.

• Settings applied to all groups

UPDATE_APPLY_MODE = 1

• Settings applied to a specific group

GROUP_NAME = testGROUP
{
    UPDATE_APPLY_MODE = 1
    ....
    ....
}

TCP_NODELAY

• It is used only in master.

• It sets TCP_NODELAY option for the CDC transfer socket. (This option does not affect the sync. It is fixed to TCP_NODELAY on.)

  • 0: It offs the socket TCP_NODELAY option.

  • 1: It ons the socket TCP_NODELAY option. (Default)

• Settings applied to all groups

TCP_NODELAY = 1

• Settings applied to a specific group

GROUP_NAME = testGROUP
{
    TCP_NODELAY = 1
    ....
    ....
}

HEARTBEAT_TIMEOUT

• It can be set in master and slave.

• It sets the maximum time (second) maintaining connection if the connection is not smooth due to network disconnection or system error after replication connection between master and slave.

• The default value is 30 (seconds).

  • The minimum value is 10 (seconds).

• Settings applied to all groups

HEARTBEAT_TIMEOUT = 40

• Settings applied to a specific group

GROUP_NAME = testGROUP
{
    HEARTBEAT_TIMEOUT = 40
    ....
    ....
}

SKIP_COMMENT

• It can be set in master.

• It is available when a specific transaction is not replicated.

• If the same value is input as a comment when executing transaction commit after setting the corresponding value, then it skips that transaction instead of replicating it.

• Settings applied to all groups

SKIP_COMMENT = 'DO_SKIP'

• Settings applied to a specific group

GROUP_NAME = testGROUP
{
    SKIP_COMMENT = 'DO_SKIP'
    ....
    ....
}

LOG_CAPTURE_INTERVAL_1

• It can be set in master.

• It sets the execution cycle of capture operated in master. The execution cycle is set in millisecond.

• It is used to quickly detect the changes of the redo log file.

• If the value of redo log file is not changed after executing 10 times with that value, then it is converted to the value of LOG_CAPTURE_INTERVAL_2 and performs capture.

• The default value is 200 (0.2 seconds), and the unit is millisecond.

• Settings applied to all groups

LOG_CAPTURE_INTERVAL_1 = 200

• Settings applied to a specific group

GROUP_NAME = testGROUP
{
    LOG_CAPTURE_INTERVAL_1 = 200
    ....
    ....
}

LOG_CAPTURE_INTERVAL_2

• It can be set in master.

• It sets the execution cycle of capture operated in master. The execution cycle is set in millisecond.

• It is used to quickly detect the changes of the redo log file.

• If the value of redo log file is not changed after executing 10 times with the value of LOG_CAPTURE_INTERVAL_1, then it is converted to the corresponding value and performs capture.

• The default value is 1000(1 second), and the unit is millisecond.

• Settings applied to all groups

LOG_CAPTURE_INTERVAL_2 = 1000

• Settings applied to a specific group

GROUP_NAME = testGROUP
{
    LOG_CAPTURE_INTERVAL_2 = 1000
    ....
    ....
}

CLUSTER

• It can be set in slave.

• It sets the connection information of N masters whih is operated in cluster environment. (It consists of ID, MASTER_IP and PORT information.)

  • ID: It is an identifier of master in slave, and only number can be input. Once the value is set, it should not be altered.

  • MASTER_IP: It sets IP in which master is being operated.

  • PORT:It sets port in which master is being operated.

• Cluster can be set only within a group.

GROUP_NAME = testGROUP
{
    CLUSTER = (ID=1, MASTER_IP=192.0.0.100, PORT=21102),
              (ID=2, MASTER_IP=192.0.0.101, PORT=21103)
    ....
    ....
}

ORACLE_DRIVER

• It can be set in slave.

• It specifies the location and name of ODBC driver file provided by Oracle.

  • Currently it supports Oracle 11g and 12c.

• It can be set in any group.

ORACLE_DRIVER = '/app/oracle/product/11.2.0/db_1/lib/libsqora.so.11.1'

GROUP_NAME = testGROUP
{
    ....
    ....
}

MYSQL_DRIVER

• It can be set in slave.

• It specifies the location and name of ODBC driver file provided by MySQL.

• It can be set in any group.

MYSQL_DRIVER = '/usr/lib64/libmaodbc.so'

GROUP_NAME = testGROUP
{
    ....
    ....
}

MYSQL_DATABASE

• It can be set in slave.

• It specifies the DATABASE name of MySQL to replicate.

• The DATABASE name of MySQL has the same meaning as the SCHEMA name.

• It can be set in any group.

MYSQL_DATABASE = mysql

GROUP_NAME = testGROUP
{
    ....
    ....
}

DB2_DRIVER

• It can be set in slave.

• It specifies the location and name of ODBC driver file provided by DB2.

• It can be set in any group.

DB2_DRIVER = '/usr/lib64/libdb2o.so'

GROUP_NAME = testGROUP
{
    ....
    ....
}

DB2_DATABASE

• It can be set in slave.

• It specifies the DATABASE name of DB2 to replicate.

• It can be set in any group.

DB2_DATABASE = db2

GROUP_NAME = testGROUP
{
    ....
    ....
}

TIBERO_DRIVER

• It can be set in slave.

• It specifies the file path and file name of the ODBC driver provided by TIBERO.

• It can be set in any group.

TIBERO_DRIVER = '/usr/lib64/libtbodb.so'

GROUP_NAME = testGROUP
{
    ....
    ....
}

SYNC_ORACLE_DRIVER

• It can be set in master.

• When performing a SYNC operation, if the database acting as the slave is Oracle, specify the file path and file name of the Oracle ODBC driver.

• It can be set in any group.

SYNC_ORACLE_DRIVER = '/app/oracle/product/11.2.0/db_1/lib/libsqora.so.11.1'

GROUP_NAME = testGROUP
{
    ....
    ....
}

SYNC_MYSQL_DRIVER

• It can be set in master.

• When performing a SYNC operation, if the database acting as the slave is MYSQL, specify the file path and file name of the MYSQL ODBC driver.

• It can be set in any group.

SYNC_MYSQL_DRIVER = '/usr/lib64/libmaodbc.so'

GROUP_NAME = testGROUP
{
    ....
    ....
}

SYNC_DB2_DRIVER

• It can be set in master.

• When performing a SYNC operation, if the database acting as the slave is DB2, specify the file path and file name of the DB2 ODBC driver.

• It can be set in any group.

SYNC_DB2_DRIVER = '/usr/lib64/libdb2o.so'

GROUP_NAME = testGROUP
{
    ....
    ....
}

SYNC_TIBERO_DRIVER

• It can be set in master.

• When performing a SYNC operation, if the database acting as the slave is TIBERO, specify the file path and file name of the TIBERO ODBC driver.

• It can be set in any group.

SYNC_TIBERO_DRIVER = '/usr/lib64/libtbodbc.so'

GROUP_NAME = testGROUP
{
    ....
    ....
}

PACKET_COMPRESSION_MODE

• It can be set in master.

• It sets whether to compress the data which is transferred from master to slave.

  • 1: Enable (Default)

  • 0: Disable

• It can be set in a specific group.

GROUP_NAME = testGROUP
{
    PACKET_COMPRESSION_MODE = 1
    ....
    ....
}

Operating

CYCLONE can be operated in D/A or C/S environment of GOLDILOCKS.

CONFIG file or PROTOCOL configuration of ODBC.INI should be used according to the environments as follows. CONFIG configuration takes precedence over ODBC.INI configuration.

The running contents during the operation can be viewed through trace log.

GOLDILOCKS Connection Policy

It is the policy for CYCLONE to connect to GOLDILOCKS.

There are two ways to connect to GOLDILOCKS. One is  that CYCLONE connects to local GOLDILOCKS, and the other is that CYCLONE master remotely connects to slave side GOLDILOCKS on the slave's side when performing SYNC. Both use  CYCLONE config value is preferentially used, then use odbc.ini. However, when performing SYNC, PROTOCOL value is ignored and  it is set to TCP no matter what.

The config properties relating to GOLDILOCKS connection are DSN, PROTOCOL, HOST_IP, HOST_EXTERNAL_IP, HOST_PORT, USER_ID, USER_PW.
(USER_ENCRYPT_PW is listed as USER_PW because it replaces USER_PW.)

Correct Examples

• It is available just with USER_ID and USER_PW because it is connected as D/A.

• The information should exist either in CONFIG or in odbc.ini. HOST_IP and HOST_PORT are ignored because it is operated in D/A.

• USER_ID is operated with the test account because it is connected with TCP and CONFIG takes precedence.

• HOST_EXTERANL_IP can not be set in slave even though it is connected as D/A.

Wrong Examples

• It is connected as D/A, but USER_PW does not exist.

• It is connected as TCP, but HOST_IP does not exist.

Executing Option

CYCLONE should be used with the following options at run-time.

• It executes all groups in master mode by using the default environment file.

prompt> cyclone --master --start

• It terminates all groups in master mode.

prompt> cyclone --master --stop

• It executes all groups in slave mode by using the default environment file.

prompt> cyclone --slave --start

• It terminates all groups in slave mode.

prompt> cyclone --slave --stop

• It executes only the TEST_GROUP group in master mode.

prompt> cyclone --master --start --group TEST_GROUP

• It terminates only TEST_GROUP group among the groups operated in master mode.

prompt> cyclone --master --stop --group TEST_GROUP

• It sets the TEST_CONFIG file in slave mode and executes it.

prompt> cyclone --slave --start --conf TEST_CONFIG

• It encrypts GOLDILOCKS user password.

prompt> cyclone --encrypt test --key 1234
Cyclone Encrypted Passwd : 'YFH+bpBPNvk='

• USER_ENCRYPT_PW is set in the config.

prompt> cyclone --master --start --key 1234

• It deletes the existing replications of master and slave, and newly starts it.

prompt> cyclone --master --start --reset all
prompt> cyclone --slave --start --reset all

• It deletes the existing replications of only the tables T1, T2 in master, and starts it newly from the current point.

prompt> cyclone --master --start --reset 'T1, T2'
prompt> cyclone --slave --start

• It displays the current replication status per each table on master side.

prompt> cyclone --master --info --group GROUP1
================================================
 GROUP NAME = GROUP1
================================================
 SCHEMA NAME         : PUBLIC
 TABLE NAME          : T1 (GIVE-UP (DDL-LSN:129541))
 PHYSICAL ID         : 5299989643264
================================================
 SCHEMA NAME         : PUBLIC
 TABLE NAME          : T2 (ACTIVE (CAPTURE-START-LSN:128922))
 PHYSICAL ID         : 35549444308992
================================================
 SCHEMA NAME         : PUBLIC
 TABLE NAME          : T3 (ACTIVE (CAPTURE-START-LSN:128922))
 PHYSICAL ID         : 35558034243584
================================================
 SCHEMA NAME         : PUBLIC
 TABLE NAME          : T4 (ACTIVE (CAPTURE-START-LSN:128922))
 PHYSICAL ID         : 35566624178176
================================================
   TOTAL   COUNT : 4
   GIVE-UP COUNT : 1
================================================

Operating Examples

The structure of operating CYCLONE is as follows.

• Device structure

  • The source device which is the original data

    • GOLDILOCKS IP: 192.168.0.10

    • GOLDILOCKS port: 22581

    • The table to be replicated: T1, T2

  • The remote target device for replication

    • GOLDILOCKS IP: 192.168.0.20

    • GOLDILOCKS port: 22581

Operating Order

1. Set the original GOLDILOCKS environment.

2. Set the remote GOLDILOCKS environment.

3. Set the CYCLONE MASTER environment.

4. Set the CYCLONE SLAVE environment.

5. Execute and operate it.

Original GOLDILOCKS Configuration

Execute all described Requirements.

Create tables T1, T2 for testing.

gSQL > CREATE TABLE T1( COL1 INTEGER PRIMARY KEY, COL2 VARCHAR(20) );
gSQL > CREATE TABLE T2( COL1 INTEGER PRIMARY KEY, COL2 VARCHAR(20) );
gSQL > COMMIT;

Remote GOLDILOCKS Configuration

Execute Registering User and Setting Privileges.

CYCLONE MASTER Configuration

• CYCLONE MASTER configuration file

  • Storing path: $GOLDILOCKS_DATA/conf/cyclone.master.conf

  • It is the source device.

• It is connected with D/A of the same device, so HOST information does not exist.

USER_ID = cdc_user
USER_PW = cdc_password

GROUP_NAME = GROUP1
{
    PORT = 21102
    CAPTURE_TABLE =
    ( 
        T1,
        T2
    )
}

CYCLONE SLAVE Configuration

• CYCLONE SLAVE configuration file

  • Storing path: $GOLDILOCKS_DATA/conf/cyclone.slave.conf

  • It is the target device.

• It is connected with D/A of the same device, but HOST_IP and HOST_PORT are used for SYNC.

USER_ID = cdc_user
USER_PW = cdc_password
HOST_IP = 192.168.0.20
HOST_PORT = 22581

GROUP_NAME = GROUP1
{
    PORT = 21102
    APPLY_TABLE =
    (
        T1 TO T1,
        T2 TO T2
    )
}

Executing and Operating

• Execute CYCLONE master.

  • It should be executed in the source device.

cyclone --master --start --conf $GOLDILOCKS_DATA/conf/cyclone.master.conf

• Execute CYCLONE SLAVE.

  • It should be executed in the target device.

cyclone --slave --start --conf $GOLDILOCKS_DATA/conf/cyclone.slave.conf

Synchronizing Data

• Synchronizing data

  • It copies all data from the master to the slave, and it performs the replication.

  • It copies the table data in the master to be replicated to the table in the slave when performing data synchronization.

  • Data synchronization can be performed to other databases (Oracle, MySQL, DB2, and Tibero).

    • To use this feature, the ODBC driver for the target database must be installed on the master.

• It should be performed after giving --sync option to both master and slave when starting the operation.

  • --sync all is used to sync all tables, and --sync TABLE_NAME is used to sync a specific table.

    • The table name of master is described in TABLE_NAME. (The table name of master should be described in TABLE_NAME even when it is operated in slave.)

• HOST_IP (or HOST_EXTERNAL_IP) and HOST_PORT should be set in CONFIG or odbc.ini of slave though it is D/A.

• Execute CYCLONE master

  • It should be executed in the source device.

cyclone --master --start --conf $GOLDILOCKS_DATA/conf/cyclone.master.conf --sync all

• Execute CYCLONE SLAVE

  • It should be executed in the target device.

cyclone --slave --start --conf $GOLDILOCKS_DATA/conf/cyclone.slave.conf --sync --all

• The following is an example of a sync error.

  • It is recommended to delete the data from tables od slave to prevent an error.

• N/A

  • Execute MASTER.

gSQL> CREATE TABLE T1( COL1 INTEGER PRIMARY KEY, COL2 VARCHAR(20) );
gSQL> INSERT INTO T1 VALUES( 1, 'HELLO' );

• N/A

  • Execute SLAVE.

gSQL> CREATE TABLE T1( COL1 INTEGER PRIMARY KEY, COL2 VARCHAR(20) );
gSQL> INSERT INTO T1 VALUES( 1, 'BYE' );

• N/A

  • Execute MASTER.

cyclone --master --start --sync all

• N/A

  • Execute SLAVE.

cyclone --slave  --start --sync all
SLAVE cyclone log
[RECEIVER] start the data synchronization.
[RECEIVER] Error Occurred.
STOP Done.

An error occurs and the sync does not operate due to an attempt to using the same value in the primary key.

Initializing Replication Information

• Initializing replication information

  • It restarts the replication from current point for the entire table or a specific table participating in the replication.

  • It is performed by using the --reset TABLE_NAME option or the --reset ALL option.

• Differences of between master and slave when performing the --reset option

  • Master stores table information and column information participating in the replication as meta. If RESET is performed in master by using the --Reset ALL option or the Reset Table_name option, it removes the meta in the table and configures meta again by using the information at the current point.

    • The --reset option should be performed in master to replicate again the table which is given up during the replication performing DDL.

  • The slave stores the information for recovery when restating the replication. --reset All or --reset TABLE_NAME should be described when executing slave as same as when executing master.

Recovery

• When using recovery,

  • It is used when handing over the replication to another cluster member in the same group and proceeding it, due to an error of master while the replication is being processed by using a standalone option in cluster environment.

  • It is performed with --recovery GROUP_NAME in master, and GROUP_NAME describes the group name of slave which previously performed the replication.

Operating CYCLONE in Cluster

Requirements

• It provides a method to replicate members in the same cluster group in cluster environment.

  • Master and slave are operated in a relationship of 1 : 1 in standalone environment.

  • Master and slave are operated in a relationship of N: 1 in cluster environment.

• Available environment

  • Master should be cluster environment. (It is automatically detected.)

  • Slave can be either standalone or cluster environment.

• The status of master connected to slave is called as a node.

Operating

Starting and Terminating

• The time of terminating master and slave are different each in standalone environment and in cluster environment.

  • Standalone environment

    • If master is terminated, then slave is automatically terminated.

    • If slave is terminated, master is switched to an idle state but it is not terminated.

  • Cluster environment

    • Even when master is terminated, slave is not terminated. If another master is already connected, then the replication proceeds. If any master is not connected, slave is switched to an idle state.

    • If slave is terminated, all connected masters are automatically terminated.

Node Type

In cluster environment, CYCLONE classifies master connecting to slave into a trust node and a non-trust node.

Operation According to Node Status

• Switching from a non-trust node to a trust node

  • It is executed when all tables are switched to online by rebalancing the cluster member while an offline table exists.

  • The node is automatically switched from a non-trust node to a trust node, and participates in replication.

• Switching from a trust node to a non-trust node

  • It is executed when a table being replicated in another cluster member in operation is altered while the cluster member is terminated and is not operated.

  • A non-trust node is automatically altered when a cluster member joins, and it is excluded from replication.

Executing

Configuration File

The configuration file of master is as same as that of standalone.
The following is an example of configuring G1N1 master environment, and IP of that device is assumed as 192.168.0.10.

• There is nothing to alter for master even in cluster environment.

USER_ID = cdc_user
USER_PW = cdc_password

GROUP_NAME = G1N1_Master
{
    PORT = 21011
    CAPTURE_TABLE =
    (
        T1,
        T2
    )
}

The following is an example of configuring G1N2 master environment, and IP of that device is assumed as 192.168.0.20.

• There is nothing to alter for master even in cluster environment.

USER_ID = cdc_user
USER_PW = cdc_password

GROUP_NAME = G1N2_Master
{
    PORT = 21012
    CAPTURE_TABLE =
    (
        T1,
        T2
    )
}

CLUSTER is added to the configuration file of slave to describe the information of master.

• CLUSTER consists of ID, MASTER_IP, and PORT. (The sequence does not matter.)

  • ID is input in numbers, and it is used as an internal delimiter to distinguish master in slave. Once it is set, it can not be altered. (If it is altered, it is recognized as a different master.)

  • MASTER_IP is IP in which CYCLONE MASTER is operated in CLUSTER member.

  • PORT is a PORT in which CYCLONE MASTER is operated in CLUSTER member.

• The information of a CLUSTER is described in parentheses, and it is distinguished by a comma (,) between parentheses.

MASTER_IP, PORT which are the master information of CLUSTER are described in CLUSTER configuration.

USER_ID = cdc_user
USER_PW = cdc_password

GROUP_NAME = G1_Slave
{
    CLUSTER = ( ID = 1, MASTER_IP = 192.168.0.10, PORT = 21011 ),
              ( ID = 2, MASTER_IP = 192.168.0.20, PORT = 21012 )
    APPLY_TABLE =
    (
        T1 TO T1,
        T2 TO T2
    )
}

Executing Method

Executing in Master

• It is executed in G1N1.

cyclone --master --start --conf $GOLDILOCKS_DATA/conf/cyclone.master.conf --group G1N1_Master

• It is executed in G1N2.

cyclone --master --start --conf $GOLDILOCKS_DATA/conf/cyclone.master.conf --group G1N2_Master

Executing in Slave

cyclone --slave --start --conf $GOLDILOCKS_DATA/conf/cyclone.slave.conf --group G1_Slave

SYNC Method

Executing Master

It is executed in G1N1. (It is master of which SYNC is to be executed, and it uses --sync option.)

cyclone --master --start --conf $GOLDILOCKS_DATA/conf/cyclone.master.conf --sync all

Executing Slave

cyclone --slave --start --conf $GOLDILOCKS_DATA/conf/cyclone.slave.conf --sync all

Others

--reset option can be used as same as it is used in standalone environment.

Monitoring (CYMON)

CYMON (CYclone MONitor) is a tool for monitoring CYCLONE which is a CDC replication tool. It periodically updates the monitoring information of CDC replication to GOLDILOCKS operated as master.

Configuration File

The configuration file of cymon uses the configuration file used when running cyclone as master. If a specific configuration file is not set by using the --conf option, the $GOLDILOCKS_DATA/conf/cyclone.master.conf file is read as the default when cyclone is run as master.

Monitoring Contents

CYMON

CYMON periodically updates the operating information of CYCLONE to the CYCLONE_MONITOR_INFO table of GOLDILOCKS. The monitoring information is as follows.

Executing and Monitoring

Executing Option

Examples

• Execute CYMON (Update the monitoring information of cyclone_monitor_info table.)

  • Executes CYMON to update the monitoring information per second. It continuously updates the information until terminating CYMON.

cymon --start --cycle 1

• View the monitoring information

gSQL> \set vertical on
gSQL> select * from cyclone_monitor_info;

              GROUP_NAME # GROUP1
                    TIME # 2015-01-13 17:34:53
            MASTER_STATE # READY
             SLAVE_STATE # N/A
             MASTER_PORT # 21102
                SLAVE_IP # null
        REDO_LOG_FILESEQ # 0
       REDO_LOG_BLOCKSEQ # 52392
         CAPTURE_FILESEQ # 0
        CAPTURE_BLOCKSEQ # 0
           APPLY_FILESEQ # 0
          APPLY_BLOCKSEQ # 0
        CAPTURE_INTERVAL # 0
   CAPTURE_INTERVAL_SIZE # 0
      CAPTURE_COMMIT_LSN # 0
        APPLY_COMMIT_LSN # 0

• N/A

  • The information above describes that only CYCLONE MASTER is being operated and SLAVE is waiting.

gSQL> \set vertical on
gSQL> select * from cyclone_monitor_info;

              GROUP_NAME # GROUP1
                    TIME # 2015-01-13 17:36:17
            MASTER_STATE # RUNNING
             SLAVE_STATE # RUNNING
             MASTER_PORT # 21102
                SLAVE_IP # 127.0.0.1
        REDO_LOG_FILESEQ # 0
       REDO_LOG_BLOCKSEQ # 52811
         CAPTURE_FILESEQ # 0
        CAPTURE_BLOCKSEQ # 52811
           APPLY_FILESEQ # 0
          APPLY_BLOCKSEQ # 52811
        CAPTURE_INTERVAL # 0
   CAPTURE_INTERVAL_SIZE # 0
      CAPTURE_COMMIT_LSN # 1023
        APPLY_COMMIT_LSN # 1023

• N/A

  • The information above describes that CYCLONE MASTER and SLAVE are being operated.

• Execute CYMON (Store the monitoring information in trace log)

  • The monitoring information is stored not only in a monitoring table but also in a trace log since when CYMON is normally executed with master.

  • Generally, it is used when unable to view cyclone_monitor_info table due to an error of the master DB. In this case, the information of master DB such as information of REDO_LOG_FILESEQ and REDO_LOG_BLOCKSEQ are not included.

  • Trace log is in $GOLDILOCKS_DATA/trc/cymon.trc.

cymon --start --trace

• View the monitoring information (When using --trace option)

  • It stores the monitoring information since when the cyclone master is normally executed.

GROUP_NAME         TIME                MASTER_STATE SLAVE_STATE  MASTER_PORT  SLAVE_IP       CAPTURE_FILESEQ CAPTURE_BLOCKSEQ TOTAL_TX_COUNT CAPTURE_TX_COUNT  CAPTURE_COMMIT_LSN  APPLY_FILESEQ   APPLY_BLOCKSEQ   APPLY_COMMIT_LSN
------------------ ------------------- ------------ ------------ ----------- --------------- --------------- ---------------- -------------- ---------------- -----------------    -------------- ---------------- ------------------
GROUP1             2016-11-02 15:43:03 READY        N/A                21102 null                          0                0              0                0                0                  0               0                  0 
GROUP2             2016-11-02 15:43:03 READY        N/A                21103 null                          0                0              0                0                0                  0               0                  0

• N/A

  • The information above describes that only CYCLONE MASTER is being operated and SLAVE is waiting.

GROUP_NAME         TIME                MASTER_STATE SLAVE_STATE  MASTER_PORT  SLAVE_IP       CAPTURE_FILESEQ CAPTURE_BLOCKSEQ TOTAL_TX_COUNT CAPTURE_TX_COUNT  CAPTURE_COMMIT_LSN  APPLY_FILESEQ   APPLY_BLOCKSEQ   APPLY_COMMIT_LSN
------------------ ------------------- ------------ ------------ ----------- --------------- --------------- ---------------- -------------- ---------------- -----------------    -------------- ---------------- ------------------
GROUP1             2016-11-02 15:43:11 RUNNING      RUNNING            21102 192.168.0.206                 7            52779              0                0               15346              7            52779             15346
GROUP2             2016-11-02 15:43:11 READY        N/A                21103 null                          0                0              0                0                   0              0                0                 0

• N/A

  • The information above describes that CYCLONE MASTER (group1) and SLAVE are being operated.

• View the operating status of CYMON

$ cymon --status
======================================
|             CYMON STATUS           |
======================================
|       Service is running...        |
--------------------------------------

• Terminate CYMON

$ cymon --stop
stop done.