|
1、背景介紹
1.1、Extract兩種抽取模式
1）Classic：基于在線日志或歸檔日志進(jìn)行抽取。對數(shù)據(jù)庫版本無特殊要求
 2）Integrated：OGG 11.2.1及以上版本提供，其中Logmining
server負(fù)責(zé)以LCR格式從數(shù)據(jù)庫日志中捕獲數(shù)據(jù)變化，extract進(jìn)程再將這些數(shù)據(jù)存入trail文件。
 --Integrated部署方式 本地部署：源庫和log mining
server數(shù)據(jù)庫是同一個(gè)數(shù)據(jù)庫（源庫和集成抽取進(jìn)程在同一臺主機(jī)）
Downstream部署：源庫和log mining
server數(shù)據(jù)庫是不同的數(shù)據(jù)庫（源庫將歸檔日志傳送downstream數(shù)據(jù)庫）（源庫和集成抽取進(jìn)程不在同一臺主機(jī)）
--Integrated模式的好處 *兼容性上支持更多的數(shù)據(jù)類型（IOT、compression、XML、LOB） *無需為RAC、ASM、TDE、RMAN做額外的配置 *多線程抽?。?/span>producer thread： capture redo；consumer thread：process redo），并發(fā)處理過程的性能提升 *OGG12c對oracle 12c 多租戶的捕獲（必須集成抽取模式才能支持） *DDL無需trigger（OGG12c、DB11204） OGG12新特性在Integrated模式DDL無需trigger
1.2、基于triggerDDL的復(fù)制 --需要考慮的問題：性能下降 1.3、replicat的三種復(fù)制模式
--集成replicat模式（replicat mode）可以支持的復(fù)制模式： *Classic capture (oracle和非oracle)-> Classic Replicat *Classic capture (oracle和非oracle)-> integrated Replicat *Integrated capture -> Classic Replicat *Integrated capture -> integrated Replicat 2、實(shí)驗(yàn)環(huán)境：源端與目標(biāo)端都基于Integrated模式的OGG12搭建 源端： database version:12.1.2.0.0 goldengate version: 12.1.2.0.0 OS version:redhat 6.4 ip:192.168.3.7 目標(biāo)端： database version:12.1.2.0 goldengate version: 12.1.2.0.0 OS version:redhat 6.4 ip:192.168.3.6
2.1、數(shù)據(jù)庫相關(guān)設(shè)置 >sqlplus / nolog SQL>conn / as sysdba; --針對全庫添加附加日志 SQL> alter database force logging; SQL>alter database add supplemental log data; SQL> alter system switch logfile; --創(chuàng)建 goldengate 管理用戶和對應(yīng)的表空間 datafile SQL>create tablespace ogg datafile '/u01/app/oracle/oradata/orcl/ogg01.dbf' size 4G autoextend on next 100M; SQL>create user ogg identified by ogg123 default tablespace ogg; SQL>create user test identified by oracle; --賦予相關(guān)權(quán)限給goldengate 管理用戶 SQL>grant connect,resource,dba to ogg; SQL>GRANT EXECUTE ON UTL_FILE TO ogg; SQL> grant create table,create sequence to ogg; SQL>EXEC DBMS_GOLDENGATE_AUTH.GRANT_ADMIN_PRIVILEGE(grantee => 'ogg', privilege_type => 'capture'）; SQL> alter system set streams_pool_size=512M scope=both; 2.2、OGG12c的安裝 1）創(chuàng)建 ogg 安裝目錄： #cd /u01 #mkdir ogg #chmod 777 ogg #chown -R oracle:oinstall /u01/ogg 2）以oracle 賬號登錄, 將ogg 介質(zhì)通過ftp 傳送到/u01/ogg 目錄下并解壓: #su –oracle $cd /u01/ogg $unzip *.zip 3）環(huán)境變量的配置： [oracle@oracle ~]$ vim .bash_profile ".bash_profile" 16L, 356C# .bash_profile # Get the aliases and functions if [ -f ~/.bashrc ]; then fi # User specific environment and startup programs PATH=/u01/ogg:$ORACLE_HOME/bin:$PATH:$HOME/bin export PATH export ORACLE_BASE=/u01/app/oracle export ORACLE_HOME=$ORACLE_BASE/product/12.1.0/dbhome_1 export ORACLE_SID=orcl export LD_LIBRARY_PATH=/u01/ogg:$ORACLE_HOME/lib:/usr/lib 4）goldengate軟件的安裝 將下載的軟件zip安裝包拷貝到數(shù)據(jù)庫服務(wù)器上，使用unzip命令解壓。 --使用Oracle Universal Installer(OUI)安裝goldengate: --在UNIX環(huán)境下，由于一般系統(tǒng)沒有默認(rèn)配置對圖形化工具的支持，所以我們可以象安裝oracle數(shù)據(jù)庫一樣，使用slient方式安裝,這里唯一要做的就是事先準(zhǔn)備好response文件。 INSTALL_OPTION=ORA11c SOFTWARE_LOCATION=/u01/ogg/goldengate START_MANAGER=false MANAGER_PORT=7809 DATABASE_LOCATION=/u01/app/oracle/product/12.1.0/dbhome_1 INVENTORY_LOCATION=/u01/app//oracle/oraInventory UNIX_GROUP_NAME=oinstall [oracle@oracle
response]$ vim oggcore.rsp ################################################################################ ## Oracle
GoldenGate installation option and details #------------------------------------------------------------------------------- # Specify the installation option. # Specify ORA12c for installing Oracle GoldenGate for Oracle Database 12c and # ORA11g for installing Oracle GoldenGate for Oracle Database 11g #-------------------------------------------------------------------------------INSTALL_OPTION=ORA12c #------------------------------------------------------------------------------- # Specify a location to install Oracle GoldenGate #-------------------------------------------------------------------------------SOFTWARE_LOCATION=/u01/ogg/goldengate #------------------------------------------------------------------------------- # Specify true to start the manager after installation. #-------------------------------------------------------------------------------START_MANAGER=false #------------------------------------------------------------------------------- # Specify a free port within the valid range for the manager process. # Required only if START_MANAGER is true. #------------------------------------------------------------------------------- MANAGER_PORT=7809 #------------------------------------------------------------------------------- # Specify the location of the Oracle Database. # Required only if START_MANAGER is true. #-------------------------------------------------------------------------------DATABASE_LOCATION=/u01/app/oracle/product/12.1.0/dbhome_1 ################################################################################ ## Specify details
to Create inventory for Oracle installs ## Required only
for the first Oracle product install on a system.
################################################################################ #------------------------------------------------------------------------------- # Specify the location which holds the install inventory files. # This is an optional parameter if installing on # Windows based Operating System. #------------------------------------------------------------------------------- INVENTORY_LOCATION=/u01/app/oracle/oraInventory #------------------------------------------------------------------------------- # Unix group to be set for the inventory directory. # This parameter is not applicable if installing on # Windows based Operating System. #-------------------------------------------------------------------------------UNIX_GROUP_NAME=oinstall [oracle@oracle Disk1]$ ./runInstaller -silent -responseFile /u01/ogg/fbo_ggs_Linux_x64_shiphome/Disk1/response/oggcore.rsp
2.2 OGG相關(guān)參數(shù)的配置（源端） #cd /u01/ogg >./ggsci --創(chuàng)建子目錄： GGSCI>create subdirs --編輯 MGR 進(jìn)程參數(shù) GGSCI> edit params mgr port 7809 DYNAMICPORTLIST 7840-7850 AUTORESTART EXTRACT *, RETRIES 3, WAITMINUTES 5, RESETMINUTES 60 PURGEOLDEXTRACTS /u01/ogg/dirdat/aa*,usecheckpoints, minkeepdays 7 LAGREPORTHOURS 1 LAGINFOMINUTES 30 LAGCRITICALMINUTES 45 --啟動 MGR 進(jìn)程 GGSCI> start mgr --查看 MGR 進(jìn)程 --GGSCI> info all --對需要同步的表進(jìn)行表級附加日志的添加，即add trandata 操作。 GGSCI > dblogin userid ogg,password ogg123 GGSCI
>ADD EXTRACT intext, INTEGRATED TRANLOG, BEGIN NOW
GGSCI >ADD EXTTRAIL /u01/ogg/dirdat/aa, EXTRACT intext GGSCI>edit params intext EXTRACT intext EXTTRAIL /u01/ogg/dirdat/aa SETENV (ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1) SETENV (NLS_LANG=AMERICAN_AMERICA.ZHS16GBK) USERID ogg, PASSWORD ogg123 WILDCARDRESOLVE DYNAMIC LOGALLSUPCOLS UPDATERECORDFORMAT compact DYNAMICRESOLUTION DDL include all TABLE test.*; GGSCI>ADD EXTRACT intdp EXTTRAILSOURCE /u01/ogg/dirdat/aa GGSCI>ADD RMTTRAIL
/u01/ogg/dirdat/aa, EXTRACT intdp, MEGABYTES 100
GGSCI>>edit params intdp EXTRACT intdp USERID ogg, PASSWORD ogg123 RMTHOST 192.168.3.6, MGRPORT 7809 RMTTRAIL /u01/ogg/dirdat/aa Dynamicresolution TABLE test.*;
2.3 OGG相關(guān)參數(shù)的配置（目標(biāo)端） #cd /u01/ogg >./ggsci --創(chuàng)建子目錄： GGSCI>create subdirs --編輯 MGR 進(jìn)程參數(shù) GGSCI> edit params mgr port 7809 DYNAMICPORTLIST 7840-7850 AUTORESTART EXTRACT *, RETRIES 3, WAITMINUTES 5, RESETMINUTES 60 PURGEOLDEXTRACTS /u01/ogg/dirdat/aa*,usecheckpoints, minkeepdays 7 LAGREPORTHOURS 1 LAGINFOMINUTES 30 LAGCRITICALMINUTES 45 --啟動 MGR 進(jìn)程 GGSCI> start mgr --查看 MGR 進(jìn)程 GGSCI> info all --集成模式： GGSCI>add replicat intrep integrated exttrail /u01/ogg/dirdat/aa GGSCI>edit params intrep REPLICAT intrep setenv (NLS_LANG=SIMPLIFIED CHINESE_CHINA.ZHS16GBK) USERID ogg, PASSWORD ogg123 REPERROR DEFAULT, ABEND ASSUMETARGETDEFS DISCARDFILE /u01/ogg/dirrpt/intrep.dsc, APPEND, MEGABYTES 100 WILDCARDRESOLVE DYNAMIC Dynamicresolution DDL include mapped MAP test.*, TARGET test.*; 2.4、啟動各個(gè)進(jìn)程 源： GGSCI>start intext GGSCI>start intdp 目標(biāo)： GGSCI>start 2.5 驗(yàn)證： 源端：在test用戶下創(chuàng)建 一個(gè)表t $sqlplus / as sysdba >create table t(id number);
--看有沒有捕獲到
目標(biāo)端：
|
|
|
