Lightdb支持在线同步到ORACLE

一、环境准备

1、安装lightdb和oracle

2、在安装lightdb的服务器上面安装oracle客户端，并配置tnsname，同时安装依赖yum install libaio （ltdts_recvlogical工具需要）

#安装oracle客户端，配置环境变量
export ORACLE_HOME=/home/lightdb/instantclient_21_6
export LD_LIBRARY_PATH=$ORACLE_HOME:$LD_LIBRARY_PATH
export NLS_LANG=AMERICAN_AMERICA.AL32UTF8
export TNS_ADMIN=$ORACLE_HOME/network/admin

#配置tnsname
vim $ORACLE_HOME/network/admin/tnsnames.ora

TEST =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.123)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = test)
    )
  )

3、lightdb上创建的表，oracle要同步创建（根据schema名字映射到oracle对应的用户）

二、使用

1、工具信息

[lightdb@lightdb instantclient_19_10]$ ltdts_recvlogical --help
ltdts_recvlogical controls LightDB logical decoding streams.

Usage:
  ltdts_recvlogical [OPTION]...

Action to be performed:
      --create-slot      create a new replication slot (for the slot's name see --slot)
      --drop-slot        drop the replication slot (for the slot's name see --slot)
      --start            start streaming in a replication slot (for the slot's name see --slot)

Options:
  -E, --endpos=LSN       exit after receiving the specified LSN
  -f, --file=FILE        receive log into this file. - for stdout(deprecated), use debug.dat instead. 
                         debug.dat enable debug mode, otherwise not log. only should use for test
  -F  --fsync-interval=SECS
                         time between write to the oracle database (default: 10)
      --if-not-exists    do not error if slot already exists when creating a slot
  -I, --startpos=LSN     where in an existing slot should the streaming start
  -n, --no-loop          do not loop on connection lost
  -o, --option=NAME[=VALUE]
                         pass option NAME with optional value VALUE to the
                         output plugin, multiple option can be passed by repeat multiple -o option
                         see all wal2sql options, https://github.com/hslightdb/wal2sql
  -P, --plugin=PLUGIN    use output plugin PLUGIN (default: wal2sql, ltdts_recvlogical only support wal2sql, this option reserved only for compatible)
  -s, --status-interval=SECS
                         time between status packets sent to server (default: 10)
  -S, --slot=SLOTNAME    name of the logical replication slot
  -v, --no-verbose       don't output verbose messages
  -V, --version          output version information, then exit
  -?, --help             show this help, then exit

Connection options:
  -d, --dbname=DBNAME    database to connect to
  -h, --host=HOSTNAME    database server host or socket directory
  -p, --port=PORT        database server port number
  -U, --username=NAME    connect as specified database user
  -w, --no-password      never prompt for password
  -W, --password         force password prompt (should happen automatically)
  -T, --target=target_type    sync to where. only-debug|oracle|lightdb|redis|kafka|es|mysql. 
                                      22.2 only support only-debug(not insert oracle, just to json parse)|oracle target. 

Oracle connection options:
  -t, --tnsname=TNSNAME               database to connect to
  -u, --oracle-username=NAME          connect as specified database user
  -k, --oracle-password=PASSWORD      oracle password
  --ignore-duplicate                  ignore duplicate error, when insert into oracle
  --ignore-not-found                  ignore 0 effected rows, when update/delete to oracle
  -z, --date_format=DATE_FORMAT             nls_date_format, default is YYYY-MM-DD HH24:MI:SS
  -Z, --timestamp_format=TIMESTAMP_FORMAT   nls_timestamp_format, default is YYYY-MM-DD HH24:MI:SS.FF6

Report bugs to <https://github.com/hslightdb>.
LightDB home page: <https://www.hs.net/lightdb>

2.lightdb和oracle对应类型

暂不支持INTERVAL、LONG、RAW、ROWID类型

3.创建复制槽，复制槽和database是一对一的，复制槽的名字是全局唯一的

ltdts_recvlogical --create-slot --slot=test_perf5 --plugin=wal2sql -d t1223 -h 192.168.0.124 -p 6789

#ps：需要先在lightdb.conf中配置wal_level=logical，然后重启数据库，否则会报错
ltdts_recvlogical: error: could not send replication command "CREATE_REPLICATION_SLOT "test_perf" LOGICAL "wal2sql" NOEXPORT_SNAPSHOT": ERROR:  logical decoding requires wal_level >= logical

#登录lightdb可查到创建的复制槽
[lightdb@lightdb ~]$ ltsql -p 6789
ltsql (13.3-22.2)
Type "help" for help.

lightdb@postgres=# select slot_name,plugin,slot_type from pg_replication_slots; 
    slot_name     | plugin  | slot_type 
------------------+---------+-----------
 ltcluster_slot_2 |         | physical
 test_perf        | wal2sql | logical
(2 rows)

4.调用工具，开始复制，slot和database要和创建的保持一致

[lightdb@lightdb ~]$ ltdts_recvlogical --tnsname=test --oracle-username=test --oracle-password=test --start --slot=test_perf5 -o include-types=false -o include-type-oids=true -d t1223 -h 192.168.0.124 -p 6789 -f debug.dat
start to sync message to oracle
start to sync message to oracle
start to sync message to oracle
started thread list: [0,1,2,3]
start to sync message to oracle
ltdts_recvlogical: starting log streaming at 0/0 (slot test_perf5)
ltdts_recvlogical: streaming initiated
ltdts_recvlogical: 22-07-01 13:43:10: confirming write up to 0/0, flush to 0/0 (slot test_perf5)
empty xlog record, just a ddl or not supported dml(not insert/update/delete/truncate)
ltdts_recvlogical: 22-07-01 13:43:20: confirming write up to 1/215C16C0, flush to 1/215C16C0 (slot test_perf5)
ltdts_recvlogical: 22-07-01 13:43:30: confirming write up to 1/215C65A0, flush to 1/215C65A0 (slot test_perf5)

-f是必选项，如果后面的文件名是debug.dat，会开启debug模式，耗费性能

#执行DDL语句会报错
empty xlog record, just a ddl or not supported dml(not insert/update/delete/truncate)

#不支持的字段类型，可通过查pg_type来获取类型名称
not supported type, oid=2950