Oracle GoldenGate DDL Synchronization configuration

2024-02-01 09:18
文章标签 oracle ddl configuration synchronization goldengate

本文主要是介绍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的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！


http://www.chinasem.cn/article/666799。

相关文章

oracle DBMS_SQL.PARSE的使用方法和示例

oracle DBMS_SQL.PARSE的使用方法和示例

《oracleDBMS_SQL.PARSE的使用方法和示例》DBMS_SQL是Oracle数据库中的一个强大包,用于动态构建和执行SQL语句,DBMS_SQL.PARSE过程解析SQL语句或PL/S... 目录语法示例注意事项DBMS_SQL 是 oracle 数据库中的一个强大包，它允许动态地构建和执行
阅读更多...
PLsql Oracle 下载安装图文过程详解

PLsql Oracle 下载安装图文过程详解

《PLsqlOracle下载安装图文过程详解》PL/SQLDeveloper是一款用于开发Oracle数据库的集成开发环境,可以通过官网下载安装配置,并通过配置tnsnames.ora文件及环境变... 目录一、PL/SQL Developer 简介二、PL/SQL Developer 安装及配置详解1.下
阅读更多...
oracle如何连接登陆SYS账号

oracle如何连接登陆SYS账号

《oracle如何连接登陆SYS账号》在Navicat12中连接Oracle11g的SYS用户时,如果设置了新密码但连接失败,可能是因为需要以SYSDBA或SYSOPER角色连接,解决方法是确保在连接... 目录oracle连接登陆NmOtMSYS账号工具问题解决SYS用户总结oracle连接登陆SYS账号
阅读更多...
Oracle数据库如何切换登录用户(system和sys)

Oracle数据库如何切换登录用户(system和sys)

《Oracle数据库如何切换登录用户(system和sys)》文章介绍了如何使用SQL*Plus工具登录Oracle数据库的system用户,包括打开登录入口、输入用户名和口令、以及切换到sys用户的... 目录打开登录入口登录system用户总结打开登录入口win+R打开运行对话框，输php入：sqlp
阅读更多...
查询Oracle数据库表是否被锁的实现方式

查询Oracle数据库表是否被锁的实现方式

《查询Oracle数据库表是否被锁的实现方式》本文介绍了查询Oracle数据库表是否被锁的方法,包括查询锁表的会话、人员信息,根据object_id查询表名,以及根据会话ID查询和停止本地进程,同时,... 目录查询oracle数据库表是否被锁1、查询锁表的会话、人员等信息2、根据 object_id查询被
阅读更多...
解决Spring运行时报错:Consider defining a bean of type ‘xxx.xxx.xxx.Xxx‘ in your configuration

解决Spring运行时报错:Consider defining a bean of type ‘xxx.xxx.xxx.Xxx‘ in your configuration

《解决Spring运行时报错:Considerdefiningabeanoftype‘xxx.xxx.xxx.Xxx‘inyourconfiguration》该文章主要讲述了在使用S... 目录问题分析解决方案总结问题Description:Parameter 0 of constructor in x
阅读更多...
Oracle查询优化之高效实现仅查询前10条记录的方法与实践

Oracle查询优化之高效实现仅查询前10条记录的方法与实践

《Oracle查询优化之高效实现仅查询前10条记录的方法与实践》：本文主要介绍Oracle查询优化之高效实现仅查询前10条记录的相关资料,包括使用ROWNUM、ROW_NUMBER()函数、FET... 目录1. 使用 ROWNUM 查询2. 使用 ROW_NUMBER() 函数3. 使用 FETCH FI
阅读更多...
数据库oracle用户密码过期查询及解决方案

数据库oracle用户密码过期查询及解决方案

《数据库oracle用户密码过期查询及解决方案》：本文主要介绍如何处理ORACLE数据库用户密码过期和修改密码期限的问题,包括创建用户、赋予权限、修改密码、解锁用户和设置密码期限,文中通过代码介绍... 目录前言一、创建用户、赋予权限、修改密码、解锁用户和设置期限二、查询用户密码期限和过期后的修改1.查询用
阅读更多...
Oracle数据库使用 listagg去重删除重复数据的方法汇总

Oracle数据库使用 listagg去重删除重复数据的方法汇总

《Oracle数据库使用listagg去重删除重复数据的方法汇总》文章介绍了在Oracle数据库中使用LISTAGG和XMLAGG函数进行字符串聚合并去重的方法,包括去重聚合、使用XML解析和CLO... 目录案例表第一种：使用wm_concat() + distinct去重聚合第二种：使用listagg，
阅读更多...
oracle中exists和not exists用法举例详解

oracle中exists和not exists用法举例详解

《oracle中exists和notexists用法举例详解》：本文主要介绍oracle中exists和notexists用法的相关资料,EXISTS用于检测子查询是否返回任何行,而NOTE... 目录基本概念：举例语法pub_name总结 exists （sql 返回结果集为真）not exists (s
阅读更多...

Copyright China编程 23002807@qq.com

沪ICP备2023033072号-2