ogg同构安装

ゝ一纸荒年。 2022-09-23 08:07 234阅读 0赞

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][ogg_BDCFG]) 62> dblogin userid ogg,password ogg

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

查看某用户下，添加

GGSCI (bd-prd-oracle-17 as  [ogg@BDCFG][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\]\]

2016-03-28 16:03:17  ERROR   OGG-01296  Error mapping from ICE2.CRM\_AGG\_USERBEHAVIOR\_CALC1\_BK to ICE2.CRM\_AGG\_USERBEHAVIOR\_CALC1\_BK.

B:查看进程信息

info ex1 showch

[ogg_BDCFG]: mailto:ogg@BDCFG