Skip to content

How to create/delete replication objects#

Overview#

For users who are new to replication, this document explains how to create and delete replication objects.

  • This document is written assuming that the user has completed the preliminary operation for replication structure or configuration.
  • The initial data between the replication target servers must match.

For guidance on replication design and configuration, refer to the following technical documents.

Version#

All versions of Altibase

Preparation#

  • Dedicated IP for replication
    It is recommended to use a dedicated line separate from the service network for the IP to be used for replication.
  • Replication service port
    Decide the service port number to use for replication. It can be arbitrarily set by the user.
    Example: 30300
  • Select the target table for replication
    The table to be replicated must have a primary key.

Enabling the replication function#

Altibase server's replication function is disabled by default, so to use the replication function you must change the relevant property to enable it.

  • If the value of the REPLICATION_PORT_NO property is 0, the replication function is disabled.

    iSQL> SELECT NAME, VALUE1 FROM V$PROPERTY WHERE NAME = 'REPLICATION_PORT_NO';
    NAME                            VALUE1                          
    ------------------------------------------------------------------- 
    REPLICATION_PORT_NO             0                                        
    1 row selected.
    
  • Enabling the replication function requires restarting the Altibase server.

Procedure for changing the REPLICATION_PORT_NO property#

Replication is enabled by changing the value of the Altibase server property REPLICATION_PORT_NO.

The REPLICATION_PORT_NO property also refers to the port number used between replication threads.

The port number must not already be in use on the server. It is arbitrarily assigned by the user, and 30300 is also commonly used.

The following procedure uses an example of setting the replication port (REPLICATION_PORT_NO) to 30300.

  1. Check whether the replication port specified by the user is already in use on the replication target server.

    If the LISTEN state is shown, the port is already in use by another process and cannot be used as the replication port.

    $ netstat -an | grep 30300 | grep LISTEN
    
  2. Open the altibase.properties file, change the REPLICATION_PORT_NO value, and save it.

    This must be done on each of the replication target servers.

    • How to set REPLICATION_PORT_NO
    $ cd $ALTIBASE_HOME/conf
    $ vi altibase.properties
    REPLICATION_PORT_NO = 30300
    
  3. Restart the Altibase server process.

    $ server restart
    

    If you want to restrict application access during the replication object creation process, change the Altibase server's service port and restart it.

    $ export ALTIBASE_PORT_NO=20400
    $ server restart
    
  4. After starting the Altibase server, check the replication port LISTEN status and the property setting.

    • How to check the replication port LISTEN status
    $ netstat -an | grep 30300 | grep LISTEN
    tcp        0      0 0.0.0.0:30300               0.0.0.0:*                   LISTEN
    
    • How to check the REPLICATION_PORT_NO setting
    iSQL> SELECT NAME, VALUE1 FROM V$PROPERTY WHERE NAME = 'REPLICATION_PORT_NO';
    NAME                            VALUE1                          
    -------------------------------------------------------------------
    REPLICATION_PORT_NO             30300                           
    1 row selected.
    

Creating replication object#

  • Replication objects are created with the CREATE REPLICATION statement and define information related to replication such as replication mode, replication server information, and replication target table.
  • Two servers to be replicated are paired.
  • Replication objects must each be created with the same object name on the paired replication target servers.

    • Syntax for creating a replication object
    CREATE REPLICATION replication_name                   -- Define the name of the replication object.
    WITH remote_host_ip, remote_replication_port_no       -- In the WITH clause, specify the IP and PORT of the remote server to be paired with.
    FROM user_name.table_name TO user_name.table_name,    -- List the tables to be replicated in the FROM ~ TO clause. 
    FROM ... 
    ;
    
  • For more information on the replication object creation syntax, refer to the Replication Creation Manual.

Procedure for creating replication objects - Example 1 (when there are 2 replication target servers)#

This is an example of creating a replication object when the conditions for creating a replication object are as follows.

  • The target server for replication is two A and B servers.
  • The replication object name is created as REP1.
  • The IP address of the target server A is 192.168.1.112 and the replication port number is 25524, and
    the IP address of the target server B is 192.168.1.113 and the replication port number is 35524.

  • The target tables for replication are the SYS user's employees table and departments table.

1. Creating the replication object#

  • Replication object creation syntax on server A (IP 192.168.1.112, replication port 25524)

    CREATE REPLICATION rep1 WITH '192.168.1.113', 35524 
    FROM sys.employees TO sys.employees,
    FROM sys.departments TO sys.departments; 
    Create success.
    

  • Replication object creation syntax on server B (IP 192.168.1.113, replication port 35524)

    CREATE REPLICATION rep1 WITH '192.168.1.112', 25524
    FROM sys.employees TO sys.employees,
    FROM sys.departments TO sys.departments; 
    Create success.
    

2. Checking that the replication object was created#

  • Checking on server A

    iSQL> SELECT REPLICATION_NAME, HOST_IP, PORT_NO FROM SYSTEM_.SYS_REPL_HOSTS_;
    REPLICATION_NAME                HOST_IP                         PORT_NO     
    --------------------------------------------------------------------------------
    REP1                            192.168.1.113                   35524       
    1 row selected.
    
    iSQL> SELECT REPLICATION_NAME, LOCAL_USER_NAME, LOCAL_TABLE_NAME FROM SYSTEM_.SYS_REPL_ITEMS_;
    REPLICATION_NAME                LOCAL_USER_NAME                 LOCAL_TABLE_NAME                
    ----------------------------------------------------------------------------------------------------
    REP1                            SYS                             EMPLOYEES                       
    REP1                            SYS                             DEPARTMENTS                     
    2 rows selected.
    

  • Checking on server B

    iSQL> SELECT REPLICATION_NAME, HOST_IP, PORT_NO FROM SYSTEM_.SYS_REPL_HOSTS_;
    REPLICATION_NAME                HOST_IP                         PORT_NO     
    --------------------------------------------------------------------------------
    REP1                            192.168.1.112                   35524       
    1 row selected.          
    
    iSQL> SELECT REPLICATION_NAME, LOCAL_USER_NAME, LOCAL_TABLE_NAME FROM SYSTEM_.SYS_REPL_ITEMS_;
    REPLICATION_NAME                LOCAL_USER_NAME                 LOCAL_TABLE_NAME                
    ----------------------------------------------------------------------------------------------------
    REP1                            SYS                             EMPLOYEES                       
    REP1                            SYS                             DEPARTMENTS                     
    2 rows selected.
    

Procedure for creating replication objects - Example 2 (when there are 3 replication target servers)#

This is an example of creating a replication object when the conditions for creating a replication object are as follows.

  • The target servers for replication are three servers, A, B, and C,
    where A synchronizes with B and C,
    B synchronizes with A and C,
    and C synchronizes with A and B.

  • The name of the replication object is determined as follows.

    • REP_A_B for servers A and B
    • REP_B_C for servers B and C
    • REP_C_A for servers C and A
  • The IP address and replication port number of each server are as follows.

    • Server A: 192.168.1.112, 30300
    • Server B: 192.168.1.113, 30300
    • Server C: 192.168.1.114, 30300

  • The target tables for replication are the SYS user's employees table and departments table.

1. Creating the replication object#

  • Replication object creation syntax on server A (IP 192.168.1.112, replication port 30300)

    CREATE REPLICATION rep_a_b WITH '192.168.1.113', 30300 FROM sys.employees TO sys.employees, FROM sys.departments TO sys.departments; 
    CREATE REPLICATION rep_c_a WITH '192.168.1.114', 30300 FROM sys.employees TO sys.employees, FROM sys.departments TO sys.departments;
    

  • Replication object creation syntax on server B (IP 192.168.1.113, replication port 30300)

    CREATE REPLICATION rep_a_b WITH '192.168.1.112', 30300 FROM sys.employees TO sys.employees, FROM sys.departments TO sys.departments; 
    CREATE REPLICATION rep_b_c WITH '192.168.1.114', 30300 FROM sys.employees TO sys.employees, FROM sys.departments TO sys.departments;
    

  • Replication object creation syntax on server C (IP 192.168.1.114, replication port 30300)

    CREATE REPLICATION rep_c_a WITH '192.168.1.112', 30300 FROM sys.employees TO sys.employees, FROM sys.departments TO sys.departments; 
    CREATE REPLICATION rep_b_c WITH '192.168.1.113', 30300 FROM sys.employees TO sys.employees, FROM sys.departments TO sys.departments;
    

2. Checking that the replication object was created#

  • Checking on server A

    iSQL> SELECT REPLICATION_NAME, HOST_IP, PORT_NO FROM SYSTEM_.SYS_REPL_HOSTS_;
    REPLICATION_NAME                HOST_IP                         PORT_NO     
    --------------------------------------------------------------------------------
    REP_A_B                         192.168.1.113                   30300       
    REP_C_A                         192.168.1.114                   30300       
    2 rows selected.
    
    iSQL> SELECT REPLICATION_NAME, LOCAL_USER_NAME, LOCAL_TABLE_NAME FROM SYSTEM_.SYS_REPL_ITEMS_;
    REPLICATION_NAME                LOCAL_USER_NAME                 LOCAL_TABLE_NAME                
    ----------------------------------------------------------------------------------------------------
    REP_A_B                         SYS                             EMPLOYEES                       
    REP_A_B                         SYS                             DEPARTMENTS                     
    REP_C_A                         SYS                             EMPLOYEES                       
    REP_C_A                         SYS                             DEPARTMENTS                     
    4 rows selected.
    

  • Checking on server B

    iSQL> SELECT REPLICATION_NAME, HOST_IP, PORT_NO FROM SYSTEM_.SYS_REPL_HOSTS_;
    REPLICATION_NAME                HOST_IP                         PORT_NO     
    --------------------------------------------------------------------------------  
    REP_A_B                         192.168.1.112                   30300       
    REP_B_C                         192.168.1.114                   30300       
    2 rows selected.        
    
    iSQL> SELECT REPLICATION_NAME, LOCAL_USER_NAME, LOCAL_TABLE_NAME FROM SYSTEM_.SYS_REPL_ITEMS_;
    REPLICATION_NAME                LOCAL_USER_NAME                 LOCAL_TABLE_NAME                
    ----------------------------------------------------------------------------------------------------                   
    REP_A_B                         SYS                             EMPLOYEES                       
    REP_A_B                         SYS                             DEPARTMENTS                     
    REP_B_C                         SYS                             EMPLOYEES                       
    REP_B_C                         SYS                             DEPARTMENTS                     
    4 rows selected.
    

  • Checking on server C

    iSQL> SELECT REPLICATION_NAME, HOST_IP, PORT_NO FROM SYSTEM_.SYS_REPL_HOSTS_;
    REPLICATION_NAME                HOST_IP                         PORT_NO     
    --------------------------------------------------------------------------------    
    REP_B_C                         192.168.1.113                   30300       
    REP_C_A                         192.168.1.112                   30300     
    2 rows selected.        
    
    iSQL> SELECT REPLICATION_NAME, LOCAL_USER_NAME, LOCAL_TABLE_NAME FROM SYSTEM_.SYS_REPL_ITEMS_;
    REPLICATION_NAME                LOCAL_USER_NAME                 LOCAL_TABLE_NAME                
    ----------------------------------------------------------------------------------------------------                                  
    REP_B_C                         SYS                             EMPLOYEES                       
    REP_B_C                         SYS                             DEPARTMENTS  
    REP_C_A                         SYS                             EMPLOYEES                       
    REP_C_A                         SYS                             DEPARTMENTS                      
    4 rows selected.
    

Starting replication#

Starting replication means starting the data synchronization.

The server that starts replication runs the replication sender thread (Sender), and the remote server paired with it runs the replication receiver thread (Receiver).

  1. Selecting the replication start server (active server)

    The server that starts replication, i.e., the server that runs the replication sender thread (Sender), refers to the server where the change transaction occurs, and is also called the active server.

    Among the paired replication target servers, the one where data changes occur is the active server, and the other server becomes the standby server.

    If change transactions occur on both servers and synchronize in both directions, both servers become active servers.

  2. Starting replication

    Replication is started on the active server with the ALTER REPLICATION statement.

    replication_name is the name of the object created in the Creating replication object step.

    • Replication start syntax
    iSQL> ALTER REPLICATION replication_name START;
    

    The server that executes the above command runs the replication sender thread (Sender), and the remote server paired with it runs the receiver thread (Receiver).

  3. Checking the replication start status

    This is a statement to check whether the replication sender thread (Sender) and receiver thread (Receiver) are running.

    • How to check the running status of the replication sender thread (Sender) on the replication start server (active server)
    iSQL> SELECT REPLICATION_NAME, DECODE(IS_STARTED, 0, 'STOPPED', 1, 'STARTED') IS_STARTED FROM SYSTEM_.SYS_REPLICATIONS_;
    REPLICATION_NAME                IS_STARTED                      
    -------------------------------------------------------------------
    REP                             STARTED                         
    1 row selected.
    
    iSQL> SELECT REP_NAME, DECODE(STATUS, 0, 'STOP', 1, 'START', STATUS) STATUS FROM V$REPSENDER;
    REP_NAME                        STATUS                          
    -------------------------------------------------------------------
    REP                             START                           
    1 row selected.
    
    • How to check the running status of the replication receiver thread (Receiver)
    iSQL> select REP_NAME, MY_IP, MY_PORT FROM V$REPRECEIVER;
    REP_NAME              MY_IP                 MY_PORT     
    ------------------------------------------------------------
    REP                   192.168.1.113         30300       
    1 row selected.
    

Stopping replication and deleting the object#

You can delete the object after stopping replication.

  • Stopping replication
iSQL> ALTER REPLICATION replication_name STOP;
  • Deleting the replication object
iSQL> DROP REPLICATION replication_name ;

Error messages#

Here are some of the error messages that may occur during the process of creating replication objects.

[ERR-61023 : Replication is disabled]#

  • This is an error message that can be encountered when executing the CREATE REPLICATION statement. It occurs when the replication function is disabled
  • Check the value of the REPLICATION_PORT_NO property. Refer to the Enabling the replication function section.

[ERR-61113 : A replicated table must have a primary key. (user_name.table_name)]#

  • This is an error message that may occur when executing the CREATE REPLICATION statement.
  • This occurs when there is no primary key in the replication target table specified in the FROM clause.
  • At the end of the error message, a primary key needs to be created in the table inside () and perform a duplicate object creation statement.

[ERR-6100D : [Sender] Failed to handshake with the peer server (Handshake Process Error)]#

  • This is an error message that may occur when ALTER REPLICATION replication_name START is executed.
  • Check if the remote server's IP and redundancy port number entered in the WITH clause are correct, and that access to the corresponding IP and port is possible.

Reference#