SQL Server数据迁移到udb
本节讲述 data2udb 工具的使用,此工具可以将 表结构/数据/视图/函数/存储过程/触发器等 导出到文件,并对语法自动转换,最后将文件导入到udb。
结合ogg实现增量数据同步。
注意事项
目标端对应数据库的表将被清除,如有重要数据请先备份
增量同步功能仅支持 SQL Server 2012, SQL Server 2014, SQL Server 2016, SQL Server 2017, SQL Server 2019
Sql Server 2008 不支持SEQUENCE
增量同步功能源端需要开启Sql Server 代理
需要 docker 环境
不支持arm架构服务器
内存需大于4G
数据导出时需要落到磁盘上,确保磁盘空间充足
确保系统时间和源端目标端一致
账号,密码,库名不建议有特殊字符
增量同步期间不支持表结构变更
增量同步需先停止源端数据写入
操作步骤
准备连接信息—–下载容器镜像—–启动容器—–准备增量同步—–全量导出—–全量导入—–开启增量同步
准备
以下用source库名为例
USE [master]
GO
CREATE LOGIN [ogguser] WITH PASSWORD=N'oggpwd', DEFAULT_DATABASE=[source], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO
EXEC master..sp_addsrvrolemember @loginame = N'ogguser', @rolename = N'sysadmin'
GO
--创建增量同步ogguser账号，并设置sysadmin权限
USE [source]
GO
CREATE SCHEMA [ogg] AUTHORIZATION [dbo];
GO
alter database source set recovery full;
GO
EXECUTE sys.sp_cdc_enable_db;
--创建ogg模式,用于记录同步信息;
--设置为完整恢复模式;
--开启cdc;
USE [source]
SELECT name,is_cdc_enabled FROM sys.databases WHERE is_cdc_enabled = 1 and name='source';
SELECT name,is_tracked_by_cdc FROM source.sys.tables WHERE is_tracked_by_cdc = 1;
--检查启用cdc的库和表
BACKUP DATABASE [source] TO DISK= N'E:\source.bak' WITH CHECKSUM,COMPRESSION
--首次备份数据库(配置完成前不要截断日志)
use source;
create table dbo.test1 (id int primary key, name varchar(50));
create table dbo.test2( id int, name varchar(50), age int);
insert into test1(id,name) values(1,'1a');
insert into test2(id,name) values(1,'2a');
-- 创建模拟表和数据
例如:
s_h=192.168.4.53 #源端地址
s_p=1633 #源端端口
s_s=dbo #源端模式名如dbo
s_d=source #源端数据库名
s_u=sa #源端账号
s_w=admin #源端密码
d_h=192.168.4.11 #目标端地址
d_p=5678 #目标端口
d_d=ms2012_source #目标端库名
d_u=unvdb #目标端账号
d_w=tx #目标端密码
下载容器镜像文件
x86_64 http://download.unvdb.com/您的服务编码/source/tool/ogg/app/x86_64/data2udb.tar.gz
arm64 http://download.unvdb.com/您的服务编码/source/tool/ogg/app/aarch64/data2udb.tar.gz
服务编码联系技术支持获取
启动容器
导入docker镜像
docker load -i 容器镜像文件
Loaded image: data2udb_x86:v24.8
#导入的镜像是 data2udb_x86:v24.8,下文使用此名称创建容器
创建容器
docker run --network=host -it --name data2udb -e TZ=Asia/Shanghai -e s_h=源端地址 -e s_p=源端端口 -e s_s=源端模式名如dbo -e s_d=源端数据库名 -e s_u=源端账号 -e s_w=源端密码 -e d_h=目标端地址 -e d_p=目标端口 -e d_d=目标端数据库名 -e d_u=目标端账号 -e d_w=目标端密码 -e ty=full data2udb_x86:v24.8 /bin/bash
#请对应修改账号信息和镜像名称
准备增量同步,仅当需要增量同步时操作
开启目标端接收
export LD_LIBRARY_PATH=/app/udb/lib/:/app/instantclient_19_3/
cat << EOF > /etc/odbc.ini
[unvdb]
Description = unvdb
Driver = /app/udb/lib/GGpsql25.so
Database = $d_d
Servername = $d_h
UserName = $d_u
Password = $d_w
PortNumber = $d_p
ReadOnly = 0
ConnSettings = set client_encoding to UTF8
[msodbcsql1710]
Description = mssql
Driver=ODBC Driver 17 for SQL Server
Server = $s_h,$s_p
Database = $s_d
User=$s_u
Password=$s_w
TrustServerCertificate=YES
EOF
cat << EOF > /app/udb/dirprm/repz.prm
replicat repz
SETENV(ODBCINI=/etc/odbc.ini)
SETENV(NLS_LANG=AMERICAN_AMERICA.ZHS16GBK)
TARGETDB unvdb,userid $d_u,password $d_w
DISCARDFILE ./dirrpt/repz.rpt, append
reportcount every 60 seconds, rate
cachemgr cachesize 1G
container mem_limit 2G
map $s_s.*, target public.*;
EOF
启动目标端接收
cat << EOF > /tmp/2
start mgr
dblogin sourcedb unvdb
add checkpointtable public.checkpointtab
add replicat repz,exttrail ./dirdat/ea,checkpointtable public.checkpointtab
exit
EOF
/app/udb/ggsci paramfile /tmp/2
开启源端提取
echo 'config ogg src ......'
echo "GGSCHEMA OGG" > /app/mssql/GLOBALS
cat << EOF > /app/mssql/dirprm/extz.prm
extract extz
--SETENV (NLS_LANG=AMERICAN_AMERICA.ZHS16GBK)
sourcedb msodbcsql1710 userid $s_u,password $s_w
discardfile ./dirrpt/extz.dsc, append
rmthost 127.0.0.1, mgrport 8030, tcpbufsize 288750, tcpflushbytes 288750
RMTTRAIL ./dirdat/ea
IGNOREREPLICATES
--TRANLOGOPTIONS DBLOGREADER
reportcount every 60 seconds, rate
table $s_s.*;
EOF
启动源端提取
cat << EOF > /tmp/2
start mgr
dblogin sourcedb msodbcsql1710 userid $s_u password $s_w
add trandata $s_s.*
ADD CHECKPOINTTABLE ogg.ckpt
shell sqlcmd -S $s_h,$s_p -U $s_u -P $s_w -d $s_d -Q "EXECUTE sys.sp_cdc_drop_job 'cleanup';"
shell ./ogg_cdc_cleanup_setup.sh createJob $s_u $s_w $s_d $s_h,$s_p ogg
add ext extz, tranlog, begin now
add exttrail ./dirdat/ea, extract extz
start extz
exit
EOF
/app/mssql/ggsci paramfile /tmp/2
检查增量数据
在源端插入数据, /app/udb/dirdat/ 目录下存在ea开头的文件,说明增量数据已经开始接收
ls /app/udb/dirdat/ -al
total 4
drwxr-x--- 1 root root 33 Aug 5 10:50 .
drwxr-xr-x 1 root root 140 Aug 5 10:45 ..
-rw-r----- 1 root root 1336 Aug 5 10:50 ea000000000
执行全量导出
准备ddl导出配置文件
mkdir /data/oradata -p
cat <<EOF > /tmp/ddl.conf
ORACLE_HOME /usr/lib/oracle/19.10/client64/
ORACLE_DSN dbi:ODBC:driver=msodbcsql1710;server=$s_h,$s_p;database=$s_d;TrustServerCertificate=yes
ORACLE_USER $s_u
ORACLE_PWD $s_w
SCHEMA $s_s
NLS_LANG UTF8
#NLS_LANG AMERICAN_AMERICA.UTF8
#设置字符编码
CLIENT_ENCODING UTF8
USE_RESERVED_WORDS 1
#启用后关键字用双引号包起来
#INDEXES_SUFFIX _idx
#给索引名添加后缀,当索引跟表同名时，建议配置该项
INDEXES_RENAMING 1
#按照“表名_列名“的格式给索引重命名,当多次使用相同索引名或相同表名的数据库，建议开启
NULL_EQUAL_EMPTY 1
#把Null设置为空字符串
USE_RESERVED_WORDS 1
INDEXES_RENAMING 1
DEFAULT_NUMERIC bigint
CASE_INSENSITIVE_SEARCH none
#EXPORT_SCHEMA 1
#CREATE_SCHEMA 1
TYPE TYPE,TABLE,FUNCTION,MVIEW,PARTITION,PROCEDURE,VIEW,TRIGGER,SEQUENCE,SYNONYM,SEQUENCE_VALUES
PG_NUMERIC_TYPE 0
#PG_INTEGER_TYPE 1
#DATA_TYPE NUMBER:bigint
#DATA_TYPE NUMBER(18, 0):bigint,NUMBER(9, 0):bitnit
#DEFAULT_NUMERIC numeric
ENABLE_MICROSECOND 1
#开启时间类型YYYY-MM-DD HH24:MI:SS.FF，设置为0时间类型则是YYYY-MM-DD HH24:MI:SS
#REPLACE_ZERO_DATE
#默认将0000-00-00 00:00:00 替换为 NULL。但当列有非空约束时，需配置该项, 替换为 1970-01-01 00:00:00
EMPTY_LOB_NULL 1
#将空的empty数据指定为null，如果有大量的空lob数据列，建议开启，可以提升迁移速度
#PACKAGE_AS_SCHEMA 0
#不将包导出为模式，而是导出为简单函数
#SKIP fkeys checks
#SKIP keys pkeys ukeys indexes checks
OUTPUT_DIR /data/oradata
#输出sql目录
OUTPUT ddl.sql
PG_VERSION 14
#STOP_ON_ERROR 0
PACKAGE_AS_SCHEMA 1
PREFIX_PARTITION 1
PREFIX_SUB_PARTITION 1
DISABLE_PARTITION 1
RENAME_PARTITION 1
TRANSACTION readonly
EOF
准备数据导出配置文件
cat << EOF > /tmp/data.conf
ORACLE_HOME /usr/lib/oracle/19.10/client64/
ORACLE_DSN dbi:ODBC:driver=msodbcsql1710;server=$s_h,$s_p;database=$s_d;TrustServerCertificate=yes
ORACLE_USER $s_u
ORACLE_PWD $s_w
SCHEMA $s_s
NLS_LANG UTF8
#NLS_LANG AMERICAN_AMERICA.UTF8
#设置字符编码
CLIENT_ENCODING UTF8
USE_RESERVED_WORDS 1
#启用后关键字用双引号包起来
#INDEXES_SUFFIX _idx
#给索引名添加后缀,当索引跟表同名时，建议配置该项
INDEXES_RENAMING 1
#按照“表名_列名“的格式给索引重命名,当多次使用相同索引名或相同表名的数据库，建议开启
NULL_EQUAL_EMPTY 1
#把Null设置为空字符串
USE_RESERVED_WORDS 1
INDEXES_RENAMING 1
DEFAULT_NUMERIC bigint
CASE_INSENSITIVE_SEARCH none
TYPE COPY
STOP_ON_ERROR 0
PG_NUMERIC_TYPE 0
PG_INTEGER_TYPE 1
DEFAULT_NUMERIC numeric
ENABLE_MICROSECOND 1
#REPLACE_ZERO_DATE
EMPTY_LOB_NULL 1
OUTPUT_DIR /data/oradata
OUTPUT data.sql
FILE_PER_TABLE 1
PREFIX_PARTITION 1
PREFIX_SUB_PARTITION 1
DISABLE_PARTITION 1
RENAME_PARTITION 1
TRANSACTION readonly
#JOBS 32
#DATA_LIMIT 50000
#PARALLEL_TABLES 4
#开启后可以加快导出速度，但有可能部分表导出异常
#EXCLUDE table1 example_.*
#ALLOW T_APM_MONITORSET_L
#PG_DSN dbi:Pg:dbname=oracle;host=192.168.2.81;port=5678
#PG_USER unvdb
#PG_PWD udb
#PG_SCHEMA soe
PG_VERSION 14
EOF
开始导出
data2udb -c /tmp/ddl.conf
[========================>] 11/11 tables (100.0%) end of scanning.
[========================>] 0/0 types (100.0%) end of output.
[========================>] 11/11 tables (100.0%) end of table export.
[========================>] 0/0 functions (100.0%) end of functions export.
[========================>] 0/0 materialized views (100.0%) end of output.
[========================>] 0/0 partitions (100.0%) end of output.
[========================>] 0/0 procedures (100.0%) end of procedures export.
[========================>] 0/0 views (100.0%) end of output.
[========================>] 0/0 triggers (100.0%) end of output.
[========================>] 0/0 synonyms (100.0%) end of output.
data2udb -c /tmp/data.conf
[========================>] 11/11 tables (100.0%) end of scanning.
[========================>] 119121/119121 rows (100.0%) Table FRGH_BILL (29 sec., 4107 recs/sec)
[========================>] 161699/161699 rows (100.0%) Table FRGH_ORDER (18 sec., 8983 recs/sec)
[========================>] 1765938/1765938 rows (100.0%) Table INV_ENTRY (61 sec., 28949 recs/sec)
[========================>] 115629/115629 rows (100.0%) Table INV_SKU (7 sec., 16518 recs/sec)
[========================>] 0/0 rows (100.0%) Table INV_STACK (0 sec., 0 recs/sec)
[========================>] 1385773/1385773 rows (100.0%) Table INV_TALLY (71 sec., 19517 recs/sec)
[========================>] 2165/2165 rows (100.0%) Table REF_VESSEL (1 sec., 2165 recs/sec)
[========================>] 444288/444288 rows (100.0%) Table TALLY_TICKET (23 sec., 19316 recs/sec)
[========================>] 3507/3507 rows (100.0%) Table VSL_VISIT (2 sec., 1753 recs/sec)
[========================>] 7014/7014 rows (100.0%) Table VSL_VOYAGE (2 sec., 3507 recs/sec)
[========================>] 1/1 rows (100.0%) Table t1 (1 sec., 1 recs/sec)
[========================>] 4005135/4005135 rows (100.0%) on total estimated data (216 sec., avg: 18542 tuples/sec)
执行全量导入
导入之前通过以下命令调整导入顺序,如主键外键触发器等需要在导入数据后执行.
提取主键外键
grep 'FOREIGN KEY' /data/oradata/TABLE_ddl.sql > /tmp/foreign.sql ;
grep '^ALTER TABLE' /data/oradata/TABLE_ddl.sql | grep -v 'FOREIGN KEY' > /tmp/alter.sql ;
\cp /data/oradata/TABLE_ddl.sql /tmp/TABLE_ddl.sql && sed -i '/FOREIGN KEY/d' /data/oradata/TABLE_ddl.sql ;
sed -i "s/^ALTER TABLE/-- ALTER TABLE/g" /data/oradata/TABLE_ddl.sql ;
导入对象
export LD_LIBRARY_PATH=/app/udbclient/lib PGPASSWORD=$d_w
/app/udbclient/ud_sql -v ON_ERROR_STOP=1 -h $d_h -p $d_p -U $d_u -w $d_d -f /data/oradata/TABLE_ddl.sql;
/app/udbclient/ud_sql -v ON_ERROR_STOP=1 -h $d_h -p $d_p -U $d_u -w $d_d -f /data/oradata/FUNCTION_ddl.sql;
/app/udbclient/ud_sql -v ON_ERROR_STOP=1 -h $d_h -p $d_p -U $d_u -w $d_d -f /data/oradata/MVIEW_ddl.sql;
/app/udbclient/ud_sql -v ON_ERROR_STOP=1 -h $d_h -p $d_p -U $d_u -w $d_d -f /data/oradata/PARTITION_ddl.sql;
/app/udbclient/ud_sql -v ON_ERROR_STOP=1 -h $d_h -p $d_p -U $d_u -w $d_d -f /data/oradata/PROCEDURE_ddl.sql;
/app/udbclient/ud_sql -v ON_ERROR_STOP=1 -h $d_h -p $d_p -U $d_u -w $d_d -f /data/oradata/VIEW_ddl.sql;
/app/udbclient/ud_sql -v ON_ERROR_STOP=1 -h $d_h -p $d_p -U $d_u -w $d_d -f /data/oradata/SEQUENCE_ddl.sql;
/app/udbclient/ud_sql -v ON_ERROR_STOP=1 -h $d_h -p $d_p -U $d_u -w $d_d -f /data/oradata/SYNONYM_ddl.sql;
注意: 自定义类型 /data/oradata/TYPE_ddl.sql 语法转换可能会不正确,需要手动处理.可以参考 https://www.sqlines.com/online 网站 进行转换或联系技术支持协助.
此时所有对象已导入目标库中.
导入数据
默认导入到 public 模式,可以使用以下方法导入到指定模式.
echo 'SET search_path TO cus_schema_dbo;' > ~/.psqlrc
注意: 通过TDS协议创建的库名对应的是 库名_dbo 模式.对应的拥有者是库名_db_owner
/app/udbclient/ud_sql -v ON_ERROR_STOP=1 -h $d_h -p $d_p -U $d_u -w $d_d -f /data/oradata/data.sql
请务必确认此步骤无报错.
导入主键/外键/触发器
/app/udbclient/ud_sql -v ON_ERROR_STOP=1 -h $d_h -p $d_p -U $d_u -w $d_d -f /tmp/alter.sql
/app/udbclient/ud_sql -v ON_ERROR_STOP=1 -h $d_h -p $d_p -U $d_u -w $d_d -f /tmp/foreign.sql
/app/udbclient/ud_sql -v ON_ERROR_STOP=1 -h $d_h -p $d_p -U $d_u -w $d_d -f /data/oradata/TRIGGER_ddl.sql
此时所有数据已导入目标库中.
开启增量同步,仅当需要增量同步时操作
export LD_LIBRARY_PATH=/app/udb/lib/:/app/instantclient_19_3/
echo "START REPLICAT repz" > /tmp/3
echo "exit" >> /tmp/3
/app/udb/ggsci paramfile /tmp/3
此时在源端的数据修改操作会同步到目标端.
其它命令
docker exec data2udb /bin/bash -c '/app/scripts/status.sh' #查看增量同步状态docker exec data2udb /bin/bash -c 'cat /app/udb/dirrpt/REPZ.rpt' #查看增量同步接收报告docker logs -f data2udb #查看容器日志docker stop data2udb #停止容器docker rm data2udb #删除容器cat /app/udb/ggserr.log #查看目标端增量同步进程日志cat /app/ogg19/ggserr.log #查看源端增量同步进程日志
给TDS账号赋权
如果使用的是TDS(兼容SQL SERVER版的数据库)数据库,此时需要修改表权限才可以用TDS账号访问数据.
例如已经用TDS账号创建了cus_schema 数据库,以下语句修改 cus_schema 模式拥有者为对应的虚拟账号.
DO
$$
DECLARE
row record;
BEGIN
FOR row IN SELECT tablename FROM pg_tables WHERE schemaname = 'cus_schema_dbo'
LOOP
EXECUTE 'ALTER TABLE ' || quote_ident(row.tablename) || ' OWNER TO cus_schema_db_owner';
END LOOP;
END
$$;