此次試驗是為了某省電力公司OGG初始化模擬演練。演練過程分為兩篇部落格記錄全過程。第一篇是安裝配置，主要介紹OGG在源端和災備端都是雙節點RAC下的配置。第二篇是OGG初始化，使用rman恢復災備端資料庫，啟用OGG複製程式追加日誌。

環境介紹：
Source Target

OS:Enterprise Linux Server release 5.7
OGG: 11.2.1.0.1
ORACLE: 11.2.0.4 RAC 雙節點
172.16.228.101 node1
172.16.228.102 node2
OGG路徑 node1 /goldengate

OS:Enterprise Linux Server release 5.7
OGG 11.2.1.0.1
ORACLE: 11.2.0.4 RAC 雙節點
172.16.228.103 node3
172.16.228.104 node4
OGG路徑 node3 /goldengate

Source系統設定

1.在node1解壓縮ogg安裝包

# su - oracle

[oracle@node1 ~]$ cd /goldengate/

[oracle@node1 goldengate]$ unzip /tmp/ogg112101_fbo_ggs_Linux_x64_ora11g_64bit.zip

[oracle@node1 goldengate]$ tar xvf fbo_ggs_Linux_x64_ora11g_64bit.tar

2.在bash_profile中新增OGG_HOME

su - oraclecdvi .bash_profile
export ORACLE_HOSTNAME=node1
export ORACLE_SID=PROD1
export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=$ORACLE_BASE/product/11.2.0/db_1
export ORACLE_UNQNAME=PROD
export OGG_HOME=/goldengate
export TNS_ADMIN=$ORACLE_HOME/network/admin
export ORACLE_TERM=xterm
export PATH=/usr/sbin:$PATH
export PATH=$ORACLE_HOME/bin:$PATH:$OGG_HOME
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib:
export CLASSPATH=$ORACLE_HOME/JRE:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib
export EDITOR=vi
export LANG=en_US
export NLS_LANG=american_america.AL32UTF8
export NLS_DATE_FORMAT='yyyy/mm/dd hh24:mi:ss'

3.建立OGG應用目錄,該操作需要在OGG_HOME路徑下

cd $OGG_HOME

[oracle@node1 goldengate]$ ggsci

GGSCI (node1) 1> CREATE SUBDIRS

4.資料庫開啟歸檔模式

檢視是否為歸檔模式archive log list;開啟歸檔模式# srvctl stop database -d prodSQL> startup mount;SQL> alter database archivelog;SQL> shutdown immediate;# srvctl start database -d prod

5.開啟資料庫級別日誌補充

sqlplus / as sysdba
SQL> ALTER DATABASE FORCE LOGGING;

SQL>ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;

SQL>ALTER DATABASE ADD SUPPLEMENTAL LOG DATA(PRIMARY KEY, UNIQUE,FOREIGN KEY) COLUMNS;

SQL>ALTER SYSTEM ARCHIVE LOG CURRENT;

SELECT
SUPPLEMENTAL_LOG_DATA_MIN,
SUPPLEMENTAL_LOG_DATA_PK,
SUPPLEMENTAL_LOG_DATA_UI,
SUPPLEMENTAL_LOG_DATA_FK,
SUPPLEMENTAL_LOG_DATA_ALL
FROM v$database;

SUPPLEME SUP SUP SUP SUP
-------- --- --- --- ---
YES YES YES YES NO

Oracle11.2.0.4版本所需引數
SQL> ALTER SYSTEM SET ENABLE_GOLDENGATE_REPLICATION = TRUE SCOPE=BOTH;

6.建立測試使用者

sqlplus / as sysdba

SQL >CREATE USER snow IDENTIFIED BY snow DEFAULT TABLESPACE USERS;

SQL >GRANT CONNECT, RESOURCE TO snow;

SQL >conn snow/snow

SQL >CREATE TABLE t1 (id INT PRIMARY KEY, name VARCHAR2(10));

7.建立OGG管理使用者oggadmin及其表空間goldengate

sqlplus / as sysdba

SQL >select name from v$datafile;

SQL >CREATE TABLESPACE goldengate DATAFILE '+DATA' SIZE 100M AUTOEXTEND ON;

SQL >CREATE USER oggadmin IDENTIFIED BY oggadmin DEFAULT TABLESPACE goldengate;

SQL >GRANT dba TO oggadmin;

8.新增角色

cd $OGG_HOME
sqlplus / as sysdba
SQL >@/goldengate/role_setup
Enter GoldenGate schema name:oggadmin
GRANT GGS_GGSUSER_ROLE TO oggadmin;

9.安裝sequence支援

cd $OGG_HOMEsqlplus / as sysdbaSQL> @sequence.sqlSQL> GRANT EXECUTE ON oggadmin.updateSequence TO oggadmin;SQL> ALTER TABLE sys.seq$ ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY) COLUMNS;

10.設定全域性引數

cd $OGG_HOME

ggsci

GGSCI> EDIT PARAMS ./GLOBALS

GGSCHEMA oggadmin

Target系統設定
11.在node3解壓縮ogg安裝包

# su - oracle

[oracle@node3 ~]$ cd /goldengate/

[oracle@node3 goldengate]$ unzip /tmp/ogg112101_fbo_ggs_Linux_x64_ora11g_64bit.zip

[oracle@node3 goldengate]$ tar xvf fbo_ggs_Linux_x64_ora11g_64bit.tar

12.在bash_profile中新增OGG_HOME

su - oracle
cd
vi .bash_profile
export ORACLE_HOSTNAME=node3
export ORACLE_SID=PROD1
export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=$ORACLE_BASE/product/11.2.0/db_1
export ORACLE_UNQNAME=PROD
export OGG_HOME=/goldengate
export TNS_ADMIN=$ORACLE_HOME/network/admin
export ORACLE_TERM=xterm
export PATH=/usr/sbin:$PATH
export PATH=$ORACLE_HOME/bin:$PATH:$OGG_HOME
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib:
export CLASSPATH=$ORACLE_HOME/JRE:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib
export EDITOR=vi
export LANG=en_US
export NLS_LANG=american_america.AL32UTF8
export NLS_DATE_FORMAT='yyyy/mm/dd hh24:mi:ss'

13.建立OGG應用目錄,該操作需要在OGG_HOME路徑下

cd $OGG_HOME

[oracle@node1 goldengate]$ ggsci

GGSCI (node1) 1> CREATE SUBDIRS

14.資料庫開啟歸檔模式

檢視是否為歸檔模式
archive log list;

開啟歸檔模式
# srvctl stop database -d prod

SQL> startup mount;

SQL> alter database archivelog;

SQL> shutdown immediate;

# srvctl start database -d prod

15.建立測試使用者

sqlplus / as sysdba

SQL >CREATE USER snow IDENTIFIED BY snow DEFAULT TABLESPACE USERS;

SQL >GRANT CONNECT, RESOURCE TO snow;

SQL >conn snow/snow

SQL >CREATE TABLE t1 (id INT PRIMARY KEY, name VARCHAR2(10));

16.建立OGG管理使用者oggadmin及其表空間goldengate

sqlplus / as sysdba

SQL >select name from v$datafile;

SQL >CREATE TABLESPACE goldengate DATAFILE '+DATA' SIZE 100M AUTOEXTEND ON;

SQL >CREATE USER oggadmin IDENTIFIED BY oggadmin DEFAULT TABLESPACE goldengate;

SQL >GRANT dba TO oggadmin;

17.設定全域性引數

cd $OGG_HOME

GGSCI

GGSCI> EDIT PARAMS ./GLOBALS

GGSCHEMA oggadmin

Source系統設定
18.配置管理程式

GGSCI> EDIT PARAM MGR
PORT 7839
DYNAMICPORTLIST 7840-7914
--AUTORESTART EXTRACT *, RETRIES 5, WAITMINUTES 7
PURGEOLDEXTRACTS ./dirdat/*, USECHECKPOINTS, MINKEEPDAYS 10
LAGREPORTHOURS 1
LAGINFOMINUTES 30
LAGCRITICALMINUTES 45

19.開啟表級別日誌補充，追加物件為使用者snow下所有表

GGSCI> DBLOGIN USERID oggadmin,PASSWORD oggadmin

GGSCI> ADD TRANDATA snow.t1

20.建立初級提取組ex1，源端是雙節點RAC，此處設定引數THREADS 2

ADD EXTRACT ex1,TRANLOG,BEGIN NOW,THREADS 2

21.為初級提取組ex1指定本地trail檔案

ADD EXTTRAIL /goldengate/dirdat/ex, EXTRACT ex1 MEGABYTES 5

22.生成OGG管理使用者oggadmin的密碼

GGSCI > encrypt password oggadmin encryptkey default
Using default key...

Encrypted password: AACAAAAAAAAAAAIARFBCXDACYBXIVCND
Algorithm used: BLOWFISH

23.配置初級提取組引數檔案，源端是雙節點RAC，此處設定引數TRANLOGOPTIONS DBLOGREADER

EXTRACT ex1

TRANLOGOPTIONS DBLOGREADER
EXTTRAIL /goldengate/dirdat/ex
SETENV (NLS_LANG="AMERICAN_AMERICA.UTF8")
USERID oggadmin, PASSWORD AACAAAAAAAAAAAIARFBCXDACYBXIVCND, ENCRYPTKEY default
GETTRUNCATES
REPORTCOUNT EVERY 30 MINUTES, RATE
DISCARDFILE /goldengate/dirrpt/ex1.dsc, APPEND, MEGABYTES 1024
DISCARDROLLOVER AT 3:00
WARNLONGTRANS 2h, CHECKINTERVAL 3m
DYNAMICRESOLUTION
DBOPTIONS ALLOWUNUSEDCOLUMN
FETCHOPTIONS NOUSESNAPSHOT
FETCHOPTIONS FETCHPKUPDATECOLS
--TRANLOGOPTIONS CONVERTUCS2CLOBS
--THREADOPTIONS MAXCOMMITPROPAGATIONDELAY 60000 IOLATENCY 60000
TABLE snow.*;

24.建立投遞組dp1，設定本地trail檔案

ADD EXTRACT dp1 EXTTRAILSOURCE /goldengate/dirdat/ex

25.為投遞進組dp1設定target端trail檔案地址

ADD RMTTRAIL /u01/app/oracle/gg/dirdat/rt, EXTRACT dp1

26.配置投遞組dp1引數檔案。172.16.228.103為目標端OGG所在伺服器IP地址

EXTRACT dp1
USERID oggadmin, PASSWORD oggadmin
RMTHOST 172.16.228.103, MGRPORT 7839, COMPRESS
PASSTHRU
NUMFILES 5000
RMTTRAIL /goldengate/dirdat/rt
DYNAMICRESOLUTION
TABLE snow.*;

Target系統
27.配置管理程式

PORT 7839
USERID oggadmin, PASSWORD AACAAAAAAAAAAAIARFBCXDACYBXIVCND, ENCRYPTKEY default
DYNAMICPORTLIST 7840-7914
AUTORESTART EXTRACT *,RETRIES 5,WAITMINUTES 3
PURGEOLDEXTRACTS ./dirdat/*,usecheckpoints, minkeepdays 5
LAGREPORTHOURS 1
LAGINFOMINUTES 30
LAGCRITICALMINUTES 45

28.建立檢查點表

GGSCI> DBLOGIN USERID oggadmin,PASSWORD oggadmin

GGSCI> ADD CHECKPOINTTABLE oggadmin.checkpointtable

29.在全域性環境中新增檢查點表

GGSCI> EDIT PARAMS ./GLOBALS

GGSCHEMA oggadmin
CHECKPOINTTABLE oggadmin.checkpointtable

30.建立複製組rt1,設定讀取trail檔案路徑以及檢查點表

ADD REPLICAT rt1, EXTTRAIL /goldengate/dirdat/rt, checkpointtable oggadmin.checkpointtable

31.為複製組rt1配置引數檔案

REPLICAT rt1
SETENV (NLS_LANG = "American_America.UTF8")
USERID oggadmin, PASSWORD AACAAAAAAAAAAAIARFBCXDACYBXIVCND, ENCRYPTKEY default
DBOPTIONS DEFERREFCONST
GETTRUNCATES
REPORT AT 06:00
REPORTCOUNT EVERY 30 MINUTES, RATE
REPORTROLLOVER AT 02:00
REPERROR DEFAULT, ABEND
--HANDLECOLLISIONS
ALLOWNOOPUPDATES
DISCARDFILE ./dirrpt/repsa.dsc, APPEND, MEGABYTES 1024M
DISCARDROLLOVER AT 02:00
ASSUMETARGETDEFS
MAP snow.*, TARGET snow.*;

測試環節
啟動source管理程式
GGSCI > START MGR

啟動target管理程式
GGSCI > START MGR

啟動source提取程式
GGSCI > START ex1

啟動target複製程式
GGSCI > START rt1

啟動source投遞程式
GGSCI > START dp1

確認source程式狀態
GGSCI > INFO ALL

Program     Status      Group       Lag at Chkpt Time Since Chkpt

MANAGER     RUNNING
EXTRACT     RUNNING     DP1         00:00:00      00:00:08
EXTRACT     RUNNING     EX1         00:00:00      00:00:03

確認target程式狀態
GGSCI > INFO ALL

Program     Status      Group       Lag at Chkpt Time Since Chkpt

MANAGER     RUNNING
REPLICAT    RUNNING     RT1         00:00:00      00:00:02

源端節點node1插入資料

begin
for i in 1..1000 loop
insert into t1 values(i,'oOo');
end loop;
commit;
end;
/

複製端驗證
select count(*) from snow.t1;

生產端（source）與災備端（target）的OGG配置到這裡就結束了。

下一篇我們將採用rman備份與ogg複製的方式來完成初始化。

OGG在RAC上的初始化(上)-- 安裝配置篇

相關文章