ogg112101 for mysql_Oracle 11g RAC到单实例OGG同步实施文档-OGG initial load

1.环境介绍

类别

源端

目标端

数据库类型

单实例

单实例

数据库版本

11.2.0.4

11.2.0.4

ORACLE_SID

orcl1, orcl2

cndba

DB_NAME

orcl

cndba

主机IP地址

192.168.1.56,58

192.168.1.85

OS版本

RedHat 7.4

RedHat 6.7

OGG版本

11.2.1.0.1 64位

11.2.1.0.1 64位

主机名

rac1,rac2

cndba

2.监听服务配置

2.1.监听配置

加入对ASM的动态注册，加入红色字体部分，具体配置根据环境决定。

注意：RAC所有节点都要配置

[grid@www.cndba.cn admin]$ cat listener.ora

LISTENER=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER))))# line added by Agent

LISTENER_SCAN1=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_SCAN1))))# line added by Agent

ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER_SCAN1=ON# line added by Agent

ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER=ON# line added by Agent

SID_LIST_LISTENER =

(SID_LIST =

(SID_DESC =

(GLOBAL_DBNAME = +ASM)

(ORACLE_HOME=/u01/gridsoft/11.2.0)

(SID_NAME = +ASM1)

)

)

--reload监控，使配置生效

[grid@www.cndba.cn admin]$ lsnrctl reload

--查看监听状态

[grid@www.cndba.cn ~]$ lsnrctl services

LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 24-MAY-2018 13:36:26

Copyright (c) 1991, 2013, Oracle. All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER)))

Services Summary...

Service "+ASM" has 2 instance(s). --红色部分表示静态注册

Instance "+ASM1", status UNKNOWN, has 1 handler(s) for this service...

Handler(s):

"DEDICATED" established:8 refused:0

LOCAL SERVER

Instance "+ASM1", status READY, has 1 handler(s) for this service...

Handler(s):

"DEDICATED" established:3 refused:0 state:ready

LOCAL SERVER

Service "cndba" has 1 instance(s).

Instance "cndba1", status READY, has 1 handler(s) for this service...

Handler(s):

"DEDICATED" established:30 refused:0 state:ready

LOCAL SERVER

Service "cndbaXDB" has 1 instance(s).

Instance "cndba1", status READY, has 1 handler(s) for this service...

Handler(s):

"D000" established:0 refused:0 current:0 max:1022 state:ready

DISPATCHER

(ADDRESS=(PROTOCOL=tcp)(HOST=rac1)(PORT=50141))

The command completed successfully

--测试连接是否正常

[oracle@www.cndba.cn admin]$ sqlplus sys/oracle@192.168.1.56:1521/+ASM as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Sat Jun 9 02:17:34 2018

Copyright (c) 1982, 2013, Oracle. All rights reserved.

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

With the Real Application Clusters and Automatic Storage Management options

2.2.配置tnsname.ora

--节点1

注意：要用oracle用户去配置

[oracle@www.cndba.cn admin]$ cd /u01/app/oracle/product/11.2.0/db_1/network/admin

[oracle@www.cndba.cn admin]$ cat tnsnames.ora

# tnsnames.ora Network Configuration File: /u01/app/oracle/product/11.2.0/db_1/network/admin/tnsnames.ora

# Generated by Oracle configuration tools.

RAC =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.56)(PORT = 1521))

(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.58)(PORT = 1521))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = orcl)

)

)

+ASM =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.56)(PORT = 1521))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = +ASM)

)

)

--节点2

注意：要用oracle用户去配置

[oracle@rac2 admin]$ cd /u01/app/oracle/product/11.2.0/db_1/network/admin

[oracle@rac2 admin]$ cat tnsnames.ora

# tnsnames.ora Network Configuration File: /u01/app/oracle/product/11.2.0/db_1/network/admin/tnsnames.ora

# Generated by Oracle configuration tools.

RAC =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.56)(PORT = 1521))

(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.58)(PORT = 1521))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = orcl)

)

)

+ASM =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.58)(PORT = 1521))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = +ASM)

)

)

3.安装前的准备工作

3.1.源端创建GoldenGate用户表空间

create tablespace ogg_data datafile '+DATA/orcl/datafile/ogg01.dbf' size 200m autoextend off;

3.2.源端创建GoldenGate用户

create user ogg identified by ogg default tablespace ogg_data;

grant connect,resource,dba,create table,create sequence to ogg;

3.3.目标端创建GoldenGate用户表空间

create tablespace ogg_data datafile '/u01/app/oracle/oradata/cndba/ogg01.dbf' size 200m autoextend off;

3.4.目标端创建GoldenGate用户表空间

create user ogg identified by ogg default tablespace ogg_data;

grant connect,resource,dba,create table,create sequence to ogg;

3.5.源端创建测试用户及测试数据

SQL> create user test identified by test;

User created.

SQL> grant connect,resource to test;

Grant succeeded.

SQL> conn test/test

Connected.

SQL> create table test (id number(10) primary key ,name varchar(8));

Table created.

SQL> insert into test values(1,'zhangsan');

1 row created.

SQL> insert into test values(2,'lisi');

1 row created.

SQL> commit;

3.6.目标端创建测试用户及测试数据

SQL> create user test identified by test;

User created.

SQL> grant connect,resource to test;

Grant succeeded.

SQL> conn test/test

Connected.

SQL> create table test (id number(10) primary key ,name varchar(8));

Table created.

目标端不需要插入数据

3.7.源端开启归档模式、强制日志、附加日志

3.7.1.查看是否开启归档模式、强制日志、附加日志

SQL> select LOG_MODE, SUPPLEMENTAL_LOG_DATA_MIN,FORCE_LOGGING from v$database;

LOG_MODE SUPPLEME FOR

------------ -------- ---

NOARCHIVELOG NO NO

3.7.2.开启归档

修改归档路径参数

SQL> alter system set log_archive_dest_1='location=+DATA/orcl/archivelog' scope=spfile sid='*';

System altered.

关闭数据库，全部节点都要关闭

[oracle@rac2 ~]$ srvctl stop database -d orcl

节点2 数据库启动到mount状态

[oracle@rac2 ~]$ srvctl start instance -d orcl -i orcl2 -o mount

修改归档并启动数据库

SQL> alter database archivelog;

Database altered.

SQL> alter database open;

Database altered.

查看归档信息

SQL> archive log list;

Database log mode Archive Mode

Automatic archival Enabled

Archive destination +DATA/orcl/archivelog

Oldest online log sequence 3

Next log sequence to archive 4

Current log sequence 4

启动节点1

alter database open;

3.7.3.开启强制日志

SQL> alter database force logging;

Database altered.

3.7.4.开启附加日志

SQL> alter database add supplemental log data;

Database altered.

3.7.5.查看是否开启归档模式、强制日志、附加日志

SQL> select LOG_MODE, SUPPLEMENTAL_LOG_DATA_MIN,FORCE_LOGGING from v$database;

LOG_MODE SUPPLEME FOR

------------ -------- ---

ARCHIVELOG YES YES

3.7.6.查看回收站是否关闭

SQL> show parameter recycle

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

buffer_pool_recycle string

db_recycle_cache_size big integer 0

recyclebin string on

SQL> alter system set recyclebin=off scope=spfile;

System altered.

--重启数据库查看

SQL> show parameter recycle

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

buffer_pool_recycle string

db_recycle_cache_size big integer 0

recyclebin string OFF

4.GoldenGate安装

4.1.源端安装OGG

4.1.1.创建软件安装目录并赋权

为Source端分配磁盘创建OGG，该磁盘为共享类型

划分磁盘

[root@rac2 ~]# fdisk /dev/sde

格式化磁盘

[root@rac2 ~]# mkfs -t ext3 /dev/sde1

创建挂在点

[root@rac2 ~]# mkdir /goldengate

[root@rac2 ~]# chown -R oracle:oinstall /goldengate

加载挂载点

[root@rac2 ~]# mount /dev/sde1 /goldengate

[root@rac2 ~]# df -h

Filesystem Size Used Avail Use% Mounted on

/dev/mapper/rhel-root 45G 12G 33G 27% /

devtmpfs 2.3G 0 2.3G 0% /dev

tmpfs 2.3G 115M 2.2G 5% /dev/shm

tmpfs 2.3G 8.9M 2.3G 1% /run

tmpfs 2.3G 0 2.3G 0% /sys/fs/cgroup

/dev/sda1 1014M 178M 837M 18% /boot

tmpfs 468M 12K 468M 1% /run/user/42

tmpfs 468M 0 468M 0% /run/user/0

/dev/sde1 9.8G 23M 9.2G 1% /goldengate

开机自动加载

[root@rac2 ~]# vi /etc/fstab

--添加如下内容：

/dev/sde1 /goldengate ext3 defaults 1 2

4.1.2.配置oracle用户环境变量

两个节点都要配置

[oracle@www.cndba.cn ~]$ vi .bash_profile

设置Library 路径

假设OGG的安装目录是/goldengate，那么在/home/oracle/.bash_profile文件里添加如下内容：

export OGG_HOME=/goldengate

export PATH=$ORACLE_HOME/bin:$PATH:$OGG_HOME

export LD_LIBRARY_PATH=$ORACLE_HOME/lib:$OGG_HOME:/lib:/usr/lib

--source 使修改生效：

[oracle@www.cndba.cn ~]$ source .bash_profile

4.1.3.解压ogg文件

只需要在一个节点做就可以

[root@cndba software]# cd /software/

[root@cndba software]# unzip ogg112101_fbo_ggs_Linux_x64_ora11g_64bit.zip

[root@cndba software]# tar -xvf fbo_ggs_Linux_x64_ora11g_64bit.tar -C /goldengate

[root@cndba ogg]# chown -R oracle:oinstall /goldengate

注意：/goldengate 是$OGG_HOME

4.1.4.运行ogg并创建目录

[oracle@cndba ~]$ cd $OGG_HOME

[oracle@cndba ogg]$ ./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 (cndba) 1> create subdirs

Creating subdirectories under current directory /u01/app/oracle/ogg

Parameter files /u01/app/oracle/ogg/dirprm: already exists

Report files /u01/app/oracle/ogg/dirrpt: created

Checkpoint files /u01/app/oracle/ogg/dirchk: created

Process status files /u01/app/oracle/ogg/dirpcs: created

SQL script files /u01/app/oracle/ogg/dirsql: created

Database definitions files /u01/app/oracle/ogg/dirdef: created

Extract data files /u01/app/oracle/ogg/dirdat: created

Temporary files /u01/app/oracle/ogg/dirtmp: created

Stdout files /u01/app/oracle/ogg/dirout: created

4.2.目标端安装OGG

4.2.1.创建软件安装目录并赋权

[root@cndba software]# mkdir -p /u01/app/oracle/ogg

[root@cndba software]# chown -R oracle:oinstall /u01/app/oracle/ogg

4.2.2.配置oracle用户环境变量

[oracle@cndba ~]$ vi .bash_profile

设置Library 路径

假设OGG的安装目录是/u01/app/oracle/ogg，那么在/home/oracle/.bash_profile文件里添加如下内容：

export OGG_HOME=$ORACLE_BASE/ogg

export PATH=$ORACLE_HOME/bin:$PATH:$OGG_HOME

export LD_LIBRARY_PATH=$ORACLE_HOME/lib:$OGG_HOME:/lib:/usr/lib

--source 使修改生效：

[oracle@cndba ~]$ source .bash_profile

4.2.3.解压ogg文件

[root@cndba software]# cd /software/

[root@cndba software]# unzip ogg112101_fbo_ggs_Linux_x64_ora11g_64bit.zip

[root@cndba software]# tar -xvf fbo_ggs_Linux_x64_ora11g_64bit.tar -C /u01/app/oracle/ogg

[root@cndba ogg]# chown -R oracle:oinstall /u01/app/oracle/ogg

注意：/u01/app/oracle/ogg 是$OGG_HOME

4.2.4.运行ogg并创建目录

[oracle@cndba ~]$ cd $OGG_HOME

[oracle@cndba ogg]$ ./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 (cndba) 1> create subdirs

Creating subdirectories under current directory /u01/app/oracle/ogg

Parameter files /u01/app/oracle/ogg/dirprm: already exists

Report files /u01/app/oracle/ogg/dirrpt: created

Checkpoint files /u01/app/oracle/ogg/dirchk: created

Process status files /u01/app/oracle/ogg/dirpcs: created

SQL script files /u01/app/oracle/ogg/dirsql: created

Database definitions files /u01/app/oracle/ogg/dirdef: created

Extract data files /u01/app/oracle/ogg/dirdat: created

Temporary files /u01/app/oracle/ogg/dirtmp: created

Stdout files /u01/app/oracle/ogg/dirout: created

5.GoldenGate配置

5.1.OGG源端配置

5.1.1.配置mgr进程

GGSCI (cndba) 3> edit params mgr

GGSCI (cndba) 4> view params mgr

port 7809

GGSCI (cndba) 5> start mgr

Manager started.

GGSCI (cndba) 6> info all

Program Status Group Lag at Chkpt Time Since Chkpt

MANAGER RUNNING

GGSCI (cndba) 7> sh netstat -ntpl |grep 7809 --查看7809端口是否启用

(Not all processes could be identified, non-owned process info

will not be shown, you would have to be root to see it all.)

tcp 0 0 :::7809 :::* LISTEN 14176/./mgr

GGSCI (rac1) 7> sh ps -ef|grep mgr

postfix 1424 1395 0 00:40 ? 00:00:00 qmgr -l -t unix -u

oracle 7535 7362 0 02:59 ? 00:00:00 ./mgr PARAMFILE /goldengate/dirprm/mgr.prm REPORTFILE /goldengate/dirrpt/MGR.rpt PROCESSID MGR PORT 7809

oracle 7577 7362 0 02:59 pts/1 00:00:00 sh -c ps -ef|grep mgr

oracle 7579 7577 0 02:59 pts/1 00:00:00 grep mgr

5.1.2.添加表级transdata

GGSCI (cndba) 10> dblogin userid ogg,password ogg

Successfully logged into database.

查看是否开启

GGSCI (cndba) 11> info trandata test.test

GGSCI (cndba) 11> add trandata test.*

Logging of supplemental redo data enabled for table TEST.TEST.

注意：为了方便这里直接添加整个用户下表级transdata，如果只是同步部分表的，请批量

执行add trandata test.tablename

5.1.3.配置extract抽取进程

GGSCI (cndba) 13> dblogin userid ogg,password ogg

Successfully logged into database.

GGSCI (cndba) 14> add extract ext1, tranlog, begin now, threads 2

EXTRACT added.

GGSCI (cndba) 15> add exttrail ./dirdat/ex, extract ext1

EXTTRAIL added.

GGSCI (cndba) 16> edit params ext1

GGSCI (cndba) 17> view params ext1

EXTRACT ext1

SETENV (NLS_LANG="AMERICAN_AMERICA.ZHS16GBK") --此处数据库字符集设为一致

--SETENV (ORACLE_SID = "cndba")

--SETENV (ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)

USERID ogg, PASSWORD ogg

TRANLOGOPTIONS ASMUSER sys@+ASM,ASMPASSWORD oracle

THREADOPTIONS MAXCOMMITPROPAGATIONDELAY 20000

EXTTRAIL ./dirdat/ex

DYNAMICRESOLUTION

--DDL INCLUDE ALL

TABLE test.*;

5.1.4.配置pump传输进程

GGSCI (cndba) 18> add extract pump1,exttrailsource ./dirdat/ex,begin now

EXTRACT added.

GGSCI (cndba) 19> add rmttrail ./dirdat/ex,extract pump1

RMTTRAIL added.

GGSCI (cndba) 20> edit params pump1

GGSCI (cndba) 21> view params pump1

EXTRACT pump1

RMTHOST 192.168.1.85, MGRPORT 7809

RMTTRAIL ./dirdat/ex

PASSTHRU

DYNAMICRESOLUTION

TABLE test.*;

GGSCI (cndba) 22> info all

Program Status Group Lag at Chkpt Time Since Chkpt

MANAGER RUNNING

EXTRACT STOPPED EXT1 00:00:00 00:18:00

EXTRACT STOPPED PUMP1 00:00:00 00:04:07

5.2.OGG目标端配置

5.2.1.添加GLOBALS参数文件，创新检查点表

GGSCI (cndba) 7> edit params ./GLOBALS

GGSCI (cndba) 8> view params ./GLOBALS

--添加以下内容：

GGSCHEMA ogg

checkpointtable ogg.checkpoint

GGSCI (cndba) 9> dblogin userid ogg,password ogg

Successfully logged into database.

GGSCI (cndba) 10> add checkpointtable ogg.checkpoint

Successfully created checkpoint table ogg.checkpoint.

5.2.2.配置mgr进程

GGSCI (cndba) 3> edit params mgr

GGSCI (cndba) 4> view params mgr

port 7809

GGSCI (cndba) 5> start mgr

Manager started.

GGSCI (cndba) 6> info all

Program Status Group Lag at Chkpt Time Since Chkpt

MANAGER RUNNING

GGSCI (cndba) 7> sh netstat -ntpl |grep 7809 --查看7809端口是否启用

(Not all processes could be identified, non-owned process info

will not be shown, you would have to be root to see it all.)

tcp 0 0 :::7809 :::* LISTEN 14176/./mgr

GGSCI (cndba) 8> sh ps -ef|grep mgr --查看mgr进程是否存在

root 14 2 0 13:24 ? 00:00:00 [async/mgr]

postfix 1867 1860 0 13:26 ? 00:00:00 qmgr -l -t fifo -u

oracle 14176 14114 0 15:43 ? 00:00:00 ./mgr PARAMFILE /u01/app/oracle/ogg/dirprm/mgr.p

oracle 14185 14114 0 15:44 pts/0 00:00:00 sh -c ps -ef|grep mgr

oracle 14187 14185 0 15:44 pts/0 00:00:00 grep mgr

5.2.3.配置replicat复制进程

GGSCI (cndba) 11> add replicat rep1, exttrail ./dirdat/ex, checkpointtable ogg.checkpoint

REPLICAT added.

GGSCI (cndba) 12> edit params rep1

GGSCI (cndba) 13> view params rep1

REPLICAT rep1

setenv (ORACLE_SID=cndba)

SETENV (ORACLE_HOME = "/u01/app/oracle/product/11.2.0/db_1")

USERID ogg,PASSWORD ogg

ASSUMETARGETDEFS

HANDLECOLLISIONS

REPERROR (DEFAULT, DISCARD)

DDLERROR DEFAULT DISCARD

DDLOPTIONS REPORT

DISCARDFILE ./dirrpt/repr1.dsc,append,megabytes 100

MAP test.*, TARGET test.*;

6.初始化数据-initial load

6.1.启动生产端和容灾端的管理进程

--源端

[oracle@cndba ogg]$ cd /goldengate

[oracle@cndba ogg]$ ./ggsci

GGSCI (cndba) 1> start mgr

Manager started.

GGSCI (cndba) 1> info all

Program Status Group Lag at Chkpt Time Since Chkpt

MANAGER RUNNING

EXTRACT STOPPED EXT1 00:00:00 00:40:35

EXTRACT STOPPED PUMP1 00:00:00 00:26:42

--目标端

[oracle@host1 ~]$ cd /u01/app/oracle/ogg

[oracle@host1 ogg]$ ./ggsci

GGSCI (host1) 1> start mgr

Manager started.

GGSCI (cndba) 14> info all

Program Status Group Lag at Chkpt Time Since Chkpt

MANAGER RUNNING

REPLICAT STOPPED REP1 00:00:00 00:11:22

6.2.源端初始化抽取的配置

GGSCI (cndba) 6> edit params extinit

添加以下内容：

EXTRACT extinit

userid ogg , password ogg

RMTHOST 192.168.1.85 , MGRPORT 7809

RMTTASK replicat,GROUP repinit --目标端replicat

TABLE test.* ;

---------添加初始化Extract 进程---------------

ADD EXTRACT extinit, SOURCEISTABLE

6.3.目标端初始化复制的配置

GGSCI (host1) 11> edit params repinit

添加以下内容：

REPLICAT repinit

USERID ogg, PASSWORD ogg

ASSUMETARGETDEFS

MAP test.* , target test.* ;

---------添加初始化Replicat 进程---------------

ADD REPLICAT repinit, SPECIALRUN

6.4.启动源端的EXTRACT进程

GGSCI (cndba) 2> start ext1

Sending START request to MANAGER ...

EXTRACT EXT1 starting

GGSCI (cndba) 4> start pump1

Sending START request to MANAGER ...

EXTRACT PUMP1 starting

GGSCI (cndba) 5> info all

Program Status Group Lag at Chkpt Time Since Chkpt

MANAGER RUNNING

EXTRACT RUNNING EXT1 00:00:00 00:00:03

EXTRACT RUNNING PUMP1 00:00:00 00:28:57

6.5.启动生产端的抽取进程

启动生产端的批量抽取进程start extinit，不需要启动repinit 进程，

MGR 会自动启动它，等同步结束，他会自动关闭：

启动生产端的抽取进程start extinit：

GGSCI (cndba) 31> start extinit

Sending START request to MANAGER ...

EXTRACT EXTINIT starting

6.6.在源端view report extinit 直到load 结束，然后做下一步

源端查看日志

[root@cndba ogg]# tail -f ggserr.log

2018-06-06 23:06:12 INFO OGG-00963 Oracle GoldenGate Manager for Oracle, mgr.prm: Command received from GGSCI on host cndba (START EXTRACT EXTINIT ).

2018-06-06 23:06:12 INFO OGG-00975 Oracle GoldenGate Manager for Oracle, mgr.prm: EXTRACT EXTINIT starting.

2018-06-06 23:06:12 INFO OGG-01017 Oracle GoldenGate Capture for Oracle, extinit.prm: Wildcard resolution set to IMMEDIATE because SOURCEISTABLE is used.

2018-06-06 23:06:12 INFO OGG-00992 Oracle GoldenGate Capture for Oracle, extinit.prm: EXTRACT EXTINIT starting.

2018-06-06 23:06:12 INFO OGG-03035 Oracle GoldenGate Capture for Oracle, extinit.prm: Operating system character set identified as UTF-8. Locale: en_US, LC_ALL:.

2018-06-06 23:06:13 INFO OGG-01815 Oracle GoldenGate Capture for Oracle, extinit.prm: Virtual Memory Facilities for: COM

anon alloc: mmap(MAP_ANON) anon free: munmap

file alloc: mmap(MAP_SHARED) file free: munmap

target directories:

/u01/app/oracle/ogg/dirtmp.

2018-06-06 23:06:13 INFO OGG-00993 Oracle GoldenGate Capture for Oracle, extinit.prm: EXTRACT EXTINIT started.

2018-06-06 23:06:41 INFO OGG-00991 Oracle GoldenGate Capture for Oracle, extinit.prm: EXTRACT EXTINIT stopped normally.

目标端查看数据已经初始化

SQL> select * from test;

ID NAME

---------- --------

1 zhangsan

2 lisi

6.7.在容灾端启动投递进程start rep1

GGSCI (cndba) 7> start rep1

Sending START request to MANAGER ...

REPLICAT REP1 starting

GGSCI (cndba) 8> info all

Program Status Group Lag at Chkpt Time Since Chkpt

MANAGER RUNNING

REPLICAT RUNNING REP1 00:00:00 00:00:01

GGSCI (cndba-dest) 53> info rep1

REPLICAT REP1 Last Started 2018-06-09 22:41 Status RUNNING

Checkpoint Lag 00:00:00 (updated 00:00:01 ago)

Log Read Checkpoint File ./dirdat/ex000005

2018-06-09 22:40:15.030168 RBA 1322

6.8.关掉冲突检查选项SEND REPLICAT rep1, NOHANDLECOLLISIONS

GGSCI (cndba) 10> SEND REPLICAT rep1, NOHANDLECOLLISIONS

Sending NOHANDLECOLLISIONS request to REPLICAT REP1 ...

REP1 No tables found matching * to set NOHANDLECOLLISIONS

.

6.9.去掉rep1 文件中的HANDLECOLLISIONS 参数

GGSCI (host1) 58> view params rep1

--HANDLECOLLISIONS

7.检查同步是否正常

7.1.DML测试

--检查目标端数据是否正常

GGSCI (cndba) 4> info all

Program Status Group Lag at Chkpt Time Since Chkpt

MANAGER RUNNING

REPLICAT RUNNING REP1 00:00:00 00:00:06

SQL> select * from test;

ID NAME

---------- --------

1 zhangsan

2 lisi

--源端表中添加数据

SQL> insert into test values(3,'wanger');

1 row created.

SQL> commit;

Commit complete.

SQL> select * from test;

ID NAME

---------- --------

1 zhangsan

2 lisi

3 wanger

--目标端查看

SQL> select * from test;

ID NAME

---------- --------

1 zhangsan

2 lisi

3 wanger

可以看到可以同步过来的。

8.开启DDL

8.1.添加参数

GGSCI (cndba) 8> edit params ./GLOBALS

GGSCI (cndba) 9> view params ./GLOBALS

GGSCHEMA ogg

8.2.在源端执行与DDL同步相关的SQL脚本

切记@marker_setup.sql 一定要在cd $OGG_HOME目录下执行否则会卡主。执行脚本时要输入ogg管理用户，本实例是ogg。

cd /goldengate

sqlplus / as sysdba

grant execute on utl_file to ogg;

@marker_setup.sql

@ddl_setup.sql

@role_setup.sql

grant GGS_GGSUSER_ROLE to ogg;

@ddl_enable.sql

@ddl_pin ogg

8.3.源端extract 配置

GGSCI (cndba) 12> edit params ext1

GGSCI (cndba) 16> view params ext1

EXTRACT ext1

SETENV (NLS_LANG="AMERICAN_AMERICA.ZHS16GBK")

--SETENV (ORACLE_SID = "cndba")

--SETENV (ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)

USERID ogg, PASSWORD ogg

TRANLOGOPTIONS ASMUSER sys@+ASM,ASMPASSWORD oracle

THREADOPTIONS MAXCOMMITPROPAGATIONDELAY 20000

EXTTRAIL ./dirdat/ex

DYNAMICRESOLUTION

DDL INCLUDE ALL

TABLE test.*;

重启extract进程

GGSCI (cndba) 13> stop ext1

Sending STOP request to EXTRACT EXT1 ...

Request processed.

GGSCI (cndba) 14> start ext1

Sending START request to MANAGER ...

EXTRACT EXT1 starting

8.4.目标端replicat 配置

GGSCI (cndba) 8> edit params rep1

GGSCI (cndba) 9> view params rep1

REPLICAT rep1

setenv (ORACLE_SID=cndba)

SETENV (ORACLE_HOME = "/u01/app/oracle/product/11.2.0/db_1")

USERID ogg,PASSWORD ogg

ASSUMETARGETDEFS

HANDLECOLLISIONS

REPERROR (DEFAULT, DISCARD)

DDLERROR DEFAULT DISCARD

DDLOPTIONS REPORT

DISCARDFILE ./dirrpt/repr1.dsc,append,megabytes 100

MAP test.*, TARGET test.*;

ddl include all

ddlerror default ignore retryop maxretries 3 retrydelay 5

重启replicat进程

GGSCI (cndba) 10> stop rep1

Sending STOP request to REPLICAT REP1 ...

Request processed.

GGSCI (cndba) 11> start rep1

Sending START request to MANAGER ...

REPLICAT REP1 starting

8.5.DDL测试

--源端：

SQL> create table test2(id number(10) primary key ,name varchar(8));

Table created.

SQL> insert into test2 values(1,'zhangsan');

1 row created.

SQL> commit;

Commit complete.

--目标端：

SQL> desc test2

Name Null? Type

----------------------------------------- -------- ----------------------------

ID NOT NULL NUMBER(10)

NAME VARCHAR2(8)

SQL> select * from test2

ID NAME

---------- --------

1 zhangsan

https://www.cndba.cn/leo1990/article/2850

https://www.cndba.cn/leo1990/article/2850

https://www.cndba.cn/leo1990/article/2850

https://www.cndba.cn/leo1990/article/2850

https://www.cndba.cn/leo1990/article/2850

https://www.cndba.cn/leo1990/article/2850

https://www.cndba.cn/leo1990/article/2850

https://www.cndba.cn/leo1990/article/2850

https://www.cndba.cn/leo1990/article/2850

https://www.cndba.cn/leo1990/article/2850

版权声明：本文为博主原创文章，未经博主允许不得转载。