Goldengate从12.1.2.1.0版本开始支持从Oracle ADG抽取数据进行数据同步(参考文档 ID 1299805.1),下面我们就来实际操作一下吧(仅配置DML同步)。
1、环境
主库:192.168.254.131
备库:192.168.254.132
OGG库:192.168.254.133
Oracle Database:11.2.0.4
Oracle Goldengate:12.2.0.1.1
2、主库安装创建数据库并配置
SQL> create tablespace goldengate;
Tablespace created.
SQL> create profile oggprofile limit PASSWORD_LIFE_TIME UNLIMITED;
Profile created.
SQL> create user goldengate identified by goldengate default tablespace goldengate profile oggprofile;
User created.
SQL> grant connect,resource ,unlimited tablespace to goldengate;
Grant succeeded.
SQL> grant execute on utl_file to goldengate;
Grant succeeded.
SQL> grant select any dictionary,select any table to goldengate;
Grant succeeded.
SQL> grant alter any table to goldengate;
Grant succeeded.
SQL> grant flashback any table to goldengate;
Grant succeeded.
3、创建备库并配置
ADG的创建很简单,创建好后修改如下参数是备库支持OGG。
SQL> alter system set enable_goldengate_replication=true scope=both;
System altered.
SQL> show parameter ENABLE_GOLDENGATE_REPLICATION
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
enable_goldengate_replication boolean TRUE
4、备库安装OGG
[
oracle@DG
~]$ mkdir /u01/app/goldengate
[
oracle@DG
~]$ unzip fbo_ggs_Linux_x64_shiphome.zip
[
oracle@DG
~]$ export DISPLAY=192.168.254.1:0.0
[
oracle@DG
~]$ cd fbo_ggs_Linux_x64_shiphome/Disk1/
[
oracle@DG
Disk1]$ ./runInstaller
[
oracle@DG
Disk1]$ cd
[
oracle@DG
~]$ tail -2 /home/oracle/.bash_profile
LD_LIBRARY_PATH=/u01/app/goldengate:$LD_LIBRARY_PATH; export LD_LIBRARY_PATH
PATH=/u01/app/goldengate:$PATH; export PATH
[
oracle@DG
~]$ source .bash_profile
5、备库配置OGG
[
oracle@DG
~]$ cd /u01/app/goldengate/
[
oracle@DG
goldengate]$ ./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 (DG) 1> edit params ./GLOBALS
CHECKPOINTTABLE goldengate.oggchkpt
GGSCI (DG) 2> exit
[
oracle@DG
goldengate]$ ./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 (DG) 1> dblogin userid goldengate,password goldengate
Successfully logged into database.
GGSCI (DG as
goldengate@dg
) 2> STOP MANAGER
Manager process is required by other GGS processes.
Are you sure you want to stop it (y/n)?y
Sending STOP request to MANAGER ...
Request processed.
Manager stopped.
GGSCI (DG as
goldengate@dg
) 3> edit param mgr
PORT 7809
PURGEOLDEXTRACTS ./dirdat/*,usecheckpoints,MINKEEPDAYS 1
AUTORESTART EXTRACT *, RETRIES 3, WAITMINUTES 5, RESETMINUTES 60
GGSCI (DG as
goldengate@dg
) 4> start manager
Manager started.
GGSCI (DG as
goldengate@dg
) 2> add extract adg_ext1, tranlog, begin now,threads 2
EXTRACT added.
GGSCI (DG as
goldengate@dg
) 3> add exttrail ./dirdat/ea,extract ADG_EXT1,megabytes 100
EXTTRAIL added.
GGSCI (DG as
goldengate@dg
) 4> edit params adg_ext1
extract adg_ext1
setenv (NLS_LANG="AMERICAN_AMERICA.AL32UTF8")
setenv (ORACLE_HOME="/u01/app/oracle/product/11.2.0/dbhome_1")
setenv (ORACLE_SID="dg")
USERID goldengate,PASSWORD goldengate
discardfile ./dirrpt/adg_ext1.dsc,purge,megabytes 100
EXTTRAIL ./dirdat/ea
TRANLOGOPTIONS MINEFROMACTIVEDG
TABLE hr.*;
GGSCI (DG as
goldengate@dg
) 5> add extract adg_dp1,exttrailsource ./dirdat/ea,begin now
EXTRACT added.
GGSCI (DG as
goldengate@dg
) 6> add rmttrail ./dirdat/pa,extract adg_dp1,megabytes 100
RMTTRAIL added.
GGSCI (DG as
goldengate@dg
) 7> edit params adg_dp1
extract adg_dp1
setenv (NLS_LANG="AMERICAN_AMERICA.AL32UTF8")
setenv (ORACLE_HOME="/u01/app/oracle/product/11.2.0/dbhome_1")
setenv (ORACLE_SID="dg")
RMTHOST 192.168.254.133,MGRPORT 7809
RMTTRAIL ./dirdat/pa
passthru
TABLE hr.*;
GGSCI (DG as
goldengate@dg
) 8> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT STOPPED ADG_DP1 00:00:00 00:03:47
EXTRACT STOPPED ADG_EXT1 00:00:00 00:27:13
GGSCI (DG as goldengate@dg) 9> start adg_ext1
Sending START request to MANAGER ...
EXTRACT ADG_EXT1 starting
GGSCI (DG as goldengate@dg) 10> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT STOPPED ADG_DP1 00:00:00 72:20:24
EXTRACT RUNNING ADG_EXT1 00:00:00 72:43:50
观察日志,如果没有报错方可进行下一步:
[root@DG ~]# tail -f /u01/app/goldengate/ggserr.log
6、OGG库创建及初始化
使用复制数据库的方式从主库复制数据到OGG库。
RMAN> duplicate target database to gg from active database;
完成后查看OGG库的日志文件确定恢复到的最后SCN
[oracle@OGG ~]$ grep 'RESETLOGS after incomplete recovery' /u01/app/oracle/diag/rdbms/pri/pri/trace/alert_pri.log
RESETLOGS after incomplete recovery UNTIL CHANGE
5991333509320
并授予goldengate用户dba权限:
SQL> grant dba to goldengate;
Grant succeeded.
7、OGG库安装OGG并配置
[oracle@GG ~]$ mkdir /u01/app/goldengate
[oracle@GG ~]$ unzip fbo_ggs_Linux_x64_shiphome.zip
[oracle@GG ~]$ export DISPLAY=192.168.254.1:0.0
[oracle@GG ~]$ cd fbo_ggs_Linux_x64_shiphome/Disk1/
[oracle@GG Disk1]$ ./runInstaller
[oracle@GG Disk1]$ cd
[oracle@GG ~]$ tail -2 /home/oracle/.bash_profile
LD_LIBRARY_PATH=/u01/app/goldengate:$LD_LIBRARY_PATH; export LD_LIBRARY_PATH
PATH=/u01/app/goldengate:$PATH; export PATH
[oracle@GG ~]$ source .bash_profile
[oracle@GG ~]$ cd /u01/app/goldengate/
[oracle@GG goldengate]$ ./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 (GG) 1> edit params ./GLOBALS
CHECKPOINTTABLE goldengate.oggchkpt
GGSCI (GG) 2> exit
[oracle@GG goldengate]$ ./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 (GG) 1> dblogin userid goldengate,password goldengate
Successfully logged into database.
GGSCI (GG as goldengate@gg) 2> add checkpointtable
No checkpoint table specified. Using GLOBALS specification (goldengate.oggchkpt)...
Successfully created checkpoint table goldengate.oggchkpt.
GGSCI (GG as goldengate@gg) 3> STOP MANAGER
Manager is already stopped.
GGSCI (GG as goldengate@gg) 30> edit param mgr
PORT 7809
PURGEOLDEXTRACTS ./dirdat/*,usecheckpoints,MINKEEPHOURS 1
GGSCI (GG as goldengate@gg) 5> start manager
Manager started.
GGSCI (GG as goldengate@gg) 6> add replicat adg_rp1,exttrail ./dirdat/pa
REPLICAT added.
GGSCI (GG as goldengate@gg) 7> edit params adg_rp1
REPLICAT adg_rp1
setenv (NLS_LANG="AMERICAN_AMERICA.AL32UTF8")
setenv (ORACLE_HOME="/u01/app/oracle/product/11.2.0/dbhome_1")
setenv (ORACLE_SID="gg")
USERID goldengate,PASSWORD goldengate
HANDLECOLLISIONS
ASSUMETARGETDEFS
allownoopupdates
DISCARDFILE ./dirrpt/adg_rp1.DSC,append,megabytes 100
map hr.*,target hr.*;
8、备库启动投递进程
GGSCI (DG as goldengate@dg) 3> start adg_dp1
Sending START request to MANAGER ...
EXTRACT ADG_DP1 starting
GGSCI (DG as goldengate@dg) 4> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING ADG_DP1 00:00:00 00:00:09
EXTRACT RUNNING ADG_EXT1 00:00:00 00:00:01
9、OGG库启动复制
GGSCI (GG as goldengate@gg) 14> start ADG_RP1,aftercsn 5991333509320
Sending START request to MANAGER ...
REPLICAT ADG_RP1 starting
GGSCI (GG as goldengate@gg) 15> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
REPLICAT RUNNING ADG_RP1 00:00:00 00:00:04
GGSCI (GG as goldengate@gg) 26> info er *
REPLICAT ADG_RP1 Last Started 2017-01-09 19:35 Status RUNNING
Checkpoint Lag 08:00:04 (updated 00:00:05 ago)
Process ID 20931
Log Read Checkpoint File ./dirdat/pa000000550
2017-01-09 11:48:01.000379 RBA 18249194
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/28536251/viewspace-2136343/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/28536251/viewspace-2136343/