使用达梦DMHS平滑迁移Oracle数据到DM8

一、迁移前准备

1.环境描述

服务	IP	架构
Oracle	192.168.10.91/92	RAC、主库
Oracle	192.168.10.98	DG备库
达梦	192.168.10.192/192.168.10.193	主备
DMHS	192.168.10.193（DM端） 192.168.10.98（Oracle端）
DTS	10.254.240.96

2.部署DMHS源端及检查事项  

（1）本次测试源端和目的端数据库为GBK字符集，在Oracle端安装达梦数据实时同步软件（DMHS），编辑配置文件，注意在DMHS服务启动脚本中加上"export LANG=zh_CN.GB18030"和"export NLS_LANG=AMERICAN_AMERICA.ZHS16GBK"（字符集与Oracle数据库保持一致）

（2）Oracle源端数据库调整

1）检查Oracle是否开启归档跟保留时长
archive log list
2）Oracle开启附加日志
最小附加日志
alter database add supplemental log data;
全字段附加日志
alter database add supplemental log data(all) columns ;
检查附加日志是否开启
select database_role,supplemental_log_data_min from v$database;
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;
3）创建同步用户并授权
alter session set container=oapdb;
create user dmhs identified by dmhs default tablespace dmhs unlimited tablespace to dmhs;
grant connect,resource to dmhs;
grant select on sys.v_$instance to dmhs;
grant select on sys.v_$database to dmhs;
grant select on sys.v_$session to dmhs;
grant select on sys.v_$parameter to dmhs;
grant select on sys.gv_$parameter to dmhs;
grant select on sys.gv_$instance to dmhs;
grant select on sys.gv_$archive_dest to dmhs;
grant select on sys.gv_$archive to dmhs;
grant select on sys.gv_$log to dmhs;
grant select on sys.gv_$logfile to dmhs;
grant select on sys.dba_tables to dmhs;
grant select on sys.obj$ to dmhs;
grant select on sys.user$ to dmhs;
grant select on sys.col$ to dmhs;
grant select on sys.dba_cons_columns to dmhs;
grant select on sys.dba_constraints to dmhs;
grant select on sys.lob$ to dmhs;
grant select on sys.tabpart$ to dmhs;
grant select on sys.tab$ to dmhs;
grant select on sys.tabsubpart$ to dmhs;
grant select on sys.tabcompart$ to dmhs;
grant execute on dbms_flashback to dmhs;
grant lock any table to dmhs;
grant select any table to dmhs;
grant select any dictionary to dmhs;

3.部署DMHS目的端及检查事项

在达梦端安装在达梦数据实时同步软件（DMHS），并进行配置，注意将版本对应的dmoci依赖文件拷贝到DMHS安装目录的bin目录下，同时在DMHS服务启动脚本中加上"export NLS_LANG=AMERICAN_AMERICA.ZHS16GBK"（字符集与Oracle数据库保持一致）和"export LANG=zh_CN.GB18030"（字符集与达梦数据库保持一致）

二、创建达梦用户

1.创建达梦用户以及表空间

创建用户及赋权
create tablespace oatbs datafile 'OATBS_01.DBF' size 10240 autoextend off;
create user oa identified by "123456789" default tablespace oatbs default index tablespace oatbs;
grant public,resource,soi,vti to oa;
设置用户为只读
alter user oa read only;

三、对象迁移及处理

1.迁移步骤

（1）使用DTS创建迁移任务，分别连接Oracle和达梦数据库
（2）选择要迁移的用户OA
（3）勾选所有对象，其中表对象只迁移表结构，且约束和索引不迁移

2.禁用所有触发器

select 'alter trigger "'||owner||'"."'||trigger_name||'" disable;' from dba_triggers where owner in ('OA') and status='Y';

四、DMHS迁移数据

1.配置文件

（1）Oracle端
注：装载时要用主库IP，开启同步前要改成备库IP并重启源端dmhs

<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<dmhs><base><lang>en</lang><mgr_port>5345</mgr_port><name>ora_hs</name><ckpt_interval>60</ckpt_interval><siteid>2</siteid><version>2.0</version></base><cpt><enable>1</enable><name>ora_cpt</name><db_type>ORACLE19C</db_type><db_server>192.168.10.91/zqpdb</db_server><db_user>DMHS</db_user><db_pwd>DMHS</db_pwd><db_port>1521</db_port><char_code>PG_GB18030</char_code><parse_thr>16</parse_thr><arch><clear_flag>0</clear_flag><clear_interval>600</clear_interval></arch><rac><rac_type>2</rac_type><nodes>2</nodes><dir_replace><item>1#/archive/archivelog</item><item>2#/archive/archivelog</item></dir_replace></rac><send><ip>192.168.10.193</ip><mgr_port>5345</mgr_port><data_port>5346</data_port><filter><enable><item>ZQPDB.OA.*</item></enable></filter><map><item>ZQPDB.OA.*==OA.*</item></map></send></cpt>
</dmhs>

（2）达梦端

<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<dmhs><base><lang>en</lang><mgr_port>5345</mgr_port><name>dm_exec</name><ckpt_interval>60</ckpt_interval><siteid>1</siteid><group>1</group><version>2.0</version></base><exec><recv><data_port>5346</data_port></recv><enable>1</enable><db_type>dm8</db_type><db_server>OA</db_server><db_user>SYSDBA</db_user><db_pwd>SYSDBA</db_pwd><db_port>5236</db_port><exec_thr>64</exec_thr>	<exec_policy>2</exec_policy><exec_sql>1024</exec_sql><exec_trx>5000</exec_trx><exec_rows>2000</exec_rows><trx_split>1</trx_split><case_sensitive>1</case_sensitive></exec>
</dmhs>

2.数据装载

（1）启动达梦目的端dmhs进程，启动执行器

cd /dm/dmhs/bin
./dmhs_console
DMHS> connect
DMHS> start exec

（2）启动Oracle源端dmhs进程，准备装载

cd /oracle/dmhs/bin
./dmhs_console
DMHS> connect
DMHS> clear exec lsn
DMHS> copy 0 "sch.name='OA'" dict|truncate|insert|pdb|"ZQPDB"|thread|32|fast|100
分区表装载
DMHS> copy 0 "sch.name='OA' and tab.name in ('TABLE1')" dict|truncate|insert|pdb|"ZQPDB"|partition|bypart|thread|32|fast|100

五、索引和约束迁移

使用DTS迁移索引和约束，其中索引勾选并行，提高索引创建效率

六、更新统计信息

在达梦数据库中，使用SYSDBA用户执行以下脚本更新数据库统计信息。

drop table if exists my_tab_stats;
create table my_tab_stats(table_name varchar2(100),owner varchar2(100),stat_flag varchar2(5),begin_time timestamp,end_time timestamp);
insert into my_tab_stats(table_name,owner) select table_name,owner from dba_tables where owner in ('OA');
update my_tab_stats set stat_flag = 0;
commit;
beginfor tb in (select rowid,table_name,owner from my_tab_stats where stat_flag = 0 ) loopupdate my_tab_stats set begin_time=sysdate where my_tab_stats.table_name=tb.table_name and my_tab_stats.owner=tb.owner;dbms_stats.gather_table_stats(tb.owner,tb.table_name,null,100,true,'FOR ALL COLUMNS SIZE AUTO',cascade => true, degree => 8);update my_tab_stats set stat_flag = 1,end_time=sysdate where my_tab_stats.table_name = tb.table_name and my_tab_stats.owner=tb.owner;commit;end loop;
end;

七、开启DMHS同步

cd /oracle/dmhs/bin
./dmhs_console
DMHS> connect
DMHS> start cpt

注：开启同步前 Oracle配置文件改成DG备库ip，减小DMHS同步对Oracle主库的影响