实验环境
主机名 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 services LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 22-MAY-2017 16:07:08 Copyright (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 Agent SID_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 sysasm SQL*Plus: Release 11.2.0.4.0 Production on Mon May 22 16:16:11 2017 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 SQL> select instance_name from v$instance; INSTANCE_NAME ---------------- +ASM1 [oracle@rac01 ~]$ sqlplus sys/oracle@rac as sysdba SQL*Plus: Release 11.2.0.4.0 Production on Mon May 22 16:16:49 2017 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 Partitioning, Real Application Clusters, Automatic Storage Management, OLAP, Data Mining and Real Application Testing options SQL> 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 sysasm SQL*Plus: Release 11.2.0.4.0 Production on Fri May 19 15:43:24 2017 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 SQL> 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/bin export 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 022 export 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]$ ggsci Oracle 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 mgr PORT 7808 autostart er * autorestart er * GGSCI (rac01) 3> start mgr GGSCI (rac01) 4> info mgr Manager 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 OGG SQL> @sequence.sql SQL> 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.sql SQL> @ddl_setup .sql SQL> @role_setup.sql SQL> grant ggs_ggsuser_role to ogg; Grant succeeded. SQL> @ddl_enable.sql Trigger altered. SQL> @ddl_pin ogg PL/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]$ ggsci GGSCI (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 testext EXTRACT 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]$ ggsci GGSCI (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 testpump EXTRACT testpump rmthost 192.168.56.40, mgrport 7808 rmttrail ./dirdat/rt passthru table test.*; GGSCI (rac01 as ogg@orcl1) 6> info all Program Status Group Lag at Chkpt Time Since Chkpt MANAGER 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 all Program Status Group Lag at Chkpt Time Since Chkpt MANAGER STOPPED REPLICAT STOPPED TESTRPT 00:00:00 00:01:32
21. 启动各个进程:
启动顺序 source和target端的mgr -- > source端的extract --> target端的replicat --> source端的extract pump source: GGSCI (rac01 as ogg@orcl1) 11> info all Program Status Group Lag at Chkpt Time Since Chkpt MANAGER 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 all Program Status Group Lag at Chkpt Time Since Chkpt MANAGER 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 testrpt Sending 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.*.*, ALLOW 3. 一些命令 开启mgr： start mgr 开启其他进程： start * / start 进程名 关闭相反 查看参数：view params 进程名 查看日志：view report 进程名 查看日志：OGG_HOME/ggserr.log
