OGG_高尔德enGate数据库配置DDL同步(案例)

2014-03-08 Created By
BaoXinjian

图片 1**一、摘要**


  1. 不接济和少数援助的DDL类型

(1).
Oracle数据库保留的Schema,高尔德(Gold)enGate是不协助的,如SYS、SYSTEM、PUBLIC等等

(2). 高尔德(Gold)enGate10g
DDL不帮助Oracle回收站成效

(3).
高尔德(Gold)enGate帮助不超高2M长度的DDL语句

(4).
高尔德enGate只协理单向的DDL复制,不协理双向的DDL复制

(5).
高尔德enGate只协理源端和对象端结构向来的

  1. DDL处理措施

(1). 不协理DDL类型的拍卖方法

a.
不帮衬的Schema,OGG默许不复制,不许越多设定

b. 关闭Oracle回收站:alter system set
recyclebin=off scope=both

(2). 受限辅助DDL类型的处理费方法

a.
通过脚本ddl_ddl2file.sql获取被忽视的Oracle
DDL操作,将其赢得的操作放在USER_DUMP_DEST目录一个文件文件中

b.
投递进度中必须运用ASSUMETARGETDEFS参数

  1. DDL复制的配备

(1). Oracle DDL复制的规律

(2). 安装GoldenGate DDL对象

(3). 配置DDL支持

(4). DDL非凡与错误处理

 

图片 2**二、Oracle DDL复制的规律**


  1. DDL复制原理

依照数据库全局Trigger的复制,在源数据建立一个Oracle全库级其他Trigger捕捉DDL操作到中间表,Extract读取中间表DDL语句并与DML语句依据csn排序,Pump投递到目的端,目的端Replicat在复出该DDL语句

  1. DDL复制和DML复制的区分

(1). DDL复制是按照Trigger

(2). DML复制是根据日志

(3).
只是在主Extract进度中经过scn号安装发生顺序进行组装,保障DDL操作和DML操作依据原来的一一之下你给

  1. DDL复制和DML复制的涉嫌

DDL复制和DML复制可以项目独立,可以只启动DDL复制,也足以只启动DML复制,互相并不影响,他们之间只是在Extract举办组装时根据scn号举行排序

 

图片 3**三、安装GoldenGate
DDL对象**


Step1. 停止Extract进程组

[ggate@gavinprod ~]$ cd $GGATE
[ggate@gavinprod ggate]$ ./ggsci
Oracle GoldenGate Command Interpreter for Oracle
Version 11.2.1.0.1 OGGCORE_11.2.1.0.1_PLATFORMS_120423.0230_FBO
Linux, x64, 64bit (optimized), Oracle 11g on Apr 23 2012 08:32:14
Copyright (C) 1995, 2012, Oracle and/or its affiliates. All rights reserved.

GGSCI (gavinprod.com) 1> stop E*
EXTRACT EXT1 is already stopped.
EXTRACT EXT2 is already stopped.

Step2. 指定源端数据库的方式

GGSCI (gavinprod.com) 2> edit params ./GLOBALS
GGSCHEMA ggate

Step3. 停止Manager进程

GGSCI (gavinprod.com) 5> stop MGR !
Manager is already stopped.

Step4. 关闭Oracle的recyclebin

[oracle@gavinprod bin]$ sqlplus '/as sysdba'
SQL*Plus: Release 11.2.0.1.0 Production on Fri Jan 30 22:50:10 2015
Copyright (c) 1982, 2009, Oracle.  All rights reserved.
Connected to an idle instance.

SQL> show parameter recyclebin
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
recyclebin                           string      OFF

SQL> alter system set recyclebin = off scope = both;

Step5. 安装DDL对象

[oracle@gavinprod ggate]$ sqlplus '/as sysdba'

SQL*Plus: Release 11.2.0.1.0 Production on Fri Jan 30 22:54:49 2015

Copyright (c) 1982, 2009, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> @/opt/oracle/ggate/marker_setup.sql

SQL> @/opt/oracle/ggate/ddl_setup.sql

SQL> @/opt/oracle/ggate/role_setup.sql

SQL> @/opt/oracle/ggate/ddl_enable.sql

Step6. 验证DDL对象

[oracle@gavinprod ggate]$ sqlplus '/as sysdba'
SQL*Plus: Release 11.2.0.1.0 Production on Fri Jan 30 22:54:49 2015
Copyright (c) 1982, 2009, Oracle.  All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> @/opt/oracle/ggate/marker_status.sql

 

图片 4**四、配置DDL支持**


Step1. 停止Extract 和
Replicat进程组

--Extract 端
GGSCI (gavinprod.com) 1> stop E*
EXTRACT EXT1 is already stopped.
EXTRACT EXT2 is already stopped.

GGSCI (gavinprod.com) 2> stop D*
EXTRACT DMP2 is already stopped.


--Replicat 端
GGSCI (odellprod.com) 1> stop R*
REPLICAT REP1 is already stopped.
REPLICAT REP2 is already stopped.

Step2. 修改Extract配置文件

GGSCI (gavinprod.com) 6> view params EXT2
extract ext2
userid ggate@gavinprod, password oracle
exttrail /opt/oracle/ggate/dirdat/ld

DDL INCLUDE ALL
DDLOPTIONS ADDTRANDATA, REPORT
table source_prod.*;

Step3. 修改Replicat配置文件

GGSCI (odellprod.com) 2> view params REP2

replicat rep2
ASSUMETARGETDEFS
userid ggate@odellprod,password oracle
discardfile /opt/oracle/ggate/dirdat/rep2_discard.txt, append, megabytes 10

DDL INCLUDE MAPPED
DDLOPTIONS REPORT
map source_prod.*, target target_prod.*;

Step4. 重启进度

--Extract 端
GGSCI (gavinprod.com) 1> start E*
GGSCI (gavinprod.com) 2> start D*

--Replicat 端
GGSCI (odellprod.com) 1> start R*

Step5. 验证结果

  1. 对象端建立table bxj_ogg_ddl
create table bxj_ogg_ddl (id number, name varchar2 (100) ); 
  1. 对象端查看所创办的table是还是不是留存

    select * from dba_objects where object_name = ‘BXJ_OGG_DDL’;

  2. 万一存在,测试成功

 

Thanks and Regards

图片 5

相关文章