本文檔主要介紹OGG在AIX系統上的安裝,不适用其他作業系統。
整體架構
軟體版本
OGG版本:
123010_ggs_Adapters_Linux_x64.zip (redhat)
122022_fbo_ggs_Linux_x64_shiphome.zip (redhat)
122022_fbo_ggs_AIX_ppc_shiphome.zip (AIX)
OGG for bigdata版本:
OGG_BigData_Linux_x64_12.3.2.1.1.zip
JDK版本:
jdk1.8
安裝步驟
OGG安裝(備庫)
安裝在/home/oracle目錄中,使用oracle使用者安裝
1.上傳安裝包
将安裝包122022_fbo_ggs_AIX_ppc_shiphome.zip上傳至/home/oracle/temp/目錄下,沒有請自行建立
2.建立安裝目錄
mkdir /home/oracle/ogg
3.解壓安裝包
unzip 122022_fbo_ggs_AIX_ppc_shiphome.zip
cd fbo_ggs_AIX_ppc_shiphome/Disk1
4.靜默安裝
修改檔案/home/oracle/temp/fbo_ggs_AIX_ppc_shiphome/Disk1/response/oggcore.rsp
修改以下位置:
INSTALL_OPTION=ORA12c
SOFTWARE_LOCATION=/home/oracle/ogg
START_MANAGER=false
MANAGER_PORT=7809
DATABASE_LOCATION=/u01/app/oracle/product/12.1.0/db_1
執行安裝指令安裝
./runInstaller -silent -responseFile /home/oracle/temp/fbo_ggs_AIX_ppc_shiphome/Disk1/response/oggcore.rsp
Starting Oracle Universal Installer…
Checking Temp space: must be greater than 120 MB
. Actual 10461 MB Passed
Checking swap space: 0 MB available, 150 MB required. Failed <<<<
Some requirement checks failed. You must fulfill these requirements before
continuing with the installation,
Exiting Oracle Universal Installer, log for this session can be found at /tmp/OraInstall2019-11-07_10-41-12AM/installActions2019-11-07_10-41-12AM.log
檢查記憶體和swap
提示以下内容安裝成功
The installation of Oracle GoldenGate Core was successful.
Please check ‘/u01/app/grid/logs/silentInstall2019-11-07_02-56-59PM.log’ for more details.
Successfully Setup Software.
OGG配置(備庫)
1.添加環境變量
vi .profile
添加
export OGG_HOME=/home/oracle/ogg
export LIBPATH=: O R A C L E H O M E / l i b : ORACLE_HOME/lib: ORACLEHOME/lib:ORACLE_HOME/lib32: O G G H O M E e x p o r t P A T H = OGG_HOME export PATH= OGGHOMEexportPATH=ORACLE_HOME/bin: O R A C L E H O M E / O P a t c h : ORACLE_HOME/OPatch: ORACLEHOME/OPatch:GRID_HOME/bin:/usr/bin:/etc:/usr/sbin:/usr/ucb:/usr/java5/bin: P A T H : PATH: PATH:OGG_HOME
退出儲存
環境變量生效
. ~/.profile
2.主庫操作
建立OGG使用者及表空間,賦權限
(1)配置Oracle資料相關資訊(主庫執行)
開啟資料庫歸檔模式
su - oracle
sqlplus / as sysdba (以DBA身份連接配接資料庫)
SQL>alter database open; (打開資料庫)
SQL>shutdown immediate; (立即關閉資料庫)
SQL>startup mount; (啟動執行個體并加載資料庫,但不打開)
SQL>alter database archivelog; (更改資料庫為歸檔模式)
SQL>alter database open; (打開資料庫)
Oracle補全日志(Supplemental logging)特性因其作用的不同可分為以下幾種:最小(Minimal),支援所有字段(all),支援主鍵(primary key),支援唯一鍵(unique),支援外鍵(foreign key)
開啟附加日志(打開最小補全日志,隻能庫級别)
1)alter database add supplemental log data;
開啟交換日志:
SQL> alter system switch logfile;
完成後,確定資料庫處于歸檔模式,并已經開啟附加日志和強制日志:
(2)建立tablespace和使用者(主庫執行)
建立使用者并賦權:
備庫操作
啟用enable_goldengate_replication參數
alter system set enable_goldengate_replication = true;
3.OGG配置
(1)進入OGG_HOME,執行./ggsci登入,建立目錄
GGSCI (coredg) 1> create subdirs
Creating subdirectories under current directory /home/oracle/ogg
Parameter files /home/oracle/ogg/dirprm: created
Report files /home/oracle/ogg/dirrpt: created
Checkpoint files /home/oracle/ogg/dirchk: created
Process status files /home/oracle/ogg/dirpcs: created
SQL script files /home/oracle/ogg/dirsql: created
Database definitions files /home/oracle/ogg/dirdef: created
Extract data files /home/oracle/ogg/dirdat: created
Temporary files /home/oracle/ogg/dirtmp: created
Credential store files /home/oracle/ogg/dircrd: created
Masterkey wallet files /home/oracle/ogg/dirwlt: created
Dump files /home/oracle/ogg/dirdmp: created
(2)編輯mgr程序
GGSCI (coredg) 5> edit params mgr
PORT 7809
DYNAMICPORTLIST 7810-7909
AUTORESTART EXTRACT ,RETRIES 5,WAITMINUTES 7
userid [email protected],password ogg
PURGEOLDEXTRACTS /home/oracle/ogg/dirdat/, usecheckpoints, minkeepdays 1
LAGREPORTHOURS 1
LAGINFOMINUTES 30
LAGCRITICALMINUTES 45
啟動mgr程序
GGSCI (coredg) 7> start manager
Manager started.
檢視程序狀态
GGSCI (coredg) 8> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER STOPPED
檢視程序日志
GGSCI (coredg) 10> view report mgr
(3)編輯抽取程序
GGSCI (coredg) 11> edit params ext0
EXTRACT ext0
setenv(ORACLE_HOME="/u01/app/oracle/product/12.1.0/db_1")
setenv(NLS_LANG=“AMERICAN_AMERICA.AL32UTF8”)
setenv(ORACLE_SID=“coredb”)
userid [email protected],password ogg
GETTRUNCATES
REPORTCOUNT EVERY 1 MINUTES, RATE
DISCARDFILE /home/oracle/ogg/dirrpt/ext0.dsc, APPEND, MEGABYTES 1000
WARNLONGTRANS 2h,CHECKINTERVAL 300
EXTTRAIL /home/oracle/ogg/dirdat/tr
TRANLOGOPTIONS EXCLUDEUSER ogg
TRANLOGOPTIONS MINEFROMACTIVEDG
DBOPTIONS ALLOWUNUSEDCOLUMN
DYNAMICRESOLUTION
REPORTROLLOVER AT 02:00
FETCHOPTIONS FETCHPKUPDATECOLS
GETUPDATEBEFORES
NOCOMPRESSDELETES
NOCOMPRESSUPDATES
IGNOREDELETES
IGNORETRUNCATES
TABLE ensemble.mb_tran_hist;
(4)在資料庫中進行程序注冊,增加抽取程序隊列
2019-11-21 10:29:37 ERROR OGG-00868 The number of Oracle redo threads (1) is not the same as the number of checkpoint threads (2). EXTRACT groups on RAC systems should be created with the THREADS parameter (e.g., ADD EXT , TRANLOG, THREADS 1, BEGIN…
根據報錯資訊選擇需要加入的threads
删除extract ext0
OGG登入
dblogin userid [email protected] password ogg
delete extract ext0
GGSCI (coredg) 18> add extract ext0, tranlog, threads 1,begin now
EXTRACT added.
—從指定時間點開始抽取資料
檢視抽取程序詳細資訊
GGSCI (coredg) > info ext9 detail
EXTRACT EXT9 Last Started 2019-12-03 15:10 Status RUNNING
Checkpoint Lag 00:00:00 (updated 00:00:08 ago)
Process ID 57511
Log Read Checkpoint Oracle Redo Logs
2019-12-03 17:06:13 Thread 1, Seqno 401, RBA 26073600
SCN 0.10769149 (10769149)
Log Read Checkpoint Oracle Redo Logs
2019-12-03 15:08:29 Thread 0, Seqno 0, RBA 0
SCN 0.0 (0)
Target Extract Trails:
Trail Name Seqno RBA Max MB Trail Type /home/oracle/ogg/dirdat/ext9/ex 2430 1615 500 EXTTRAIL
Extract Source Begin End
Not Available 2019-12-03 15:08 2019-12-03 17:06
Not Available * Initialized * 2019-12-03 15:08
Not Available * Initialized * 2019-12-03 15:08
Not Available * Initialized * 2019-12-03 15:08
Not Available * Initialized * 2019-12-03 15:08
Not Available * Initialized * 2019-12-03 15:08
Current directory /home/oracle/ogg
Report file /home/oracle/ogg/dirrpt/EXT9.rpt
Parameter file /home/oracle/ogg/dirprm/ext9.prm
Checkpoint file /home/oracle/ogg/dirchk/EXT9.cpe
Process file /home/oracle/ogg/dirpcs/EXT9.pce
Error log /home/oracle/ogg/ggserr.log
查詢SCN對應的時間點,從該時間點開始取數
add extract ext0, tranlog, threads 1,begin 2019-12-03 17:06:13
(5)最後添加trail檔案的定義與extract程序綁定:
GGSCI (coredg) 20> ADD EXTTRAIL /home/oracle/ogg/dirdat/tr, EXTRACT ext0, megabytes 1
EXTTRAIL added.
(6)Start ext0啟動抽取程序
GGSCI (coredg as [email protected]) 32> start ext0
檢視程序狀态info all
GGSCI (coredg as [email protected]) 35> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING EXT0 00:00:00 00:02:44
(7)添加投遞程序
GGSCI (coredg as [email protected]) 36> edit param pump0
extract pump0
dynamicresolution
gettruncates
userid [email protected],password ogg
RMTHOST 57.0.10.100, MGRPORT 7809
RMTFILE /home/oracle/oggbd/dirdat/tr, MEGABYTES 2, PURGE
TABLE ensemble.mb_tran_hist;
增加傳送程序隊列,分别将本地trail檔案和目标端的trail檔案綁定到extract程序:
添加pump捕獲組:
add extract pump0, exttrailsource /home/oracle/ogg/dirdat/tr,begin now
定義pump trail檔案:
add rmttrail /home/oracle/oggbd/dirdat/tr, extract pump0 megabytes 1
啟動投遞程序pump
GGSCI (coredg as [email protected]) 55> start pump0
GGSCI (coredg as [email protected]) 58> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING EXT0 00:00:00 00:00:08
EXTRACT RUNNING PUMP0 00:00:00 00:00:07
OGG for Bigdata安裝
Kafka會限制消息的大小,當超過限制時會舍棄該消息,需要修改消息大小限制參數:
message.max.bytes
replica.fetch.max.bytes
1.切換到Oracle使用者
su - oracle
2.建立java目錄
mkdir -p /home/oracle/java
3.将軟體包 jdk-8u191-linux-x64.tar.gz 以oracle使用者上傳到 /home/oracle/java目錄
4.解壓縮
tar zxvfjdk-8u191-linux-x64.tar.gz -C /home/oracle/java
4.修改環境變量
vi .bash_profile
增加如下内容:
export OGG_HOME=/home/oracle/oggbd
export JAVA_HOME=/home/oracle/java/jdk1.8.0_191-amd64
export PATH= J A V A H O M E / b i n : JAVA_HOME/bin: JAVAHOME/bin:PATH
export LD_LIBRARY_PATH= J A V A H O M E / j r e / l i b / a m d 64 / s e r v e r : JAVA_HOME/jre/lib/amd64/server: JAVAHOME/jre/lib/amd64/server:JAVA_HOME/jre/lib/amd64: J A V A H O M E / j r e / l i b / a m d 64 / s e r v e r / l i b j v m . s o : JAVA_HOME/jre/lib/amd64/server/libjvm.so: JAVAHOME/jre/lib/amd64/server/libjvm.so:JAVA_HOME/jre/lib/amd64/libjsig.so:$OGG_HOME/lib
5.環境變量生效
source .profile
OGG for Bigdata配置
配置管理程序mgr
GGSCI (BigDataAPP) 10> edit param mgr
PORT 7809
DYNAMICPORTLIST 7810-7909
AUTORESTART EXTRACT , RETRIES 5, WAITMINUTES 7
purgeoldextracts /home/oracle/oggbd/dirdat/, usecheckpoints, minkeepdays 1
LAGREPORTHOURS 1
LAGINFOMINUTES 30
LAGCRITICALMINUTES 45
編寫REPLICAT程序
REPLICAT rep0
TARGETDB LIBFILE libggjava.so SET property=dirprm/exp0_kafka.props
SOURCEDEFS /home/oracle/oggbd/dirdef/source.def
REPORTCOUNT EVERY 1 MINUTES, RATE
DISCARDFILE /home/oracle/oggbd/dirrpt/exp0_kafka.dsc, APPEND, MEGABYTES 100
REPORTROLLOVER AT 02:00
GROUPTRANSOPS 10000
MAP ensemble., TARGET ensemble.;
配置exp0_kafka.props
進入OGG_HOME/dirprm
vi exp0_kafka.props
gg.handlerlist = kafkahandler
gg.handler.kafkahandler.type = kafka
gg.handler.kafkahandler.KafkaProducerConfigFile=custom_kafka_producer.properties
gg.handler.kafkahandler.TopicName =oggaix
#gg.handler.kafkahandler.format =avro_op
gg.handler.kafkahandler.SchemaTopicName=myoggtest
gg.handler.kafkahandler.BlockingSend =false
gg.handler.kafkahandler.includeTokens=false
#json
gg.handler.kafkahandler.format = json
gg.handler.kafkahandler.format.insertOpKey = I
gg.handler.kafkahandler.format.updateOpKey = U
gg.handler.kafkahandler.format.deleteOpKey = D
gg.handler.kafkahandler.format.truncateOpKey=T
gg.handler.kafkahandler.format.prettyPrint = true
gg.handler.kafkahandler.format.jsonDelimiter = CDATA[]
gg.handler.kafkahandler.format.generateSchema = true
gg.handler.kafkahandler.format.schemaDirectory = dirdef
#gg.handler.kafkahandler.format.treatAllColumnsAsString = true
gg.handler.kafkahandler.format.includePrimaryKeys = true
gg.handler.kafkahandler.mode =tx
#gg.handler.kafkahandler.maxGroupSize =100, 1Mb
#gg.handler.kafkahandler.minGroupSize =50, 500Kb
goldengate.userexit.timestamp=utc
goldengate.userexit.writers=javawriter
javawriter.stats.display=TRUE
javawriter.stats.full=TRUE
gg.log=log4j
gg.log.level=INFO
gg.report.time=30sec
#Sample gg.classpath for Apache Kafka
gg.classpath=dirprm/:/home/oracle/hadoopclient/Kafka/kafka/libs/*
javawriter.bootoptions=-Xmx512m -Xms32m -Djava.class.path=ggjava/ggjava.jar
配置custom_kafka_producer.properties
bootstrap.servers=57.0.10.81:21005,57.0.10.79:21005,57.0.10.80:21005
acks=1
compression.type=gzip
reconnect.backoff.ms=1000
value.serializer=org.apache.kafka.common.serialization.ByteArraySerializer
key.serializer=org.apache.kafka.common.serialization.ByteArraySerializer
100KB per partition
batch.size=409600
linger.ms=10000
添加rep0程序
add replicat rep0, exttrail /home/oracle/oggbd/dirdat/tr,begin now
啟動程序
Start mgr
Start rep0
在源端和目标端的OGG指令行下使用start [程序名]的形式啟動所有程序。
啟動順序按照源mgr——目标mgr——源extract——源pump——目标replicate來完成。