實驗環境：
源端：192.168.1.30，Oracle 10.2.0.5 單實例
目標端：192.168.1.31，Oracle 10.2.0.5 單實例

• 1.模擬源數據庫業務持續運行
• 2.配置OGG前期準備
• 3.配置OGG單向復制

1.模擬源數據庫業務持續運行

OGG的單向配置比較簡單，但實際生產過程很多業務要求不間斷運行，
所以我創建了2張模擬業務表，簡單模擬在業務不間斷運行場景下OGG的配置。

1.1 創建模擬的業務用戶

首先我創建業務用戶jy，并指定密碼，賦予基本業務用戶的角色權限。

--user create user jy identified by jy default tablespace users; --grant grant resource, connect to jy;

1.2 在業務用戶下創建表和存儲過程

連接到業務用戶jy下，創建1個序列，2張表（其中一張表有主鍵）：

--connect conn jy/jy --sequence create sequence s1; --tables --table t_second 無主鍵 create table t_second as select s1.nextval id, to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') time from dual; --table t_second_p 有主鍵 create table t_second_p as select s1.nextval id, to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') time from dual; alter table t_second_p add constraint pk_t_second_p primary key(id);

創建存儲過程,功能是：每秒向2張業務表分別插入1條數據；
用來模擬持續運行的業務；

--procedure create or replace procedure p1 is begin for i in 1..86400 loop insert into t_second select s1.nextval, to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual; commit; insert into t_second_p select s1.nextval, to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual; commit; dbms_lock.sleep(1); end loop; end; /

執行存儲過程，相當于模擬業務正式啟動：

--execute [oracle@oradb30 scripts]$ cat business.sh #!/bin/bashsqlplus jy/jy <<EOF exec p1; EOF [oracle@oradb30 scripts]$ nohup sh business.sh &

2.配置OGG前期準備

2.1 創建源端和目標端OGG的管理員用戶

--源數據庫端： create user ggs_admin identified by ggs_admin; grant dba to ggs_admin;--目標數據庫端： create user ggt_admin identified by ggt_admin; grant dba to ggt_admin;

2.2 配置源數據庫端的附加日志

--查看數據庫附加日志開啟狀態： SQL> select SUPPLEMENTAL_LOG_DATA_MIN, SUPPLEMENTAL_LOG_DATA_PK, SUPPLEMENTAL_LOG_DATA_UI from v$database; --數據庫級別開啟最小附加日志： SQL> alter database add supplemental log data; --表級別開啟詳細附加日志： GGSCI (oradb30) 6> dblogin userid ggs_admin@ora10, password ggs_admin Successfully logged into database. GGSCI (oradb30) 7> add trandata jy.t_second 2017-01-13 23:37:38 WARNING OGG-00869 No unique key is defined for table 'T_SECOND'. All viable columns will be used to represent the key, but may not guarantee uniqueness. KEYCOLS may be used to define the key. Logging of supplemental redo data enabled for table JY.T_SECOND. GGSCI (oradb30) 8> add trandata jy.t_second_p Logging of supplemental redo data enabled for table JY.T_SECOND_P. GGSCI (oradb30) 9>

2.3 配置源端和目標端的OGG所需進程

配置進程（組）名沒有具體的規范，我這里參考曉明在書中所用的組命名方式：
對于復雜環境，第一位字母，local、immdiate、remote 分別對應 l、i、r；
后面根據進程屬性屬于extract、datapump、replicat 而分別對應 x、p、r；

源端：
源端在上篇已經配置好Manager進程。
接下來繼續配置源端的extract進程：
GGSCI (oradb30) 3> edit params lxjy1

--Local Extract lxjy1 --Author: Alfred Zhao -- EXTRACT lxjy1 SETENV(NLS_LANG=american_america.ZHS16GBK) SETENV(ORACLE_SID=ora10) USERID ggs_admin, PASSWORD ggs_admin EXTTRAIL ./dirdat/sa TABLE JY.T_SECOND; TABLE JY.T_SECOND_P;

添加lxjy1進程（local extract）：

GGSCI (oradb30) 1> add extract lxjy1, tranlog, begin now, threads 1 EXTRACT added. GGSCI (oradb30) 2> add exttrail ./dirdat/sa, extract lxjy1, megabytes 50 EXTTRAIL added.

配置源端的datapump進程：

--Local datapump lpjy1 --Author: Alfred Zhao -- EXTRACT lpjy1 PASSTHRU RMTHOST 192.168.1.31, MGRPORT 7777 RMTTRAIL ./dirdat/ta TABLE JY.T_SECOND; TABLE JY.T_SECOND_P;

添加lpjy1進程（本地datapump進程）：

GGSCI (oradb30) 2> add extract lpjy1, exttrailsource ./dirdat/sa, begin now EXTRACT added. GGSCI (oradb30) 17> ADD RMTTRAIL ./dirdat/ta, EXTRACT LPJY1, MEGABYTES 50 RMTTRAIL added.

查看目前源端進程狀態：

GGSCI (oradb30) 7> info allProgram Status Group Lag at Chkpt Time Since ChkptMANAGER RUNNING EXTRACT STOPPED LPJY1 00:00:00 00:07:01 EXTRACT STOPPED LXJY1 00:00:00 00:18:55

可以看到Manager進程啟動，LXJY1和LPJY1沒有啟動。
目標端：
參考《OGG學習筆記01-基礎概述》中安裝配置，對目標端也配置Manager進程。
配置checkpointtable：

GGSCI (oradb31) 1> view params ./GLOBALS checkpointtable ggt_admin.chkpt GGSCI (oradb31) 2> dblogin userid ggt_admin@ora10dg, password ggt_admin Successfully logged into database. GGSCI (oradb31) 3> add checkpointtable No checkpoint table specified, using GLOBALS specification (ggt_admin.chkpt)... Successfully created checkpoint table ggt_admin.chkpt.

然后繼續配置replicat進程：

--replicat rjy1 --Author: Alfred Zhao -- REPLICAT rjy1 SETENV (ORACLE_SID=ora10) USERID ggt_admin, PASSWORD ggt_admin DISCARDFILE ./dirrpt/rjy1.dsc, PURGE HandleCollisions AssumeTargetDefs Map jy.*,Target jy.*;

添加rjy1進程（replicat進程）：

GGSCI (oradb31) 8> add replicat rjy1, exttrail ./dirdat/ta REPLICAT added.

查看當前目標端進程狀態：

GGSCI (oradb31) 9> info allProgram Status Group Lag at Chkpt Time Since ChkptMANAGER RUNNING REPLICAT STOPPED RJY1 00:00:00 00:00:18

可以看到Manager已經啟動，RJY1還沒有啟動。

3.配置OGG單向復制

注意以下3個步驟執行的順序不能更改，否則很可能會造成數據不一致。

3.1 啟動源庫extract進程

確認已經啟動Manager進程。
啟動lxjy1

GGSCI (oradb30) 12> start extract lxjy1

啟動lpjy1

GGSCI (oradb30) 19> start extract lpjy1

查看源端進程狀態：

GGSCI (oradb30) 20> info allProgram Status Group Lag at Chkpt Time Since ChkptMANAGER RUNNING EXTRACT RUNNING LPJY1 01:08:28 00:00:00 EXTRACT RUNNING LXJY1 00:00:00 00:00:10

可以看到所有進程都是正常RUNNING狀態。如果啟動后狀態不是RUNNING，就需要檢查配置和查看 ggserr.log 日志信息定位原因。

現在可以查看源端extract進程信息：

GGSCI (oradb30) 21> info extract lxjy1EXTRACT LXJY1 Last Started 2017-01-14 00:57 Status RUNNING Checkpoint Lag 00:00:00 (updated 00:00:03 ago) Log Read Checkpoint Oracle Redo Logs2017-01-14 01:05:56 Thread 1, Seqno 133, RBA 41481856SCN 0.1517654 (1517654)GGSCI (oradb30) 22> info extract lpjy1EXTRACT LPJY1 Last Started 2017-01-14 01:02 Status RUNNING Checkpoint Lag 00:00:00 (updated 00:00:09 ago) Log Read Checkpoint File ./dirdat/sa0000002017-01-14 01:06:00.000000 RBA 1551197

可以看到OGG的extract進程抓取到的信息：

GGSCI (oradb30) 26> STATS ext lxjy1Sending STATS request to EXTRACT LXJY1 ...Start of Statistics at 2017-01-14 01:09:21.Output to ./dirdat/sa:Extracting from JY.T_SECOND to JY.T_SECOND:*** Total statistics since 2017-01-14 00:58:01 ***Total inserts 5217.00Total updates 0.00Total deletes 0.00Total discards 0.00Total operations 5217.00*** Daily statistics since 2017-01-14 00:58:01 ***Total inserts 5217.00Total updates 0.00Total deletes 0.00Total discards 0.00Total operations 5217.00*** Hourly statistics since 2017-01-14 01:00:00 ***Total inserts 560.00Total updates 0.00Total deletes 0.00Total discards 0.00Total operations 560.00*** Latest statistics since 2017-01-14 00:58:01 ***Total inserts 5217.00Total updates 0.00Total deletes 0.00Total discards 0.00Total operations 5217.00Extracting from JY.T_SECOND_P to JY.T_SECOND_P:*** Total statistics since 2017-01-14 00:58:01 ***Total inserts 5217.00Total updates 0.00Total deletes 0.00Total discards 0.00Total operations 5217.00*** Daily statistics since 2017-01-14 00:58:01 ***Total inserts 5217.00Total updates 0.00Total deletes 0.00Total discards 0.00Total operations 5217.00*** Hourly statistics since 2017-01-14 01:00:00 ***Total inserts 560.00Total updates 0.00Total deletes 0.00Total discards 0.00Total operations 560.00*** Latest statistics since 2017-01-14 00:58:01 ***Total inserts 5217.00Total updates 0.00Total deletes 0.00Total discards 0.00Total operations 5217.00End of Statistics.GGSCI (oradb30) 27> stats ext lpjy1Sending STATS request to EXTRACT LPJY1 ...Start of Statistics at 2017-01-14 01:09:31.Output to ./dirdat/ta:Extracting from JY.T_SECOND to JY.T_SECOND:*** Total statistics since 2017-01-14 01:02:43 ***Total inserts 4509.00Total updates 0.00Total deletes 0.00Total discards 0.00Total operations 4509.00*** Daily statistics since 2017-01-14 01:02:43 ***Total inserts 4509.00Total updates 0.00Total deletes 0.00Total discards 0.00Total operations 4509.00*** Hourly statistics since 2017-01-14 01:02:43 ***Total inserts 4509.00Total updates 0.00Total deletes 0.00Total discards 0.00Total operations 4509.00*** Latest statistics since 2017-01-14 01:02:43 ***Total inserts 4509.00Total updates 0.00Total deletes 0.00Total discards 0.00Total operations 4509.00Extracting from JY.T_SECOND_P to JY.T_SECOND_P:*** Total statistics since 2017-01-14 01:02:43 ***Total inserts 4509.00Total updates 0.00Total deletes 0.00Total discards 0.00Total operations 4509.00*** Daily statistics since 2017-01-14 01:02:43 ***Total inserts 4509.00Total updates 0.00Total deletes 0.00Total discards 0.00Total operations 4509.00*** Hourly statistics since 2017-01-14 01:02:43 ***Total inserts 4509.00Total updates 0.00Total deletes 0.00Total discards 0.00Total operations 4509.00*** Latest statistics since 2017-01-14 01:02:43 ***Total inserts 4509.00Total updates 0.00Total deletes 0.00Total discards 0.00Total operations 4509.00End of Statistics.GGSCI (oradb30) 28>

3.2 初始化目標庫數據

我這里使用Oracle的exp/imp工具，從源端導出數據，導入到目標端。
exp 導出源庫表數據
--exp 導出
exp jy/jy tables=t_second,t_second_p file=source_jy.dmp log=exp_source_jy.log

SQL> select count(1) from t_second;COUNT(1) ----------11056SQL> select count(1) from t_second_p; COUNT(1) ----------11072[oracle@oradb30 ~]$ exp jy/jy tables=t_second,t_second_p file=source_jy.dmp log=exp_source_jy.logExport: Release 10.2.0.5.0 - Production on Sat Jan 14 01:15:26 2017Copyright (c) 1982, 2007, Oracle. All rights reserved.Connected to: Oracle Database 0g Enterprise Edition Release 10.2.0.5.0 - 4bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options Export done in US7ASCII character set and AL16UTF16 NCHAR character set server uses ZHS16GBK character set (possible charset conversion)About to export specified tables via Conventional Path ... . . exporting table T_SECOND 11096 rows exported EXP-00091: Exporting questionable statistics. . . exporting table T_SECOND_P 11097 rows exported EXP-00091: Exporting questionable statistics. EXP-00091: Exporting questionable statistics. Export terminated successfully with warnings.

imp 導入目標庫數據
--創建用戶
create user jy identified by jy default tablespace users;
grant resource, connect to jy;
--imp 導入
imp jy/jy tables=t_second,t_second_p file=source_jy.dmp log=imp_source_jy.log

[oracle@oradb31 ~]$ imp jy/jy tables=t_second,t_second_p file=source_jy.dmp log=imp_source_jy.logImport: Release 10.2.0.5.0 - Production on Sat Jan 14 01:20:08 2017Copyright (c) 1982, 2007, Oracle. All rights reserved.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 optionsExport file created by EXPORT:V10.02.01 via conventional path import done in US7ASCII character set and AL16UTF16 NCHAR character set import server uses ZHS16GBK character set (possible charset conversion) . importing JY's objects into JY . importing JY's objects into JY . . importing table "T_SECOND" 11096 rows imported . . importing table "T_SECOND_P" 11097 rows imported Import terminated successfully without warnings.

3.3 開啟目標庫replicat應用

確認已經啟動Manager進程。
啟動replicat應用

GGSCI (oradb31) 2> start replicat rjy1 Sending START request to MANAGER ... REPLICAT RJY1 startingGGSCI (oradb31) 3> info all Program Status Group Lag at Chkpt Time Since Chkpt MANAGER RUNNING REPLICAT RUNNING RJY1 00:00:00 00:41:50

下面我來驗證數據：
--開啟replicat之前查詢目標庫，也就是初始化導入的數據：

SQL> select count(1) from t_second;COUNT(1) ----------11096 SQL> select count(1) from t_second_p;COUNT(1) ----------11097

--開啟replicat后查詢目標庫：

SQL> select count(1) from t_second_p;COUNT(1) ----------11581 SQL> select count(1) from t_second;COUNT(1) ----------16459

為了更清晰的看到差異，我將模擬應用的會話殺掉，這樣兩表的數量不再變化。

--查詢源庫兩個表的數量：

SQL> select count(1) from t_second_p;COUNT(1) ----------11966 SQL> select count(1) from t_second;COUNT(1) ----------11966

--查看目標庫兩個表的數量：

SQL> select count(1) from t_second_p;COUNT(1) ----------11966 SQL> select count(1) from t_second;COUNT(1) ----------16840

發現有主鍵的表t_second_p在ogg同步后數據完全一致，符合預期；
而沒有主鍵的表t_second在ogg同步后數據重復插入了一部分（即源端開啟extract進程后捕獲到的插入，和imp導入成功前這期間的數據重復插入了）
根據這個現象，OGG需要同步的表還是最好有主鍵約束。
最后，一切正常后，把目標端replicat進程的參數文件中的HandleCollisions配置去掉。因為正式同步后是建議有沖突問題人工處理。

Reference

• • 張曉明. 大話Oracle Grid[M]. 人民郵電出版社, 2014.
  • Oracle GoldenGate 11g Implementer's guide

轉載于:https://www.cnblogs.com/wangsicongde/p/7577082.html

總結

以上是生活随笔為你收集整理的OGG学习笔记02的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。