Oracle 11g Golden Gate DDL单向同步实例演示

最新推荐文章于 2025-01-01 10:57:12 发布

转载最新推荐文章于 2025-01-01 10:57:12 发布 · 1.6k 阅读

文章标签：

#OGG

OGG 专栏收录该内容

1 篇文章

订阅专栏

本文介绍如何在Oracle GoldenGate环境中配置DDL（数据定义语言）同步，包括安装DDL支持、创建必要的表和触发器、调整相关参数以及进行简单的测试验证。

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

1 指定Source端数据库模式

GGSCI (ogg1) 19> edit params ./GLOBALS
ggschema ogg

GGSCI (ogg1) 23> stop mgr
Manager process is required by other GGS processes.
Are you sure you want to stop it (y/n)? y
Sending STOP request to MANAGER ...
Request processed.
Manager stopped.

GGSCI (ogg1) 24> info all
Program     Status      Group       Lag at Chkpt  Time Since Chkpt
MANAGER     STOPPED                                          
EXTRACT     RUNNING     EXT1        00:00:00      00:00:10   
EXTRACT     RUNNING     PUMP1       00:00:00      00:00:00

2 Source Database安装DDL支持

SQL> conn /as sysdba;
Connected.

SQL> !pwd
/u01/app/oracle/ogg

创建用于记录DDL信息的表

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.

创建相应的存储过程和trigger

SQL> @ddl_setup

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 STATU
-------------------------------------------------
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/ogg1/ogg1/trace/ggs_ddl_trace.log

Analyzing installation status...


STATUS OF DDL REPLICATION
----------------------------------------------------
SUCCESSFUL installation of DDL Replication software components

Script complete.

创建DDL同步需要的角色

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

where <loggedUser> is the user assigned to the GoldenGate processes.

启动ddl trigger

SQL> @ddl_enable.sql
Trigger altered.

优化ddl trigger性能

SQL> @ddl_pin ogg

PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.

[oracle@ogg1 ogg]$ pwd
/u01/app/oracle/ogg

[oracle@ogg1 ogg]$ ggsci
Oracle GoldenGate Command Interpreter for Oracle
Version 11.2.1.0.1 OGGCORE_11.2.1.0.1_PLATFORMS_120423.0230_FBO
Linux, x86, 32bit (optimized), Oracle 11g on Apr 23 2012 08:09:25
Copyright (C) 1995, 2012, Oracle and/or its affiliates. All rights reserved.

关闭Manager进程

GGSCI (ogg1) 11> stop mgr

3 修改Source Database参数

停止Extract进程

GGSCI (ogg1) 2> stop ext1

Sending STOP request to EXTRACT EXT1 ...
Request processed.

GGSCI (ogg1) 4> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt
MANAGER     STOPPED                                          
EXTRACT     STOPPED     EXT1        00:00:00      00:00:08   
EXTRACT     RUNNING     PUMP1       00:00:00      00:00:03

修改Extract参数

GGSCI (ogg1) 6> edit params ext1

extract ext1
dynamicresolution
setenv (NLS_.AL32UTF8)
userid ogg,password ogg
exttrail /u01/app/oracle/ogg/dirdat/et
ddl include all
ddloptions addtrandata, report
table askoracle.*;

ddl include all：启动ddl支持，并且包含所有的ddl操作

启动MGR & Extract进程

GSCI (ogg1) 11> start mgr
Manager started.

GGSCI (ogg1) 12> start ext1
Sending START request to MANAGER ...
EXTRACT EXT1 starting

GGSCI (ogg1) 14> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt
MANAGER     RUNNING                                          
EXTRACT     RUNNING     EXT1        00:02:51      00:00:04   
EXTRACT     RUNNING     PUMP1       00:00:00      00:00:03

4 修改Target Database参数

停止MGR和REP1进程

GGSCI (ogg2) 3> stop mgr

Manager process is required by other GGS processes.
Are you sure you want to stop it (y/n)? y
Sending STOP request to MANAGER ...
Request processed.
Manager stopped.

GGSCI (ogg2) 4> stop rep1
Sending STOP request to REPLICAT REP1 ...
Request processed.

GGSCI (ogg2) 5> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt
MANAGER     STOPPED                                          
REPLICAT    STOPPED     REP1        00:00:00      00:00:04

修改Rep1参数

GGSCI (ogg2) 6> edit params rep1

replicat rep1
userid ogg,password ogg
assumetargetdefs
reperror default,discard
discardfile /u01/app/oracle/ogg/dirrpt/rep1.dsc,append,megabytes 50
dynamicresolution
ddloptions report
ddlerror default ignore retryop maxretries 3 retrydelay 5
ddlerror default discard
ddlerror default ignore retryop
map askoracle.*, target askoracle.*;

启动MGR进程

GGSCI (ogg2) 7> start mgr
Manager started.

GGSCI (ogg2) 8> info all
Program     Status      Group       Lag at Chkpt  Time Since Chkpt
MANAGER     RUNNING                                          
REPLICAT    RUNNING     REP1        00:00:00      00:00:02

5. 单向DDL同步测试

Source端：

SQL> show user;
USER is "ASKORACLE"

SQL> select table_name from user_tables;

TABLE_NAME
------------------------------
OGG_TEST

SQL> create table askoracle(id number(10),createdate date,name varchar2(100));
Table created.

SQL> insert into askoracle values (1,sysdate,'askoracle');
 1    row created.

SQL> commit;
Commit complete.

Target端：

SQL> show user;
USER is "ASKORACLE"

SQL> select table_name from user_tables;

TABLE_NAME
------------------------------
OGG_TEST

SQL> select table_name from user_tables;

TABLE_NAME
------------------------------
ASKORACLE
OGG_TEST

SQL> select * from askoracle;

         ID CREATEDAT NAME
---------------------------------
          1 07-JAN-14 askoracle