这篇文章给大家分享的是有关OGG在RAC上如何安装配置的内容。小编觉得挺实用的,因此分享给大家做个参考,一起跟随小编过来看看吧。
此次试验是为了某省电力公司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 hh34: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 hh34: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配置到这里就结束了。
感谢各位的阅读!关于“OGG在RAC上如何安装配置”这篇文章就分享到这里了,希望以上内容可以对大家有一定的帮助,让大家可以学到更多知识,如果觉得文章不错,可以把它分享出去让更多的人看到吧!