Oracle goldengate 11g （二）【DML and DDL單向複製】

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部落格 ” ，連結：http://blog.itpub.net/26442936/viewspace-764335/，如需轉載，請註明出處，否則將追究法律責任。

Oracle goldengate 11g （二）【DML and DDL單向複製】

Oracle goldengate 11g （一）【DML單向複製】

相關文章