- 浏览: 4373103 次
- 性别:
- 来自: 厦门
文章分类
- 全部博客 (634)
- Oracle日常管理 (142)
- Oracle体系架构 (45)
- Oracle Tuning (52)
- Oracle故障诊断 (35)
- RAC/DG/OGG (64)
- Oracle11g New Features (48)
- DataWarehouse (15)
- SQL, PL/SQL (14)
- DB2日常管理 (9)
- Weblogic (11)
- Shell (19)
- AIX (12)
- Linux/Unix高可用性 (11)
- Linux/Unix日常管理 (66)
- Linux桌面应用 (37)
- Windows (2)
- 生活和工作 (13)
- 私人记事 (0)
- Python (9)
- CBO (15)
- Cognos (2)
- ORACLE 12c New Feature (2)
- PL/SQL (2)
- SQL (1)
- C++ (2)
- Hadoop大数据 (5)
- 机器学习 (3)
- 非技术 (1)
最新评论
-
di1984HIT:
xuexilee!!!
Oracle 11g R2 RAC高可用连接特性 – SCAN详解 -
aneyes123:
谢谢非常有用那
PL/SQL的存储过程和函数(原创) -
jcjcjc:
写的很详细
Oracle中Hint深入理解(原创) -
di1984HIT:
学习了,学习了
Linux NTP配置详解 (Network Time Protocol) -
avalonzst:
大写的赞..
AIX内存概述(原创)
前言
OS:RHEL 5.4 Oracle:10.2.0.1 GoldenGate:11.2.0.1 For Oracle 10g
注意事项:
1、用户的默认表空间不要指定为SYSTEM,否则在初始化DDL支持时可能会遇到段无法扩展,或包无法编译等错误。
正式应用的话,需要注意ogg用户所在表空间是否有充足的空间。
2、以SYSDBA身份登录到SQL*Plus,此时应该断开所有连接到ORACLE数据库的会话,并且不再允许创建新会话,仅保留刚刚创建的SYSDBA身份登录的SQL*Plus。
配置DDL支持
1、关闭数据库回收站:
SQL>alter system set recyclebin=off scope=both;
2、编辑globals参数文件,标明支持DDL的GG对象存放在哪个schema下:
GGSCI>view param ./globals
GGSCHEMA ogg
如果由于某些特殊的需求自定义DDL相关对象的名称的话,可以通过重定义位于GoldenGate软件安装目录下params.sql文件中的相关变量值,再配置GLOBALS,新增对象名称的对应关系,例如:
MARKERTABLE <new_table_name>
DDLTABLE <new_table_name>
前者表示Marker表,后一个表示DDL历史表。
一般情况下不需要修改,ORACLE也建议保持数据库对象默认值,该操作为可选操作
3、执行所需脚本
备忘清单
marker_setup.sql
ddl_setup.sql
role_setup.sql
Grant the role to all GoldenGate Extract users
ddl_enable.sql
install and use the optional performance tool
$ cd /home/oracle/ogg
$ sqlplus / as sysdba;
marker_setup.sql
该脚本用于创建DDL的marker表GGS_MARKER,用于存储DDL信息,该表只进行insert操作
SQL> @marker_setup
Marker setup 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
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.
ddl_setup.sql
该脚本创建了进行DDL复制抽取和复制所需的对象
SQL> @ddl_setup
GoldenGate DDL Replication setup script
Verifying that current user has privileges to install DDL Replication...
Checking user sessions...
Check complete.
You will be prompted for the name of a schema for the 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 GoldenGate schema name: ogg
You 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:INITIALSETUP
Working, please wait ...
Spooling to file ddl_setup_spool.txt
Using ogg as a GoldenGate schema name, INITIALSETUP as a mode of installation.
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
DDLORA_GETTABLESPACESIZE STATUS:
.....................
.....................
Analyzing installation status...
STATUS OF DDL REPLICATION
-----------------------------------------------------------------------------------
SUCCESSFUL installation of DDL Replication software components
Script complete.
role_setup.sql
创建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.
按照上面返回的提示,执行授权操作,注意是将权限授予执行Extract/GGSCI或其它管理操作的用户,我们这里的情况看显然是ggate,执行命令如下:
SQL> GRANT GGS_GGSUSER_ROLE TO GGATE;
Grant succeeded.
ddl_enable.sql
启用DDL复制,实质上是创建触发器,用以想MARKER和HISTORY表插入DDL信息
SQL> @ddl_enable
Trigger altered.
安装性能优化工具 (可选项)
要提高DDL触发器的性能,可以通过ddl_pin脚本,该脚本会将触发器使用的包加载到内存,以此提高效率。该脚本执行时需要引用dbms_shared_pool系统包,因此在使用ddl_pin脚本前需要确保dbms_shared_pool可用。
SQL> @?/rdbms/admin/dbmspool.sql
Package created.
Grant succeeded.
View created.
Package body created.
执行ddl_pin脚本需要指定GoldenGate管理员schema名称,例如:
SQL> @ddl_pin ggate
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
4、配置抽取和复制进程
抽取进程配置如下
GGSCI (rac1) 44> view params eora
extract eora
userid ogg,password ogg
exttrail ./dirdat/et
rmthost 192.168.1.112, mgrport 7500
rmttrail ./dirdat/rt
ddl
include all
table hr.*;
抽取复制配置如下
GGSCI (rac2) 17> view params pora
replicat pora
setenv(ORACLE_SID=orcl)
userid ogg, password ogg
assumetargetdefs
handlecollisions
discardfile ./dirrpt/pora.dsc, purge
ddl include all
ddlerror default ignore retryop
map hr.*, target hr.*;
源端进程状态
GGSCI (rac1) 45> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING EORA 00:00:00 00:00:00
目标端进程状态
GGSCI (rac2) 90> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
REPLICAT RUNNING PORA 00:00:00 00:00:00
验证
在Source DB 上创建一张测试表:
SQL> conn dave/dave;
Connected.
SQL> create table anqing as select *from sys.all_users;
Table created.
到Target DB上验证:
SQL> select count(1) from anqing;
COUNT(1)
----------
33
DDL 同步验证成功。
DDL参数语法说明
在配置DDL复制时,需要在Extract 和Replicat 进程的配置文件里添加ddl属性。下面简要减少下这个属性的常用参数,参考至《OGG Windows and UNIX Reference Guide》,英文较简单,我也就不一字一句的翻译了。
DDL语句包含的主要语法如下:
DDL [
{INCLUDE | EXCLUDE}
[, MAPPED | UNMAPPED | OTHER | ALL]
[, OPTYPE <type>]
[, OBJTYPE ‘<type>’]
[, OBJNAME “<name>”]
[, INSTR ‘<string>’]
[, INSTRCOMMENTS ‘<comment_string>’]
]
[...]
{INCLUDE | EXCLUDE}
Use INCLUDE and EXCLUDE to identify the beginning of an inclusion or exclusion clause.
(1) An inclusion clause containsfiltering criteria that identifies the DDL that this parameter will affect.
(2) An exclusion clause containsfiltering criteria that excludes specific DDL from this parameter.
The inclusion orexclusion clause must consist of the INCLUDE or EXCLUDE keyword followed by anyvalid combination of other options of the parameter that is being applied.
If you use EXCLUDE, you must create acorresponding INCLUDE clause.
--如果要使用Exclude,那么必须先指定include。
For example, the following is invalid:
DDL EXCLUDE OBJNAME “hr.*”
--这种语法是错误的。
However, you can use either of thefollowing:
DDL INCLUDE ALL,EXCLUDE OBJNAME “hr.*”
DDL INCLUDE OBJNAME “fin.*” EXCLUDE “fin.ss”
--正确语法
An EXCLUDE takes priority over any INCLUDEs that contain the same criteria. You can use multipleinclusion and exclusion clauses.
[, MAPPED | UNMAPPED | OTHER | ALL]
Use MAPPED, UNMAPPED,OTHER, and ALL to apply INCLUDE or EXCLUDE based on the DDL operation scope.
1、MAPPED applies INCLUDE or EXCLUDE to DDL operations that are of MAPPED scope. MAPPED filtering is performed beforefiltering that is specified with other DDL parameter options.
2、UNMAPPED applies INCLUDE or EXCLUDE to DDL operations that are of UNMAPPED scope.
3、OTHER applies INCLUDE or EXCLUDE to DDL operations that are of OTHER scope.
4、ALL applies INCLUDE or EXCLUDE to DDL operations of all scopes.
关于这四种scope的具体范围详见官方文档《OGG Windows and UNIX Reference Guide》
OPTYPE <type>
Use OPTYPE toapply INCLUDE or EXCLUDE to a specific type of DDL operation, such as CREATE, ALTER,and RENAME. For <type>, use any DDL command that is valid for thedatabase.
For example, to include ALTER operations,the correct syntax is:
DDL INCLUDE OPTYPE ALTER
OBJTYPE‘<type>’
Use OBJTYPE to apply INCLUDE or EXCLUDE to a specific type of database object. For <type>,use any object type that is valid for the database, such as TABLE, INDEX, and TRIGGER.For an Oracle materialized view and materialized views log, the correct typesare snapshot and snapshot log, respectively. Enclose the name of the objecttype within single quotes.
For example:
DDL INCLUDE OBJTYPE ‘INDEX’
DDL INCLUDE OBJTYPE ‘SNAPSHOT’
For Oracle object type USER, do not use the OBJNAME option, because OBJNAME expects “owner.object” whereas USER only has aschema.
OBJNAME“<name>”
Use OBJNAME toapply INCLUDE or EXCLUDE to the fully qualified name of an object, for example owner.table_name.This option takes a doublequoted string as input. You can use a wildcard onlyfor the object name.
Example:
DDL INCLUDE OBJNAME “accounts.*”
Do not use OBJNAME for the Oracle USER object,because OBJNAME expects “owner.object” whereas USER only has a schema.
When using OBJNAMEwith MAPPED in a Replicat parameter file, the value for OBJNAME must refer tothe name specified with the TARGET clause of the MAP statement. For example,given the following MAP statement, the correct value is OBJNAME “fin2.*”.
MAP fin.exp_*,TARGET fin2.*;
In the following example, a CREATE TABLE statementexecutes like this on the source:
CREATE TABLE fin.exp_phone;
And like this on the target:
CREATE TABLE fin2.exp_phone;
If a target owner is not specified in the MAP statement, Replicat maps it to the database user that is specified with the USERID parameter.
For DDL that creates triggers, synonyms, and indexes, the value for OBJNAME must be the name of the base object, not the name of the trigger, synonym, or index.
For example, to include the following DDL statement, the correct value is “hr.accounts,” not “hr.insert_trig.”
CREATE TRIGGER hr.insert_trig ON hr.accounts;
For RENAME operations,the value for OBJNAME must be the new table name. For example, to include the following DDL statement, the correct value is “hr.acct.”
ALTER TABLE hr.accounts RENAME TO acct;
The following is an example of how tocombine DDL parameter options.
DDL &
INCLUDE UNMAPPED &
OPTYPE alter &
OBJTYPE ‘table’ &
OBJNAME users.tab* &
INCLUDE MAPPED OBJNAME * &
EXCLUDE MAPPED OBJNAME temporary.tab"
参考至: 《OGG Windows and UNIX Reference Guide》
《OGG Installation and Setup Guide》
http://junsansi.itpub.net/post/29894/521800
http://blog.csdn.net/tianlesoftware/article/details/6981358
本文原创,转载请注明出处、作者
如有错误,欢迎指正
邮箱:czmcj@163.com
发表评论
-
Duplicate a Database Using RMAN in Oracle Database 11g Release 2
2016-11-03 15:38 945Duplicate a Database Using RMA ... -
grid control 安装总结 (三)之EMD upload error: uploadXMLFiles skipped或忘记OMS密码
2014-11-17 17:16 2777[oracle@anbob agent10g]$ bin/e ... -
Grid Control服务端看不到Agent相关信息,同时OMS状态为unknown处理
2014-11-17 17:14 2148一次Grid Control的agent是启动的,但是在GC ... -
给11gR2 RAC添加LISTENER监听器并静态注册
2014-10-22 17:52 3158之前有同学想要给11gR2的RAC添加LISTENER监听器 ... -
HOW TO REMOVE CRS AUTO START AND RESTART FOR A RAC INSTANCE
2014-10-12 20:08 1461Subject: HOW TO REMOVE CRS AU ... -
How To Enable Archive Logging In RAC Environment [ID 1186764.1]
2014-10-12 19:45 1015How To Enable Archive Logging ... -
Oracle 11g RAC使用Manual和Policy Managed方法配置
2014-10-12 17:25 8862软件环境: 操作系统:Red Hat Enterpris ... -
Moving OCR and Voting Disk to Oracle ASM Online
2014-10-12 16:35 2628Prior Oracle 11gR2, it was only ... -
Oracle 11g R2的启动与关闭(原创)
2014-10-10 15:04 27186概述 Oracle 11gR2 中对CRSD资源进行了重新 ... -
重新配置与卸载11gR2 Grid Infrastructure
2014-10-10 08:24 2291概述Oracle 11g R2 Grid Infrastru ... -
Data Guard Broker Properties
2014-10-07 16:15 967Data Guard Broker Properties ... -
Data Guard - Snapshot Standby Database配置
2014-10-07 07:39 933概述一般情况下,物理standby数据库处于mount状态 ... -
Active Data Guard
2014-10-06 19:27 870Oracle Active Data Guard is a ... -
Data Guard Broker系列之六:Fast-Start Failover
2014-10-06 18:29 3068Fast-Start Failover 基本概念 Fa ... -
Data Guard Broker系列之五:数据库角色转换
2014-10-06 18:28 1228数据库转换的基本概 ... -
Data Guard Broker系列之四:数据库管理
2014-10-06 18:27 1196数据库状态管理 数据 ... -
Data Guard Broker系列之三:Data Guard Broker配置管理
2014-10-06 16:56 1262配置data guard broker配置文件基本参数 ... -
Data Guard Broker系列之二:Data Guard Broker配置实战
2014-10-05 16:16 1579配置之前DG环境状态 测试的DG环境安装在同一个机器上, ... -
Data Guard broker系列之一:Data Guard Broker基础
2014-10-05 16:13 2274Data Guard broker概览 Data ... -
Dataguard切换保护模式(从MAXIMUM PERFORMANCE 到 maximize availability)
2014-10-05 14:52 12411. 首先查看主库当前保护模式(主库操作)SQL> s ...
相关推荐
goldengate配置ddl,网络资源大家学习参考。
goldengate安装配置goldengate安装配置goldengate安装配置goldengate安装配置goldengate安装配置goldengate安装配置goldengate安装配置goldengate安装配置goldengate安装配置goldengate安装配置
配置GoldenGate同步DDL 详细内容及命令
介绍了ogg的单向,双向,复制传输,DML DDL的安装配置以及排错。 主要是介绍的windows平台。
Oracle GoldenGate 安装配置详细手册
Goldengate安装配置详细说明,,GoldenGate软件提供了一个单一的平台,这个平台可以为任何企业环境实现秒一级的灾难备份。GoldenGate是一种基于日志的结构化数据复制方式,它通过解析源数据库在线日志或归档日志获得...
oracle goldengate安装配置
(完整word版)Oracle-GoldenGate-11g单向DDL配置实战.doc
GoldenGate安装配置两小时教程,对于初级入门来说应该有用。 GoldenGate安装配置 –软件下载 –操作系统准备 –数据库准备 –软件安装 –源端OGG配置 –目标端OGG配置
Oracle GoldenGate安装配置.doc
基于实时数据库的GoldenGate数据同步,包括GoldenGate安装配置等
Goldengate安装配置文档.pdf
OracleGoldenGate手册,是我详细整理出来的,其中还包含了遇到的问题以及处理方案,源端数据库有:DB2/oracle/mysql 目标端有:数据库/kafka,如果有问题,欢迎提出相关意见!
7.2 GoldenGate的GLOBALS配置文件内容 44 7.3 GoldenGate的eiex01配置文件内容 44 7.4 GoldenGate的epmp01配置文件内容 44 7.5 GoldenGate的rora01配置文件内容 45 7.6 GoldenGate的eiexbb配置文件内容 45 7.7 ...
Oracle GoldenGate 安装配置windows32.docx
OracleGoldenGateDirector配置手册.pdf
GoldenGate安装配置文档及概要使用说明.doc
OracleGoldengate使用部分参数详解 OracleGoldengate使用部分参数详解
linux 下 oracle goldengate
内容包括介绍、配置、维护三篇文档,凭此资料完成可以完成goldengate的配置。