GoldenGate: Oracle 資料庫表格同步測試步驟

更新於2016.1.26

如果同步10.2.0.5 , 11.2.0.2 之後版本的資料庫, 請先參考以下連結

http://www.askmaclean.com/archives/goldengate-parameter-suppresstriggers-deferrefconst.html

http://oracleabout.blogspot.tw/2013/06/goldengate-user-creation.html

OGG Tuning 所需注意的tips

http://jaychu649.blogspot.tw/2015/11/ogg-tuning-tips.html

-----

前言:

1.Target replicate 端 需 disable triggers , and disable cascade delete constraints
  否則extract端執行一次delete，replicat端會執行兩次 delete，造成進程abend
  (when target triggers :  AFTER/before INSERT OR UPDATE OR DELETE)

2.為了達成consistency , 可用expdp 的flashback_time 來做initial load，並在add extract時指定時間性
2.1 使用flashback_time 做initial load(一般來說少用時間來做初始化而是SCN)
    --Check Flashback Database retention
    --select (sysdate - oldest_flashback_time)*24*60 as history from v$flashback_database_log;
    --get 140.35 ==> 140mins, expdp最久可以做140mins
    expdp 'test/test'  flashback_time=\"TO_TIMESTAMP\(\'2010-06-30 10:15:00\', \'YYYY-MM-DD HH24:MI:SS\'\)\" schemas=test dumpfile=test.dmp
    add extract ext1 tranlog , begin 2010-06-30 10:15
2.2 使用SCN 做initial load (system change number)
    select dbms_flashback.get_system_change_number() from dual;  --get scn 14073964 
    expdp 'test/test' flashback_scn=14073964 schemas=test dumpfile=test.dmp
    start replicat aaaa, AFTERCSN 14073963  (並不包含n, 所以要 + 1 )
    or
    start replicat aaaa, atcsn 14073964

最好的辦法還是在一段乾淨的時間做export的動作, 不然遇到了long transaction的區間下, 進行export , 會有掉資料的情形

Ref: https://community.oracle.com/thread/2430923

目標 : 達成跨版本且跨平台之資料同步

環境設定

Source db (Oracle 9.2.0.8)

OS	Aix 5300-06
IP	192.168.11.190
GoldenGate版本	Oracle GoldenGate V10.4.0.x for Oracle 9i 64bit on AIX 5.3/6.1
GoldenGate 路徑	/oracle/gg/
C complier 版本
Instance_name	orcl
.profile (oracle)	ORACLE_HOME=/oracle/db920 ; export ORACLE_HOME ORACLE_SID=orcl ; export ORACLE_SID export LIBPATH=$ORACLE_HOME/lib LD_LIBRARY_PATH=$ORACLE_HOME/lib NLS_LANG=AMERICAN_AMERICA.AL32UTF8

Target db (Oracle 11.1.0.7)

OS	Solaris 10 8/07[註1]
IP	192.168.11.174
GoldenGate 版本	Oracle GoldenGate V10.4.0.x for Oracle 11g 64bit on Solaris 10
GoldenGate 路徑	/oracle11g/
Instance_name	orcl
.profile (oroacle)	ORACLE_HOME= /oracle11g/db111 ; export ORACLE_HOME ORACLE_SID=orcl ; export ORACLE_SID LD_LIBRARY_PATH=$ORACLE_HOME/lib32:/usr/lib:/usr/ccs/lib:/usr/dt/lib:/usr/openwin/lib:.; export LD_LIBRARY_PATH LD_LIBRARY_PATH_64=$ORACLE_HOME/lib:/usr/lib:/usr/ccs/lib:/usr/dt/lib:/usr/openwin/lib:.; export LD_LIBRARY_PATH_64 NLS_LANG=AMERICAN_AMERICA.AL32UTF8

Preconfig :

設定兩台資料庫時間同步[註2]  ex : 用linux 指令: date 031614522010  或設定 ntp server

基礎設定 : 建立資料夾 ，啟動manager 在兩台資料庫

Source

cd /oracle/gg ./ggsci GGSCI (COM-AIX) #> create subdirs GGSCI (COM-AIX) #> edit params mgr port 7809 dynamicportlist 7900-7950 purgeoldextracts ./dirdat/t*, minkeepdays 2, usecheckpoints GGSCI (COM-AIX) #> start mgr Manager started. GGSCI (COM-AIX) #> info mgr Manager is running (IP port COM-AIX.7809).

Target

cd /oracle11g/gg ./ggsci GGSCI ( db2 ) #> create subdirs GGSCI ( db2 ) #> edit params mgr port 7809 dynamicportlist 7900-7950 purgeoldextracts ./dirdat/t*, minkeepdays 2, usecheckpoints GGSCI ( db2 ) #> info all Program     Status      Group       Lag           Time Since Chkpt MANAGER     STOPPED GGSCI ( db2 ) #> start mgr Manager started. GGSCI ( db2 ) #> info mgr Manager is running (IP port db2.7809).

Source : 開啟archiving , supplemental log

# su - oracle $ lsnrctl start sqlplus "/ as sysdba" startup alter database add supplemental log data; --verify select supplemental_log_data_min from v$database; alter system switch logfile; --開啟archive log mode alter system set log_archive_dest_1="LOCATION=/oracle/oradata/arch" scope=both; alter system set log_archive_start=true scope=spfile; shutdown immediate; startup mount; alter database archivelog; alter database open;

Source : 建立user及賦予權限。

SQL> create user ggs identified by ggs default tablespace users; grant create session to ggs; grant alter session to ggs; grant connect , resource to ggs; grant select any dictionary to ggs; grant select any table to ggs; create table ggs.tb1 (A number);

Target : 建立user及賦予權限。

SQL> create user ggt identified by ggt default tablespace users; grant create session to ggt; grant alter session to ggt; grant connect , resource to ggt; grant select any dictionary to ggt; grant select any table to ggt; create table ggt.tb1 (A number); insert into ggt.tb1 values ('1'); commit;

1.設定Initial Load

Source

GGSCI (COM-AIX) #> add extract initmydb , sourceistable[C1]  EXTRACT added. GGSCI (COM-AIX) #>edit params initmydb extract initmydb setenv (ORACLE_SID=ORCL)      userid ggs@ORCL,password ggs rmthost 192.168.11.174, mgrport 7809 rmttask replicat, group delmydb table ggs.tb1;  [C2]

Target

GGSCI ( db2 ) #> add replicat delmydb,specialrun REPLICAT added. GGSCI ( db2 ) #> info replicat delmydb,tasks REPLICAT   DELMYDB   Initialized   2010-03-17 14:20   Status STOPPED Checkpoint Lag       00:00:00 (updated 00:00:05 ago) Log Read Checkpoint  Not Available Task                 SPECIALRUN GGSCI ( db2 ) #> edit params delmydb replicat delmydb setenv (ORACLE_SID=ORCL) assumetargetdefs[C3]  userid ggt@ORCL,password ggt discardfile[C4]  ./dirrpt/delmydb.dsc, purge HANDLECOLLISIONS[C5]   map ggs.tb1, target ggt.tb1;

Prerequisites for initial load (gg_wux_admin_v104.pdf)

1.      確認target tables是空的，或已經有unique constraint否則資料會重複塞入。

Ex : truncate table table_name;

2.      Disable foreign key constraints

set echo off set newpage 0 set space 0 set pagesize 0 set feedback off set heading off set trimspool on set tab off select 'alter table '||a.owner||'.'||a.table_name|| ' disable constraint '||a.constraint_name||';' from all_constraints a, all_constraints b where a.constraint_type = 'R' and a.r_constraint_name = b.constraint_name and a.r_owner  = b.owner;

3.      Remove indexes from target (optional)

4.      HANDLECOLLISIONS

Source

GGSCI (COM-AIX) #> start EXTRACT initmydb find out if the load is finished. VIEW REPORT initmydb START REPLICAT delmydb

Source

可開另一個視窗tail -f /oracle/gg/ggserr.log 觀察進度

Target

tail -f /oracle11g/gg/ggserr.log

檢查record

At source database : while true; do ./ggs.sh; sleep 5; done #ggs.sh sqlplus "/ as sysdba" <<EOF select * from ggs.tb1; EOF At target database : while true; do ./ggt.sh; sleep 5; done #ggt.sh sqlplus 'sys/oracle@orclt as sysdba' <<EOF select * from ggt.tb1; EOF

心得:

1.initial load之前要先把target table資料全部刪掉，否則會有重複資料?。

2.如果Target表格有定義unique primary key且已存在資料且，在initial load 會遇到primary key限制，而失敗。 ex:

2010-03-16 10:44:55  GGS WARNING     218  Oracle GoldenGate Delivery for Oracle, delmydb.prm:  Aborted grouped transaction on 'GGT.TB1', Database error 1 (ORA-00001: unique constraint (GGT.PK1) violated).

2.設定Online synchronization

Create a GoldenGate Checkpoint table

Target(在Target建立checkpoint table即可)

GGSCI (COM-AIX) #> EDIT PARAMS ./GLOBALS CHECKPOINTTABLE GGT.CHKPTAB GGSCI ( db2 ) #> DBLOGIN USERID ggt@ORCL, PASSWORD ggt Successfully logged into database. GGSCI ( db2 ) #> ADD CHECKPOINTTABLE GGT.CHKPTAB Successfully created checkpoint table GGT.CHKPTAB. $sqlplus ggs/ggs SQL> desc ggs.chkptab;  Name                                      Null?    Type  ----------------------------------------- -------- ----------------------------  GROUP_NAME                                NOT NULL VARCHAR2(8)  GROUP_KEY                                 NOT NULL NUMBER(19)  SEQNO                                              NUMBER(10)  RBA                                       NOT NULL NUMBER(19)  AUDIT_TS                                           VARCHAR2(29)  CREATE_TS                                 NOT NULL DATE  LAST_UPDATE_TS                            NOT NULL DATE  CURRENT_DIR                               NOT NULL VARCHAR2(255)

Create an Extract group

Source

GGSCI (COM-AIX) #> add extract ext1 tranlog , begin now [C6]  EXTRACT added. GGSCI (COM-AIX) #> add rmttrail /oracle11g/ggs/r1 extract ext1, megabytes 50 RMTTRAIL added.

Create a parameter file for the online Extract group

Source

GGSCI (COM-AIX) #>edit param ext1 extract ext1 SETENV (ORACLE_SID=ORCL) userid ggs@ORCL,password ggs rmthost 192.168.11.174, mgrport 7809 rmttrail /oracle11g/ggs/r1 discardfile ./dirrpt/disext1.dsc, APPEND, MEGABYTES 10 table ggs.tb1;

Create a Replicat group

Target

GGSCI ( db2 ) #> ADD replicat rep1 ,EXTTRAIL /oracle11g/ggs/r1, CHECKPOINTTABLE GGT.CHKPTAB

Create a parameter file for the online Replicat group

Target

GGSCI ( db2 ) #> edit param rep1 replicat rep1 assumetargetdefs SETENV (ORACLE_SID=ORCL) userid ggt@ORCL,password ggt EXTTRAIL /oracle11g/ggs/r1 discardfile ./dirrpt/disrep1.dsc, APPEND, MEGABYTES 10 map ggs.tb1, target ggt.tb1;

Source

GGSCI (COM-AIX) #> start ext1 Sending START request to MANAGER ... EXTRACT EXT1 starting GGSCI (COM-AIX) #> info ext1 EXTRACT    EXT1      Last Started 2010-03-16 18:56   Status RUNNING Checkpoint Lag       00:00:00 (updated 00:00:03 ago) Log Read Checkpoint  Oracle Redo Logs                      2010-03-16 10:10:47  Seqno 36, RBA 56434688

Target

GGSCI ( db2 ) #> start rep1 Sending START request to MANAGER ... REPLICAT REP1 starting GGSCI ( db2 ) #> info rep1 REPLICAT   REP1      Last Started 2010-03-16 15:14   Status RUNNING Checkpoint Lag       00:00:00 (updated 00:00:07 ago) Log Read Checkpoint  File /home/oracle/ggs/r1000000                      First Record  RBA 0

心得:

1.      如果正在online-sync 刪除rep1 要將trail資料一起刪掉，否則在加回去之後會重新再塞一次trail的資料。

2.      ggt.chkptab 非常重要，請勿刪資料。

3.設定DDL 同步

cd /oracle/gg sqlplus "/ as sysdba" 檢查是否已經configure goldengate SQL> @marker_status Please enter the name of a schema for the GoldenGate database objects: ggs Setting schema name to GGS MARKER TABLE -------------------------------------------------------------- FAILED: Table does not exist MARKER SEQUENCE -------------------------------------------------------------- FAILED: Sequence does not exist SQL> @marker_setup Marker setup script You will be prompted for the name of a schema for the GoldenGate database object s. NOTE: The schema must be created prior to running this script. NOTE: Stop all DDL replication before starting this installation. Enter GoldenGate schema name:ggs MARKER TABLE ------------------------------- OK MARKER SEQUENCE ------------------------------- OK Script complete. SQL> alter session set recyclebin=OFF; #在oracle 9i 並無recyclebin故此步驟忽略[C7]  SQL> @ddl_setup GoldenGate DDL Replication setup script Verifying that current user has privileges to install DDL Replication... You will be prompted for the name of a schema for the GoldenGate database object s. NOTE: The schema must be created prior to running this script. NOTE: On Oracle 10g and up, system recycle bin must be disabled. NOTE: Stop all DDL replication before starting this installation. Enter GoldenGate schema name:ggs You will be prompted for the mode of installation. To install or reinstall DDL replication, enter INITIALSETUP To upgrade DDL replication, enter NORMAL Enter mode of installation:INITIALSETUP Using GGS as a GoldenGate schema name, INITIALSETUP as a mode of installation. Working, please wait ... DDL replication setup script complete, running verification script... Please enter the name of a schema for the GoldenGate database objects: Setting schema name to GGS DDLORA_GETTABLESPACESIZE STATUS: Line/pos ---------------------------------------- Error ----------------------------------------------------------------- No errors No errors CLEAR_TRACE STATUS: Line/pos ---------------------------------------- Error ----------------------------------------------------------------- No errors No errors CREATE_TRACE STATUS: Line/pos ---------------------------------------- Error ----------------------------------------------------------------- No errors No errors TRACE_PUT_LINE STATUS: Line/pos ---------------------------------------- Error ----------------------------------------------------------------- No errors No errors INITIAL_SETUP STATUS: Line/pos ---------------------------------------- Error ----------------------------------------------------------------- No errors No errors DDLVERSIONSPECIFIC PACKAGE STATUS: Line/pos ---------------------------------------- Error ----------------------------------------------------------------- No errors No errors DDLREPLICATION PACKAGE STATUS: Line/pos ---------------------------------------- Error ----------------------------------------------------------------- No errors No errors DDLREPLICATION PACKAGE BODY STATUS: Line/pos ---------------------------------------- Error ----------------------------------------------------------------- No errors No errors DDL HISTORY TABLE ----------------------------------- OK DDL HISTORY TABLE(1) ----------------------------------- OK DDL DUMP TABLES ----------------------------------- OK DDL DUMP COLUMNS ----------------------------------- OK DDL DUMP LOG GROUPS ----------------------------------- OK DDL DUMP PARTITIONS ----------------------------------- OK DDL DUMP PRIMARY KEYS ----------------------------------- OK DDL SEQUENCE ----------------------------------- OK GGS_TEMP_COLS ----------------------------------- OK GGS_TEMP_UK ----------------------------------- OK DDL TRIGGER CODE STATUS: Line/pos ---------------------------------------- Error ----------------------------------------------------------------- No errors No errors DDL TRIGGER INSTALL STATUS ----------------------------------- OK DDL TRIGGER RUNNING STATUS -------------------------------------------------------------------------------- ENABLED STAYMETADATA IN TRIGGER -------------------------------------------------------------------------------- OFF DDL TRIGGER SQL TRACING -------------------------------------------------------------------------------- 0 DDL TRIGGER TRACE LEVEL -------------------------------------------------------------------------------- 0 LOCATION OF DDL TRACE FILE -------------------------------------------------------------------------------- /oracle/admin/orcl/udump/ggs_ddl_trace.log Analyzing installation status... STATUS OF DDL REPLICATION -------------------------------------------------------------------------------- SUCCESSFUL installation of DDL Replication software components Script complete. SQL> @role_setup GGS Role setup script This script will drop and recreate the role GGS_GGSUSER_ROLE To use a different role name, quit this script and then edit the params.sql scri pt to change the gg_role parameter to the preferred name. (Do not run the script .) You will be prompted for the name of a schema for the GoldenGate database object s. NOTE: The schema must be created prior to running this script. NOTE: Stop all DDL replication before starting this installation. Enter GoldenGate schema name: ggs Wrote file role_setup_set.txt PL/SQL procedure successfully completed. Role setup script complete Grant this role to each user assigned to the Extract, GGSCI, and Manager process es, by using the following SQL command: GRANT GGS_GGSUSER_ROLE TO <loggedUser> where <loggedUser> is the user assigned to the GoldenGate processes. SQL> grant ggs_ggsuser_role to ggs;   Grant succeeded. SQL> @ddl_enable Trigger altered. SQL> @ddl_pin ggs BEGIN dbms_shared_pool.keep('ggs .DDLReplication', 'P'); END;       * ERROR at line 1: ORA-06550: line 1, column 7: PLS-00201: identifier 'DBMS_SHARED_POOL.KEEP' must be declared ORA-06550: line 1, column 7:  9i無pin ddl功能 PL/SQL: Statement ignored SQL> desc dbms_shared_pool ERROR: ORA-04043: object dbms_shared_pool does not exist 於Source: GGSCI (COM-AIX) #> EDIT PARAM EXT1 加入以下參數 DDL INCLUDE MAPPED

Target (僅在source config即可)

cd /oracle11g/gg sqlplus "/ as sysdba" SQL> @marker_setup Marker setup script You will be prompted for the name of a schema for the GoldenGate database object s. NOTE: The schema must be created prior to running this script. NOTE: Stop all DDL replication before starting this installation. Enter GoldenGate schema name:ggt Marker setup table script complete, running verification script... Please enter the name of a schema for the GoldenGate database objects: Setting schema name to GGT MARKER TABLE -------------------------------------------------------------- OK MARKER SEQUENCE -------------------------------------------------------------- OK Script complete. SQL> @ddl_setup GoldenGate DDL Replication setup script Verifying that current user has privileges to install DDL Replication... You will be prompted for the name of a schema for the GoldenGate database object s. NOTE: The schema must be created prior to running this script. NOTE: On Oracle 10g and up, system recycle bin must be disabled. NOTE: Stop all DDL replication before starting this installation. Enter GoldenGate schema name:ggt You will be prompted for the mode of installation. To install or reinstall DDL replication, enter INITIALSETUP To upgrade DDL replication, enter NORMAL Enter mode of installation:INITIALSETUP Working, please wait ... Spooling to file ddl_setup_spool.txt Using GGT as a GoldenGate schema name, INITIALSETUP as a mode of installation. Working, please wait ... RECYCLEBIN must be empty. This installation will purge RECYCLEBIN for all users. To proceed, enter yes. To stop installation, enter no. Enter yes or no:yes DDL replication setup script complete, running verification script... Please enter the name of a schema for the GoldenGate database objects: Setting schema name to GGT DDLORA_GETTABLESPACESIZE STATUS: Line/pos -------------------- Error ----------------------------------------------------------------- No errors No errors CLEAR_TRACE STATUS: Line/pos -------------------- Error ----------------------------------------------------------------- No errors No errors CREATE_TRACE STATUS: Line/pos -------------------- Error ----------------------------------------------------------------- No errors No errors TRACE_PUT_LINE STATUS: Line/pos -------------------- Error ----------------------------------------------------------------- No errors No errors INITIAL_SETUP STATUS: Line/pos -------------------- Error ----------------------------------------------------------------- No errors No errors DDLVERSIONSPECIFIC PACKAGE STATUS: Line/pos -------------------- Error ----------------------------------------------------------------- No errors No errors DDLREPLICATION PACKAGE STATUS: Line/pos -------------------- Error ----------------------------------------------------------------- No errors No errors DDLREPLICATION PACKAGE BODY STATUS: Line/pos -------------------- Error ----------------------------------------------------------------- No errors No errors DDL HISTORY TABLE ---------------------------------------------------------------------- OK DDL HISTORY TABLE(1) ---------------------------------------------------------------------- OK DDL DUMP TABLES ---------------------------------------------------------------------- OK DDL DUMP COLUMNS ---------------------------------------------------------------------- OK DDL DUMP LOG GROUPS ---------------------------------------------------------------------- OK DDL DUMP PARTITIONS ---------------------------------------------------------------------- OK DDL DUMP PRIMARY KEYS ---------------------------------------------------------------------- OK DDL SEQUENCE ---------------------------------------------------------------------- OK GGS_TEMP_COLS ---------------------------------------------------------------------- OK GGS_TEMP_UK ---------------------------------------------------------------------- OK DDL TRIGGER CODE STATUS: Line/pos -------------------- Error ----------------------------------------------------------------- No errors No errors DDL TRIGGER INSTALL STATUS ---------------------------------------------------------------------- OK DDL TRIGGER RUNNING STATUS ---------------------------------------------------------------------- ENABLED STAYMETADATA IN TRIGGER ---------------------------------------------------------------------- OFF DDL TRIGGER SQL TRACING ---------------------------------------------------------------------- 0 DDL TRIGGER TRACE LEVEL ---------------------------------------------------------------------- 0 LOCATION OF DDL TRACE FILE -------------------------------------------------------------------------------- /oracle11g/diag/rdbms/orcl/orcl/trace/ggs_ddl_trace.log Analyzing installation status... STATUS OF DDL REPLICATION -------------------------------------------------------------------------------- SUCCESSFUL installation of DDL Replication software components Script complete. SQL> @role_setup GGS Role setup script This script will drop and recreate the role GGS_GGSUSER_ROLE To use a different role name, quit this script and then edit the params.sql scri pt to change the gg_role parameter to the preferred name. (Do not run the script .) You will be prompted for the name of a schema for the GoldenGate database object s. NOTE: The schema must be created prior to running this script. NOTE: Stop all DDL replication before starting this installation. Enter GoldenGate schema name:ggt Wrote file role_setup_set.txt PL/SQL procedure successfully completed. Role setup script complete Grant this role to each user assigned to the Extract, GGSCI, and Manager process es, by using the following SQL command: GRANT GGS_GGSUSER_ROLE TO <loggedUser> where <loggedUser> is the user assigned to the GoldenGate processes. SQL> grant ggs_ggsuser_role to ggt; Grant succeeded. SQL> @ddl_enable Trigger altered. SQL> @ddl_pin ggt PL/SQL procedure successfully completed. PL/SQL procedure successfully completed. PL/SQL procedure successfully completed.

在Source 重新啟動extractor ext1:

DDL的語法即可同步(truncate table , delete from , alter table table_name  modify ("A" varchar2(40)); 等等)到target 資料庫。

GGSCI (COM-AIX) #> stop ext1 Sending STOP request to EXTRACT EXT1 ... Request processed. GGSCI (COM-AIX) 97> start ext1 Sending START request to MANAGER ... EXTRACT EXT1 starting GGSCI (COM-AIX) 98> info all Program     Status      Group       Lag           Time Since Chkpt MANAGER     RUNNING                                           EXTRACT     STOPPED     EXT1        00:00:00      00:00:16

心得:

如果重建extract,記得重新config enable DDL

4.測試

Source

insert into ggs.tb1 values ('2'); insert into ggs.tb1 values ('3'); insert into ggs.tb1 values ('4'); insert into ggs.tb1 values ('5'); commit; 資料已經過去!!

塞入一百萬筆資料測試

begin    for v_number in 10000000..2000000 loop         insert into ggs.tb1 values (v_number);         commit;      end loop; end; / Errors in file /oracle/admin/orcl/udump/orcl_ora_307228.trc: ORA-00600: internal error code, arguments: [unable to load XDB library], [], [],  [], [], [], [], [] 可觀察到target trail file之RBA(relative byte addreee)一直補進checkpoint。 RBA數字即為trail檔案大小。

Truncate table測試

SQL> truncate table ggs.tb1; Table truncated.

修改表結構

at Source SQL> alter table ggs.tb1 modify ("A" number(20)); Table altered. at Target

Add , Drop column

Source: SQL> desc ggs.tb1  Name                                      Null?    Type  ----------------------------------------- -------- ----------------------------  A                                                  NUMBER SQL> alter table ggs.tb1 add (B varchar(10)); Table altered. SQL> desc ggs.tb1;  Name                                      Null?    Type  ----------------------------------------- -------- ----------------------------  A                                                  NUMBER  B                                                  VARCHAR2(10) Traget: SQL> desc ggtt.tb1;  Name                                      Null?    Type  ----------------------------------------- -------- ----------------------------  A                                                  NUMBER  B                                                  VARCHAR2(10) Source: SQL> select * from ggs.tb1 where rownum ;          A B ---------- --------------------       2661       2662 SQL> alter table ggs.tb1 drop column B; Table altered. Traget: SQL> desc ggtt.tb1;  Name                                      Null?    Type  ----------------------------------------- -------- ----------------------------  A

[註1] Oracle® GoldenGate Patch Set Notes 10.4

In Solaris 9 and Solaris 10 environments, there is a known memory leak that could

affect our product and cause Extract to Abend

The patch for Solaris 10 is here: 

http://sunsolve.sun.com/search/document.do?assetkey=1-21-119963-15-1 

patchadd /source/119963-15

This patch for Solaris 9 is here: 

http://sunsolve.sun.com/search/document.do?assetkey=1-21-111712-23-1

patchadd /source/111712-23

[註2]

Extract , Capture 皆會根據redo checkpoint的時間來判定，故ntp時間同步性非常重要 !

[註3] 參考連結

http://gavinsoorma.wordpress.com/2010/02/08/goldengate-concepts-and-architecture/

---

 [C1]Extracts entire records from source tables

, threads 2 (for 2 node rac)

 [C2]如果source跑在ASM上的話加上:

TRANLOGOPTIONS ASMUSER sys@ASM1, ASMPASSWORD g32adrwe, ENCRYPTKEY DEFAULT

 [C3]Use ASSUMETARGETDEFS if the source and target

tables have the same definitions.

 [C4]

Generate a discard file to which GoldenGate can log records that it cannot process.

Goldengate從redo帶出的交易紀錄內容如果有部份無法被target 辨認則會被寫到此discard file.

APPEND : 一直APPEND

PURGE : 遇到錯誤就再砍掉

 [C5]Handle collisions between failback data copy and replication

 [C6], add extract ext1 tranlog , begin now , thread n

代表目前本機有幾個redo thread…如果為2 node RAC , 則 n=2

Specifies the number of redo threads when extracting data from an Oracle RAC clustered database configuration.

 [C7]

資料庫需關閉資源回收筒功能，並重啟。

SQL>

alter system set RECYCLEBIN=OFF scope=spfile;