1. 环境说明
部署oracle 到 dm 数据库的单向同步，源端、目标端均为单实例环境
|
|
源端 |
目标端 |
|
服务器IP |
192.168.56.66 |
192.168.56.60 |
|
db 版本 |
oracle 11.2.0.4 |
dm8 |
|
实例名 |
crmdb |
crmdb |
|
实例端口 |
1521 |
5326 |
|
DMHS 同步mgr 端口 |
5355 |
5355 |
|
DMHS 同步data 端口 |
5356 |
5356 |
|
VERI 数据校验agent 端口 |
5347 |
5347 |
|
|
|
|
2. 部署源库oracle 环境
调整系统参数
|
关闭防火墙 # systemctl stop firewalld # systemctl disable firewalld
安装依赖包 2. 安装依赖包 # yum -y install libaio-devel ksh unzip udev # yum -y install gcc gcc-c++ compat-libstdc++-33 elfutils-libelf-devel glibc-devel glibc-headers libaio-devel libstdc++-devel sysstat xclock # yum -y install libcap.x86 libcap-ng libcap-devel libcap-ng-devel libcap-ng-utils libcap-ng-python compat-libcap1
配置/etc/sysctl.conf # vi /etc/sysctl.conf fs.aio-max-nr = 1048576 fs.file-max = 6815744 kernel.shmall = 2097152 kernel.shmmax = 8329127936 kernel.shmmni = 4096 # semaphores: semmsl, semmns, semopm, semmni kernel.sem = 250 32000 100 128 net.ipv4.ip_local_port_range = 9000 65500 net.core.rmem_default=262144 net.core.rmem_max=4194304 net.core.wmem_default=262144 net.core.wmem_max=1048586
禁用selinux # vi /etc/selinux/config SELINUX=disabled |
部署oracle 环境
部署过程略
开启补充日志
|
SQL> select SUPPLEMENTAL_LOG_DATA_MIN, SUPPLEMENTAL_LOG_DATA_ALL from v$database; SUPPLEME SUP -------- --- NO NO
SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA; Database altered.
SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS; Database altered.
SQL> select SUPPLEMENTAL_LOG_DATA_MIN, SUPPLEMENTAL_LOG_DATA_ALL from v$database; SUPPLEME SUP -------- --- YES YES |
关闭回收站
|
SQL> alter system set recyclebin=off deferred; System altered. |
查看字符集
|
SQL> select userenv('language') from dual; USERENV('LANGUAGE') ---------------------------------------------------- AMERICAN_AMERICA.ZHS16GBK
配置NLS_LANG 环境变量 SQL> !echo $NLS_LANG AMERICAN_AMERICA.ZHS16GBK 如果该变量值为空或者与查询结果不一致，请将该变量设置为查询结果的值修改~/.bash_profile 文件，增加export NLS_LANG= “sql 查询结果值” |
创建要同步的数据用户
|
创建用户授权： create user dmhs identified by dmhs; grant dba to dmhs;
如果没有DBA 权限，需要做如下赋权操作（例如用户是DMHS ）： grant all on dmhs_ddl_sql to dmhs; grant select any table to dmhs; grant select any dictionary to dmhs; grant create session to dmhs; grant lock any table to dmhs; grant execute on dbms_flashback to dmhs; grant connect to dmhs |
执行ddl 脚本
|
源端数据库必须允许 DDL 触发器的触发动作，即数据库参数_system_trig_enabled 为 TRUE 或者未设置。 查看隐含参数： SQL> col name format a50; col value format a20; select x.ksppinm name, y.ksppstvl value, y.ksppstdf isdefault, decode(bitand(y.ksppstvf,7),1,'MODIFIED',4,'SYSTEM_MOD','FALSE') ismod, decode(bitand(y.ksppstvf,2),2,'TRUE','FALSE') isadj from sys.x$ksppi x, sys.x$ksppcv y where x.inst_id = userenv('Instance') and y.inst_id = userenv('Instance') and x.indx = y.indx and x.ksppinm like '%_&par%' order by translate(x.ksppinm, ' _', ' '); Enter value for par: system_trig_enabled old 9: x.indx = y.indx and x.ksppinm like '%_&par%' new 9: x.indx = y.indx and x.ksppinm like '%_system_trig_enabled%'
NAME VALUE ISDEFAULT ISMOD ISADJ ------------------------------------------ ---------------- -------------- ---------- ----- _system_trig_enabled TRUE TRUE FALSE FALSE
执行ddl 脚本： SQL> @ddl_sql_ora.sql Table created. /
|
3. 部署目标库dm 环境
调整系统参数
|
关闭防火墙 # systemctl stop firewalld # systemctl disable firewalld
# vi /etc/pam.d/login session required /lib/security/pam_limits.so
修改用户资源 #vi /etc/security/limits.conf dmdba soft nproc 65536 dmdba hard nproc 65536 dmdba soft nofile 65536 dmdba hard nofile 65536
# vi /etc/security/limits.d/20-nproc.conf * soft nproc 1024 为： * soft nproc 163840
禁用selinux # vi /etc/selinux/config SELINUX=disabled
|
创建dm 用户及工作目录
|
# groupadd -g 1001 dinstall # useradd -u 1001 -g 1001 dmdba # passwd dmdba
# mkdir -p /dm8/dmdbms # mkdir -p /dm8/config # mkdir -p /dm8/dmdata # chown -R dmdba:dinstall/dm8 # chmod -R 755 /dm8 |
安装达梦软件
|
挂载DM ISO 介质， 命令行方式安装 # mount /dev/sr0 /mnt # su - dmdba $ cd /mnt $ ./DMInstall.bin -i
输出信息如下： [dmdba@centos7 mnt]$ ./DMInstall.bin -i Please select the installer's language (E/e:English C/c:Chinese) [E/e]: Extract install files.......... core file size (blocks, -c) 0 data seg size (kbytes, -d) unlimited scheduling priority (-e) 0 file size (blocks, -f) unlimited pending signals (-i) 3871 max locked memory (kbytes, -l) 64 max memory size (kbytes, -m) unlimited open files (-n) 1024 pipe size (512 bytes, -p) 8 POSIX message queues (bytes, -q) 819200 real-time priority (-r) 0 stack size (kbytes, -s) 8192 cpu time (seconds, -t) unlimited max user processes (-u) 3871 virtual memory (kbytes, -v) unlimited file locks (-x) unlimited
The max number of open files is too little, suggest to set 65536 or more to number of open files.
Welcome to DM DBMS Installer
Whether to input the path of Key File? (Y/y:Yes N/n:No) [Y/y]:n
Whether to Set The TimeZone? (Y/y:Yes N/n:No) [Y/y]: TimeZone: [ 1]: GTM-12=West Date Line [ 2]: GTM-11=Samoa [ 3]: GTM-10=Hawaii [ 4]: GTM-09=Alaska [ 5]: GTM-08=Pacific(America and Canada) [ 6]: GTM-07=Arizona [ 7]: GTM-06=Central(America and Canada) [ 8]: GTM-05=East(America and Canada) [ 9]: GTM-04=Atlantic(America and Canada) [10]: GTM-03=Brasilia [11]: GTM-02=Middle Atlantic [12]: GTM-01=Azores [13]: GTM=Greenwich Mean Time [14]: GTM+01=Sarajevo [15]: GTM+02=Cairo [16]: GTM+03=Moscow [17]: GTM+04=AbuDhabi [18]: GTM+05=Islamabad [19]: GTM+06=Dakar [20]: GTM+07=BangKok,Hanoi [21]: GTM+08=China [22]: GTM+09=Seoul [23]: GTM+10=Guam [24]: GTM+11=Solomon [25]: GTM+12=Fiji [26]: GTM+13=Nukualofa [27]: GTM+14=Kiribati Please Select the TimeZone [21]:
Installation Type: 1 Typical 2 Server 3 Client 4 Custom Please Input the number of the Installation Type [1 Typical]: 4 # 选择定制安装全部选项 1 Server component 2 Client component 2.1 Manager 2.2 Monitor 2.3 DTS 2.4 Console 2.5 Analyzer 2.6 DISQL 3 DM Drivers 4 Manual component 5 DBMS Service 5.1 Realtime Audit Service 5.2 Job Service 5.3 Instance Monitor Service 5.4 Assistant Plug-In Service Please Input the number of the Installation Type [1 2 3 4 5]:1 2 3 4 5 Require Space: 1088M
Please Input the install path [/home/dmdba/dmdbms]: /dm8/dmdbms Available Space:43G Please Confirm the install path(/dm8/dmdbms)? (Y/y:Yes N/n:No) [Y/y]:y
Pre-Installation Summary Installation Location: /dm8/dmdbms Require Space: 1088M Available Space: 43G Version Information: Expire Date: Installation Type: Typical Confirm to Install? (Y/y:Yes N/n:No):y 2020-09-29 16:27:14 [INFO] Installing DM DBMS... 2020-09-29 16:27:15 [INFO] Installing BASE Module... 2020-09-29 16:27:43 [INFO] Installing SERVER Module... 2020-09-29 16:27:47 [INFO] Installing CLIENT Module... 2020-09-29 16:28:11 [INFO] Installing DRIVERS Module... 2020-09-29 16:28:18 [INFO] Installing MANUAL Module... 2020-09-29 16:28:22 [INFO] Installing SERVICE Module... 2020-09-29 16:28:23 [INFO] Move ant log file to log directory. 2020-09-29 16:28:25 [INFO] Installed DM DBMS completely.
Please execute the commands by root: /dm8/dmdbms/script/root/root_installer.sh End
根据提示，新开一个会话执行脚本 [root@centos7 ~]# /dm8/dmdbms/script/root/root_installer.sh Move /dm8/dmdbms/bin/dm_svc.conf to /etc Modify the files' mode of DM Server Create the DmAPService service Created symlink from /etc/systemd/system/multi-user.target.wants/DmAPService.service to /usr/lib/systemd/system/DmAPService.service. Finished to create the service (DmAPService) Start the DmAPService service
|
初始化实例
准备初始化配置文件
|
$ vi /dm8/config/dminit.ini [CRMDB] # 实例名字 system_path = /dm8/dmdata main = /dm8/dmdata/crmdb/main.dbf main_size = 2048 system = /dm8/dmdata/crmdb/system.dbf system_size = 2048 roll = /dm8/dmdata/crmdb/roll.dbf roll_size = 2048 ctl_path = /dm8/dmdata/crmdb/dm.ctl log_size = 2048 log_path = /dm8/dmdata/crmdb/log01.log log_path = /dm8/dmdata/crmdb/log02.log auto_overwrite =2
|
初始化实例
|
[dmdba@centos7 bin] $ ./dminit CONTROL=/dm8/config/dminit.ini initdb V8 db version: 0x7000a file dm.key not found, use default license! License will expire on 2021-06-24
log file path: /dm8/dmdata/crmdb/log01.log
log file path: /dm8/dmdata/crmdb/log02.log
write to dir [/dm8/dmdata/CRMDB]. create dm database success. 2020-09-30 17:57:43
或者使用最简单方式初始化实例 $ ./dminit path=/dmdata instance_name=CRMDB db_name=crmdb page_size=16
实例初始化完成后，会生成对应的实例参数文件 $ ls -lrt /dm8/dmdata/crmdb /dm.ini -rw-rw-r--. 1 dmdba dmdba 49275 Sep 30 17:57 /dm8/dmdata/crmdb/dm.ini |
注册数据库服务
|
使用root 执行 # ls -lrt /dm8/dmdbms/script/root/dm_service_installer.sh -rwxr-xr-x. 1 dmdba dmdba 27352 Sep 29 16:28 /dm8/dmdbms/script/root/dm_service_installer.sh
# ./dm_service_installer.sh -t dmserver -dm_ini /dm8/dmdata/DAMENG/dm.ini -p DMSERVER Created symlink from /etc/systemd/system/multi-user.target.wants/DmServiceDMSERVER.service to /usr/lib/systemd/system/DmServiceDMSERVER.service. Finished to create the service ( DmServiceDMSERVER)
参数说明： -t ： service_type -p ： service_name_postfix -dm_ini ： dm_ini_file
注意：达梦不建议运行dm_service_installer.sh 这个脚本，设置systemctl 启动数据库 |
启动dm 服务
|
$ cp /dm8 /dmdbms/bin/service_template/DmService /dm8 /dmdbms/bin $ vi DmService DM_HOME="/dm8 /dmdbms" INI_PATH=//dm8/dmdata/ crmdb /dm.ini
或者使用 # systemctl start DmServiceDMSERVER # systemctl status DmServiceDMSERVER ● DmServiceDMSERVER.service - Dameng Database Service(DmServiceDMSERVER). Loaded: loaded (/usr/lib/systemd/system/DmServiceDMSERVER.service; enabled; vendor preset: disabled) Active: active (running) since Wed 2020-09-30 18:09:22 CST; 18s ago Process: 10643 ExecStart=/dm8/dmdbms/bin/DmServiceDMSERVER start (code=exited, status=0/SUCCESS) Main PID: 10667 (dmserver) CGroup: /system.slice/DmServiceDMSERVER.service └─10667 /dm8/dmdbms/bin/dmserver /dm8/dmdata/DAMENG/dm.ini -noconsole
Sep 30 18:09:07 centos7 systemd[1]: Starting Dameng Database Service(DmServiceDMSERVER).... Sep 30 18:09:22 centos7 DmServiceDMSERVER[10643]: [39B blob data] Sep 30 18:09:22 centos7 systemd[1]: Started Dameng Database Service(DmServiceDMSERVER)..
|
登陆数据库
|
[dmdba@centos7 dmdata]$ cd /dm8/dmdbms/bin [dmdba@centos7 bin]$ ./disql disql V8 username: password:
Server[LOCALHOST:5236]:mode is normal, state is open login used time: 11.404(ms)
SQL> select name,status$,mode$ from v$instance; LINEID NAME STATUS$ MODE$ ------------------- ---------- ------------ ------ 1 crmdb OPEN NORMAL |
调整达梦参数
|
修改参数： ARCH_INI = 1 RLOG_APPEND_LOGIC = 1 FAST_COMMIT = 0
重新启动dm 数据库
查看归档。 SQL> select arch_mode from v$database; LINEID ARCH_MODE ---------- --------- 1 Y 设置归档路径 SQL> select arch_dest,arch_file_size from v$dm_arch_ini where arch_type='LOCAL' and arch_is_valid='Y';
切换到Mount 状态 SQL> alter database mount; executed successfully
设置归档路径 SQL> alter database add archivelog 'DEST=/dm8/dmdata/archive,TYPE=local, FILE_SIZE=1024,SPACE_LIMIT=2048'; executed successfully
SQL> alter database open; executed successfully
再次查看归档路径 SQL> select arch_dest,arch_file_size from v$dm_arch_ini where arch_type='LOCAL' and arch_is_valid='Y';
LINEID ARCH_DEST ARCH_FILE_SIZE ---------- ------------------- -------------- 1 /dm8/dmdata/archive 1024
同时在实例路径下生成dmarch.ini 配置参数 [dmdba@centos7 archive]$ cat /dm8/dmdata/DAMENG/dmarch.ini #DaMeng Database Archive Configuration file #this is comments
ARCH_WAIT_APPLY = 0
[ARCHIVE_LOCAL1] ARCH_TYPE = LOCAL ARCH_DEST = /dm8/dmdata/archive ARCH_FILE_SIZE = 1024 ARCH_SPACE_LIMIT = 2048
|
检查相关参数
|
SQL> select para_name,para_value from v$dm_ini where para_name ='RLOG_APPEND_LOGIC';
LINEID PARA_NAME PARA_VALUE ----------------- ------------------------------- ---------- 1 RLOG_APPEND_LOGIC 1 |
创建同步用户
|
创建dmhs 用户： SQL>create user dmhs identified by dmhs123456; SQL>grant dba to dmhs; |
4. 安装dmhs 软件
源端dmhs for oracle 安装
安装介质
|
安装dmhs for oracle 的介质 [dmdba@centos7 media]$ unzip dmhs_V3.1.3_oracle_rev92035_rh6_64_veri_20200707.zip [dmdba@centos7 dm8]$ mv debug/ dmhs
|
替换key 文件
|
$ mv dmhsE0037003.key dmhs.key $ chmod 755 dmhs.key $ cd /dm8/dmhs/ debug $ cp /dm8/media/dmhs.key . |
配置odbc
|
网上下载odbc 源码包，安装 $ tar -zxvf unixODBC-2.3.2.gz $ cd unixODBC-2.3.2 $ ./configure --enable-drivers=no --with-iconv-char-enc=GB18030 --enable-gui=no --enable-iconv=yes $ make $ make install
查看ODBC 安装的信息，默认Odbc 安装到 /usr/local/etc 目录下， 注意 : 在 19c 配置 odbc 时，需要使用 prefix 选项指定安装在 /etc 目录下 pdbass-1-idcpdb1[gzodb]/home/gzodb>odbcinst -j unixODBC 2.3.1 DRIVERS............: /usr/local/etc/odbcinst.ini SYSTEM DATA SOURCES: /usr/local/etc/odbc.ini FILE DATA SOURCES..: /usr/local/etc/ODBCDataSources USER DATA SOURCES..: /home/gzodb/.odbc.ini SQLULEN Size.......: 8 SQLLEN Size........: 8 SQLSETPOSIROW Size.: 8
配置 UNIXODBC ，切换到“/usr/local/etc” 目录，修改 odbc.ini 和 odbcinst.ini 参数。
# cd /usr/local/etc # vi odbc.ini [ORACLE] Description = ORACLE ODBC DSN Driver = Oracle in OraDb11g_home1 SERVER = 127.0.0.1 UID = DMHS PWD = dmhs Servername = CRMDB PORT = 1521
odbcinst.ini 参数内容如下所示： [Oracle in OraDb11g_home1] Description = ODBC DRIVER FOR ORACLE Driver = /oracle/app/oracle/product/11.2.0/db_1/lib/libsqora.so.11.1 – 对应 oracle 安装路径 Threading = 0
配置完成之后，可使用 isql 命令测试配置是否正确。 $ isql -v ORACLE DMHS dmhs |
准备dmhs 配置参数dmhs.hs
|
dmhs.hs 放到和dmhs_server 同样的目录下， 即debug 目录。 $ vi dmhs.hs
|
目标端dmhs for dm 安装
安装介质
$ unzip dmhs_V3.1.3_dm8-kafka_rev91683_rh6_64_veri_20200618.zip
替换key 文件
|
$ mv dmhsE0037003.key dmhs.key # key 文件通用于oracle 和dm 版本 $ chmod 755 dmhs.key $ cd /dm8/dmhs/ debug $ cp /dm8/media/dmhs.key . |
准备dmhs 配置参数dmhs.hs
|
dmhs.hs 放到和dmhs_server 同样的目录下。 $ vi dmhs.hs
|
5. 启动数据同步
DMHS 针对各个模块的启停顺序有着严格的顺序要求。在一个同步链路里面，停止模块时，顺序应先从源端开始停，然后停止下一级的目标端，直到链路的最后一级；在启动模块时则是先从链路的末尾一级开始。启动，逆向一级一级的启动。
目标端(dm)
启动dmhs 服务
|
$ cd /dm8/dmhs/debug $ ./dmhs_server ./dmhs.hs MGR[INFO]: DMHS start up, current version: V3.1.3-Build(2020.06.18-91683trunc)_D64(Enterprise Edition) MGR[WARN]: License will expire on 2021-06-01 MGR[INFO]: 成功加载配置文件, 站点号:4, 管理端口:5355, 轮询间隔:3 MGR[INFO]: 管理 服务正在监听管理端口:5355 |
启动接收服务
|
再开启一个会话 [dmdba@centos7 debug]$ ./dmhs_console CSL[INFO]: DMHS 控制台工具: V3.1.3-Build(2020.06.18-91683trunc)_D64 DMHS >
DMHS >connect 127.0.0.1:5355 CSL[WARN]: [INPUT CMD: connect 127.0.0.1:5355] CSL[INFO]: 执行成功
DMHS >start exec CSL[WARN]: [INPUT CMD: start exec] CSL[INFO]: 执行成功
|
第一个窗口的服务信息显示如下：
源端(oracle)
启动dmhs 服务
|
[oracle@oracle release]$ ./dmhs_server ./dmhs.hs MGR[INFO]: DMHS start up, current version: V3.1.3-Build(2020.07.07-92035trunc)_D64(Enterprise Edition) MGR[WARN]: License will expire on 2021-06-01 MGR[INFO]: 成功加载配置文件, 站点号:3, 管理端口:5355, 轮询间隔:3 MGR[INFO]: 管理 服务正在监听管理端口:5355 |
启动投递服务
|
再打开一个会话，登陆dmhs 控制台 [oracle@oracle release]$ ./dmhs_console CSL[INFO]: DMHS 控制台工具: V3.1.3-Build(2020.07.07-92035trunc)_D64
DMHS >connect 127.0.0.1:5355 CSL[WARN]: [INPUT CMD: connect 127.0.0.1:5355] CSL[INFO]: 执行成功
DMHS >clear exec lsn CSL[WARN]: [INPUT CMD: clear exec lsn] CSL[INFO]: 执行成功
DMHS >load 0 "sch.name='DMHS'" CREATE|INSERT|DICT CSL[WARN]: [INPUT CMD: copy 0 "sch.name='DMHS'" CREATE|INSERT|DICT] CSL[INFO]: copy mask is : |CREATE|INSERT|TABLE|DICT|OBJID|REP CSL[UNKNOW]: 执行完成，请查看执行模块日志，检查数据装载是否成功
DMHS >start cpt CSL[WARN]: [INPUT CMD: start cpt] CSL[INFO]: 执行成功 |
这时候第一个会话窗口显示如下信息
后台启动dmhs 服务
|
默认使用dmhs_server 启动的是前端dmhs 服务进程，当操作的会话窗口关闭后，进程也会自动关闭。 可以使用相同目录下的dmhs_serverd 这个shell 脚本来后台启动dmhs_server 服务 注意： 需要根据实际dm 安装的目录修改dmhs_serverd 脚本中参数， $ vi /dm8/dmhs/debug/dmhs_serverd ....... #set execute environment #REPLACE DMHS_HOME path DMHS_HOME=/dm8/dmhs # 修改为对应的路径 #REPLACE program dmhs_server dir PROG_DIR=/dm8/dmhs/debug # 修改为对应的路径 #REPLACE program dmhs_server config path CONF_PATH=/dm8/dmhs/debug/dmhs.hs # 修改为dmhs.hs 对应的路径
启动dmhs_server 服务 [dmdba@centos7 debug]$ ./dmhs_serverd start Starting dmhs_serverd: [ OK ] [dmdba@centos7 debug]$ ./dmhs_serverd status dmhs_serverd (pid 13850) is running... |
6. 测试数据同步
源端创建表，写入数据
|
[oracle@oracle release]$ sqlplus dmhs/dmh SQL> create table t1 (id int,name varchar2(10), addr varchar2(10)); Table created.
SQL> insert into t1 values (1,'tom','gz'); 1 row created.
SQL> alter table t1 add constraint pk_t1 primary key (id); Table altered.
SQL> commit; Commit complete. |
目标端验证同步结果
|
目标库检查数据： SQL> select * from dmhs.t1; LINEID ID NAME ADDR ---------- -- ---- ---- 1 1 tom gz
used time: 1.443(ms). Execute id is 320. SQL> desc dmhs.t1;
LINEID NAME TYPE$ NULLABLE ------------------ ------- ------------------ -------------- 1 ID NUMBER N 2 NAME VARCHAR(10) Y 3 ADDR VARCHAR(10) Y
used time: 20.534(ms). Execute id is 321.
可以看到，数据已经正常同步过来
|
目标端的dmhs 服务控制台显示
7. 使用VERI 验证数据同步
安装DMHS 的环境中， 可以使用dmhs_veri 验证目标端和源端的数据是否一致， 在运行dmhs_veri 的机器上必须配置好unixODBC 。
配置agent 代理
源端oracle 的 agent.xml
|
[oracle@oracle release]$ pwd /oracle/dmhs/release
[dmdba@centos7 debug]$ cat agent.xml
|
目标库DM 的agent.xml
|
[dmdba@centos7 debug]$ cat agent.xml
|
前台启动agent 代理
|
源端oracle 库： $ cd /oracle/dmhs/release $ ./dmhs_veri_agent_ora
目标端dm 库： $ cd /dm8/dmhs/debug $./dmhs_veri_agent_dm8 |
配置dmhs_agent 后台服务启动
|
$ vi dmdb/dmhs/bin/AgentDmService
DMHS_HOME=/opt/dmdb/dmhs PROG_DIR=/opt/dmdb/dmhs/bin CONF_PATH=/opt/dmdb/dmhs/bin/agent.xml
$ ./ AgentDmService start $ ./ AgentDmService stop
|
准备veri 数据比对
安装oracle 客户端
Veri 工具运行在目标库dm 的服务器上，这个机器需要安装oracle client 。
配置tnsnames.ora
|
[dmdba@centos7 debug]$ cat $ORACLE_HOME/network/admin/tnsnames.ora CRMDB = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.66)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = crmdb) ) ) |
配置环境变量
|
目标端 dm ，在LD_LIBRARY_PATH 增加oracle 的lib 库路径调用。 $ vi .bash_profile export ORACLE_BASE=/oracle/app/oracle export ORACLE_HOME=/oracle/app/oracle/product/11.2.0/db_1
export DM_HOME="/dm8/dmdbms" export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:/dm8/dmdbms/bin:/dm8/dmhs/debug/:/usr/local/lib:$ORACLE_HOME/lib |
安装unixODBC
|
yum install gcc -y # tar -zxvf unixODBC-2.3.1.tar.gz #./configure --enable-iconv=yes --with-iconv-char-enc=GB18030 # make && make install
[dmdba@centos7 debug]$ odbcinst -j unixODBC 2.3.1 DRIVERS............: /usr/local/etc/odbcinst.ini SYSTEM DATA SOURCES: /usr/local/etc/odbc.ini FILE DATA SOURCES..: /usr/local/etc/ODBCDataSources USER DATA SOURCES..: /home/dmdba/.odbc.ini SQLULEN Size.......: 8 SQLLEN Size........: 8 SQLSETPOSIROW Size.: 8
修改odbc 配置文件： [dmdba@centos7 debug]$ cat /usr/local/etc/odbcinst.ini [Oracle in OraDb11g_home1] Ddecription = ODBC DRIVER FOR ORACLE Driver = /oracle/app/oracle/product/11.2.0/db_1/lib/libsqora.so.11.1 Treading = 0
[DM8 ODBC DRIVER] Description = ODBC DRIVER FOR DM8 Driver = /dm8/dmdbms/bin/libdodbc.so Setup = /lib/libdmOdbcSetup.so threading = 0
[dmdba@centos7 debug]$ cat /usr/local/etc/odbc.ini [ORACLE] Description = ORACLE ODBC DSN Driver = Oracle in OraDb11g_home1 SERVER = 192.168.56.66 UID = dmhs PWD = dmhs Servername = crmdb # 这里应该对应 oracle 客户端的 tnsnames.ora 中的名字 PORT = 1521
[DM] Description = DM ODBC DSN Driver = DM8 ODBC DRIVER SERVER = 192.168.56.60 UID = DMHS PWD = dmhs123456 TCP_PORT = 5236
验证ODBC 是否配置正确。 [dmdba@centos7 ~]$ isql dm dmhs dmhs123456 +---------------------------------------+ | Connected! | | | | sql-statement | | help [tablename] | | quit | | | +---------------------------------------+
验证库文件正常装载 [root@centos7 ~]# ldd /oracle/app/oracle/product/11.2.0/db_1/lib/libsqora.so.11.1 linux-vdso.so.1 => (0x00007ffd9772e000) libdl.so.2 => /lib64/libdl.so.2 (0x00007f9a0138d000) libm.so.6 => /lib64/libm.so.6 (0x00007f9a0108b000) libpthread.so.0 => /lib64/libpthread.so.0 (0x00007f9a00e6f000) libnsl.so.1 => /lib64/libnsl.so.1 (0x00007f9a00c55000) libclntsh.so.11.1 => /lib64/libclntsh.so.11.1 (0x00007f99fe1eb000) libodbcinst.so.1 => /lib64/libodbcinst.so.1 (0x00007f99fdfd4000) libc.so.6 => /lib64/libc.so.6 (0x00007f99fdc06000) /lib64/ld-linux-x86-64.so.2 (0x00007f9a01591000) libnnz11.so => /oracle/app/oracle/product/11.2.0/db_1/lib/libnnz11.so (0x00007f99fd839000) libaio.so.1 => /lib64/libaio.so.1 (0x00007f99fd637000) 如果出现file not found 的提示信息，则需要检查相关配置 |
veri 配置文件
|
目标库上： [dmdba@centos7 debug]$ cat veri.xml
|
生成全库数据对比xml
|
根据dmhs.hs 中配置的所有同步表， 生成对应的比对数据表。 源端操作： $ ./veri_xml_crt job50.xml dmhs.hs
输出： [oracle@oracle release]$ ./veri_xml_crt job50.xml dmhs.hs SND[INFO]: ENABLE: DMHS.*
[oracle@oracle release]$ more job50.xml
|
对比数据
|
目标库操作 1. 对比xml 表中的全部表数据的同步情况 $ ./dmhs_veri jobname=job50.xml mode=normal
2. 手工指定表比对数据同步情况 $ ./dmhs_veri "table=(DMHS.T1==DMHS.T1)" mode=normal |
8.dmhs 基础维护
查看源端、目标端的dmhs 状态
|
DMHS >state CSL[WARN]: [INPUT CMD: state] CSL[INFO]: MGR: 执行 CSL[INFO]: EXEC:1 CSL[INFO]: VID SITEID CPT IP PORT DBNAME CSL[INFO]: 0 3 192.168.56.66 5355 CSL[INFO]: 执行成功
|
查看统计信息
|
DMHS >exec 3 CSL[WARN]: [INPUT CMD: exec 3] 日志捕获模块信息: 日志执行模块信息: 状 态: 空闲 状 态: 空闲 库 名:crmdb 数 据 库: 正常 库 ID:3 活动事务:0 个 日志模式: 在线 等待执行:0 个 分析队列:0 个 执行次数:1 次(0 次/S) 投递队列:0 个 提交事务:1 个(0 个/S) 日志页序号:0 回滚事务:1 个(0 个/S) 待分析日志:0 个 影响行数:1 行(0 行/S) 待投递日志:0 个 缓存大字段:0 个 日志页数:54616 页(420/S) 检 查 点:2020-10-14 09:22:00 投递次数:6410 次(49/S) CVT 状 态: 空闲 文件IO 次数:399 次 (3/S) CVT 事务数:0 个 缓存日志量:0 B(0B/S) 解析日志量：0 B(0B/S) 日志总量:13463472 B(101.14K/S) 归档目录:/oracle/app/oracle/fast_recovery_area/CRMDB/archivelog 日志文件:redo01.log 日志时间:2020-10-14 17:36:24
日志发送模块信息: 日志接收模块信息: 状 态: 等待日志 状 态: 等待接收日志 过滤个数:0 总日志数:6447 队列长度:0 (0.00 percent) 接收次数:176 次 总日志数:6448 执行次数:6447 次 发送次数:176 次 日志LSN:1026968 日志LSN:1026968 消息 LSN:176 消息LSN:176 起始LSN:1011601 起始LSN:1011601 消息流量:162447 B(1.22K/S) 消息流量:162447 B(1.22K/S) 日志时间:2020-10-14 17:36:12 推送状态: 未接收推送 CSL[INFO]: 执行成功
|
查看同步延迟
|
DMHS >lag 3 CSL[WARN]: [INPUT CMD: lag 3] 站点号:3 表:.DMHS.T1 LAG:53s START_SEQID:0 INS:1 DEL:0 UPD:0 CSL[INFO]: 执行成功
|
Dmhs 启停顺序
|
在数据同步运行过程中，会涉及到同步模块的启停操作。DMHS 针对各个模块的启停顺序有着严格的顺序要求。在一个同步链路里面，停止模块时，顺序应先从 源端开始停，然后停止下一级的目标端，直到链路的最后一级；在 启动模块时则是先从链路的末尾一级开始启动，逆向一级一级的启动。在同时配置有 CPT 或 EXEC 的模块中，可以使用命令指定启停某个模块。 |
查看检查点信息
|
查看目标库的log 目录下dmhs 日志 $ cat dmhs_202010.log | grep CHECKPOINT 2020-10-01 01:05:12 EXE[WARN]: 建表: DMHS_CHECKPOINT_TABLE 2020-10-01 01:57:31 EXE[INFO]: CHECKPOINT SITEID:3 SEQID: 983169, LFS : 0 EPOCH: 0(UNKNOW) 2020-10-01 02:03:43 EXE[INFO]: CHECKPOINT SITEID:3 SEQID: 983397, LFS : 0 EPOCH: 1053369758(2020-10-09 18:42:38) 2020-10-01 02:04:17 EXE[INFO]: CHECKPOINT SITEID:3 SEQID: 983417, LFS : 0 EPOCH: 1053369768(2020-10-09 18:42:48) 2020-10-01 02:28:22 EXE[INFO]: CHECKPOINT SITEID:3 SEQID: 984311, LFS : 0 EPOCH: 1053371237(2020-10-09 19:07:17) 2020-10-14 09:19:06 EXE[INFO]: CHECKPOINT SITEID:3 SEQID: 984311, LFS : 0 EPOCH: 1053371237(2020-10-09 19:07:17) 2020-10-14 09:22:13 EXE[INFO]: CHECKPOINT SITEID:3 SEQID: 1011602, LFS : 0 EPOCH: 1053768119(2020-10-14 09:21:59) 2020-10-14 09:23:50 EXE[INFO]: CHECKPOINT SITEID:3 SEQID: 1011602, LFS : 0 EPOCH: 1053768120(2020-10-14 09:22:00) 2020-10-14 09:45:13 EXE[INFO]: CHECKPOINT SITEID:3 SEQID: 1011602, LFS : 0 EPOCH: 1053768120(2020-10-14 09:22:00) 2020-10-14 17:36:21 EXE[INFO]: CHECKPOINT SITEID:3 SEQID: 1011602, LFS : 0 EPOCH: 1053768120(2020-10-14 09:22:00)
|