Oracle goldengate 11g （二）【DML and DDL 单向复制】

实验环境，oracle goldengate 安装，仅开启DML：请看

Oracle goldengate 11g （一）【DML单向复制】

http://space.itpub.net/26442936/viewspace-764272

实验整体思路

一、停止所有extract、replicat进程

二、 Oracle 数据库准备

三、添加DDL所需参数

四、 Oracle goldengate DDL 测试

一、停止所有extract、replicat进程

Source database

GGSCI (doudou-NAS) 4> info all

Program Status Group Lag at Chkpt Time Since Chkpt

MANAGER STOPPED

EXTRACT STOPPED EXT1 00:00:00 00:00:15

EXTRACT STOPPED PUMP1 00:00:00 00:00:14

Target database

GGSCI (localhost.localdomain) 4> info all

Program Status Group Lag at Chkpt Time Since Chkpt

MANAGER STOPPED

REPLICAT STOPPED REP1 00:00:00 00:00:15

二、 Oracle 数据库准备

SQL> conn /as sysdba

Connected.

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

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 OGG 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 OGG

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/doudou/doudou/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: 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 -- 开启DDL功能

Trigger altered.

SQL> @marker_status.sql -- 校验DDL状态

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

ogg

Setting schema name to OGG

MARKER TABLE

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

OK

MARKER SEQUENCE

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

OK

三、添加DDL所需参数

3-1 、Add ddl support to the extract process

GGSCI (doudou-NAS) 8> view params ext1

extract ext1

userid ogg,password oracle

exttrail /opt/ogg/dirdat/tt

TRANLOGOPTIONS asmuser sys@asm,asmpassword oracle

DDL INCLUDE ALL

DDLOPTIONS ADDTRANDATA

table doudou.*;

3-2 、Add ddl support to the replicat process

GGSCI (localhost.localdomain) 17> edit params rep1

replicat rep1

userid ogg ,password oracle

assumetargetdefs

dynamicresolution

DISCARDFILE ./dirrpt/rhr.dsc, append, megabytes 100

DDLERROR DEFAULT IGNORE

map doudou.*,target doudou.*;

GGSCI (doudou-NAS) 18> info all

Program Status Group Lag at Chkpt Time Since Chkpt

MANAGER RUNNING

EXTRACT RUNNING EXT1 00:00:00 00:00:04

EXTRACT RUNNING PUMP1 00:00:00 00:00:01

GGSCI (localhost.localdomain) 19> info all

Program Status Group Lag at Chkpt Time Since Chkpt

MANAGER RUNNING

REPLICAT RUNNING REP1 00:00:00 00:00:02

到此Oracle goldengate DDL安装结束

四、 Oracle goldengate DDL 测试

Source database

SQL> select * from test;

NAME

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

ID

----------

兜兜

1

Target database

SQL> select * from test;

NAME

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

ID

----------

兜兜

1

Source database

SQL> truncate table test;

Table truncated.

SQL> commit;

Commit complete.

SQL> select * from test;

no rows selected

Target database

SQL> select * from test;

no rows selected

--DDL truncate 操作，已经同步到了目标库上

源库：建立一个用户xiaoqiang并建立表xiaoqiang和数据

目标库：用户被建立xiaoqiang和表xiaoqiang被建立，但是数据未同步

Source database

SQL> conn /as sysdba

Connected.

SQL> create user xiaoqiang identified by oracle;

User created.

SQL> grant connect,resource to xiaoqiang;

Grant succeeded.

SQL> conn xiaoqiang/oracle

Connected.

SQL> create table xiaoqiang (name varchar(200),id number);

Table created.

SQL> insert into xiaoqiang values (' 小强',1);

1 row created.

SQL> commit;

Commit complete.

Target database

SQL> conn xiaoqiang/oracle

Connected.

SQL> select * from xiaoqiang;

no rows selected

--DDL create user 操作已经同步到了目标库，但是insert操作并没有同步，因为我们并没有配置xiaoqiang用户的DML，这里xiaoqiang用户的DDL操作之所以能同步到目标库因为我们配置了 DDL INCLUDE ALL

想一想？

如果我们删除的用户是doudou，建立新用户doudou并插入数据，目标库就会用户建立，表建立，数据填入

到此证明oracle goldengate的 DDL已经成功

总结：

Oracle goldengate DDL 成功

参考文档

https://blogs.oracle.com/imc/entry/oracle_goldengate_configuring_ddl_replication

ITPub博客

Oracle 11g 高可用 goldengate （二）【DML and DDL单向复制】

Oracle goldengate 11g （一）【DML单向复制】

最新文章