dmhs oracle11G同步dm8

1 oracle开启归档

切换用户（尽量使用以下带 - 的方式切换用户）

su – oracle

创建目录

mkdir /data/oracle/oradata/orcl/archlog

连接数据库

sqlplus / as sysdba

# 关闭数据库服务

SQL> shutdown immediate

# 以 mount 方式启动 oracle 数据库

SQL> startup mount

# 开启归档

SQL> alter database archivelog;

# 设置归档文件路径（如果使用本地路径存放归档日志，需要将 db_recovery_file_dest 参数置空，然后设置 log_archive_dest 参数

SQL> alter system set db_recovery_file_dest=’’;

SQL> alter system set log_archive_dest=’C:\app\Administrator\oradata\orcl53\drchlog’;

# 恢复为 open 状态

SQL> alter database open;

# 再次检查归档

SQL> archive log list;

4.2 开启附加日志

附加日志（supplemental log）指数据库在日志中添加额外信息到日志流中，以支持基于日志的工具，进行数据的分析。

检查附加日志

SQL> select SUPPLEMENTAL_LOG_DATA_MIN min,SUPPLEMENTAL_LOG_DATA_PK  pk,SUPPLEMENTAL_LOG_DATA_UI ui,SUPPLEMENTAL_LOG_DATA_FK fk, SUPPLEMENTAL_LOG_DATA_ALL "all" from v$database;

开启数据库最小附加日志级全列日志

SQL> alter database add supplemental log data;

SQL> alter database add supplemental log data (all) columns;

# 再次检查附加日志

SQL> select supplemental_log_data_min, supplemental_log_data_all from v$database;

4.3 关闭 oracle 的回收机制

检查 oracle 回收机制 on/off

SQL> show parameter recyclebin;# 如果是on，需要关闭oracle回收机制SQL> alter system set recyclebin=off deferred;

需要重启数据库后才生效。

检查字符集是否一致

先查询 oracle 数据库的字符集：（AMERICAN_AMERICA.ZHS16GBK）

SQL> select userenv(‘language’) from dual;

检查字符集是否一致

先查询 oracle 数据库的字符集：（AMERICAN_AMERICA.ZHS16GBK）

SQL> select userenv(‘language’) from dual;

再查询系统字符集

echo $NLS_LANG

• 1

# 如果该变量值为空或者与查询结果不一致，则将该变量设置为查询结果的值

vi ~/.bash_profile# 增加 export NLS_LANG=”sql查询结果”

source ~/.bash_profile

创建同步用户及授权 SYS用户

启动 oracle 数据库。

SQL> create user DMHS identified by "DMHS" default tablespace  USERS temporary tablespace TEMP profile DEFAULT;

SQL> grant connect to DMHS;

SQL> grant create any table to DMHS;

SQL> grant select any table to DMHS;

SQL> grant select any dictionary to DMHS;

SQL> grant create session to DMHS;

SQL> grant lock any table to DMHS;

SQL> grant execute on dbms_flashback to DMHS;

SQL> grant unlimited tablespace to DMHS;

root 用户安装 odbc  ---按照odbc安装即可

odbcinst.ini文件配置：

[Oracle in OraDb11g_home1]

Description = ODBC DRIVER FOR ORACLE

Driver = /opt/oracle/oracle/product/11.2.0/dbhome_1/lib/libsqora.so.11.1

Threading = 0

odbc.ini文件配置

[ORACLE]

Description = ODBC for Oracle

Driver = Oracle in OraDb11g_home1

SERVER = 192.168.216.147

UID =dmhs

PWD =admin123

Servername = orcl1

PORT = 1521

ldd /data/oracle/product/11.2.0/lib/libsqora.so.11.1

find / -name libodbcinst*

复制相应文件到依赖路径下：

cp /usr/lib64/libodbcinst.so.2 /lib64/libodbcinst.so.1

ldd /data/oracle/product/11.2.0/lib/libsqora.so.11.1

isql -v ORACLE DMHS DMHS

DM8安装，开起归档

开启逻辑日志

执行数据库后重启数据库服务生效

SP_SET_PARA_VALUE(2,’RLOG_APPEND_LOGIC’,1);

查询逻辑日志是否开启：1/0

SELECT PARA_VALUE FROM SYS.V$DM_INI WHERE PARA_NAME=’RLOG_APPEND_LOGIC’;

dm.ini 配置参数中“FAST_COMMIT”必须为 0，否则会导致逻辑日志不全而影响同步

select para_value from v$dm_ini where para_name = 'FAST_COMMIT';

创建同步用户

create user DMHS identified by "DMHS_1234" default tablespace MAIN temporary tablespace TEMP;

SQL> grant resource to DMHS;

SQL> grant select any table to DMHS;

SQL> grant dba to DMHS;

SQL> grant unlimited tablespace to DMHS;

DMHS 服务部署（dm8 目的端）

6.1 部署 dmhs 服务

解压安装包

mkdir /opt/dmhs

cd dmhs

unzip dmhs_dm8_rh6_64_veri_20220301_x86_kylin10.zip

修改配置文件

cd /opt/dmhs/bin

vi /opt/dmhs/bin/dmhs.hs

dm8配置

<?xml version="1.0" encoding="GB2312"?>

<dmhs>

    <base>

        <lang>en</lang>

        <mgr_port>5345</mgr_port>

        <chk_interval>2</chk_interval>

        <ckpt_interval>45</ckpt_interval>

        <siteid>4</siteid>

        <version>2.0</version>

    </base>

    <exec>

        <recv>

            <data_port>5346</data_port>

        </recv>

        <db_type>dm8</db_type>

        <db_server>192.168.163.148</db_server>

        <db_user>DMHS</db_user>

        <db_pwd>DMHS_1234</db_pwd>

        <db_port>5236</db_port>

        <char_code>PG_UTF8</char_code>

        <db_name></db_name>

        <exec_thr>8</exec_thr>

        <exec_sql>1024</exec_sql>

        <exec_trx>2000</exec_trx>

        <exec_rows>2000</exec_rows>

        <msg_col_size>30000</msg_col_size>

        <ddl_continue>1</ddl_continue>

        <affect_row>0</affect_row>

        <exec_policy>2</exec_policy>

        <enable_rowid>0</enable_rowid>

        <clear_trx_file>1</clear_trx_file>

        <trx_max_file>8</trx_max_file>

    </exec>

</dmhs>

配置服务文件

将模板文件，复制一份为正式服务并授权

cp TemplateDmhsService DmhsService

chmod +x DmhsService

12

编辑文件

vi DmhsService

1

修改DmhsService以下内容：

#set execute environment

#REPLACE DMHS_HOME path

DMHS_HOME=/opt/dmhs/release

#REPLACE program dir

PROG_DIR=/opt/dmhs/release

#REPLACE program config path

CONF_PATH=/opt/dmhs/release/dmhs.hs

#REPLACE need library path, LD_LIBRARY_PATH/LIBPATH

NEED_LIB_PATH=

HS_NLS_LANG=""

启动同步

4.1 启动DM端

[dmdba@DM8 debug]$ ./dmhs_server ./dmhs.hs

MGR[INFO]: DMHS start up, current version: V3.1.3-Build(2021.01.08-96949trunc)_D64 (The beta)(Enterprise Edition)

MGR[WARN]: License will expire on 2021-04-08

MGR[INFO]: load config file successful,site no:4, manager port :5355, poll interval:3

MGR[INFO]: manager listening  port:5355

再开一个窗口

[dmdba@DM8 debug]$ ./dmhs_console

DMHS console tool: V3.1.3-Build(2021.01.08-96949trunc)_D64

Copyright (c) 2020, DMHS. All rights reserved.

Type ? or "help" for help, type "quit" to quit console.

DMHS> connect 127.0.0.1:5355

execute success

DMHS> start exec

execute success

ORACLE DMHS.HS配置

<?xml version="1.0" encoding="GB2312"?>

<dmhs>

                <base>

                <lang>en</lang>

         <mgr_port>5345</mgr_port>

      <ckpt_interval>60</ckpt_interval>

       <siteid>1</siteid>

  <version>2.0</version>

                </base>

                <cpt>

         <db_type>oracle11g</db_type>

         <db_server>192.168.216.147:1521/orcl1</db_server>

          <db_user>DMHS</db_user>

       <db_pwd>admin123</db_pwd>

        <ddl_mask>op:obj</ddl_mask>

            <char_code>PG_UTF8</char_code>

              <arch>

     <clear_interval>600</clear_interval>

   <clear_flag>0</clear_flag>

         </arch>

        <send>

    <ip>192.168.216.148</ip>

         <mgr_port>5345</mgr_port>

 <data_port>5346</data_port>

      <filter>

  <enable>

        <item>DMHS.*</item>

   </enable>

             <disable>

             </disable>

                  </filter>

                          <map>

                   </map>

                                </send></cpt>

</dmhs>

启动ORACLE端

[oracle@ORACLE debug]$ ./dmhs_server ./dmhs.hs

MGR[INFO]: DMHS start up, current version: V3.1.3-Build(2021.01.08-96949trunc)_D64 (The beta)(Enterprise Edition)

MGR[WARN]: License will expire on 2021-04-08

MGR[INFO]: load config file successful,site no:1, manager port :5355, poll interval:3

MGR[INFO]: manager listening  port:5355

DMHS> connect 127.0.0.1:5355

execute success

DMHS> clear exec lsn

execute success

DMHS> load 0   "sch.name='SYSDBA'" CREATE|INSERT|DICT

copy mask is : |CREATE|INSERT|TABLE|DICT|OBJID|REP

execute finish, please look up log file of exec module to check data load result

DMHS> start cpt

execute success

有报错根据报错日志查看报错内容进行排查