目录
环境准备
源端:rhel8.5 + oracle 19C
目的端:rhel8.5 + dm8
-
源端配置
-
数据库配置
1、开启归档:
SQL> shutdown immediate
SQL> alter database archivelog;
SQL> alter system set LOG_ARCHIVE_DEST_1='LOCATION=/Oradata/arch' scope=both;
SQL> alter database open;
2、创建用于同步的表空间 user 赋权等
SQL> create tablespace data_test datafile 'data_test.dbf' size 1G autoextend off;
SQL> create user ora_test identified by "Dameng88" default tablespace data_test;
SQL> grant dba to ora_test;
3、创建表、索引,并插入数据:
create table ora_test.test1
(C1 int not null,
C2 varchar2(40))
tablespace data_test
pctused 0
pctfree 10;
create unique index ora_test.idx_test1_c1 on ora_test.test1(C1);-- tablespace DATA_TEST;
ALTER TABLE ORA_TEST.TEST1
ADD CONSTRAINT TEST1_PK
PRIMARY KEY
(C1) using index ora_test.idx_test1_c1;
insert into ora_test.test1 values(1,'test0311');
commit;
4、
--检查数据库最小附加日志及全列日志:
SQL> select SUPPLEMENTAL_LOG_DATA_MIN, SUPPLEMENTAL_LOG_DATA_ALL from v$database;
--开启最小附加日志和全列日志
SQL> alter database add supplemental log data;
SQL> alter database add supplemental log data(all) columns;
修改回收站参数:重新连接生效
SQL> show parameter recyclebin
SQL> alter system set recyclebin=off deferred;
设置参数 允许DDL触发器的触发动作:
SQL> show parameter "_system_trig_enabled";
SQL> alter system set "_system_trig_enabled"=true scope=both;
现实环境如果没有DBA权限,需要做如下赋权操作(例如用户是ora_test):
grant all on dmhs_ddl_sql to ora_test;
grant select any table to ora_test;
grant select any dictionary to ora_test;--dba权限的用户也需要执行加粗部分
grant create session to ora_test;
grant lock any table to ora_test;
grant execute on dbms_flashback to ora_test;
grant flashback any table to ora_test;
配置tnsnames.ora
vim /oracle/19300/db_1/home/network/admin/tnsnames.ora
TESTORA =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.183.140)(PORT = 1526))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = testOra)
(INSTANCE_NAME = testOra)
)
安装oracle odbc包(需要安装gcc-c++)
上传unixODBC-2.3.9.tar.gz安装包(建议2.3版本以上)
[root@test1 ~]# cd /soft/
[root@test1 soft]# tar -xvf unixODBC-2.3.9.tar.gz
[root@test1 soft]# cd unixODBC-2.3.9/
[root@test1 unixODBC-2.3.9]# ./configure --prefix=/usr/local/unixODBC-2.3.9 --includedir=/usr/include --libdir=/usr/lib --bindir=/usr/bin --sysconfdir=/etc
[root@test1 unixODBC-2.3.9]# make &make install
[root@test1 soft]# odbcinst -j
[root@test1 soft]# vim /etc/odbc.ini
[ORACLE]
Description=ORACLE ODBC DSN
Driver=Oracle 19c ODBC driver
Server=192.168.183.140
Servername=TESTORA
PORT=1526
test</home/oracle>$less /etc/odbcinst.ini
[Oracle 19c ODBC driver]
Description = Oracle ODBC driver for Oracle 19
Driver = /oracle/19300/db_1/home/lib/libsqora.so.19.1
Setup =
FileUsage =
CPTimeout =
CPReuse =
连接测试
isql oracle system Dameng88 oracle -v
报错:
[root@test1 soft]# odbcinst -j
odbcinst: error while loading shared libraries: libodbcinst.so.2: cannot open shared object file: No such file or directory
解决办法:执行ldconfig在默认目录/lib和/usr/lib搜寻可共享的动态链接库
oracle odbc连接报错
报错:[01000][unixODBC][Driver Manager]Can't open lib '/oracle/19300/db_1/home/lib/libsqora.so.19.1' : file not found
[ISQL]ERROR: Could not SQLConnect
[root@test1 ~]# isql oracle system Dameng88 oracle -v
[IM004][unixODBC][Driver Manager]Driver's SQLAllocHandle on SQL_HANDLE_HENV failed
[ISQL]ERROR: Could not SQLConnect
Segmentation fault (core dumped)
解决办法:
root用户下 添加环境变量
[root@test1 ~]# vim ~/.bash_profile
export ORACLE_HOME=/oracle/19300/db_1/home
export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:$ORACLE_HOME/lib:$ORACLE_HOME/srvm/lib:/lib:/usr/lib:/usr/openwin/lib:/usr/local/lib
[root@test1 ~]# source .bash_profile
报错:ORA-12154: TNS:could not resolve the connect identifier specified
解决办法:检查tns
oracle用户下安装dmhs:
先切换到root用户下授予权限
[root@test1 soft]# chmod +x dmhs_V4.1.48_oracle12_rev102088_rh6_64_veri_20210924.bin
切换到oracle用户下:
[root@test1 opt]# su - oracle
test1</home/oracle>$cd /soft/
test1</soft>$./dmhs_V4.1.48_oracle12_rev102088_rh6_64_veri_20210924.bin -i
源端创建表&触发器 sql脚本安装目录下scripts/ddl_sql_ora.sql
根据脚本创建表触发器等
目标端DM8配置
安装dm8数据库并配置服务开机自启
开启归档:
SQL> alter database mount;
SQL> alter database archivelog;
SQL> alter database add archivelog 'TYPE=LOCAL,DEST=/dmdata/DAMENG/arch,FILE_SIZE=1024,SPACE_LIMIT=10240';
SQL> alter database open;
--开启附加日志参数
SQL> alter system set 'RLOG_APPEND_LOGIC'=1 both;
.
安装dmhs
创建表空间和用户:
create TABLESPACE data_test DATAFILE 'data_test_01.dbf' SIZE 1024 autoextend off;
create user u_test identified by "Dameng88." DEFAULT TABLESPACE DATA_TEST;
配置DMHS
源端oracle dmhs.hs
$vim /home/oracle/dmhs/bin/dmhs.hs
<?xml version="1.0" encoding="GB2312" standalone="no"?>
<dmhs>
<base>
<lang>ch</lang>
<mgr_port>5345</mgr_port>
<name>cpt</name>
<ckpt_interval>60</ckpt_interval>
<siteid>1</siteid>
<version>2.0</version>
</base>
<cpt>
<enable>1</enable>
<name>cpt</name>
<driver>{Oracle 19c ODBC driver}</driver>
<db_type>ORACLE19c</db_type>
<db_server>testOra</db_server>
<db_user>ora_test</db_user>
<db_pwd>Dameng88</db_pwd>
<db_port>1526</db_port>
<cpt_mask/>
<send>
<ip>192.168.183.130</ip>
<mgr_port>5345</mgr_port>
<data_port>5346</data_port>
<filter>
<enable>
<item>ORA_TEST.*</item>
</enable>
</filter>
<map/>
</send>
</cpt>
</dmhs>
目标端dmhs.hs
$vim /home/dmdba/dmhs/bin/dmhs.hs
<?xml version="1.0" encoding="GB2312" standalone="no"?>
<dmhs>
<base>
<lang>ch</lang>
<mgr_port>5345</mgr_port>
<name>exec</name>
<ckpt_interval>60</ckpt_interval>
<siteid>2</siteid>
<version>2.0</version>
</base>
<exec>
<recv>
<data_port>5346</data_port>
</recv>
<db_type>DM8</db_type>
<db_server>192.168.183.130</db_server>
<db_user>u_test</db_user>
<db_pwd>Dameng88.</db_pwd>
<db_port>5236</db_port>
<case_sensitive>1</case_sensitive>
<exec_thr>1</exec_thr><!-- 数据入库的工作线程数-->
<exec_policy>2</exec_policy><!-- 事务入库出错时的执行策略-->
<!--<exec_sql>512</exec_sql>--><!-- SQL内存池-->
<!--<exec_trx>5000</exec_trx>--><!--允许缓存的事务个数-->
<exec_rows>250</exec_rows><!--数据入库时允许的最大批量行数-->
<!--<trxid_tables>1</trxid_tables>
<vpool>7</vpool>
<recv_caches>8</recv_caches>-->
</exec>
</dmhs>
-
启动dmhs服务
./dmhs_server dmhs.hs
源端:
目标端:
- 目标端新开一个连接
test</home/oracle/dmhs/bin>$./dmhs_console
DMHS> connect
DMHS> start exec
start exec 执行报错:
报错解决:
[root@test2 ~]# su - dmdba
[dmdba@test2 ~]$ cd dmhs/bin
[dmdba@test2 bin]$ ldd libdmhs_exec.so
[root@test2 ~]# find / -name libdmoci.so
[dmdba@test2 ~]$ vim ~/.bash_profile
添加:
export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:/home/dmdba/dmhs/bin:/home/dmdba/dmhs/db/bin
[dmdba@test2 ~]$ source ~/.bash_profile
重试:start exec 卡住
重新拷贝一个libdmoci.so到/home/dmdba/dmhs/bin下
重新start exec 报错
yum install -y unixODBC
依然执行start exec时卡住或报错
更换libdmoci.so文件(与目标数据库版本一致的so文件):执行成功
- 源端开启一个连接
DMHS> connect
DMHS> clear exec lsn
报错:CSL[ERROR]: 初始装载失败
详细错误信息:
CPT[ERROR]: DB:ORA-12541: TNS:no listener
(code=12541)
CPT[ERROR]: OCI模块数据库登录失败,server=testOra; user=ora_test; password=******
解决:检查oracle数据库&监听是否开启,发现未开启数据库&监听 ,开启后重试
源端执行数据装载:
copy 0 "sch.name='ORA_TEST'" DICT|LSN|CREATE|INSERT|INDEX|COUNT
报错:初始装载失败
没有查询系统表和视图的权限:DB:ORA-01031: insufficient privileges
将权限赋予同步的账号查询系统基表的权限:
grant select on SYS.OBJ$ to ora_test;
grant select on sys.tab$ to ora_test;
grant select on SYS.USER$ to ora_test;
grant select on SYS.NTAB$ to ora_test;
grant select on sys.seg$ to ora_test;
源端开启同步:
DMHS> start
创建dmhs服务:
目标端dm
[root@test ~]# cd /home/dmdba/dmhs/scripts/root/
[root@test root]# ./dmhs_service_installer.sh -t dmhs_server -d /home/dmdba/dmhs/bin -p hs -x /home/dmdba/dmhs/bin/dmhs.hs
源端oracle:
[root@test1 ~]# cd /home/oracle/dmhs/scripts/root/
[root@test1 root]# ./dmhs_service_installer.sh -t dmhs_server -d /home/oracle/dmhs/bin -p hs -x /home/oracle/dmhs/bin/dmhs.hs