星期四, 12月 01, 2011

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
    --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 14073965  (14073964 + 1 )
    or

    start replicat aaaa, atcsn 14073964

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;

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)
userid ggt@ORCL,password ggt
discardfile[C4]  ./dirrpt/delmydb.dsc, purge
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:  9ipin 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 fileRBA(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] 參考連結



 [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.
Goldengateredo帶出的交易紀錄內容如果有部份無法被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;

沒有留言:

LinkWithin-相關文件

Related Posts Plugin for WordPress, Blogger...