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

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

一.1  BLOG文档结构图

 

wps5E60.tmp 

 

一.2  前言部分

 

一.2.1  导读

各位技术爱好者，看完本文后，你可以掌握如下的技能，也可以学到一些其它你所不知道的知识，~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皆可，您的批评指正是我写作的最大动力。

一.2.2  实验环境介绍

 

项目

source db

target  db

db 类型

单实例

单实例

db version

11.2.0.3

11.2.0.3

db 存储

FS type

FS type

ORACLE_SID

ogg1

ogg2

db_name

ogg1

ogg2

主机IP地址：

192.168.59.129

192.168.59.130

OS版本及kernel版本

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

RHEL6.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 hostname

orcltest

rhel6_lhr

 

 

 

一.2.3  相关参考文章链接

 

【OGG】OGG的下载和安装篇：http://blog.itpub.net/26736162/viewspace-1693241/

【OGG】OGG的单向DML复制配置(一):http://blog.itpub.net/26736162/viewspace-1696020/

【OGG】OGG的单向复制配置-支持DDL(二)：http://blog.itpub.net/26736162/viewspace-1696031/

 

 

一.2.4  本文简介

 

 

本文基于OGG的双向复制功能，主要参考网址为：http://ylw6006.blog.51cto.com/all/470441/16 ，非常感谢斩月大师。

 

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

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

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

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

wps5E70.tmp 

 

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

 

 

 

 

 

 

一.3  实验部分

 

一.3.1  实验目标

 

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

 

一.3.2  配置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>

 

 

一.3.3  配置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;

 

Grant succeeded.

 

SQL> @ddl_enable.sql 

 

Trigger altered.

 

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

 

Package created.

 

 

Grant succeeded.

 

 

SQL> @ddl_pin.sql ggusr

 

PL/SQL procedure successfully completed.

 

 

PL/SQL procedure successfully completed.

 

 

PL/SQL procedure successfully completed.

 

SQL> exit

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

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

[oracle@orcltest gg11]$

 

一.3.4  OGG2上配置extract和pump进程

[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> dblogin userid ggusr@ogg2,password lhr

Successfully logged into database.

 

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 (ORACLE_SID=ogg2)

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

setenv (NLS_LANG=AMERICAN_AMERICA.ZHS16GBK)  

passthru 

rmthost 192.168.59.129,mgrport 7809 

rmttrail ./dirdat/pb 

table hr.*;

 

 

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

 

Sending START request to MANAGER ...

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) 

ddl include all

tranlogoptions excludeuser ggusr

userid ggusr,password lhr

exttrail ./dirdat/hr 

table hr.*;

 

 

GGSCI (orcltest) 16>

 

 

 

一.3.5  OGG1上配置replicat进程

 

[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> info all

 

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

 

MANAGER     RUNNING                                          

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

setenv (ORACLE_SID=ogg1)

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

setenv (NLS_LANG=AMERICAN_AMERICA.ZHS16GBK)

ddl include all

ddlerror default ignore retryop maxretries 3 retrydelay 5

userid ggusr,password lhr

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

 

replicat rora_hr2

setenv (ORACLE_SID=ogg1)

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

setenv (NLS_LANG=AMERICAN_AMERICA.ZHS16GBK)

ddl include all

ddlerror default ignore retryop maxretries 3 retrydelay 5

DDLOPTIONS REPORT

userid ggusr,password lhr

handlecollisions

assumetargetdefs

discardfile ./dirrpt/rora_hr2.dsc,purge

map hr.* ,target hr.*;

 

 

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

REPLICAT added.

 

 

GGSCI (orcltest) 6> start replicat rora_hr2

 

Sending START request to MANAGER ...

REPLICAT RORA_HR2 starting

 

 

GGSCI (orcltest) 7> info all 

 

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

 

MANAGER     RUNNING                                          

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  测试

一.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

 

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>  create table t5 as select * from dual;

 

Table created.

 

SQL> conn hr/hr@ogg2

Connected.

SQL> select count(1) from t5;

 

  COUNT(1)

----------

         1

 

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

 

Table altered.

 

SQL> conn hr/hr@ogg1

Connected.

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测试

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

 

SQL*Plus: Release 11.2.0.3.0 Production on Wed Jun 10 18:13:37 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>  create table t6 as select * from dual;

 

Table created.

 

SQL> insert into t6 select * from t6;

 

1 row created.

 

SQL> commit;

 

Commit complete.

 

SQL> select count(1) from t6;

 

  COUNT(1)

----------

         2

 

SQL> conn hr/hr@ogg2

Connected.

SQL>  select count(1) from t6;

 

  COUNT(1)

----------

         2

 

SQL> insert into t6 select * from t6;

 

2 rows created.

 

SQL> commit;

 

Commit complete.

 

SQL>  select count(1) from t6;

 

  COUNT(1)

----------

         4

 

SQL> conn hr/hr@ogg1

Connected.

SQL>  select count(1) from t6;

 

  COUNT(1)

----------

         4

 

SQL>

 

 

一.3.7  实验总结

 

OGG的双向复制其实就是在每台机器上都进行相关的单向配置。

 

一.4  About Me

 

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

本文作者：小麦苗，只专注于数据库的技术，更注重技术的运用

ITPUB BLOG：http://blog.itpub.net/26736162

本文地址：http://blog.itpub.net/26736162/viewspace-1699516/

本文pdf版：http://yunpan.cn/QCwUAI9bn7g7w  提取码：af2d

QQ：642808185 若加QQ请注明你所正在读的文章标题

创作时间地点：2015-06-10 09:00~ 2015-06-10 19:00 于外汇交易中心

<版权所有，文章允许转载，但须以链接方式注明源地址，否则追究法律责任!>

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

 

 

posted @ 2015-06-15 09:28  DB宝  阅读(13623)  评论(0)  编辑  收藏  举报