http://space.itpub.net/25618347/viewspace-719359

今天測試了下 goldengate，主站是oracle 10.2.0.5.0，目標庫是 11.2.0.1.0，詳細配置如下：

由於主從版本不一致(OS都是 x86-64)，下載的包也不一樣

1.解壓並安裝gg：

主資料庫：fbo_ggs_Linux_x64_ora10g_64bit.zip

從資料庫：fbo_ggs_Linux_x64_112100_ora11g_64bit.zip

[oracle@trptdb ~]$ unzip fbo_ggs_Linux_x64_112100_ora11g_64bit.zip| tar -xvof

tar命令最後解壓出來大概100多個檔案，我這裡安裝目錄:/home/oracle/ggs

由於我使用的是oracle使用者進行安裝的，所以直接在oracle家目錄下修改.bash_profile檔案，把gg的安裝目錄新增到庫檔案目錄，如下：

LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib:/home/oracle/ggs;

安裝很簡單，如下：

[oracle@testdb ~]$ pwd
/home/oracle
[oracle@testdb ~]$ cd ggs
[oracle@testdb ggs]$ ./ggsci

Oracle GoldenGate Command Interpreter for Oracle
Version 11.1.1.1.2 OGGCORE_11.1.1.1.2_PLATFORMS_111004.2100
Linux, x64, 64bit (optimized), Oracle on Oct 4 2011 23:50:20

GGSCI (testdb) 1> create subdirs

目標庫也按照上述步驟安裝GoldenGate並建立工作目錄。

2.配置主資料庫：

主庫必須置於歸檔模式，force logging，並且啟用supplemental logging。檢視這幾個選項是否啟動，最簡單的方式是查詢v$database檢視，例如：

SQL> select log_mode,supplemental_log_data_min,force_logging from v$database;

LOG_MODE SUP FOR

------------ --- ---

ARCHIVELOG YES YES

由於主資料庫之前配置了stream，所以這些設定都已經啟用，啟用上述幾個選項的操作如下，以sysdba身份登入到sqlplus命令列，執行下列命令：

--啟動到mount狀態：

startup mount;

--置於歸檔模式：

alter database archivelog;

--強制日誌記錄：

alter database force logging;

--啟用最少附加日誌

alter database add supplemental log ;

--啟動資料庫並查詢狀態：

SQL> alter database open;

Database altered.

SQL> select log_mode,supplemental_log_data_min,force_logging from v$database; LOG_MODE SUP FOR

------------ --- ---

ARCHIVELOG YES YES

建立goldengate使用者，並賦相應的許可權:

SQL> create user ogg identified by ogg;

User created.

grant execute on utl_file to ogg ;

grant to ogg ;

grant connect to ogg ;

grant resource to ogg ;
禁用recycle bin

對於: 需要重啟才能生效

ALTER SYSTEM SET recyclebin = OFF scope=spfile;

對於10g:

Alter system set recyclebin=off;

編輯全域性引數檔案

GGSCI (testdb) 1> edit params ./GLOBAL

GGSCHEMA ogg
CHECKPOINTTABLE ogg.checkpoint

為了支援ddl同步，需要執行以下指令碼：

marker_setup.sql--該指令碼安裝marker系統，這部分為啟用DLL支援所必備，執行該指令碼時會提示輸入GoldenGate管理帳戶schema名.

ddl_setup-- 執行該指令碼要確認關閉掉所有ORACLE會話，未被關閉的會話會以列表形式顯示，執行過程中會要求希望物件屬主，並選擇安裝模式。如果是初次安裝就選擇 “INITIALSETUP”，該模式假設當前沒有任何GoldenGate DDL物件存在，如果存在則會刪除並重建。如果是重新安裝，則應該選擇“NORMAL”.

role_setup--該操作會重建DDL同步所需的許可權，授予GoldenGate中的DDL物件以DML許可權.

ddl_enable--啟用DDL觸發器，以捕獲DDL操作.

要提高DDL觸發器的效能，可以透過ddl_pin指令碼，該指令碼會將觸發器使用的包載入到記憶體，以此提高效率。該指令碼執行時需要引用dbms_shared_pool系統包，因此在使用ddl_pin指令碼前需要確保dbms_shared_pool可用。

@?/rdbms/admin/dbmspool.sql

ddl_pin--執行ddl_pin指令碼需要指定GoldenGate管理員schema名稱.

然後，用於同步DDL操作的物件就建立完成了。

配置主資料庫gg相關：

--配置MANAGER引數：

GGSCI (testdb) 1>edit params mgr

PORT 7809
PURGEOLDEXTRACTS ./dirdat/k1*,USECHECKPOINTS,MINKEEPFILES 10
AUTORESTART ER *, RETRIES 3, WAITMINUTES 5
PURGEDDLHISTORY MINKEEPDAYS 3, MAXKEEPDAYS 5, FREQUENCYMINUTES 30
PURGEMARKERHISTORY MINKEEPDAYS 3, MAXKEEPDAYS 5, FREQUENCYMINUTES 30

--配置抽取程式ext1引數：

GGSCI (testdb) 1> dblogin userid ogg,password ogg

Successfully logged into database.

GGSCI (testdb) 2> add extract ext1,tranlog, begin now

EXTRACT added.

GGSCI (testdb) 4>add exttrail /home/oracle/ggs/dirdat/k1, extract ext1

EXTTRAIL added.

GGSCI (testdb) 5> edit params ext1

EXTRACT EXT1
setenv ( NLS_LANG = AMERICAN_AMERICA.ZHS16GBK )
USERID ogg,PASSWORD ogg
exttrail /home/oracle/ggs/dirdat/k1
DYNAMICRESOLUTION
DDL INCLUDE MAPPED
DDLOPTIONS ADDTRANDATA,REPORT
FETCHOPTIONS, USESNAPSHOT, NOUSELATESTVERSION, MISSINGROW REPORT
STATOPTIONS REPORTFETCH
WARNLONGTRANS 1H, CHECKINTERVAL 5M
mynet_app.*;

--建立datapump

GGSCI (testdb) 4>> add extract dpump,exttrailsource /home/oracle/ggs/dirdat/k1

EXTRACT added.

GGGSCI (testdb) 4> add rmttrail /home/oracle/ggs/dirdat/k1, extract dpump

RMTTRAIL added.

GGSCI (testdb) 4>> view params dpump

extract dpump
setenv ( NLS_LANG = AMERICAN_AMERICA.ZHS16GBK )
userid ogg, password ogg
rmthost 10.1.8.142, mgrport 7809
rmttrail /home/oracle/ggs/dirdat/k1
DYNAMICRESOLUTION
table mynet_app.*;

GGSCI (testdb) 5> info all

Program Status Group Lag Time Since Chkpt

MANAGER RUNNING

EXTRACT STOPPED DPUMP 00:00:00 00:08:49

EXTRACT STOPPED EXT1 00:00:00 00:11:37

啟動捕獲程式：

GGSCI (testdb) 6> info all

Program Status Group Lag Time Since Chkpt

MANAGER     RUNNING
EXTRACT     RUNNING     DPUMP       00:00:00      00:00:08
EXTRACT     RUNNING     EXT1        00:00:00      00:00:06

3.配置從資料庫：

–建立Replicat

GGSCI (trptdb) 1> view param ./GLOBALS

GGSCHEMA ogg

CHECKPOINTTABLE ogg.checkpoint

GGSCI (trptdb) 1> dblogin userid ogg,password ogg

GGSCI (trptdb) 2> add checkpointtable ogg.checkpoint

Successfully created checkpoint table OGG.CHECKPOINT.

GGSCI (trptdb) 2> add replicat rep1,exttrail /home/oracle/ggs/dirdat/k1, checkpointtable ogg.checkpoint

REPLICAT added.

GGSCI (trptdb) 3> edit param rep1

REPLICAT rep1
ASSUMETARGETDEFS
USERID ogg, PASSWORD ogg
discardfile ./dirdat/rep1_discard.txt,append,megabytes 5
mapexclude mynet_app.test0410
DDL INCLUDE MAPPED
DDLOPTIONS REPORT
BATCHSQL
DBOPTIONS DEFERREFCONST
DBOPTIONS LOBWRITESIZE 102400
HANDLECOLLISIONS
DDLERROR DEFAULT DISCARD RETRYOP MAXRETRIES 5 RETRYDELAY 20
--grouptransops 1
--maxtransops 1

APPLYNOOPUPDATES --解決不能更新操作
MAP mynet_app.*, TARGET mynet_app.*;

------------------------------------------------------------------------------------------------------------------------

4.初始化：

如果要求零停機，DB事務就會不間斷進行，可以透過如下兩種方法來保證初始化過程中事務的完整性和資料的準確性呢.

（1）. 利用 Keys + Handlecollisions

Handlecollisions引數依賴於表上的Key(Primarykey/Unique key)來對資料進行重複行和缺失行的處理

利用 commit SCN/CSN

可以使用一下幾種方法來實現：

一致性的exp和imp

透過exp和expdp的一致性引數(flashback_scn)，匯出特定SCN點上的一致性版本.

FLASHBACK_SCN 引數用於指定匯出特定SCN時刻的表資料，如

使用這種方法初始化存在一個問題，就是構造一致性資料過程中會對undo造成

比較大的壓力，尤其對大型資料庫來講，可以透過分割的datapump來實現

資料的分組同步，分散undo的壓力，然後合適時間將分組的datapump合併即

可。

（2）一致性的expdp和impdp (表空間移植,但是需要將表控制置於read only模式)

（3）基於的表空間搬移

對於不同位元組順序的source-target平臺初始化，需要進行convert），但是需要

10g以上版本才支援，同樣受到表空間搬移的那些限制條件。

Dataguard 適合同平臺版本

我這裡採用的是用帶flashback_scn引數的expdp/impdp完成初始化，具體操作如下：

–主資料庫

[oracle@trptdb ggs]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Fri Mar 23 13:48:11 2012

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select current_scn from v$database ;

CURRENT_SCN

———–

70653113406

--匯出匯入（從資料庫）

impdp strmadmin directory = DPDATA1 network_link = uat schemas=mynet_app flashback_scn=70653113406 TABLE_EXISTS_ACTION =replace

。。。

匯入完畢，叢庫啟動replicat程式：

GGSCI (trptdb) 3> start rep1,aftercsn 70653113406

之後主從開始同步，日誌如下：

主庫：

2012-03-23 14:08:36 WARNING OGG-00869 Oracle GoldenGate Capture for Oracle, ext1.prm: Object MYNET_APP.M_WEEKLY_REPORT_SOURCING has NOLOGGING option specified either at the table level or at the level of one of its (sub)partitions.
2012-03-23 14:08:36 WARNING OGG-00869 Oracle GoldenGate Capture for Oracle, ext1.prm: No unique key is defined for table M_WEEKLY_REPORT_SOURCING. All viable columns will be used to represent the key, but may not guarantee uniqueness. KEYCOLS may be used to define the key.
2012-03-23 14:10:33 WARNING OGG-00869 Oracle GoldenGate Capture for Oracle, ext1.prm: Object MYNET_APP.M_WEEKLY_REPORT_CATEGORY has NOLOGGING option specified either at the table level or at the level of one of its (sub)partitions.
2012-03-23 14:10:33 WARNING OGG-00869 Oracle GoldenGate Capture for Oracle, ext1.prm: No unique key is defined for table M_WEEKLY_REPORT_CATEGORY. All viable columns will be used to represent the key, but may not guarantee uniqueness. KEYCOLS may be used to define the key.
2012-03-23 14:11:42 WARNING OGG-00869 Oracle GoldenGate Capture for Oracle, ext1.prm: Object MYNET_APP.M_WEEKLY_REPORT_ITEM has NOLOGGING option specified either at the table level or at the level of one of its (sub)partitions.
2012-03-23 14:11:42 WARNING OGG-00869 Oracle GoldenGate Capture for Oracle, ext1.prm: No unique key is defined for table M_WEEKLY_REPORT_ITEM. All viable columns will be used to represent the key, but may not guarantee uniqueness. KEYCOLS may be used to define the key.
2012-03-23 14:12:19 WARNING OGG-00869 Oracle GoldenGate Capture for Oracle, ext1.prm: No unique key is defined for table M_MEMBER_COMMENT. All viable columns will be used to represent the key, but may not guarantee uniqueness. KEYCOLS may be used to define the key.
2012-03-23 14:14:15 WARNING OGG-00869 Oracle GoldenGate Capture for Oracle, ext1.prm: Object MYNET_APP.M_WEEKLY_REPORT_SUPPLIER has NOLOGGING option specified either at the table level or at the level of one of its (sub)partitions.
2012-03-23 14:14:15 WARNING OGG-00869 Oracle GoldenGate Capture for Oracle, ext1.prm: No unique key is defined for table M_WEEKLY_REPORT_SUPPLIER. All viable columns will be used to represent the key, but may not guarantee uniqueness. KEYCOLS may be used to define the key.

叢庫：

2012-03-23 14:11:36 INFO    OGG-01670 Oracle GoldenGate Collector for Oracle: Closing /home/oracle/ggs/dirdat/k1000096.
2012-03-23 14:11:36 INFO    OGG-01669 Oracle GoldenGate Collector for Oracle: Opening /home/oracle/ggs/dirdat/k1000097 (byte -1, current EOF 0).
2012-03-23 14:11:54 WARNING OGG-00869 Oracle GoldenGate Delivery for Oracle, rep1.prm: No unique key is defined for table 'M_WEEKLY_REPORT_SOURCING'. All viable columns will be used to represent the key, but may not guarantee uniqueness. KEYCOLS may be used to define the key.
2012-03-23 14:13:37 INFO    OGG-01407 Oracle GoldenGate Delivery for Oracle, rep1.prm: Setting current schema for DDL operation to [mynet_app].
2012-03-23 14:13:37 INFO    OGG-01408 Oracle GoldenGate Delivery for Oracle, rep1.prm: Restoring current schema for DDL operation to [ogg].
2012-03-23 14:13:38 INFO    OGG-01407 Oracle GoldenGate Delivery for Oracle, rep1.prm: Setting current schema for DDL operation to [mynet_app].
2012-03-23 14:13:38 INFO    OGG-01408 Oracle GoldenGate Delivery for Oracle, rep1.prm: Restoring current schema for DDL operation to [ogg].
2012-03-23 14:13:38 INFO    OGG-01407 Oracle GoldenGate Delivery for Oracle, rep1.prm: Setting current schema for DDL operation to [mynet_app].
2012-03-23 14:13:38 INFO    OGG-01408 Oracle GoldenGate Delivery for Oracle, rep1.prm: Restoring current schema for DDL operation to [ogg].
2012-03-23 14:13:48 WARNING OGG-00869 Oracle GoldenGate Delivery for Oracle, rep1.prm: No unique key is defined for table 'M_WEEKLY_REPORT_CATEGORY'. All viable columns will be used to represent the key, but may not guarantee uniqueness. KEYCOLS may be used to define the key.

5.同步測試：

主庫建張表並插入些資料：

[oracle@testdb ggs]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.5.0 - Production on Fri Mar 23 14:17:07 2012

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> conn mynet_app
Enter password:
Connected.
SQL> create table test121312(id number);

Table created.

SQL> insert into test121312 select rownum from dual connect by rownum<=10;

10 rows created.

SQL> commit;

Commit complete.

SQL> select count(9) from test121312;

COUNT(9)
----------
10

SQL>

叢庫進行查詢：

[oracle@trptdb ggs]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Fri Mar 23 14:23:44 2012

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> conn mynet_app
Enter password:
Connected.
SQL> select count(9) from test121312;

COUNT(9)
----------
10

SQL>

成功同步，在此記錄一下~

oracle goldengate 配置

相關文章