Oracle GoldenGate DDL Synchronization configuration

本文主要是介绍Oracle GoldenGate DDL Synchronization configuration，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！

DML同步参考Oracle GoldenGate for Oracle 11g(Solaris 10 x86-64) to Oracle 10g(CentOS 4.8) configuration

关闭源端数据库的recyclebin,如果你是Oracle11g,可以不用关闭

[oracle@gg01 ~]$ sqlplus / AS sysdbaSQL*Plus: Release 10.2.0.1.0 - Production ON Sun Jan 15 12:49:39 2012Copyright (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 optionsSYS@gg01:~>SHOW parameter recyclebinNAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
recyclebin                           string      ON
SYS@gg01:~>ALTER system SET recyclebin=off;System altered.SYS@gg01:~>SHOW parameter recyclebinNAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
recyclebin                           string      OFF

创建DDL复制用户

SYS@gg01:~>CREATE USER ggadmin IDENTIFIED BY ggadmin DEFAULT tablespace users;USER created.SYS@gg01:~>GRANT dba TO ggadmin;GRANT succeeded.

编辑ogg的globals参数,指定DDL用户

GGSCI (gg01) 1> edit params ./GLOBALS

GGSCI (gg01) 3> VIEW params ./GLOBALSggschema ggadmin

安装DDL对象,运行marker_setup.sql脚本,提示输入OGG用户,这里是之前定义的ggadmin

SYS@gg01:~>@marker_setupMarker setup scriptYou 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:ggadminMarker setup TABLE script complete, running verification script...
Please enter the name OF a schema FOR the GoldenGate DATABASE objects:
Setting schema name TO GGADMINMARKER TABLE
-------------------------------
OKMARKER SEQUENCE
-------------------------------
OKScript complete.

运行ddl_setup.sql脚本,提示输入用户:ggadmin

SYS@gg01:~>@ddl_setupGoldenGate DDL Replication setup scriptVerifying 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:ggadminYou 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:INITIALSETUPWorking, please wait ...
Spooling TO file ddl_setup_spool.txtUSING GGADMIN 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:yesDDL replication setup script complete, running verification script...
Please enter the name OF a schema FOR the GoldenGate DATABASE objects:
Setting schema name TO GGADMINDDLORA_GETTABLESPACESIZE STATUS:Line/pos
--------------------
Error
-----------------------------------------------------------------
No errors
No errorsCLEAR_TRACE STATUS:Line/pos
--------------------
Error
-----------------------------------------------------------------
No errors
No errorsCREATE_TRACE STATUS:Line/pos
--------------------
Error
-----------------------------------------------------------------
No errors
No errorsTRACE_PUT_LINE STATUS:Line/pos
--------------------
Error
-----------------------------------------------------------------
No errors
No errorsINITIAL_SETUP STATUS:Line/pos
--------------------
Error
-----------------------------------------------------------------
No errors
No errorsDDLVERSIONSPECIFIC PACKAGE STATUS:Line/pos
--------------------
Error
-----------------------------------------------------------------
No errors
No errorsDDLREPLICATION PACKAGE STATUS:Line/pos
--------------------
Error
-----------------------------------------------------------------
No errors
No errorsDDLREPLICATION PACKAGE BODY STATUS:Line/pos
--------------------
Error
-----------------------------------------------------------------
No errors
No errorsDDL HISTORY TABLE
-----------------------------------
OKDDL HISTORY TABLE(1)
-----------------------------------
OKDDL DUMP TABLES
-----------------------------------
OKDDL DUMP COLUMNS
-----------------------------------
OKDDL DUMP LOG GROUPS
-----------------------------------
OKDDL DUMP PARTITIONS
-----------------------------------
OKDDL DUMP PRIMARY KEYS
-----------------------------------
OKDDL SEQUENCE
-----------------------------------
OKGGS_TEMP_COLS
-----------------------------------
OKGGS_TEMP_UK
-----------------------------------
OKDDL TRIGGER CODE STATUS:Line/pos
--------------------
Error
-----------------------------------------------------------------
No errors
No errorsDDL TRIGGER INSTALL STATUS
-----------------------------------
OKDDL TRIGGER RUNNING STATUS
----------------------------------------------------------------------
ENABLEDSTAYMETADATA IN TRIGGER
----------------------------------------------------------------------
OFFDDL TRIGGER SQL TRACING
----------------------------------------------------------------------
0DDL TRIGGER TRACE LEVEL
----------------------------------------------------------------------
0LOCATION OF DDL TRACE FILE
--------------------------------------------------------------------------------
/u01/app/oracle/admin/gg01/udump/ggs_ddl_trace.logAnalyzing installation STATUS...STATUS OF DDL REPLICATION
--------------------------------------------------------------------------------
SUCCESSFUL installation OF DDL Replication software componentsScript complete.
SYS@gg01:~>

创建DDL复制角色,运行role_setup.sql脚本

SYS@gg01:~>@role_setupGGS ROLE setup scriptThis 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:ggadmin
Wrote file role_setup_set.txtPL/SQL PROCEDURE successfully completed.ROLE setup script completeGRANT this ROLE TO each USER assigned TO the EXTRACT, GGSCI, AND Manager processes, BY USING the following SQL command:GRANT GGS_GGSUSER_ROLE TO loggedUserWHERE loggedUser IS the USER assigned TO the GoldenGate processes.
SYS@gg01:~>

开启DDL功能,运行ddl_enable.sql脚本

SYS@gg01:~>@ddl_enableTRIGGER altered.

验证DDL脚本的安装,运行marker_status.sql脚本

SYS@gg01:~>@marker_status
Please enter the name OF a schema FOR the GoldenGate DATABASE objects:
ggadmin
Setting schema name TO GGADMINMARKER TABLE
-------------------------------
OKMARKER SEQUENCE
-------------------------------
OK
SYS@gg01:~>

配置DDL复制,修改extract参数

GGSCI (gg01) 5> VIEW params eorajjEXTRACT EORAJJ
USERID system, PASSWORD oracle
RMTHOST gg02, MGRPORT 7809
RMTTRAIL /u01/app/oracle/goldengate/dirdat/jjDDL INCLUDE ALL
--DDLERROR RESTARTSKIP 100000 SKIPTRIGGERERROR 100000
DDLOPTIONS ADDTRANDATA,REPORTTABLE scott.*;
SEQUENCE scott.*;GGSCI (gg01) 6>

修改replicat参数

REPLICAT RORAJJ
USERID system, PASSWORD oracle
HANDLECOLLISIONS
ASSUMETARGETDEFS
DISCARDFILE /u01/app/oracle/goldengate/dirrpt/RORAJJ.DSC, PURGEDDL INCLUDE MAPPED
--DDLERROR DEFAULT IGNORE RETRYOP
DDLOPTIONS REPORTMAP scott.*, TARGET scott.*;

分别启动源端extract和目标端replicat进程

GGSCI (gg01) 15> START EXTRACT eorajjSending START request TO MANAGER ...
EXTRACT EORAJJ startingGGSCI (gg01) 16> info ALLProgram     STATUS      GROUP       Lag           TIME Since ChkptMANAGER     RUNNING                                           
EXTRACT     RUNNING     EORAJJ      00:00:00      00:14:39

GGSCI (gg02) 7> START replicat rorajjSending START request TO MANAGER ...
REPLICAT RORAJJ startingGGSCI (gg02) 8> info ALLProgram     STATUS      GROUP       Lag           TIME Since ChkptMANAGER     RUNNING                                           
REPLICAT    RUNNING     RORAJJ      00:00:00      00:00:01

先验证DML

源端

SCOTT@gg01:~>INSERT INTO bonus VALUES('ZWC','10','1000',10);1 ROW created.SCOTT@gg01:~>commit;Commit complete.SCOTT@gg01:~>

目标段

SCOTT@gg02:~>SELECT * FROM bonus WHERE ename='ZWC';ENAME      JOB              SAL       COMM
---------- --------- ---------- ----------
ZWC        10              1000         10SCOTT@gg02:~>

验证DDL

SCOTT@gg01:~>CREATE TABLE t_zwc(tid INTEGER PRIMARY KEY,tname VARCHAR(10));TABLE created.SCOTT@gg01:~>DESC t_zwcName                    NULL?    TYPE----------------------- -------- ----------------TID                     NOT NULL NUMBER(38)TNAME                            VARCHAR2(10)SCOTT@gg01:~>SELECT tname FROM tab;TNAME
------------------------------
DEPT
EMP
BONUS
SALGRADE
T_ZWC5 ROWS selected.

目标端查看

SCOTT@gg02:~>SELECT tname FROM tab;TNAME
------------------------------
DEPT
EMP
BONUS
SALGRADE
T_ZWCSCOTT@gg02:~>SET linesize 50
SCOTT@gg02:~>DESC t_zwcName                    NULL?    TYPE----------------------- -------- ----------------TID                     NOT NULL NUMBER(38)TNAME                            VARCHAR2(10)

验证存储过程

SCOTT@gg01:~>CREATE OR REPLACE PROCEDURE ddl_test2  IS3  BEGIN4    NULL;5  END ddl_test;6  /PROCEDURE created.

目标端

SCOTT@gg02:~>SELECT DISTINCT name FROM user_source WHERE TYPE='PROCEDURE';NAME
------------------------------
DDL_TESTSCOTT@gg02:~>SET pages 100
SCOTT@gg02:~>SELECT text FROM user_source;TEXT
--------------------------------------------------------------------------------
PROCEDURE         "DDL_TEST"
IS
BEGINNULL;
END ddl_test;  /* GOLDENGATE_DDL_REPLICATION */

验证sequence

SCOTT@gg01:~>CREATE SEQUENCE seq_ddl START WITH 1 INCREMENT BY 2 maxvalue 99999;SEQUENCE created.

目标端

SCOTT@gg02:~>SELECT sequence_name,max_value,increment_by FROM user_sequences WHERE sequence_name=UPPER('seq_ddl');SEQUENCE_NAME    MAX_VALUE INCREMENT_BY
--------------- ---------- ------------
SEQ_DDL              99999            2

这篇关于Oracle GoldenGate DDL Synchronization configuration的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！

---