此次试验是为了某省电力公司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复制的方式来完成初始化。

ITPub博客

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

最新文章