之前一直沒有玩過goldengate,看到現在這個軟體應用的越來越廣泛於是也想嚐鮮一把，這次主要是測試goldengate的單向複製功能，最後透過dml的複製結果來驗證！
操作步驟如下:

1.環境描述

實驗環境源端目標端

資料庫 10.2.0.4.0 10.2.0.4.0

作業系統 centos5.8 centos5.8

主機名 sourcedb targetdb

2.安裝goldengate(兩端操作)

2.1解壓軟體包

[oracle@sourcedb ogg]$ tar -xvf fbo_ggs_Linux_x64_ora10g_64bit.tar -C /oradata/ogg

2.2 建立goldengate子目錄

[oracle@sourcedb ogg]$ cd /oradata/ogg/
[oracle@sourcedb ogg]$ ./ggsci

GGSCI (sourcedb) 1> create subdirs

2.3設定使用者環境變數

[oracle@sourcedb ~]$ cat .bash_profile
export ORACLE_BASE=/oracle/product
export ORACLE_HOME=$ORACLE_BASE/10.2/db_1
export ORACLE_SID=wilson
export PATH=$ORACLE_HOME/bin:$PATH
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/usr/lib:/usr/local/lib
export NLS_LANG=american_america.ZHS16GBK

3.配置oracle資料庫

3.1配置歸檔模式(源端操作)

[oracle@sourcedb ~]$ sqlplus / as sysdba

SQL>shutdown immediate

SQL>startup mount

SQL>alter database archivelog

3.2配置補充日誌(源端操作)

SQL> alter database add supplemental log data

3.3建立goldengate管理使用者(兩端操作)

SQL>create user ggs_admin identified by ggs_admin;

SQL>grant dba to ggs_admin;

4.新增表級的trandata(源端操作)

[oracle@sourcedb ogg]$ ./ggsci

GGSCI (sourcedb) 1> dblogin userid ggs_admin,password ggs_admin

GGSCI (sourcedb) 2> add trandata scott.emp

5.配置hosts檔案(兩端操作)

[oracle@sourcedb ~]$ cat /etc/hosts
127.0.0.1 localhost.localdomain localhost
::1 localhost6.localdomain6 localhost6
10.1.101.19 sourcedb
10.1.101.13 targetdb

6.配置goldengate的manager程式(兩端操作)

[oracle@sourcedb ogg]$ ./ggsci

GGSCI (sourcedb) 1> edit params mgr

port 7809

purgeoldextracts ./dirdat/sa*, usecheckpoints, minkeephours 2

GGSCI (sourcedb) 2> start mgr

7.配置goldengate的extract程式(源端操作)
7.1配置extract程式

[oracle@sourcedb ogg]$ ./ggsci

GGSCI (sourcedb) 1> edit params edb01
extract edb01
setenv (ORACLE_SID=wilson)
userid ggs_admin,password ggs_admin
exttrail ./dirdat/sa
table scott.emp;

GGSCI (sourcedb) 2>add extract edb01,tranlog,begin now

GGSCI (sourcedb) 3>add exttrail ./dirdat/sa,extact edb01

GGSCI (sourcedb) 4>start extract edb01

7.2配置goldengate的pump程式
GGSCI (sourcedb) 5> edit params epmp01

extract epmp01
passthru
rmthost targetdb,mgrport 7809
rmttrail ./dirdat/ta

table scott.emp;

GGSCI (sourcedb) 6>add extract epmp01,exttrailsource ./dirdat/sa,begin now

GGSCI (sourcedb) 7>add rmttrail ./dirdat/ta,extact epmp01

GGSCI (sourcedb) 8>start extract epmp01

GGSCI (sourcedb) 9>info all

Program Status Group Lag at Chkpt Time Since Chkpt

MANAGER     RUNNING
EXTRACT     RUNNING     EDB01       00:00:00      00:00:08
EXTRACT     RUNNING     EPMP01      00:00:00      00:00:08

8.配置goldengate的replicat程式(目標端操作)

8.1新增checkpoint表

[oracle@targetdb ogg]$ ./ggsci

GGSCI (targetdb) 1> edit params ./GLOBALS
checkpointtable ggs_admin.tl

GGSCI (targetdb) 2> dblogin userid ggs_admin,password ggs_admin

GGSCI (targetdb) 3> add chweckpointtable ggs_admin.tl

8.2配置replicat程式

GGSCI (sourcedb) 4> edit params rdb01

replicat rdb01
setenv (ORACLE_SID=wilson)
userid ggs_admin,password ggs_admin
discardfile ./dirrpt/rdb01.dsc,purge
map scott.emp,target tl.emp;
assumetargetdefs

GGSCI (sourcedb) 5>add replicat rdb01,exttrail ./dirdat/ta

GGSCI (sourcedb) 6>start replicat rdb01

GGSCI (sourcedb) 7>info all

Program Status Group Lag at Chkpt Time Since Chkpt

MANAGER RUNNING
REPLICAT RUNNING RDB01 00:00:00 00:00:08

9.初始化資料(兩端操作)

這一步我透過expdp/impdp將源端的scott.emp匯入到目標端的tl.emp，這裡就不演示了

10.驗證dml複製(兩端操作)

10.1dml操作前(源端操作)

[oracle@sourcedb ogg]$ sqlplus scott/tiger

SQL> select ename,sal from emp where ename='SMITH';

ENAME SAL
---------- ----------
SMITH 800

10.2dml操作前(目標端操作)
[oracle@targetdb ogg]$ sqlplus tl/tl

SQL> select ename,sal from emp where ename='SMITH';

ENAME SAL
---------- ----------
SMITH 800

10.3dml操作後(源端操作)

SQL> show user
USER is "SCOTT"

SQL> update emp set sal=8800 where ename='SMITH';

1 row updated.

SQL> commit;

Commit complete.

SQL> select ename,sal from emp where ename='SMITH';

ENAME SAL
---------- ----------
SMITH 8800
10.4dml操作後(目標端操作)

SQL> show user
USER is "TL"

SQL> select ename,sal from emp where ename='SMITH';

ENAME SAL
---------- ----------
SMITH 8800

OK,透過dml複製的結果發現更新的結果已經同步到目標端，實驗成功！實驗過程中出現過一個錯誤就是當在源端對emp表進行dml操作並提交後，發現目標端的資料並沒有同步過來，此時的replicat程式已為abend狀態，透過view replicat rdb01發現報告中提示"無法發現SCOTT.EMP的定義",解決方法為在replicat引數檔案中新增assumetargetdefs選項即可。

實戰goldengate之ora-To-ora單向複製

相關文章