奕新集团---单实例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.
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.
The cause is "/" after ORACLE_HOME location.
In this case
/oracle/product/11.2.0/
was entered for ORACLE_HOME.
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
第二种方法
分别使用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
版权声明：原创作品，谢绝转载！否则将追究法律责任。
本文中将演示下使用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
12
13 [oracle@db1 ~]$ source .bash_profile
14
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 )
24
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
43
44 SQL> select supplemental_log_data_min from v$database;
45
46 SUPPLEME
47 --------
48 NO
49
50 SQL> alter database add supplemental log data;
51 Database altered.
52
53 SQL> select supplemental_log_data_min from v$database;
54
55 SUPPLEME
56 --------
57 YES
58
59 SQL> alter database force logging;
60 Database altered.
61
62 SQL> create tablespace tbs_ogg;
63 Tablespace created.
64
65 SQL> create user ogg identified by ogg default tablespace tbs_ogg temporary tablespace temp account unlock;
66 User created.
67
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
73
74 [oracle@db1 ogg]$ ./ggsci
75 GGSCI (db1) 1> create subdirs
76
77 Creating subdirectories under current directory /u01/app/oracle/ogg
78
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
88
89 GGSCI (db1) 2> edit params mgr
90
91 GGSCI (db1) 3> view params mgr
92 PORT 7809
93
94 GGSCI (db1) 4> start mgr
95 Manager started.
96
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.
Process id: 26185 Description: *********************************************************************** 2012-06-20 09:40:55 INFO OGG-03035 Operating system character set identified as UTF-8. Locale: en_US, LC_ALL: 2012-06-20 09:40:59 INFO OGG-01815 Virtual Memory Facilities for: COM CACHEMGR virtual memory values (may have been adjusted) Database Version: Database Language and Character Set: Processing table HR.T1 *********************************************************************** Report at 2012-06-20 09:42:26 (activity since 2012-06-20 09:40:59) Output to rinig1: From Table HR.T1: REDO Log Statistics |
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
Copyright (c) 1991, 2011, Oracle. All rights reserved.
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
Copyright (c) 1991, 2011, Oracle. All rights reserved.
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
Copyright (c) 1991, 2011, Oracle. All rights reserved.
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
Copyright (c) 1991, 2011, Oracle. All rights reserved.
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
Copyright (c) 1991, 2011, Oracle. All rights reserved.
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
Copyright (c) 1991, 2011, Oracle. All rights reserved.
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]$ 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
Copyright (c) 1991, 2011, Oracle. All rights reserved.
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
Copyright (c) 1991, 2011, Oracle. All rights reserved.
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 )
OK
