GOLDENGATE安裝配置與複制流搭建_DG端抽取支援DDL版
1、配置場景
OGG版本11.2.1.0.1
2、OGG軟體安裝
2.1 源端和目标端建立OGG安裝目錄與授權
#mkdir /u01/ogg
#chown –R oracle:oinstall /u01/ogg
#chmod –R 777 /u01/ogg
2.2 源端和目标端OS層參數調整與配置環境變量
(1)OS參數調整
#vi /etc/sysctl.conf
net.core.rmem_max=8388608 net.core.wmem_default=8388608 net.core.wmem_max=8388608 |
#sysctl -p
(2)設定環境變量
#su – oracle
[[email protected]~]$ vi .bash_profile
添加以下内容:
export OGG=/u01/ogg export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/u01/ogg |
[[email protected]~]$ source .bash_profile
2.3 源端和目标端安裝OGG軟體
(1) 将ogg112101_fbo_ggs_Linux_x64_ora11g_64bit.zip軟體複制到/u01/ogg目錄内
(2) 解壓縮軟體
[[email protected] ~]$unzip ogg112101_fbo_ggs_Linux_x64_ora11g_64bit.zip
[[email protected] ~]$tar xvffbo_ggs_Linux_x64_ora11g_64bit.tar
2.4 源端和目标端資料庫内建立OGG專用表空間及使用者
SQL>create tablespace goldengate datafile '/dba/oracle/oradata/single/goldengate_01.dbf' size1G;
SQL>create user goldengate identified by goldengate default tablespace goldengate;
SQL>grant dba to goldengate;
2.5源端資料庫建立測試表與插入測試資料
(如果是生産環境,此步可以跳過)
(1) 建立測試表TABLE GOLDENGATE.OGG_UPG
create table GOLDENGATE.OGG_UPG ( pr_id NUMBER(10)notnull, t_name VARCHAR2(20), sal NUMBER(10), insert_time DATE primary key ) tablespace GOLDENGATE |
(2) 建立測試用的sequence
create sequence GOLDENGATE.SEQ_OGG_T01 minvalue 1 maxvalue 9999999999999999999999999999 start with1 increment by 1 cache 20; |
(3)插入資料
declare i number:=1; begin loop insert into goldengate.OGG_UPG values(goldengate.seq_ogg_t01.nextval,'ogg_test1',i,sysdate); commit; i:=i+1; exit when i=10001; end loop; end; / |
3、源端資料庫與表配置修改
3.1 開啟資料庫最小級别追加日志
SQL>alter database add supplemental log data;
SQL>altersystem switch logfile;
SQL>exit
3.2 開啟需要同步表的表級追加日志
[[email protected]~]$./ggsci
GGSCI>dbloginuserid goldengate, password goldengate
GGSCI>addtrandata goldengate.ogg_upg
4、配置goldengate支援DDL
如果是ORACLE 10g,需要關閉ORACLE資料庫的recycle bin,11G以上不需要關閉。
$cd$OGG
$sqlplus“/as sysdba”
SQL>@marker_setup.sql
SQL>@ddl_setup.sql
SQL>@role_setup.sql
SQL>grant ggs_ggsuser_role to goldengate
SQL>@ddl_enable.sql
SQL>@ddl_pin goldengate
4、OGG源端配置
4.1 建立subdirs
GGSCI>create subdirs
4.2 建立MGR
GGSCI>editparams mgr
Port 7809 dynamicportlist 7800-8000 --autorestart extract *,waitminutes 30,resetminutes 5 lagreporthours 1 laginfominutes 20 lagcriticalminutes 60 purgeoldextracts ./dirdat/tr*,usecheckpoints,minkeepdays 10 |
GGSCI>start mgr
4.3 建立extract程序
GGSCI>add extexttr, tranlog, begin now
GGSCI>addexttrail ./dirdat/tr, ext exttr, megabytes 200
GGSCI>editparams exttr
extract exttr setenv (NLS_LANG = AMERICAN_AMERICA.AL32UTF8) userid goldengate, password goldengate REPORT AT 01:59 reportrollover at 02:00 TRANLOGOPTIONS CONVERTUCS2CLOBS THREADOPTIONS MAXCOMMITPROPAGATIONDELAY 90000 IOLATENCY 100000 tranlogoptions dblogreader --此條為支援從ASM中讀取redo log tranlogoptions altarchivelogdest primary instance orcl1 /u01/archive, altarchivelogdest instance orcl2 /u01/archive discardfile ./dirrpt/exttr.dsc, append, megabytes 1000 gettruncates --warnlongtrans 2h, checkintervals 3m DDL INCLUDE OBJNAME "goldengate.*" EXCLUDE INSTRCOMMENTS 'ggh_notsync' exttrail ./dirdat/tr numfiles 2000 dynamicresolution TABLEGOLDENGATE.OGG_UPG; |
特别知識點說明:
EXCLUDE INSTRCOMMENTS 'ggh_notsync'說明:表示,語句帶 “ggh_notsync”關鍵字的語句排除不抽取,如altertableb_rangedroppartition q1 ; 由于該語句中帶有ggh_notsync,是以排除不抽取。
這種應用場景非常實用,如有一張分區表,源端隻想保留兩個分區,曆史的資料分區在目标端都保留,那麼,就可以在源端drop分區的語句上加上排除的辨別,在源端删除分區的動作就不會同步到目标端了。
4.4 建立dataPump程序
GGSCI>add ext dpetr,exttrailsource ./dirdat/tr
GGSCI>add rmttrail ./dirdat/tr, ext dpetr, megabytes200
GGSCI>edit param dpetr
extract dpetr setenv (NLS_LANG = AMERICAN_AMERICA.AL32UTF8) userid goldengate, password goldengate REPORT AT 01:59 reportrollover at 02:00 rmthost 192.168.1.220, mgrport 7809, compress rmttrail ./dirdat/tr dynamicresolution numfiles 2000 gettruncates TABLE GOLDENGATE.OGG_UPG; |
GGSCI>start param dpetr
5、目标端OGG配置
5.1 建立subdirs
GGSCI>create subdirs
5.2 建立MGR
GGSCI> edit params mgr
Port 7809 dynamicportlist 7800-8000 autorestart replicat *,waitminutes 5,resetminutes 5 lagreporthours 1 laginfominutes 20 lagcriticalminutes 60 purgeoldextracts ./dirdat/tr*,usecheckpoints,minkeepdays 10 |
GGSCI>start mgr
5.3 建立replicat程序
GGSCI>dbloginuserid goldengate, password goldengate
GGSCI>addcheckpointtable goldengate.checkpoint_reptr_01
GGSCI>addreplicat reptr, exttrail ./dirdat/tr, checkpointtablegoldengate.checkpoint_reptr_01
GGSCI>edit params reptr
replicat reptr setenv (NLS_LANG = AMERICAN_AMERICA.AL32UTF8) userid goldengate, password goldengate sqlexec "Alter session set constraints=deferred" REPORT AT 01:59 reportrollover at 02:00 discardrollover on friday --handlecollisions reperror default,abend discardfile ./dirrpt/reptr.dsc,append, megabytes 1000 assumetargetdefs checksequencevalue allownoopupdates dynamicresolution numfiles 2000 GETTRUNCATES batchsql BATCHESPERQUEUE 100, OPSPERBATCH 8000 grouptransops 10000 maxtransops 10000 DDL INCLUDE ALL EXCLUDE INSTRCOMMENTS 'ggh_notsync' ddlerror default ignore retryop maxretries 3 retrydelay 5 ddloptions report MAP GOLDENGATE.OGG_UPG, TARGET GOLDENGATE.OGG_UPG; |
建立完replicat程序後,千萬不要啟動
6、資料初始化
6.1 停用源端資料庫的JOB
Sql>alter system set job_queue_processes=0 scope=both; --先關閉job隊列,防止後續的job再啟動. Sql>alter system set aq_tm_processes=0 scope=both; --關閉進階隊列 Sql>select count(*) from dba_jobs_running; --檢視系統是否存在有job正在執行,如果還有JOB在運作,則進行Kill操作 |
6.2停止前端系統應用
(防止有事務啟動後,在取SCN時還未結束,由于GOLDENGATE抽取程序,隻會抽取在extract程序啟動之後開始的事務,該點很重要。)
6.3殺掉所有事務
(1)查詢還在執行的事務
select * from gv$transaction;
(2)kill 目前正在執行的事務的session
6.4停掉listener
$lsnrctl stop
(防止新的連接配接連上來建立新事務操作表資料)
6.5查詢源端資料庫的目前SCN号
SQL>select dbms_flashback.get_system_change_number from dual;
記錄下SCN号:2553440
6.6導出資料
$expdpsystem/XXX directory=expdp_dir dumpfile=tableXXX.dmp filesize=30Glogfile=expdp_XXXX.log flashback_scn=2553440 tables=GOLDENGATE.OGG_UPG
6.7恢複源端資料庫的JOB參數
Sql>alter system set job_queue_processes=20 scope=both; --根據實際值進行恢複. Sql>alter system set aq_tm_processes=1 scope=both; |
6.8目标端資料庫導入資料
$imppdp system/XXX directory=expdp_dir dumpfile=tableXXX.dmp logfile=impdp_XXXX.log |
7、啟動目标端的replicat程序
GGSCI>startreptr,aftercsn 2553440
8、驗證OGG能正常同步資料
8.1 在目标資料庫中查詢GOLDENGATE.OGG_UPG表資料量
SQL> select count(*) from goldengate.ogg_upg; COUNT(*) ---------- 10000 |
8.2 在源端資料庫上的GOLDENGATE.OGG_UPG表中再插入一萬行資料
declare i number:=1; begin loop insert into goldengate.OGG_UPGvalues(goldengate.seq_ogg_t01.nextval,'ogg_test1',i,sysdate); commit; i:=i+1; exit when i=10001; end loop; end; / |
8.2 在目标資料庫上查詢GOLDENGATE.OGG_UPG表資料是否增長
SQL> select count(*) from goldengate.ogg_upg; COUNT(*) ---------- 20000 |
配置成功,并可以正常同步。
本文作者:黎俊傑(網名:踩點),從事”系統架構、作業系統、儲存設備、資料庫、中間件、應用程式“六個層面系統性的性能優化工作
歡迎加入 系統性能優化專業群,共同探讨性能優化技術。群号:258187244