一、Goldengate雙向復(fù)制配置
站在用戶的角度思考問題，與客戶深入溝通，找到尼河口網(wǎng)站設(shè)計(jì)與尼河口網(wǎng)站推廣的解決方案，憑借多年的經(jīng)驗(yàn)，讓設(shè)計(jì)與互聯(lián)網(wǎng)技術(shù)結(jié)合，創(chuàng)造個(gè)性化、用戶體驗(yàn)好的作品，建站類型包括：成都網(wǎng)站設(shè)計(jì)、成都網(wǎng)站制作、外貿(mào)網(wǎng)站建設(shè)、企業(yè)官網(wǎng)、英文網(wǎng)站、手機(jī)端網(wǎng)站、網(wǎng)站推廣、域名與空間、網(wǎng)站空間、企業(yè)郵箱。業(yè)務(wù)覆蓋尼河口地區(qū)。
1.1.在進(jìn)行如下配置之前，先在源數(shù)據(jù)庫source system（原來的目標(biāo)數(shù)據(jù)庫）端
添加輔助的redolog配置：
SQL>alter database add supplemental log data;
SQL>alter system switch logfile;
SQL>alter database force logging;
1.2.在原Source和原Target分別用scott用戶創(chuàng)建一張emp_ogg表
SQL> create table emp_ogg as select * from emp where 1=0;//原source庫建立表但不插入數(shù)據(jù)
Table created.
單項(xiàng)復(fù)制時(shí)Source端是EINI_1進(jìn)程，Target端是RINI_1進(jìn)程,雙向復(fù)制時(shí)反之，以便完成兩表的初始化同步，配置如下：
原Source
GGSCI (gc2) 36> ADD REPLICAT RINI_1, SPECIALRUN
REPLICAT added.
GGSCI (gc2) 43> EDIT PARAMS RINI_1
-- GoldenGate Initial Load Delivery
--
REPLICAT RINI_1
SETENV (NLS_LANG=AMERICAN_AMERICA.ZHS16GBK)
ASSUMETARGETDEFS
USERID ogg, PASSWORD ogg
DISCARDFILE ./dirrpt/RINIaa.dsc, PURGE
MAP scott.*, TARGET scott.*;
原Target
GGSCI (oraclelinux54.cuug.net) 10> ADD EXTRACT EINI_1, SOURCEISTABLE
EXTRACT added.
GGSCI (oraclelinux54.cuug.net) 11> INFO EXTRACT *, TASKS
EXTRACT EINI_1 Initialized 2014-08-12 23:05 Status STOPPED
Checkpoint Lag Not Available
Log Read Checkpoint Not Available
First Record Record 0
Task SOURCEISTABLE
GGSCI (oraclelinux54.cuug.net) 12> EDIT PARAMS EINI_1
-- GoldenGate Initial Data Capture
-- for EMP_OGG and DEPT_OGG
--
EXTRACT EINI_1
SETENV (NLS_LANG=AMERICAN_AMERICA.ZHS16GBK)
USERID ogg, PASSWORD ogg
RMTHOSTgc2, MGRPORT 7809
RMTTASK REPLICAT, GROUP RINI_1
TABLE scott.EMP_OGG; //這里我們只為初始化scott用戶下的emp_ogg表
GGSCI (oraclelinux54.cuug.net) 19> START EXTRACT EINI_1
Sending START request to MANAGER ...
EXTRACT EINI_1 starting
GGSCI (oraclelinux54.cuug.net) 20> VIEW REPORT EINI_1
……...
***********************************************************************
* ** Run Time Statistics ** *
***********************************************************************
Report at 2014-08-12 23:11:04 (activity since 2014-08-12 23:10:59)
Output to RINI_1:
From Table SCOTT.EMP_OGG:
# inserts: 14 //可以看出兩邊已經(jīng)同步成功
# updates: 0
# deletes: 0
# discards: 0
1.3.在ogg環(huán)境下，添加日志跟蹤：
GGSCI (oraclelinux54.cuug.net)DBLOGIN USERID ogg, PASSWORD ogg
GGSCI (oraclelinux54.cuug.net)ADD TRANDATA scott.* //這里為了試驗(yàn)方便，我們?cè)O(shè)置為scott下全部表均設(shè)為可同步狀態(tài)
2013-08-13 03:21:18 GGS WARNING 109 No unique key is defined for table EMP_OGG. All viable columns will be used to represent the key, but may not guarantee uniqueness. KEYCOLS may be used to define the key.
2013-08-13 03:21:18 GGS WARNING 301 Failed to add supplemental log group on table SCOTT.EMP_OGG due to ORA-01031: insufficient privileges, SQL ALTER TABLE "SCOTT"."EMP_OGG" ADD SUPPLEMENTAL LOG GROUP "GGS_EMP_OGG_74686" ("EMPNO","ENAME","JOB","MGR","HIREDATE","SAL","COMM","DEPTNO") ALWAYS /* GOLDENGATE_DDL_REPLICATION */.
解決辦法：
SQL>alter table emp_ogg add constraint emp_ogg_pk primary key(empno);
SQL> grant alter any table to ogg;
Grant succeeded.
1.4.配置extract
GGSCI (gc2) 71>EDIT PARAMS EORA_1 //編輯前先停止進(jìn)程
EXTRACT EORA_1
SETENV (NLS_LANG=AMERICAN_AMERICA.ZHS16GBK)
USERID ogg, PASSWORD ogg
EXTTRAIL ./dirdat/aa
TABLE scott.*;
DDL INCLUDE OBJNAME "scott.*"
TRANLOGOPTIONS EXCLUDEUSER ogg //雙向復(fù)制關(guān)鍵
GGSCI (oraclelinux54.cuug.net) 74>EDIT PARAMS EORA_1 //編輯前先停止進(jìn)程
EXTRACT EORA_1
SETENV (NLS_LANG=AMERICAN_AMERICA.ZHS16GBK)
USERID ogg, PASSWORD ogg
TRANLOGOPTIONS EXCLUDEUSER ogg
EXTTRAIL ./dirdat/aa
TABLE scott.*;
DDL INCLUDE OBJNAME "scott.*"
GGSCI (雙節(jié)點(diǎn)執(zhí)行)> ADD EXTRACT EORA_1, TRANLOG, BEGIN NOW
注：
ADD EXTRACT EORA_1:添加EXTRACT進(jìn)程，這就是一直運(yùn)行，一段停止redo里的日志就沒人去抓取了
TRANLOG, BEGIN NOW：現(xiàn)在開始同步日志，也可以用異步，那就要另外配置
EXTRACT added.
GGSCI (雙節(jié)點(diǎn)執(zhí)行)> ADD EXTTRAIL ./dirdat/aa, EXTRACT EORA_1, MEGABYTES 5 //添加跟蹤文件給EORA_1用，大小為5M
EXTTRAIL added.
GGSCI (雙節(jié)點(diǎn)執(zhí)行)>START EXTRACT EORA_1
1.5.配置pump進(jìn)程
GGSCI (gc2) 72> EDIT PARAMS PORA_1 //編輯前先停止進(jìn)程
EXTRACT PORA_1
SETENV (NLS_LANG=AMERICAN_AMERICA.ZHS16GBK)
PASSTHRU
RMTHOSToraclelinux54.cuug.net, MGRPORT 7809
RMTTRAIL /u01/app/ogg/dirdat/pa
TABLE scott.*;
GGSCI (oraclelinux54.cuug.net) 75> EDIT PARAMS PORA_1 //編輯前先停止進(jìn)程
EXTRACT PORA_1
SETENV (NLS_LANG=AMERICAN_AMERICA.ZHS16GBK)
PASSTHRU
RMTHOSTgc2, MGRPORT 7809
RMTTRAIL ./dirdat/pa
TABLE scott.*;
GGSCI (雙節(jié)點(diǎn)執(zhí)行)> ADD EXTRACT PORA_1, EXTTRAILSOURCE ./dirdat/aa // 告訴PORA_1要傳送哪個(gè)路徑下的跟蹤信息
EXTRACT added.
GGSCI (雙節(jié)點(diǎn)執(zhí)行)> ADD RMTTRAIL ./dirdat/pa, EXTRACT PORA_1, MEGABYTES 5 //表示把捕獲到的信息傳送到遠(yuǎn)程的哪個(gè)目錄的文件中
RMTTRAIL added.
GGSCI (雙節(jié)點(diǎn)執(zhí)行)> START EXTRACT PORA_1
配置replicate
GGSCI (gc2) 74> EDIT PARAM RORA_1
REPLICAT RORA_1
SETENV (NLS_LANG=AMERICAN_AMERICA.ZHS16GBK)
USERID ogg, PASSWORD ogg
HANDLECOLLISIONS
ASSUMETARGETDEFS
DISCARDFILE ./dirrpt/RORA_aa.DSC, PURGE
DDL INCLUDE ALL
DDLERROR DEFAULT IGNORE RETRYOP MAXRETRIES 3 RETRYDELAY 5
DDLERROR DEFAULT DISCARD
DDLERROR DEFAULT IGNORE RETRYOP
MAP scott.*, TARGET scott.*;
GGSCI (oraclelinux54.cuug.net) 87> EDIT PARAM RORA_1
REPLICAT RORA_1
SETENV (NLS_LANG=AMERICAN_AMERICA.ZHS16GBK)
USERID ogg, PASSWORD ogg
HANDLECOLLISIONS
ASSUMETARGETDEFS
DISCARDFILE ./dirrpt/RORA_aa.DSC, PURGE
DDL INCLUDE ALL
DDLERROR DEFAULT IGNORE RETRYOP MAXRETRIES 3 RETRYDELAY 5
DDLERROR DEFAULT DISCARD
DDLERROR DEFAULT IGNORE RETRYOP
MAP scott.*, TARGET scott.*;
GGSCI (雙節(jié)點(diǎn)執(zhí)行) 87>ADD REPLICAT RORA_1, EXTTRAIL ./dirdat/pa //表示從哪里提取傳送過來的信息
GGSCI (雙節(jié)點(diǎn)執(zhí)行) 87>START REPLICAT RORA_1
SQL> conn / as sysdba
Connected.
SQL> grant insert on scott.emp_ogg to ogg;
Grant succeeded.
SQL> conn / as sysdba
Connected.
SQL> grant delete on scott.emp_ogg to ogg;
Grant succeeded.
SQL>grant update on scott.emp_ogg to ogg;
Grant succeeded.
二、配置checkpoint
GGSCI (oraclelinux54.cuug.net) EDIT PARAMS ./GLOBALS
CHECKPOINTTABLE ogg.ggschkpt
GGSCI (oraclelinux54.cuug.net) exit
GGSCI (oraclelinux54.cuug.net) DBLOGIN USERID ogg, PASSWORD ogg
Successfully logged into database.
GGSCI (oraclelinux54.cuug.net) ADD CHECKPOINTTABLE //如果單項(xiàng)復(fù)制已經(jīng)做了，這里不用再添加
應(yīng)該看到，兩節(jié)點(diǎn)的進(jìn)程都為Running狀態(tài)
GGSCI (gc2) 73> info all
Program Status Group Lag Time Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING EORA_1 00:00:00 00:00:02
EXTRACT RUNNING PORA_1 00:00:00 00:00:04
REPLICAT RUNNING RORA_1 00:00:00 00:00:01
GGSCI (oraclelinux54.cuug.net) 88> info all
Program Status Group Lag Time Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING EORA_1 00:00:00 00:00:09
EXTRACT RUNNING PORA_1 00:00:00 00:00:02
REPLICAT RUNNING RORA_1 00:00:00 00:00:05
三、測(cè)試雙向傳送結(jié)果
3.1.source到target
Sourcesystem
SQL> INSERT INTO emp_ogg VALUES(8000,'HL','CLERK',7902,'12-DEC-80',800,100,20);
1 row created.
SQL> commit;
Commit complete.
SQL> select * from emp_ogg;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
8000 HL CLERK 7902 12-DEC-80 800 100 20
Target system
SQL> select * from emp_ogg;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- ---------- ---------- --------------------- ---------- ---------- ----------
8000 HL CLERK 7902 1980-12-12 : 00:00:00 800 100 20
3.2target 到 source
Target system
SQL> select * from tcustmer;
CUST NAME CITY ST
---- ------------------------------ -------------------- --
WILL BG SOFTWARE CO. SEATTLE WA
JANE ROCKY FLYER INC. DENVER CO
SQL> insert into tcustmer values ('HL','zai','cuug','en');
1 row created.
SQL> commit;
Commit complete.
Source system
SQL> select * from tcustmer;
CUST NAME CITY ST
---- ------------------------------ -------------------- --
WILL BG SOFTWARE CO. SEATTLE WA
JANE ROCKY FLYER INC. DENVER CO
helei zai cuug en
四、支持DDL復(fù)制配置
4.1在兩個(gè)節(jié)點(diǎn)執(zhí)行執(zhí)行DDL同步腳本命令：
先進(jìn)入goldengate軟件安裝目錄，以SYSDBA身份登錄oracle執(zhí)行以下腳本，執(zhí)行腳本過程中，需要輸入的用戶全部是ogg，安裝模式為INITIALSETUP，如果數(shù)據(jù)字典或者某些內(nèi)部的包有錯(cuò)誤，則需要運(yùn)行catalog.sql和catproc.sql腳本。
SQL>show parameter recyclebin;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
recyclebin string off 必須是off
這里執(zhí)行第二個(gè)腳本@ddl_setup時(shí)會(huì)報(bào)一個(gè)recyclebin的錯(cuò)誤，而且只能在Pfile中修改recyclebin=off后用pfile起庫，執(zhí)行兩個(gè)清除腳本，再重新運(yùn)行一下腳本
SQL>@marker_setup
SQL>@ddl_setup
SQL>@role_setup
SQL>grant GGS_GGSUSER_ROLE to ogg;
SQL>@ddl_enable
如果某項(xiàng)腳本執(zhí)行錯(cuò)誤，需要重新執(zhí)行時(shí)，先要執(zhí)行清除的腳本：ddl_remove.sql和marker_remove.sql
重慶分公司
重慶分公司
