Oracle到DM的DMHS安装部署(详细版)
Oracle到DM8的数据同步(Linux)
一、准备工作
1.部署规划
数据库规划:
IP地址 | 数据库 | 安装目录 | 端口号 | 机器 |
---|---|---|---|---|
192.168.11.145 | Oracle | /home/oracle | 1521 | LH05 |
192.168.11.143 | DM8 | /home/dmdba/dm8 | 5236 | LH03 |
服务端口规划
服务 | IP地址 | 安装目录 | 端口号 |
---|---|---|---|
DMHS源端 | 192.168.11.145 | /home/oracle/dmhs/release | 5345 |
DMHS目的端 | 192.168.11.143 | /home/dmdba/dmhs/release | 5345 |
2.安装Oracle
可以参考我之前的博文:基于Linux的Oracle安装部署
3.安装DM8
可以参考我之前的博文:DM数据库安装及启动教程(Linux超详细版)
二、目的端准备(DM8)
1.开启归档日志
连接数据库
./disql SYSDBA/SYSDBA
查询归档是否开启
SQL> select arch_mode from v$database;
开启归档
alter database mount;
alter database archivelog;
alter database add archivelog ‘TYPE=LOCAL,DEST=/home/dmdba/dm8/dmdata/DAMENG/arch,SPACE_LIMIT=10240’;
alter database open;
再次查询归档
select arch_mode from v$database;
2.开启逻辑日志
执行数据库后重启数据库服务生效
SQL> SP_SET_PARA_VALUE(2,’RLOG_APPEND_LOGIC’,1);
SQL> exit;
DmServiceDMSERVER restart;
查询逻辑日志是否开启
SQL> SELECT PARA_VALUE FROM SYS.V$DM_INI WHERE PARA_NAME=’RLOG_APPEND_LOGIC’;
dm.ini 配置参数中“FAST_COMMIT”必须为 0,否则会导致逻辑日志不全而影响同步。
SQL> select para_value from v$dm_ini where para_name = 'FAST_COMMIT';
输出为 0 则正常。
3.创建同步用户
SQL> 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;
三、源端准备(Oracle)
1.开启归档日志
切换到oracle用户
创建目录
mkdir /opt/oracle/oradata/orcl/archlog
连接数据库
root登录到linux
su – oracle 切换oracle用户下
lsnrctl start 先启动监听
sqlplus /nolog 进入Sqlplus控制台
connect / as sysdba 以系统管理员登录
startup 启动数据库
检查服务状态
lsnrctl status 监听状态
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='/opt/oracle/oradata/orcl/archlog';
# 恢复为 open 状态
SQL> alter database open;
# 再次检查归档
SQL> archive log list;
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 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.关闭Oracle回收机制
检查 oracle 回收机制 on/off
SQL> show parameter recyclebin;
# 如果是on,需要关闭oracle回收机制
SQL> alter system set recyclebin=off deferred;
需要重启数据库后才生效。
4.检查字符集是否一致
先查询 oracle 数据库的字符集:(AMERICAN_AMERICA.ZHS16GBK)
SQL> select userenv('language') from dual;
再查询系统字符集
echo $NLS_LANG
# 如果该变量值为空或者与查询结果不一致,则将该变量设置为查询结果的值
vi ~/.bash_profile
# 增加 export NLS_LANG=”sql查询结果”
source ~/.bash_profile
5.创建同步用户及授权
启动 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;
6.root用户安装odbc
yum -y install unixODBC
配置 unixODBC
新增 odbc.ini 文件
vim /etc/odbc.ini
[ORACLE]
Description = Oracle ODBC driver for Oracle 11g
Driver= Oracle in OraDb11g_home1
SERVER = 192.168.11.145
ServerName = orcl
UserID = DMHS
Password = DMHS
Port = 1521
配置 odbcinst.ini 文件
vim /etc/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
查看是否缺少依赖:
ldd /opt/oracle/oracle/product/11.2.0/dbhome_1/lib/libsqora.so.11.1
缺少依赖
查找系统其他位置是否存在依赖文件:
find / -name libodbcinst*
复制相应文件到依赖路径下:
cp /usr/lib64/libodbcinst.so.2 /lib64/libodbcinst.so.1
再次查看发现所有依赖都可被找到。
配置完成之后,可使用 isql 命令测试配置是否正确。
注意要用 oralce 用户测试。
isql -v ORACLE DMHS DMHS
四、DMHS 服务部署(dm8 目的端)
DMHS(dm8)压缩包百度网盘链接:
链接:https://pan.baidu.com/s/1gnSPQOgB8RcqCsGtSOm8eg?pwd=m109
提取码:m109
解压安装包
mkdir /home/dmdba/dmhs
cd dmhs
unzip dmhs_dm8_rh6_64_veri_20220301_x86_kylin10.zip
修改配置文件,上传release文件夹,主要改IP地址
cd /home/dmdba/dmhs/release/
vi /home/dmdba/dmhs/release/dmhs.hs
<?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.11.143</db_server>
<db_user>SYSDBA</db_user>
<db_pwd>SYSDBA</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
修改DmhsService以下内容:
#set execute environment
#REPLACE DMHS_HOME path
DMHS_HOME=/home/dmdba/dmhs/release
#REPLACE program dir
PROG_DIR=/home/dmdba/dmhs/release
#REPLACE program config path
CONF_PATH=/home/dmdba/dmhs/release/dmhs.hs
#REPLACE need library path, LD_LIBRARY_PATH/LIBPATH
NEED_LIB_PATH=
HS_NLS_LANG="AMERICAN_AMERICA.ZHS16GBK"
6.3 启动服务
dmdba 用户到 /home/dmdba/dmhs/release 目录下执行以下命令:
./DmhsService start
./dmhs_console
#启动 exec 模块
DMHS> start exec
黄色部分警告要上传key文件到./dmhs/release下
查看日志发现错误是:缺少依赖包
缺少依赖
找到依赖包拷贝到release文件
执行完成后,可以在 /opt/dmhs/release/log 目录下查看日志文件:
cd /home/dmdba/dmhs/release/log
tail -f dmhs_202207.log
五、DMHS 服务部署(Oracle11g 源端)
安装包链接:https://pan.baidu.com/s/1p6IpEIZdJc-Sx3-SUNOKIg?pwd=zl1z
提取码:zl1z
root 用户执行以下操作:
解压安装包
mkdir /home/oracle/dmhs
unzip dmhs_oracle_rh6_64_veri_20220301_x86_rh7.zip
修改配置文件
vi /home/oracle/dmhs/release/dmhs.hs
文件内容:(注意修改俩 ip 地址)
<?xml version="1.0" encoding="GB2312"?>
<dmhs>
<base>
<lang>en</lang>
<mgr_port>5345</mgr_port>
<ckpt_interval>45</ckpt_interval>
<siteid>1</siteid>
<version>2.0</version>
</base>
<cpt>
<db_type>oracle11g</db_type>
<db_server>192.168.11.145:1521/orcl</db_server>
<db_user>DMHS</db_user>
<db_pwd>DMHS</db_pwd>
<ddl_mask>op:obj</ddl_mask>
<char_code>PG_UTF8</char_code>
<constraint>1</constraint>
<arch>
<clear_interval>600</clear_interval>
<clear_flag>0</clear_flag>
</arch>
<send>
<ip>192.168.11.143</ip>
<mgr_port>5345</mgr_port>
<data_port>5346</data_port>
<filter>
<enable>
<item>DMHS.*</item>
</enable>
<disable>
</disable>
</filter>
<map>
<item>DMHS.*==DMHS.*</item>
</map>
</send>
</cpt>
</dmhs>
将模板文件,复制一份为正式服务并授权
cp TemplateDmhsService DmhsService
chmod +x DmhsService
编辑文件
vi DmhsService
修改DmhsService以下内容:
#set execute environment
#REPLACE DMHS_HOME path
DMHS_HOME=/home/oracle/dmhs/release
#REPLACE program dir
PROG_DIR=/home/oracle/dmhs/release
#REPLACE program config path
CONF_PATH=/home/oracle/dmhs/release/dmhs.hs
#REPLACE need library path, LD_LIBRARY_PATH/LIBPATH
NEED_LIB_PATH=
HS_NLS_LANG="AMERICAN_AMERICA.ZHS16GBK"
启动服务
oracle 用户到 ./dmhs/release 目录下执行以下命令:
./DmhsService start
./dmhs_console
DMHS> clear exec lsn
DMHS> COPY 0 "SCH.NAME='ORCL'" CREATE
DMHS> COPY 0 "SCH.NAME='ORCL'" INSERT|THREAD|2
DMHS> start cpt
验证同步效果
登录 DM 检查表是否存在
使用 dmdba 用户登录DM数据库,确认数据存在
源端:
目的端:
六、部署过程中问题的解决办法
问题1:MEMORY_TARGET not supported on this system
报错如下:
SQL> startup
ORA-00845: MEMORY_TARGET not supported on this system
alert 日志中的信息类似如下:
WARNING: You are trying to use the MEMORY_TARGET feature. This feature requires the /dev/shm file system to be mounted for at least 53687091200 bytes. /dev/shm is either not mounted or is mounted with available space less than this size. Please fix this so that MEMORY_TARGET can work as expected. Current available is 33619214336 and used is 26345472 bytes. Ensure that the mount point is /dev/shm for this directory.
memory_target needs larger /dev/shm
查看关于错误的描述 很多时候, Oracle 错误代码后面的提示信息会给我们充足的信息。但是有些时候的信息也会让我们有点儿懵圈。比如本次我们需要了解的错误。 按照英文的意思,就是当前操作系统不支持使用memory_target参数。但是我们知道, 在当前Linux操作系统中,这个参数是支持的。所以肯定是有其他原因。关于ORACLE错误 更详细的信息,可以使用oerr ora errcode 。下面是本次相关错误的详细信息提示:
[user@hostname ~]$ oerr ora 845
00845, 00000, "MEMORY_TARGET not supported on this system"
// *Cause: The MEMORY_TARGET parameter was not supported on this operating system or /dev/shm was not sized correctly on Linux.
// *Action: Refer to documentation for a list of supported operating systems. Or, size /dev/shm to be at least the SGA_MAX_SIZE on each Oracle instance running
根据提示Cause:/dev/shm was not sized correctly on Linux. 我们知道了此错误的原因,原来是因为/dev/shm 的大小有问题。 而根据提示Action: size /dev/shm to be at least the SGA_MAX_SIZE…. 这里我们知道了。/dev/shm 的大小至少应该大于 sga_max_size的值。
解决办法:
mount -o size=64G -o nr_inodes=1000000 -o noatime,nodiratime -o remount /dev/shm
问题2:监听问题
oracle 监听启动问题 Instance “orcl”, status UNKNOWN, has 1 handler(s) for this service…
解决办法:
1.修改listener.ora文件内容
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.11.145)(PORT = 1521))
)
)
SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (GLOBAL_DBNAME = orcl)(SID_NAME=orcl)(ORACLE_HOME=/opt/oracle/oracle/product/11.2.0/dbhome_1)))
ADR_BASE_LISTENER = /opt/oracle
2.修改tnsnames.ora的内容
LISTENER_ORCL =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.11.145)(PORT = 1521))
orcl =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.11.145)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SID = orcl)
)
)
重启监听,就OK了!
问题3:ORA-01102: cannot mount database in EXCLUSIVE mode报错
我借鉴的博文中的方法可以解决我的问题
https://blog.csdn.net/morigejile/article/details/54727679
达梦在线服务平台: https://eco.dameng.com