DMHS DM8到Oracle12c PDB双向同步（大小不敏感+落地文件转发）

---

前言

上一次针对MySQL进行了大小写敏感+非落地方式的双向复制部署及简单测试，本次将以一台DM8和ORACLE 12c的PDB（大小写不敏感）进行落地文件并转发方式的双向同步来展示另一种架构，软件安装部分在另一篇已经介绍过，本篇将不再赘述，将重点放在这一架构的配置和测试以及一些故障处理分享

---

架构思路

相较于非落地方式，落地文件再转发是更为灵活的选择，在复制网络出现问题时避免由于目标端未接受而使得归档日志不能清理的入侵性。但凡事有利必有弊，落地再转发就会带来一定的时延和开销。
Oracle12c引入了CDB/PDB概念，使得其权限和表出现了分层，加上后期版本因为修复一些安全漏洞使得对sys下的表和视图需要独立的权限操作，所以在同步工具上为了尽可能维持配置的对称性，采用CDB用户连接CDB库，配合container切换和字典对pdb进行映射来工作，具体将在步骤中体现

数据库配置

DM8侧的配置在MySQL篇中已经讨论过，这里只讲Oracle 12c的部分

关闭防火墙

[root@myOracle01 ~]# systemctl stop firewalld
[root@myOracle01 ~]# systemctl disable firewalld
Removed symlink /etc/systemd/system/multi-user.target.wants/firewalld.service.
Removed symlink /etc/systemd/system/dbus-org.fedoraproject.FirewallD1.service.

确认字符集

登录PDB
[oracle@myOracle01 ~]$ sqlplus / as sysdba
SQL*Plus: Release 12.2.0.1.0 Production on Mon Apr 25 07:55:05 2022
Copyright (c) 1982, 2016, Oracle.  All rights reserved.

Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> show pdbs

    CON_ID CON_NAME			  OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
	 2 PDB$SEED			  READ ONLY  NO
	 3 PDBORCL			  READ WRITE NO

SQL> alter session set container= PDBORCL;
Session altered.

SQL> col parameter for a30
SQL> col value for a30
SQL> SELECT * FROM Nls_Database_Parameters where parameter in ('NLS_LANGUAGE','NLS_CHARACTERSET','NLS_NCHAR_CHARACTERSET');

PARAMETER		       VALUE
------------------------------ ------------------------------
NLS_NCHAR_CHARACTERSET	       AL16UTF16
NLS_CHARACTERSET	       ZHS16GBK
NLS_LANGUAGE		       AMERICAN

打开补充日志

SQL> alter database add supplemental log data(all,primary key,unique,foreign key) columns;

Database altered.

开启归档

SQL> alter system set log_archive_start=true scope=spfile;

System altered.

添加归档路径

SQL> alter system set log_archive_dest_1='location=/opt/oracle/archive' scope=spfile;

System altered.

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORACLE instance started.

Total System Global Area 1660944384 bytes
Fixed Size		    8793448 bytes
Variable Size		 1056965272 bytes
Database Buffers	  587202560 bytes
Redo Buffers		    7983104 bytes
Database mounted.
SQL>  alter database archivelog; 

Database altered.

SQL> archive log list;
Database log mode	       Archive Mode
Automatic archival	       Enabled
Archive destination	       /opt/oracle/archive
Oldest online log sequence     1
Next log sequence to archive   2
Current log sequence	       2
SQL> alter database open;

创建复制用户并赋予权限

SQL> create user c##dmhs identified by 123456789;
User created.

grant dba to c##dmhs container=all;
alter user c##dmhs quota unlimited on USERS;
grant all on dmhs_ddl_sql to c##dmhs container=all;
grant select any table to c##dmhs container=all;
grant select any dictionary to c##dmhs container=all;
grant create session to c##dmhs container=all;
grant lock any table to c##dmhs container=all;
grant execute on dbms_flashback to c##dmhs container=all;
grant flashback any table to c##dmhs container=all;

12c修复安全漏洞后，sys视图必须单独赋予权限
grant select on sys.user$ to c##dmhs container=all;
grant select on sys.obj$ to c##dmhs container=all;
grant create any trigger to c##dmhs container=all;
grant ADMINISTER DATABASE TRIGGER to c##dmhs container=all;

DDL配置
DDL复制的本质是通过DDL触发器将语句到辅助表中，上层通过对该表复制到对端进行执行来工作
在CDB/目标PDB中分别以SYS身份执行脚本ddl_ora.sql内容创建辅助表和触发器，仅对PDB执行会报错，目前原因不明，文末错误中会展示

SQL> create table dmhs_ddl_sql (
    objid          NUMBER,
    dataobj        NUMBER,
    op_type        varchar2(32),
    obj_schname    varchar2(128),
    obj_name       varchar2(128),
    obj_type       varchar2(32),
    op_sql         varchar(4000),
    op_sql2        clob,
    ddl_time       date,
    resvd1         NUMBER,
    resvd2         NUMBER,
    resvd3         NUMBER,
    resvd4         NUMBER,
    resvd5         varchar2(1000),
    resvd6         varchar2(1000)
);
Table created.

Create trigger 
create or replace trigger dmhs_trigger
before ddl
on database
declare	
   e1          exception;
   sql_text    ora_name_list_t;
   ddl_sql     clob;
   op_no       number := NULL;
   objid       number := NULL;
   dataobj     number := NULL;
   v_num			 number;
   sql_item		 varchar(8000);
   sql_temp		 varchar(8000);
begin

/*
if ora_login_user = 'DMHS' then
    return;
end if;
*/

if (SUBSTR(ora_dict_obj_name, 1, 5) = 'DMHS_' and ora_dict_obj_name<>'DMHS_TRXID_TABLE') or (ora_dict_obj_owner = 'SYS' and ora_dict_obj_type != 'TABLESPACE') then
		--下面这个条件是为了捕获dmhs_ddl_sql表的TRUNCATE操作的,这个操作不捕获会导致无法解析到该表的OP_SQL2列
		if ora_dict_obj_name = 'DMHS_DDL_SQL' and ora_dict_obj_type = 'TABLE' and ora_sysevent = 'DROP' then
      raise_application_error(-20002, 'table cannot drop before dmhs_trigger is droped');
    end if;
    
		if ora_sysevent != 'TRUNCATE' or ora_dict_obj_name <> 'DMHS_DDL_SQL' then
       return;
    end if;
end if;
/*
if ora_dict_obj_type <> 'TABLE' and  ora_dict_obj_type <> 'VIEW' and ora_dict_obj_type <> 'SEQUENCE' and ora_dict_obj_type <> 'INDEX' and ora_dict_obj_type <> 'SYNONYM' then
       return;
end if;
*/
sql_temp := '';
dbms_lob.createtemporary(ddl_sql, TRUE);
v_num := ora_sql_txt (sql_text);
for i in 1 .. v_num
loop	
	sql_item := sql_text (i);
	if length(sql_item) + length(sql_temp) > 3000 then
		dbms_lob.append(ddl_sql, sql_temp);
		sql_temp := '';
	end if;
	sql_temp := sql_temp || sql_item;
end loop; 
dbms_lob.append(ddl_sql, sql_temp);
if ora_sysevent != 'CREATE' then
  if ora_dict_obj_type = 'TABLE' or ora_dict_obj_type = 'SEQUENCE' then
    select o.obj#, o.dataobj# into objid, dataobj from sys.obj$ o, sys.user$ u where o.name = ora_dict_obj_name and o.type# in(2, 6) and o.subname is null and u.name = ora_dict_obj_owner and o.owner# = u.user#;
  end if;
 
  insert into dmhs_ddl_sql(objid, dataobj, op_type, obj_schname, obj_name, obj_type, op_sql, ddl_time) values(objid, dataobj, ora_sysevent, ora_dict_obj_owner, ora_dict_obj_name, ora_dict_obj_type, SUBSTR(ddl_sql, 1, 2000), sysdate);
else
  if ora_dict_obj_type = 'TABLE' then
    insert into dmhs_ddl_sql(op_type, obj_schname, obj_name, obj_type, op_sql, op_sql2, ddl_time) values(ora_sysevent, ora_dict_obj_owner, ora_dict_obj_name, ora_dict_obj_type, SUBSTR(ddl_sql, 1, 2000), ddl_sql, sysdate); 
  elsif ora_dict_obj_type in ('VIEW', 'MATERIALIZED VIEW', 'TRIGGER', 'PROCEDURE', 'FUNCTION', 'PACKAGE', 'PACKAGE BODY', 'TYPE', 'TYPE BODY', 'SYNONYM', 'TABLESPACE', 'USER', 'ROLE', 'INDEX', 'SEQUENCE') then
    insert into dmhs_ddl_sql(op_type, obj_schname, obj_name, obj_type, op_sql, op_sql2, ddl_time) values(ora_sysevent, ora_dict_obj_owner, ora_dict_obj_name, ora_dict_obj_type, SUBSTR(ddl_sql, 1, 2000), ddl_sql, sysdate); 
	end if;
end if;  
exception
    when e1 then raise e1;
    when no_data_found then
        dbms_output.put_line('object is not exist');
    when others then
        raise_application_error (-20002,'An error has occurred on ddl sql.');
end dmhs_trigger;

SP2-0814: Trigger created with compilation warnings

12c PL/SQL未绑定错误号的exception会warning，但不影响使用

12c之前需要关闭回收站，12c之后不用

SQL> alter system set recyclebin=off deferred;

System altered;

ODBC配置

[root@tpcc Packages]# rpm -ivh unixODBC-2.3.1-11.el7.x86_64.rpm
警告：unixODBC-2.3.1-11.el7.x86_64.rpm: 头V3 RSA/SHA256 Signature, 密钥 ID fd431d51: NOKEY
准备中...                          ################################# [100%]
正在升级/安装...
   1:unixODBC-2.3.1-11.el7            ################################# [100%]
   
[root@tpcc Packages]# rpm -ivh unixODBC-devel-2.3.1-11.el7.x86_64.rpm
警告：unixODBC-devel-2.3.1-11.el7.x86_64.rpm: 头V3 RSA/SHA256 Signature, 密钥 ID fd431d51: NOKEY
准备中...                          ################################# [100%]
正在升级/安装...
   1:unixODBC-devel-2.3.1-11.el7      ################################# [100%]

找到版本对应的instance client odbc

上传并解压介质

[root@myOracle01 /]# ls -la /opt/dmhs/setup/
总用量 496056
drwxr-xr-x. 2 dmhs dinstall       123 4月  25 10:39 .
drwxr-xr-x. 6 dmhs dinstall        55 4月  25 10:30 ..
-rwxr-xr-x. 1 dmhs oinstall 507325262 4月  25 07:47 dmhs_V4.2.60_oracle12_rev106302_rh6_64_veri_20220225.bin
-rw-r--r--. 1 root root        634023 4月  25 10:39 instantclient-odbc-linux.x64-12.2.0.1.0-2.zip

[root@myOracle01 setup]# unzip instantclient-odbc-linux.x64-12.2.0.1.0-2.zip
[root@myOracle01 instantclient_12_2]# mkdir -p /opt/dmhs/odbc

安装ODBC

如果有明确的unixODBC安装路径可以指定，如果没有绕过脚本逻辑让其生成即可，具体看脚本逻辑即可
odbc_update_ini.sh

odbc.ini
[OracleODBC-12c]
Application Attributes = T
Attributes = W
BatchAutocommitMode = IfAllSuccessful
BindAsFLOAT = F
CloseCursor = F
DisableDPM = F
DisableMTS = T
Driver = Oracle 12c ODBC driver
DSN = OracleODBC-12c
EXECSchemaOpt =
EXECSyntax = T
Failover = T
FailoverDelay = 10
FailoverRetryCount = 10
FetchBufferSize = 64000
ForceWCHAR = F
Lobs = T
Longs = T
MaxLargeData = 0
MetadataIdDefault = F
QueryTimeout = T
ResultSets = T
ServerName = orcl
SQLGetData extensions = F
Translation DLL =
Translation Option = 0
DisableRULEHint = T
UserID =
StatementCache=F
CacheBufferSize=20
UseOCIDescribeAny=F
SQLTranslateErrors=F
MaxTokenSize=8192
AggregateSQLType=FLOAT

修改权限

[root@myOracle01 instantclient_12_2]# chown -R dmhs.oinstall /opt/dmhs/odbc/

odbcinst.ini

client依赖

测试DSN

DMHS配置

配置dmhs_trans_ora.hs

<?xml version="1.0" encoding="UTF8"?>
<dmhs>
 <base>
 <lang>en</lang>    //程序输出固定GBK，可以从console适应，但en更通用
 <mgr_port>11345</mgr_port>
 <ckpt_interval>60</ckpt_interval>
 <siteid>2</siteid>
 <version>2.0</version>
 </base>
 <transfer>
 <name>transfer</name>
 <enable>1</enable>
 <recv>
 <data_file>
 <path>/opt/dmhs/data</path>
 <src_siteid>1</src_siteid>            //负责转发ORACLE测到对端
 </data_file>
 </recv>
 <send><!-- 发送模块配置 -->
 <ip>192.168.56.7</ip>
 <mgr_port>10345</mgr_port>
 <data_port>10346</data_port>
 <net_turns>0</net_turns>
 </send>
 </transfer>
</dmhs>

启动dmhs_server

[dmhs@myOracle01 bin]$ dmhs_server /opt/dmhs/inst1/dmhs_trans_ora.hs
MGR[INFO]: DMHS start up, current version: V4.2.60-Build(2022.02.24-106302trunc)_D64 (The beta)(Enterprise Edition)
MGR[WARN]: License will expire in 18 day(s) on 2022-05-24
MGR[INFO]: load config file successful,site no:2, manager port :11345, poll interval:3
MGR[INFO]: manager listening  port:11345
REV[INFO]: listen path: /opt/dmhs/data
REV[INFO]: Brocast thread createing, file path: /opt/dmhs/data
REV1[INFO]: are synchronized directory: /opt/dmhs/data
MGR[INFO]: init the analysis module...
MGR[WARN]: site not config analysis module ! 

配置dmhs_trans_dm8.hs

<?xml version="2.0" encoding="UTF8"?>
<dmhs>
 <base>
 <lang>en</lang>
 <mgr_port>11345</mgr_port>
 <ckpt_interval>60</ckpt_interval>
 <siteid>3</siteid>
 </base>
 <transfer>
 <recv>
 <data_file>
 <path>/opt/dmhs/data</path>
 <switch_interval>10</switch_interval>
 <size>64</size>
 <src_siteid>4</src_siteid>                 //负责转发DM8侧到对端
 </data_file>
 </recv>
 <send><!-- 发送模块配置 -->
 <ip>192.168.56.199</ip>
 <mgr_port>9345</mgr_port>
 <data_port>9346</data_port>
 <net_turns>0</net_turns>
 </send>
</transfer>
</dmhs>

启动dmhs_server

[dmhs@dmdsc0 bin]$ dmhs_server /opt/dmhs/inst1/dmhs_trans_dm8.hs 
MGR[INFO]: DMHS start up, current version: V4.2.60-Build(2022.02.24-106302trunc)_D64 (The beta)(Enterprise Edition)
MGR[WARN]: License will expire in 18 day(s) on 2022-05-24
MGR[INFO]: load config file successful,site no:3, manager port :11345, poll interval:3
MGR[INFO]: manager listening  port:11345
REV[INFO]: listen path: /opt/dmhs/data
REV[INFO]: Brocast thread createing, file path: /opt/dmhs/data
REV1[INFO]: are synchronized directory: /opt/dmhs/data
MGR[INFO]: init the analysis module...
MGR[WARN]: site not config analysis module ! 

配置dmhs_bi_ora.hs

配置捕获并发送到本地文件，与ogg exttrail相同

<?xml version="1.0" encoding="UTF8"?>
<dmhs>
 <base>
 <lang>en</lang>    //程序输出固定GBK，可以从console适应，但en更通用
 <mgr_port>9345</mgr_port>
 <ckpt_interval>60</ckpt_interval>
 <siteid>1</siteid>
 </base>
 <recv>
 <data_port>9346</data_port>
 <exec>
 <db_type>ORACLE12c</db_type>
 <db_server>orcl</db_server>
 <db_name>pdborcl</db_name>
 <db_user>c##dmhs</db_user>
 <db_pwd>123456789</db_pwd>
 <db_port>1521</db_port>
 <db_ssl_path></db_ssl_path>
 <db_ssl_pwd></db_ssl_pwd>
 <driver>Oracle 12c ODBC driver</driver>
 <char_code>PG_UTF8</char_code>
 <exec_mode>1</exec_mode>
 <exec_thr>1</exec_thr>
 <exec_sql>32</exec_sql>
 <exec_trx>1000</exec_trx>
 <exec_rows>20</exec_rows>
 <commit_policy>0</commit_policy>
 <exec_policy>2</exec_policy>
 <msg_col_size>8000</msg_col_size>
 </exec>
 </recv>
 <cpt>
 <db_type>ORACLE12c</db_type>
 <db_server>orcl</db_server>
 <db_user>c##dmhs</db_user>
 <db_pwd>123456789</db_pwd>
 <db_port>1521</db_port>
 <db_ssl_path></db_ssl_path>
 <db_ssl_pwd></db_ssl_pwd>
 <driver>Oracle 12c ODBC driver</driver>
 <parse_thr>1</parse_thr>
 <ddl_mask>obj:op</ddl_mask>
 <arch>
 <clear_interval>600</clear_interval>
 <clear_flag>0</clear_flag>
 </arch>
 <send>
 <data_file>
 <path>/opt/dmhs/data</path>
 <src_siteid>1</src_siteid>
 <switch_interval>10</switch_interval>
 <size>64</size>
 </data_file>
 <filter>
 <enable>
     <item>PDBORCL.DMHS.*</item>
 </enable>
 </filter>
 <map>
     <item>DMHS.*==DMHS.*</item>
 </map>
 </send>
 <send_delay_second>0</send_delay_second>
 <start_scn>0</start_scn>
 <supplement_log>1</supplement_log>
 <rec_heap_size>16</rec_heap_size>
 <vpool_size>8</vpool_size>
 <enable_mview>0</enable_mview>
 <send_lst>2</send_lst>
 </cpt>
</dmhs>

启动dmhs_server

[dmhs@myOracle01 bin]$ dmhs_server /opt/dmhs/inst1/dmhs_bi_ora.hs 
MGR[INFO]: DMHS start up, current version: V4.2.60-Build(2022.02.24-106302trunc)_D64 (The beta)(Enterprise Edition)
MGR[WARN]: License will expire in 18 day(s) on 2022-05-24
MGR[INFO]: load config file successful,site no:1, manager port :9345, poll interval:3
MGR[INFO]: manager listening  port:9345
PUB[WARN]: NLS_LANG user env:AMERICAN_AMERICA.ZHS16GBK conflicts with char_code:PG_UTF8, set NLS_LANG to AMERICAN_AMERICA.AL32UTF8
MGR[INFO]: loading the execute module...
EXE[INFO]: CONNECT: SERVER=orcl;DRIVER=Oracle 12c ODBC driver;UID=c##dmhs;PWD=******;
EXE[INFO]: EXEC_V3.1.2_D64
EXE[WARN]: Purpose for ORACLE database, need to adjust the execution strategy for random submit, no and perform the work thread deadlock may occur
REV[INFO]: exec server data receiving thread created successfully, listening data port : 9346

启动EXEC

[dmhs@dmdsc0 ~]$ dmhs_console /opt/dmhs/inst1/dmhs_bi_ora.hs 
DMHS console tool: V4.2.60-Build(2022.02.24-106302trunc)_D64
Copyright (c) 2020, DMHS. All rights reserved.
Type ? or "help" for help, type "quit" to quit console.

DMHS> connect 127.0.0.1:9345
execute success

DMHS> start exec
execute success

配置dmhs_bi_dm8.hs

<?xml version="1.0" encoding="UTF8"?>
<dmhs>
 <base>
 <lang>en</lang>
 <mgr_port>10345</mgr_port>
 <ckpt_interval>60</ckpt_interval>
 <siteid>4</siteid>
 </base>
 <recv>
 <data_port>10346</data_port>
 <level>0</level>
 <exec>
 <db_type>DM8</db_type>
 <db_server>127.0.0.1</db_server>
 <db_user>DMHS</db_user>
 <db_pwd>123456789</db_pwd>
 <db_port>7236</db_port>
 <char_code>PG_GB18030</char_code>
 <exec_mode>1</exec_mode>
 <exec_thr>1</exec_thr>
 <exec_sql>32</exec_sql>
 <exec_trx>1000</exec_trx>
 <exec_rows>20</exec_rows>
 <commit_policy>0</commit_policy>
 <exec_policy>2</exec_policy>
 <msg_col_size>8000</msg_col_size>
 </exec>
 </recv>
 <cpt>
 <db_type>dm8</db_type>
 <db_server>127.0.0.1</db_server>
 <db_user>DMHS</db_user>
 <db_pwd>123456789</db_pwd>
 <db_port>7236</db_port>
 <db_ssl_path></db_ssl_path>
 <db_ssl_pwd></db_ssl_pwd>
 <parse_thr>1</parse_thr>
 <ddl_mask>op:obj</ddl_mask> <!--DDL 配置项-->
 <arch>
 <clear_interval>600</clear_interval>
 <clear_flag>0</clear_flag>
 </arch>
 <send>
 <data_file>
 <path>/opt/dmhs/data</path>
 <src_siteid>1</src_siteid>
 <switch_interval>10</switch_interval>
 <size>64</size>
 </data_file>
 <filter>
 <enable>
 <item>DMHS.*</item>
 </enable>
 </filter>
 <map><!--映射配置项-->
 <item>DMHS.*==PDBORCL.DMHS.*</item>
 </map>
 </send>
 </cpt>
</dmhs>

启动dmhs_server

[dmhs@dmdsc0 bin]$ dmhs_server /opt/dmhs/inst1/dmhs_bi_dm8.hs
MGR[INFO]: DMHS start up, current version: V4.2.60-Build(2022.02.24-106302trunc)_D64 (The beta)(Enterprise Edition)
MGR[WARN]: License will expire in 18 day(s) on 2022-05-24
MGR[INFO]: load config file successful,site no:4, manager port :10345, poll interval:3
MGR[INFO]: manager listening  port:10345
MGR[INFO]: loading the execute module...
EXE[INFO]: CONNECT: SERVER=127.0.0.1;DRIVER=DM8 ODBC DRIVER;UID=DMHS;PWD=******;TCP_PORT=7236;
EXE[INFO]: DM8 SAVEPOINT_LIMIT of dm.ini is 512, so SAVEPOINT optimization is enabled.
EXE[INFO]: EXEC_V3.1.2_D64
REV[INFO]: exec server data receiving thread created successfully, listening data port : 10346

启动EXEC

[dmhs@dmdsc0 ~]$ dmhs_console /opt/dmhs/inst1/dmhs_bi_dm8.hs 
DMHS console tool: V4.2.60-Build(2022.02.24-106302trunc)_D64
Copyright (c) 2020, DMHS. All rights reserved.
Type ? or "help" for help, type "quit" to quit console.

DMHS> connect 127.0.0.1:10345
execute success

DMHS> start exec
execute success

关于ORACLE RAC/DM8 DSC的场景

使用RAC且redo放在ASM上时，在CPT模块中配置连接到ASM的信息

<rac>                                    // rac标签针对于ORACLE RAC
            <rac_type>1</rac_type>             // 1表示类型为ASM
            <db_server>ASM</db_server>          // 连接ASM的服务名  也可以写成asm_server
            <db_user>SYS</db_user>              //连接ASM的用户  也可以写成asm_user
            <db_pwd>oracle</db_pwd>         //连接ASM的密码  也可以写成asm_pwd
            <nodes>2</nodes>                     //活动节点数
            <epoch>0</epoch>                    //禁用时间戳缓存排序功能，改为通过SCN排序，避免多节点时间戳不一致导致顺序错乱
</rac>

使用RAC且redo放在共享磁盘上时，在CPT模块中配置所有节点日志的路径

<rac>
            <rac_type>2</rac_type>                     // 2表示类型为文件系统
            <db_server>ASM</db_server>
            <db_user>SYS</db_user>
            <db_pwd>oracle</db_pwd>
            <nodes>2</nodes>
            <epoch>0</epoch>
            <dir_replace>
                <item>1#/u01/app/oracle/arch1</item>                 //线程1 对应归档本地挂载路径4
                <item>2#/u01/app/oracle/arch2</item>               //线程2 对应归档本地挂载路径4
            </dir_replace>
</rac>

使用DSC时，无论redo放在ASM还是磁盘上时，都需要CPT模块中配置连接到ASM的信息及映射路径

<dm8_rac>
            <rac_type>1</rac_type>
            <db_server>ASM</db_server>
            <db_user>SYS</db_user>
            <db_pwd>dm</db_pwd>
            <nodes>2</nodes>
            <epoch>60</epoch>
            <dir_replace>
                <item>1#/data/dmdata/arch1</item>
                <item>2#/data/dmdata/arch2</item>
            </dir_replace>
</dm8_rac>

创建离线字典

两侧都要装载
ORACLE侧

DMHS> COPY 0 "sch.name='DMHS'" DICT|pdb|"PDBORCL"

copy mask is : |DICT|PARTITION|REP
execute finish, please look up log file of exec module to check data load result

DM8侧

DMHS> COPY 0 "sch.name='DMHS'" DICT|pdb|"PDBORCL"
copy mask is : |DICT|PARTITION|REP
execute finish, please look up log file of exec module to check data load result

初始数据装载

从数据基准测装载即可，有必要可以分表进行，可以参考MySQL篇

DMHS> COPY 0 "sch.name='DMHS'" LSN|CREATE|INSERT|BLOB|INDEX|THREAD|2| pdb|"PDBORCL"
CSL[WARN]: Detect the LSN mask, the mask will be ignored in the log is less than the current LSN all operations, please confirm whether to continue?(Y/N)
Y
copy mask is : |CREATE|INSERT|BLOB|THREAD|INDEX|TABLE|LSN|PARTITION|OBJID|REP
execute finish, please look up log file of exec module to check data load result

该架构操作时所有涉及流量的服务都会产生日志和文件，大体可以参考示例输出
CPT

TRANSFER

EXEC

数据比对

此时可以通过外部工具进行数据校验和比对工作，这里数据很少可以看到已经同步过来

启动捕获

ORACLE

DM8

交叉测试

初始状态

DM8

[dmdba@dmdsc0 bin]$ ./disql dmhs/123456789@192.168.56.7:7236

服务器[192.168.56.7:7236]:处于普通打开状态
登录使用时间 : 1.216(ms)
disql V8
SQL> select * from test;
select * from test;
第1 行附近出现错误[-2106]:无效的表或视图名[test].
已用时间: 0.457(毫秒). 执行号:0.
SQL>

ORACLE

[oracle@myOracle01 ~]$ sqlplus dmhs/123456789@pdborcl          

SQL*Plus: Release 12.2.0.1.0 Production on Thu May 5 18:04:35 2022

Copyright (c) 1982, 2016, Oracle.  All rights reserved.

Last Successful login time: Thu May 05 2022 18:03:02 +08:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> select * from test;
select * from test
              *
ERROR at line 1:
ORA-00942: table or view does not exist

建表

ORACLE建表

SQL> create table dmhs.test(id int,name varchar(100),dt timestamp(6));

Table created.

DM8验证

SQL> desc test;

行号     name type$        nullable
---------- ---- ------------ --------
1          ID   NUMBER       Y
2          NAME VARCHAR(100) Y
3          DT   DATETIME(6)  Y

插入数据

DM8插入

SQL>  insert into dmhs.test values(0,'kabasdf',now());
影响行数 1

已用时间: 0.640(毫秒). 执行号:3102.
SQL> commit;
操作已执行
已用时间: 0.673(毫秒). 执行号:3103.

ORACLE验证

SQL> select * from dmhs.test;

	ID NAME 		DT
---------- -------------------- ------------------------------
	 0 kabasdf		06-MAY-22 10.43.54.814708 AM

修改数据1

ORACLE修改

SQL> update dmhs.test set name='superman' where id=0;

1 row updated.

SQL> commit;
Commit complete.

DM8验证

SQL> select * from DMHS.test;

行号     ID NAME     DT                        
---------- -- -------- --------------------------
1          0  superman 2022-05-06 10:43:54.814708

已用时间: 0.616(毫秒). 执行号:5605.

修改数据2

DM8修改

SQL> update DMHS.test set name='burgerman' where id=0;
影响行数 1

已用时间: 0.887(毫秒). 执行号:5606.
SQL> commit;
操作已执行.

ORACLE验证

SQL> select * from dmhs.test;

	ID NAME 		DT
---------- -------------------- ------------------------------
	 0 burgerman		06-MAY-22 10.43.54.814708 AM

添加索引

ORACLE添加索引

SQL> create index test_idx on dmhs.test(name);

Index created.

DM8验证

SQL> select INDEX_NAME  from dba_indexes where table_name='TEST' and owner='DMHS' and INDEX_TYPE<>'CLUSTER';

行号     INDEX_NAME
---------- ----------
1          TEST_IDX

已用时间: 66.890(毫秒). 执行号:5608

添加约束

DM8添加

SQL> alter table dmhs.test add constraint cons_pk primary key(id);
操作已执行
已用时间: 33.638(毫秒). 执行号:5609.

ORALCE验证

SQL> select CONSTRAINT_NAME from user_constraints where table_name = 'TEST' and constraint_type ='P'; 

CONSTRAINT_NAME
--------------------------------------------------------------------------------
cons_pk 

删除表

ORACLE删除

SQL> drop table dmhs.test;

Table dropped.

DM8验证

SQL> select * from DMHS.test;
select * from DMHS.test;
第1 行附近出现错误[-2106]:无效的表或视图名[test].
已用时间: 0.309(毫秒). 执行号:0.

服务注册

测试全部正常后可以注册为服务交由系统托管，流程可以参考MySQL篇相同

常见问题

权限不足

12c在修复安全问题之后的版本中，所有sys表需要单独赋予权限
例如
grant select on sys.user$ to c##dmhs container=all;

缺少ODBC库

ODBC作为标准接口，下层驱动只提供实现，上层引用接口库位于unixODBC中，安装unixODBC即可

补全日志

早期是用于stream/standby/logmnr/ogg等复制功能使用，由于默认只记录rowid和操作信息，出于本地恢复使用，而对于物理位置不同的逻辑复制是没有意义的，那么此时就可以通过补全日志来将主键/唯一键或所有列组合起来，与被修改的列一起放入日志中，精确匹配到复制端，如果未正确开启补全日志则会看到下面的错误，相应开启即可，开启后应当尽量避免所有列被放入的场景，尤其是包含lob列

无法定位DDL

错误的直接原因是由于日志记录存在DDL操作的object实际上在DMHS_DDL_SQL中并没有相应的记录，一般是由于调试阶段产生的错误DDL信息于DMHS_DDL_SQL中被清空导致，调试阶段将日志起始点前拉即可，实际环境中则要根据具体事务看跳过哪些
DMHS> set exec lsn 0
execute success

DDL重复错误

对于DMHS_DDL_SQL表内容的检索，并不仅限于dmhs.DMHS_DDL_SQL，而是该PDB中所有名为DMHS_DDL_SQL的表，所以如果同时存在sys.DMHS_DDL_SQL和dmhs.DMHS_DDL_SQL，则会出现下面的错误，删除一个即可

DDL TRIGGER检测错误

测试中如果仅在目标pdb下建立DDL TRIGGER会报出如下错误，但是在CDB也建立后则不会有问题，目前原因待查，由于是两个层面，所以建立并不会导致DDL重复的问题

字段类型不兼容

某些字段类型或者缩写在默认MAP中无法对应，此时需要自行修改DMHS_DTYPE_MAP

不加载DICT

在某次启动时，发现始终检测不到字典，尝试重新初始化字典也同样报错

跟踪其堆栈发现其打开DDL这个目录后便不再加载信息，直接抛出错误了

问题的成因尚不明确，该目录实际上是一个空目录，将其删除重新初始化字典后恢复正常

---

总结

本次通过DMHS搭建DM8到Oracle PDB双向同步进行了另一种落地架构的可行性验证及功能测试，相较于此前的非落地架构，该架构具备了更多的灵活性，对于源端日志进行了一定程度的解耦，也能够适应网闸和应对一些需要转发的特殊场景，但其性能也会相应有所降低，对于实时性要求不高而更注重其他方面的方案具备一定的意义。

达梦云适配技术社区
https://eco.dameng.com/