分类: Oracle
2012-01-04 13:53:57
在本配置中,目标是初始化源端ora9node1中jss.j1对象数据到目标端rhel5u3数据库。
安装goldengateOracle GoldenGate是个小软件(相比Oracle Database),该软件可以在oracle的官网直接下载:。注意,下载的GoldenGate版本一定要与database版本相匹配,否则无法正常使用。
GoldenGate的安装非常简单,详细步骤如下:
# su - oracle
$ mkdir /data/oracle/ora9i/ggate
$ unzip V22662-01.zip
$ tar xvf ggs_Linux_x64_ora9i_64bit_v11_1_1_0_0_078.tar -C /data/oracle/ora9i/ggate/基本上,安装就算完成了,其实就是解包解压缩。
为了方便调用,最好将相关的路径加入到环境变量中,GoldenGate与database如果是在同一个帐户下就更简单了(就像本例中这样),直接修改当前的环境变量,增加一些路径即可,本例中编译环境变量如下:
$ vi ~/.bash_profile
export ORACLE_BASE=/data/oracle/ora9i
export ORACLE_SID=jss9i
export PATH=$ORACLE_BASE/ggate:$PATH
export LD_LIBRARY_PATH=$ORACLE_BASE/ggate/:$LD_LIBRARY_PATH加载刚刚设置的环境变量:
进入ggsci命令行:
[oracle@ora9node1 ~]$ /data/oracle/ora9i/ggate/ggsci
Oracle GoldenGate Command Interpreter for Oracle
Version 11.1.1.0.0 Build 078
Linux, x64, 64bit (optimized), Oracle 9 on Jul 28 2010 15:51:02
Copyright (C) 1995, 2010, Oracle and/or its affiliates. All rights reserved.
GGSCI (ora9node1) 1>创建相关目录:
GGSCI (ora9node1) 1> create subdirs
Creating subdirectories under current directory /data/oracle/ora9i/ggate
Parameter files /data/oracle/ora9i/ggate/dirprm: created
Report files /data/oracle/ora9i/ggate/dirrpt: created
Checkpoint files /data/oracle/ora9i/ggate/dirchk: created
Process status files /data/oracle/ora9i/ggate/dirpcs: created
SQL script files /data/oracle/ora9i/ggate/dirsql: created
Database definitions files /data/oracle/ora9i/ggate/dirdef: created
Extract data files /data/oracle/ora9i/ggate/dirdat: created
Temporary files /data/oracle/ora9i/ggate/dirtmp: created
Veridata files /data/oracle/ora9i/ggate/dirver: created
Veridata Lock files /data/oracle/ora9i/ggate/dirver/lock: created
Veridata Out-Of-Sync files /data/oracle/ora9i/ggate/dirver/oos: created
Veridata Out-Of-Sync XML files /data/oracle/ora9i/ggate/dirver/oosxml: created
Veridata Parameter files /data/oracle/ora9i/ggate/dirver/params: created
Veridata Report files /data/oracle/ora9i/ggate/dirver/report: created
Veridata Status files /data/oracle/ora9i/ggate/dirver/status: created
Veridata Trace files /data/oracle/ora9i/ggate/dirver/trace: created
Stdout files /data/oracle/ora9i/ggate/dirout: created目标端也按照上述步骤安装GoldenGate并创建工作目录。
配置源端数据库源端数据库必须置于归档模式,force logging,并且启用supplemental logging。查看这几个选项是否启动,最简单的方式是查询v$database视图,例如:
SQL> select log_mode,supplemental_log_data_min,force_logging from v$database;
LOG_MODE SUP FOR
------------ --- ---
NOARCHIVELOG NO NO启用上述几个选项的操作如下,以sysdba身份登录到sqlplus命令行,执行下列命令:
--启动到mount状态:
startup mount;
--置于归档模式:
alter database archivelog;
--强制日志记录:
alter database force logging;
--启用最少附加日志
alter database add supplemental log data;
--启动数据库并查询状态:
SQL> alter database open;
Database altered.
SQL> select log_mode,supplemental_log_data_min,force_logging from v$database;
LOG_MODE SUP FOR
------------ --- ---
ARCHIVELOG YES YES注意,对于oracle9i版本来说,必须要将LOG_PARALLELISM参数值设置为1,GoldenGate不支持该参数值大于1。
创建GoldenGate管理用户:
SQL> create user ggate identified by ggate;
User created.
SQL> grant dba to ggate;
Grant succeeded.创建测试用户:
SQL> create user jss identified by jss default tablespace jsstbs quota unlimited on jsstbs;
User created.
SQL> grant connect,resource to jss;
Grant succeeded.初始化一个默认表:
SQL> create table j1 (id number not null ,vl varchar2(200) ,primary key(id));
Table created.
SQL> insert into j1 select rownum rn,object_name from all_objects;
23623 rows created.
SQL> commit;
Commit complete.目标端数据库同样需要创建jss/ggate两用户。同时,目标端数据库还需要创建j1表,但是不需要填充数据,初始化数据的操作将由goldengate来完成。
提示:目标库的用户名和对象名称可以与源端不同,关键在于配置文件中要能够正确匹配。另外,不要忘记配置源和目标两端tnsnames,保持互联互通。
配置源端goldengate查看信息:
GGSCI (ora9node1) 1> info all
Program Status Group Lag Time Since Chkpt
MANAGER STOPPED编译参数文件:
增加下列内容:
启动管理服务:
GGSCI (ora9node1) 3> start manager
Manager started.
GGSCI (ora9node1) 4> info all
Program Status Group Lag Time Since Chkpt
MANAGER RUNNING接下来,配置源端复制队列,先连接到数据库:
GGSCI (ora9node1) 5> dblogin userid ggate, password ggate
Successfully logged into database.增加一个抽取:
GGSCI (ora9node1) 6> add extract ext1,SOURCEISTABLE
EXTRACT added.
GGSCI (ora9node1) 7> info extract ext1, tasks
EXTRACT EXT1 Initialized 2011-06-24 13:51 Status STOPPED
Checkpoint Lag Not Available
Log Read Checkpoint Not Available
First Record Record 0
Task SOURCEISTABLE编辑配置文件:
增加下列内容,主要是配置目标端主机及要启动的任务:
extract ext1
userid ggate@source, password ggate
rmthost 172.16.1.110, mgrport 7809
RMTTASK REPLICAT, GROUP rep1
table jss.j1源端配置完成。
配置目标端goldengate查看信息:
GGSCI (rhel5u3) 1> info all
Program Status Group Lag Time Since Chkpt
MANAGER STOPPED编译参数文件:
增加下列内容:
启动管理服务:
GGSCI (rhel5u3) 3> start manager
Manager started.
GGSCI (rhel5u3) 4> info all
Program Status Group Lag Time Since Chkpt
MANAGER RUNNING接下来配置目标端的复制类,连接到目标端管理用户下:
GGSCI (rhel5u3) 1> dblogin userid ggate, password ggate
Successfully logged into database.增加一个复制类,命名为rep1,指定specialrun参数,让其作为任务运行:
GGSCI (rhel5u3) 2> add replicat rep1,specialrun
REPLICAT added.编辑该类的配置文件:
增加下列内容:
REPLICAT rep1
ASSUMETARGETDEFS
USERID ggate@target, PASSWORD goldengate
DISCARDFILE ./dirrpt/rep1_gg2.dsc, PURGE
MAP jss.*, TARGET jss.*;目标端配置完成!
同步数据仅需要在源端启动ext1即可,它会自动调用目标端的rep1:
GGSCI (ora9node1) 46> start extract ext1
Sending START request to MANAGER ...
EXTRACT EXT1 starting而后,目标端查询数据量:
SQL> select count(0) from jss.j1;
COUNT(0)
----------
23623数据量无误,可以看到,数据已然同步。
两端均可以使用view report查看任务的详细信息,例如,源端查看ext1的详细信息:
GGSCI (ora9node1) 47> view report ext1
2011-06-23 14:42:25 INFO OGG-01017 Wildcard resolution set to IMMEDIATE because SOURCEISTABLE is used.
***********************************************************************
Oracle GoldenGate Capture for Oracle
Version 11.1.1.0.0 Build 078
Linux, x64, 64bit (optimized), Oracle 9 on Jul 28 2010 16:08:42
Copyright (C) 1995, 2010, Oracle and/or its affiliates. All rights reserved.
Starting at 2011-06-24 14:42:25
***********************************************************************
Operating System Version:
Linux
Version #1 Wed May 2 15:01:08 PDT 2007, Release 2.6.9-55.0.0.0.2.EL
Node: ora9node1
Machine: x86_64
soft limit hard limit
Address Space Size : unlimited unlimited
Heap Size : unlimited unlimited
File Size : unlimited unlimited
CPU Time : unlimited unlimited
Process id: 5981
Description:
***********************************************************************
** Running with the following parameters **
***********************************************************************
extract ext1
userid ggate, password *****
rmthost 172.16.1.110, mgrport 7809
RMTTASK REPLICAT, GROUP rep1
table jss.j1;
Using the following key columns for source table JSS.J1: ID.
CACHEMGR virtual memory values (may have been adjusted)
CACHEBUFFERSIZE: 64K
CACHESIZE: 8G
CACHEBUFFERSIZE (soft max): 4M
CACHEPAGEOUTSIZE (normal): 4M
PROCESS VM AVAIL FROM OS (min): 16G
CACHESIZEMAX (strict force to disk): 13.99G
Database Version:
Oracle9i Enterprise Edition Release 9.2.0.8.0 - 64bit Production
PL/SQL Release 9.2.0.8.0 - Production
CORE 9.2.0.8.0 Production
TNS for Linux: Version 9.2.0.8.0 - Production
NLSRTL Version 9.2.0.8.0 - Production
Database Language and Character Set:
NLS_LANG = "AMERICAN_AMERICA.ZHS16GBK"
NLS_LANGUAGE = "AMERICAN"
NLS_TERRITORY = "AMERICA"
NLS_CHARACTERSET = "ZHS16GBK"