Oracle数据实时同步⼤数据平台的解决⽅案
数据实时同步⼤数据平台的解决⽅案
摘要：基于传统企业Oracle数据库实时增量数据同步到Kafka消息系统，供下游做实时分析/实时ETL等场景，引进Oracle GoldenGate组件提供不影响系统处理功能的实时数据集成和持续可⽤性解决⽅案，使企业能显著提⾼整个企业关键系统的可⽤性、可靠性和性能并同时降低IT成本。
⼀、企业现状篇
。
⼆、数据同步篇
传统关系型数据库数据同步到⼤数据平台的⽅式有很多。1.全量同步
2.按时间戳增量同步
3.基于数据库归档⽇志的同步
三、安装配置篇
下⾯主要介绍Oracle数据实时增量同步Kafka消息系统，供下游做实时处理场景。源端 oracle database + oracle goldengate for oracle,⽬标端oracle goldengate for bigdata +kafka
（⼀）环境准备
1.java环境变量（版本jdk1.8）
2.机器⽹络连通
3.机器时钟同步
4.源端oracle数据库（版本11.2.0.4以上）
（⼆）环境背景
1.源端
机器IP: 192.168.72.3
服务：oracle, ogg的mgr(端⼝7809), ext_test, dpe_test
2.⽬标端
机器IP: 192.168.72.3
服务：ogg的mgr(端⼝7909), rep_test，zookeeper, kafka
3.版本
oracle 版本 11g release 2
kafka 版本 2.11-2.0.0
zookeeper 版本 3.4.13
JVM 版本，1.8（ogg for big data必须1.8 以上，否者⽆法启动）
ogg 源端安装包⽂件名，123010_fbo_ggs_Linux_x64_shiphome
ogg ⽬标端安装包⽂件名，123010_ggs_Adapters_Linux_x64
（三）源端配置
1.解压安装
1.设置response参数
[oracle@stream ~]$ cd /home/oracle/tmp/fbo_ggs_Linux_x64_shiphome/Disk1
[oracle@stream Disk1]$vi ./response/oggcore.rsp
INSTALL_OPTION=ORA11g
SOFTWARE_LOCATION=/home/oracle/software/oracle/goldengate
START_MANAGER=false
MANAGER_PORT=7809
DATABASE_LOCATION=$ORACLE_HOME
UNIX_GROUP_NAME=oracle
# INSTALL_OPTION 安装选项 oracle11g设置ORA11g；oracle12c设置ORA12c
# SOFTWARE_LOCATION OGG的安装⽬录
# START_MANAGER是否⾃动启动mgr管理进程 true,false
# MANAGER_PORT mgr启动端⼝，START_MANAGER=true时添加
# START_MANAGER 数据库地址 START_MANAGER=true时添加$ORACLE_HOME
# UNIX_GROUP_NAME 系统权限组
2.运⾏安装程序
[oracle@ogg Disk1]$ ./runInstaller -silent -responseFile
/home/oracle/tmp/fbo_ggs_Linux_x64_shiphome/Disk1/response/oggcore.rsp
2.环境变量
goldengate安装要配置JAVA_HOME、ORACLE_SID、ORACLE_HOME、SOGG_HOME 、LD_LIBRARY_PATH环境变量。#jdk
export JAVA_HOME=/home/oracle/software/jdk/jdk1.8.0_162
export PATH=$JAVA_HOME/bin:$PATH
#oracle
export ORACLE_HOME=/home/oracle/software/oracle/database/oracle11g/product/11.2.0/dbhome_1 export ORACLE_SID=orcl
export PATH=$PATH:$ORACLE_HOME/bin:$ORACLE_HOME/OPatch
#goldengate
export SOGG_HOME=/home/oracle/software/oracle/goldengate
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:$ORACLE_HOME/lib32:$SOGG_HOME
export PATH=$PATH:$SOGG_HOME
3.权限分配
1.归档⽇志
源端数据库要开归档模式、置成force logging、开追加⽇志操作。
①Archive logging
检查oracle 是否已经开启 Archive logging（⽇志⾃动归档）
⽅式⼀：
[oracle@stream ~]$ sqlplus / as sysdba
SQL> archive log list
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 6
Current log sequence 8
SQL>
⽅式⼆：
SQL> select name,log_mode from v$database;
LOG_MODE 显⽰NOARCHIVELOG则代表没有开启。
如果没有开启Archive logging，需要先停⽌数据库，执⾏以下命令：
SQL> shutdown immediate;
SQL> startup mount;
SQL> alter database archivelog;
Database altered.
SQL> alter database open;
②forcelogging & minimal supplemental logging
检查是否开启 forcelogging 和 minimal supplemental logging
SQL> SELECT supplemental_log_data_min,force_logging FROM v$database;
环境没有开启屏幕输出NO、开启输出YES，所以还需要执⾏开启命令，执⾏完毕后，我们再来
SQL> alter database add supplemental log data;
SQL> alter database force logging;
//TODO待验证
SQL> alter database add supplemental log data (primary key) columns;
SQL> alter database add supplemental log data (unique) columns;
SQL> alter database force logging;
SQL> alter system switch logfile;
注意：如果不指定Primary key 和unique 属性，OGG将不会传送PK字段或Unique indiex字段信息。这样，下游的应⽤，在处理update数据时将失去依据
检查开启情况，显⽰如下则代表ok
查看forcelogging和minimal supplemental logging 的开启情况
SQL> SELECT supplemental_log_data_min,force_logging FROM v$database;
SUPPLEME FOR
-------- ---
IMPLICIT YES
③ENABLE_GOLDENGATE_REPLICATION参数
alter system set ENABLE_GOLDENGATE_REPLICATION=true scope=both;
2.指定⽤户并授权
create user goldengate identified by <password>;
grant connect to goldengate;
grant alter session to goldengate;
grant create session to goldengate;
grant connect to goldengate;
grant resource to goldengate;
grant select any dictionary to goldengate;
grant select any table to goldengate;
grant insert any table to goldengate;
grant update any table to goldengate;
grant delete any table to goldengate;
grant create any table to goldengate;
grant alter any table to goldengate;
grant select any transaction to goldengate;
grant create any index to goldengate;
grant alter any index to goldengate;
grant create any sequence to goldengate;
grant unlimited tablespace to goldengate;
grant drop any table to goldengate;
grant drop any sequence to goldengate;
grant flashback any table to goldengate;
3.Register Extract Process
-
-这⾥是Enable integrated capture mode的关键步骤
[oracle@stream goldengate]$ ./ggsci
GGSCI (stream) 1> dblogin userid goldengate password <password>
GGSCI (stream as goldengate@orcl) 2> register extract ext_test database
2020-04-02 18:30:01 ERROR OGG-02062 User goldengate does not have the required privileges to use integrated capture.
---需要授权, 且注意⽤户名要⼤写
SQL> exec DBMS_GOLDENGATE_AUTH.GRANT_ADMIN_PRIVILEGE (grantee=>'GOLDENGATE',
privilege_type=>'capture',grant_select_privileges=>true, do_grants=>TRUE);
PL/SQL procedure successfully completed.
SQL>
GGSCI (stream as goldengate@orcl) 2> register extract ext_test database
2020-04-02 19:14:47 INFO OGG-02003 Extract EXT_TEST successfully registered with database at SCN 1067318. GGSCI (stream as goldengate@orcl) 3>
//TODO其他异常
GGSCI (stream as goldengate@orcl) 3> register extract ext_test database
2020-04-02 19:14:50 WARNING OGG-02064 Oracle compatibility version 11.2.0.0.0 has limited datatype support for integrated capture. Version 11.2.0.3 required for full support.
ERROR: Cannot register or unregister EXTRACT EXT_TEST because of the following SQL error: OCI Error 6,550.
--上⾯错误是由于权限不够，授权，
SQL>exec DBMS_GOLDENGATE_AUTH.GRANT_ADMIN_PRIVILEGE (grantee=>'GOLDENGATE');linux系统安装oracle11g
ERROR: Cannot register or unregister EXTRACT EXT_TEST because of the following SQL error: OCI Error 1,950.
--上⾯错误是由于权限不够，授权，
SQL>GRANT UNLIMITED TABLESPACE TO GOLDENGATE;
GGSCI (stream as goldengate@orcl) 3> register extract ep1 database
版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。
发表评论