合 OGG从入门到高可用系列

发布日期 · 已更新  

0    550    1

Tags：

👉 本文共约70975个字，系统预计阅读时间或需267分钟。

【OGG】OGG简单配置双向复制(三)

实验环境介绍

项目source dbtarget db
db 类型单实例单实例
db version11.2.0.311.2.0.3
db 存储FS typeFS type
ORACLE_SIDogg1ogg2
db_nameogg1ogg2
主机IP地址：192.168.59.129192.168.59.130
OS版本及kernel版本RHEL6.5 64位，2.6.32-504.16.2.el6.x86_64RHEL6.5 64位，2.6.32-504.16.2.el6.x86_64
OGG版本11.2.1.0.1 64位11.2.1.0.1 64位
OS hostnameorcltestrhel6_lhr

本文简介

在完成ogg的单向复制配置后，自然会想着向前推进一层，实现双向复制；在实际应用中，双向复制面临着许多问题，主要有如下几点：

1. 如果两个库同时更新同一条记录 如何处理？

2. 如果网络出现失败如何处理？

3. 如果数据不同步后如何修复？

本文介绍如何在前文的基础上简单实现ogg的双向复制！双向复制一般用于双业务中心环境下，目前笔者的生产环境中未使用到ogg，ogg系列的文章只是从技术上提前做一个准备，因而许多问题的细节未能理清，后续将继续学习研究！在开始之前，请先配置好db1-db2的单向复制(include ddl replicat)!

实验部分

实验目标

实现OGG1和OGG2的双向复制功能。

配置OGG1，添加checkpoint表

注意：本文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

Copyright (C) 1995, 2012, Oracle and/or its affiliates. All rights reserved.

GGSCI (orcltest) 1> view params ./GLOBALS

GGSCHEMA ggusr

GGSCI (orcltest) 2> edit params ./GLOBALS

GGSCHEMA ggusr

checkpointtable ggusr.rep_demo_ckpt

~

~

~

~

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

~

~

~

~

~

"./GLOBALS" 2L, 51C written

GGSCI (orcltest) 3> view params ./GLOBALS

GGSCHEMA ggusr

checkpointtable ggusr.rep_demo_ckpt

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>

配置OGG2,运行相关的脚本，支持DDL的复制

[oracle@rhel6_lhr 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

Copyright (C) 1995, 2012, Oracle and/or its affiliates. All rights reserved.

GGSCI (rhel6_lhr) 1> view params ./GLOBALS

checkpointtable ggusr.rep_demo_ckpt

GGSCI (rhel6_lhr) 2> edit params ./GLOBALS

GGSCI (rhel6_lhr) 3> view params ./GLOBALS

GGSCHEMA ggusr

checkpointtable ggusr.rep_demo_ckpt

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

Copyright (c) 1982, 2011, Oracle. All rights reserved.

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

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

OK

MARKER SEQUENCE

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

OK

Script complete.

SQL> @ddl_setup.sql

Oracle GoldenGate DDL Replication setup script

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

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

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

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

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.

Working, please wait ...

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

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

Setting schema name to GGUSR

CLEAR_TRACE STATUS:

Line/pos Error

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

No errors No errors

CREATE_TRACE STATUS:

Line/pos Error

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

No errors No errors

TRACE_PUT_LINE STATUS:

Line/pos Error

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

No errors No errors

INITIAL_SETUP STATUS:

Line/pos Error

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

No errors No errors

DDLVERSIONSPECIFIC PACKAGE STATUS:

Line/pos Error

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

No errors No errors

DDLREPLICATION PACKAGE STATUS:

Line/pos Error

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

No errors No errors

DDLREPLICATION PACKAGE BODY STATUS:

Line/pos Error

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

No errors No errors

DDL IGNORE TABLE

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

OK

DDL IGNORE LOG TABLE

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

OK

DDLAUX PACKAGE STATUS:

Line/pos Error

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

No errors No errors

DDLAUX PACKAGE BODY STATUS:

Line/pos Error

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

No errors No errors

SYS.DDLCTXINFO PACKAGE STATUS:

Line/pos Error

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

No errors No errors

SYS.DDLCTXINFO PACKAGE BODY STATUS:

Line/pos Error

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

No errors No errors

DDL HISTORY TABLE

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

OK

DDL HISTORY TABLE(1)

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

OK

DDL DUMP TABLES

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

OK

DDL DUMP COLUMNS

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

OK

DDL DUMP LOG GROUPS

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

OK

DDL DUMP PARTITIONS

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

OK

DDL DUMP PRIMARY KEYS

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

OK

DDL SEQUENCE

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

OK

GGS_TEMP_COLS

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

OK

GGS_TEMP_UK

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

OK

DDL TRIGGER CODE STATUS:

Line/pos Error

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

No errors No errors

DDL TRIGGER INSTALL STATUS

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

OK

DDL TRIGGER RUNNING STATUS

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

ENABLED

STAYMETADATA IN TRIGGER

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

OFF

DDL TRIGGER SQL TRACING

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

0

DDL TRIGGER TRACE LEVEL

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

0

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

Script complete.

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.

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

NOTE: Stop all DDL replication before starting this installation.

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>

SQL> GRANT GGS_GGSUSER_ROLE TO ggusr;

本人提供Oracle(OCP、OCM)、MySQL(OCP)、PostgreSQL(PGCA、PGCE、PGCM)等数据库的培训和考证业务，私聊QQ646634621或微信dbaup66，谢谢！
AiDBA后续精彩内容已被站长无情隐藏，请输入验证码解锁本文！
验证码：
获取验证码： 请先关注本站微信公众号，然后回复“验证码”，获取验证码。在微信里搜索“AiDBA”或者“dbaup6”或者微信扫描右侧二维码都可以关注本站微信公众号。

标签：

Avatar photo

小麦苗

学习或考证，均可联系麦老师，请加微信db_bao或QQ646634621

您可能还喜欢...

发表回复