系统：OracleLinux 5.9
数据库版本11.2.0.3
hostname source target
vim /etc/hosts
172.16.10.140 142
内核参数
D:Oracle官方文档E11882_01install.112e24326toc.htm
添加用户组
代码语言：javascript复制[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
binutils-2.17.50.0.6
compat-libstdc -33-3.2.3
compat-libstdc -33-3.2.3 (32 bit)
elfutils-libelf-0.125
elfutils-libelf-devel-0.125
gcc-4.1.2
gcc-c -4.1.2
glibc-2.5-24
glibc-2.5-24 (32 bit)
glibc-common-2.5glibc-devel-2.5
glibc-devel-2.5 (32 bit)
glibc-headers-2.5
ksh-20060214
libaio-0.3.106
libaio-0.3.106 (32 bit)
libaio-devel-0.3.106
libaio-devel-0.3.106 (32 bit)
libgcc-4.1.2
libgcc-4.1.2 (32 bit)
libstdc -4.1.2
libstdc -4.1.2 (32 bit
libstdc -devel 4.1.2make-3.81sysstat-7.0.2解决：
代码语言：javascript复制[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问题就被解决了。
更改环境变量
代码语言：javascript复制 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版本 环境变量全部设置成下面的目录
代码语言：javascript复制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用户
代码语言：javascript复制export ORACLE_SID=target
export ORACLE_BASE=/u01/oracle
export GRID_HOME=/u01/grid/11g
export DB_HOME=/u01/oracle/11g
export ORACLE_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”
alias sqlplus=’rlwrap sqlplus’用这个
代码语言：javascript复制export ORACLE_SID=target
export ORACLE_BASE=/u01/oracle
export ORACLE_HOME=/u01/oracle/11.2.3/db_1
PATH=ORACLE_HOME/bin:/bin:/usr/bin:/usr/local/bin:/usr/sbin:HOME/bin
export PATH
alias db=”. ~/.db”
alias asm=”. ~/.bash_profile”grid用户
代码语言：javascript复制#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/oraclegrid用户 这里我不掩饰了。
grid软件
这里检查失踪报错。
原因 就是 在 /etc/security/limits.conf 文件中
配置 grid 和oracle用户
#==============add =================
代码语言：javascript复制#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数据库
用这个
代码语言：javascript复制export ORACLE_SID=target
export ORACLE_BASE=/u01/oracle
export ORACLE_HOME=/u01/oracle/11.2.3/db_1
PATH=ORACLE_HOME/bin:/bin:/usr/bin:/usr/local/bin:/usr/sbin:HOME/bin
export PATH
alias db=”. ~/.db”
alias asm=”. ~/.bash_profile”开始配置 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
6 打开源端 force logging之后 我执行一次归档之后操作
SQL> alter system archive log current;
alter system archive log current
*
ERROR at line 1:
ORA-00258: manual archiving in NOARCHIVELOG mode must identify log
7 打开源库补充日志
SQL> alter system archive log current;
alter system archive log current
*
ERROR at line 1:
ORA-00258: manual archiving in NOARCHIVELOG mode must identify log
8 创建单独的表空间和用户（源*目标）
create user goldengate identified by goldengate default tablespace gg_tbs temporary tablespace
temp quota unlimited on users;
ORA-01756: quoted string not properly terminated
字符集的问题
源：
create tablespace gg_tbs datafile ‘/oracle/oradata/source/gg.ora’ size 300m
autoextend on next 20m maxsize unlimited logging online permanent extent management local
autoallocate blocksize 16k segment space management auto flashback on;
目标：
create tablespace gg_tbs datafile ‘/oracle/oradata/target/gg.ora’ size 300m
autoextend on next 20m maxsize unlimited logging online permanent extent management local
autoallocate blocksize 16k segment space management auto flashback on;
*
ERROR at line 1:
ORA-29339: tablespace block size 16384 does not match configured block sizes
SQL> show parameter db_block_size
NAME TYPE VALUE
———————————— ———– ——————————
db_block_size integer 8192
SQL> select component,current_size from v$sga_dynamic_components;
COMPONENT CURRENT_SIZE
—————————————————————- ————
shared pool 310378496
large pool 4194304
java pool 4194304
streams pool 0
DEFAULT buffer cache 197132288
KEEP buffer cache 0
RECYCLE buffer cache 0
DEFAULT 2K buffer cache 0
DEFAULT 4K buffer cache 0
DEFAULT 8K buffer cache 0
DEFAULT 16K buffer cache 0
COMPONENT CURRENT_SIZE
—————————————————————- ————
DEFAULT 32K buffer cache 0
Shared IO Pool 0
ASM Buffer Cache 0
14 rows selected.
解决：
alter system set db_16k_cache_size=36m;
grant connect to goldengate;
grant alter any table to goldengate;
grant alter session to goldengate;
grant create session to goldengate;
grant flashback any table to goldengate;
grant select any table to goldengate;
grant select any dictionary to goldengate;
grant resource to goldengate;
grant drop any table to goldengate;
grant dba to goldengate;
源
[oracle@source gg]$ ./ggsci
./ggsci: error while loading shared libraries: libnnz11.so: cannot open shared object file: No such file or directory
目标
[oracle@target gg]$ ./ggsci
./ggsci: error while loading shared libraries: libnnz11.so: cannot open shared object file: No such file or directory
[oracle@target gg]$
现在的目录
export ORACLE_SID=source
export ORACLE_BASE=/u01/oracle
export GRID_HOME=/u01/grid/11g
export DB_HOME=/u01/oracle/11g
export ORACLE_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”
alias sqlplus=’rlwrap sqlplus’
~
source
exportORACLE_SID=source
exportORACLE_SID=ggsource
exportORACLE_BASE=/u01/oracle
exportGRID_HOME=/u01/grid/11g
exportDB_HOME=/u01/oracle/11g
exportORACLE_HOME=/u01/oracle/product
PATH=GRID_HOME/bin:ORACLE_BASE/gg11:/bin:/usr/bin:/usr/local/bin:/usr/sbin:
LD_LIBRARY_PATH=ORACLE_HOME/lib:ORACLE_BASE/gg11:
export NLS_LANG=AMERICAN_AMERICA.UTF8
export LANG=en_US.UTF-8
alias db=”. ~/.db”
alias asm=”. ~/.bash_profile”
alias sqlplus=’rlwrap sqlplus’
cd $ORACLE_BASE/gg11
检查生成的环境。
[oracle@source gg11]$ env | grep LD
OLDPWD=/home/oracle
LD_LIBRARY_PATH=/u01/oracle/product/lib:/u01/oracle/gg11:/u01/oracle/product/lib:/u01/oracle/gg11:
[oracle@source gg11]$
OK 连接进来了。
为什么会出现那个 ./ggsci: error while loading shared libraries: libnnz11.so: cannot open shared object file: No such file or directory
是因为 环境变量没有设置 怎样设置环境变量。我的GG中有。
[oracle@source gg11]$ ./ggsci
Oracle GoldenGate Command Interpreter for Oracle
Version 11.2.1.0.1 OGGCORE_11.2.1.0.1_PLATFORMS_120423.0230_FBO
Linux, x64, 64bit (optimized), Oracle 11g on Apr 23 2012 08:32:14
Copyright (C) 1995, 2012, Oracle and/or its affiliates. All rights reserved.
GGSCI (source) 1> create subdirs
Creating subdirectories under current directory /u01/oracle/gg11
Parameter files /u01/oracle/gg11/dirprm: already exists
Report files /u01/oracle/gg11/dirrpt: created
Checkpoint files /u01/oracle/gg11/dirchk: created
Process status files /u01/oracle/gg11/dirpcs: created
SQL script files /u01/oracle/gg11/dirsql: created
Database definitions files /u01/oracle/gg11/dirdef: created
Extract data files /u01/oracle/gg11/dirdat: created
Temporary files /u01/oracle/gg11/dirtmp: created
Stdout files /u01/oracle/gg11/dirout: created
我在目标端这个么干的：
export ORACLE_SID=target
export ORACLE_SID=ggtarget
export ORACLE_BASE=/u01/oracle
export ORACLE_HOME=/u01/oracle/11.2.3/db_1
export PATH=ORACLE_HOME/bin:/oracle/gg:/bin:/usr/bin:/usr/local/bin:/usr/sbin:HOME/bin
export LD_LIBRARY_PATH=ORACLE_HOME/lib:/oracle/gg:LD_LIBRARY_PATH
export NLS_LANG=AMERICAN_AMERICA.UTF8
export LANG=en_US.UTF-8
alias db=”. ~/.db”
alias asm=”. ~/.bash_profile”
alias sqlplus=’rlwrap sqlplus’
目标端
[oracle@target ~]$ cd /oracle/gg/
[oracle@target gg]$ env | grep LD
OLDPWD=/home/oracle
LD_LIBRARY_PATH=/u01/oracle/11.2.3/db_1/lib:/oracle/gg:
[oracle@target gg]$
源端
EXTRACT EINI01
USERID goldengate,PASSWORD goldengate
RMTHOST target,MGRPORT 7809
RMTTASK REPLICAT,GROUP RINI01
TABLE goldengate.MHZ1;
TABLE goldengate.MHZ2;
目标端
REPLICAT RINI01
ASSUMETARGETDEFS
USERID goldengate,PASSWORD goldengate
DISCADFILE ./dirrpt/RINI01.dsc,PURGE
MAP goldengate.*,TARGET goldengate.*;
问题： 尝试1
[/u01/oracle/gg11/extract(__gxx_personality_v0 0x38a) [0x4e8b7a]]
2013-11-05 09:24:33 ERROR OGG-00664 OCI Error beginning session (status = 1034-ORA-01034: ORACLE not available
ORA-27101: shared memory realm does not exist
Linux-x86_64 Error: 2: No such file or directory).
2013-11-05 09:24:33 ERROR OGG-01668 PROCESS ABENDING.
解决 OGG-00064
尝试1 我的不是这个问题
ERROR OGG-00664 OCI Error beginning session (status = 1017-ORA-01017:
CASE 源库开始抽取，通过VIEW REPORT EXP1：
ERROR OGG-00664 OCI Error beginningsession (status = 1017-ORA-01017:
解决方案：
原因：配置中账户密码不对
修改抽取或者复制配置文件的账户密码；
#Goldengate
进入环境变量的目录
[oracle@source gg11]$ pwd
/u01/oracle/gg11
-rw-
Oracle GoldenGate Command Interpreter for Oracle
Version 11.2.1.0.1 OGGCORE_11.2.1.0.1_PLATFORMS_120423.0230_FBO
Linux, x64, 64bit (optimized), Oracle 11g on Apr 23 2012 08:32:14
Copyright (C) 1995, 2012, Oracle and/or its affiliates. All rights reserved.
GGSCI (source) 1> edit params mgr
尝试2
环境变量
进入环境变量
cd /oracle/gg/
ggsci
Oracle GoldenGate Command Interpreter for Oracle
Version 11.2.1.0.1 OGGCORE_11.2.1.0.1_PLATFORMS_120423.0230_FBO
Linux, x64, 64bit (optimized), Oracle 11g on Apr 23 2012 08:32:14
Copyright (C) 1995, 2012, Oracle and/or its affiliates. All rights reserved.
GGSCI (target) 1> edit params mgr
注意： 测试数据
复制 从 source 复制到target数据。
source 数据上必须有数据 target数据上必须没有数据实现一个单向同步。
目标：
GGSCI (source) 3> view params eini01
#EXTRACT EINI01
#USERID goldengate,PASSWORD goldengate
#RMTHOST target,MGRPORT 7809
#RMTTASK REPLICAT,GROUP RINI01
#TABLE goldengate.mhz1;
#TABLE goldengate.mhz2;
extract eini01
setenv (NLS_LANG=AMERICAN_AMERICA.AL32UTF8)
userid goldengate,password goldengate
rmthost 172.16.10.142,mgrport 7809
rmttask replicat,group rini01
table goldengate.mhz1;
table goldengate.mhz2;
目标：
REPLICAT RINI01
ASSUMETARGETDEFS
USERID goldengate,PASSWORD goldengate
DISCADFILE ./dirrpt/RINI01.dsc,PURGE
MAP goldengate.*,TARGET goldengate.*;
replicat rini01
setenv (NLS_LANG=AMERICAN_AMERICA.AL32UTF8)
assumetargetdefs
userid goldengate,password goldengate
discardfile ./dirrpt/rini01.dsc,purge
map goldengate.*,target goldengate.*;
~
注意：
注意：用INITIAL EXTRACT进行一次性抽取初始化数据时，目标库的replicat进程不用手动启动，只要源库的extract进程start以后，自动会同步到目标库，而且此时用info all是看不到extract和replicat进程的，只能看见mgr进程
OGG-00664 OCI Error beginning session ORA-27101: shared memory realm does not exist
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=source)(PORT=1521)))
The listener supports no services
The command completed successfully
[oracle@source admin]$ cat listener.ora
# listener.ora Network Configuration File: /u01/grid/11g/network/admin/listener.ora
# Generated by Oracle configuration tools.
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 172.16.10.140)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
)
ADR_BASE_LISTENER = /u01/oracle
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER=ON# line added by Agent
[oracle@source admin]$ sqlplus goldengate/goldengate@source
SQL*Plus: Release 11.2.0.3.0 Production on Thu Nov 7 06:45:41 2013
Copyright (c) 1982, 2011, Oracle. All rights reserved.
ERROR:
ORA-12504: TNS:listener was not given the SERVICE_NAME in CONNECT_DATA
Enter user-name:
解决：
停止 lsnrctl stop
srvctl stop linstener
netmgr
新建 Linstener
新建 tnsnames.ora
srvctl enable listener
crs_stat -all
OK
———————————-
报错：
oracle@source admin]$ sqlplus goldengate/goldengate@source
SQL*Plus: Release 11.2.0.3.0 Production on Thu Nov 7 07:17:01 2013
Copyright (c) 1982, 2011, Oracle. All rights reserved.
ERROR:
ORA-12514: TNS:listener does not currently know of service requested in connect
descriptor
Enter user-name:
[oracle@source admin]$ lsnrctl status
卸载GG 先停止所有进程
进入 gg安装目录
info all
rm -rf gg
就这样解决了这个问题 数据库实例没有启动
2013-11-07 09:45:29 ERROR OGG-00446 Missing filename opening checkpoint file.
2013-11-07 09:45:29 ERROR OGG-01668 PROCESS ABENDING.
dblogin userid goldengate,password goldengate
add checkpointtable goldengate.checkpoint
add replicat rora01,exttrail ./dirdat/rt,begin now,checkpointtable goldengate.checkpoint
今天早上遇到一个错误
GGSCI (target) 3> add checkpointtable
ERROR: Missing checkpoint table specification.
GGSCI (target) 4> add checkpointtable
ERROR: Missing checkpoint table specification.
解决：
代码语言：javascript复制1 GGSCI (db2) 1> edit params ./GLOBALS
2 GGSCI (db2) 2> view params ./GLOBALS
3 checkpointtable ogg.ggschkpt
4
5 GGSCI (db2) 3> exit //这里需要退出ggsci终端 编辑之后必须退出
6 [oracle@db2 ~]$ sqlplus ogg/ogg
7 SQL> select tname from tab;
8 no rows selected
9
10 [oracle@db2 ogg]$ ggsci
11 GGSCI (db2) 1> dblogin userid ogg,password ogg
12 Successfully logged into database.
13
14 GGSCI (db2) 2> add checkpointtable
15 No checkpoint table specified, using GLOBALS specification (ogg.ggschkpt)…
16 Successfully created checkpoint table ogg.ggschkpt.
17 总结：
周玲的那个文档无法做出来 不要用那个文档 没有任何意思。
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文件
代码语言：javascript复制18 [oracle@db1 ~]$ tail .bash_profile
19 export ORACLE_SID=db1
20 export ORACLE_BASE=/u01/app/oracle
21 export ORACLE_HOME=$ORACLE_BASE/product/10.2.0/db1
22 export PATH=ORACLE_HOME/bin:PATH
23 export NLS_LANG=AMERICAN_AMERICA.AL32UTF8
24 export NLS_DATE_FORMAT=‘yyyy-mm-dd-hh24:mi:ss’
25 export EDITOR=vim
26 export TNS_ADMIN=$ORACLE_HOME/network/admin
27 export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib
28 export GGATE=$ORACLE_BASE/ogg
29
30 [oracle@db1 ~]$ source .bash_profile
31
32 [oracle@db1 ~] cat TNS_ADMIN/tnsnames.ora
33 DB1 =
34 (DESCRIPTION =
35 (ADDRESS = (PROTOCOL = TCP)(HOST = db1)(PORT = 1521))
36 (CONNECT_DATA =
37 (SERVER = DEDICATED)
38 (SERVICE_NAME = db1)
39 )
40 )
41
42 DB2 =
43 (DESCRIPTION =
44 (ADDRESS = (PROTOCOL = TCP)(HOST = db2)(PORT = 1521))
45 (CONNECT_DATA =
46 (SERVER = DEDICATED)
47 (SERVICE_NAME = db2)
48 )
49 ) 2：确定数据库运行在归档模式，开启数据库附加日志，打开force logging,创建用于复制的数据库账号ogg，为了方便，这里直接赋予dba权限，如果对数据库安全要求高，可以去查询ogg文档，赋予复制需要的最小权限！(source和target端做相同的操作)
代码语言：javascript复制50 [oracle@db1 ~]$ sqlplus /nolog
51 SQL> conn /as sysdba
52 Connected.
53 SQL> archive log list;
54 Database log mode Archive Mode
55 Automatic archival Enabled
56 Archive destination USE_DB_RECOVERY_FILE_DEST
57 Oldest online log sequence 1
58 Next log sequence to archive 2
59 Current log sequence 2
60
61 SQL> select supplemental_log_data_min from v$database;
62
63 SUPPLEME
64 ——–
65 NO
66
67 SQL> alter database add supplemental log data;
68 Database altered.
69
70 SQL> select supplemental_log_data_min from v$database;
71
72 SUPPLEME
73 ——–
74 YES
75
76 SQL> alter database force logging;
77 Database altered.
78
79 SQL> create tablespace tbs_ogg;
80 Tablespace created.
81
82 SQL> create user ogg identified by ogg default tablespace tbs_ogg temporary tablespace temp account unlock;
83 User created.
84
85 SQL> grant connect,resource,dba to ogg;
86 Grant succeeded
备注： 在oracle中可以通过rowid来定位某条记录，但是目标端的数据库和源端数据库的数据库可能完全不一样，所以无法通过rowid来确定源端数据库的逻辑变化，这时附加日志supplemental log便登上了表演的舞台。数据库在开启附加日志功能后，对于源端的修改操作，oracle会同时追加能够唯一标示记录的列到redo log。这样目标端数据库就可以知道源端发生了哪些具体的变化。
三：安装ogg软件，启动mgr管理进程，source和target端做相同的操作
87 [oracle@db1 ~] mkdir GGATE
88 [oracle@db1 ~] cd GGATE
89 [oracle@db1 ogg]$ tar -xvf /home/oracle/fbo_ggs_Linux_x64_ora10g_64bit.tar
90
91 [oracle@db1 ogg]$ ./ggsci
92 GGSCI (db1) 1> create subdirs
93
94 Creating subdirectories under current directory /u01/app/oracle/ogg
95
96 Parameter files /u01/app/oracle/ogg/dirprm: already exists
97 Report files /u01/app/oracle/ogg/dirrpt: created
98 Checkpoint files /u01/app/oracle/ogg/dirchk: created
99 Process status files /u01/app/oracle/ogg/dirpcs: created
100 SQL script files /u01/app/oracle/ogg/dirsql: created
101 Database definitions files /u01/app/oracle/ogg/dirdef: created
102 Extract data files /u01/app/oracle/ogg/dirdat: created
103 Temporary files /u01/app/oracle/ogg/dirtmp: created
104 Stdout files /u01/app/oracle/ogg/dirout: created
105
106 GGSCI (db1) 2> edit params mgr
107
108 GGSCI (db1) 3> view params mgr
109 PORT 7809
110
111 GGSCI (db1) 4> start mgr
112 Manager started.
113
114 GGSCI (db1) 5> info mgr
115 Manager is running (IP port db1.7809).
四：准备测试用户和表
116 SQL> conn /as sysdba
117 Connected.
118
119 SQL> alter user hr identified by hr account unlock;
120 User altered.
121
122 SQL> grant connect,resource,select_catalog_role to hr;
123 Grant succeeded.
124
125 SQL> conn hr/hr
126 Connected.
127
128 SQL> create table t1 as select * from dba_objects;
129 Table created.
130
131 SQL> alter table t1 add constraint pk_t1 primary key(object_id);
132 Table altered.
133
134 SQL> select count(*) from t1; //source端
135
136 COUNT(*)
137 ———-
138 50315
139
140 SQL> select count(*) from t1; //target端，只复制表定义，不填充数据
141
142 COUNT(*)
143 ———-
144 0
五:初始化加载数据,在异构数据库平台(例如oracle-mysql)，这个功能显得非常的有用！而在oracle-oracle的数据复制条件下，oracle推荐使用expdp/impdp工具
1.source端添加extract进程
145 GGSCI (db1) 1> add extract einig1,sourceistable //sourceistable代表直接从表中读取数据
146 EXTRACT added.
147
148 GGSCI (db1) 2> edit params einig1 //einig1代表extract initial load group 1缩写
149
150 GGSCI (db1) 3> view params einig1
151 extract einig1
152 setenv (NLS_LANG=AMERICAN_AMERICA.AL32UTF8)
153 userid ogg,password ogg
154 rmthost 192.168.123.20,mgrport 7809
155 rmttask replicat,group rinig1
156 table hr.t1;
2.target端添加replicat进程
157 GGSCI (db2) 1> add replicat rinig1,specialrun //specialrun代表只运行一次
158 REPLICAT added.
159
160 GGSCI (db2) 2> edit params rinig1 //rinig1代表replicat initial load group 1缩写
161
162 GGSCI (db2) 3> view params rinig1 //rinig1的名字必须同source端定义的group名字相同
163 replicat rinig1
164 setenv (NLS_LANG=AMERICAN_AMERICA.AL32UTF8)
165 assumetargetdefs
166 userid ogg,password ogg
167 discardfile ./dirrpt/rinig1.dsc,purge
168 map hr.*,target hr.*;
3.source端启动extract进程，查看日志输出
169 GGSCI (db1) 4> start extract einig1
170
171 Sending START request to MANAGER …
172 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:LinuxVersion #1 SMP Thu Sep 3 03:28:30 EDT 2009, Release 2.6.18-164.el5Node: db1Machine: x86_64 soft limit hard limitAddress Space Size : unlimited unlimitedHeap Size : unlimited unlimitedFile Size : unlimited unlimitedCPU 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端验证
代码语言：javascript复制173 [oracle@db2 ogg]$ sqlplus hr/hr
174 SQL> select count(*) from t1;
175
176 COUNT(*)
177 ———-
178 50315 六：配置db1,db2间的实时同步复制 1：在source上配置extract进程，进程的名字不能超过8个字符
代码语言：javascript复制179 GGSCI (db1) 1> edit params eora_t1
180
181 GGSCI (db1) 2> view params eora_t1
182 extract eora_t1
183 setenv (NLS_LANG=AMERICAN_AMERICA.AL32UTF8)
184 userid ogg,password ogg
185 exttrail ./dirdat/aa
186 table hr.*; 2：开启hr用户下所有表的附加日志
代码语言：javascript复制187 GGSCI (db1) 3> dblogin userid ogg, password ogg
188 Successfully logged into database.
189
190 GGSCI (db1) 4> add trandata hr.*
3：添加extract进程，添加trail文件，文件名前缀不能超过2个字符
191 GGSCI (db1) 5> add extract eora_t1,tranlog,begin now
192 EXTRACT added.
193
194 GGSCI (db1) 6> add exttrail ./dirdat/aa,extract eora_t1,megabytes 100 //添加trail文件
195 EXTTRAIL added.
196
197 GGSCI (db1) 7> start extract eora_t1
198
199 Sending START request to MANAGER …
200 EXTRACT EORA_T1 starting
201
202 GGSCI (db1) 8> info extract eora_t1
203
204 EXTRACT EORA_T1 Last Started 2012-06-20 10:06 Status RUNNING
205 Checkpoint Lag 00:00:00 (updated 00:00:02 ago)
206 Log Read Checkpoint Oracle Redo Logs
207 2012-06-20 10:06:36 Seqno 3, RBA 21804544
208 SCN 0.562134 (562134)
209
210 GGSCI (db1) 9> info all
211 Program Status Group Lag at Chkpt Time Since Chkpt
212
213 MANAGER RUNNING
214 EXTRACT RUNNING EORA_T1 00:08:24 00:00:05
4：添加pump进程
215 GGSCI (db1) 10> edit params pora_t1
216
217 GGSCI (db1) 11> view params pora_t1
218 extract pora_t1
219 setenv (NLS_LANG=AMERICAN_AMERICA.AL32UTF8)
220 passthru
221 rmthost 192.168.123.20,mgrport 7809
222 rmttrail ./dirdat/pa
223 table hr.*;
224
225 GGSCI (db1) 12> add extract pora_t1,exttrailsource ./dirdat/aa //这里aa文件名同前面extract进程参数文件中定义的trail文件名一
226
227 致
228 EXTRACT added.
229
230 GGSCI (db1) 13> add rmttrail ./dirdat/pa,extract pora_t1,megabytes 100 //添加传输到target数据库的trail问文件名，应该同参数文
231
232 件中描述的一致
233 RMTTRAIL added.
234
235 GGSCI (db1) 14> start extract pora_t1
236 Sending START request to MANAGER …
237 EXTRACT PORA_T1 starting
238
239 GGSCI (db1) 15> info all
240 Program Status Group Lag at Chkpt Time Since Chkpt
241
242 MANAGER RUNNING
243 EXTRACT RUNNING EORA_T1 00:00:00 00:00:02
244 EXTRACT RUNNING PORA_T1 00:00:00 00:00:22
4：在target端添加检查表，配置replicat进程
245 GGSCI (db2) 1> edit params ./GLOBALS
246 GGSCI (db2) 2> view params ./GLOBALS
247 checkpointtable ogg.ggschkpt
248
249 GGSCI (db2) 3> exit //这里需要退出ggsci终端
250 [oracle@db2 ~]$ sqlplus ogg/ogg
251 SQL> select tname from tab;
252 no rows selected
253
254 [oracle@db2 ogg]$ ggsci
255 GGSCI (db2) 1> dblogin userid ogg,password ogg
256 Successfully logged into database.
257
258 GGSCI (db2) 2> add checkpointtable
259 No checkpoint table specified, using GLOBALS specification (ogg.ggschkpt)…
260 Successfully created checkpoint table ogg.ggschkpt.
261
262 SQL> select tname from tab;
263
264 TNAME
265 ——————————
266 GGSCHKPT
267 GGSCHKPT_LOX
268
269 GGSCI (db2) 3> edit params rora_t1
270 GGSCI (db2) 4> view params rora_t1
271 replicat rora_t1
272 setenv (NLS_LANG=AMERICAN_AMERICA.AL32UTF8)
273 userid ogg,password ogg
274 handlecollisions
275 assumetargetdefs
276 discardfile ./dirrpt/rora_t1.dsc,purge
277 map hr.* ,target hr.*;
278
279 GGSCI (db2) 5> add replicat rora_t1,exttrail ./dirdat/pa
280 REPLICAT added.
281
282 GGSCI (db2) 6> start replicat rora_t1
283
284 Sending START request to MANAGER …
285 REPLICAT RORA_T1 starting
286
287 GGSCI (db2) 7> info replicat rora_t1
288 REPLICAT RORA_T1 Last Started 2012-06-20 10:21 Status RUNNING
289 Checkpoint Lag 00:00:00 (updated 00:00:03 ago)
290 Log Read Checkpoint File ./dirdat/t1000000
291 First Record RBA 0
292
293 GGSCI (db2) 8> info all
294 Program Status Group Lag at Chkpt Time Since Chkpt
295
296 MANAGER RUNNING
297 REPLICAT RUNNING RORA_T1 00:00:00 00:00:05 备注： 在target端配置replicat进程之前，通常需要在目标端的数据库中创建一个checkpoint表，这个表是基于ogg checkpoint文件的，它记录了所有ogg可恢复的checkpoint以及sequence，这个操作不是必须的，但oracle强烈建议使用它，因为它可以使得checkpoint包含在replicat的事务中，保证了可以从各类失败场景中恢复！
七：测试同步 1：插入数据
代码语言：javascript复制298 [oracle@db1 ogg]$ sqlplus hr/hr
299 SQL> desc t1
300 Name Null? Type
301 —————————————– ——– —————————-
302 OWNER VARCHAR2(30)
303 OBJECT_NAME VARCHAR2(128)
304 SUBOBJECT_NAME VARCHAR2(30)
305 OBJECT_ID NOT NULL NUMBER
306 DATA_OBJECT_ID NUMBER
307 OBJECT_TYPE VARCHAR2(19)
308 CREATED DATE
309 LAST_DDL_TIME DATE
310 TIMESTAMP VARCHAR2(19)
311 STATUS VARCHAR2(7)
312 TEMPORARY VARCHAR2(1)
313 GENERATED VARCHAR2(1)
314 SECONDARY VARCHAR2(1)
315
316 SQL> select max(object_id) from t1;
317
318 MAX(OBJECT_ID)
319 ————–
320 52504
321
322 SQL> insert into t1 (object_id,object_name) values (52505,’ogg_test’);
323 1 row created.
324
325 SQL> commit;
326 Commit complete.
327
328 SQL> conn hr/hr@db2
329 Connected.
330 SQL> select max(object_id) from t1;
331
332 MAX(OBJECT_ID)
333 ————–
334 52505 2：抽取trail文件中可打印的内容分析
代码语言：javascript复制335 [root@db1 dirdat]# pwd
336 /u01/app/oracle/ogg/dirdat
337 [root@db1 dirdat]# strings aa000000
338 uri:db1::u01:app:oracle:ogg6
339 ./dirdat/aa0000007
340 564200
341 Linux1
342 db12
343 2.6.18-164.el53
344 “#1 SMP Thu Sep 3 03:28:30 EDT 20094
345 x86_642
346 DB12
347 db13
348 Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 – 64bi
349 PL/SQL Release 10.2.0.1.0 – Production
350 CORE 10.2.0.1.0 Production
351 TNS for Linux: Version 10.2.0.1.0 – Production
352 NLSRTL Version 10.2.0.1.0 – Production
353 EORA_T11
354 ?Version 11.2.1.0.1 OGGCORE_11.2.1.0.1_PLATFORMS_120423.0230_FBO4
355 HR.T1
356 ogg_test
357 52505
358 1900-01-01:00:00:00
359 1900-01-01:00:00:00
360 AAAM0YAAEAAAARlAAA
361 5642006
362 2.46.299Z
363
364 [root@db2 dirdat]# pwd
365 /u01/app/oracle/ogg/dirdat
366 [root@db2 dirdat]# strings pa000000
367 uri:db1::u01:app:oracle:ogg5
368 uri:db1::u01:app:oracle:ogg6
369 ./dirdat/pa0000007
370 Linux1
371 db12
372 2.6.18-164.el53
373 “#1 SMP Thu Sep 3 03:28:30 EDT 20094
374 x86_642
375 DB12
376 db13
377 Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 – 64bi
378 PL/SQL Release 10.2.0.1.0 – Production
379 CORE 10.2.0.1.0 Production
380 TNS for Linux: Version 10.2.0.1.0 – Production
381 NLSRTL Version 10.2.0.1.0 – Production
382 EORA_T11
383 ?Version 11.2.1.0.1 OGGCORE_11.2.1.0.1_PLATFORMS_120423.0230_FBO4
384 HR.T1
385 ogg_test
386 52505
387 1900-01-01:00:00:00
388 1900-01-01:00:00:00
389 AAAM0YAAEAAAARlAAA
390 5642006
391 2.46.299Z 3：删除测试
代码语言：javascript复制392 SQL> conn hr/hr
393 Connected.
394 SQL> delete from t1 where object_id > 1000;
395 49362 rows deleted.
396
397 SQL> commit;
398 Commit complete
399
400 SQL> select max(object_id) from t1;
401
402 MAX(OBJECT_ID)
403 ————–
404 1000
405
406 SQL> conn hr/hr@db2
407 Connected.
408 SQL> select max(object_id) from t1;
409
410 MAX(OBJECT_ID)
411 ————–
412 1000 第三步:
GoldenGate单向复制配置(支持DDL复制)
2012-06-21 09:05:27
标签：ogg ddl replication oracle goldengate
版权声明：原创作品，谢绝转载！否则将追究法律责任。
在上一篇文件中的OGG单向复制配置不支持DLL的同步，只支持DML，因而本文在之前的基础上增加对DDL语句的复制，下面是简要配置过程记录！
一：验证之前的配置不支持DDL复制，这里在source端，新建一张表，发现无法复制到target端！target端也新建相同的表后，DML操作可以成功复制
代码语言：javascript复制413 [root@db1 ~]# su – oracle
414 [oracle@db1 ~]$ sqlplus hr/hr
415 SQL> create table t2 (id number primary key,name varchar2(20));
416 Table created.
417 SQL> conn hr/hr@db2
418 Connected.
419 SQL> select tname from tab where tname=‘T2’;
420 no rows selected
421
422 SQL> create table t2 (id number primary key,name varchar2(20));
423 Table created.
424
425 SQL> conn hr/hr
426 Connected.
427 SQL> insert into t2 values (1,’one’);
428 1 row created.
429
430 SQL> commit;
431 Commit complete.
432
433 SQL> conn hr/hr@db2
434 Connected.
435 SQL> select * from t2;
436
437 ID NAME
438 ———- ——————–
439 1 one
二：开始配置OGG支持DDL复制(在source端操作)
1：赋予ogg用户相应的权限，修改全局配置文件添加ggschema参数
440 SQL> conn /as sysdba
441 Connected.
442 SQL> grant execute on utl_file to ogg;
443 Grant succeeded.
444
445 [oracle@db1 ~] cd GGATE
446 [oracle@db1 ogg]$ ggsci
447 GGSCI (db1) 1> edit param ./GLOBALS
448
449 GGSCI (db1) 2> view param ./GLOBALS
450 ggschema ogg
2：运行相关的sql脚本
451 [oracle@db1 ~] cd GGATE
452 [oracle@db1 ogg]$ sqlplus /nolog
453 SQL> conn /as sysdba
454 Connected.
455 SQL> @marker_setup.sql
456
457 Marker setup script
458 You will be prompted for the name of a schema for the Oracle GoldenGate database objects.
459 NOTE: The schema must be created prior to running this script.
460 NOTE: Stop all DDL replication before starting this installation.
461
462 Enter Oracle GoldenGate schema name:ogg
463
464 Marker setup table script complete, running verification script…
465 Please enter the name of a schema for the GoldenGate database objects:
466 Setting schema name to OGG
467
468 MARKER TABLE
469 ——————————-
470 OK
471
472 MARKER SEQUENCE
473 ——————————-
474 OK
475
476 Script complete.
477
478 SQL> show parameter recyclebin;
479
480 NAME TYPE VALUE
481 ———————————— ———– ——————————
482 recyclebin string on
483
484 SQL> alter system set recyclebin=off;
485 System altered.
486
487 SQL> show parameter recyclebin;
488
489 NAME TYPE VALUE
490 ———————————— ———– ——————————
491 recyclebin string OFF
492
493 SQL> @ddl_setup.sql
494 Oracle GoldenGate DDL Replication setup script
495 Verifying that current user has privileges to install DDL Replication…
496 You will be prompted for the name of a schema for the Oracle GoldenGate database objects.
497 NOTE: For an Oracle 10g source, the system recycle bin must be disabled. For Oracle 11g and later, it can be enabled.
498 NOTE: The schema must be created prior to running this script.
499 NOTE: Stop all DDL replication before starting this installation.
500 Enter Oracle GoldenGate schema name:ogg
501
502 Working, please wait …
503 Spooling to file ddl_setup_spool.txt
504
505 Checking for sessions that are holding locks on Oracle Golden Gate metadata tables …
506 Check complete.
507 Using OGG as a Oracle GoldenGate schema name.
508 Working, please wait …
509 RECYCLEBIN must be empty.
510 This installation will purge RECYCLEBIN for all users.
511 To proceed, enter yes. To stop installation, enter no.
512
513 Enter yes or no:yes
514 ————————其他输出省略————————
515
516 STATUS OF DDL REPLICATION
517 —————————————————————————————
518 SUCCESSFUL installation of DDL Replication software components
519
520 Script complete.
521
522 SQL> @role_setup.sql
523 GGS Role setup script
524 This script will drop and recreate the role GGS_GGSUSER_ROLE
525 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.)
526
527 You will be prompted for the name of a schema for the GoldenGate database objects.
528 NOTE: The schema must be created prior to running this script.
529 NOTE: Stop all DDL replication before starting this installation.
530
531 Enter GoldenGate schema name:ogg
532 Wrote file role_setup_set.txt
533 PL/SQL procedure successfully completed.
534
535 Role setup script complete
536
537 Grant this role to each user assigned to the Extract, GGSCI, and Manager processes, by using the following SQL command:
538 GRANT GGS_GGSUSER_ROLE TO <loggedUser>
539 where <loggedUser> is the user assigned to the GoldenGate processes.
540
541 SQL> grant ggs_ggsuser_role to ogg;
542 Grant succeeded.
543
544 SQL> @ddl_enable.sql
545 Trigger altered.
546
547 SQL> @?/rdbms/admin/dbmspool.sql
548 Package created.
549 Grant succeeded.
550 View created.
551 Package body created.
552
553 SQL> @ddl_pin.sql ogg
554 PL/SQL procedure successfully completed.
555 PL/SQL procedure successfully completed.
556 PL/SQL procedure successfully completed.
3：source端修改extract进程的params文件，添加”ddl include all”参数，重启extract进程
557 GGSCI (db1) 1> view params eora_t1
558 extract eora_t1
559 setenv (NLS_LANG=AMERICAN_AMERICA.AL32UTF8)
560 ddl include all
561 userid ogg,password ogg
562 exttrail ./dirdat/aa
563 table hr.*;
564
565 GGSCI (db1) 2> stop extract eora_t1
566 Sending STOP request to EXTRACT EORA_T1 …
567 Request processed.
568
569 GGSCI (db1) 3> start extract eora_t1
570 Sending START request to MANAGER …
571 EXTRACT EORA_T1 starting
572
573 GGSCI (db1) 4> info extract eora_t1
574 EXTRACT EORA_T1 Last Started 2012-06-20 15:42 Status RUNNING
575 Checkpoint Lag 00:00:00 (updated 00:00:10 ago)
576 Log Read Checkpoint Oracle Redo Logs
577 2012-06-20 15:42:58 Seqno 3, RBA 50044416
578 SCN 0.567478 (567478)
4：target端修改replicat进程的params文件，添加”ddl include all”和”ddlerror default ignore retryop maxretries 3 retrydelay 5″ 参数，重启replicat进程
579 [root@db2 ~]# su – oracle
580 [oracle@db2 ~] cd GGATE
581 [oracle@db2 ogg]$ ggsci
582 GGSCI (db2) 1> edit params rora_t1
583 GGSCI (db2) 2> view params rora_t1
584 replicat rora_t1
585 setenv (NLS_LANG=AMERICAN_AMERICA.AL32UTF8)
586 ddl include all
587 ddlerror default ignore retryop maxretries 3 retrydelay 5
588 userid ogg,password ogg
589 handlecollisions
590 assumetargetdefs
591 discardfile ./dirrpt/rora_t1.dsc,purge
592 map hr.* ,target hr.*;
593
594 GGSCI (db2) 3> stop replicat rora_t1
595 Sending STOP request to REPLICAT RORA_T1 …
596 Request processed.
597
598 GGSCI (db2) 4> start replicat rora_t1
599 Sending START request to MANAGER …
600 REPLICAT RORA_T1 starting
601
602 GGSCI (db2) 5> info replicat rora_t1
603 REPLICAT RORA_T1 Last Started 2012-06-20 15:50 Status RUNNING
604 Checkpoint Lag 00:00:00 (updated 00:00:00 ago)
605 Log Read Checkpoint File ./dirdat/pa000000
606 First Record RBA 4780973 三：测试
代码语言：javascript复制607 [oracle@db1 ogg]$ sqlplus hr/hr
608 SQL> alter table t2 add location varchar2(200);
609 Table altered.
610
611 SQL> conn hr/hr@db2
612 Connected.
613 SQL> desc t2
614 Name Null? Type
615 —————————————– ——– —————————-
616 ID NOT NULL NUMBER
617 NAME VARCHAR2(20)
618 LOCATION VARCHAR2(200)
619
620 SQL> conn hr/hr
621 Connected.
622 SQL> create table t3 as select object_id,object_name from dba_objects;
623
624 Table created.
625
626 SQL> conn hr/hr@db2
627 Connected.
628 SQL> select tname from tab where tname=‘T3’;
629
630 TNAME
631 ——————————
632 T3
633
634 SQL> desc t3;
635 Name Null? Type
636 —————————————– ——– —————————-
637 OBJECT_ID NUMBER
638 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来标识两台数据库服务器)
代码语言：javascript复制639 GGSCI (db1) 3> view params ./GLOBALS
640 ggschema ogg
641 checkpointtable ogg.ggschkpt
642
643 GGSCI (db1) 4> exit
644
645 [oracle@db1 ogg]$ ggsci
646 GGSCI (db1) 2> dblogin userid ogg,password ogg
647 Successfully logged into database.
648
649 GGSCI (db1) 3> add checkpointtable
650 No checkpoint table specified, using GLOBALS specification (ogg.ggschkpt)…
651 Successfully created checkpoint table ogg.ggschkpt. 二：配置db2,运行相关的脚本，支持DDL的复制
代码语言：javascript复制652 [oracle@db2 ogg]$ sqlplus /nolog
653 SQL> conn /as sysdba
654 Connected.
655 SQL> grant execute on utl_file to ogg;
656 Grant succeeded.
657
658 [oracle@db2 ogg]$ ggsci
659 GGSCI (db2) 1> view params ./GLOBALS
660 ggschema ogg
661 checkpointtable ogg.ggschkpt
662
663 [oracle@db2 ogg]$ sqlplus /nolog
664 SQL> conn /as sysdba
665 Connected.
666 SQL> @marker_setup.sql
667 SQL> @ddl_setup.sql
668 SQL> @role_setup.sql
669 SQL> grant ggs_ggsuser_role to ogg;
670 SQL> @ddl_enable.sql
671 SQL> @?/rdbms/admin/dbmspool.sql
672 SQL> @ddl_pin.sql ogg
三：db2上配置extract和pump进程
673 [oracle@db2 ogg]$ ggsci
674 GGSCI (db2) 1> dblogin userid ogg,password ogg
675 Successfully logged into database.
676
677 GGSCI (db2) 2> add trandata hr.*
678 GGSCI (db2) 5> view params eora_t2
679 extract eora_t2
680 setenv (NLS_LANG=AMERICAN_AMERICA.AL32UTF8)
681 userid ogg,password ogg
682 tranlogoptions excludeuser ogg //避免出现死循环复制，db1上的extract进程也需要进行此项设置
683 exttrail ./dirdat/ab
684 table hr.*;
685
686 GGSCI (db2) 6> add extract eora_t2,tranlog,begin now
687 EXTRACT added.
688
689 GGSCI (db2) 7> add exttrail ./dirdat/ab,extract eora_t2,megabytes 100
690 EXTTRAIL added.
691
692 GGSCI (db2) 8> start extract eora_t2
693 Sending START request to MANAGER …
694 EXTRACT EORA_T2 starting
695
696 GGSCI (db2) 13> view params pora_t2
697 extract pora_t2
698 setenv (NLS_LANG=AMERICAN_AMERICA.AL32UTF8)
699 passthru
700 rmthost 192.168.123.10,mgrport 7809
701 rmttrail ./dirdat/pb
702 table hr.*;
703
704 GGSCI (db2) 14> add extract pora_t2,exttrailsource ./dirdat/ab
705 EXTRACT added.
706
707 GGSCI (db2) 15> add rmttrail ./dirdat/pb extract pora_t2,megabytes 100
708 RMTTRAIL added.
709
710 GGSCI (db2) 19> start extract pora_t2
711 Sending START request to MANAGER …
712 EXTRACT PORA_T2 starting
713
714 GGSCI (db2) 20> info all
715 Program Status Group Lag at Chkpt Time Since Chkpt
716
717 MANAGER RUNNING
718 EXTRACT RUNNING EORA_T2 00:00:00 00:00:04
719 EXTRACT RUNNING PORA_T2 00:00:00 00:01:10
720 REPLICAT RUNNING RORA_T1 00:00:00 00:00:04
四：db1上配置replicat进程
721 GGSCI (db1) 7> view params rora_t2
722 replicat rora_t2
723 setenv (NLS_LANG=AMERICAN_AMERICA.AL32UTF8)
724 ddl include all
725 ddlerror default ignore retryop maxretries 3 retrydelay 5
726 userid ogg,password ogg
727 handlecollisions
728 assumetargetdefs
729 discardfile ./dirrpt/rora_t2.dsc,purge
730 map hr.* ,target hr.*;
731
732 GGSCI (db1) 1> add replicat rora_t2,exttrail ./dirdat/pb
733 REPLICAT added.
734
735 GGSCI (db1) 2> start replicat rora_t2
736 Sending START request to MANAGER …
737 REPLICAT RORA_T2 starting
738
739 GGSCI (db1) 3> info all
740 Program Status Group Lag at Chkpt Time Since Chkpt
741
742 MANAGER RUNNING
743 EXTRACT RUNNING EORA_T1 00:00:00 00:00:10
744 EXTRACT RUNNING PORA_T1 00:00:00 00:00:06
745 REPLICAT RUNNING RORA_T2 00:00:00 00:00:05
五：测试
746 [oracle@db1 ~]$ sqlplus hr/hr@db1
747 SQL> col location for a20
748 SQL> select * from t2;
749
750 ID NAME LOCATION
751 ———- ——————– ——————–
752 2 two china
753 1 one
754
755 SQL> update t2 set location=‘america’ where id=1;
756 1 row updated.
757
758 SQL> commit;
759 Commit complete.
760
761 SQL> select * from t2;
762
763 ID NAME LOCATION
764 ———- ——————– ——————–
765 2 two china
766 1 one america
767
768 SQL> conn hr/hr@db2
769 Connected.
770 SQL> select * from t2;
771
772 ID NAME LOCATION
773 ———- ——————– ——————–
774 2 two china
775 1 one american
776
777 SQL> insert into t2 values (3,’three’,’japan’);
778 1 row created.
779
780 SQL> commit;
781 Commit complete.
782
783 SQL> conn hr/hr@db1
784 Connected.
785 SQL> select * from t2;
786
787 ID NAME LOCATION
788 ———- ——————– ——————–
789 2 two china
790 1 one america
791 3 three japan
792
793
794 SQL> alter table t2 add sex char(4);
795 Table altered.
796
797 SQL> desc t2
798 Name Null? Type
799 —————————————– ——– —————————-
800 ID NOT NULL NUMBER
801 NAME VARCHAR2(20)
802 LOCATION VARCHAR2(200)
803 SEX CHAR(4)
804
805 SQL> conn hr/hr@db2
806 Connected.
807
808 SQL> desc t2
809 Name Null? Type
810 —————————————– ——– —————————-
811 ID NOT NULL NUMBER
812 NAME VARCHAR2(20)
813 LOCATION VARCHAR2(200)
814 SEX CHAR(4) 好了 上班的单实例与单实例就成功了
配置OGG RAC – —》知向单实例
第一步： 配置 网络监听
1.在RAC节点上配置ASM实例监听动态注册，确保goldengate用户能够连接到所有的ASM实例。
代码语言：javascript复制[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————————
代码语言：javascript复制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配置文件的管理：
代码语言：javascript复制[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 Agent1 netca
选择默认 公网网段
上面 添加监听就可以了 MACLEAN_LISTENER —-动态的方式添加的。
注意 ： 上面是添加监听 下面必须要整为静态注册，上面也是grid用户。
su – grid
netmgr
记住保存一下
之后退出就可以了 点击 EXIT 。
之后 使用命令重新启动一下就可乐就可以了。
代码语言：javascript复制[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 去解决这个问题。
下面是步骤
代码语言：javascript复制[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————————
代码语言：javascript复制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,红色的数字
代码语言：javascript复制[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会出现新的记录:
代码语言：javascript复制[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管理更为简便。
之后仅需要加入静态注册信息即可,如:
代码语言：javascript复制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)，并重启监听即完成静态注册：
代码语言：javascript复制[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结合这个来看：
代码语言：javascript复制815 [grid@rac1 rac1] tail -8 TNS_ADMIN/listener.ora
816 SID_LIST_LISTENER =
817 (SID_LIST =
818 (SID_DESC =
819 (GLOBAL_DBNAME = ASM)
820 (ORACLE_HOME=/u01/app/11.2.0/grid)
821 (SID_NAME = ASM1)
822 )
823 )
824
825 [grid@rac2 ~] tail -8 TNS_ADMIN/listener.ora
826 SID_LIST_LISTENER =
827 (SID_LIST =
828 (SID_DESC =
829 (GLOBAL_DBNAME = ASM)
830 (ORACLE_HOME=/u01/app/11.2.0/grid)
831 (SID_NAME = ASM2)
832 )
833 ) 好了 我的也是。
测试。
上面只是一个测试而已
怎样配置动态监听 ：
1 listerner.ora 添加： 每台都要添加
代码语言：javascript复制834 [grid@rac1 rac1] tail -8 TNS_ADMIN/listener.ora
835 SID_LIST_LISTENER =
836 (SID_LIST =
837 (SID_DESC =
838 (GLOBAL_DBNAME = ASM)
839 (ORACLE_HOME=/u01/app/11.2.0/grid) 这个是grid的安装目录
840 (SID_NAME = ASM1) ——–>自己改
841 )
842 )
2 vim tnsnames.ora 每天都要添加
843 ASM =
844 (DESCRIPTION =
845 (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.41)(PORT = 1521))
846 (CONNECT_DATA =
847 (SERVER = DEDICATED)
848 (SERVICE_NAME = ASM)
849 (SID_NAME = ASM1) —–>自己改
850 )
851 ) OK
创建ACFS
oracle 5.9 不支持 acfs 文件系统 怎么解决呢？
这个问题：
代码语言：javascript复制[root@m1 lib]# uname -a
Linux m1 2.6.39-300.26.1.el5uek #1 SMP Thu Jan 3 18:31:38 PST 2013 x86_64 x86_64 x86_64 GNU/Linux
[root@m1 lib]# 
