搭建一個Oracle到Oracle的GoldenGate雙向複製環境
目標:搭建一個Oracle到Oracle的Goldengate雙向複製環境(支援DDL+DML)。
環境:
OS:Red Hat Enterprise Linux Server release 5.5 (Tikanga)
DB:Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
gg1和gg2互為source和target,所處OS和所安DB均一樣。
GoldenGate單向表DML同步
Oracle GoldenGate 系列:Extract 進程的恢複原理
Oracle GoldenGate安裝配置
Oracle goldengate的OGG-01004 OGG-1296錯誤
Oracle GoldenGate快速入門教程:基本概念和配置
搭建Oracle 到Oracle 的Golden Gate 單向複製測試環境
一、安裝GG
在gg1和gg2都執行如下操作:
(1)添加環境變數,在/home/oracle/.bash_profile檔案裡添加如下內容:
export PATH=/u01/ggate:$PATH
exportLD_LIBRARY_PATH=/u01/ggate:$LD_LIBRARY_PATH
export ggtest=/u01/ggate
這裡的GG 和Oracle 使用的是相同的使用者,所以把GG 的變數加上就可以了。載入剛剛設定的環境變數:
source/home/oracle/.bash_profile
(2)使用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和target
(1)GoldenGate通過抓取源端資料庫重做日誌進行分析,將擷取的資料應用到目標端,實現資料同步。因此,來源資料庫需要必須處于歸檔模式,並啟用附加日誌和強制日誌。
歸檔模式、附加日誌、強制日誌
--查看
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)禁用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。
(3)建立存放DDL 資訊的user並賦權
SQL> create user ggtest identified by oracle default tablespace users temporary tablespace temp;
User created.
SQL> grant connect,resource to ggtest;
Grant succeeded.
SQL> grant execute on utl_file to ggtest;
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:ggtest
Marker setup table script complete, runningverification script...
Please enter the name of a schema for theGoldenGate database objects:
Setting schema name to ggtest
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:ggtest
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 ggtest 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 ggtest
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_GGSUSER_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:ggtest
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 ggtest;
Grant succeeded.
--指令碼5:
SQL> @ddl_enable.sql;
Trigger altered.
注意這裡指令碼建立的table都是使用預設的名稱,當然也可以修改這些table的預設名。
更多詳情見請繼續閱讀下一頁的精彩內容: