Oracle Golden Gate配置使用手册 v2.0
环境：Oracle 11.2.0.4
OGG 11.1.1.0
Oracle -> Oracle 同版本复制
主库：172.16.57.26
备库：172.16.57.27
一、准备工作
1、开启主库归档日志、补充日志及force
logging（备库不需要同步到其他库，可以不开启）
alter database archivelog;
alter database add supplemental log data;
alter database force logging;
alter system set
enable_goldengate_replication=true scope=both;
2、关闭回收站（备库不需要同步到其他库，可以不开启）
alter system set recyclebin=off
scope=spfile;
3、创建OGG管理用户（主备库都要设置）
create user ogg identified by ogg account
unlock;
grant connect,resource to ogg;
grant select any dictionary to ogg;
grant select any table to ogg;
grant execute on utl_file to ogg;
grant restricted session to
ogg;
GRANT CREATE TABLE,CREATE SEQUENCE TO
OGG; (必须有的操作，后续会介绍)
grant dba to ogg;(可选)
二、安装OGG（主备库都需要安装）
1、创建OGG目录，并解压软件
mkdir /opt/app/OGG
chown oracle:oinstall /opt/app/OGG
chmod 775 /opt/app/OGG
unzip OGG11_Oracle11g_x86_64_Linux.zip
tar -xvf
ggs_Linux_x64_ora11g_64bit_v11_1_1_0_0_078.tar
2、设置环境变量
su - oracle
vi ~/.bash_profile
##添加OGG_HOME,PATH,LD_LIBRARY中的变量信息
export OGG_HOME=/opt/app/OGG
export
PATH=$ORACLE_HOME/bin:/usr/sbin:$ORACLE_HOME/OPatch:/opt/app/OGG:$PATH
export
LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib:/opt/app/OGG
3、安装OGG软件
~/.bash_profile 使变量生效
cd /opt/app/OGG
ggsci
GGSCI> create subdirs
三、数据初始化（Oracle initial load）
# 注意：同步过程中需要停止应用服务！！！
# initial load方式很慢，推荐其他同步方式请参考：五、其他同步方式
1、使用expdp/impdp将主库的表结构同步到备库
主库：
expdp \'/ as sysdba\' directory=DATA_PUMP_DIR
schemas=ICE1,ICE2 dumpfile=ICE1_METADATA.dmp
logfile=ICE1_METADATA.log content=metadata_only
备库：
impdp \'/ as sysdba\' directory=DATA_PUMP_DIR
dumpfile=ICE1_METADATA.dmp logfile=impdp_ICE1_metadata.log
禁用备库的触发器（迁移完成后，需要将触发器打开！）：
SQL> select owner,trigger_name from
all_triggers where owner in ('ICE1','ICE2');
OWNER
TRIGGER_NAME
---------------
------------------------------------------------------------------------------------------
ICE1
SHIELDLIST_TRIGGER
ICE1
TRG_APP_FACT_ADVCALLBACK_SYST
ICE1
TRG_APP_STG_FIRSTVISIT_SYST
ICE1
TRG_APP_FACT_ADVERTISERS_SYST
ICE2
EID_ID
alter trigger ICE1.SHIELDLIST_TRIGGER
disable;
alter trigger ICE1.TRG_APP_FACT_ADVCALLBACK_SYST
disable;
alter trigger ICE1.TRG_APP_STG_FIRSTVISIT_SYST
disable;
alter trigger ICE1.TRG_APP_FACT_ADVERTISERS_SYST
disable;
alter trigger ICE2.EID_ID disable;
2、主库基本配置：
A：配置并启动mgr进程
cd /opt/app/OGG
ggsci
GGSCI> edit params mgr
port 1357
GGSCI> start mgr
Manager started.
GGSCI> info mgr
Manager is running (IP port SFV490-1.1357).
B：配置数据同步用户
GGSCI> dblogin userid ogg,password ogg
GGSCI> add trandata ICE1.*
GGSCI> add trandata ICE2.*
3、备库基本配置
A：配置并启动mgr进程
cd /opt/app/OGG
ggsci
GGSCI> edit params mgr
port 1358
GGSCI> start mgr
Manager started.
GGSCI> info mgr
Manager is running (IP port
SFV490-1.1357).
4、主库初始化配置
A：配置extract进程
GGSCI> add extract e_ICE1 ,
sourceistable
GGSCI> info extract
*,tasks
EXTRACT
E_ICE1 Last
Started 2016-03-23 10:24 Status
STOPPED
Checkpoint
Lag
Not Available
Log Read Checkpoint Table
ICE2.CRM_AGG_USERBEHAVIOR_CALC1_BK
2016-03-23 11:55:40 Record 13569826
Task
SOURCEISTABLE
B:编辑extract进程参数
GGSCI (n1) 59> edit params e_ICE1
extract e_ICE1
userid ogg,password ogg
rmthost 172.16.57.27,mgrport
1358
rmttask replicat,group
r_ICE1
table ICE1.*;
table ICE2.*;
5、备库初始化配置
A：配置replicat进程
GGSCI> add replicat r_ICE1 , specialrun
GGSCI> info replicat *, TASKS
B:编辑replicat进程参数
GGSCI> edit params r_ICE1
replicat r_ICE1
assumetargetdefs
userid ogg,password ogg
discardfile ./dirrpt/r_ICE1.dsc,purge
map ICE1.*, target ICE1.*;
map ICE2.*, target
ICE2.*;
6、启动初始化进程
主库：
GGSCI>start e_ICE1
GGSCI> view report
e_ICE1
备库：
GGSCI>start r_ICE1
GGSCI> view report
r_ICE1
四、数据同步
1、配置DDL同步
A:备库 配置globals参数
GGSCI>view param ./GLOBALS
ggschema ogg
B:主库 执行DDL配置脚本
sqlplus / as sysdba
SQL>
@/opt/app/OGG/marker_setup.sql
输入OGG管理用户名：ogg
SQL>
@/opt/app/OGG/ddl_setup.sql
输入OGG管理用户名：ogg
注意1：此处可能会报错：ORA-04098: trigger
'SYS.GGS_DDL_TRIGGER_BEFORE' is invalid and
failed，同时OGG中的很多表和视图无法创建，原因主要由于OGG缺少权限引起，即便有
DBA权限也是不足的（OGG BUG），可以通过如下方法修复：
1）先将触发器关闭，否则执行任何sql都会包ORA-04098的错误
@/opt/app/OGG/ddl_disable.sql
2)赋予ogg对应权限
grant execute on utl_file to ogg;
grant restricted session to ogg;
GRANT CREATE TABLE,CREATE SEQUENCE TO OGG;
3)重新执行ddl_setup.sql
注意2：当主库上有很多应用连接时，执行该sql会出现如下报警：
IMPORTANT: Oracle sessions that used or may use
DDL must be disconnected. If you
continue, some of these sessions may cause DDL
to fail with ORA-6508.
To proceed, enter yes. To stop installation,
enter no.
Enter yes or no:
为了不影响主库，选no，选择一个时间点，停止应用再创建ddl。
如果不创建ddl，需要在主备库的ogg进程参数中添加truncate选项：
gettruncates，参考后面同步进程配置。
SQL>
@/opt/app/OGG/role_setup.sql
输入OGG管理用户名：ogg
SQL> GRANT GGS_GGSUSER_ROLE TO
OGG;
SQL>
@/opt/app/OGG/ddl_enable.sql
2、配置数据同步
A:主库 配置日志抓取进程
GGSCI> add extract m_ICE1, tranlog, begin
now, threads 1
GGSCI> add rmttrail
/opt/app/OGG/dirdat/ft,extract m_ICE1
GGSCI> edit params m_ICE1
extract m_ICE1
userid ogg,password ogg
rmthost 172.16.57.27, mgrport 1358
rmttrail /opt/app/OGG/dirdat/tf
discardfile
/opt/app/OGG/dirrpt/trail.dsc,append,megabytes 100
ddl include
mapped
table ICE1.*;
table ICE2.*;
注意：如果不支持ddl，那么添加 gettruncates
B:备库 配置日志解析进程
1）编辑globals参数
GGSCI> edit params ./GLOBALS
ggschema ogg
checkpointtable ogg.chkpnt_ICE1
2）创建checkpoint表
GGSCI> dblogin userid ogg,password ogg
GGSCI> add checkpointtable
ogg.chkpnt_ICE1
3)配置解析进程
GGSCI> add replicat s_ICE1,exttrail
/opt/app/OGG/dirdat/tf,checkpointtable ogg.chkpnt_ICE1
GGSCI> info replicat *, TASKS
GGSCI> edit params s_ICE1
replicat s_ICE1
userid ogg,password ogg
discardfile
/opt/app/OGG/dirrpt/s_ICE1.dsc,append,megabytes 100
assumetargetdefs
ddl include all
ddlerror default ignore retryop
map ICE1.*, target ICE1.*;
map ICE2.*, target ICE2.*;
注意：如果不支持ddl，那么添加 gettruncates
C:启动同步进程
主库：GGSCI> start m_ICE1
备库：GGSCI> start s_ICE1
D:数据验证测试（略）
主库：
SQL> set linesize 300 pagesize 300
SQL> col owner for a15
SQL> col segment_name for a40
SQL> select owner,segment_name,bytes from
dba_segments where owner in ('ICE1','ICE2') and
segment_type='TABLE' and segment_name not like 'BIN$%' order by
bytes;
6 rows selected.
备库：
set linesize 300 pagesize 300
col owner for a15
col segment_name for a40
select owner,segment_name,bytes from
dba_segments where owner in ('ICE1','ICE2') and
segment_type='TABLE' and segment_name not like 'BIN$%' order by
bytes;
6 rows selected.
五、其他同步方式
A：通过Oracle Data Pump方式
1）配置并启动主库extract进程,参考：四、数据同步
主库：start extract m_ICE1
alter system switch logfile;
alter system checkpoint;
2）查看并记住主库scn
SQL> select dbms_flashback.get_system_change_number from
dual;
GET_SYSTEM_CHANGE_NUMBER
------------------------
31284766
2）在主库执行expdp备份
# 表结构如果没建立好，可以将content=data_only去掉
expdp \'/ as sysdba\' directory=DATA_PUMP_DIR schemas=ICE1,ICE2
dumpfile=ICE1_dataonly.dmp logfile=expdp_ICE1_dataonly.log
content=data_only flashback_scn=31284766
3）在备库执行impdp导入
impdp \'/ as sysdba\' directory=DATA_PUMP_DIR
dumpfile=ICE1_dataonly.dmp logfile=ICE1_dataonly.log
并关闭job、触发器
alter trigger ICE1.SHIELDLIST_TRIGGER disable;
alter trigger ICE1.TRG_APP_FACT_ADVCALLBACK_SYST disable;
alter trigger ICE1.TRG_APP_STG_FIRSTVISIT_SYST disable;
alter trigger ICE1.TRG_APP_FACT_ADVERTISERS_SYST disable;
alter trigger ICE2.EID_ID disable;
4）配置备库replicat进程，参考：四、数据同步
5）启动备库replicat进程
备库：start replicat s_ICE102,aftercsn 31284766
B:通过rman方式
1）配置并启动主库extract进程,参考：四、数据同步
主库：start extract m_ICE1
alter system switch logfile;
alter system checkpoint;
2）查看并记住主库scn
SQL> select dbms_flashback.get_system_change_number from
dual;
GET_SYSTEM_CHANGE_NUMBER
------------------------
31284766
3）backup database , controlfile and archivelog
4) restore database
5) recover database UNTIL SCN 31284766;
SELECT
CHECKPOINT_CHANGE#,CHECKPOINT_TIME FROM V$DATAFILE_HEADER;
6) alter database open resetlogs;
7) 配置备库replicat进程，参考：四、数据同步
8）启动备库replicat进程
备库：start replicat
s_ICE102,aftercsn 31284766
参考：http://www.askmaclean.com/archives/ogg-goldengate-initial-load-method.html
C:各同步方式优缺点
RMAN
优点：不需要停止业务,当主库数据量比较大时，推荐使用该方式。
缺点：不支持跨版本、跨平台
EXPDP/IMPDP
优点：不需要停业务，主备库版本不一致时推荐使用该方式。
缺点：支持10G以后的数据库使用。
EXP/IMP
优点：不需要停业务、低版本数据库可以使用此工具。
缺点：速度比数据泵要慢，数据量较大时，需要较长时间
GoldenGate Initial Load
优点：跨版本、跨平台。
缺点：速度比较慢，特殊情况下需要停机操作。
如果是跨数据库平台，如SQLSERVER到ORACLE，可以选用OGG自带的迁移功能GoldenGate Initial
Load。
六、故障处理：
A：同步进程中断：（主备库数据不一致导致repicat中断）
Opened trail file /u01/app/OGG/dirdat/tf000324 at 2016-03-28
16:03:14
Wildcard MAP resolved (entry ICE2.*):
map ICE2.CRM_AGG_USERBEHAVIOR_CALC1_BK, target
ICE2.CRM_AGG_USERBEHAVIOR_CALC1_BK;
2016-03-28 16:03:14 WARNING
OGG-00869 No unique key is defined for table
CRM_AGG_USERBEHAVIOR_CALC1_BK. All viable columns will be used to
represent the key, but may not guarant
ee uniqueness. KEYCOLS may be used to define the
key.
Using following columns in default map by name:
EUTIME, EID, USERID, ISPREMIUM, ISPREMIUMGS,
ISPREMIUMJC,
ISPREMIUMTZDS, ISFUNDTRADE, FIRSTPROVINCE,
FIRSTCITY, LASTPROVINCE,
LASTCITY, DFCFFIRSTDATE, DFCFLASTDATE,
TTJJFIRSTDATE, TTJJLASTDATE,
DFCFNUMALL, DFCFNUM180, TTJJNUMALL, TTJJNUM180,
LASTUPDDT, ISL2,
GUBANUMKT, GUBANUMFT, GUBANUMPL
Using the following key columns for target table
ICE2.CRM_AGG_USERBEHAVIOR_CALC1_BK: EUTIME, EID, USERID, ISPREMIUM,
ISPREMIUMGS, ISPREMIUMJC, ISPREMIUMTZDS, ISFUNDTRADE, FIRS
TPROVINCE, FIRSTCITY, LASTPROVINCE, LASTCITY, DFCFFIRSTDATE,
DFCFLASTDATE, TTJJFIRSTDATE, TTJJLASTDATE, DFCFNUMALL, DFCFNUM180,
TTJJNUMALL, TTJJNUM180, LASTUPDDT, ISL2, GUBANUMKT,
GUBANUMFT, GUBANUMPL.
2016-03-28 16:03:15 WARNING
OGG-01004 Aborted grouped transaction on
'ICE2.CRM_AGG_USERBEHAVIOR_CALC1_BK', Database error 100
(retrieving bind info for query).
2016-03-28 16:03:15 WARNING
OGG-01003 Repositioning to rba 8344518 in seqno
324.
2016-03-28 16:03:17 WARNING
OGG-01154 SQL error 1403 mapping
ICE2.CRM_AGG_USERBEHAVIOR_CALC1_BK to
ICE2.CRM_AGG_USERBEHAVIOR_CALC1_BK.
2016-03-28 16:03:17 WARNING
OGG-01003 Repositioning to rba 8344518 in seqno
324.
Source Context :
SourceModule
: [er.main]
SourceID
:
[/home/ecloud/workspace/Build_FBO_OpenSys_r11.1.1.0.0_078_[34086]/perforce/src/app/er/rep.c]
SourceFunction
: [take_rep_err_action]
SourceLine
: [15780]
ThreadBacktrace
: [8] elements
: [/u01/app/OGG/replicat(CMessageContext::AddThreadContext()+0x26)
[0x5da0b6]]
:
[/u01/app/OGG/replicat(CMessageFactory::CreateMessage(CSourceContext*,
unsigned int, ...)+0x7b2) [0x5d0b52]]
:
[/u01/app/OGG/replicat(_MSG_ERR_MAP_TO_TANDEM_FAILED(CSourceContext*,
DBString<777> const&, DBString<777> const&,
CMessageFactory::MessageDisposition)+
0x9b) [0x57c91b]]
: [/u01/app/OGG/replicat() [0x7f36e3]]
: [/u01/app/OGG/replicat() [0x8c0c21]]
: [/u01/app/OGG/replicat(main+0x1d30) [0x4f5360]]
: [/lib64/libc.so.6(__libc_start_main+0xfd) [0x3b42a1ed5d]]
加载中…