本文档详细介绍了在Oracle 11g环境下，如何在Linux OEL5.8上安装和配置Golden Gate，包括在Source和Target端的管理进程、Extract进程、Replicat进程的设置，以及数据库配置、用户创建、文件复制、环境变量编辑等步骤。最后通过测试验证了数据复制的正确性。
软件环境：
oracle 11g
linux OEL5.8 64bit
在Source端，配置一个管理进程,添加一个Extract进程，添加一个本地队列路径，定义一个远端的接收队列路径。
在Target端，配置一个管理进程和添加一个Replicat进程，指定一个应用队列，即抽取进程定义的远端队列。
source： odd.up.com ORACLE_SID=PROD
target: odd2.up.com ORACLE_SID=PROD
在source 和target上执行：
1.配置GoldenGate用户
useradd -g oinstall -G dba gg
mkdir /u01/app/oracle/gg
chown -R oracle:oinstall /u01/app/oracle/gg
passwd gg
在source 和target上执行：
2.复制文件
su - oracle
cd /u01/app/oracle/gg
cp -r /mnt/share/oracle11g/64/ogg112101_fbo_ggs_Linux_x64_ora11g_64bit/ogg/* ./
在source 和target上执行：
编辑环境变量
export GGATE=/u01/app/oracle/gg
export PATH=$GGATE:$PATH
export LD_LIBRARY_PATH=$GGATE:$ORACLE_HOME/lib:/lib:/usr/lib
在source 和target上执行：
ggsci
create subdirs
二.配置Source database(单向复制，只需要配置source DB就可以了)
select log_mode,supplemental_log_data_min,force_logging from v$database;
alter system set recyclebin=off scope=spfile;
shutdown immediate
startup mount
alter database archivelog;
alter database force logging;
alter database add supplemental log data;
alter database open;
2.3.2 创建存放DDL信息的user并赋权(在source和target上执行：)
create tablespace gg datafile '/u01/app/oracle/oradata/PROD/gg01.dbf' size 100M autoextend on extent management local segment space management auto;
create user gg identified by gg default tablespace gg temporary tablespace temp;
grant connect,resource to gg;
grant execute on utl_file to gg;
退出所有使用Oracle 的session，然后使用SYSDBA权限的用户执行如下脚本：
--进入GG的目录，然后调用脚本：
[oracle@odd gg]$ cd /u01/app/oracle/gg
[oracle@odd gg]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Mon Aug 19 18:54:34 2013
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> @marker_setup.sql;
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:GG
Marker setup table script complete, running verification script...
Please enter the name of a schema for the GoldenGate database objects:
Setting schema name to GG
MARKER TABLE
-------------------------------
OK
MARKER SEQUENCE
-------------------------------
OK
Script complete.
SQL> @ddl_setup.sql;
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 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 Oracle GoldenGate schema name:GG
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 GG 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 GG
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
------------------------------------------------------------------------------------------------------------------------
/u01/app/oracle/diag/rdbms/prod/PROD/trace/ggs_ddl_trace.log
Analyzing installation status...
STATUS OF DDL REPLICATION
------------------------------------------------------------------------------------------------------------------------
SUCCESSFUL installation of DDL Replication software components
Script complete.
SQL> @role_setup.sql;
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:GG
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.
SQL> grant GGS_GGSUSER_ROLE to gg;
Grant succeeded.
SQL> @ddl_enable.sql;
Trigger altered.
SQL>
三.测试GG
经过第一和第二节的配置，GG 的配置基本完成，这里我们开始测试GG。
注意：
（1） 目标库的用户名和对象名称可以与源端不同，关键在于配置文件中要能够正确匹配。
（2） 配置源和目标两端tnsnames，保持互联互通。
--source database
create user sender identified by oracle default tablespace gg temporary tablespace temp;
grant connect,resource,dba to sender;
--target database
create user receiver identified by oracle default tablespace gg temporary tablespace temp;
grant connect,resource,dba to receiver;
3.2 在Source和Target 上配置Manager
[oracle@odd ~]$ cd /u01/app/oracle/gg
[oracle@odd gg]$ 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 (odd.up.com) 1> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER STOPPED
GGSCI (odd.up.com) 2> edit params mgr
添加内容：
PORT 7809
GGSCI (odd.up.com) 3> start manager
Manager started.
GGSCI (odd.up.com) 4> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
以上是在Source 库上执行的，在Target 库上执行同样的操作。
配置SourceDB 的复制队列
先连接到数据库，测试连接：
GGSCI (odd.up.com) 5> dblogin userid gg@prod , password gg
Successfully logged into database.
添加一个抽取：
GGSCI (odd.up.com) 6> add extract ext1 , tranlog , begin now
EXTRACT added.
GGSCI (odd.up.com) 7> add exttrail /u01/app/oracle/gg/dirdat/lt ,extract ext1
EXTTRAIL added.
修改抽取进程ext1参数：
GGSCI (odd.up.com) 8> edit params ext1
GGSCI (odd.up.com) 9> view params ext1
extract ext1
userid gg@prod, password ggate
rmthost odd2, mgrport 7809
rmttrail /u01/app/oracle/gg/dirdat/lt
ddl include mapped objname sender.*;
table sender.*;
GGSCI (odd.up.com) 10> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT STOPPED EXT1 00:00:00 00:01:57
配置TargetDB 同步队列
3.4 配置TargetDB同步队列
3.4.1 在Target端添加checkpoint表：
GGSCI (odd2.up.com) 5> edit params ./GLOBAL
GGSCI (odd2.up.com) 6> view params ./GLOBAL
GGSCHEMA gg
CHECKPOINTTABLE gg.checkpoint
添加如上2条记录。
GGSCI (odd2.up.com) 10> dblogin userid gg@prod2 , password gg
Successfully logged into database.
在这儿需要确保listener和tnsname都是能够配通的。
--说明，这个用户是在Source库启用DDL 创建的，我在Target库也创建了这个用户。
GGSCI (odd2.up.com) 12> add checkpointtable gg.checkpoint
Successfully created checkpoint table gg.checkpoint.
3.4.2 创建同步队列
GGSCI (odd2.up.com) 13> add replicat rep1 , exttrail /u01/app/oracle/gg/dirdat/lt, checkpointtable gg.checkpoint
REPLICAT added.
GGSCI (odd2.up.com) 14> edit params rep1
GGSCI (odd2.up.com) 15> view params rep1
replicat rep1
ASSUMETARGETDEFS
userid gg@prod2,password gg
discardfile /u01/app/oracle/gg/dirdat/rep1_discard.txt,append, megabytes 10
DDL
map sender.*, target receiver.*;
3.5开启同步
3.5.1 Source DB：
GGSCI (odd.up.com) 16> start ext1
Sending START request to MANAGER ...
EXTRACT EXT1 starting
GGSCI (odd.up.com) 17> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING EXT1 00:00:00 00:09:18
3.5.2 Target DB
GGSCI (odd2.up.com) 16> start rep1
Sending START request to MANAGER ...
REPLICAT REP1 starting
GGSCI (odd2.up.com) 17> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
REPLICAT RUNNING REP1 00:00:00 00:00:01
测试Data复制
我们在Source DB上的sender用户下创建一张表，然后看这张表是否同步到了Target DB的receiver用户下。
--Source DB：
SQL> conn sender/oracle
Connected.
SQL> create table zd as select * from sys.all_objects;
Table created.
SQL> commit
2 ;
Commit complete.
SQL> create table t (id number);
Table created.
SQL> commit;
Commit complete.
--Target DB：
SQL> conn receiver/oracle
Connected.
SQL> select * from tab;
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
ZD TABLE
1 row selected.
SQL> select count(*) from zd;
COUNT(*)
----------
71545
1 row selected.
SQL> select * from tab;
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
T TABLE
ZD TABLE
2 rows selected.
SQL> select count(*) from zd;
COUNT(*)
----------
71545
1 row selected.
SQL> select count(*) from t;
COUNT(*)
----------
0
1 row selected.
参考文章：http://blog.csdn.net/tianlesoftware/article/category/776328
1457
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
