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
版权声明:本文为博主原创文章,未经博主允许不得转载。