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
