ogg同构安装-蒲公英云

Oracle Golden Gate配置使用手册

环境：Oracle 11.2.0.4

  OGG 11.1.1.0
  Oracle  ->  Oracle 同版本复制
  主库：172.16.57.26
  备库：172.16.57.27

一、准备工作

补充

先检查是否有大字段

SQL> set linesize 400 pagesize 400

SQL> select owner,table_name,column_name,data_type from dba_tab_columns where owner in (‘KETTLE’,’BIPT’,’EMBD’) and data_type in (‘BLOB’,’LONG’,’ADT’);

OWNER TABLE_NAME COLUMN_NAME

-————————————————————————————————————————————- ————————————————————————————————————————————— —————————————————————————————————————————————

DATA_TYPE

-——————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————-

KETTLE QRTZ_JOB_DETAILS JOB_DATA

BLOB

KETTLE QRTZ_TRIGGERS JOB_DATA

BLOB

KETTLE QRTZ_BLOB_TRIGGERS BLOB_DATA

BLOB

KETTLE QRTZ_CALENDARS CALENDAR

BLOB

如果有的话，应该先排除

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;

加步骤，检查补充日志是否已经添加、

先登录数据库

GGSCI (bd-prd-oracle-17 as ogg@BDCFG) 62> dblogin userid ogg,password ogg

GGSCI (bd-prd-oracle-17 as ogg@BDCFG) 64> info trandata kettle.*

查看某用户下，添加

GGSCI (bd-prd-oracle-17 as ogg@BDCFG) 65> add trandata kettle.*

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将主库的表结构同步到备库

注意这里不需要授权，本来就是超级用户，还有DATA_PUMP_DIR 是系统默认路径

主库：

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/ft

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/ft,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

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

并关闭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

备库开启触发器（数据导入完成以后打开）

alter trigger ICE1.SHIELDLIST_TRIGGER ENABLE;

alter trigger ICE1.TRG_APP_FACT_ADVCALLBACK_SYST ENABLE;

alter trigger ICE1.TRG_APP_STG_FIRSTVISIT_SYST ENABLE;

alter trigger ICE1.TRG_APP_FACT_ADVERTISERS_SYST ENABLE;

alter trigger ICE2.EID_ID ENABLE;

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\]\]
                      : \[/u01/app/OGG/replicat(\_\_gxx\_personality\_v0+0x1da) \[0x4d8e8a\]\]