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.

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.

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.

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.

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

Oracle Dataguard + Goldengate資料同步

相關文章