奕新集团---单实例GG环境搭建-阿里云开发者社区

奕新集团---单实例GG环境搭建

系统：OracleLinux 5.9

数据库版本11.2.0.3

hostname source target

vim /etc/hosts

172.16.10.140 142

内核参数

D:\Oracle官方文档\E11882_01\install.112\e24326\toc.htm

添加用户组

[root@source ~]# groupadd -g 500 oinstall

[root@source ~]# groupadd -g 511 dba

[root@source ~]# useradd -u 500 -g 500 -G 511 oracle

[root@source ~]# echo oracle | passwd oracle --stdin

Changing password for user oracle.

passwd: all authentication tokens updated successfully.

[root@source ~]# mkdir -p /u01/oracle/11g

[root@source ~]# mkdir -p /u01/grid/11g

[root@source ~]# chown -R oracle.oinstall /u01

[root@source ~]# su - oracle

解决：

[root@target ~]# vim /etc/ntp.conf

[root@target ~]# /sbin/service ntpd stop

Shutting down ntpd: [FAILED]

[root@target ~]# /sbin/service ntpd status

ntpd is stopped

[root@target ~]# chkconfig ntpd off

[root@target ~]# mv /etc/ntp.conf /etc/ntp.conf.bak

解决：

执行完上面2个脚本执行吓面脚本

安装数据库软件不建立数据库

环境监测

耐心等待

执行脚本我没有截图

点击close

dbca 有些时候报错说不能找到命令说明我们的环境变量设置错误。等会来改

DBCA命令创建数据库

这里不用启用归档。

等待

处理这个问题

指定环境变量中ORACLE_SID

指定了之后变成

去掉试试

解决了 INS-32025问题

解决 INS -32018 问题

注意 INS-32025 问题当INS-32018解决之后 INS-32025问题就被解决了。

更改环境变量

Edit the response file and remove the quotes around ORACLE_HOME and ORACLE_BASE variables
ORACLE_BASE=/u01/oracle/product
ORACLE_HOME=/u01/oracle/product/11gR2_database

- retry the installation

下次要安装11gr2版本环境变量全部设置成下面的目录

vim /home/oracle/.bash_profile

exportORACLE_BASE=/u01/oracle

exportGRID_HOME=/u01/grid/11g

exportDB_HOME=/u01/oracle/11g

exportORACLE_HOME=/u01/oracle/product/

PATH=$GRID_HOME/bin:/bin:/usr/bin:/usr/local/bin:/usr/sbin:$HOME/bin

export PATH

alias db=". ~/.db"

alias asm=". ~/.bash_profile"

INS-32025 问题

INS-32025 while installing 11.2 examples (文档 ID 1325924.1)

转到底部

In this Document

APPLIES TO:

Oracle Database - Enterprise Edition - Version 11.2.0.1 and later
Oracle Universal Installer - Version 11.2.0.1 and later
Oracle Database - Standard Edition - Version 11.2.0.1 and later
Information in this document applies to any platform.

SYMPTOMS

Installation of 11.2 Examples into an 11.2 ORACLE_HOME fails with:

INFO: Launching Oracle Examples Installer

SEVERE: [FATAL] [INS-32025] The chosen installation conflicts with software already installed in the given Oracle home.
CAUSE: The chosen installation conflicted with software already installed in the given Oracle home.
ACTION: Install into a different Oracle home.
SEVERE: [FATAL] [INS-52001] Oracle Database Examples can only be installed into an existing Oracle Home.
CAUSE: Oracle Database Examples can only be installed into an existing Oracle Home.
ACTION: Oracle Database Examples can only be installed into an existing Oracle Home.

CAUSE

The cause is "/" after ORACLE_HOME location.
In this case

/oracle/product/11.2.0/

was entered for ORACLE_HOME.

SOLUTION

1. Remove the trailing "/" (slash) from the the ORACLE_HOME path when prompted to enter this information, for example:

change

/oracle/product/11.2.0/
to

/oracle/product/11.2.0

第二种方法

分别使用grid用户和oracle用户

oracle用户

export ORACLE_SID=sdb

export ORACLE_UNQNAME=sdb

export JAVA_HOME=/usr/local/java

export ORACLE_BASE=/u01/oracle

export ORACLE_HOME=$ORACLE_BASE/11.2.3

export OGG_HOME=$ORACLE_BASE/ogg

export LD_LIBRARY_PATH=$ORACLE_HOME/lib

export ORACLE_TERM=xterm

export NLS_DATE_FORMAT="YYYY:MM:DD HH24:MI:SS"

export NLS_LANG=AMERICAN_AMERICA.AL32UTF8

export TNS_ADMIN=$ORACLE_HOME/network/admin

export ORA_NLS11=$ORACLE_HOME/nls/data

PATH=.:${JAVA_HOME}/bin:${PATH}:$HOME/bin:$ORACLE_HOME/bin:$ORA_CRS_HOME/bin

PATH=${PATH}:/usr/bin:/bin:/usr/bin/X11:/usr/local/bin

export PATH

LD_LIBRARY_PATH=$ORACLE_HOME/lib

LD_LIBRARY_PATH=${LD_LIBRARY_PATH}:$ORACLE_HOME/oracm/lib

LD_LIBRARY_PATH=${LD_LIBRARY_PATH}:/lib:/usr/lib:/usr/local/lib

export LD_LIBRARY_PATH

CLASSPATH=$ORACLE_HOME/JRE

CLASSPATH=${CLASSPATH}:$ORACLE_HOME/jlib

CLASSPATH=${CLASSPATH}:$ORACLE_HOME/rdbms/jlib

CLASSPATH=${CLASSPATH}:$ORACLE_HOME/network/jlib

export CLASSPATH

export THREADS_FLAG=native

export TEMP=/tmp

export TMPDIR=/tmp

umask 022

alias sqlplus='rlwrap sqlplus'

alias ggsci='rlwrap ggsci'

grid用户

#export ORACLE_SID=+ASM1

export ORACLE_BASE=/u01/grid

export ORACLE_HOME=/u01/app/grid/11.2.3

export PATH=$ORACLE_HOME/bin:$PATH:/usr/local/bin/:.

export TEMP=/tmp

export TMP=/tmp

export TMPDIR=/tmp

umask 022

alias sqlplus='rlwrap sqlplus'

mkdir -p /u01/grid/11g/

[root@localhost ~]# mkdir -p /u01/grid

[root@localhost ~]# mkdir -p /u01/oracle/11g

[root@localhost ~]# mkdir -p /u01/app/grid

[root@localhost ~]# chown grid.oinstall /u01/app/grid/

[root@localhost ~]# chmod -R 775 /u01

chown oracle.oinstall /u01/oracle

grid用户
这里我不掩饰了。

grid软件

这里检查失踪报错。

原因就是在 /etc/security/limits.conf 文件中

配置 grid 和oracle用户

#==============add =================

#grid

grid soft nproc 8188

grid hard nproc 65536

grid soft nofile 4096

grid hard nofile 262144

#oracle

oracle soft nproc 8188

oracle hard nproc 65536

oracle soft nofile 4096

oracle hard nofile 262144

上面是配置单实例下面是集群

ASM磁盘组

oracle用户：

oracle软件

oracle数据库

开始配置 GG

源、目标数据库上target

oracle用户

mkdir erp

1 创建表空间

CREATE TABLESPACE erp000 DATAFILE '/home/oracle/erp/erp00.dbf' SIZE 100M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED;

CREATE TABLESPACE erp001 DATAFILE '/home/oracle/erp/erp01.dbf' SIZE 100M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED;

2 创建用户

CREATE USER erp000 IDENTIFIED BY erp000 DEFAULT TABLESPACE erp000 QUOTA UNLIMITED ON erp000;

CREATE USER erp001 IDENTIFIED BY erp001 DEFAULT TABLESPACE erp001 QUOTA UNLIMITED ON erp001;

3 授权

grant resource，connect to erp000

grant resource，connect to erp001

grid 管理的是listener.ora

oracle 用户管理的是 tnsnames.ora

注意：

注意：用INITIAL EXTRACT进行一次性抽取初始化数据时，目标库的replicat进程不用手动启动，只要源库的extract进程start以后，自动会同步到目标库，而且此时用info all是看不到extract和replicat进程的，只能看见mgr进程

----------------------------------

总结：

周玲的那个文档无法做出来不要用那个文档没有任何意思。

GoldenGate介绍

2012-06-20 11:41:08

标签：ogg oracle goldengate

Oracle Golden Gate软件是一种基于日志的结构化数据复制备份软件，它通过解析源数据库在线日志或归档日志获得数据的增量变化，再将这些变化应用到目标数据库，从而实现源数据库与目标数据库同步。Oracle Golden Gate可以在异构的IT基础结构（包括几乎所有常用操作系统平台和数据库平台）之间实现大量数据亚秒一级的实时复制,从而在可以在应急系统、在线报表、实时数据仓库供应、交易跟踪、数据同步、集中/分发、容灾、数据库升级和移植、双业务中心等多个场景下应用。同时，Oracle Golden Gate可以实现一对一、广播(一对多)、聚合(多对一)、双向、点对点、级联等多种灵活的拓扑结构。

和传统的逻辑复制一样，Oracle GoldenGate实现原理是通过抽取源端的redo log或者archive log，然后通过TCP/IP投递到目标端，最后解析还原应用到目标端，使目标端实现同源端数据同步。以下是OracleGoldenGate的技术架构

1:Manager进程:
Manager进程是GoldenGate的控制进程，运行在源端和目标端上。它主要作用有以下几个方面：启动、监控、重启Goldengate的其他进程，报告错误及事件，分配数据存储空间，发布阀值报告等。

2:Extract进程:
Extract运行在数据库源端，负责从源端数据表或者日志中捕获数据。

3:Pump进程:
pump进程运行在数据库源端。其作用是如果源端使用了本地的trail文件，那么pump进程就会把trail以数据块的形式通过TCP/IP协议发送到目标端，这通常也是推荐的方式。pump进程本质是extract进程的一种特殊形式，如果不使用trails文件，那么就是extract进程在抽取完数据以后，直接投递到目标端。

4.Trail文件:
为了更有效、更安全的把数据库事务信息从源端投递到目标端。GoldenGate引进trail文件的概念。前面提到extract抽取完数据以后Goldengate会将抽取的事务信息转化为一种GoldenGate专有格式的文件。然后pump负责把源端的trail文件投递到目标端，所以源、目标两端都会存在这种文件。trail文件存在的目的旨在防止单点故障，将事务信息持久化，并且使用checkpoint机制来记录其读写位置，如果故障发生，则数据可以根据checkpoint记录的位置来重传。

5.Replicat进程:
Replicat进程，通常我们也把它叫做应用进程。运行在目标端，是数据传递的最后一站，负责读取目标端trail文件中的内容，并将其解析为DML或DDL语句，然后应用到目标数据库中。

6.GGSCI:
GGSCI是GoldenGate Software Command Interface 的缩写，它提供了十分丰富的命令来对Goldengate进行各种操作，如创建、修改、监控GoldenGate进程等等

GoldenGate应用的拓展结构、支持平台和数据库

第一步：

GoldenGate单向复制配置

2012-06-20 14:21:02

标签：extract 单向复制 ogg oracle

本文中将演示下使用ogg在两台oracle 10g数据库服务器间实现单向复制的配置！

一：环境介绍
db1：source端
ip地址：192.168.123.10
数据库版本：10.2.0.1 64 bit
操作系统版本：centos 5.4 64 bit
ogg版本：fbo_ggs_Linux_x64_ora10g_64bit.tar

db2: target端
ip地址：192.168.123.20
数据库版本：10.2.0.1 64 bit
操作系统版本：centos 5.4 64 bit
ogg版本：fbo_ggs_Linux_x64_ora10g_64bit.tar

二：准备工作，在source和target端都配置
1：配置环境变量和tnsnames.ora文件

1 [oracle@db1 ~]$ tail .bash_profile

2 export ORACLE_SID=db1

3 export ORACLE_BASE=/u01/app/oracle

4 export ORACLE_HOME=$ORACLE_BASE/product/10.2.0/db1

5 export PATH=$ORACLE_HOME/bin:$PATH

6 export NLS_LANG=AMERICAN_AMERICA.AL32UTF8

7 export NLS_DATE_FORMAT='yyyy-mm-dd-hh24:mi:ss'

8 export EDITOR=vim

9 export TNS_ADMIN=$ORACLE_HOME/network/admin

10 export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib

11 export GGATE=$ORACLE_BASE/ogg

13 [oracle@db1 ~]$ source .bash_profile

15 [oracle@db1 ~]$ cat $TNS_ADMIN/tnsnames.ora

16 DB1 =

17 (DESCRIPTION =

18 (ADDRESS = (PROTOCOL = TCP)(HOST = db1)(PORT = 1521))

19 (CONNECT_DATA =

20 (SERVER = DEDICATED)

21 (SERVICE_NAME = db1)

22 )

23 )

25 DB2 =

26 (DESCRIPTION =

27 (ADDRESS = (PROTOCOL = TCP)(HOST = db2)(PORT = 1521))

28 (CONNECT_DATA =

29 (SERVER = DEDICATED)

30 (SERVICE_NAME = db2)

31 )

32 )

2：确定数据库运行在归档模式，开启数据库附加日志，打开force logging,创建用于复制的数据库账号ogg，为了方便，这里直接赋予dba权限，如果对数据库安全要求高，可以去查询ogg文档，赋予复制需要的最小权限！(source和target端做相同的操作)

33 [oracle@db1 ~]$ sqlplus /nolog

34 SQL> conn /as sysdba

35 Connected.

36 SQL> archive log list;

37 Database log mode Archive Mode

38 Automatic archival Enabled

39 Archive destination USE_DB_RECOVERY_FILE_DEST

40 Oldest online log sequence 1

41 Next log sequence to archive 2

42 Current log sequence 2

44 SQL> select supplemental_log_data_min from v$database;

46 SUPPLEME

47 --------

48 NO

50 SQL> alter database add supplemental log data;

51 Database altered.

53 SQL> select supplemental_log_data_min from v$database;

55 SUPPLEME

56 --------

57 YES

59 SQL> alter database force logging;

60 Database altered.

62 SQL> create tablespace tbs_ogg;

63 Tablespace created.

65 SQL> create user ogg identified by ogg default tablespace tbs_ogg temporary tablespace temp account unlock;

66 User created.

68 SQL> grant connect,resource,dba to ogg;

69 Grant succeeded

备注：在oracle中可以通过rowid来定位某条记录，但是目标端的数据库和源端数据库的数据库可能完全不一样，所以无法通过rowid来确定源端数据库的逻辑变化，这时附加日志supplemental log便登上了表演的舞台。数据库在开启附加日志功能后，对于源端的修改操作，oracle会同时追加能够唯一标示记录的列到redo log。这样目标端数据库就可以知道源端发生了哪些具体的变化。

三：安装ogg软件，启动mgr管理进程，source和target端做相同的操作

70 [oracle@db1 ~]$ mkdir $GGATE

71 [oracle@db1 ~]$ cd $GGATE

72 [oracle@db1 ogg]$ tar -xvf /home/oracle/fbo_ggs_Linux_x64_ora10g_64bit.tar

74 [oracle@db1 ogg]$ ./ggsci

75 GGSCI (db1) 1> create subdirs

77 Creating subdirectories under current directory /u01/app/oracle/ogg

79 Parameter files /u01/app/oracle/ogg/dirprm: already exists

80 Report files /u01/app/oracle/ogg/dirrpt: created

81 Checkpoint files /u01/app/oracle/ogg/dirchk: created

82 Process status files /u01/app/oracle/ogg/dirpcs: created

83 SQL script files /u01/app/oracle/ogg/dirsql: created

84 Database definitions files /u01/app/oracle/ogg/dirdef: created

85 Extract data files /u01/app/oracle/ogg/dirdat: created

86 Temporary files /u01/app/oracle/ogg/dirtmp: created

87 Stdout files /u01/app/oracle/ogg/dirout: created

89 GGSCI (db1) 2> edit params mgr

91 GGSCI (db1) 3> view params mgr

92 PORT 7809

94 GGSCI (db1) 4> start mgr

95 Manager started.

97 GGSCI (db1) 5> info mgr

98 Manager is running (IP port db1.7809).

四：准备测试用户和表

99 SQL> conn /as sysdba

100 Connected.

101

102 SQL> alter user hr identified by hr account unlock;

103 User altered.

104

105 SQL> grant connect,resource,select_catalog_role to hr;

106 Grant succeeded.

107

108 SQL> conn hr/hr

109 Connected.

110

111 SQL> create table t1 as select * from dba_objects;

112 Table created.

113

114 SQL> alter table t1 add constraint pk_t1 primary key(object_id);

115 Table altered.

116

117 SQL> select count(*) from t1; //source端

118

119 COUNT(*)

120 ----------

121 50315

122

123 SQL> select count(*) from t1; //target端，只复制表定义，不填充数据

124

125 COUNT(*)

126 ----------

127 0

五:初始化加载数据,在异构数据库平台(例如oracle-mysql)，这个功能显得非常的有用！而在oracle-oracle的数据复制条件下，oracle推荐使用expdp/impdp工具

1.source端添加extract进程

128 GGSCI (db1) 1> add extract einig1,sourceistable //sourceistable代表直接从表中读取数据

129 EXTRACT added.

130

131 GGSCI (db1) 2> edit params einig1 //einig1代表extract initial load group 1缩写

132

133 GGSCI (db1) 3> view params einig1

134 extract einig1

135 setenv (NLS_LANG=AMERICAN_AMERICA.AL32UTF8)

136 userid ogg,password ogg

137 rmthost 192.168.123.20,mgrport 7809

138 rmttask replicat,group rinig1

139 table hr.t1;

2.target端添加replicat进程

140 GGSCI (db2) 1> add replicat rinig1,specialrun //specialrun代表只运行一次

141 REPLICAT added.

142

143 GGSCI (db2) 2> edit params rinig1 //rinig1代表replicat initial load group 1缩写

144

145 GGSCI (db2) 3> view params rinig1 //rinig1的名字必须同source端定义的group名字相同

146 replicat rinig1

147 setenv (NLS_LANG=AMERICAN_AMERICA.AL32UTF8)

148 assumetargetdefs

149 userid ogg,password ogg

150 discardfile ./dirrpt/rinig1.dsc,purge

151 map hr.*,target hr.*;

3.source端启动extract进程，查看日志输出

152 GGSCI (db1) 4> start extract einig1

153

154 Sending START request to MANAGER ...

155 EXTRACT EINIG1 starting

GGSCI (db1) 5> view report einig1

2012-06-20 09:40:55  INFO    OGG-01017  Wildcard resolution set to IMMEDIATE because SOURCEISTABLE is used.
***********************************************************************
                 Oracle GoldenGate Capture for Oracle
    Version 11.2.1.0.1 OGGCORE_11.2.1.0.1_PLATFORMS_120423.0230_FBO
   Linux, x64, 64bit (optimized), Oracle 10g on Apr 23 2012 07:44:10

Copyright (C) 1995, 2012, Oracle and/or its affiliates. All rights reserved.

                    Starting at 2012-06-20 09:40:55
***********************************************************************
Operating System Version:
Linux
Version #1 SMP Thu Sep 3 03:28:30 EDT 2009, Release 2.6.18-164.el5
Node: db1
Machine: x86_64
                         soft limit   hard limit
Address Space Size   :    unlimited    unlimited
Heap Size            :    unlimited    unlimited
File Size            :    unlimited    unlimited
CPU Time             :    unlimited    unlimited

Process id: 26185

Description:

***********************************************************************
** Running with the following parameters **
***********************************************************************

2012-06-20 09:40:55 INFO OGG-03035 Operating system character set identified as UTF-8. Locale: en_US, LC_ALL:
.
extract einig1
setenv (NLS_LANG=AMERICAN_AMERICA.AL32UTF8)
Set environment variable (NLS_LANG=AMERICAN_AMERICA.AL32UTF8)
userid ogg,password ***
rmthost 192.168.123.20,mgrport 7809
rmttask replicat,group rinig1
table hr.t1;
Using the following key columns for source table HR.T1: OBJECT_ID.

2012-06-20 09:40:59  INFO    OGG-01815  Virtual Memory Facilities for: COM
    anon alloc: mmap(MAP_ANON)  anon free: munmap
    file alloc: mmap(MAP_SHARED)  file free: munmap
    target directories:
    /u01/app/oracle/ogg/dirtmp.

CACHEMGR virtual memory values (may have been adjusted)
CACHESIZE:                               64G
CACHEPAGEOUTSIZE (normal):                8M
PROCESS VM AVAIL FROM OS (min):         128G
CACHESIZEMAX (strict force to disk):     96G

Database Version:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bi
PL/SQL Release 10.2.0.1.0 - Production
CORE 10.2.0.1.0 Production
TNS for Linux: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production

Database Language and Character Set:
NLS_LANG         = "AMERICAN_AMERICA.AL32UTF8"
NLS_LANGUAGE     = "AMERICAN"
NLS_TERRITORY    = "AMERICA"
NLS_CHARACTERSET = "AL32UTF8"

Processing table HR.T1

***********************************************************************
* ** Run Time Statistics ** *
***********************************************************************

Report at 2012-06-20 09:42:26 (activity since 2012-06-20 09:40:59)

Output to rinig1:

From Table HR.T1:
       #                   inserts:     50315
       #                   updates:         0
       #                   deletes:         0
       #                  discards:         0

REDO Log Statistics
Bytes parsed 0
Bytes output 13168227

4：target端验证

156 [oracle@db2 ogg]$ sqlplus hr/hr

157 SQL> select count(*) from t1;

158

159 COUNT(*)

160 ----------

161 50315

六：配置db1,db2间的实时同步复制
1：在source上配置extract进程，进程的名字不能超过8个字符

162 GGSCI (db1) 1> edit params eora_t1

163

164 GGSCI (db1) 2> view params eora_t1

165 extract eora_t1

166 setenv (NLS_LANG=AMERICAN_AMERICA.AL32UTF8)

167 userid ogg,password ogg

168 exttrail ./dirdat/aa

169 table hr.*;

2：开启hr用户下所有表的附加日志

170 GGSCI (db1) 3> dblogin userid ogg, password ogg

171 Successfully logged into database.

172

173 GGSCI (db1) 4> add trandata hr.*

3：添加extract进程，添加trail文件，文件名前缀不能超过2个字符

174 GGSCI (db1) 5> add extract eora_t1,tranlog,begin now

175 EXTRACT added.

176

177 GGSCI (db1) 6> add exttrail ./dirdat/aa,extract eora_t1,megabytes 100 //添加trail文件

178 EXTTRAIL added.

179

180 GGSCI (db1) 7> start extract eora_t1

181

182 Sending START request to MANAGER ...

183 EXTRACT EORA_T1 starting

184

185 GGSCI (db1) 8> info extract eora_t1

186

187 EXTRACT EORA_T1 Last Started 2012-06-20 10:06 Status RUNNING

188 Checkpoint Lag 00:00:00 (updated 00:00:02 ago)

189 Log Read Checkpoint Oracle Redo Logs

190 2012-06-20 10:06:36 Seqno 3, RBA 21804544

191 SCN 0.562134 (562134)

192

193 GGSCI (db1) 9> info all

194 Program Status Group Lag at Chkpt Time Since Chkpt

195

196 MANAGER RUNNING

197 EXTRACT RUNNING EORA_T1 00:08:24 00:00:05

4：添加pump进程

198 GGSCI (db1) 10> edit params pora_t1

199

200 GGSCI (db1) 11> view params pora_t1

201 extract pora_t1

202 setenv (NLS_LANG=AMERICAN_AMERICA.AL32UTF8)

203 passthru

204 rmthost 192.168.123.20,mgrport 7809

205 rmttrail ./dirdat/pa

206 table hr.*;

207

208 GGSCI (db1) 12> add extract pora_t1,exttrailsource ./dirdat/aa //这里aa文件名同前面extract进程参数文件中定义的trail文件名一

209

210 致

211 EXTRACT added.

212

213 GGSCI (db1) 13> add rmttrail ./dirdat/pa,extract pora_t1,megabytes 100 //添加传输到target数据库的trail问文件名，应该同参数文

214

215 件中描述的一致

216 RMTTRAIL added.

217

218 GGSCI (db1) 14> start extract pora_t1

219 Sending START request to MANAGER ...

220 EXTRACT PORA_T1 starting

221

222 GGSCI (db1) 15> info all

223 Program Status Group Lag at Chkpt Time Since Chkpt

224

225 MANAGER RUNNING

226 EXTRACT RUNNING EORA_T1 00:00:00 00:00:02

227 EXTRACT RUNNING PORA_T1 00:00:00 00:00:22

4：在target端添加检查表，配置replicat进程

228 GGSCI (db2) 1> edit params ./GLOBALS

229 GGSCI (db2) 2> view params ./GLOBALS

230 checkpointtable ogg.ggschkpt

231

232 GGSCI (db2) 3> exit //这里需要退出ggsci终端

233 [oracle@db2 ~]$ sqlplus ogg/ogg

234 SQL> select tname from tab;

235 no rows selected

236

237 [oracle@db2 ogg]$ ggsci

238 GGSCI (db2) 1> dblogin userid ogg,password ogg

239 Successfully logged into database.

240

241 GGSCI (db2) 2> add checkpointtable

242 No checkpoint table specified, using GLOBALS specification (ogg.ggschkpt)...

243 Successfully created checkpoint table ogg.ggschkpt.

244

245 SQL> select tname from tab;

246

247 TNAME

248 ------------------------------

249 GGSCHKPT

250 GGSCHKPT_LOX

251

252 GGSCI (db2) 3> edit params rora_t1

253 GGSCI (db2) 4> view params rora_t1

254 replicat rora_t1

255 setenv (NLS_LANG=AMERICAN_AMERICA.AL32UTF8)

256 userid ogg,password ogg

257 handlecollisions

258 assumetargetdefs

259 discardfile ./dirrpt/rora_t1.dsc,purge

260 map hr.* ,target hr.*;

261

262 GGSCI (db2) 5> add replicat rora_t1,exttrail ./dirdat/pa

263 REPLICAT added.

264

265 GGSCI (db2) 6> start replicat rora_t1

266

267 Sending START request to MANAGER ...

268 REPLICAT RORA_T1 starting

269

270 GGSCI (db2) 7> info replicat rora_t1

271 REPLICAT RORA_T1 Last Started 2012-06-20 10:21 Status RUNNING

272 Checkpoint Lag 00:00:00 (updated 00:00:03 ago)

273 Log Read Checkpoint File ./dirdat/t1000000

274 First Record RBA 0

275

276 GGSCI (db2) 8> info all

277 Program Status Group Lag at Chkpt Time Since Chkpt

278

279 MANAGER RUNNING

280 REPLICAT RUNNING RORA_T1 00:00:00 00:00:05

备注：
在target端配置replicat进程之前，通常需要在目标端的数据库中创建一个checkpoint表，这个表是基于ogg checkpoint文件的，它记录了所有ogg可恢复的checkpoint以及sequence，这个操作不是必须的，但oracle强烈建议使用它，因为它可以使得checkpoint包含在replicat的事务中，保证了可以从各类失败场景中恢复！

七：测试同步
1：插入数据

281 [oracle@db1 ogg]$ sqlplus hr/hr

282 SQL> desc t1

283 Name Null? Type

284 ----------------------------------------- -------- ----------------------------

285 OWNER VARCHAR2(30)

286 OBJECT_NAME VARCHAR2(128)

287 SUBOBJECT_NAME VARCHAR2(30)

288 OBJECT_ID NOT NULL NUMBER

289 DATA_OBJECT_ID NUMBER

290 OBJECT_TYPE VARCHAR2(19)

291 CREATED DATE

292 LAST_DDL_TIME DATE

293 TIMESTAMP VARCHAR2(19)

294 STATUS VARCHAR2(7)

295 TEMPORARY VARCHAR2(1)

296 GENERATED VARCHAR2(1)

297 SECONDARY VARCHAR2(1)

298

299 SQL> select max(object_id) from t1;

300

301 MAX(OBJECT_ID)

302 --------------

303 52504

304

305 SQL> insert into t1 (object_id,object_name) values (52505,'ogg_test');

306 1 row created.

307

308 SQL> commit;

309 Commit complete.

310

311 SQL> conn hr/hr@db2

312 Connected.

313 SQL> select max(object_id) from t1;

314

315 MAX(OBJECT_ID)

316 --------------

317 52505

2：抽取trail文件中可打印的内容分析

318 [root@db1 dirdat]# pwd

319 /u01/app/oracle/ogg/dirdat

320 [root@db1 dirdat]# strings aa000000

321 uri:db1::u01:app:oracle:ogg6

322 ./dirdat/aa0000007

323 564200

324 Linux1

325 db12

326 2.6.18-164.el53

327 "#1 SMP Thu Sep 3 03:28:30 EDT 20094

328 x86_642

329 DB12

330 db13

331 Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bi

332 PL/SQL Release 10.2.0.1.0 - Production

333 CORE 10.2.0.1.0 Production

334 TNS for Linux: Version 10.2.0.1.0 - Production

335 NLSRTL Version 10.2.0.1.0 - Production

336 EORA_T11

337 ?Version 11.2.1.0.1 OGGCORE_11.2.1.0.1_PLATFORMS_120423.0230_FBO4

338 HR.T1

339 ogg_test

340 52505

341 1900-01-01:00:00:00

342 1900-01-01:00:00:00

343 AAAM0YAAEAAAARlAAA

344 5642006

345 2.46.299Z

346

347 [root@db2 dirdat]# pwd

348 /u01/app/oracle/ogg/dirdat

349 [root@db2 dirdat]# strings pa000000

350 uri:db1::u01:app:oracle:ogg5

351 uri:db1::u01:app:oracle:ogg6

352 ./dirdat/pa0000007

353 Linux1

354 db12

355 2.6.18-164.el53

356 "#1 SMP Thu Sep 3 03:28:30 EDT 20094

357 x86_642

358 DB12

359 db13

360 Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bi

361 PL/SQL Release 10.2.0.1.0 - Production

362 CORE 10.2.0.1.0 Production

363 TNS for Linux: Version 10.2.0.1.0 - Production

364 NLSRTL Version 10.2.0.1.0 - Production

365 EORA_T11

366 ?Version 11.2.1.0.1 OGGCORE_11.2.1.0.1_PLATFORMS_120423.0230_FBO4

367 HR.T1

368 ogg_test

369 52505

370 1900-01-01:00:00:00

371 1900-01-01:00:00:00

372 AAAM0YAAEAAAARlAAA

373 5642006

374 2.46.299Z

3：删除测试

375 SQL> conn hr/hr

376 Connected.

377 SQL> delete from t1 where object_id > 1000;

378 49362 rows deleted.

379

380 SQL> commit;

381 Commit complete

382

383 SQL> select max(object_id) from t1;

384

385 MAX(OBJECT_ID)

386 --------------

387 1000

388

389 SQL> conn hr/hr@db2

390 Connected.

391 SQL> select max(object_id) from t1;

392

393 MAX(OBJECT_ID)

394 --------------

395 1000

第三步:

GoldenGate单向复制配置(支持DDL复制)

2012-06-21 09:05:27

标签：ogg ddl replication oracle goldengate

在上一篇文件中的OGG单向复制配置不支持DLL的同步，只支持DML，因而本文在之前的基础上增加对DDL语句的复制，下面是简要配置过程记录！

一：验证之前的配置不支持DDL复制，这里在source端，新建一张表，发现无法复制到target端！target端也新建相同的表后，DML操作可以成功复制

396 [root@db1 ~]# su - oracle

397 [oracle@db1 ~]$ sqlplus hr/hr

398 SQL> create table t2 (id number primary key,name varchar2(20));

399 Table created.

400 SQL> conn hr/hr@db2

401 Connected.

402 SQL> select tname from tab where tname='T2';

403 no rows selected

404

405 SQL> create table t2 (id number primary key,name varchar2(20));

406 Table created.

407

408 SQL> conn hr/hr

409 Connected.

410 SQL> insert into t2 values (1,'one');

411 1 row created.

412

413 SQL> commit;

414 Commit complete.

415

416 SQL> conn hr/hr@db2

417 Connected.

418 SQL> select * from t2;

419

420 ID NAME

421 ---------- --------------------

422 1 one

二：开始配置OGG支持DDL复制(在source端操作)
1：赋予ogg用户相应的权限，修改全局配置文件添加ggschema参数

423 SQL> conn /as sysdba

424 Connected.

425 SQL> grant execute on utl_file to ogg;

426 Grant succeeded.

427

428 [oracle@db1 ~]$ cd $GGATE

429 [oracle@db1 ogg]$ ggsci

430 GGSCI (db1) 1> edit param ./GLOBALS

431

432 GGSCI (db1) 2> view param ./GLOBALS

433 ggschema ogg

2：运行相关的sql脚本

434 [oracle@db1 ~]$ cd $GGATE

435 [oracle@db1 ogg]$ sqlplus /nolog

436 SQL> conn /as sysdba

437 Connected.

438 SQL> @marker_setup.sql

439

440 Marker setup script

441 You will be prompted for the name of a schema for the Oracle GoldenGate database objects.

442 NOTE: The schema must be created prior to running this script.

443 NOTE: Stop all DDL replication before starting this installation.

444

445 Enter Oracle GoldenGate schema name:ogg

446

447 Marker setup table script complete, running verification script...

448 Please enter the name of a schema for the GoldenGate database objects:

449 Setting schema name to OGG

450

451 MARKER TABLE

452 -------------------------------

453 OK

454

455 MARKER SEQUENCE

456 -------------------------------

457 OK

458

459 Script complete.

460

461 SQL> show parameter recyclebin;

462

463 NAME TYPE VALUE

464 ------------------------------------ ----------- ------------------------------

465 recyclebin string on

466

467 SQL> alter system set recyclebin=off;

468 System altered.

469

470 SQL> show parameter recyclebin;

471

472 NAME TYPE VALUE

473 ------------------------------------ ----------- ------------------------------

474 recyclebin string OFF

475

476 SQL> @ddl_setup.sql

477 Oracle GoldenGate DDL Replication setup script

478 Verifying that current user has privileges to install DDL Replication...

479 You will be prompted for the name of a schema for the Oracle GoldenGate database objects.

480 NOTE: For an Oracle 10g source, the system recycle bin must be disabled. For Oracle 11g and later, it can be enabled.

481 NOTE: The schema must be created prior to running this script.

482 NOTE: Stop all DDL replication before starting this installation.

483 Enter Oracle GoldenGate schema name:ogg

484

485 Working, please wait ...

486 Spooling to file ddl_setup_spool.txt

487

488 Checking for sessions that are holding locks on Oracle Golden Gate metadata tables ...

489 Check complete.

490 Using OGG as a Oracle GoldenGate schema name.

491 Working, please wait ...

492 RECYCLEBIN must be empty.

493 This installation will purge RECYCLEBIN for all users.

494 To proceed, enter yes. To stop installation, enter no.

495

496 Enter yes or no:yes

497 ————————其他输出省略————————

498

499 STATUS OF DDL REPLICATION

500 ---------------------------------------------------------------------------------------

501 SUCCESSFUL installation of DDL Replication software components

502

503 Script complete.

504

505 SQL> @role_setup.sql

506 GGS Role setup script

507 This script will drop and recreate the role GGS_GGSUSER_ROLE

508 To use a different role name, quit this script and then edit the params.sql script to change the gg_role parameter to the preferred name. (Do not run the script.)

509

510 You will be prompted for the name of a schema for the GoldenGate database objects.

511 NOTE: The schema must be created prior to running this script.

512 NOTE: Stop all DDL replication before starting this installation.

513

514 Enter GoldenGate schema name:ogg

515 Wrote file role_setup_set.txt

516 PL/SQL procedure successfully completed.

517

518 Role setup script complete

519

520 Grant this role to each user assigned to the Extract, GGSCI, and Manager processes, by using the following SQL command:

521 GRANT GGS_GGSUSER_ROLE TO <loggedUser>

522 where <loggedUser> is the user assigned to the GoldenGate processes.

523

524 SQL> grant ggs_ggsuser_role to ogg;

525 Grant succeeded.

526

527 SQL> @ddl_enable.sql

528 Trigger altered.

529

530 SQL> @?/rdbms/admin/dbmspool.sql

531 Package created.

532 Grant succeeded.

533 View created.

534 Package body created.

535

536 SQL> @ddl_pin.sql ogg

537 PL/SQL procedure successfully completed.

538 PL/SQL procedure successfully completed.

539 PL/SQL procedure successfully completed.

3：source端修改extract进程的params文件，添加"ddl include all"参数，重启extract进程

540 GGSCI (db1) 1> view params eora_t1

541 extract eora_t1

542 setenv (NLS_LANG=AMERICAN_AMERICA.AL32UTF8)

543 ddl include all

544 userid ogg,password ogg

545 exttrail ./dirdat/aa

546 table hr.*;

547

548 GGSCI (db1) 2> stop extract eora_t1

549 Sending STOP request to EXTRACT EORA_T1 ...

550 Request processed.

551

552 GGSCI (db1) 3> start extract eora_t1

553 Sending START request to MANAGER ...

554 EXTRACT EORA_T1 starting

555

556 GGSCI (db1) 4> info extract eora_t1

557 EXTRACT EORA_T1 Last Started 2012-06-20 15:42 Status RUNNING

558 Checkpoint Lag 00:00:00 (updated 00:00:10 ago)

559 Log Read Checkpoint Oracle Redo Logs

560 2012-06-20 15:42:58 Seqno 3, RBA 50044416

561 SCN 0.567478 (567478)

4：target端修改replicat进程的params文件，添加"ddl include all"和"ddlerror default ignore retryop maxretries 3 retrydelay 5" 参数，重启replicat进程

562 [root@db2 ~]# su - oracle

563 [oracle@db2 ~]$ cd $GGATE

564 [oracle@db2 ogg]$ ggsci

565 GGSCI (db2) 1> edit params rora_t1

566 GGSCI (db2) 2> view params rora_t1

567 replicat rora_t1

568 setenv (NLS_LANG=AMERICAN_AMERICA.AL32UTF8)

569 ddl include all

570 ddlerror default ignore retryop maxretries 3 retrydelay 5

571 userid ogg,password ogg

572 handlecollisions

573 assumetargetdefs

574 discardfile ./dirrpt/rora_t1.dsc,purge

575 map hr.* ,target hr.*;

576

577 GGSCI (db2) 3> stop replicat rora_t1

578 Sending STOP request to REPLICAT RORA_T1 ...

579 Request processed.

580

581 GGSCI (db2) 4> start replicat rora_t1

582 Sending START request to MANAGER ...

583 REPLICAT RORA_T1 starting

584

585 GGSCI (db2) 5> info replicat rora_t1

586 REPLICAT RORA_T1 Last Started 2012-06-20 15:50 Status RUNNING

587 Checkpoint Lag 00:00:00 (updated 00:00:00 ago)

588 Log Read Checkpoint File ./dirdat/pa000000

589 First Record RBA 4780973

三：测试

590 [oracle@db1 ogg]$ sqlplus hr/hr

591 SQL> alter table t2 add location varchar2(200);

592 Table altered.

593

594 SQL> conn hr/hr@db2

595 Connected.

596 SQL> desc t2

597 Name Null? Type

598 ----------------------------------------- -------- ----------------------------

599 ID NOT NULL NUMBER

600 NAME VARCHAR2(20)

601 LOCATION VARCHAR2(200)

602

603 SQL> conn hr/hr

604 Connected.

605 SQL> create table t3 as select object_id,object_name from dba_objects;

606

607 Table created.

608

609 SQL> conn hr/hr@db2

610 Connected.

611 SQL> select tname from tab where tname='T3';

612

613 TNAME

614 ------------------------------

615 T3

616

617 SQL> desc t3;

618 Name Null? Type

619 ----------------------------------------- -------- ----------------------------

620 OBJECT_ID NUMBER

621 OBJECT_NAME VARCHAR2(128)

第4步骤：

简单配置GoldenGate双向复制

2012-06-26 09:40:30

标签：oracle ogg 双向复制 golden gate

在完成ogg的单向复制配置后，自然会想着向前推进一层，实现双向复制；在实际应用中，双向复制面临着许多问题，主要有如下几点：
1. 如果两个库同时更新同一条记录如何处理？
2. 如果网络出现失败如何处理？
3. 如果数据不同步后如何修复？

本文介绍如何在前文的基础上简单实现ogg的双向复制！双向复制一般用于双业务中心环境下，目前笔者的生产环境中未使用到ogg，ogg系列的文章只是从技术上提前做一个准备，因而许多问题的细节未能理清，后续将继续学习研究！在开始之前，请先配置好db1-db2的单向复制(include ddl replicat)! 参考：
http://ylw6006.blog.51cto.com/470441/903752
http://ylw6006.blog.51cto.com/470441/904373

一：配置db1，添加checkpoint表(本文db1和db2互为source和target，因而直接采用db1和db2来标识两台数据库服务器)

622 GGSCI (db1) 3> view params ./GLOBALS

623 ggschema ogg

624 checkpointtable ogg.ggschkpt

625

626 GGSCI (db1) 4> exit

627

628 [oracle@db1 ogg]$ ggsci

629 GGSCI (db1) 2> dblogin userid ogg,password ogg

630 Successfully logged into database.

631

632 GGSCI (db1) 3> add checkpointtable

633 No checkpoint table specified, using GLOBALS specification (ogg.ggschkpt)...

634 Successfully created checkpoint table ogg.ggschkpt.

二：配置db2,运行相关的脚本，支持DDL的复制

635 [oracle@db2 ogg]$ sqlplus /nolog

636 SQL> conn /as sysdba

637 Connected.

638 SQL> grant execute on utl_file to ogg;

639 Grant succeeded.

640

641 [oracle@db2 ogg]$ ggsci

642 GGSCI (db2) 1> view params ./GLOBALS

643 ggschema ogg

644 checkpointtable ogg.ggschkpt

645

646 [oracle@db2 ogg]$ sqlplus /nolog

647 SQL> conn /as sysdba

648 Connected.

649 SQL> @marker_setup.sql

650 SQL> @ddl_setup.sql

651 SQL> @role_setup.sql

652 SQL> grant ggs_ggsuser_role to ogg;

653 SQL> @ddl_enable.sql

654 SQL> @?/rdbms/admin/dbmspool.sql

655 SQL> @ddl_pin.sql ogg

三：db2上配置extract和pump进程

656 [oracle@db2 ogg]$ ggsci

657 GGSCI (db2) 1> dblogin userid ogg,password ogg

658 Successfully logged into database.

659

660 GGSCI (db2) 2> add trandata hr.*

661 GGSCI (db2) 5> view params eora_t2

662 extract eora_t2

663 setenv (NLS_LANG=AMERICAN_AMERICA.AL32UTF8)

664 userid ogg,password ogg

665 tranlogoptions excludeuser ogg //避免出现死循环复制，db1上的extract进程也需要进行此项设置

666 exttrail ./dirdat/ab

667 table hr.*;

668

669 GGSCI (db2) 6> add extract eora_t2,tranlog,begin now

670 EXTRACT added.

671

672 GGSCI (db2) 7> add exttrail ./dirdat/ab,extract eora_t2,megabytes 100

673 EXTTRAIL added.

674

675 GGSCI (db2) 8> start extract eora_t2

676 Sending START request to MANAGER ...

677 EXTRACT EORA_T2 starting

678

679 GGSCI (db2) 13> view params pora_t2

680 extract pora_t2

681 setenv (NLS_LANG=AMERICAN_AMERICA.AL32UTF8)

682 passthru

683 rmthost 192.168.123.10,mgrport 7809

684 rmttrail ./dirdat/pb

685 table hr.*;

686

687 GGSCI (db2) 14> add extract pora_t2,exttrailsource ./dirdat/ab

688 EXTRACT added.

689

690 GGSCI (db2) 15> add rmttrail ./dirdat/pb extract pora_t2,megabytes 100

691 RMTTRAIL added.

692

693 GGSCI (db2) 19> start extract pora_t2

694 Sending START request to MANAGER ...

695 EXTRACT PORA_T2 starting

696

697 GGSCI (db2) 20> info all

698 Program Status Group Lag at Chkpt Time Since Chkpt

699

700 MANAGER RUNNING

701 EXTRACT RUNNING EORA_T2 00:00:00 00:00:04

702 EXTRACT RUNNING PORA_T2 00:00:00 00:01:10

703 REPLICAT RUNNING RORA_T1 00:00:00 00:00:04

四：db1上配置replicat进程

704 GGSCI (db1) 7> view params rora_t2

705 replicat rora_t2

706 setenv (NLS_LANG=AMERICAN_AMERICA.AL32UTF8)

707 ddl include all

708 ddlerror default ignore retryop maxretries 3 retrydelay 5

709 userid ogg,password ogg

710 handlecollisions

711 assumetargetdefs

712 discardfile ./dirrpt/rora_t2.dsc,purge

713 map hr.* ,target hr.*;

714

715 GGSCI (db1) 1> add replicat rora_t2,exttrail ./dirdat/pb

716 REPLICAT added.

717

718 GGSCI (db1) 2> start replicat rora_t2

719 Sending START request to MANAGER ...

720 REPLICAT RORA_T2 starting

721

722 GGSCI (db1) 3> info all

723 Program Status Group Lag at Chkpt Time Since Chkpt

724

725 MANAGER RUNNING

726 EXTRACT RUNNING EORA_T1 00:00:00 00:00:10

727 EXTRACT RUNNING PORA_T1 00:00:00 00:00:06

728 REPLICAT RUNNING RORA_T2 00:00:00 00:00:05

五：测试

729 [oracle@db1 ~]$ sqlplus hr/hr@db1

730 SQL> col location for a20

731 SQL> select * from t2;

732

733 ID NAME LOCATION

734 ---------- -------------------- --------------------

735 2 two china

736 1 one

737

738 SQL> update t2 set location='america' where id=1;

739 1 row updated.

740

741 SQL> commit;

742 Commit complete.

743

744 SQL> select * from t2;

745

746 ID NAME LOCATION

747 ---------- -------------------- --------------------

748 2 two china

749 1 one america

750

751 SQL> conn hr/hr@db2

752 Connected.

753 SQL> select * from t2;

754

755 ID NAME LOCATION

756 ---------- -------------------- --------------------

757 2 two china

758 1 one american

759

760 SQL> insert into t2 values (3,'three','japan');

761 1 row created.

762

763 SQL> commit;

764 Commit complete.

765

766 SQL> conn hr/hr@db1

767 Connected.

768 SQL> select * from t2;

769

770 ID NAME LOCATION

771 ---------- -------------------- --------------------

772 2 two china

773 1 one america

774 3 three japan

775

776

777 SQL> alter table t2 add sex char(4);

778 Table altered.

779

780 SQL> desc t2

781 Name Null? Type

782 ----------------------------------------- -------- ----------------------------

783 ID NOT NULL NUMBER

784 NAME VARCHAR2(20)

785 LOCATION VARCHAR2(200)

786 SEX CHAR(4)

787

788 SQL> conn hr/hr@db2

789 Connected.

790

791 SQL> desc t2

792 Name Null? Type

793 ----------------------------------------- -------- ----------------------------

794 ID NOT NULL NUMBER

795 NAME VARCHAR2(20)

796 LOCATION VARCHAR2(200)

797 SEX CHAR(4)

好了上班的单实例与单实例就成功了

配置OGG RAC - --》知向单实例

第一步：配置网络监听

1.在RAC节点上配置ASM实例监听动态注册，确保goldengate用户能够连接到所有的ASM实例。

[grid@m1 ~]$ lsnrctl service

LSNRCTL for Linux: Version 11.2.0.3.0 - Production on 11-NOV-2013 14:35:41

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER)))

Services Summary...

Service "+ASM" has 1 instance(s).

Instance "+ASM1", status READY, has 1 handler(s) for this service...

Handler(s):

"DEDICATED" established:0 refused:0 state:ready

LOCAL SERVER

Service "mes" has 1 instance(s).

Instance "mes1", status READY, has 1 handler(s) for this service...

Handler(s):

"DEDICATED" established:0 refused:0 state:ready

LOCAL SERVER

The command completed successfully

[grid@m1 ~]$ cat /u01/oracle/

11.2.0/ admin/ cfgtoollogs/ checkpoints/ diag/

[grid@m1 ~]$ cat /u01/oracle/11.2.0/db_1/network/admin/

samples/ tnsnames1311048PM2637.bak

shrept.lst tnsnames.ora

[grid@m1 ~]$ cat /u01/

app/ gridbase/ gridsoft/ oracle/ oraInventory/

[grid@m1 ~]$ echo $ORACLE_HOME

/u01/app/grid/11.2.0

[grid@m1 ~]$ cat /u01/app/grid/11.2.0/network/admin/listener.ora

LISTENER=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER)))) # line added by Agent

LISTENER_SCAN1=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_SCAN1)))) # line added by Agent

ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER_SCAN1=ON # line added by Agent

ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER=ON # line added by Agent

[grid@m1 ~]$ cat /u01/app/grid/11.2.0/network/admin/endpoints_listener.ora

LISTENER_M1=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=m1-vip)(PORT=1521))(ADDRESS=(PROTOCOL=TCP)(HOST=172.16.15.101)(PORT=1521)(IP=FIRST)))) # line added by Agent

[grid@m1 ~]$ lsnrctl staus

LSNRCTL for Linux: Version 11.2.0.3.0 - Production on 11-NOV-2013 14:43:14

NL-00853: undefined command "staus". Try "help"

[grid@m1 ~]$ lsnrctl status

LSNRCTL for Linux: Version 11.2.0.3.0 - Production on 11-NOV-2013 14:43:18

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER)))

STATUS of the LISTENER

------------------------

Alias LISTENER

Version TNSLSNR for Linux: Version 11.2.0.3.0 - Production

Start Date 03-NOV-2013 17:53:11

Uptime 7 days 20 hr. 50 min. 7 sec

Trace Level off

Security ON: Local OS Authentication

SNMP OFF

Listener Parameter File /u01/app/grid/11.2.0/network/admin/listener.ora

Listener Log File /u01/gridbase/diag/tnslsnr/m1/listener/alert/log.xml

Listening Endpoints Summary...

(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER)))

(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=172.16.15.101)(PORT=1521)))

(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=172.16.15.104)(PORT=1521)))

Services Summary...

Service "+ASM" has 1 instance(s).

Instance "+ASM1", status READY, has 1 handler(s) for this service...

The command completed successfully

[grid@m1 ~]$

以上listener.ora配置文件中的信息是Grid Infrastructure安装过程中Agent自行添加的(During the Grid Infrastructure installation, the (default) node VIP listener is always created referencing the public network)，比较难以理解的可能是LISTENER仅指定了PROTOCOL=IPC的信息，而没有指定监听的地址、端口等信息。

11g 变了增加了 lsnrctl实例

实际上11.2 GI的LISTENER 监听器配置默认受到11.2新引入的endpoints_listener.ora配置文件的管理：

[grid@m1 admin]$ more listener.ora

LISTENER=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER)))) # line added by Agent

LISTENER_SCAN1=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_SCAN1)))) # line added by Agent

ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER_SCAN1=ON # line added by Agent

ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER=ON # line added by Agent

[grid@m1 admin]$ lsnrctl status listener

LSNRCTL for Linux: Version 11.2.0.3.0 - Production on 11-NOV-2013 14:57:22

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER)))

STATUS of the LISTENER

------------------------

Alias LISTENER

Version TNSLSNR for Linux: Version 11.2.0.3.0 - Production

Start Date 03-NOV-2013 17:53:11

Uptime 7 days 21 hr. 4 min. 10 sec

Trace Level off

Security ON: Local OS Authentication

SNMP OFF

Listener Parameter File /u01/app/grid/11.2.0/network/admin/listener.ora

Listener Log File /u01/gridbase/diag/tnslsnr/m1/listener/alert/log.xml

Listening Endpoints Summary...

(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER)))

(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=172.16.15.101)(PORT=1521)))

(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=172.16.15.104)(PORT=1521)))

Services Summary...

Service "+ASM" has 1 instance(s).

Instance "+ASM1", status READY, has 1 handler(s) for this service...

The command completed successfully

[grid@m1 admin]$

[grid@m1 admin]$ more endpoints_listener.ora

LISTENER_M1=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=m1-vip)(PORT=1521))(ADDRESS=(PROTOCOL=TCP)(HOST=172.16.15.101)(PORT=1521)(IP=FIR

ST)))) # line added by Agent

1 netca

选择默认公网网段

上面添加监听就可以了 MACLEAN_LISTENER ----动态的方式添加的。

注意：上面是添加监听下面必须要整为静态注册，上面也是grid用户。

su - grid

netmgr

记住保存一下

之后退出就可以了点击 EXIT 。

之后使用命令重新启动一下就可乐就可以了。

[grid@vrh1 admin]$ crsctl status res ora.MACLEAN_LISTENER.lsnr

NAME=ora.MACLEAN_LISTENER.lsnr

TYPE=ora.listener.type

TARGET=ONLINE , ONLINE

STATE=ONLINE on vrh1, ONLINE on vrh2

[grid@vrh1 admin]$ crsctl stop res ora.MACLEAN_LISTENER.lsnr

CRS-2673: Attempting to stop 'ora.MACLEAN_LISTENER.lsnr' on 'vrh1'

CRS-2673: Attempting to stop 'ora.MACLEAN_LISTENER.lsnr' on 'vrh2'

CRS-2677: Stop of 'ora.MACLEAN_LISTENER.lsnr' on 'vrh1' succeeded

CRS-2677: Stop of 'ora.MACLEAN_LISTENER.lsnr' on 'vrh2' succeeded

[grid@vrh1 admin]$ crsctl start res ora.MACLEAN_LISTENER.lsnr

CRS-2672: Attempting to start 'ora.MACLEAN_LISTENER.lsnr' on 'vrh2'

CRS-2672: Attempting to start 'ora.MACLEAN_LISTENER.lsnr' on 'vrh1'

CRS-2676: Start of 'ora.MACLEAN_LISTENER.lsnr' on 'vrh1' succeeded

CRS-2676: Start of 'ora.MACLEAN_LISTENER.lsnr' on 'vrh2' succeeded

[grid@vrh1 admin]$ lsnrctl status MACLEAN_LISTENER

LSNRCTL for Linux: Version 11.2.0.3.0 - Production on 27-NOV-2011 11:00:42

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=MACLEAN_LISTENER)))

STATUS of the LISTENER

------------------------

Alias MACLEAN_LISTENER

Version TNSLSNR for Linux: Version 11.2.0.3.0 - Production

Start Date 27-NOV-2011 11:00:11

Uptime 0 days 0 hr. 0 min. 31 sec

Trace Level off

Security ON: Local OS Authentication

SNMP OFF

Listener Parameter File /g01/11.2.0/grid/network/admin/listener.ora

Listener Log File /g01/orabase/diag/tnslsnr/vrh1/maclean_listener/alert/log.xml

Listening Endpoints Summary...

(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=MACLEAN_LISTENER)))

(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.161)(PORT=1598)))

(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.162)(PORT=1598)))

Services Summary...

Service "VPROD" has 1 instance(s).

Instance "VPROD1", status UNKNOWN, has 1 handler(s) for this service...

The command completed successfully

检查一下：

[grid@m1 admin]$ lsnrctl services

LSNRCTL for Linux: Version 11.2.0.3.0 - Production on 12-NOV-2013 13:58:58

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER)))

Services Summary...

Service "+ASM" has 1 instance(s).

Instance "+ASM1", status READY, has 1 handler(s) for this service...

Handler(s):

"DEDICATED" established:0 refused:0 state:ready

LOCAL SERVER

Service "mes" has 1 instance(s).

Instance "mes1", status READY, has 1 handler(s) for this service...

Handler(s):

"DEDICATED" established:0 refused:0 state:ready

LOCAL SERVER

Service "mesXDB" has 1 instance(s).

Instance "mes1", status READY, has 1 handler(s) for this service...

Handler(s):

"D000" established:0 refused:0 current:0 max:1022 state:ready

DISPATCHER <machine: m1, pid: 8550>

(ADDRESS=(PROTOCOL=tcp)(HOST=m1)(PORT=58498))

The command completed successfully

如果数据文件是存放在ASM中，则还必须将ASM实例静态注册到监听器中，否则，在后续实验过程中会失败的

我们的实例是那么管理的所以，我们必须要静态注册

到这里也不要怕：

这里不能手动添加。

+ 报错无效的全局名字。

解决问题：

使用netca能够用得起。

但是到了netmgr哪里就会被卡主呵呵怎么解决呢？

使用srvctl 去解决这个问题。

下面是步骤

[grid@m1 admin]$ NEW_ASM_LISTENER

[grid@m1 admin]$ cat listener.ora

NEW_ASM_LISTENER=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=NEW_ASM_LISTENER)))) # line added by Agent

ASM_LISTENER=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=ASM_LISTENER)))) # line added by Agent

# listener.ora Network Configuration File: /u01/app/grid/11.2.0/network/admin/listener.ora

# Generated by Oracle configuration tools.

ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER_SCAN1 = ON

SID_LIST_MACLEAN_LISTENER =

(SID_LIST =

(SID_DESC =

(GLOBAL_DBNAME = mes)

(ORACLE_HOME = /u01/app/grid/11.2.0)

(SID_NAME = mes1)

)

SID_LIST_LISTENER =

(SID_LIST =

(SID_DESC =

(GLOBAL_DBNAME = +ASM)

(ORACLE_HOME = /u01/app/grid/11.2.0)

(SID_NAME = +ASM1)

)

LISTENER =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = IPC)(KEY = LISTENER))

)

ADR_BASE_LISTENER = /u01/gridbase

MACLEAN_LISTENER =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = IPC)(KEY = MACLEAN_LISTENER))

)

ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER = ON

ADR_BASE_MACLEAN_LISTENER = /u01/gridbase

LISTENER_SCAN1 =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = IPC)(KEY = LISTENER_SCAN1))

)

ENABLE_GLOBAL_DYNAMIC_ENDPOINT_MACLEAN_LISTENER = ON

ADR_BASE_LISTENER_SCAN1 = /u01/gridbase

ENABLE_GLOBAL_DYNAMIC_ENDPOINT_ASM_LISTENER=ON # line added by Agent

ENABLE_GLOBAL_DYNAMIC_ENDPOINT_NEW_ASM_LISTENER=ON # line added by Agent

[grid@m1 admin]$ vim listener.ora

[grid@m1 admin]$ pwd

/u01/app/grid/11.2.0/network/admin

[grid@m1 admin]$ srvctl stop listener -l NEW_MACLEAN_LISTENER

PRCR-1001 : Resource ora.NEW_MACLEAN_LISTENER.lsnr does not exist

[grid@m1 admin]$ srvctl stop listener -l NEW_ASM_LISTENER

[grid@m1 admin]$ srvctl start listener -l NEW_ASM_LISTENER

[grid@m1 admin]$ lsnrctl status NEW_ASM_LISTENER

LSNRCTL for Linux: Version 11.2.0.3.0 - Production on 12-NOV-2013 16:54:13

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=NEW_ASM_LISTENER)))

STATUS of the LISTENER

------------------------

Alias NEW_ASM_LISTENER

Version TNSLSNR for Linux: Version 11.2.0.3.0 - Production

Start Date 12-NOV-2013 16:53:47

Uptime 0 days 0 hr. 0 min. 26 sec

Trace Level off

Security ON: Local OS Authentication

SNMP OFF

Listener Parameter File /u01/app/grid/11.2.0/network/admin/listener.ora

Listener Log File /u01/app/grid/11.2.0/log/diag/tnslsnr/m1/new_asm_listener/alert/log.xml

Listening Endpoints Summary...

(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=NEW_ASM_LISTENER)))

(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=172.16.15.101)(PORT=1601)))

(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=172.16.15.104)(PORT=1601)))

Services Summary...

Service "+ASM" has 1 instance(s).

Instance "+ASM1", status UNKNOWN, has 1 handler(s) for this service...

The command completed successfully

[grid@m1 admin]$

2. 使用srvctl 工具添加监听并手动加入静态注册信息

检查默认network的network number,红色的数字

[grid@vrh1 admin]$ srvctl config network

Network exists: 1/192.168.1.0/255.255.255.0/eth0, type static

srvctl 添加监听的语法如下

[grid@vrh1 admin]$ srvctl add listener -h

Adds a listener configuration to the Oracle Clusterware.

Usage: srvctl add listener [-l <lsnr_name>] [-s] [-p "[TCP:]<port>[, ...][/IPC:<key>]

[/NMP:<pipe_name>][/TCPS:<s_port>] [/SDP:<port>]"] [-o <oracle_home>] [-k <net_num>]

-l <lsnr_name> Listener name (default name is LISTENER)

-o <oracle_home> ORACLE_HOME path (default value is CRS_HOME)

-k <net_num> network number (default number is 1)

-s Skip the checking of ports

-p "[TCP:]<port>[, ...][/IPC:<key>][/NMP:<pipe_name>][/TCPS:<s_port>] [/SDP:<port>]"

Comma separated tcp ports or listener endpoints

-h Print usage

[grid@vrh1 admin]$ srvctl add listener -l NEW_MACLEAN_LISTENER -o $CRS_HOME -p 1601 -k 1

-k 填入方才获得的network number，-p填入端口号，-l填入监听名，-o 填入GI HOME路径

[grid@vrh1 admin]$ srvctl start listener -l NEW_MACLEAN_LISTENER

srvctl start listener启动新添加的监听后listener.ora和endpoints_listener.ora会出现新的记录:

[grid@vrh1 admin]$ head -1 listener.ora

NEW_MACLEAN_LISTENER=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=NEW_MACLEAN_LISTENER))))

# line added by Agent

[grid@vrh1 admin]$ head -1 endpoints_listener.ora

NEW_MACLEAN_LISTENER_VRH1=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=vrh1-vip)(PORT=1601))

(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.1.161)(PORT=1601)(IP=FIRST))))

# line added by Agent

以上已经完成了监听的添加，足见使用srvctl管理更为简便。

之后仅需要加入静态注册信息即可,如:

SID_LIST_NEW_MACLEAN_LISTENER =

(SID_LIST =

(SID_DESC =

(GLOBAL_DBNAME = VPROD)

(ORACLE_HOME = /g01/11.2.0/grid)

(SID_NAME = VPROD1)

)

加入如上信息到listener.ora配置文件中(SID_LIST_($LISTENER_NAME)，并重启监听即完成静态注册：

[grid@vrh1 admin]$ srvctl stop listener -l NEW_MACLEAN_LISTENER

[grid@vrh1 admin]$ srvctl start listener -l NEW_MACLEAN_LISTENER

[grid@vrh1 admin]$ lsnrctl status NEW_MACLEAN_LISTENER

LSNRCTL for Linux: Version 11.2.0.3.0 - Production on 27-NOV-2011 11:21:37

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=NEW_MACLEAN_LISTENER)))

STATUS of the LISTENER

------------------------

Alias NEW_MACLEAN_LISTENER

Version TNSLSNR for Linux: Version 11.2.0.3.0 - Production

Start Date 27-NOV-2011 11:21:25

Uptime 0 days 0 hr. 0 min. 11 sec

Trace Level off

Security ON: Local OS Authentication

SNMP OFF

Listener Parameter File /g01/11.2.0/grid/network/admin/listener.ora

Listener Log File /g01/11.2.0/grid/log/diag/tnslsnr/vrh1/new_maclean_listener/alert/log.xml

Listening Endpoints Summary...

(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=NEW_MACLEAN_LISTENER)))

(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.161)(PORT=1601)))

(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.162)(PORT=1601)))

Services Summary...

Service "VPROD" has 1 instance(s).

Instance "VPROD1", status UNKNOWN, has 1 handler(s) for this service...

The command completed successfully

结合这个来看：

798
[grid@rac1 rac1]$ tail -8 $TNS_ADMIN/listener.ora

799 SID_LIST_LISTENER =

800 (SID_LIST =

801 (SID_DESC =

802 (GLOBAL_DBNAME = +ASM)

803 (ORACLE_HOME=/u01/app/11.2.0/grid)

804 (SID_NAME = +ASM1)

805 )

806 )

807

808 [grid@rac2 ~]$ tail -8 $TNS_ADMIN/listener.ora

809 SID_LIST_LISTENER =

810 (SID_LIST =

811 (SID_DESC =

812 (GLOBAL_DBNAME = +ASM)

813 (ORACLE_HOME=/u01/app/11.2.0/grid)

814 (SID_NAME = +ASM2)

815 )

816 )

好了我的也是。

测试。

上面只是一个测试而已

怎样配置动态监听：

1 listerner.ora 添加：每台都要添加

817 [grid@rac1 rac1]$ tail -8 $TNS_ADMIN/listener.ora

818 SID_LIST_LISTENER =

819 (SID_LIST =

820 (SID_DESC =

821 (GLOBAL_DBNAME = +ASM)

822 (ORACLE_HOME=/u01/app/11.2.0/grid) 这个是grid的安装目录

823 (SID_NAME = +ASM1) -------->自己改

824 )

825 )

2 vim tnsnames.ora 每天都要添加

826 ASM =

827 (DESCRIPTION =

828 (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.41)(PORT = 1521))

829 (CONNECT_DATA =

830 (SERVER = DEDICATED)

831 (SERVICE_NAME = +ASM)

832 (SID_NAME = +ASM1) ----->自己改

833 )

834 )