实验环境
主机名 IP地址
rac01 192.168.56.10
rac02 192.168.56.20
rac-scan 192.168.56.30
目标库：
ora-ogg 192.168.56.40
实验是从rac环境到单实例的ogg同步
1. 查看rac节点是否有ASM的监听注册（双节点）
[oracle@rac01 ~]$ lsnrctl servicesLSNRCTL for Linux: Version 11.2.0.4.0 - Production on 22-MAY-2017 16:07:08Copyright (c) 1991, 2013, Oracle. All rights reserved.Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
Services Summary...
Service "+ASM" has 1 instance(s).
Instance "+ASM1", status READY, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:1 refused:0 state:ready
LOCAL SERVER
Service "orcl" has 1 instance(s).
Instance "orcl1", status READY, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0 state:ready
LOCAL SERVER
Service "orclXDB" has 1 instance(s).
Instance "orcl1", status READY, has 1 handler(s) for this service...
Handler(s):
"D000" established:0 refused:0 current:0 max:1022 state:ready
DISPATCHER <machine: rac01, pid: 4164>
(ADDRESS=(PROTOCOL=tcp)(HOST=rac01)(PORT=44556))
The command completed successfully
如果没有，需要用grid用户在$ORACLE_HOME/network/admin/listener.ora文件中添加静态注册，然后reload listener（双节点）：
[grid@rac01 admin]$ vi 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 AgentSID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = +ASM)
(ORACLE_HOME=/u01/app/11.2.0/grid)
(SID_NAME = +ASM1)
)
)
2. 用oracle用户编辑$ORACLE_HOME/network/admin/tnsnames.ora文件，使其用别名可连接ASM实例和数据库
[oracle@rac01 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 = rac-scan)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
)
)ASM =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.10)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = +ASM)
(SID_NAME = +ASM1)
) )
3. 查看endpoints_listener.ora文件配置是否合理
[grid@rac01 admin]$ cat endpoints_listener.ora
LISTENER_RAC01=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=rac01-vip)(PORT=1521))(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.56.10)(PORT=1521)(IP=FIRST)))) # line added by Agent
4.测试连接：
[oracle@rac01 ~]$ sqlplus sys/oracle@ASM as sysasmSQL*Plus: Release 11.2.0.4.0 Production on Mon May 22 16:16:11 2017Copyright (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 optionsSQL> select instance_name from v$instance;INSTANCE_NAME
----------------
+ASM1[oracle@rac01 ~]$ sqlplus sys/oracle@rac as sysdbaSQL*Plus: Release 11.2.0.4.0 Production on Mon May 22 16:16:49 2017Copyright (c) 1982, 2013, Oracle. All rights reserved.Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing optionsSQL> select instance_name from v$instance;INSTANCE_NAME
----------------
orcl1
5. 安装acfs（ASM Cluster File System）
[grid@rac01 ~]$ acfsload -s
ACFS-9228: usage: acfsload {start|stop} [-s]
[grid@rac01 ~]$ acfsload start
ACFS-9130: Root access required
[grid@rac01 ~]$ exit
logout
[root@rac01 ogg]# cd /u01/app/11.2.0/grid/bin/
[root@rac01 bin]# ./acfsload start
ACFS-9391: Checking for existing ADVM/ACFS installation.
ACFS-9392: Validating ADVM/ACFS installation files for operating system.
ACFS-9393: Verifying ASM Administrator setup.
ACFS-9308: Loading installed ADVM/ACFS drivers.
ACFS-9327: Verifying ADVM/ACFS devices.
ACFS-9156: Detecting control device '/dev/asm/.asm_ctl_spec'.
ACFS-9156: Detecting control device '/dev/ofsctl'.
ACFS-9322: completed
6. 这里可以使用挂在上来的磁盘或者现有的ASM磁盘做ogg的软件目录
我采用在现有ASM磁盘上创建一个ASM卷并挂载
[grid@rac01 ~]$ sqlplus / as sysasmSQL*Plus: Release 11.2.0.4.0 Production on Fri May 19 15:43:24 2017Copyright (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 optionsSQL> alter diskgroup DATA add volume acfsload size 1G;Diskgroup altered.SQL> quit[grid@rac01 ~]$ ll /dev/asm*
brw-rw---- 1 grid oinstall 8, 32 May 19 15:44 /dev/asm-disk1
brw-rw---- 1 grid oinstall 8, 16 May 19 15:44 /dev/asm-disk2
brw-rw---- 1 grid oinstall 8, 48 May 19 10:33 /dev/asm-disk3/dev/asm:
total 0
brwxrwx--- 1 root dba 252, 107521 May 19 15:44 acfsload-210
7. 创建acfs挂载目录，在两个节点都要创建相同目录
[root@rac01 deinstall]# mkdir -p /u01/app/acfsmounts/acfsload-210
[root@rac02 deinstall]# mkdir -p /u01/app/acfsmounts/acfsload-210
8. 用mkfs创建文件系统
[root@rac01 deinstall]# mkfs -t acfs -n acfs01 /dev/asm/acfsload-210
shell-init: error retrieving current directory: getcwd: cannot access parent directories: No such file or directory
mkfs.acfs: version = 11.2.0.4.0
mkfs.acfs: on-disk version = 39.0
mkfs.acfs: volume = /dev/asm/acfsload-210
mkfs.acfs: volume size = 1073741824
mkfs.acfs: Format complete.
9. 用acfsuit命令注册文件系统
[root@rac01 bin]# acfsutil registry -a -f /dev/asm/acfsload-210 /u01/app/acfsmounts/acfsload-210
acfsutil registry: mount point /u01/app/acfsmounts/acfsload-210 successfully added to Oracle Registry
10. 使用mount.acfs -o all命令挂载文件系统
[root@rac01 bin]# mount.acfs -o all
[root@rac01 bin]# df -h
Filesystem Size Used Avail Use% Mounted on
/dev/mapper/vg_rac01-LogVol01
21G 13G 6.8G 65% /
tmpfs 1.9G 134M 1.8G 7% /dev/shm
/dev/sda1 190M 42M 138M 24% /boot
/dev/asm/acfsload-210
1.0G 79M 946M 8% /u01/app/acfsmounts/acfsload-210
11. 改变文件系统的归属，改为Oracle：oinstall
[root@rac01 bin]# chown oracle:oinstall /u01/app/acfsmounts/acfsload-210
[root@rac01 bin]# ll -d /u01/app/acfsmounts/acfsload-210
drwxrwx--- 4 oracle oinstall 4096 May 19 15:53 /u01/app/acfsmounts/acfsload-210
12. 安装ogg软件
创建文件目录：mkdir /tmp/orasoft
将安装文件上传到该目录下面并解压
[oracle@rac01 orasoft]$ ls
fbo_ggs_Linux_x64_shiphome fbo_ggs_Linux_x64_shiphome.zip OGG-12.2.0.1.1-ReleaseNotes.pdf OGG-12.2.0.1-README.txt
使用图形化安装，指定ogg_home，oracle用户的oracle_home，安装11g的ogg
[oracle@rac01 ~]$ /tmp/orasoft/fbo_ggs_Linux_x64_shiphome/Disk1/runInstalle
13. 配置oracle环境变量，需要加入LD_LIBRARY_PATH及OGG_HOME等
[oracle@rac01 orasoft]$ cat ~/.bash_profile
# .bash_profile# Get the aliases and functions
if [ -f ~/.bashrc ]; then
. ~/.bashrc
fi# User specific environment and startup programs
PATH=$PATH:$HOME/binexport PATH
export ORACLE_SID=orcl1
export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=$ORACLE_BASE/product/11.2.0/db_1
export ORACLE_UNQNAME=orcl
#export PATH=$ORACLE_HOME/bin:$PATH
umask 022export OGG_HOME=/u01/app/acfsmounts/acfsload-210/ogg
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib:$ORACLE_HOME/rdbms/lib
export PATH=$PATH:$ORACLE_HOME/bin:$OGG_HOME
14. 配置mgr进程
[oracle@rac01 ~]$ cd /u01/app/acfsmounts/acfsload-210/ogg/
[oracle@rac01 ogg]$ ggsciOracle GoldenGate Command Interpreter for Oracle
Version 12.2.0.1.1 OGGCORE_12.2.0.1.0_PLATFORMS_151211.1401_FBO
Linux, x64, 64bit (optimized), Oracle 11g on Dec 12 2015 00:54:38
Operating system character set identified as UTF-8.Copyright (C) 1995, 2015, Oracle and/or its affiliates. All rights reserved.GGSCI (rac01) 1> edit params mgr
GGSCI (rac01) 2> view params mgrPORT 7808
autostart er *
autorestart er *GGSCI (rac01) 3> start mgr
GGSCI (rac01) 4> info mgrManager is running (IP port rac01.7808, Process ID 18924).
15. 目标库安装ogg，方法同样，目标安装在/ogg目录下
16. 在rac库上创建用户，配置，授权等以及开启ddl复制相关脚本
SQL> create user ogg identified by ogg;User created.SQL> grant connect,resource,unlimited tablespace to ogg;User created.SQL> create tablespace ogg datafile '+data' size 1g autoextend off;Tablespace created.SQL> alter user ogg default tablespace ogg;User altered.SQL> select username,default_tablespace from dba_users where username='OGG';USERNAME DEFAULT_TABLESPACE
------------------------------ ------------------------------
OGG OGGSQL> @sequence.sqlSQL> alter database add supplemental log data (primary key) columns;Database altered.SQL> alter database add supplemental log data (foreign key) columns;Database altered.SQL> alter database add supplemental log data (unique) columns;Database altered.SQL> alter system archive log current;System altered.SQL> grant execute on utl_file to ogg;Grant succeeded.SQL> @marker_setup.sqlSQL> @ddl_setup .sqlSQL> @role_setup.sqlSQL> grant ggs_ggsuser_role to ogg;Grant succeeded.SQL> @ddl_enable.sqlTrigger altered.SQL> @ddl_pin oggPL/SQL procedure successfully completed.PL/SQL procedure successfully completed.PL/SQL procedure successfully completed.SQL> alter database add supplemental log data;Database altered.SQL> alter system set enable_goldengate_replication=true scope=both;System altered.
17. 创建同步的用户test，同步test用户下面的数据
SQL> create user test identified by test; User created.SQL> grant connect,resource to test;Grant succeeded.
18. 源端配置extract group抽取数据
[oracle@rac01 ogg]$ ggsciGGSCI (rac01) 1>
GGSCI (rac01) 2> dblogin userid ogg password ogg
Successfully logged into database.GGSCI (rac01 as ogg@orcl1) 3> add extract testext,tranlog,begin now,threads 2
EXTRACT added.GGSCI (rac01 as ogg@orcl1) 4> add exttrail ./dirdat/et,extract testext
EXTTRAIL added.GGSCI (rac01 as ogg@orcl1) 5> edit params testextEXTRACT testext
SETENV (ORACLE_HOME = "/u01/app/oracle/product/11.2.0/db_1")
USERID ogg@rac, PASSWORD ogg
TRANLOGOPTIONS ASMUSER sys@ASM,ASMPASSWORD oracle
EXTTRAIL ./dirdat/et
DYNAMICRESOLUTION
DDL INCLUDE ALL
TABLE test.*;
19. 源端配置data pump传送数据
[oracle@rac01 ogg]$ ggsciGGSCI (rac01) 2> dblogin userid ogg password ogg
Successfully logged into database.GGSCI (rac01 as ogg@orcl1) 3> add extract testpump,exttrailsource ./dirdat/et,begin now
EXTRACT added.GGSCI (rac01 as ogg@orcl1) 4> add rmttrail ./dirdat/rt,extract testpump
RMTTRAIL added.GGSCI (rac01 as ogg@orcl1) 5> edit params testpumpEXTRACT testpump
rmthost 192.168.56.40, mgrport 7808
rmttrail ./dirdat/rt
passthru
table test.*;GGSCI (rac01 as ogg@orcl1) 6> info allProgram Status Group Lag at Chkpt Time Since ChkptMANAGER RUNNING
EXTRACT STOPPED EXTDATA 00:00:00 00:53:31
EXTRACT STOPPED TESTPUMP 00:00:00 00:04:01
20. 目标库配置：
SQL> create user ogg identified by ogg;User created.SQL> grant connect,resource,dba to ogg;Grant succeeded.SQL> create user test identified by test;User created.SQL> grant connect ,resource to test;Grant succeeded.SQL> alter system set enable_goldengate_replication=true scope=both;System altered.SQL> exit[oracle@ora-ogg ogg]$ ./ggsci
GGSCI (ora-ogg) 2> dblogin userid ogg password ogg
Successfully logged into database.GGSCI (ora-ogg as ogg@orcl) 3> add replicat testrpt,exttrail ./dirdat/rt,nodbcheckpoint
REPLICAT added.GGSCI (ora-ogg as ogg@orcl) 4> edit params testrpt
REPLICAT testrpt
setenv (ORACLE_SID=orcl)
SETENV (ORACLE_HOME = "/u01/app/oracle/product/11.2.0/db_1")
USERID ogg@orcl,PASSWORD ogg
ASSUMETARGETDEFS
HANDLECOLLISIONS
REPERROR (DEFAULT, DISCARD)
DDLERROR DEFAULT DISCARD
DDLOPTIONS REPORT
DISCARDFILE ./repsz.dsc,append,megabytes 100
MAP test.*, TARGET test.*; GGSCI (ora-ogg as ogg@orcl) 6> info allProgram Status Group Lag at Chkpt Time Since ChkptMANAGER STOPPED
REPLICAT STOPPED TESTRPT 00:00:00 00:01:32
21. 启动各个进程:
启动顺序
source和target端的mgr -- > source端的extract --> target端的replicat --> source端的extract pumpsource:GGSCI (rac01 as ogg@orcl1) 11> info allProgram Status Group Lag at Chkpt Time Since ChkptMANAGER RUNNING
EXTRACT RUNNING TESTEXT 00:00:00 00:00:08
EXTRACT RUNNING TESTPUMP 00:00:00 00:00:02 target:GGSCI (ora-ogg as ogg@orcl) 19> info allProgram Status Group Lag at Chkpt Time Since ChkptMANAGER RUNNING
REPLICAT RUNNING TESTRPT 00:00:00 00:00:01
22. 开始同步
总结：遇到的坑：
1. ggsci无法进入，报错如下：
[oracle@rac01 ~]$ ggsci
Cannot load ICU resource bundle 'ggMessage', error code 2 - No such file or directory
Aborted (core dumped)原因1：ggsci必须在ogg的安装目录下输入才能进入；
原因2：未配置oracle用户的环境变量。2. 无法启动REPLICAT，
GGSCI (ora-ogg as ogg@orcl) 7> start testrptSending START request to MANAGER ...ERROR: Access denied.2017-05-22 15:29:10 WARNING OGG-01742 Command sent to MGR MGR returned with an ERROR response.
告警中日志错误
2017-05-22 15:29:10 WARNING OGG-00936 Oracle GoldenGate Manager for Oracle, mgr.prm: Access denied (request from 192.168.56.40, rule #0).解决办法，在目标库mgr配置文件中添加访问主机IP：
GGSCI (ora-ogg as ogg@orcl) 8> edit params mgr
PORT 7808
ACCESSRULE, PROG *, IPADDR 192.168.*.*, ALLOW3. 一些命令
开启mgr： start mgr
开启其他进程： start * / start 进程名
关闭相反查看参数：view params 进程名
查看日志：view report 进程名
查看日志：OGG_HOME/ggserr.log
