OGG支持DDL双向复制的配置

  • zhangsharp20

    2014-09-22 17:35:09
  • Oracle
  • 原创

实验目的：

实现数据从oracle database 10.2.0.1.0到oracle database 10.2.0.1.0的双向DDL操作的复制。

实验环境：

Item

Source System

Target System

Platform

OEL5.4

OEL5.4

Hostname

zhang

gc1

Database

Oracle 10.2.0.1.0

Oracle 10.2.0.1.0

Character Set

Zhs16gbk

Zhs16gbk

ORACLE_SID

PROD

Rui

Listener Name/Port

LISTENER/1521

LISTENER/1521

Goldengate User

ogg

Ogg


实验步骤：

一、  在两个节点上执行DDL同步脚本命令

1.先进入GoldenGate软件安装目录，以sysdba身份登陆oracle执行以下脚本，执行脚本过程中，需要输入的用户全为ogg，安装模式为initialsetup

[oracle@zhang ogg]$ pwd

/u01/app/ogg

[oracle@zhang ogg]$ !sq

sqlplus / as sysdba


SQL*Plus: Release 10.2.0.1.0 - Production on Fri Apr 25 08:40:26 2014


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



Connected to:

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production

With the Partitioning, OLAP and Data Mining options


SQL> !pwd

/u01/app/ogg


SQL> @marker_setup


Marker setup 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:ogg



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

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

Setting schema name to OGG


MARKER TABLE

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

OK


MARKER SEQUENCE

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

OK


Script complete.

SQL>

SQL> @ddl_setup


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 GoldenGate database objects.

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

NOTE: On Oracle 10g and up, system recycle bin must be disabled.

NOTE: Stop all DDL replication before starting this installation.


Enter GoldenGate schema name:ogg


You will be prompted for the mode of installation.

To install or reinstall DDL replication, enter INITIALSETUP

To upgrade DDL replication, enter NORMAL

Enter mode of installation:initialsetup


Working, please wait ...

Spooling to file ddl_setup_spool.txt



Using OGG as a GoldenGate schema name, INITIALSETUP as a mode of installation.


Working, please wait ...

BEGIN

*

ERROR at line 1:

ORA-20783:

GoldenGate DDL Replication setup:

*** RECYCLEBIN must be turned off.

*** For 10gr2 and up, set RECYCLEBIN in parameter file to OFF. For 10gr1, set

_RECYCLEBIN in parameter file to FALSE. Then restart database and installation.

ORA-06512: at line 19



Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production

With the Partitioning, OLAP and Data Mining options

SQL> @role_setup


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:ogg

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


where is the user assigned to the GoldenGate processes.

SQL> grant GGS_GGSUSER_ROLE to ogg;


Grant succeeded.

SQL> @ddl_enable

ALTER TRIGGER sys .GGS_DDL_TRIGGER_BEFORE ENABLE

*

ERROR at line 1:

ORA-04080: trigger 'GGS_DDL_TRIGGER_BEFORE' does not exist

2.如果某项脚本执行错误，需要重新执行时，先要执行清除的脚本：ddl_remove.sql和marker_remove.sql

SQL> @ddl_remove  


DDL replication removal script.

WARNING: this script removes all DDL replication objects and data.


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.


Enter GoldenGate schema name:ogg

Working, please wait ...

Spooling to file ddl_remove_spool.txt


Script complete.

SQL> marker_remove

SP2-0734: unknown command beginning "marker_rem..." - rest of line ignored.

SQL> @marker_remove


Marker removal script.

WARNING: this script removes all marker objects and data.


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.


Enter GoldenGate schema name:ogg


PL/SQL procedure successfully completed.



Sequence dropped.



Table dropped.



Script complete.

SQL>

由于oracle for 10gr2必须关掉recyclebin才能执行ddl_setup


SQL> alter system set recyclebin =off;


System altered.

重新执行上述脚本

SQL> @marker_setup


Marker setup 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:ogg



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

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

Setting schema name to OGG


MARKER TABLE

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

OK


MARKER SEQUENCE

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

OK


Script complete.

SQL> @ddl_setup


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 GoldenGate database objects.

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

NOTE: On Oracle 10g and up, system recycle bin must be disabled.

NOTE: Stop all DDL replication before starting this installation.


Enter GoldenGate schema name:ogg


You will be prompted for the mode of installation.

To install or reinstall DDL replication, enter INITIALSETUP

To upgrade DDL replication, enter NORMAL

Enter mode of installation:initialsetup


Working, please wait ...

Spooling to file ddl_setup_spool.txt



Using OGG as a GoldenGate schema name, INITIALSETUP as a mode of installation.


Working, please wait ...


RECYCLEBIN must be empty.

This installation will purge RECYCLEBIN for all users.

To proceed, enter yes. To stop installation, enter no.


Enter yes or no:yes



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

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

Setting schema name to OGG


DDLORA_GETTABLESPACESIZE STATUS:


Line/pos

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

Error

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

No errors

No errors



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 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/admin/PROD/udump/ggs_ddl_trace.log


Analyzing installation status...



STATUS OF DDL REPLICATION

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

SUCCESSFUL installation of DDL Replication software components


Script complete.

SQL> @role_setup


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:ogg

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


where is the user assigned to the GoldenGate processes.

SQL> grant GGS_GGSUSER_ROLE to ogg;


Grant succeeded.

SQL> @ddl_enable


Trigger altered.

3.配置完之后打开recyclebin

SQL> alter system set recyclebin=on;


System altered.

4.在另一个节点上执行相同的操作

二、配置两个节点的extract

在EORA_1配置文件中添加下一行：

DDL INCLUDE OBJNAME "scott.emp_ogg,scott.dept_ogg"

GGSCI (zhang) 1> stop EORA_1


Sending STOP request to EXTRACT EORA_1 ...

Request processed.

GGSCI (zhang) 2> info EORA_1


EXTRACT    EORA_1    Last Started 2014-04-24 18:31   Status STOPPED

Checkpoint Lag       00:00:00 (updated 00:00:05 ago)

Log Read Checkpoint  Oracle Redo Logs

                     2014-04-25 10:03:52  Seqno 19, RBA 17010176



GGSCI (zhang) 3> edit params EORA_1



-- Change Capture parameter file to capture

-- EMP_OGG and DEPT_OGG changes

EXTRACT EORA_1

SETENV (NLS_LANG=AMERICAN_AMERICA.ZHS16GBK)

USERID ogg, PASSWORD Ogg

TRANLOGOPTIONS EXCLUDEUSER ogg

EXTTRAIL ./dirdat/aa

DDL INCLUDE OBJNAME "SCOTT.EMP_OGG,SCOTT.DEPT_OGG"

TABLE scott.EMP_OGG;

TABLE scott.DEPT_OGG;


~

"dirprm/eora_1.prm" 11L, 307C written

关闭recyclebin，启动EORA_1


SQL> alter system set recyclebin =off scope=both;


System altered.


GGSCI (zhang) 6> start extract eora_1


Sending START request to MANAGER ...

EXTRACT EORA_1 starting


GGSCI (zhang) 7> info eora_1


EXTRACT    EORA_1    Last Started 2014-04-25 10:40   Status RUNNING

Checkpoint Lag       00:36:29 (updated 00:00:03 ago)

Log Read Checkpoint  Oracle Redo Logs

                     2014-04-25 10:03:52  Seqno 19, RBA 17010176

三、配置两个节点的replicat参数文件

添加以下几行到RORA_1配置文件中：

DDL INCLUDE ALL

DDLERROR DEFAULT IGNORE RETRYOP MAXRETRIES 3 RETRYDELAY 5

DDLERROR DEFAULT DISCARD

DDLERROR DEFAULT IGNORE RETRYOP

操作如下：

GGSCI (zhang) 10> stop rora_1


Sending STOP request to REPLICAT RORA_1 ...

Request processed.



GGSCI (zhang) 11> info rora_1


REPLICAT   RORA_1    Last Started 2014-04-25 07:45   Status STOPPED

Checkpoint Lag       00:00:00 (updated 00:00:07 ago)

Log Read Checkpoint  File ./dirdat/pa000000

                     2014-04-25 10:40:45.522702  RBA 852927



GGSCI (zhang) 12> edit params 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.emp_ogg, TARGET scott.emp_ogg;

MAP scott.dept_ogg,TARGET scott.dept_ogg;

~

"dirprm/rora_1.prm" 12L, 373C written

GGSCI (zhang) 14> start rora_1


Sending START request to MANAGER ...

REPLICAT RORA_1 starting



GGSCI (zhang) 15> info rora_1


REPLICAT   RORA_1    Last Started 2014-04-25 10:56   Status RUNNING

Checkpoint Lag       00:00:00 (updated 00:00:02 ago)

Log Read Checkpoint  File ./dirdat/pa000000

                     First Record  RBA 852927

四、注意事项

1.如果新建的表进行同步，需要为要同步的表添加日志追踪文件

ADD TRANDATA [scott.new_tab]；

这样子ogg才会去捕捉new_tab的日志信息。

2. 如果是序列，无需关注，因为oracle是取得序列的值进行insert的

3. 如果源端建表的时候是基于子查询，如果子查询中访问的表在目标端没有，则无法实现ddl同步。或者子查询中的表如果数据不一样，则同步的表数据也不一样，根据各自数据库的子查询中的表来定。

4. 如果是insert操作，数据基于子查询，如果子查询访问的表目标端没有，却不受影响，能够同步。

5. update某行时，如果目标数据库没有符合条件的行，ogg会insert一条新行。

解决办法： 在最新的GG11.2版本中针对此种情况设置了专门的冲突解决机制，在11.2以前配置解决这个冲突很复杂，initial load 中有一个参数可以避免这个冲突，但是在正常复制时一般不建议用

五、测试结果

1.单向测试

在hostname为zhang的节点上truncate掉emp_ogg


SQL> truncate table emp_ogg;


Table truncated.

SQL> select count(*) from emp_ogg;


  COUNT(*)

----------

         0

在hostname为gc1的节点上查看emp_ogg

SQL> conn scott/tiger

Connected.

SQL> select count(*) from emp_ogg;


  COUNT(*)

----------

         0

2.逆向测试

在hostname为gc1的库上truncate掉dept_ogg

SQL> select count(*) from dept_ogg;


  COUNT(*)

----------

         4


SQL> truncate table dept_ogg;


Table truncated.


SQL> select count(*) from dept_ogg;


  COUNT(*)

----------

         0

在hostname=zhang的库中查看结果

SQL> select count(*) from dept_ogg;


  COUNT(*)

----------

         0

六、配置过程中遇到的问题

1. GGS ERROR 2003


原因：对于oracle 10gr2版本，必须关掉recyclebin

解决方案：关掉recyclebin


2.ggs error 101


原因：


，与target之间没有空格

解决方法：在，与target之间加上空格










请使用浏览器的分享功能分享到微信等