alter database..."/>
溫馨提示×

溫馨提示×

您好,登錄后才能下訂單哦!

密碼登錄×
登錄注冊×
其他方式登錄
點擊 登錄注冊 即表示同意《億速云用戶服務(wù)條款》

Goldengate雙向復(fù)制配置

發(fā)布時間:2020-08-10 23:50:54 來源:網(wǎng)絡(luò) 閱讀:1004 作者:dbapower 欄目:關(guān)系型數(shù)據(jù)庫

 

一、Goldengate雙向復(fù)制配置

 

1.1.在進行如下配置之前,先在源數(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.SourceTarget分別用scott用戶創(chuàng)建一張emp_ogg

 SQL> create table emp_ogg as select * from emp where 1=0; //source庫建立表但不插入數(shù)據(jù)

 

Table created.

單項復(fù)制時Source端是EINI_1進程,Target端是RINI_1進程,雙向復(fù)制時反之,以便完成兩表的初始化同步,配置如下:

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

RMTHOST gc2, 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.*        //這里為了試驗方便,我們設(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     //編輯前先停止進程

 

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   //編輯前先停止進程

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é)點執(zhí)行)>  ADD EXTRACT EORA_1, TRANLOG, BEGIN NOW

注:

ADD EXTRACT EORA_1:添加EXTRACT進程,這就是一直運行,一段停止redo 里的日志就沒人去抓取了

TRANLOG, BEGIN NOW:現(xiàn)在開始同步日志,也可以用異步,那就要另外配置

EXTRACT added.

GGSCI (雙節(jié)點執(zhí)行)>  ADD EXTTRAIL ./dirdat/aa, EXTRACT EORA_1, MEGABYTES 5             //添加跟蹤文件給EORA_1用,大小為5M

EXTTRAIL added.

GGSCI (雙節(jié)點執(zhí)行)> START EXTRACT EORA_1

 

1.5.配置pump進程

GGSCI (gc2) 72> EDIT PARAMS PORA_1   //編輯前先停止進程

EXTRACT PORA_1

SETENV (NLS_LANG=AMERICAN_AMERICA.ZHS16GBK)

PASSTHRU

RMTHOST oraclelinux54.cuug.net, MGRPORT 7809

RMTTRAIL /u01/app/ogg/dirdat/pa

TABLE scott.*;

 

GGSCI (oraclelinux54.cuug.net) 75> EDIT PARAMS PORA_1 //編輯前先停止進程

EXTRACT PORA_1

SETENV (NLS_LANG=AMERICAN_AMERICA.ZHS16GBK)

PASSTHRU

RMTHOST gc2, MGRPORT 7809

RMTTRAIL ./dirdat/pa

TABLE scott.*;

 

GGSCI (雙節(jié)點執(zhí)行)>  ADD EXTRACT PORA_1, EXTTRAILSOURCE ./dirdat/aa           //  告訴PORA_1要傳送哪個路徑下的跟蹤信息

EXTRACT added.

GGSCI (雙節(jié)點執(zhí)行)>  ADD RMTTRAIL ./dirdat/pa, EXTRACT PORA_1, MEGABYTES 5       // 表示把捕獲到的信息傳送到遠程的哪個目錄的文件中

RMTTRAIL added.

GGSCI (雙節(jié)點執(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é)點執(zhí)行) 87> ADD REPLICAT RORA_1, EXTTRAIL ./dirdat/pa       //表示從哪里提取傳送過來的信息

GGSCI (雙節(jié)點執(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    //如果單項復(fù)制已經(jīng)做了,這里不用再添加

 

 

應(yīng)該看到,兩節(jié)點的進程都為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 

 

三、測試雙向傳送結(jié)果

3.1.source target

Source system

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在兩個節(jié)點執(zhí)行執(zhí)行DDL同步腳本命令:

 

先進入goldengate軟件安裝目錄,以SYSDBA身份登錄oracle執(zhí)行以下腳本,執(zhí)行腳本過程中,需要輸入的用戶全部是ogg,安裝模式為INITIALSETUP,如果數(shù)據(jù)字典或者某些內(nèi)部的包有錯誤,則需要運行catalog.sqlcatproc.sql腳本。

SQL>show parameter recyclebin;

 

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

recyclebin                           string      off                                                                 必須是off

 

 這里執(zhí)行第二個腳本@ddl_setup時會報一個recyclebin的錯誤,而且只能在Pfile中修改recyclebin=off后用pfile起庫,執(zhí)行兩個清除腳本,再重新運行一下腳本

SQL>@marker_setup

SQL>@ddl_setup

SQL>@role_setup

SQL>grant GGS_GGSUSER_ROLE to ogg;

SQL>@ddl_enable

 

如果某項腳本執(zhí)行錯誤,需要重新執(zhí)行時,先要執(zhí)行清除的腳本:ddl_remove.sqlmarker_remove.sql


向AI問一下細節(jié)

免責(zé)聲明:本站發(fā)布的內(nèi)容(圖片、視頻和文字)以原創(chuàng)、轉(zhuǎn)載和分享為主,文章觀點不代表本網(wǎng)站立場,如果涉及侵權(quán)請聯(lián)系站長郵箱:is@yisu.com進行舉報,并提供相關(guān)證據(jù),一經(jīng)查實,將立刻刪除涉嫌侵權(quán)內(nèi)容。

AI