Goldengate 基本配置
Goldengate 基本配置
源庫ip：192.168.9.142 db_name:testdb
目標庫ip：192.168.9.143 db_name:o01fdr
監控庫ip：192.168.9.144 db_name:dbadb
===============================================================================
1-9步源庫和目標資料庫做一樣的操作
===============================================================================
1 建立目錄並安裝
[oracle@test1 ~]:testdb> cd /tmp
[oracle@test1 tmp]:testdb> ll
total 87124
drwxr-x---. 2 oracle oinstall 4096 Oct 28 13:27 hsperfdata_oracle
drwx------. 2 root root 4096 Oct 28 11:24 keyring-uaxFJh
-rw-r--r--. 1 root root 89186858 Oct 28 10:08 ogg112101_fbo_ggs_Linux_x64_ora11g_64bit.zip
drwx------. 2 root root 4096 Oct 28 11:28 orbit-root
drwx------. 2 root root 4096 Oct 28 11:24 pulse-oZDqCnH2yFgj
drwx------. 2 oracle oinstall 4096 Aug 7 22:41 pulse-Up2S1oVzRfXF
drwx------. 2 root root 4096 Oct 28 11:24 ssh-WSdzia1676
[oracle@test1 tmp]:testdb> mkdir -p /app/goldengate
[oracle@test1 tmp]:testdb>
[oracle@test1 tmp]:testdb> cp ogg112101_fbo_ggs_Linux_x64_ora11g_64bit.zip /app/goldengate/
[oracle@test1 tmp]:testdb>
[oracle@test1 tmp]:testdb>
[oracle@test1 tmp]:testdb> cd /app/goldengate/
[oracle@test1 goldengate]:testdb> ll
total 87100
-rw-r--r--. 1 oracle oinstall 89186858 Oct 28 13:44 ogg112101_fbo_ggs_Linux_x64_ora11g_64bit.zip
[oracle@test1 goldengate]:testdb> unzip ogg112101_fbo_ggs_Linux_x64_ora11g_64bit.zip
Archive: ogg112101_fbo_ggs_Linux_x64_ora11g_64bit.zip
inflating: fbo_ggs_Linux_x64_ora11g_64bit.tar
inflating: OGG_WinUnix_Rel_Notes_11.2.1.0.1.pdf
inflating: Oracle GoldenGate 11.2.1.0.1 README.txt
inflating: Oracle GoldenGate 11.2.1.0.1 README.doc
[oracle@test1 goldengate]:testdb> ll
total 310632
-rw-rw-r--. 1 oracle oinstall 228556800 Apr 23 2012 fbo_ggs_Linux_x64_ora11g_64bit.tar
-rw-r--r--. 1 oracle oinstall 89186858 Oct 28 13:44 ogg112101_fbo_ggs_Linux_x64_ora11g_64bit.zip
-rwxrwxrwx. 1 oracle oinstall 220546 May 2 2012 OGG_WinUnix_Rel_Notes_11.2.1.0.1.pdf
-rwxrwxrwx. 1 oracle oinstall 93696 May 2 2012 Oracle GoldenGate 11.2.1.0.1 README.doc
-rwxrwxrwx. 1 oracle oinstall 24390 May 2 2012 Oracle GoldenGate 11.2.1.0.1 README.txt
[oracle@test1 goldengate]:testdb> tar -xvf fbo_ggs_Linux_x64_ora11g_64bit.tar
UserExitExamples/
UserExitExamples/ExitDemo_more_recs/
UserExitExamples/ExitDemo_more_recs/Makefile_more_recs.HPUX
UserExitExamples/ExitDemo_more_recs/Makefile_more_recs.SOLARIS
UserExitExamples/ExitDemo_more_recs/Makefile_more_recs.LINUX
UserExitExamples/ExitDemo_more_recs/Makefile_more_recs.AIX
UserExitExamples/ExitDemo_more_recs/exitdemo_more_recs.vcproj
UserExitExamples/ExitDemo_more_recs/exitdemo_more_recs.c
UserExitExamples/ExitDemo_more_recs/readme.txt
UserExitExamples/ExitDemo_passthru/
UserExitExamples/ExitDemo_passthru/exitdemo_passthru.c
UserExitExamples/ExitDemo_passthru/exitdemopassthru.vcproj
UserExitExamples/ExitDemo_passthru/Makefile_passthru.HPUX
UserExitExamples/ExitDemo_passthru/Makefile_passthru.AIX
UserExitExamples/ExitDemo_passthru/Makefile_passthru.HP_OSS
UserExitExamples/ExitDemo_passthru/Ma
===============================================================================
2 配置環境變數：
源庫：
[oracle@test1 ~]:testdb> cat prof_testdb
# .bash_profile
# Get the aliases and functions
if [ -f ~/.bashrc ]; then
. ~/.bashrc
fi
# User specific environment and startup programs
export ORACLE_BASE=/app/oracle
export ORACLE_HOME=$ORACLE_BASE/ora11g
export GRID_BASE=/app/grid
export GRID_HOME=$GRID_BASE/grid11g
export ORACLE_SID=testdb
export TNS_ADMIN=$ORACLE_HOME/network/admin
export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:$ORACLE_HOME/lib
export PATH=$PATH:$HOME/bin:$ORACLE_HOME/bin:$GRID_HOME/bin:/usr/sbin:/usr/local/bin:$ORACLE_HOME/OPatch
export EDITOR=vi
export NLS_LANG=AMERICAN_AMERICA.ZHS16GBK
export NLS_DATE_FORMAT="yyyy-mm-dd hh24:mi:ss"
export OGG_HOME=/app/goldengate
export LD_LIBRARY_PATH=$OGG_HOME:$LD_LIBRARY_PATH
export PATH=$OGG_HOME:$PATH
export PS1="[\u@\h \W]:$ORACLE_SID> "
umask 022
[oracle@test1 ~]:testdb>
目標庫：
[oracle@test2 ~]:O01FDR> cat prof_O01FDR
# .bash_profile
# Get the aliases and functions
if [ -f ~/.bashrc ]; then
. ~/.bashrc
fi
# User specific environment and startup programs
export ORACLE_BASE=/app/oracle
export ORACLE_HOME=$ORACLE_BASE/ora11g
export GRID_BASE=/app/grid
export GRID_HOME=$GRID_BASE/grid11g
export ORACLE_SID=O01FDR
export TNS_ADMIN=$ORACLE_HOME/network/admin
export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:$ORACLE_HOME/lib
export PATH=$PATH:$HOME/bin:$ORACLE_HOME/bin:$GRID_HOME/bin:/usr/sbin:/usr/local/bin:$ORACLE_HOME/OPatch
export EDITOR=vi
export NLS_LANG=AMERICAN_AMERICA.ZHS16GBK
export NLS_DATE_FORMAT="yyyy-mm-dd hh24:mi:ss"
export OGG_HOME=/app/goldengate
export LD_LIBRARY_PATH=$OGG_HOME:$LD_LIBRARY_PATH
export PATH=$OGG_HOME:$PATH
export PS1="[\u@\h \W]:$ORACLE_SID> "
umask 022
[oracle@test2 ~]:O01FDR>
===============================================================================
3 開啟supplementary log
[oracle@test1 goldengate]:testdb> sqlplus "/as sysdba"
SQL*Plus: Release 11.2.0.4.0 Production on Tue Oct 28 13:50:25 2014
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> select force_logging,supplemental_log_data_min from v$database;
FOR SUPPLEME
--- --------
NO NO
SQL> alter database force logging;
Database altered.
SQL> alter database add supplemental log data;
Database altered.
SQL> select force_logging, supplemental_log_data_min from v$database;
FOR SUPPLEME
--- --------
YES YES
SQL>
===============================================================================
4 建立表空間
&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&
CREATE TABLESPACE DBADATATBS DATAFILE
'/app/oracle/oradata/testdb/dbadatatbs01.dbf' SIZE 2048M AUTOEXTEND OFF
LOGGING
ONLINE
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
BLOCKSIZE 8K
SEGMENT SPACE MANAGEMENT AUTO
FLASHBACK ON;
&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&
資料庫訪問使用者
&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&
CREATE USER GGSYNC
IDENTIFIED BY 123456
DEFAULT TABLESPACE DBADATATBS
TEMPORARY TABLESPACE TEMP
PROFILE DEFAULT
ACCOUNT UNLOCK;
&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&
賦給使用者許可權
&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&
---roles
GRANT CONNECT TO GGSYNC;
GRANT RESOURCE TO GGSYNC;
ALTER USER GGSYNC DEFAULT ROLE ALL;
---privileges
GRANT ALTER SESSION TO GGSYNC;
GRANT CREATE SESSION TO GGSYNC;
GRANT SELECT ANY TABLE TO GGSYNC;
GRANT SELECT ANY DICTIONARY TO GGSYNC;
GRANT CREATE TABLE TO GGSYNC;
GRANT UNLIMITED TABLESPACE TO GGSYNC;
GRANT FLASHBACK ANY TABLE TO GGSYNC;
GRANT INSERT ANY TABLE TO GGSYNC;
GRANT DELETE ANY TABLE TO GGSYNC;
GRANT UPDATE ANY TABLE TO GGSYNC;
GRANT ALTER ANY TABLE TO GGSYNC;
&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&
心跳錶
&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&
CREATE TABLE GGSYNC.GG_SYNC_TESTDB
(
SOURCE_DB VARCHAR2 (20 BYTE),
TARGET_DB VARCHAR2 (20 BYTE),
EXT_NAME VARCHAR2 (20 BYTE),
DPE_NAME VARCHAR2 (20 BYTE),
REP_NAME VARCHAR2 (20 BYTE),
CHECK_FLAG VARCHAR2 (20 BYTE),
CHECK_TIME VARCHAR2 (30 BYTE) DEFAULT SYSDATE
)
TABLESPACE DBADATATBS;
*************************************************
CREATE UNIQUE INDEX GGSYNC.PK_GG_SYNC_TESTDB
ON GGSYNC.GG_SYNC_TESTDB (SOURCE_DB, TARGET_DB);
*************************************************
ALTER TABLE GGSYNC.GG_SYNC_TESTDB ADD (
CONSTRAINT PK_GG_SYNC_TESTDB
PRIMARY KEY
(SOURCE_DB,TARGET_DB)
USING INDEX GGSYNC.PK_GG_SYNC_TESTDB);
***************************************************
GRANT SELECT,
INSERT,
UPDATE,
DELETE
ON GGSYNC.GG_SYNC_TESTDB
TO DBMON;
&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&
建立心跳錶更新儲存過程
&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&
CREATE OR REPLACE PROCEDURE GGSYNC.PROC_GG_SYNC_UPDATE
AS
v_time VARCHAR2 (30 BYTE);
v_count NUMBER;
v_table VARCHAR2 (50 BYTE);
v_sql VARCHAR2 (1000 BYTE);
v_eor VARCHAR2 (200 BYTE);
v_dbname VARCHAR2 (30 BYTE);
BEGIN
SELECT TO_CHAR (SYSDATE, 'YYYY-MM-DD HH24:MI:SS') INTO v_time FROM
DUAL;
SELECT NAME
INTO V_DBNAME
FROM V$DATABASE
WHERE ROWNUM = 1;
v_table := 'ggsync.gg_sync_' || v_dbname;
v_sql :=
'select count(*) from '
|| v_table
|| ' where source_db=''ALL'' and target_db=''ALL''';
EXECUTE IMMEDIATE v_sql INTO v_count;
IF v_count = 0
THEN
BEGIN
v_sql :=
'insert into '
|| v_table
|| ' values(''ALL'',''ALL'',''E_ALL'',''T_ALL'',''R_ALL'',''Y'','''
|| v_time
|| ''')';
EXECUTE IMMEDIATE v_sql;
COMMIT;
END;
ELSE
BEGIN
v_sql :=
'update '
|| v_table
|| ' set check_time='''
|| v_time
|| ''' where source_db=''ALL'' and target_db=''ALL''';
EXECUTE IMMEDIATE v_sql;
COMMIT;
END;
END IF;
EXCEPTION
WHEN OTHERS
THEN
ROLLBACK;
NULL;
END;
/
&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&
建立心跳錶自動更新JOB（GGSYNC 下建立）
&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&
DECLARE
X NUMBER;
BEGIN
SYS.DBMS_JOB.SUBMIT (job => X,
what => 'begin ggsync.proc_gg_sync_update; end;',
next_date => SYSDATE,
interval => 'sysdate+1/1440',
no_parse => FALSE);
SYS.DBMS_OUTPUT.PUT_LINE ('Job Number is: ' || TO_CHAR (x));
COMMIT;
END;
/
&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&
===============================================================================
5 建goldengate目錄
[root@test1 testdb]# su - oracle
[oracle@test1 ~]$ . prof_testdb
[oracle@test1 ~]:testdb> cd $OGG_HOME
[oracle@test1 goldengate]:testdb> ll
total 513484
-rw-r-----. 1 oracle oinstall 426 Oct 15 2010 bcpfmt.tpl
-rw-r-----. 1 oracle oinstall 1725 Oct 15 2010 bcrypt.txt
drwxr-x---. 2 oracle oinstall 4096 Apr 23 2012 cfg
-rw-r-----. 1 oracle oinstall 1285 Feb 29 2012 chkpt_ora_create.sql
[oracle@test1 goldengate]:testdb> mkdir direnv
[oracle@test1 goldengate]:testdb> ./ggsci
Oracle GoldenGate Command Interpreter for Oracle
Version 11.2.1.0.1 OGGCORE_11.2.1.0.1_PLATFORMS_120423.0230_FBO
Linux, x64, 64bit (optimized), Oracle 11g on Apr 23 2012 08:32:14
Copyright (C) 1995, 2012, Oracle and/or its affiliates. All rights reserved.
GGSCI (test1) 1> create subdirs
Creating subdirectories under current directory /app/goldengate
Parameter files /app/goldengate/dirprm: already exists
Report files /app/goldengate/dirrpt: created
Checkpoint files /app/goldengate/dirchk: created
Process status files /app/goldengate/dirpcs: created
SQL script files /app/goldengate/dirsql: created
Database definitions files /app/goldengate/dirdef: created
Extract data files /app/goldengate/dirdat: created
Temporary files /app/goldengate/dirtmp: created
Stdout files /app/goldengate/dirout: created
===============================================================================
6 編輯GLOBALS 檔案
--輸入如下內容
CHECKPOINTTABLE ggsync.gg_checkpoint_tab
GGSCHEMA ggsync
GGSCI (test1) 2> edit params ./globals
CHECKPOINTTABLE ggsync.gg_checkpoint_tab
GGSCHEMA ggsync
~
~
~
~
~
"./globals" [New] 2L, 57C written
GGSCI (test1) 3>
===============================================================================
7 增加CHECKPOINTTABLE
[oracle@test1 goldengate]:testdb> ./ggsci
Oracle GoldenGate Command Interpreter for Oracle
Version 11.2.1.0.1 OGGCORE_11.2.1.0.1_PLATFORMS_120423.0230_FBO
Linux, x64, 64bit (optimized), Oracle 11g on Apr 23 2012 08:32:14
Copyright (C) 1995, 2012, Oracle and/or its affiliates. All rights reserved.
GGSCI (test1) 1> dblogin userid ggsync,password 123456
Successfully logged into database.
GGSCI (test1) 2> add checkpointtable ggsync.gg_checkpoint_tab
Successfully created checkpoint table ggsync.gg_checkpoint_tab.
GGSCI (test1) 3>
===============================================================================
8 建立MGR 引數檔案並啟動MGR 程式
--輸入如下內容:
port 7809
DYNAMICPORTLIST 7810-7909
PURGEOLDEXTRACTS ./dirdat/*/*,usecheckpoints,minkeepdays 3
autostart er *
autorestart er *,retries 5,waitminutes 7,resetminutes 60
lagreporthours 1
laginfominutes 5
lagcriticalminutes 5
GGSCI (test1) 3> edit param mgr
port 7809
DYNAMICPORTLIST 7810-7909
PURGEOLDEXTRACTS ./dirdat/*/*,usecheckpoints,minkeepdays 3
autostart er *
autorestart er *,retries 5,waitminutes 7,resetminutes 60
lagreporthours 1
laginfominutes 5
lagcriticalminutes 5
~
~
~
"dirprm/mgr.prm" [New] 8L, 222C written
啟動mgr：
GGSCI (test1) 4> start mgr
Manager started.
GGSCI (test1) 5> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
GGSCI (test1) 6>
===============================================================================
9 建立oby 檔案
[oracle@test1 goldengate]:testdb> cd $OGG_HOME
[oracle@test1 goldengate]:testdb> cd direnv/
[oracle@test1 direnv]:testdb> vi testdb.oby
sETENV (ORACLE_HOME = /app/oracle/ora11g)
SETENV (ORACLE_SID=testdb)
SETENV (NLS_LANG="AMERICAN_AMERICA.ZHS16GBK")
userid ggsync, password 123456
~
~
~
"testdb.oby" [New] 4L, 146C written
[oracle@test1 direnv]:testdb>
[oracle@test1 direnv]:testdb>
示例：
SETENV (ORACLE_HOME = "" )
SETENV (ORACLE_SID="")
SETENV (NLS_LANG="AMERICAN_AMERICA.ZHS16GBK")
userid ggsync, password ****
===============================================================================
源庫配 抽取程式：e_testdb 傳輸程式：t_O01FDR
目標庫配 恢復程式： r_testdb
===============================================================================
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
步驟10--14源庫執行：（同步資料庫testdb 的表test1.t1,test1.t2,test2.t1 至O01FDR 的test1.t1,test1.t2,test2.t1）
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
10 建立抽取檔案存放目錄
cd $OGG_HOME/dirdat
mkdir e_testdb
[oracle@test1 ~]:testdb> cd $OGG_HOME/dirdat
[oracle@test1 dirdat]:testdb> ll
total 4
drwxr-xr-x. 2 oracle oinstall 4096 Oct 30 12:32 e_testdb
[oracle@test1 dirdat]:testdb>
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
11 建立抽取程式
[oracle@test1 goldengate]:testdb> ./ggsci
Oracle GoldenGate Command Interpreter for Oracle
Version 11.2.1.0.1 OGGCORE_11.2.1.0.1_PLATFORMS_120423.0230_FBO
Linux, x64, 64bit (optimized), Oracle 11g on Apr 23 2012 08:32:14
Copyright (C) 1995, 2012, Oracle and/or its affiliates. All rights reserved.
GGSCI (test1) 1> add extract e_testdb, tranlog, begin now
EXTRACT added.
GGSCI (test1) 2> add exttrail ./dirdat/e_testdb/ea, extract e_testdb, megabytes 500
EXTTRAIL added.
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
12 編輯抽取程式e_testdb
GGSCI (test1) 12> edit param e_testdb
新增內容：
EXTRACT e_testdb
obey ./direnv/testdb.oby
--tranlogoptions excludeuser ggsync
EXTTRAIL ./dirdat/e_testdb/ea
DISCARDFILE ./dirrpt/e_testdb.dsc,append,megabytes 2000
DISCARDROLLOVER AT 05:30 ON Friday
REPORTROLLOVER AT 05:30 ON Friday
REPORTCOUNT EVERY 10 MINUTES, RATE
WARNLONGTRANS 4H, CHECKINTERVAL 30m
numfiles 5000
DYNAMICRESOLUTION
WILDCARDRESOLVE DYNAMIC
-- gg sync
table ggsync.gg_sync_testdb;
table test1.t1;
table test1.t2;
table test2.t1;
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
13 新增附加日誌
cd $OGG_HOME
./ggsci
GGSCI (test1) 1>dblogin userid ggsync,password 123456
GGSCI (test1) 1>add trandata ggsync.gg_sync_testdb
GGSCI (test1) 1>add trandata test1.t1
GGSCI (test1) 1>add trandata test1.t2
GGSCI (test1) 1>add trandata test2.t1
此步驟容易出錯，一定要測試新增成功，測試表要具有sequence，主鍵索引之類的。
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
14 配置傳輸程式
目標庫DB_NAME 為：O01FDR
建立恢復檔案存放目錄：
mkdir $OGG_HOME/irdat/r_testdb
增加傳輸程式：t_O01FDR
cd $OGG_HOME
./ggsci
GGSCI (test1) 1>add extract t_O01FDR,exttrailsource ./dirdat/e_testdb/ea, begin now
GGSCI (test1) 1>add rmttrail ./dirdat/r_testdb/ra, extract t_O01FDR, MEGABYTES 500
編輯傳輸程式：t_O01FDR
GGSCI (test1) 15> edit param t_O01FDR
增加如下內容：
EXTRACT t_O01FDR
obey ./direnv/testdb.oby
passthru
RMTHOST 192.168.9.143, MGRPORT 7809, compress
RMTTRAIL ./dirdat/r_testdb/ra
DISCARDFILE ./dirrpt/t_O01FDR.dsc,append,megabytes 2000
DISCARDROLLOVER AT 05:30 ON Friday
REPORTROLLOVER AT 05:30 ON Friday
REPORTCOUNT EVERY 10 MINUTES, RATE
-- gg sync
table ggsync.gg_sync_testdb;
table test1.t1;
table test1.t2;
table test2.t1;
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
-------------------------------------------------------------------------------
步驟15--17 目標庫執行 （配置恢復程式：r_testdb）
-------------------------------------------------------------------------------
15 建立恢復檔案存放目錄
cd $OGG_HOME/dirdat
mkdir r_testdb
-------------------------------------------------------------------------------
16 新增恢復程式：r_testdb
cd $OGG_HOME
./ggsic
GGSCI (test1) 1>add replicat r_testdb, EXTTRAIL ./dirdat/r_testdb/ra
-------------------------------------------------------------------------------
17 編輯恢復程式：r_testdb
cd $OGG_HOME
./ggsic
GGSCI (test1) 1>edit param r_testdb
新增如下內容：
replicat r_testdb
obey ./direnv/O01FDR.oby
--HANDLECOLLISIONS
REPERROR DEFAULT, ABEND
DISCARDFILE ./dirrpt/r_testdb.dsc,append,megabytes 2000
DISCARDROLLOVER AT 05:30 ON Friday
REPORTROLLOVER AT 05:30 ON Friday
REPORTCOUNT EVERY 10 MINUTES, RATE
numfiles 5000
CHECKPOINTSECS 30
GROUPTRANSOPS 10000
MAXTRANSOPS 30000
ASSUMETARGETDEFS
dynamicresolution
WILDCARDRESOLVE DYNAMIC
ALLOWDUPTARGETMAP
-- gg sync
map ggsync.gg_sync_testdb, target ggsync.gg_sync_testdb;
map test1.t1, target test1.t1;
map test1.t2, target test1.t2;
map test2.t1, target test2.t1;
-------------------------------------------------------------------------------
&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&
1.初始化所有表資料
從源庫複製需要同步的表至目標庫
使用dblink方法同步：
（目標庫執行）
create public database link dblink1
connect to GGSYNC identified by "123456"
using '(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.9.142)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = testdb)
)
)';
insert into GGSYNC.GG_SYNC_TESTDB select * from GGSYNC.GG_SYNC_TESTDB@dblink1;
create public database link dblink2
connect to test1 identified by "123456"
using '(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.9.142)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = testdb)
)
)';
create public database link dblink3
connect to test2 identified by "123456"
using '(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.9.142)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = testdb)
)
)';
create table test1.t1 as select * from test1.t1@dblink2;
create table test1.t2 as select * from test1.t1@dblink2;
create table test2.t1 as select * from test2.t1@dblink3;
create table test2.t2 as select * from test2.t1@dblink3;
2.啟用引數HANDLECOLLISIONS
去掉HANDLECOLLISIONS 前的，”--”
（如果是重新同步個別表，則在map 後面新增HANDLECOLLISIONS，
例如：map test1.t1, target test.t1 HANDLECOLLISIONS;)
3.啟動恢復程式r_testdb
4.等待恢復程式同步完成之後，停止恢復程式r_testdb
5.禁用HANDLECOLLISIONS
6.啟動恢復程式
GGSCI (test2) 7> start r_testdb
7. 最後狀態
源庫：
GGSCI (test1) 16> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING E_TESTDB 00:00:00 00:00:04
EXTRACT RUNNING T_O01FDR 00:00:00 00:00:03
目標庫：
GGSCI (test2) 12> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
REPLICAT RUNNING R_TESTDB 00:00:00 00:00:21
全部屬於running狀態
並且測試資料可以同步過去，增加內容後，記得commit，才可以到目標庫查詢到相應的行
&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&
@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
配置監控庫：192.168.9.144
@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
源庫ip：192.168.9.142 db_name:testdb
目標庫ip：192.168.9.143 db_name:o01fdr
監控庫ip：192.168.9.144 db_name:dbadb
===============================================================================
1-9步源庫和目標資料庫做一樣的操作
===============================================================================
1 建立目錄並安裝
[oracle@test1 ~]:testdb> cd /tmp
[oracle@test1 tmp]:testdb> ll
total 87124
drwxr-x---. 2 oracle oinstall 4096 Oct 28 13:27 hsperfdata_oracle
drwx------. 2 root root 4096 Oct 28 11:24 keyring-uaxFJh
-rw-r--r--. 1 root root 89186858 Oct 28 10:08 ogg112101_fbo_ggs_Linux_x64_ora11g_64bit.zip
drwx------. 2 root root 4096 Oct 28 11:28 orbit-root
drwx------. 2 root root 4096 Oct 28 11:24 pulse-oZDqCnH2yFgj
drwx------. 2 oracle oinstall 4096 Aug 7 22:41 pulse-Up2S1oVzRfXF
drwx------. 2 root root 4096 Oct 28 11:24 ssh-WSdzia1676
[oracle@test1 tmp]:testdb> mkdir -p /app/goldengate
[oracle@test1 tmp]:testdb>
[oracle@test1 tmp]:testdb> cp ogg112101_fbo_ggs_Linux_x64_ora11g_64bit.zip /app/goldengate/
[oracle@test1 tmp]:testdb>
[oracle@test1 tmp]:testdb>
[oracle@test1 tmp]:testdb> cd /app/goldengate/
[oracle@test1 goldengate]:testdb> ll
total 87100
-rw-r--r--. 1 oracle oinstall 89186858 Oct 28 13:44 ogg112101_fbo_ggs_Linux_x64_ora11g_64bit.zip
[oracle@test1 goldengate]:testdb> unzip ogg112101_fbo_ggs_Linux_x64_ora11g_64bit.zip
Archive: ogg112101_fbo_ggs_Linux_x64_ora11g_64bit.zip
inflating: fbo_ggs_Linux_x64_ora11g_64bit.tar
inflating: OGG_WinUnix_Rel_Notes_11.2.1.0.1.pdf
inflating: Oracle GoldenGate 11.2.1.0.1 README.txt
inflating: Oracle GoldenGate 11.2.1.0.1 README.doc
[oracle@test1 goldengate]:testdb> ll
total 310632
-rw-rw-r--. 1 oracle oinstall 228556800 Apr 23 2012 fbo_ggs_Linux_x64_ora11g_64bit.tar
-rw-r--r--. 1 oracle oinstall 89186858 Oct 28 13:44 ogg112101_fbo_ggs_Linux_x64_ora11g_64bit.zip
-rwxrwxrwx. 1 oracle oinstall 220546 May 2 2012 OGG_WinUnix_Rel_Notes_11.2.1.0.1.pdf
-rwxrwxrwx. 1 oracle oinstall 93696 May 2 2012 Oracle GoldenGate 11.2.1.0.1 README.doc
-rwxrwxrwx. 1 oracle oinstall 24390 May 2 2012 Oracle GoldenGate 11.2.1.0.1 README.txt
[oracle@test1 goldengate]:testdb> tar -xvf fbo_ggs_Linux_x64_ora11g_64bit.tar
UserExitExamples/
UserExitExamples/ExitDemo_more_recs/
UserExitExamples/ExitDemo_more_recs/Makefile_more_recs.HPUX
UserExitExamples/ExitDemo_more_recs/Makefile_more_recs.SOLARIS
UserExitExamples/ExitDemo_more_recs/Makefile_more_recs.LINUX
UserExitExamples/ExitDemo_more_recs/Makefile_more_recs.AIX
UserExitExamples/ExitDemo_more_recs/exitdemo_more_recs.vcproj
UserExitExamples/ExitDemo_more_recs/exitdemo_more_recs.c
UserExitExamples/ExitDemo_more_recs/readme.txt
UserExitExamples/ExitDemo_passthru/
UserExitExamples/ExitDemo_passthru/exitdemo_passthru.c
UserExitExamples/ExitDemo_passthru/exitdemopassthru.vcproj
UserExitExamples/ExitDemo_passthru/Makefile_passthru.HPUX
UserExitExamples/ExitDemo_passthru/Makefile_passthru.AIX
UserExitExamples/ExitDemo_passthru/Makefile_passthru.HP_OSS
UserExitExamples/ExitDemo_passthru/Ma
===============================================================================
2 配置環境變數：
源庫：
[oracle@test1 ~]:testdb> cat prof_testdb
# .bash_profile
# Get the aliases and functions
if [ -f ~/.bashrc ]; then
. ~/.bashrc
fi
# User specific environment and startup programs
export ORACLE_BASE=/app/oracle
export ORACLE_HOME=$ORACLE_BASE/ora11g
export GRID_BASE=/app/grid
export GRID_HOME=$GRID_BASE/grid11g
export ORACLE_SID=testdb
export TNS_ADMIN=$ORACLE_HOME/network/admin
export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:$ORACLE_HOME/lib
export PATH=$PATH:$HOME/bin:$ORACLE_HOME/bin:$GRID_HOME/bin:/usr/sbin:/usr/local/bin:$ORACLE_HOME/OPatch
export EDITOR=vi
export NLS_LANG=AMERICAN_AMERICA.ZHS16GBK
export NLS_DATE_FORMAT="yyyy-mm-dd hh24:mi:ss"
export OGG_HOME=/app/goldengate
export LD_LIBRARY_PATH=$OGG_HOME:$LD_LIBRARY_PATH
export PATH=$OGG_HOME:$PATH
export PS1="[\u@\h \W]:$ORACLE_SID> "
umask 022
[oracle@test1 ~]:testdb>
目標庫：
[oracle@test2 ~]:O01FDR> cat prof_O01FDR
# .bash_profile
# Get the aliases and functions
if [ -f ~/.bashrc ]; then
. ~/.bashrc
fi
# User specific environment and startup programs
export ORACLE_BASE=/app/oracle
export ORACLE_HOME=$ORACLE_BASE/ora11g
export GRID_BASE=/app/grid
export GRID_HOME=$GRID_BASE/grid11g
export ORACLE_SID=O01FDR
export TNS_ADMIN=$ORACLE_HOME/network/admin
export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:$ORACLE_HOME/lib
export PATH=$PATH:$HOME/bin:$ORACLE_HOME/bin:$GRID_HOME/bin:/usr/sbin:/usr/local/bin:$ORACLE_HOME/OPatch
export EDITOR=vi
export NLS_LANG=AMERICAN_AMERICA.ZHS16GBK
export NLS_DATE_FORMAT="yyyy-mm-dd hh24:mi:ss"
export OGG_HOME=/app/goldengate
export LD_LIBRARY_PATH=$OGG_HOME:$LD_LIBRARY_PATH
export PATH=$OGG_HOME:$PATH
export PS1="[\u@\h \W]:$ORACLE_SID> "
umask 022
[oracle@test2 ~]:O01FDR>
===============================================================================
3 開啟supplementary log
[oracle@test1 goldengate]:testdb> sqlplus "/as sysdba"
SQL*Plus: Release 11.2.0.4.0 Production on Tue Oct 28 13:50:25 2014
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> select force_logging,supplemental_log_data_min from v$database;
FOR SUPPLEME
--- --------
NO NO
SQL> alter database force logging;
Database altered.
SQL> alter database add supplemental log data;
Database altered.
SQL> select force_logging, supplemental_log_data_min from v$database;
FOR SUPPLEME
--- --------
YES YES
SQL>
===============================================================================
4 建立表空間
&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&
CREATE TABLESPACE DBADATATBS DATAFILE
'/app/oracle/oradata/testdb/dbadatatbs01.dbf' SIZE 2048M AUTOEXTEND OFF
LOGGING
ONLINE
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
BLOCKSIZE 8K
SEGMENT SPACE MANAGEMENT AUTO
FLASHBACK ON;
&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&
資料庫訪問使用者
&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&
CREATE USER GGSYNC
IDENTIFIED BY 123456
DEFAULT TABLESPACE DBADATATBS
TEMPORARY TABLESPACE TEMP
PROFILE DEFAULT
ACCOUNT UNLOCK;
&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&
賦給使用者許可權
&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&
---roles
GRANT CONNECT TO GGSYNC;
GRANT RESOURCE TO GGSYNC;
ALTER USER GGSYNC DEFAULT ROLE ALL;
---privileges
GRANT ALTER SESSION TO GGSYNC;
GRANT CREATE SESSION TO GGSYNC;
GRANT SELECT ANY TABLE TO GGSYNC;
GRANT SELECT ANY DICTIONARY TO GGSYNC;
GRANT CREATE TABLE TO GGSYNC;
GRANT UNLIMITED TABLESPACE TO GGSYNC;
GRANT FLASHBACK ANY TABLE TO GGSYNC;
GRANT INSERT ANY TABLE TO GGSYNC;
GRANT DELETE ANY TABLE TO GGSYNC;
GRANT UPDATE ANY TABLE TO GGSYNC;
GRANT ALTER ANY TABLE TO GGSYNC;
&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&
心跳錶
&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&
CREATE TABLE GGSYNC.GG_SYNC_TESTDB
(
SOURCE_DB VARCHAR2 (20 BYTE),
TARGET_DB VARCHAR2 (20 BYTE),
EXT_NAME VARCHAR2 (20 BYTE),
DPE_NAME VARCHAR2 (20 BYTE),
REP_NAME VARCHAR2 (20 BYTE),
CHECK_FLAG VARCHAR2 (20 BYTE),
CHECK_TIME VARCHAR2 (30 BYTE) DEFAULT SYSDATE
)
TABLESPACE DBADATATBS;
*************************************************
CREATE UNIQUE INDEX GGSYNC.PK_GG_SYNC_TESTDB
ON GGSYNC.GG_SYNC_TESTDB (SOURCE_DB, TARGET_DB);
*************************************************
ALTER TABLE GGSYNC.GG_SYNC_TESTDB ADD (
CONSTRAINT PK_GG_SYNC_TESTDB
PRIMARY KEY
(SOURCE_DB,TARGET_DB)
USING INDEX GGSYNC.PK_GG_SYNC_TESTDB);
***************************************************
GRANT SELECT,
INSERT,
UPDATE,
DELETE
ON GGSYNC.GG_SYNC_TESTDB
TO DBMON;
&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&
建立心跳錶更新儲存過程
&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&
CREATE OR REPLACE PROCEDURE GGSYNC.PROC_GG_SYNC_UPDATE
AS
v_time VARCHAR2 (30 BYTE);
v_count NUMBER;
v_table VARCHAR2 (50 BYTE);
v_sql VARCHAR2 (1000 BYTE);
v_eor VARCHAR2 (200 BYTE);
v_dbname VARCHAR2 (30 BYTE);
BEGIN
SELECT TO_CHAR (SYSDATE, 'YYYY-MM-DD HH24:MI:SS') INTO v_time FROM
DUAL;
SELECT NAME
INTO V_DBNAME
FROM V$DATABASE
WHERE ROWNUM = 1;
v_table := 'ggsync.gg_sync_' || v_dbname;
v_sql :=
'select count(*) from '
|| v_table
|| ' where source_db=''ALL'' and target_db=''ALL''';
EXECUTE IMMEDIATE v_sql INTO v_count;
IF v_count = 0
THEN
BEGIN
v_sql :=
'insert into '
|| v_table
|| ' values(''ALL'',''ALL'',''E_ALL'',''T_ALL'',''R_ALL'',''Y'','''
|| v_time
|| ''')';
EXECUTE IMMEDIATE v_sql;
COMMIT;
END;
ELSE
BEGIN
v_sql :=
'update '
|| v_table
|| ' set check_time='''
|| v_time
|| ''' where source_db=''ALL'' and target_db=''ALL''';
EXECUTE IMMEDIATE v_sql;
COMMIT;
END;
END IF;
EXCEPTION
WHEN OTHERS
THEN
ROLLBACK;
NULL;
END;
/
&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&
建立心跳錶自動更新JOB（GGSYNC 下建立）
&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&
DECLARE
X NUMBER;
BEGIN
SYS.DBMS_JOB.SUBMIT (job => X,
what => 'begin ggsync.proc_gg_sync_update; end;',
next_date => SYSDATE,
interval => 'sysdate+1/1440',
no_parse => FALSE);
SYS.DBMS_OUTPUT.PUT_LINE ('Job Number is: ' || TO_CHAR (x));
COMMIT;
END;
/
&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&
===============================================================================
5 建goldengate目錄
[root@test1 testdb]# su - oracle
[oracle@test1 ~]$ . prof_testdb
[oracle@test1 ~]:testdb> cd $OGG_HOME
[oracle@test1 goldengate]:testdb> ll
total 513484
-rw-r-----. 1 oracle oinstall 426 Oct 15 2010 bcpfmt.tpl
-rw-r-----. 1 oracle oinstall 1725 Oct 15 2010 bcrypt.txt
drwxr-x---. 2 oracle oinstall 4096 Apr 23 2012 cfg
-rw-r-----. 1 oracle oinstall 1285 Feb 29 2012 chkpt_ora_create.sql
[oracle@test1 goldengate]:testdb> mkdir direnv
[oracle@test1 goldengate]:testdb> ./ggsci
Oracle GoldenGate Command Interpreter for Oracle
Version 11.2.1.0.1 OGGCORE_11.2.1.0.1_PLATFORMS_120423.0230_FBO
Linux, x64, 64bit (optimized), Oracle 11g on Apr 23 2012 08:32:14
Copyright (C) 1995, 2012, Oracle and/or its affiliates. All rights reserved.
GGSCI (test1) 1> create subdirs
Creating subdirectories under current directory /app/goldengate
Parameter files /app/goldengate/dirprm: already exists
Report files /app/goldengate/dirrpt: created
Checkpoint files /app/goldengate/dirchk: created
Process status files /app/goldengate/dirpcs: created
SQL script files /app/goldengate/dirsql: created
Database definitions files /app/goldengate/dirdef: created
Extract data files /app/goldengate/dirdat: created
Temporary files /app/goldengate/dirtmp: created
Stdout files /app/goldengate/dirout: created
===============================================================================
6 編輯GLOBALS 檔案
--輸入如下內容
CHECKPOINTTABLE ggsync.gg_checkpoint_tab
GGSCHEMA ggsync
GGSCI (test1) 2> edit params ./globals
CHECKPOINTTABLE ggsync.gg_checkpoint_tab
GGSCHEMA ggsync
~
~
~
~
~
"./globals" [New] 2L, 57C written
GGSCI (test1) 3>
===============================================================================
7 增加CHECKPOINTTABLE
[oracle@test1 goldengate]:testdb> ./ggsci
Oracle GoldenGate Command Interpreter for Oracle
Version 11.2.1.0.1 OGGCORE_11.2.1.0.1_PLATFORMS_120423.0230_FBO
Linux, x64, 64bit (optimized), Oracle 11g on Apr 23 2012 08:32:14
Copyright (C) 1995, 2012, Oracle and/or its affiliates. All rights reserved.
GGSCI (test1) 1> dblogin userid ggsync,password 123456
Successfully logged into database.
GGSCI (test1) 2> add checkpointtable ggsync.gg_checkpoint_tab
Successfully created checkpoint table ggsync.gg_checkpoint_tab.
GGSCI (test1) 3>
===============================================================================
8 建立MGR 引數檔案並啟動MGR 程式
--輸入如下內容:
port 7809
DYNAMICPORTLIST 7810-7909
PURGEOLDEXTRACTS ./dirdat/*/*,usecheckpoints,minkeepdays 3
autostart er *
autorestart er *,retries 5,waitminutes 7,resetminutes 60
lagreporthours 1
laginfominutes 5
lagcriticalminutes 5
GGSCI (test1) 3> edit param mgr
port 7809
DYNAMICPORTLIST 7810-7909
PURGEOLDEXTRACTS ./dirdat/*/*,usecheckpoints,minkeepdays 3
autostart er *
autorestart er *,retries 5,waitminutes 7,resetminutes 60
lagreporthours 1
laginfominutes 5
lagcriticalminutes 5
~
~
~
"dirprm/mgr.prm" [New] 8L, 222C written
啟動mgr：
GGSCI (test1) 4> start mgr
Manager started.
GGSCI (test1) 5> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
GGSCI (test1) 6>
===============================================================================
9 建立oby 檔案
[oracle@test1 goldengate]:testdb> cd $OGG_HOME
[oracle@test1 goldengate]:testdb> cd direnv/
[oracle@test1 direnv]:testdb> vi testdb.oby
sETENV (ORACLE_HOME = /app/oracle/ora11g)
SETENV (ORACLE_SID=testdb)
SETENV (NLS_LANG="AMERICAN_AMERICA.ZHS16GBK")
userid ggsync, password 123456
~
~
~
"testdb.oby" [New] 4L, 146C written
[oracle@test1 direnv]:testdb>
[oracle@test1 direnv]:testdb>
示例：
SETENV (ORACLE_HOME = "
SETENV (ORACLE_SID="
SETENV (NLS_LANG="AMERICAN_AMERICA.ZHS16GBK")
userid ggsync, password ****
===============================================================================
源庫配 抽取程式：e_testdb 傳輸程式：t_O01FDR
目標庫配 恢復程式： r_testdb
===============================================================================
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
步驟10--14源庫執行：（同步資料庫testdb 的表test1.t1,test1.t2,test2.t1 至O01FDR 的test1.t1,test1.t2,test2.t1）
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
10 建立抽取檔案存放目錄
cd $OGG_HOME/dirdat
mkdir e_testdb
[oracle@test1 ~]:testdb> cd $OGG_HOME/dirdat
[oracle@test1 dirdat]:testdb> ll
total 4
drwxr-xr-x. 2 oracle oinstall 4096 Oct 30 12:32 e_testdb
[oracle@test1 dirdat]:testdb>
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
11 建立抽取程式
[oracle@test1 goldengate]:testdb> ./ggsci
Oracle GoldenGate Command Interpreter for Oracle
Version 11.2.1.0.1 OGGCORE_11.2.1.0.1_PLATFORMS_120423.0230_FBO
Linux, x64, 64bit (optimized), Oracle 11g on Apr 23 2012 08:32:14
Copyright (C) 1995, 2012, Oracle and/or its affiliates. All rights reserved.
GGSCI (test1) 1> add extract e_testdb, tranlog, begin now
EXTRACT added.
GGSCI (test1) 2> add exttrail ./dirdat/e_testdb/ea, extract e_testdb, megabytes 500
EXTTRAIL added.
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
12 編輯抽取程式e_testdb
GGSCI (test1) 12> edit param e_testdb
新增內容：
EXTRACT e_testdb
obey ./direnv/testdb.oby
--tranlogoptions excludeuser ggsync
EXTTRAIL ./dirdat/e_testdb/ea
DISCARDFILE ./dirrpt/e_testdb.dsc,append,megabytes 2000
DISCARDROLLOVER AT 05:30 ON Friday
REPORTROLLOVER AT 05:30 ON Friday
REPORTCOUNT EVERY 10 MINUTES, RATE
WARNLONGTRANS 4H, CHECKINTERVAL 30m
numfiles 5000
DYNAMICRESOLUTION
WILDCARDRESOLVE DYNAMIC
-- gg sync
table ggsync.gg_sync_testdb;
table test1.t1;
table test1.t2;
table test2.t1;
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
13 新增附加日誌
cd $OGG_HOME
./ggsci
GGSCI (test1) 1>dblogin userid ggsync,password 123456
GGSCI (test1) 1>add trandata ggsync.gg_sync_testdb
GGSCI (test1) 1>add trandata test1.t1
GGSCI (test1) 1>add trandata test1.t2
GGSCI (test1) 1>add trandata test2.t1
此步驟容易出錯，一定要測試新增成功，測試表要具有sequence，主鍵索引之類的。
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
14 配置傳輸程式
目標庫DB_NAME 為：O01FDR
建立恢復檔案存放目錄：
mkdir $OGG_HOME/irdat/r_testdb
增加傳輸程式：t_O01FDR
cd $OGG_HOME
./ggsci
GGSCI (test1) 1>add extract t_O01FDR,exttrailsource ./dirdat/e_testdb/ea, begin now
GGSCI (test1) 1>add rmttrail ./dirdat/r_testdb/ra, extract t_O01FDR, MEGABYTES 500
編輯傳輸程式：t_O01FDR
GGSCI (test1) 15> edit param t_O01FDR
增加如下內容：
EXTRACT t_O01FDR
obey ./direnv/testdb.oby
passthru
RMTHOST 192.168.9.143, MGRPORT 7809, compress
RMTTRAIL ./dirdat/r_testdb/ra
DISCARDFILE ./dirrpt/t_O01FDR.dsc,append,megabytes 2000
DISCARDROLLOVER AT 05:30 ON Friday
REPORTROLLOVER AT 05:30 ON Friday
REPORTCOUNT EVERY 10 MINUTES, RATE
-- gg sync
table ggsync.gg_sync_testdb;
table test1.t1;
table test1.t2;
table test2.t1;
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
-------------------------------------------------------------------------------
步驟15--17 目標庫執行 （配置恢復程式：r_testdb）
-------------------------------------------------------------------------------
15 建立恢復檔案存放目錄
cd $OGG_HOME/dirdat
mkdir r_testdb
-------------------------------------------------------------------------------
16 新增恢復程式：r_testdb
cd $OGG_HOME
./ggsic
GGSCI (test1) 1>add replicat r_testdb, EXTTRAIL ./dirdat/r_testdb/ra
-------------------------------------------------------------------------------
17 編輯恢復程式：r_testdb
cd $OGG_HOME
./ggsic
GGSCI (test1) 1>edit param r_testdb
新增如下內容：
replicat r_testdb
obey ./direnv/O01FDR.oby
--HANDLECOLLISIONS
REPERROR DEFAULT, ABEND
DISCARDFILE ./dirrpt/r_testdb.dsc,append,megabytes 2000
DISCARDROLLOVER AT 05:30 ON Friday
REPORTROLLOVER AT 05:30 ON Friday
REPORTCOUNT EVERY 10 MINUTES, RATE
numfiles 5000
CHECKPOINTSECS 30
GROUPTRANSOPS 10000
MAXTRANSOPS 30000
ASSUMETARGETDEFS
dynamicresolution
WILDCARDRESOLVE DYNAMIC
ALLOWDUPTARGETMAP
-- gg sync
map ggsync.gg_sync_testdb, target ggsync.gg_sync_testdb;
map test1.t1, target test1.t1;
map test1.t2, target test1.t2;
map test2.t1, target test2.t1;
-------------------------------------------------------------------------------
&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&
1.初始化所有表資料
從源庫複製需要同步的表至目標庫
使用dblink方法同步：
（目標庫執行）
create public database link dblink1
connect to GGSYNC identified by "123456"
using '(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.9.142)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = testdb)
)
)';
insert into GGSYNC.GG_SYNC_TESTDB select * from GGSYNC.GG_SYNC_TESTDB@dblink1;
create public database link dblink2
connect to test1 identified by "123456"
using '(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.9.142)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = testdb)
)
)';
create public database link dblink3
connect to test2 identified by "123456"
using '(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.9.142)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = testdb)
)
)';
create table test1.t1 as select * from test1.t1@dblink2;
create table test1.t2 as select * from test1.t1@dblink2;
create table test2.t1 as select * from test2.t1@dblink3;
create table test2.t2 as select * from test2.t1@dblink3;
2.啟用引數HANDLECOLLISIONS
去掉HANDLECOLLISIONS 前的，”--”
（如果是重新同步個別表，則在map 後面新增HANDLECOLLISIONS，
例如：map test1.t1, target test.t1 HANDLECOLLISIONS;)
3.啟動恢復程式r_testdb
4.等待恢復程式同步完成之後，停止恢復程式r_testdb
5.禁用HANDLECOLLISIONS
6.啟動恢復程式
GGSCI (test2) 7> start r_testdb
7. 最後狀態
源庫：
GGSCI (test1) 16> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING E_TESTDB 00:00:00 00:00:04
EXTRACT RUNNING T_O01FDR 00:00:00 00:00:03
目標庫：
GGSCI (test2) 12> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
REPLICAT RUNNING R_TESTDB 00:00:00 00:00:21
全部屬於running狀態
並且測試資料可以同步過去，增加內容後，記得commit，才可以到目標庫查詢到相應的行
&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&
@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
配置監控庫：192.168.9.144
@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/29500582/viewspace-1314717/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- GoldenGate的基本配置流程Go
- GoldenGate Logdump基本使用Go
- GoldenGate基本原理Go
- GoldenGate MSSQL Oracle基本流程GoSQLOracle
- oracle goldengate 配置OracleGo
- 【GoldenGate】Oracle GoldenGate(三) DDL同步配置GoOracle
- GoldenGate 基本引數含義Go
- 【GoldenGate】Oracle GoldenGate(二) 雙向同步配置GoOracle
- goldengate ddl 配置說明Go
- Oracle goldengate 安裝配置OracleGo
- oracle GoldenGate Veridata配置OracleGo
- goldengate配置DDL複製Go
- 安裝並配置goldengateGo
- GoldenGate for win安裝配置Go
- GoldenGate雙向同步配置Go
- GoldenGate配置（三）之DDL複製配置Go
- 【GoldenGate】Oracle GoldenGate Veridata 安裝配置與應用GoOracle
- GoldenGate配置（二）之雙向複製配置Go
- GoldenGate配置（一）之單向複製配置Go
- Oracle GoldenGate Director配置手冊OracleGo
- goldengate 單向複製配置Go
- mysql goldengate同步 簡單配置MySqlGo
- 【GoldenGate】Oracle GoldenGate(一) 安裝與DML單向同步配置GoOracle
- NGINX基本配置Nginx
- MySQL基本配置MySql
- git基本配置Git
- oracle資料庫配置goldengate同步Oracle資料庫Go
- GOLDENGATE安裝和配置手冊Go
- oracle goldengate 雙向複製配置OracleGo
- goldengate單向複製的配置Go
- oracle goldengate 配置DML&DDL實驗OracleGo
- GoldenGate的安裝、配置與測試Go
- 配置Oracle GoldenGate for DB2(雙向)OracleGoDB2
- 配置GoldenGate增量資料同步機制Go
- 解除安裝goldengate相關配置資訊Go
- nginx基本配置使用Nginx
- RIP的基本配置
- Oracle GoldenGate 學習教程二、配置和使用OracleGo