goldengate ddl mysql_GoldenGate配置（三）之DDL复制配置

最新推荐文章于 2023-05-03 13:12:18 发布

weixin_39740737

最新推荐文章于 2023-05-03 13:12:18 发布

阅读量102

点赞数

CC 4.0 BY-SA版权

文章标签： goldengate ddl mysql

本文链接：https://blog.youkuaiyun.com/weixin_39740737/article/details/114171786

本文续接前文，介绍了GoldenGate的DDL复制配置。详细说明了在Red Hat Linux环境下，Oracle数据库的DDL复制配置过程，包括在gc1和gc2上执行脚本、配置Extract和Replicat进程等，最后对create、alter、drop等DDL操作进行了验证。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

GoldenGate配置(三)之DDL复制配置【说明】此篇续接上一篇：GoldenGate配置(二)之双向复制配置点击打开链接环境： Item Source System Target System Platform Red Hat Enterprise Linux Server release 5.4 Red Hat Enterprise Linux Server release

GoldenGate配置(三)之DDL复制配置

【说明】此篇续接上一篇：“GoldenGate配置(二)之双向复制配置”点击打开链接

环境：

Item

Source System

Target System

Platform

Red Hat Enterprise

Linux Server release 5.4

Red Hat Enterprise

Linux Server release 5.4

Hostname

gc1

gc2

Database

Oracle 10.2.0.1

Oracle 11.2.0.1

Character Set

ZHS16GBK

ORACLE_SID

PROD

EMREP

Listener Name/Port

LISTENER/1521

Goldengate User

ogg

DDL复制配置

gc1：执行脚本(注意：要到/u01/app/ogg目录下执行，否则会被hang住，oracle的bug)

SQL>@marker_setup.sql;--到/u01/app/ogg目录后登陆sqlplus执行

Marker setup script

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

NOTE: The schema must be created prior to runningthis script.

NOTE: Stop all DDL replication before startingthis installation.

Enter GoldenGate schema name:ogg

Marker setup table script complete, runningverification script...

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

Setting schema name to OGG

MARKER TABLE

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

MARKER SEQUENCE

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

Script complete.

SQL> alter system set recyclebin=off scope=spfile; --关闭回收站

SQL> startup force; --重启库

SQL> @ddl_setup

GoldenGateDDL Replication setup script

Verifyingthat current user has privileges to install DDL Replication...

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

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

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

NOTE:Stop all DDL replication before starting this installation.

EnterGoldenGate schema name:ogg

You willbe prompted for the mode of installation.

Toinstall or reinstall DDL replication, enter INITIALSETUP

Toupgrade DDL replication, enter NORMAL

Entermode of installation:INITIALSETUP

Working,please wait ...

Spoolingto file ddl_setup_spool.txt

UsingOGG as a GoldenGate schema name, INITIALSETUP as a mode of installation.

Working,please wait ...

RECYCLEBINmust be empty.

Thisinstallation will purge RECYCLEBIN for all users.

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

Enteryes or no:yes

SQL>@role_setup

GGS Role setup script

This script will drop and recreate the roleGGS_GGSUSER_ROLE

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

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

NOTE: The schema must be created prior to runningthis script.

NOTE: Stop all DDL replication before startingthis 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 theExtract, GGSCI, and Manager processes, by using the following SQL command:

GRANT GGS_GGSUSER_ROLE TO

where is the user assigned tothe GoldenGate processes.

SQL> grant GGS_GGSUSER_ROLE to ogg;

SQL> @ddl_enable

Trigger altered.

gc2：执行脚本(同gc1上操作)

SQL>alter system set recyclebin=off scope=spfile;--关闭回收站

SQL>startup force; --重启库

SQL>@marker_setup

SQL>@ddl_setup

SQL>@role_setup

SQL>grant GGS_GGSUSER_ROLE to ogg;

SQL>@ddl_enable

gc1：配置Extract进程

GGSCI(gc1) 3> STOP EORA_1

Sending STOP request to MANAGER ...

Request Processed.

GGSCI(gc1) 4> EDIT PARAMS EORA_1

添加红字部分:

-- Change Capture parameter file to capture

-- TCUSTMER and TCUSTORD changes

EXTRACT EORA_1

SETENV (NLS_LANG=AMERICAN_AMERICA.AL32UTF8)

USERID ogg, PASSWORD Welcome1

EXTTRAIL ./dirdat/aa

DDL INCLUDE OBJNAME "scott.*"

TABLE scott.TCUSTMER;

TABLE scott.TCUSTORD;

GGSCI(gc1) 5> START EORA_1

Sending START request to MANAGER ...

EXTRACT EINI_1 starting

gc2：配置extract

GGSCI(gc2) 3> stop EORA_1 --关闭EORA_1进程

Sending STOP request to MANAGER ...

Request Processed.

GGSCI(gc2) 4> EDIT PARAMS EORA_1 --添加红字部分

-- Change Capture parameter file to capture

-- TCUSTMER and TCUSTORD changes

EXTRACT EORA_1

SETENV (NLS_LANG=AMERICAN_AMERICA.AL32UTF8)

USERID ogg, PASSWORD Welcome1

EXTTRAIL ./dirdat/aa

DDL INCLUDE OBJNAME "scott.*"

TABLE scott.TCUSTMER;

TABLE scott.TCUSTORD;

GGSCI(gc2) 5>START EORA_1

Sending START request to MANAGER ...

EXTRACT EINI_1 starting

gc1:配置Replicat进程

GGSCI(gc1) 9> stop RORA_1

Sending STOP request to REPLICAT RORA_1 ...

Request processed.

GGSCI(gc1) 10> info all

Program Status Group Lag Time Since Chkpt

MANAGER RUNNING

EXTRACT RUNNING EORA_1 00:00:00 00:00:07

EXTRACT RUNNING PORA_1 00:00:00 00:00:02

REPLICAT STOPPED RORA_1 00:00:00 00:00:31

GGSCI(gc1) 11> EDIT PARAMS RORA_1 --添加红色部分

DDLERROR DEFAULT IGNORE RETRYOP

-- Change Delivery parameter file to apply

-- TCUSTMER and TCUSTORD Changes

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.tcustmer, TARGET scott.tcustmer;

MAP scott.tcustord, TARGET scott.tcustord;

"dirprm/rora_1.prm" 16L, 458C written

GGSCI(gc1) 12> start RORA_1

Sending START request to MANAGER ...

REPLICAT RORA_1 starting

GGSCI(gc1) 13> info all

Program Status Group Lag Time Since Chkpt

MANAGER RUNNING

EXTRACT RUNNING EORA_1 00:00:00 00:00:06

EXTRACT RUNNING PORA_1 00:00:00 00:00:02

REPLICAT RUNNING RORA_1 00:00:00 00:00:02

gc2:配置Replicate进程

配置过程，同gc1

验证ddl:create操作

gc1：

SQL>create table test1 as select * from emp;

Table created.

gc2：

SQL>select * from test1;

EMPNOENAME JOB MGR HIREDATE SAL COMM DEPTNO

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

7369SMITH CLERK 7902 17-DEC-80 800 20

7499ALLEN SALESMAN 7698 20-FEB-81 1600 300 30

......

7902FORD ANALYST 7566 03-DEC-81 3000 20

7934MILLER CLERK 7782 23-JAN-82 1300 10

14 rows selected.

gc2:

SQL>create table test2 as select * from emp;

Table created.

gc1:

SQL>select * from test2;

EMPNOENAME JOB MGR HIREDATE SAL COMM DEPTNO

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

7369SMITH CLERK 7902 17-DEC-80 800 20

7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30

......

7902FORD ANALYST 7566 03-DEC-81 3000 20

7934MILLER CLERK 7782 23-JAN-82 1300 10

14 rows selected.

验证ddl:alter操作

gc1:

SQL>alter table test1 rename column mgr to manager;

Table altered.

gc2:

SQL>select * from test1;

EMPNOENAME JOB MANAGER HIREDATE SAL COMM DEPTNO

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

7369SMITH CLERK 7902 17-DEC-80 800 20

7499ALLEN SALESMAN 7698 20-FEB-81 1600 300 30

7521WARD SALESMAN 7698 22-FEB-81 1250 500 30

......

gc2:

SQL>alter table test1 rename column manager to mgr;

Table altered.

gc1:

SQL>select * from test1;

EMPNOENAME JOB MGR HIREDATE SAL COMM DEPTNO

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

7369SMITH CLERK 7902 17-DEC-80 800 20

7499ALLEN SALESMAN 7698 20-FEB-81 1600 300 30

7521WARD SALESMAN 7698 22-FEB-81 1250 500 30

7566JONES MANAGER 7839 02-APR-81 2975 20

......

验证ddl:drop操作

gc2:

SQL>select * from tab;

TNAME TABTYPE CLUSTERID

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

BONUS TABLE

DEPT TABLE

EMP TABLE

SALGRADE TABLE

TCUSTMER TABLE

TCUSTORD TABLE

TEST1 TABLE

TEST2 TABLE

8 rows selected.

gc1:

SQL>drop table test1;

Table dropped.

gc2:

SQL>select * from tab;

TNAME TABTYPE CLUSTERID

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

BONUS TABLE

DEPT TABLE

EMP TABLE

SALGRADE TABLE

TCUSTMER TABLE

TCUSTORD TABLE

TEST2 TABLE

7 rows selected.

gc1:

SQL>select * from tab;

TNAME TABTYPE CLUSTERID

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

DEPT TABLE

EMP TABLE

BONUS TABLE

SALGRADE TABLE

TCUSTMER TABLE

TCUSTORD TABLE

TEST2 TABLE

7 rows selected.

gc2:

SQL>drop table test2;

Table dropped.

SQL>select * from tab;

TNAME TABTYPE CLUSTERID

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

DEPT TABLE

EMP TABLE

BONUS TABLE

SALGRADE TABLE

TCUSTMER TABLE

TCUSTORD TABLE

6 rows selected.

声明：

原创作品，出自 “深蓝的blog” 博客，允许转载，转载时请务必注明出处(http://blog.youkuaiyun.com/huangyanlong)。

关于涉及版权事宜，作者有权追究法律责任。

本条技术文章来源于互联网，如果无意侵犯您的权益请点击此处反馈版权投诉

本文系统来源：php中文网