I:安装包版本及下载
a.安装包：
Ogg version:
121210_ggs_Windows_x64_shiphome.zip
Oracle version:
win64_11gR2_database
b.下载ogg最新版本:
--》downloads-->middleware-->goldengate-->
AcceptLicense
Agreement 勾上同意
12cwindows下最新版本是：
点击下载，然后跳转到license登陆界面：
我的账号：
我的密码：824661247!@#qweZl
下载的文件名称如下：
121210_ggs_Windows_x64_shiphome.zip
II:构思(适用场景：数据仓库读写分离前台报表)
a.测试拓扑图
b.测试环境准备
准备1台windows
7_64bit虚拟机: win7-1
Win7-1 ip地址：10.21.45.85源数据库：sourcedb
目标数据库：targetdb和db3(2个目标数据库设置一致即可)
C.测试需要达到的目标
Configuring DML AND DDL Support
第一步：仅安装数据库软件
第二步：配置数据库
a.连接字符串配置
1.使用net Manger配置连接字符串
SOURCE =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST =10.21.45.85)(PORT
= 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = sourcedb)
)
)
TARGET =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST
= 10.21.45.85)(PORT
= 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = targetdb)
)
)
DB3 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)( HOST =10.21.45.85)(PORT
= 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = db3)
)
)
b.目标库DB3上设置(在targetdb目标库上也执行同样的操作)
1.创建ogg用户授予dba角色权限(ogg用户用于goldengate进程replicat访问分析redo
log)
2.创建receiver用户授予dba角色权限(receiver用户用于目标库测试演示源库dml和ddl操作后效果查看)
Cmdà
Microsoft
Windows [版本6.1.7601]
版权所有(c) 2009 Microsoft Corporation。保留所有权利。
C:\Users\Administrator>sqlplus
sys/oracle@db3 as sysdba
SQL*Plus:
Release11.2.0.1.0 Production on星期日4月19 19:58:18 2015
Copyright
(c) 1982, 2010, Oracle. All rights
reserved.
连接到:
Oracle
Database11gEnterprise Edition
Release11.2.0.1.0 - 64bit Production
With the
Partitioning, OLAP, Data Mining and Real Application Testing options
SQL>
sho parameter db_name
NAME TYPE VALUE
------------------------------------
----------- ------------------------------
db_name string db3
SQL>
create user ogg identified by oracle default tablespace users;
用户已创建。
SQL>
grant dba to ogg;
授权成功。
SQL>
create user receiver identified by oracle default tablespace users;
用户已创建。
SQL>
conn sys/oracle@db3 as sysdba
已连接。
SQL>
grant dba to receiver;
授权成功。
SQL>
conn receiver/oracle@db3
已连接。
SQL>
create table t(id int);
表已创建。
c.源库sourcedb上设置
1.创建ogg用户授予dba角色权限(ogg用户用于goldengate进程extract访问分析redo
log)
2.创建sender用户授予dba角色权限(sender用户用于源库测试演示session的dml和ddl操作)
Cmdà命令类同目标库设置
第三步：数据库归档处理(sourcedb,targetdb,db3均设置归档)
一键归档脚本如下：
conn
sys/oracle as sysdba
alter
database add supplemental log data;
alter
database force logging;
shutdown
immediate
startup
mount
alter
database archivelog;
alter
database open;
select
log_mode,supplemental_log_data_min,force_logging from v$database;
第四步：安装ogg软件
1. 双击执行setup.exe
解压ogg安装压缩包：
C:\Users\Administrator\Desktop\121210_ggs_Windows_x64_shiphome\ggs_Windows_x64_shiphome\Disk1\setup.exe双击运行
2.下载这个文件安装即可：
第五步：配置ogg
注册服务：GGSCI (win7) 1> install
addservice addevents(12c的貌似不用注册)
1.GGSCI (win7) 1>create subdirs
源库上和目标库上都创建，创建目录文件后(比之前多了10个文件夹)
2.源库上创建并开启管理进程：
GGSCI
(win7) 2> edit params mgr
GGSCI
(win7) 3>Start mgr
3.源库上创建extract
GGSCI
(win7) 4> add extract ext4, tranlog, begin now
EXTRACT
added.
GGSCI
(win7) 5> info all
Program Status
Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT STOPPED
EXT1 00:00:00 00:00:04
EXTRACT STOPPED
EXT2 00:00:00 00:00:04
4添加本地trail文件
GGSCI
(win7) 6> add exttrail C:\ogg\dirdat\dl, extract ext4
EXTTRAIL
added.
GGSCI
(win7) 8> view params ext4
extract ext4
setenv(ORACLE_SID='SOURCE')
DDL INCLUDE ALL
USERID OGG@SOURCE,
password oracle
rmthost win7, mgrport
7809
rmttrail
c:\ogg\dirdat\dl
table sender.*;
5.目标数据库创建checkpoint表
GGSCI
(win7) 9>edit params GLOBALS
GGSCHEMA OGG
CHECKPOINTTABLE
OGG.CKPT
GGSCI
(win7) 10>Dblogin userid ogg@target password oracle
GGSCI
(win7) 11>Add checkpointtable ogg.ckpt
6.目标库上创建replicat
GGSCI>add replicat rep4, exttrail c:\ogg\dirdat\dl, checkpointtable
ogg.ckpt
REPLICAT
added.
replicat
rep4
userid
ogg@target, password oracle
reperror
default abend
DDL
INCLUDE ALL
assumetargetdefs
map
sender.*, target receiver.*;
第六开启ddl操作
以下操作在源端、目标端都要执行
安装DDL相关对象时，建议退出所有应用程序，并防止数据库有新的连接，否则可能导致操作失败。
安装支持DDL同步对象，可通过下述命令实现：
1.创建ogg单独表空间tbs_ogg(用于存放goldengate
ddl对象)
C:\Users\Administrator>sqlplus
sys/oracle@source as sysdba
SQL*Plus: Release11.2.0.1.0
Production on 星期日 4月 19
21:06:37 2015
Copyright (c)
1982, 2010, Oracle. All rights reserved.
连接到:
Oracle Database11gEnterprise Edition Release11.2.0.1.0
- 64bit Production
With the Partitioning,
OLAP, Data Mining and Real Application Testing options
SQL> select
default_tablespace from dba_users where username='OGG';
DEFAULT_TABLESPACE
------------------------------
USERS
SQL> create
tablespace tbs_ogg datafile 'c:\oracle\sourcedata\tbs_ogg.dbf'size10mautoextend on nex
t10m;
表空间已创建。
SQL> alter user
ogg default tablespace tbs_ogg;
用户已更改。
SQL> select
default_tablespace from dba_users where username='OGG';
DEFAULT_TABLESPACE
------------------------------
TBS_OGG
SQL>
2. 赋予GoldenGate用户utl_file执行权限(最好断开所有数据库连接、停止监听)
SQL> conn sys/oracle@source
as sysdba
已连接。
SQL> grant
execute on utl_file to ogg;
授权成功。
SQL> conn
sys/oracle@target as sysdba
已连接。
SQL> grant
execute on utl_file to ogg;
授权成功。
SQL> conn
sys/oracle@db3 as sysdba
已连接。
SQL> grant
execute on utl_file to ogg;
授权成功。
3.c:\ogg\params.sql脚本设置
ddl_fire_error_in_trigger=‘TRUE’
allow_invisible_index_keys
= 'TRUE'
4.创建GLOBALS参数
GGSCI
(win7) 9>edit params GLOBALS
GGSCHEMA
OGG
《依次执行以下脚本》
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
5.OGG之DDL复制配置时执行marker_setup.sql hang的问题
C:\Users\Administrator>cd
c:\
c:\>cd
c:\ogg ---注意一定要先切换到脚本目录下在进入数据库@调用脚本，否则hang住
c:\ogg>sqlplus
sys/oracle@source as sysdba
SQL*Plus:
Release11.2.0.1.0 Production on星期日4月19 21:56:29 2015
Copyright
(c) 1982, 2010, Oracle. All rights
reserved.
连接到:
Oracle
Database11gEnterprise Edition
Release11.2.0.1.0 - 64bit Production
With the
Partitioning, OLAP, Data Mining and Real Application Testing options
SQL>
@marker_setup
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.
SQL>
6.SQL> @ddl_setup
--以下是执行脚本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
Oracle10gsource, the system
recycle bin must be disabled. For Oracle11gand later, i
t 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 STATUS
----------------------------------------------------------------------
ENABLED
STAYMETADATA IN
TRIGGER
----------------------------------------------------------------------
OFF
DDL TRIGGER SQL
TRACING
----------------------------------------------------------------------
0
DDL TRIGGER TRACE
LEVEL
----------------------------------------------------------------------
0
LOCATION OF DDL
TRACE FILE
----------------------------------------------------------------------------------------------------
--------------------
c:\oracle\diag\rdbms\sourcedb\source\trace/ggs_ddl_trace.log
Analyzing
installation status...
VERSION OF DDL
REPLICATION
----------------------------------------------------------------------------------------------------
--------------------
OGGCORE_12.1.2.1.0_PLATFORMS_140920.0203
STATUS OF DDL
REPLICATION
----------------------------------------------------------------------------------------------------
--------------------
SUCCESSFUL
installation of DDL Replication software components
Script complete.
--以上是执行脚本ddl_setup情况结束
7.SQL> @role_setup --以下是执行脚本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
已写入 file role_setup_set.txt
PL/SQL 过程已成功完成。
Role setup script
complete
Grant this role to
each user assigned to the Extract, GGSCI, and Manager processes, by using the
fol
lowing SQL
command:
GRANT
GGS_GGSUSER_ROLE TO
where
is the user assigned to the GoldenGate processes.
--以上是执行脚本role_setup情况结束
8.授予ogg角色ggs_ggsuser_role
SQL> grant ggs_ggsuser_role to ogg;
授权成功。
9.运行脚本
@ddl_enable.sql
SQL>
@ddl_enable.sql
触发器已更改10．将DDL相关对象pin到shared
pool
实施此操作需要调用dbms_shared_pool包，需要事先确认此包是否已安装。确认dbms_shared_pool包是否已安装，通过下述命令实现：desc dbms_shared_pool
将DDL对象pin到shared pool，通过下述命令实现：@ddl_pin ogg
SQL> desc dbms_shared_pool
PROCEDURE
ABORTED_REQUEST_THRESHOLD
参数名称 类型 输入/输出默认值?
------------------------------
----------------------- ------ --------
THRESHOLD_SIZE NUMBER IN
PROCEDURE KEEP
参数名称 类型 输入/输出默认值?
------------------------------
----------------------- ------ --------
NAME VARCHAR2
IN
FLAG CHAR IN DEFAULT
PROCEDURE PURGE
参数名称 类型 输入/输出默认值?
------------------------------
----------------------- ------ --------
NAME VARCHAR2
IN
FLAG CHAR IN DEFAULT
HEAPS NUMBER IN DEFAULT
PROCEDURE SIZES
参数名称 类型
输入/输出默认值?
------------------------------
----------------------- ------ --------
MINSIZE NUMBER IN
PROCEDURE UNKEEP
参数名称 类型 输入/输出默认值?
------------------------------
----------------------- ------ --------
NAME VARCHAR2
IN
FLAG CHAR IN DEFAULT
SQL> @ddl_pin ogg
PL/SQL 过程已成功完成。
PL/SQL 过程已成功完成。
PL/SQL 过程已成功完成。
cmd-->
Microsoft Windows [版本6.1.7601]
版权所有(c) 2009
Microsoft Corporation。保留所有权利。
C:\Users\Administrator>cd c:\ogg
c:\ogg>ggsci
Oracle GoldenGate Command Interpreter for
Oracle
Version12.1.2.1.0
OGGCORE_12.1.2.1.0_PLATFORMS_140920.0203
Windows x64 (optimized), Oracle11gon Sep 20 2014 07:17:50
Operating system character set identified
as GBK.
Copyright (C) 1995, 2014, Oracle and/or its
affiliates. All rights reserved.
GGSCI (win7) 1> info
all
Program
Status Group Lag at Chkpt Time Since Chkpt
MANAGER
STOPPED
EXTRACT
STOPPED EXT1 00:00:00 176:44:48
EXTRACT
STOPPED EXT2 00:00:00 172:11:22
EXTRACT
ABENDED EXT3 00:00:00 171:23:16
REPLICAT
STOPPED REP1
00:00:00 176:44:47
GGSCI (win7) 2> add
extract ext4, tranlog, begin now
EXTRACT added.
GGSCI (win7) 3> info all
Program
Status Group Lag at Chkpt Time Since Chkpt
MANAGER
STOPPED
EXTRACT
STOPPED EXT1 00:00:00
176:45:45
EXTRACT
STOPPED EXT2 00:00:00 172:12:19
EXTRACT
ABENDED EXT3 00:00:00 171:24:13
EXTRACT
STOPPED EXT4 00:00:00 00:00:04
REPLICAT
STOPPED REP1 00:00:00 176:45:44
GGSCI (win7) 5> add
exttrail c:\ogg\dirdat\dl, extract ext4
EXTTRAIL added.
GGSCI (win7) 6> edit
params ext4
GGSCI (win7) 8>
view params ext4
extract ext4
setenv(ORACLE_SID='SOURCE')
ddl include all
USERID OGG@SOURCE, password oracle
rmthost10.21.45.85,
mgrport 7809
rmttrail c:\ogg\dirdat\dl
table sender.*;
GGSCI (win7) 10> view params mgr
PORT 7809
GGSCI (win7) 11> edit params GLOBALS
GGSCI (win7) 12>
add replicat rep4, exttrail c:\ogg\dirdat\dl, checkpointtable ogg.checkpoint
REPLICAT added.
GGSCI (win7) 13> info all
Program
Status Group Lag at Chkpt Time Since Chkpt
MANAGER
STOPPED
EXTRACT
STOPPED EXT1 00:00:00 177:11:30
EXTRACT
STOPPED EXT2 00:00:00 172:38:04
EXTRACT
ABENDED EXT3
00:00:00 171:49:57
EXTRACT
STOPPED EXT4 00:00:00 00:25:49
REPLICAT
STOPPED REP1 00:00:00 177:11:29
REPLICAT
STOPPED REP2 00:00:00 00:00:04
GGSCI (win7) 14> edit params rep4
GGSCI (win7) 15> view params rep4
replicat rep2
userid ogg@target, password oracle
reperror default abend
assumetargetdefs
DDL INCLUDE MAPPED
DDLERROR DEFAULT IGNORE RETRYOP
map sender.*, target receiver.*;
GGSCI (win7) 16> start mgr
Manager started.
GGSCI (win7) 17>
start ext4
Sending START request to MANAGER ...
EXTRACT EXT4 starting
GGSCI (win7) 18>
info all
Program
Status Group Lag at Chkpt Time Since Chkpt
MANAGER
RUNNING
EXTRACT
STOPPED EXT1 00:00:00 177:20:08
EXTRACT
STOPPED EXT2 00:00:00 172:46:42
EXTRACT
ABENDED EXT3 00:00:00 171:58:36
EXTRACT
RUNNING EXT4 00:34:27 00:00:00
REPLICAT
STOPPED REP1 00:00:00 177:20:07
REPLICAT
STOPPED REP2 00:00:00 00:08:42
GGSCI (win7) 19>
start rep2
Sending START request to MANAGER ...
REPLICAT REP2 starting
GGSCI (win7) 20>
info all
Program
Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT
STOPPED EXT1 00:00:00 177:20:29
EXTRACT
STOPPED EXT2 00:00:00 172:47:03
EXTRACT
ABENDED EXT3 00:00:00 171:58:56
EXTRACT
RUNNING
EXT4 00:00:00 00:00:10
REPLICAT
STOPPED REP1 00:00:00 177:20:28
REPLICAT
RUNNING
REP2 00:00:00 00:00:02
Ok,成功！
1678
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
