【OGG】OGG簡單配置雙向複制(三)

各位技術愛好者，看完本文後，你可以掌握如下的技能，也可以學到一些其它你所不知道的知識，~O(∩_∩)O~：

① OGG的雙向實時複制功能

注意：本篇BLOG中代碼部分需要特别關注的地方我都用***背景和紅色字型來表示，比如下邊的例子中，thread 1的最大歸檔日志号為33，thread 2的最大歸檔日志号為43是需要特别關注的地方。

List of Archived Logs in backup set 11

Thrd Seq Low SCN Low Time Next SCN Next Time

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

1 32 1621589 2015-05-29 11:09:52 1625242 2015-05-29 11:15:48

1 33 1625242 2015-05-29 11:15:48 1625293 2015-05-29 11:15:58

2 42 1613951 2015-05-29 10:41:18 1625245 2015-05-29 11:15:49

2 43 1625245 2015-05-29 11:15:49 1625253 2015-05-29 11:15:53

本文如有錯誤或不完善的地方請大家多多指正，ITPUB留言或QQ皆可，您的批評指正是我寫作的最大動力。

項目

source db

target db

db 類型

單執行個體

db version

11.2.0.3

db 存儲

FS type

ORACLE_SID

ogg1

ogg2

db_name

主機IP位址：

192.168.59.129

192.168.59.130

OS版本及kernel版本

RHEL6.5 64位，2.6.32-504.16.2.el6.x86_64

OGG版本

11.2.1.0.1 64位

OS hostname

orcltest

rhel6_lhr

在完成ogg的單向複制配置後，自然會想着向前推進一層，實作雙向複制；在實際應用中，雙向複制面臨着許多問題，主要有如下幾點：

1. 如果兩個庫同時更新同一條記錄如何處理？

2. 如果網絡出現失敗如何處理？

3. 如果資料不同步後如何修複？

本文介紹如何在前文的基礎上簡單實作ogg的雙向複制！雙向複制一般用于雙業務中心環境下，目前筆者的生産環境中未使用到ogg，ogg系列的文章隻是從技術上提前做一個準備，因而許多問題的細節未能理清，後續将繼續學習研究！在開始之前，請先配置好db1-db2的單向複制(include ddl replicat)!

實作OGG1和OGG2的雙向複制功能。

注意：本文OGG1和OGG2互為source和target，因而直接采用OGG1和OGG2來辨別兩台資料庫伺服器

[oracle@orcltest gg11]$ ggsci

Oracle GoldenGate Command Interpreter for Oracle

Version 11.2.1.0.1 OGGCORE_11.2.1.0.1_PLATFORMS_120423.0230_FBO

Linux, x64, 64bit (optimized), Oracle 11g on Apr 23 2012 08:32:14

GGSCI (orcltest) 1> view params ./GLOBALS

GGSCHEMA ggusr

GGSCI (orcltest) 2> edit params ./GLOBALS

checkpointtable ggusr.rep_demo_ckpt

。。。。。。。。。。。。。。。。

"./GLOBALS" 2L, 51C written

GGSCI (orcltest) 3> view params ./GLOBALS

GGSCI (orcltest) 4>

GGSCI (orcltest) 4> dblogin userid ggusr@ogg1,password lhr

Successfully logged into database.

GGSCI (orcltest) 5> add checkpointtable ggusr.rep_demo_ckpt

Successfully created checkpoint table ggusr.rep_demo_ckpt.

GGSCI (orcltest) 6> add checkpointtable ggusr.rep_demo_ckpt

ERROR: OCI Error ORA-00955: name is already used by an existing object (status = 955). Creating checkpoint table ggusr.rep_demo_ckpt, SQL <CREATE TABLE ggusr.rep_demo_ckpt ( group_name VARCHAR2(8) NOT NULL, group_key NUMBER(19) NOT NULL, seqno NUMBER(10), rba NUMBER(19) NOT NULL, audit_ts VARCHAR2(29), create_ts DATE NOT NULL, last_update_ts DATE NOT NULL, current_dir VARCHAR2(255) NOT NULL, log_csn VARCHAR2(129), log_xid VARCHAR2(129), log_cmplt_csn VARCHAR2(129), log_cmplt_xids VARCHAR2(2000), version NUMBER(3), PRIMARY KEY (group_name, group_key))>.

GGSCI (orcltest) 7>

[oracle@rhel6_lhr gg11]$ ggsci

GGSCI (rhel6_lhr) 1> view params ./GLOBALS

GGSCI (rhel6_lhr) 2> edit params ./GLOBALS

GGSCI (rhel6_lhr) 3> view params ./GLOBALS

GGSCI (rhel6_lhr) 4>

[oracle@orcltest ~]$ cd $OGG_HOME

[oracle@orcltest gg11]$ sqlplus sys/lhr@ogg2 as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Wed Jun 10 17:10:45 2015

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select name,SUPPLEMENTAL_LOG_DATA_MIN, FORCE_LOGGING from v$database;

NAME SUPPLEME FOR

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

OGG2 NO NO

SQL> alter database add supplemental log data ;

Database altered.

SQL> archive log list;

Database log mode Archive Mode

Automatic archival Enabled

Archive destination USE_DB_RECOVERY_FILE_DEST

Oldest online log sequence 7

Next log sequence to archive 9

Current log sequence 9

SQL>

SQL> grant execute on utl_file to ggusr;

Grant succeeded.

SQL> @marker_setup.sql

Marker setup script

You will be prompted for the name of a schema for the Oracle GoldenGate database objects.

NOTE: The schema must be created prior to running this script.

NOTE: Stop all DDL replication before starting this installation.

Enter Oracle GoldenGate schema name:ggusr

Marker setup table script complete, running verification script...

Please enter the name of a schema for the GoldenGate database objects:

Setting schema name to GGUSR

MARKER TABLE

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

MARKER SEQUENCE

Script complete.

SQL> @ddl_setup.sql

Oracle GoldenGate DDL Replication setup script

Verifying that current user has privileges to install DDL Replication...

NOTE: For an Oracle 10g source, the system recycle bin must be disabled. For Oracle 11g and later, it can be enabled.

Working, please wait ...

Spooling to file ddl_setup_spool.txt

Checking for sessions that are holding locks on Oracle Golden Gate metadata tables ...

Check complete.

Using GGUSR as a Oracle GoldenGate schema name.

DDL replication setup script complete, running verification script...

CLEAR_TRACE STATUS:

Line/pos Error

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

No errors No errors

CREATE_TRACE STATUS:

TRACE_PUT_LINE STATUS:

INITIAL_SETUP STATUS:

DDLVERSIONSPECIFIC PACKAGE STATUS:

DDLREPLICATION PACKAGE STATUS:

DDLREPLICATION PACKAGE BODY STATUS:

DDL IGNORE TABLE

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

DDL IGNORE LOG TABLE

DDLAUX PACKAGE STATUS:

DDLAUX PACKAGE BODY STATUS:

SYS.DDLCTXINFO PACKAGE STATUS:

SYS.DDLCTXINFO PACKAGE BODY STATUS:

DDL HISTORY TABLE

DDL HISTORY TABLE(1)

DDL DUMP TABLES

DDL DUMP COLUMNS

DDL DUMP LOG GROUPS

DDL DUMP PARTITIONS

DDL DUMP PRIMARY KEYS

DDL SEQUENCE

GGS_TEMP_COLS

GGS_TEMP_UK

DDL TRIGGER CODE STATUS:

DDL TRIGGER INSTALL STATUS

DDL TRIGGER RUNNING STATUS

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

ENABLED

STAYMETADATA IN TRIGGER

OFF

DDL TRIGGER SQL TRACING

DDL TRIGGER TRACE LEVEL

LOCATION OF DDL TRACE FILE

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

/u01/app/oracle/diag/rdbms/ogg2/ogg2/trace/ggs_ddl_trace.log

Analyzing installation status...

STATUS OF DDL REPLICATION

SUCCESSFUL installation of DDL Replication software components

SQL> @role_setup.sql

GGS Role setup script

This script will drop and recreate the role GGS_GGSUSER_ROLE

To use a different role name, quit this script and then edit the params.sql script to change the gg_role parameter to the preferred name. (Do not run the script.)

You will be prompted for the name of a schema for the GoldenGate database objects.

Enter GoldenGate schema name:ggusr

Wrote file role_setup_set.txt

PL/SQL procedure successfully completed.

Role setup script complete

Grant this role to each user assigned to the Extract, GGSCI, and Manager processes, by using the following SQL command:

GRANT GGS_GGSUSER_ROLE TO <loggedUser>

where <loggedUser> is the user assigned to the GoldenGate processes.

SQL> GRANT GGS_GGSUSER_ROLE TO ggusr;

SQL> @ddl_enable.sql

Trigger altered.

SQL> @?/rdbms/admin/dbmspool.sql

Package created.

SQL> @ddl_pin.sql ggusr

SQL> exit

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

[oracle@orcltest gg11]$

[oracle@rhel6_lhr gg11]$ ggsci

GGSCI (rhel6_lhr) 1> dblogin userid ggusr@ogg2,password lhr

GGSCI (rhel6_lhr) 2> add trandata hr.*

Logging of supplemental redo data enabled for table HR.COUNTRIES.

Logging of supplemental redo data enabled for table HR.DEPARTMENTS.

Logging of supplemental redo data enabled for table HR.EMPLOYEES.

Logging of supplemental redo data enabled for table HR.JOBS.

Logging of supplemental redo data enabled for table HR.JOB_HISTORY.

Logging of supplemental redo data enabled for table HR.LOCATIONS.

Logging of supplemental redo data enabled for table HR.REGIONS.

Logging of supplemental redo data enabled for table HR.T1.

Logging of supplemental redo data enabled for table HR.T2.

2015-06-10 03:11:40 WARNING OGG-00869 No unique key is defined for table 'T3'. All viable columns will be used to represent the key, but may not guarantee uniqueness. KEYCOLS may be used to define the key.

Logging of supplemental redo data enabled for table HR.T3.

GGSCI (rhel6_lhr) 3> edit params eora_hr2

GGSCI (rhel6_lhr) 4> view params eora_hr2

extract eora_hr2

setenv (ORACLE_SID=ogg2)

setenv (ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1)

setenv (NLS_LANG=AMERICAN_AMERICA.ZHS16GBK)

ddl include all

userid ggusr,password lhr

tranlogoptions excludeuser ggusr //避免出現死循環複制，db1上的extract程序也需要進行此項設定

exttrail ./dirdat/ab

table hr.*;

GGSCI (rhel6_lhr) 4> add extract eora_hr2,tranlog,begin now

EXTRACT added.

GGSCI (rhel6_lhr) 5> add exttrail ./dirdat/ab,extract eora_hr2,megabytes 100

EXTTRAIL added.

GGSCI (rhel6_lhr) 6> start extract eora_hr2

Sending START request to MANAGER ...

EXTRACT EORA_HR2 starting

GGSCI (rhel6_lhr) 7> edit params pora_hr2

GGSCI (rhel6_lhr) 8> view params pora_hr2

extract pora_hr2

setenv (NLS_LANG=AMERICAN_AMERICA.ZHS16GBK)

passthru

rmthost 192.168.59.129,mgrport 7809

rmttrail ./dirdat/pb

GGSCI (rhel6_lhr) 9> add extract pora_hr2,exttrailsource ./dirdat/ab

EXTRACT added.

GGSCI (rhel6_lhr) 10> add rmttrail ./dirdat/pb extract pora_hr2,megabytes 100

RMTTRAIL added.

GGSCI (rhel6_lhr) 11> start extract pora_hr2

EXTRACT PORA_HR2 starting

GGSCI (rhel6_lhr) 12> info all

Program Status Group Lag at Chkpt Time Since Chkpt

MANAGER RUNNING

EXTRACT RUNNING EORA_HR2 00:00:00 00:04:16

EXTRACT RUNNING PORA_HR2 00:00:00 00:00:00

REPLICAT RUNNING RORA_HR 00:00:00 00:00:01

GGSCI (rhel6_lhr) 13>

OGG1上設定抽取程序參數，添加tranlogoptions excludeuser ggusr：

GGSCI (orcltest) 15> view params EORA_HR

extract eora_hr

setenv (ORACLE_SID=ogg1)

setenv (ORACLE_HOME=/u02/app/oracle/product/11.2.0/dbhome_1)

setenv (NLS_LANG=AMERICAN_AMERICA.ZHS16GBK)

tranlogoptions excludeuser ggusr

exttrail ./dirdat/hr

GGSCI (orcltest) 16>

GGSCI (orcltest) 1> info all

EXTRACT RUNNING EORA_HR 00:00:00 00:00:10

EXTRACT RUNNING PORA_HR 00:00:00 00:00:05

GGSCI (orcltest) 2> view params rora_hr2

ERROR: PARAMS file RORA_HR2 does not exist.

GGSCI (orcltest) 3> edit params rora_hr2

replicat rora_hr2

ddlerror default ignore retryop maxretries 3 retrydelay 5

handlecollisions

assumetargetdefs

discardfile ./dirrpt/rora_hr2.dsc,purge

map hr.* ,target hr.*;

。。。。。。。。。。。。。。。

"dirprm/rora_hr2.prm" [New] 11L, 345C written

GGSCI (orcltest) 4> view params rora_hr2

DDLOPTIONS REPORT

GGSCI (orcltest) 5> add replicat rora_hr2,exttrail ./dirdat/pb

REPLICAT added.

GGSCI (orcltest) 6> start replicat rora_hr2

REPLICAT RORA_HR2 starting

GGSCI (orcltest) 7> info all

EXTRACT RUNNING EORA_HR 00:00:00 00:00:00

EXTRACT RUNNING PORA_HR 00:00:00 00:00:09

REPLICAT RUNNING RORA_HR2 00:00:00 00:00:04

GGSCI (orcltest) 8>

一.3.6.1 ddl測試

[oracle@orcltest gg11]$ sqlplus hr/hr@ogg1

SQL*Plus: Release 11.2.0.3.0 Production on Wed Jun 10 18:07:48 2015

SQL> create table t5 as select * from dual;

Table created.

SQL> conn hr/hr@ogg2

Connected.

SQL> select count(1) from t5;

COUNT(1)

----------

SQL> alter table t5 add name varchar2(255);

Table altered.

SQL> conn hr/hr@ogg1

SQL> desc t5

Name Null? Type

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

DUMMY VARCHAR2(1)

NAME VARCHAR2(255)

DDL測試注意檢視ddl日志：

OGG2上的日志（/u01/app/oracle/diag/rdbms/ogg2/ogg2/trace/ggs_ddl_trace.log）：

SESS 470002-2015-06-10 18:11:50 : DDL : ************************* Start of log for DDL sequence [1508], v[ $Id: ddl_setup.sql /st_oggcore_11.2.1/8 2012/04/02 11:11:33 smijatov Exp $ ] trace level [0], owner schema of DDL package [GGUSR], objtype [TABLE] name [HR.T5]

SESS 470002-2015-06-10 18:11:50 : DDLTRACE1 : Before Trigger: point in execution = [1.0], objtype [TABLE] name [HR.T5]

SESS 470002-2015-06-10 18:11:50 : DDL : DDL operation [ create table t5 as select * from dual ], sequence [1508], DDL type [CREATE] TABLE, real object type [TABLE], validity [], object ID [], object [HR.T5], real object [HR.T5], base object schema [], base object name [], logged as [HR]

SESS 470002-2015-06-10 18:11:50 : DDL : Start SCN found [1112052]

SESS 470002-2015-06-10 18:11:50 : DDL : ------------------------- End of log for DDL sequence [1508]

SESS 450003-2015-06-10 18:12:38 : DDL : ************************* Start of log for DDL sequence [1509], v[ $Id: ddl_setup.sql /st_oggcore_11.2.1/8 2012/04/02 11:11:33 smijatov Exp $ ] trace level [0], owner schema of DDL package [GGUSR], objtype [TABLE] name [HR.T5]

SESS 450003-2015-06-10 18:12:38 : DDLTRACE1 : Before Trigger: point in execution = [1.0], objtype [TABLE] name [HR.T5]

SESS 450003-2015-06-10 18:12:38 : DDL : DDL operation [alter table hr."T5" add name varchar2(255) /* GOLDENGATE_DDL_REPLICATION */ ], sequence [1509], DDL type [ALTER] TABLE, real object type [TABLE], validity [VALID], object ID [76900], object [HR.T5], real object [HR.T5], base object schema [], base object name [], logged as [GGUSR]

SESS 450003-2015-06-10 18:12:38 : DDL : Start SCN found [1112101]

SESS 450003-2015-06-10 18:12:40 : DDL : ------------------------- End of log for DDL sequence [1509]

OGG1上的日志（/u02/app/oracle/diag/rdbms/ogg1/ogg1/trace/ggs_ddl_trace.log）：

SESS 450045-2015-06-10 04:05:42 : DDL : ************************* Start of log for DDL sequence [43], v[ $Id: ddl_setup.sql /st_oggcore_11.2.1/8 2012/04/02 11:11:33 smijatov Exp $ ] trace level [0], owner schema of DDL package [GGUSR], objtype [TABLE] name [HR.T5]

SESS 450045-2015-06-10 04:05:42 : DDLTRACE1 : Before Trigger: point in execution = [1.0], objtype [TABLE] name [HR.T5]

SESS 450045-2015-06-10 04:05:42 : DDL : DDL operation [ create table hr."T5" as select * from dual /* GOLDENGATE_DDL_REPLICATION */ ], sequence [43], DDL type [CREATE] TABLE, real object type [TABLE], validity [], object ID [], object [HR.T5], real object [HR.T5], base object schema [], base object name [], logged as [GGUSR]

SESS 450045-2015-06-10 04:05:42 : DDL : Start SCN found [1121651]

SESS 450045-2015-06-10 04:05:42 : DDL : ------------------------- End of log for DDL sequence [43]

SESS 470002-2015-06-10 04:06:19 : DDL : ************************* Start of log for DDL sequence [44], v[ $Id: ddl_setup.sql /st_oggcore_11.2.1/8 2012/04/02 11:11:33 smijatov Exp $ ] trace level [0], owner schema of DDL package [GGUSR], objtype [TABLE] name [HR.T5]

SESS 470002-2015-06-10 04:06:19 : DDLTRACE1 : Before Trigger: point in execution = [1.0], objtype [TABLE] name [HR.T5]

SESS 470002-2015-06-10 04:06:19 : DDL : DDL operation [alter table t5 add name varchar2(255) ], sequence [44], DDL type [ALTER] TABLE, real object type [TABLE], validity [VALID], object ID [76920], object [HR.T5], real object [HR.T5], base object schema [], base object name [], logged as [HR]

SESS 470002-2015-06-10 04:06:19 : DDL : Start SCN found [1121693]

SESS 470002-2015-06-10 04:06:20 : DDL : ------------------------- End of log for DDL sequence [44]

一.3.6.2 dml測試

SQL*Plus: Release 11.2.0.3.0 Production on Wed Jun 10 18:13:37 2015

SQL> create table t6 as select * from dual;

SQL> insert into t6 select * from t6;

1 row created.

SQL> commit;

Commit complete.

SQL> select count(1) from t6;

2 rows created.

OGG的雙向複制其實就是在每台機器上都進行相關的單向配置。

...........................................................................................................................................................................................

本文轉自lhrbest 51CTO部落格，原文連結：http://blog.51cto.com/lhrbest/1661911，如需轉載請自行聯系原作者

【OGG】OGG簡單配置雙向複制(三)

繼續閱讀

MySQL的4種隔離級别？出現問題

Ubuntu Linux下Apache的配置檔案

XX系統實施過程問題總結

無元件上傳圖檔到資料庫中，最完整解決方案

【MySQL資料庫】資料庫索引事務1.索引2.事務

neo4j之cypher使用文檔

NOSQL安全攻擊

mybatis_入門程式Mybatis入門

samba伺服器的功能

登入plsql 報錯 the account is locked --使用者被鎖

sqlServer根據經緯查距離

【Linux】UDP廣播封包接收速率問題

SequoiaDB巨杉資料庫C++驅動概述

Linux裝置模型（中）之上層容器

Oracle 批量查詢傳入List 傳回List

PowerPC平台 Linux移植三