GoldenGate schema級複製 實施過程

一.環境描述

Source Database:
192.168.8.21/192.168.8.22 (RAC)
REDHAT 4.0 x64
ORACLE 10.2.0.4

Target Database
192.168.3.106
AIX 5.3
ORACLE 10.2.0.4

二.環境說明
1. 源資料庫和目標資料庫建立ggs使用者，授予DBA許可權，用於儲存GoldenGate管理資料；在源資料庫建立test1使用者，目標資料庫建立test2使用者，授予DBA許可權，用於schema級的資料同步測試
2. Source資料庫需要開啟附加日誌和強制歸檔，DDL同步需設定source資料庫系統引數recyclebin為off
3. 軟體安裝
   從oracle網站下載GolenGate 軟體：
source端使用軟體包：ggs_redhatAS40_x64_ora10g_64bit_v10.4.0.19_002.tar
target端使用軟體包：ggs_aix52_ppc_ora102_64bit_v10.4.0.19_002.tar

source端安裝於192.168.8.21的/u02/ggs
target端安裝於192.168.3.106的/u02/ggs

安裝方法：
 將相應的軟體包放入/u02/ggs下，使用作業系統的oracle使用者，解包（ggs目錄屬於oracle使用者）：
然後執行：./ggsci
GGSCI (idcdb01) 1> CREATE SUBDIRS

Creating subdirectories under current directory /u02/ggs

Parameter files                /u02/ggs/dirprm: created
Report files                   /u02/ggs/dirrpt: created
Checkpoint files               /u02/ggs/dirchk: created
Process status files           /u02/ggs/dirpcs: created
SQL script. files               /u02/ggs/dirsql: created
Database definitions files     /u02/ggs/dirdef: created
Extract data files             /u02/ggs/dirdat: created
Temporary files                /u02/ggs/dirtmp: created
Veridata files                 /u02/ggs/dirver: created
Veridata Lock files            /u02/ggs/dirver/lock: created
Veridata Out-Of-Sync files     /u02/ggs/dirver/oos: created
Veridata Out-Of-Sync XML files /u02/ggs/dirver/oosxml: created
Veridata Parameter files       /u02/ggs/dirver/params: created
Veridata Report files          /u02/ggs/dirver/report: created
Veridata Status files          /u02/ggs/dirver/status: created
Veridata Trace files           /u02/ggs/dirver/trace: created
Stdout files                   /u02/ggs/dirout: created

以上目錄成功建立後，goldengate軟體即安裝完成。

新增資料庫附加日誌

SQLPLUS / AS SYSDBA
SQL>select supplemental_log_data_min from v$database;

如果返回結果為YES，則說明目前資料庫已經開啟附加日誌功能。如果為NO,則需要執行下面的命令修改：

SQL>alter database add supplemental log data;

配置資料庫強制歸檔

SQLPLUS / AS SYSDBA
SQL>ALTER DATABASE FORCE LOGGING;

建立goldengate使用者，並授予dba許可權（在source和target資料庫端均要建立）
SQL> CREATE USER ggs IDENTIFIED BY ggs;
SQL> GRANT DBA TO ggs;

三.GoldenGate 配置
以下操作需要在source和target資料庫端均操作
1.配置mgr程式
GGSCI>edit param mgr
輸入：port 7809

2.配置GLOBALS
GGSCI>edit param ./GLOBALS
輸入：
GGSCHEMA ggs
CHECKPOINTTABLE ggs.ggs_checkpoint

3.啟動mgr程式

GGSCI>start mgr

4.我們可以通過下面的命令看到狀態：

GGSCI>info all

四.DML同步測試
 測試資料使用GoldenGate自帶測試指令碼生成，指令碼位於安裝目錄（/u02/ggs）下：
 注意：
經多次測試，GoldenGate管理所用schema與資料同步的schema應該分離，使用不用的schema，並且source和target應該使用同名的tablespace。
 Source：
 [oracle@idcdb01 ggs] $ cd /u02/ggs
 [oracle@idcdb01 ggs] $ sqlplus / as sysdba
 SQL> create user ggs identified by ggs;
 
User created.

SQL> grant dba to ggs;

Grant succeeded.

SQL> grant dba to ggs;

Grant succeeded.

SQL> create user test1 identified by test1;

User created.

SQL> grant dba to test1;

Grant succeeded.

SQL> conn ggs/ggs

SQL> @ demo_ora_create.sql
(建立tcustmer、tcustord兩表)

SQL> exit

[oracle@idcdb01 ggs] $ ./ggsci
Oracle GoldenGate Command Interpreter for Oracle
Version 10.4.0.19 Build 002
Linux, x64, 64bit (optimized), Oracle 10 on Sep 22 2009 14:18:08

Copyright (C) 1995, 2009, Oracle and/or its affiliates.  All rights reserved.

GGSCI (idcdb01) 1> dblogin userid ggs,password ggs
Successfully logged into database.

GGSCI (idcdb01) 2> add trandata ggs.*

GGSCI (idcdb01) 3> start mgr

Manager started.

GGSCI (idcdb01) 4> info all

Program     Status      Group       Lag           Time Since Chkpt

MANAGER     RUNNING

GGSCI (idcdb01) 5> edit param eiexaa

extract eiexaa
userid ggs,password ggs
exttrail ./dirdat/dd
tranlogoptions altarchivelogdest instance payroll1 /u02/archive,altarchivelogdest instance payroll2 /nfs
tranlogoptions asmuser sys@ASM1,asmpassword orapayrolla
table ggs.*;

由於source伺服器是rac環境，兩節點archivelog分別在各自本地目錄，所以首先將節點2（192.168.8.22）的archivelog目錄配置為nfs服務目錄，指定給節點1使用，在節點1上mount節點2的archivelog目錄到掛載點/nfs；
由於source伺服器使用ASM存放資料檔案，在extract中必須這定ASM例項的登入使用者sys和密碼，此處ASM1是tnsnames.ora中的串名。

GGSCI (idcdb01)6> add extract eiexaa,tranlog,begin now,threads 2
EXTRACT added.

threads 2表示，extract是由RAC的兩個節點中抽取資料

GGSCI (idcdb01) 7> add exttrail ./dirdat/dd,extract eiexaa,megabytes 5
EXTTRAIL added.

megabytes 5表示，extract生成的檔案每個大小為5M

下面配置pump用於傳輸extract生成的日誌檔案到target端：

GGSCI (idcdb01) 10> edit param epmpaa
extract epmpaa
passthru
rmthost 192.168.3.106,mgrport 7809
rmttrail ./dirdat/dd
table ggs.*;

GGSCI (idcdb01) 12> add extract epmpaa,exttrailsource ./dirdat/dd
EXTRACT added.

GGSCI (idcdb01) 13> add rmttrail ./dirdat/dd,extract epmpaa,megabytes 5
RMTTRAIL added.

GGSCI (idcdb01) 14> start eiexaa

GGSCI (idcdb01) 15> start epmpaa

GGSCI (idcdb01) 16> info all

Program     Status      Group       Lag           Time Since Chkpt

MANAGER     RUNNING                                          
EXTRACT     RUNNING     EIEXAA      00:00:00      00:03:59   
EXTRACT     RUNNING     EPMPAA      00:00:00      00:04:05   

Target：
 $ cd /u02/ggs
 $ sqlplus / as sysdba
 SQL> create user ggs identified by ggs;
 
User created.

SQL> grant dba to ggs;

Grant succeeded.

SQL> create user test2 identified by test2;

User created.

SQL> grant dba to test2;

Grant succeeded.

SQL> conn ggs/ggs

SQL> @ chkpt_ora_create.sql
(建立ggs_checkpoint表)

SQL> @ demo_ora_create.sql
(建立tcustmer、tcustord兩表)

SQL> exit

$ ./ggsci

GGSCI (hrostemp) 2> dblogin userid ggs,password ggs
Successfully logged into database.

GGSCI (hrostemp) 8> edit param rorabb

replicat rorabb
userid ogg,password ogg
handlecollisions
ASSUMETARGETDEFS
DISCARDFILE ./dirrpt/rorabb.dsc,PURGE
MAP ggs.*, TARGET ggs.*;

GGSCI (hrostemp) 10> add replicat rorabb,checkpointtable ggs.ggs_checkpoint,exttrail ./dirdat/dd
REPLICAT added.

GGSCI (hrostemp) 11> start mgr

GGSCI (hrostemp) 12> start rorabb

GGSCI (hrostemp) 13> info all

Program     Status      Group       Lag           Time Since Chkpt

MANAGER     RUNNING                                          
REPLICAT    RUNNING     RORABB      00:09:10      00:02:56   

以上配置完成後，進行DML驗證測試。
Source：
 [oracle@idcdb01 ggs] $ cd /u02/ggs
 [oracle@idcdb01 ggs] $ sqlplus ggs/ggs

SQL> @demo_ora_insert.sql
(在tcustmer、tcustord兩表中插入資料)

SQL> select * from tcustmer;
SQL> select * from tcustord;

Target：
 $ cd /u02/ggs
 $ sqlplus ggs/ggs

SQL> select * from tcustmer;
SQL> select * from tcustord;
驗證這兩個表中的資料是否已從source端同步到target端。

五.DDL同步測試
 Source：
 [oracle@idcdb01 ggs] $ cd /u02/ggs
 [oracle@idcdb01 ggs] $ sqlplus / as sysdba
 
 SQL> show parameter recyclebin

NAME                     TYPE        VALUE
-------------------   ----------- --------------
recyclebin                string      ON
 
 SQL> alter system set recyclebi=off;
 
Recyclebin原值為on，DDL同步必須改為off

SQL> @marker_setup
Enter GoldenGate schema name:ggs

SQL> @ddl_setup
Enter GoldenGate schema name:ggs
Enter mode of installation:INITIALSETUP

SQL> @role_setup
Enter GoldenGate schema name:ggs

SQL> @ddl_enable

SQL> exit

[oracle@idcdb01 ggs] $ ./ggsci
Oracle GoldenGate Command Interpreter for Oracle
Version 10.4.0.19 Build 002
Linux, x64, 64bit (optimized), Oracle 10 on Sep 22 2009 14:18:08

Copyright (C) 1995, 2009, Oracle and/or its affiliates.  All rights reserved.

GGSCI (idcdb01) 1> dblogin userid ggs,password ggs
Successfully logged into database.

GGSCI (idcdb01) 3> start mgr

Manager started.
 
GGSCI (idcdb01) 5> edit param eiexaa

extract eiexaa
userid ggs,password ggs
exttrail ./dirdat/dd
DDL
tranlogoptions altarchivelogdest instance payroll1 /u02/archive,altarchivelogdest instance payroll2 /nfs
tranlogoptions asmuser sys@ASM1,asmpassword orapayrolla
table test1.*;

在extract引數中增加關鍵字：DDL

GGSCI (idcdb01)6> delete eiexaa

GGSCI (idcdb01)7> add extract eiexaa,tranlog,begin now,threads 2
EXTRACT added.

重新配置extract

GGSCI (idcdb01) 8> add exttrail ./dirdat/dd,extract eiexaa,megabytes 5
EXTTRAIL added.
 
重新配置pump用於傳輸extract生成的日誌檔案到target端：

GGSCI (idcdb01) 10> edit param epmpaa
extract epmpaa
passthru
rmthost 192.168.3.106,mgrport 7809
rmttrail ./dirdat/dd
table test1.*;

GGSCI (idcdb01) 11> delete epmpaa

GGSCI (idcdb01) 12> add extract epmpaa,exttrailsource ./dirdat/dd
EXTRACT added.

GGSCI (idcdb01) 13> add rmttrail ./dirdat/dd,extract epmpaa,megabytes 5
RMTTRAIL added.

GGSCI (idcdb01) 14> start eiexaa

GGSCI (idcdb01) 15> start epmpaa

GGSCI (idcdb01) 16> info all

Program     Status      Group       Lag           Time Since Chkpt

MANAGER     RUNNING                                          
EXTRACT     RUNNING     EIEXAA      00:00:00      00:03:59   
EXTRACT     RUNNING     EPMPAA      00:00:00      00:04:05   

Target：
 $ cd /u02/ggs

$ ./ggsci

GGSCI (hrostemp) 2> dblogin userid ggs,password ggs
Successfully logged into database.

GGSCI (hrostemp) 8> edit param rorabb

replicat rorabb
userid ogg,password ogg
handlecollisions
ASSUMETARGETDEFS
DISCARDFILE ./dirrpt/rorabb.dsc,PURGE
MAP test1.*, TARGET test2.*;

GGSCI (hrostemp) 9> delete rorabb

GGSCI (hrostemp) 10> add replicat rorabb,checkpointtable ggs.ggs_checkpoint,exttrail ./dirdat/dd
REPLICAT added.

GGSCI (hrostemp) 11> start mgr

GGSCI (hrostemp) 12> start rorabb

GGSCI (hrostemp) 13> info all

Program     Status      Group       Lag           Time Since Chkpt

MANAGER     RUNNING                                          
REPLICAT    RUNNING     RORABB      00:09:10      00:02:56   

以上配置完成後，進行DDL驗證測試。
Source：
 [oracle@idcdb01 ggs] $ cd /u02/ggs
 [oracle@idcdb01 ggs] $ sqlplus ggs/ggs

SQL> @ demo_ora_lob_create.sql
(建立了帶BLOB和CLOB欄位的表TSRSLOB，使用過程TESTING_LOBS，可向此表中插入測試資料)

SQL> exec testing_lobs;

SQL> select count(*) from tsrslob;

SQL> create table t(id int);

SQL> alter table t add name varchar2(20);

SQL> alter table t drop column id;

SQL> drop table t;

Target：
 $ cd /u02/ggs
 $ sqlplus ggs/ggs

SQL> desc tsrslob;

SQL> select count(*) from tsrslob;
驗證表tsrslob是否已在target中建立，並檢視錶中的資料是否同步到target端。

SQL> desc t;

六.Goldengate監控
在goldengate軟體安裝目錄下，有關ggserr.log檔案
我們可以通過檢視此日誌瞭解goldengate的執行過程：
Tail –f ggserr.log
同時GoldenGate還提供了view report命令：
GGSCI>view report eiexaa