达梦数据库系列—47.DMHS实现Oracle12C到DM8的同步

目录

1、准备介质

2、安装

3、准备源端Oracle和目标端DM8

软件安装

数据库创建

打开归档

开启附加日志

创建辅助表

Oracle端安装ODBC

创建连接用户

创建测试用户和表

4、同步配置

修改服务配置

Oracle到Dm单向同步配置

Dm到Oracle单向同步配置

5、启动DMHS服务

初始装载

装载数据

启动cpt模块

启动exec模块

6、查看信息

---

源端：Oracle12C，RH7.5

目标端：DM8，麒麟V10

介质：

1、准备介质

2、安装

3、准备源端Oracle和目标端DM8

软件安装

略

数据库创建

没有数据库可参考该步骤创建，否则略过。

目标端：

dmdba用户：

dminit path=/dm/data PAGE_SIZE=32 EXTENT_SIZE=32 CASE_SENSITIVE=y CHARSET=1 DB_NAME=HS02 INSTANCE_NAME=HS02 PORT_NUM=5239

root用户：

cd /home/dmdba/dmdbms/script/root/

./dm_service_installer.sh -t dmserver -dm_ini /dm/data/HS02/dm.ini -p HS02

打开归档

源端：

shutdown immediate

startup mount

alter database archivelog;

alter system set log_archive_dest='/oracle/product/db12c/oradata/ywdb/arch';

alter database open;

archive log list

目标端：

ALTER DATABASE MOUNT;

ALTER DATABASE ADD ARCHIVELOG 'TYPE = LOCAL,DEST = /dm/data/HS02/arch,FILE_SIZE = 128,SPACE_LIMIT = 1024';

ALTER DATABASE ARCHIVELOG;

ALTER DATABASE OPEN;

select para_value from v$dm_ini where para_name in ('ARCH_INI');

开启附加日志

源端：

alter database add supplemental log data;

alter database add supplemental log data (all) columns;

select supplemental_log_data_min,supplemental_log_data_all from v$database;

目标端执行：

SP_SET_PARA_VALUE(1,'RLOG_APPEND_LOGIC',1);

select para_value from v$dm_ini where para_name in ('RLOG_APPEND_LOGIC');

创建辅助表

源端执行：

sqlplus / as sysdba

SQL> @/dmhs/scripts/ddl_sql_ora.sql

目标端执行：

set define off

set char_code utf8

start /dmhs/scripts/ddl_sql_dm8.sql

共9个辅助表，4个触发器:

select owner, table_name from dba_tables where owner = 'SYSDBA' and table_name like 'DMHS%' and status = 'VALID';

select owner, trigger_name from dba_triggers where owner = 'SYSDBA' and trigger_name like 'DMHS%' and status = 'Y';

Oracle端安装ODBC

tar -xzvf unixODBC-2.3.0.tar.gz

cd unixODBC-2.3.0

./configure

make

make install

cd /usr/local/etc

vim odbcinst.ini

[Oracle 12c ODBC driver]

Description=Oracle in OraDb12c

Driver=/oracle/product/db12c/db_1/lib/libsqora.so.12.1

vim odbc.ini

[DMHS]

Description = Oracle in OraDb12c

Driver = Oracle 12c ODBC driver

SERVER = 192.168.64.129

UserID = HSEXEC

Password = HSEXEC

Servername = ywdb

PORT = 1521

tnsname.ora

ywdb =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1521))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = ywdb)

))

listener.ora

SID_LIST_LISTENER =

  (SID_LIST =

    (SID_DESC =

      (GLOBAL_DBNAME = ywdb)

      (ORACLE_HOME = /oracle/product/db12c/db_1)

      (SID_NAME = ywdb)

    )

  )

LISTENER =

  (DESCRIPTION_LIST =

    (DESCRIPTION =

      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.64.129)(PORT = 1521))

    )

    (DESCRIPTION =

      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))

    )

  )

sqlnet.ora

SQLNET.INBOUND_CONNECT_TIMEOUT =0

SQLNET.ALLOWED_LOGON_VERSION_SERVER=8

SQLNET.ALLOWED_LOGON_VERSION_CLIENT=8

连接

odbcinst -j

isql dmhs -v

isql dmhs HSEXEC HSEXEC -v

Oracle到Dm:

把libdmoci.so上传到DM端的/dmhs/bin目录下

Dm到Oracle:

unixodbc动态库加到环境变量：

export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:/usr/local/lib

创建软连接：

ln -s /usr/local/lib/libodbc.so.2 /usr/local/lib/libodbc.so.1

创建连接用户

源端执行：

CREATE USER HSEXEC IDENTIFIED by "HSEXEC";

GRANT CONNECT TO HSEXEC;

GRANT PUBLIC TO HSEXEC;

GRANT RESOURCE TO HSEXEC;

GRANT DBA TO HSEXEC;

GRANT SELECT ON SYS.USER$ TO HSEXEC;

目标端执行：

CREATE TABLESPACE HSEXEC DATAFILE 'HSEXEC.DBF' size 128;

CREATE USER HSEXEC IDENTIFIED by "HSEXEC" DEFAULT TABLESPACE HSEXEC DEFAULT INDEX TABLESPACE HSEXEC;

GRANT VTI TO HSEXEC;

GRANT PUBLIC TO HSEXEC;

GRANT RESOURCE TO HSEXEC;

GRANT DBA TO HSEXEC;

创建测试用户和表

略

源端：

CREATE USER HSTEST IDENTIFIED by "HSTEST";

GRANT PUBLIC TO HSTEST;

GRANT RESOURCE TO HSTEST;

GRANT DBA TO HSTEST;

create table HSTEST.t_ora_dm (id int ,name varchar(10));

insert into HSTEST.t_ora_dm VALUES(1,'TEST');

COMMIT;

4、同步配置

修改服务配置

节点一：

cd /dmhs/bin

cp TemplateDmhsService DmhsService

vim DmhsService

节点二：

同上

Oracle到Dm单向同步配置

源端：

cd /dmhs/bin

vim dmhs.hs

exec_policy=2表示执行事务出错时，忽略出错的操作后继续执行；ddl_continue=1表示DDL操作同步出错时，继续往下操作。

<?xml version="1.0" encoding="utf-8"?>

<dmhs>

<base>

<lang>en</lang>

<mgr_port>5345</mgr_port>

<chk_interval>20</chk_interval>

<siteid>1</siteid>

<version>2.0</version>

</base>

<cpt>

<db_type>oracle12c</db_type>

<db_server>ywdb</db_server>

<db_user>HSEXEC</db_user>

<db_pwd>HSEXEC</db_pwd>

<db_port>1521</db_port>

<char_code>PG_UTF8</char_code>

<ddl_mask>op:TABLE:VIEW:PROCEDURE:FUNCTION:TRIGGER:INDEX:CHECK:SEQUENCE:TYPE:PACKAGE:SYNONYM</ddl_mask>

<parse_thr>1</parse_thr>

<arch>

<clear_interval>600</clear_interval>

<clear_flag>1</clear_flag>

<bak_dir></bak_dir>

</arch>

<send>

<ip>192.168.64.132</ip>

<mgr_port>5345</mgr_port>

<data_port>5346</data_port>

<level>0</level>

<trigger>0</trigger>

<constraint>0</constraint>

<identity>0</identity>

<net_turns>0</net_turns>

<filter>

<enable>

<item>HSTEST.T_ORA_DM</item>

</enable>

<disable>

</disable>

</filter>

<map>

<item>HSTEST.T_ORA_DM==HSTEST1.T_ORA_DM</item>

</map>

</send>

</cpt>

</dmhs>

目标端：

cd /dmhs/bin

vim dmhs.hs

<?xml version="1.0" encoding="utf-8"?>

<dmhs>

<base>

<lang>en</lang>

<mgr_port>5345</mgr_port>

<chk_interval>20</chk_interval>

<siteid>2</siteid>

<version>2.0</version>

</base>

<exec>

    <recv>

    <mgr_port>5345</mgr_port>

    <data_port>5346</data_port>

  </recv>

  <enable>1</enable>

  <name>exec</name>

<db_type>DM8</db_type>

<db_server>192.168.64.132</db_server>

<db_user>HSEXEC</db_user>

<db_pwd>HSEXEC</db_pwd>

<db_port>5239</db_port>

<exec_thr>4</exec_thr>

<exec_sql>512</exec_sql>

<exec_trx> 5000 </exec_trx>

<exec_rows>250</exec_rows>

<exec_policy>2</exec_policy>

</exec>

</dmhs>

Dm到Oracle单向同步配置

源端：

<?xml version="1.0" encoding="utf-8"?>

<dmhs>

<base>

<lang>en</lang>

<mgr_port>5345</mgr_port>

<chk_interval>2</chk_interval>

<siteid>2</siteid>

<version>2.0</version>

</base>

<cpt>

<db_type>dm8</db_type>

<db_server>192.168.64.132</db_server>

<db_user>HSEXEC</db_user>

<db_pwd>HSEXEC</db_pwd>

<db_port>5239</db_port>

<ddl_mask>op:TABLE:VIEW:PROCEDURE:FUNCTION:TRIGGER:INDEX:CHECK:SEQUENCE:TYPE:PACKAGE:SYNONYM</ddl_mask>

<parse_thr>1</parse_thr>

<arch>

<clear_interval>600</clear_interval>

<clear_flag>1</clear_flag>

<bak_dir></bak_dir>

</arch>

<send>

<ip>192.168.64.129</ip>

<mgr_port>5345</mgr_port>

<data_port>5346</data_port>

<level>0</level>

<trigger>0</trigger>

<constraint>0</constraint>

<identity>0</identity>

<net_turns>0</net_turns>

<filter>

<enable>

<item>HSTEST1.T_ORA_DM</item>

</enable>

<disable>

</disable>

</filter>

<map>

<item>HSTEST1.T_ORA_DM==HSTEST.T_ORA_DM</item>

</map>

</send>

</cpt>

</dmhs>

目标端：

<?xml version="1.0" encoding="utf-8"?>

<dmhs>

<base>

<lang>en</lang>

<mgr_port>5345</mgr_port>

<chk_interval>20</chk_interval>

<siteid>1</siteid>

<version>2.0</version>

</base>

<exec>

<char_code>PG_UTF8</char_code>

    <recv>

     <mgr_port>5345</mgr_port>

<data_port>5346</data_port>

</recv>

<driver>{Oracle 12c ODBC driver}</driver>

<db_type>oracle12c</db_type>

<db_server>ywdb</db_server>

<db_user>HSEXEC</db_user>

<db_pwd>HSEXEC</db_pwd>

<db_port>1521</db_port>

<exec_thr>4</exec_thr>

<exec_sql>512</exec_sql>

<exec_trx> 5000 </exec_trx>

<exec_rows>250</exec_rows>

<exec_policy>2</exec_policy>

</exec>

</dmhs>

5、启动DMHS服务

节点一：

cd /dmhs/bin

./DmhsService start

或者

./dmhs_server dmhs.hs

ps -ef |grep dmhs

节点二：

cd /dmhs/bin

./DmhsService start

或者

./dmhs_server dmhs.hs

ps -ef |grep dmhs

初始装载

目的端：

./dmhs_console

DMHS> connect

执行成功

DMHS> start exec

执行成功

源端：

 ./dmhs_console

DMHS> connect

执行成功

DMHS> clear exec lsn                              

执行成功

装载字典

copy 0 "sch.name = 'HSTEST'" DICT

装载数据

源端：

装载建表语句

copy 0 "sch.name = 'HSTEST'" CREATE

装载数据

copy 0 "sch.name = 'HSTEST'" INSERT|THREAD|2

装载某个表的数据

copy 0 "sch.name = 'HSTEST' and tab.name in ('T_ORA_DM')" INSERT|THREAD|2

copy 0 "sch.name='EDM_BASE' and tab.name in('VW_CHANNEL_TR_STATUS')" DICT|CREATE|INSERT|THREAD|16|FAST|200|group|32

启动cpt模块

源端：

源端cpt启动，目标端exec会自动启动

DMHS> start cpt

DMHS> state

启动exec模块

目标端：

cd /dmhs/bin

./dmhs_console

DMHS> start exec

DMHS> state

6、查看信息

查看日志：

cd /dmhs/bin/log

tail -100f dmhs_202408.log

目标端：

exec

thr

trx

源端：

cpt