GoldenGate基本原理

Jet_Zhang發表於2018-01-19
Go

什麼是GoldenGate

官方定義：
Oracle GoldenGate is a comprehensive software package for real-time data integration and replication in heterogeneous IT environments.The product set enables high availability solutions, real-time data integration, transactional change data capture, data replication, transformations, and verification between operational and analytical enterprise systems.

大白話版：
Oracle GoldenGate(OGG)只會做一件事：複製資料。除此之外，啥也不會。
  • 專一
  • 高效
  • 跨平臺

OGG支援的平臺

支援的資料庫：
Oracle
  • MySQL
  • DB2
  • SQL Server
  • NonStop SQL/MX
  • Sybase
  • Teradata
  • TimesTen
  • PostgreSQL
  • Informix
支援的平臺：
  • Unix(Solaris,AIX,HP-UX)
  • Linux
  • Windows
  • z/OS
  • HP NonStop
支援的操作：
  • DML
  • DDL

OGG支援的架構




OGG基本原理-Oracle的Redo Log機制



LGWR: Log Writer Process
日誌寫程式，將快取中的Change Vector寫入到檔案(Online Redo Log)中

ARCn: Archiver Processes
歸檔程式，對Online Redo Log進行複製

Redo Log:
記錄資料庫的變更資料(Change Vector)。
分為：Online Redo Log和Archived Redo Log

OGG基本原理-工作機制






主要程式：
  • Manager
  • 總控程式
  • 管理其它程式
  • 管理網路埠
  • 管理TRAIL檔案
  • 管理執行日誌
Extract
  • 資料抓取
  • 資料分發(Data Pump)
Replicat
  • 資料應用
Collector
  • 資料接收

主要檔案：
Trail
資料的記錄

Checkpoints
資料檢查點資訊


OGG的安裝

環境: CentOS 6.7/Oracle 11.2.0.4/GoldenGate 11.2.1.0.32

Step 1. 設定環境變數
export ORACLE_HOME=/u01/app/oracle/product/database/11.2.0/db_1
export LD_LIBRARY_PATH=$ORACLE_HOME/lib
export PATH=$ORACLE_HOME/bin:$PATH

Step 2. 解壓縮軟體包
cd /u01/app/oracle/product/ogg/gghome/11.2.1/gghome_1
tar xf fbo_ggs_Linux_x64_ora11g_64bit.tar

Step 3. 安裝執行目錄
cd /u01/app/oracle/product/ogg/gghome/11.2.1/gghome_1
./ggsci
GGSCI> create subdirs


OGG實施的基本步驟

場景：源庫的表J.T1實時同步到目標庫的J.T1


Step 1. 源端OGG賬號配置
SQL> CREATE TABLESPACE GGS DATAFILE '+DATA01' SIZE 4G;
SQL> CREATE USER GGS IDENTIFIED BY GGS123 DEFAULT TABLESPACE GGS QUOTA UNLIMITED ON GGS;
SQL> GRANT DBA TO GGS;


$ ./keygen 128 1
0xD63ED81A1DA4DA0596004362410C5522
$ cat > ENCKEYS
DEFKEY 0xD63ED81A1DA4DA0596004362410C5522


GGSCI> encrypt password GGS123 encryptkey DEFKEY
Encrypted password:  AADAAAAAAAAAAAGAJDSJTIIEHJJBOBPDNEZBYIAAPDOCCFGILFMAOGNCMCCAXABIQIFIJAXCKBLFQIMC
Algorithm used:  AES128

Step 2. 源端資料庫配置
SQL> ALTER DATABASE FORCE LOGGING;
SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;


GGSCI> dblogin userid ggs, password AADAAAAAAAAAAAGAJDSJTIIEHJJBOBPDNEZBYIAAPDOCCFGILFMAOGNCMCCAXABIQIFIJAXCKBLFQIMC, encryptkey DEFKEY
GGSCI> add trandata J.T1


Step 3. 源端Manager程式配置
GGSCI> edit params MGR
PORT 7809
PURGEOLDEXTRACTS /data01/oracle/ogg/dirdat/ta*, USECHECKPOINTS, MINKEEPDAYS 30
AUTORESTART ER *, RETRIES 5, WAITMINUTES 1, RESETMINUTES 30
LAGREPORTMINUTES 30
LAGCRITICALMINUTES 60
GGSCI> start MGR


Step 4. 源端Extract程式配置
GGSCI> edit params EJT
EXTRACT EJT
SETENV (ORACLE_HOME="/u01/app/oracle/product/database/11.2.0/db_1")
SETENV (ORACLE_SID="stest")
USERID GGS, PASSWORD AADAAAAAAAAAAAGAJDSJTIIEHJJBOBPDNEZBYIAAPDOCCFGILFMAOGNCMCCAXABIQIFIJAXCKBLFQIMC, ENCRYPTKEY DEFKEY
TRANLOGOPTIONS DBLOGREADER, DBLOGREADERBUFSIZE 2097152
EXTTRAIL /data01/oracle/ogg/dirdat/ta
DISCARDFILE /u01/app/oracle/product/ogg/gghome/11.2.1/gghome_1/dirrpt/EJT.dsc, APPEND Megabytes 256
DISCARDROLLOVER AT 00:00 on SUNDAY
REPORTROLLOVER AT 00:00 on SUNDAY
REPORTCOUNT EVERY 4 HOURS, RATE
FETCHOPTIONS, FETCHPKUPDATECOLS, USESNAPSHOT, NOUSELATESTVERSION, MISSINGROW REPORT
STATOPTIONS REPORTFETCH
WARNLONGTRANS 1H, CHECKINTERVAL 5M


TABLE J.T1  ;

Step 5. 源端新增Extract程式
GGSCI> add extract EJT, tranlog, begin now
GGSCI> add exttrail /data01/oracle/ogg/dirdat/ta, extract EJT, megabytes 256
GGSCI> start extract EJT

Step 6. 目標端資料庫配置
SQL> ALTER SYSTEM SET enable_goldengate_replication=TRUE;

Step 7. 目標端OGG賬號配置
參見Step 1. 源端OGG賬號配置

Step 8. 目標端Checkpoint配置
GGSCI> dblogin userid ggs, password AADAAAAAAAAAAAGAJDSJTIIEHJJBOBPDNEZBYIAAPDOCCFGILFMAOGNCMCCAXABIQIFIJAXCKBLFQIMC, encryptkey DEFKEY
GGSCI> add checkpointtable ggs.checkpoint

Step 9. 目標端Manager程式配置
GGSCI> edit params ./GLOBALS
CHECKPOINTTABLE ggs.checkpoint
ALLOWOUTPUTDIR /data01/oracle/ogg/dirdat


GGSCI> edit params MGR
PORT 7809
PURGEOLDEXTRACTS /data01/oracle/ogg/dirdat/ta*, USECHECKPOINTS, MINKEEPDAYS 30
AUTORESTART ER *, RETRIES 5, WAITMINUTES 1, RESETMINUTES 30
LAGREPORTMINUTES 30
LAGCRITICALMINUTES 60
GGSCI> start MGR

Step 10. 源端Data Pump程式配置
GGSCI> edit params PJT
EXTRACT PJT
SETENV (ORACLE_HOME="/u01/app/oracle/product/database/11.2.0/db_1")
PASSTHRU
RMTHOST tj, MGRPORT 7809
RMTTRAIL /data01/oracle/ogg/dirdat/ta
DISCARDFILE /u01/app/oracle/product/ogg/gghome/11.2.1/gghome_1/dirrpt/PJT.dsc, APPEND, MEGABYTES 256
DISCARDROLLOVER AT 00:00 on SUNDAY
REPORTROLLOVER AT 00:00 on SUNDAY
REPORTCOUNT EVERY 4 HOURS, RATE


TABLE J.T1  ;


Step 11. 源端新增Data Pump程式
GGSCI> add extract PJT, exttrailsource /data01/oracle/ogg/dirdat/ta
GGSCI> add rmttrail /data01/oracle/ogg/dirdat/ta, extract PJT, megabytes 256
GGSCI> start extract PJT

Step 12. 目標端Replicat程式配置
GGSCI> edit params RJT
REPLICAT RJT
SETENV (ORACLE_HOME = "/u01/app/oracle/product/database/11.2.0/db_1" )
SETENV (ORACLE_SID="ttest")
SETENV (NLS_LANG = "AMERICAN_CHINA.ZHS16GBK")
USERID GGS, PASSWORD AADAAAAAAAAAAAGAJDSJTIIEHJJBOBPDNEZBYIAAPDOCCFGILFMAOGNCMCCAXABIQIFIJAXCKBLFQIMC, ENCRYPTKEY DEFKEY
DISCARDFILE /u01/app/oracle/product/ogg/gghome/11.2.1/gghome_1/dirrpt/RJT.dsc, APPEND Megabytes 256
DISCARDROLLOVER AT 00:00 on SUNDAY
REPORTROLLOVER AT 00:00 on SUNDAY
REPORTCOUNT EVERY 4 HOURS, RATE
ASSUMETARGETDEFS


MAP J.T1, TARGET J.T1;

Step 13. 目標端新增Replicat程式
GGSCI> dblogin userid ggs, password AADAAAAAAAAAAAGAJDSJTIIEHJJBOBPDNEZBYIAAPDOCCFGILFMAOGNCMCCAXABIQIFIJAXCKBLFQIMC, encryptkey DEFKEY
GGSCI> add replicat RJT, exttrail /data01/oracle/ogg/dirdat/ta

Step 14. 資料初始化-源端資料匯出
SQL> SELECT CURRENT_SCN FROM V$DATABASE;


  CURRENT_SCN
-------------
    373926987


$ expdp system/systm123 directory=DUMPDIR dumpfile=JT1.dump tables=J.T1 flashback_scn=373926987 logfile=JT1.expdp.log

Step 15. 資料初始化-目標端資料匯入
$ impdp system/systm123 directory=DUMPDIR dumpfile=JT1.dump logfile=JT1.impdp.log

Step 16. 目標端啟Replicat程式
GGSCI> start replicat RJT, atcsn 373926987


來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/13885898/viewspace-2150318/，如需轉載，請註明出處，否則將追究法律責任。

相關文章