搭建一個Oracle到Oracle的GoldenGate單向複製測試環境
測試環境:
OS: 5.4 64bit
DB: 11.2.0.3 64bit
示例採用GoldenGate典型的配置:
在Source端,配置一個管理程式, 新增一個Extract程式,新增一個本地佇列路徑,定義一個遠端的接收佇列路徑。
在Target端,配置一個管理程式和新增一個Replicat程式,指定一個應用佇列,即抽取程式定義的遠端佇列。
GoldenGate單向表DML同步
Oracle GoldenGate 系列:Extract 程式的恢復原理
Oracle GoldenGate安裝配置
Oracle goldengate的OGG-01004 OGG-1296錯誤
Oracle GoldenGate快速入門教程:基本概念和配置
搭建一個Oracle到Oracle的GoldenGate雙向複製環境
一.安裝GG 軟體
1.1 OS 和 DB 版本
[root@gg2 ~]# uname -a
Linux gg2 2.6.18-164.el5xen #1 SMP Tue Aug18 15:59:52 EDT 2009 x86_64 x86_64 x86_64 GNU/Linux
[root@gg2 ~]# cat /etc/redhat-release
Red Hat Enterprise Linux Server release 5.4(Tikanga)
SQL> select * from v$version whererownum=1;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise EditionRelease 11.2.0.3.0 - 64bit Production
1.2 安裝GG
在source database 和 target database 都執行如下操作:
[root@gg2 ~]# su - oracle
gg2:/home/oracle> mkdir /u01/ggate
gg2:/home/oracle> cd /u01
gg2:/u01> ls
app ggate
fbo_ggs_Linux_x64_ora11g_64bit.tar OGG_WinUnix_Rel_Notes_11.1.1.1.0.pdf
fbo_ggs_Linux_x64_ora11g_64bit.zip README.txt
gg2:/u01> tar xvffbo_ggs_Linux_x64_ora11g_64bit.tar -C /u01/ggate
1.3 新增環境變數
在/home/oracle/.bash_profile檔案裡新增如下內容:
export PATH=/u01/ggate:$PATH
exportLD_LIBRARY_PATH=/u01/ggate:$LD_LIBRARY_PATH
export GGATE=/u01/ggate
注意我這裡的GG 和Oracle 使用的是相同的使用者,所以把GG 的變數加上就可以了。載入剛剛設定的環境變數:
gg2:/home/oracle> source/home/oracle/.bash_profile
1.4 使用ggsci工具,建立必要的目錄
gg1:/u01/ggate> ggsci
--呼叫ggsci 工具
Oracle GoldenGate Command Interpreter forOracle
Version 11.1.1.1OGGCORE_11.1.1_PLATFORMS_110421.2040
Linux, x64, 64bit (optimized), Oracle 11gon Apr 21 2011 22:42:14
Copyright (C) 1995, 2011, Oracle and/or itsaffiliates. All rights reserved.
GGSCI (gg1) 1> create subdirs
--使用ggsci 工具建立目錄
Creating subdirectories under currentdirectory /u01/ggate
Parameter files /u01/ggate/dirprm: created
Report files /u01/ggate/dirrpt: created
Checkpoint files /u01/ggate/dirchk: created
Process status files /u01/ggate/dirpcs: created
SQL script files /u01/ggate/dirsql: created
Database definitions files /u01/ggate/dirdef: created
Extract data files /u01/ggate/dirdat: created
Temporary files /u01/ggate/dirtmp: created
Veridata files /u01/ggate/dirver: created
Veridata Lock files /u01/ggate/dirver/lock: created
Veridata Out-Of-Sync files /u01/ggate/dirver/oos: created
Veridata Out-Of-Sync XML files/u01/ggate/dirver/oosxml: created
Veridata Parameter files /u01/ggate/dirver/params: created
Veridata Report files /u01/ggate/dirver/report: created
Veridata Status files /u01/ggate/dirver/status: created
Veridata Trace files /u01/ggate/dirver/trace: created
Stdout files /u01/ggate/dirout: created
GGSCI (gg1) 2>
以上就是GG 的安裝,在source 和target database 都執行。
二.配置Source database
GoldenGate透過抓取源端資料庫重做日誌進行分析,將獲取的資料應用到目標端,實現資料同步。因此,源資料庫需要必須處於歸檔模式,並啟用附加日誌和強制日誌。
2.1 歸檔模式、附加日誌、強制日誌
--檢視
SQL> select log_mode,supplemental_log_data_min,force_logging from v$database;
LOG_MODE SUPPLEME FOR
------------ -------- ---
ARCHIVELOG NO NO
--修改
(1)archivelog
SQL>shutdown immediate
SQL>startup mount
SQL>alter database archivelog;
SQL>alter database open;
(2) force logging
SQL>alterdatabase force logging;
(3)supplemental log data
SQL>alterdatabase add supplemental log data;
2.2 Oracle 9i的_LOG_PARALLELISM引數
如果是Oracle 9i的資料庫,還需要將_LOG_PARALLELISM 參??設定為1. 因為GG 不支援該值超過1.
If using OracleGoldenGate for an Oracle 9i source database, set the _LOG_PARALLELISMparameter to 1. Oracle GoldenGate does not support values higher than 1.
2.3 啟用DDL 支援
GG雖然支援DDL,但是也是有限制的,GG 支援DDL 也是透過建立一些table 來儲存這些DDL 的資訊,關於這些table 的具體說明,在如下連結的第二小節:啟用GG 對DDL 操作的支援有詳細說明:
對於這些存放DDL 資訊表的管理的理論支援,參考如下連結的第四小結:Managing theOracle DDL replication environment。
這塊的測試內容會另篇Blog進行測試。
2.3.1 禁用Recycle Bin
如果啟用DDL 支援,必須關閉recycle bin。官網的解釋如下:
If the recyclebin is enabled, the Oracle GoldenGate DDL trigger session receives implicitrecycle bin DDL operations that cause the trigger to fail.
Oracle 11g:
SQL> alter system set recyclebin=offscope=spfile;
System altered.
如果資料庫是10g,需要關閉recyclebin並重啟;或者手工purge recyclebin。
2.3.2 建立存放DDL 資訊的user並賦權
SQL> create user ggate identified by ggate default tablespace users temporary tablespace temp;
User created.
SQL> grant connect,resource to ggate;
Grant succeeded.
SQL> grant execute on utl_file to ggate;
Grant succeeded.
退出所有使用Oracle 的session,然後使用SYSDBA許可權的使用者執行如下指令碼:
gg1:/u01/ggate> echo $GGATE
/u01/ggate
--進入GG的目錄,然後呼叫指令碼:
gg1:/home/oracle> cd $GGATE
gg1:/u01/ggate> sqlplus / as sysdba;
SQL*Plus: Release 11.2.0.3.0 Production onTue Nov 8 19:41:58 2011
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise EditionRelease 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Miningand Real Application Testing options
--指令碼1:
SQL> @marker_setup.sql;
Marker setup script
You will be prompted for the name of aschema for the GoldenGate database objects.
NOTE: The schema must be created prior torunning this script.
NOTE: Stop all DDL replication beforestarting this installation.
--輸入我們之前建立的使用者名稱:
Enter GoldenGate schema name:ggate
Marker setup table script complete, runningverification script...
Please enter the name of a schema for theGoldenGate database objects:
Setting schema name to GGATE
MARKER TABLE
-------------------------------
OK
MARKER SEQUENCE
-------------------------------
OK
Script complete.
--指令碼2:
SQL> @ddl_setup.sql;
GoldenGate DDL Replication setup script
Verifying that current user has privilegesto install DDL Replication...
You will be prompted for the name of aschema for the GoldenGate database objects.
NOTE: For an Oracle 10g source, the systemrecycle bin must be disabled. For Oracle 11g and later, it can be enabled.
--注意這裡提示我們在10g裡,必須關閉recycle bin,在11g以後的版本,可以不用關閉。
NOTE: The schema must be created prior torunning this script.
NOTE: Stop all DDL replication beforestarting this installation.
--提示輸入GG的使用者:
Enter GoldenGate schema name:ggate
You will be prompted for the mode ofinstallation.
To install or reinstall DDL replication,enter INITIALSETUP
To upgrade DDL replication, enter NORMAL
--這裡讓我們選擇安裝模式: install 和 reinstall 選擇INITIALSETUP
Enter mode of installation:INITIALSETUP
Working, please wait ...
Spooling to file ddl_setup_spool.txt
Checking for sessions that are holdinglocks on Oracle Golden Gate metadata tables ...
Check complete.
Using GGATE 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 theGoldenGate database objects:
Setting schema name to GGATE
DDLORA_GETTABLESPACESIZE STATUS:
……
STATUS OF DDL REPLICATION
-------------------------------------------------------------------------------------------------------
SUCCESSFUL installation of DDL Replicationsoftware components
Script complete.
--指令碼3:
SQL> @role_setup.sql;
GGS Role setup script
This script will drop and recreate the roleGGS_GGR_ROLE
To use a different role name, quit thisscript and then edit the params.sql script to change the gg_role parameter tothe preferred name. (Do not run the script.)
You will be prompted for the name of aschema for the GoldenGate database objects.
NOTE: The schema must be created prior torunning this script.
NOTE: Stop all DDL replication beforestarting this installation.
--同樣輸入GG使用者名稱:
Enter GoldenGate schema name:ggate
Wrote file role_setup_set.txt
PL/SQL procedure successfully completed.
Role setup script complete
Grant this role to each user assigned tothe Extract, GGSCI, and Manager processes, by using the following SQL command:
--這裡提示我們賦權給相關的使用者:
GRANT GGS_GGSUSER_ROLE TO<loggedUser>
where <loggedUser> is the userassigned to the GoldenGate processes.
--指令碼4:賦權
SQL> grant GGS_GGSUSER_ROLE to ggate;
Grant succeeded.
--指令碼5:
SQL> @ddl_enable.sql;
Trigger altered.
注意這裡指令碼建立的table都是使用預設的名稱,當然也可以修改這些table的預設名,具體這塊參考之前的文件中的說明。
三.測試GG
經過第一和第二節的配置,GG 的配置基本完成,這裡我們開始測試GG。
注意:
(1) 目標庫的使用者名稱和物件名稱可以與源端不同,關鍵在於配置檔案中要能夠正確匹配。
(2) 配置源和目標兩端tnsnames,保持互聯互通。
3.1 在Source 和 Target database上建立測試使用者
--source database
SQL> create user sender identified by default tablespace users temporary tablespace temp;
User created.
SQL> grant connect,resource,dba tosender;
Grant succeeded.
--target database
SQL> create user receiver identified byoracle default tablespace users temporary tablespace temp;
User created.
SQL> grant connect,resource,dba toreceiver;
Grant succeeded.
3.2 在Source 和Target 上配置Manager
gg1:/home/oracle> cd $GGATE
gg1:/u01/ggate> ggsci
Oracle GoldenGate Command Interpreter forOracle
Version 11.1.1.1OGGCORE_11.1.1_PLATFORMS_110421.2040
Linux, x64, 64bit (optimized), Oracle 11gon Apr 21 2011 22:42:14
Copyright (C) 1995, 2011, Oracle and/or itsaffiliates. All rights reserved.
GGSCI (gg1) 1> info all
Program Status Group Lag Time Since Chkpt
MANAGER STOPPED
GGSCI (gg1) 2> edit params mgr
PORT 7809
--這裡我們指定了埠,然後:wq 儲存退出。
GGSCI (gg1) 3> start manager
Manager started.
以上是在Source 庫上執行的,在Target 庫上執行同樣的操作。
3.3 配置SourceDB 的複製佇列
3.3.1 先連線到資料庫,測試連線:
GGSCI (gg1) 10> dblogin userid ggate@gg1, password ggate
Successfully logged into database.
3.3.2 增加一個抽取:
GGSCI (gg1) 11> add extract ext1,tranlog, begin now
2011-11-08 20:36:47 INFO OGG-01749 Successfully registeredEXTRACT EXT1 to start managing log retention at SCN 1121060.
EXTRACT added.
GGSCI (gg1) 12> add exttrail /u01/ggate/dirdat/lt, extract ext1
EXTTRAIL added
修改抽取程式ext1引數:引數不能註釋,要不然程式異常不能啟動。
EXTRACT ABENDED EXT1 00:00:00 17:19:39
GGSCI (gg1) 13> edit params ext1
extract ext1
userid ggate@gg1, password ggate
rmthost gg2, mgrport 7809 gg2(hostname)最好寫成IP地址,否則可能ping不通hostname(如果沒配置對的話)。
rmttrail /u01/ggate/dirdat/lt
ddl include mapped objname sender.*;
table sender.*;
GGSCI (gg1) 14> info all
Program Status Group Lag Time Since Chkpt
MANAGER RUNNING
EXTRACT STOPPED EXT1 00:00:00 00:03:26
3.4 配置TargetDB 同步佇列
3.4.1 在Target 端新增checkpoint表:
GGSCI (gg2) 6> edit params ./GLOBAL
GGSCHEMA ggate
CHECKPOINTTABLE ggate.checkpoint
新增如上2條記錄。
GGSCI (gg2) 12> dblogin userid ggate@gg2,password ggate
Successfully logged into database.
--說明,這個使用者是在Source 庫啟用DDL 建立的,我在Target 庫也建立了這個使用者。
GGSCI (gg2) 13> add checkpointtable ggate.checkpoint
Successfully created checkpoint tableGGATE.CHECKPOINT.
3.4.2 建立同步佇列
GGSCI (gg2) 14> add replicat rep1,exttrail /u01/ggate/dirdat/lt, checkpointtable ggate.checkpoint 和前面的路徑相對照,要不然資料不能同步。
REPLICAT added.
GGSCI (gg2) 15> edit params rep1
replicat rep1
ASSUMETARGETDEFS
userid ggate@gg2,password ggate
discardfile /u01/ggate/dirdat/rep1_discard.txt,append, megabytes 10貌似最後一個逗號之後都要空格一下,直接寫在這個路徑/u01/ggate/,資料不能同步。
DDL
map sender.*, target receiver.*;target與逗號之間有空格。新增如上內容。
3.5開啟同步
3.5.1 Source DB:
GGSCI (gg1) 15> start extract ext1
Sending START request to MANAGER ...
EXTRACT EXT1 starting
GGSCI (gg1) 16> info all
Program Status Group Lag Time Since Chkpt
MANAGER RUNNING
EXTRACT STOPPED EXT1 00:00:00 00:14:16
GGSCI (gg1) 17> info all
Program Status Group Lag Time Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING EXT1 00:14:26 00:00:02
3.5.2 Target DB
GGSCI (gg2) 16> start replicat rep1
Sending START request to MANAGER ...
REPLICAT REP1 starting
GGSCI (gg2) 17> info all
Program Status Group Lag Time Since Chkpt
MANAGER RUNNING
REPLICAT RUNNING REP1 00:00:00 00:00:04
3.6 測試Data 複製
我們在Source DB上的sender 使用者下建立一張表,然後看這張表是否同步到了Target DB的receiver使用者下。
--Source DB:
SQL> conn sender/oracle;
Connected.
SQL> create table dave as select * fromsys.all_users;
Table created.
--Target DB:
SQL> conn receiver/oracle;
Connected.
SQL> select count(*) from dave;
COUNT(*)
----------
32
資料同步過來了,因為我們啟用了DDL的支援,所以這裡把表給複製過來了。
現在我們在Source DB上在插入一些記錄,在驗證下GG的同步情況:
SQL> insert into dave select * fromsys.all_users;
32 rows created.
SQL> commit;
Commit complete.
只有commit之後,資料才會同步過去。
在Target DB 驗證:
SQL> select count(*) from dave;
COUNT(*)
----------
32
SQL> /
COUNT(*)
----------
64
同步正常,以上就是Oracle to Oracle 下的一個GG 單向複製示例。有關GG的更多內容會繼續測試。 沒有主鍵也會同步。
單向複製:如果目標端有不同於源端的表,會出現EXTRACT ABENDED。
現在只能實現source端到target端的單向複製(insert、update、delete、select實時同步)
步驟:
連線資料庫
dblogin userid ggate@gg1, password ggate
啟動程式
Start manager 、start extract ext
檢視
Info all
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/9034054/viewspace-1973428/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- 搭建oracle 11.2.0.4環境下的goldengate複製OracleGo
- GoldenGate簡單複製環境的搭建Go
- 通過goldengate從ORACLE向mysql的單向複製GoOracleMySql
- Oracle GoldenGate環境搭建OracleGo
- GoldenGate複製的幾個簡單測試Go
- Oracle使用goldengate分別向Oracle和mysql雙路的單向複製OracleGoMySql
- oracle goldengate 雙向複製配置OracleGo
- Oracle 11g GoldenGate單向複製配置(DML)OracleGo
- 使用goldengate的資料泵進行Oracle-Oracle的單向複製GoOracle
- Oracle goldengate 11g (二)【DML and DDL單向複製】OracleGo
- goldengate單向複製的配置Go
- goldengate 單向複製配置Go
- GoldenGate配置(一)之單向複製配置Go
- GoldenGate單向複製配置(支援DDL複製)Go
- goldengate不使用資料泵完成Oracle-Oracle的雙向複製GoOracle
- GoldenGate在異構環境下的複製Go
- 使用資料泵進行Oracle-Oracle的單向複製。Oracle
- 揭密Oracle之 七種武器 第一章 搭建測試環境Oracle
- 自己營造ORACLE測試環境Oracle
- 前端單元測試之Karma環境搭建前端
- 【GoldenGate】Oracle GoldenGate(一) 安裝與DML單向同步配置GoOracle
- MySQL搭建帶過濾的複製環境MySql
- 生產環境搭建MySQL複製的教程MySql
- Oracle Stream實戰(2)—測試環境準備(一)Oracle
- MySQL InnoDB Cluster環境搭建和簡單測試MySql
- 快速搭建streams表級複製環境
- 實戰goldengate之ora-To-ora單向複製Go
- kaldi環境搭建 | yesno 測試
- android測試環境搭建Android
- oracle goldengate 雙活複製避免迴圈複製引數OracleGo
- 分分鐘搭建Oracle環境Oracle
- ORACLE無GUI搭建環境OracleGUI
- 生產環境中MySQL複製的搭建KPMySql
- Oracle9i高階複製(單向複製部分)實施完畢Oracle
- OCM實驗-測試環境的搭建
- 搭建rac+DataGuard的測試環境
- MySQL 5.5使用Xtrabackup線上搭建複製環境MySql
- griffin環境搭建及功能測試