平台：HP IA 64
源库：11.2.0.3
目标库：11.1.0.6
实现方式：实现一次性抽取
OGG的实现方式很多，最简单的莫过于一次性抽取，即只在启动extract进程时同步。待此次同步完成后，如表中数据变化则目标库中的数据不随之变化。
1. 两边配置监听
2. 安装软件包
源库：
$ ll   /home/oratest/ogg/
-rw-r--r--   1 oratest    oinstall   78964027 Jan 10 11:32 p14764589_112104_HPUX-IA64.zip
$ unzip p14764589_112104_HPUX-IA64.zip
Archive: p14764589_112104_HPUX-IA64.zip
inflating: ggs_HPUX_ia64_ora11g_64bit.tar
inflating: Oracle-GoldenGate-11.2.1.0.4-README.doc
inflating: Oracle-GoldenGate-11.2.1.0.4-README.txt
inflating: OGG-WinUnix-Rel-Notes-11.2.1.0.4.pdf
$ ls
OGG-WinUnix-Rel-Notes-11.2.1.0.4.pdf     Oracle-GoldenGate-11.2.1.0.4-README.txt p14764589_112104_HPUX-IA64.zip
Oracle-GoldenGate-11.2.1.0.4-README.doc ggs_HPUX_ia64_ora11g_64bit.tar
$ tar -xvf ggs_HPUX_ia64_ora11g_64bit.tar
目标库：
$ ll   /home/orazkf/ogg/
-rw-r--r--   1 oratest    oinstall   78964027 Jan 10 11:32 p14764589_112104_HPUX-IA64.zip
$ unzip p14764589_112104_HPUX-IA64.zip
Archive: p14764589_112104_HPUX-IA64.zip
inflating: ggs_HPUX_ia64_ora11g_64bit.tar
inflating: Oracle-GoldenGate-11.2.1.0.4-README.doc
inflating: Oracle-GoldenGate-11.2.1.0.4-README.txt
inflating: OGG-WinUnix-Rel-Notes-11.2.1.0.4.pdf
$ ls
OGG-WinUnix-Rel-Notes-11.2.1.0.4.pdf     Oracle-GoldenGate-11.2.1.0.4-README.txt p14764589_112104_HPUX-IA64.zip
Oracle-GoldenGate-11.2.1.0.4-README.doc ggs_HPUX_ia64_ora11g_64bit.tar
$ tar -xvf ggs_HPUX_ia64_ora11g_64bit.tar
3. 配置环境变量
源库：
umask 022
export ORACLE_SID=TEST
export ORACLE_BASE=/pmsdb/oracle/$ORACLE_SID
export ORACLE_HOME=$ORACLE_BASE/11.2.0
export LD_LIBRARY_PATH=/home/oratest/ogg:$ORACLE_HOME/lib:$ORACLE_HOME/rdbms/lib:/lib:/usr/lib
export ORACLE_TERM=xterm
export PATH=/home/oratest/ogg:$ORACLE_HOME/bin:$PATH
目标库：
umask 022
export ORACLE_SID=ZKFP1
export ORACLE_BASE=/pmsdb/oracle/ZKFT1
export ORACLE_HOME=$ORACLE_BASE/11.1.0
export ORACLE_TERM=xterm
export PATH=/home/orazkf/ogg:$ORACLE_HOME/bin:$PATH
export LD_LIBRARY_PATH=/home/oratest/ogg:$ORACLE_HOME/lib:$ORACLE_HOME/rdbms/lib:/lib:/usr/lib
export NLS_LANG=AMERICAN_AMERICA.AL32UTF8
4 创建目录
源库：
GGSCI (I2TRNDB2) 7> exit
$ cd ogg
$ ggsci
Oracle GoldenGate Command Interpreter for Oracle
Version 11.2.1.0.4 14636914 OGGCORE_11.2.1.0.4_PLATFORMS_121007.2020
HP/UX, IA64, 64bit (optimized), Oracle 11g on Oct 8 2012 02:25:02
Copyright (C) 1995, 2012, Oracle and/or its affiliates. All rights reserved.
GGSCI (I2TRNDB2) 1> create subdirs
Creating subdirectories under current directory /home/oratest/ogg
Parameter files                /home/oratest/ogg/dirprm: already exists
Report files                   /home/oratest/ogg/dirrpt: created
Checkpoint files               /home/oratest/ogg/dirchk: created
Process status files           /home/oratest/ogg/dirpcs: created
SQL script files               /home/oratest/ogg/dirsql: created
Database definitions files     /home/oratest/ogg/dirdef: created
Extract data files             /home/oratest/ogg/dirdat: created
Temporary files                /home/oratest/ogg/dirtmp: created
Stdout files                   /home/oratest/ogg/dirout: created
port 7809
目标库：
$ ggsci
Oracle GoldenGate Command Interpreter for Oracle
Version 11.2.1.0.4 14636914 OGGCORE_11.2.1.0.4_PLATFORMS_121007.2020
HP/UX, IA64, 64bit (optimized), Oracle 11g on Oct 8 2012 02:25:02
Copyright (C) 1995, 2012, Oracle and/or its affiliates. All rights reserved.
GGSCI (I2TRNDB1) 1> create subdirs
Creating subdirectories under current directory /home/orazkf/ogg
Parameter files                /home/orazkf/ogg/dirprm: already exists
Report files                   /home/orazkf/ogg/dirrpt: created
Checkpoint files               /home/orazkf/ogg/dirchk: created
Process status files           /home/orazkf/ogg/dirpcs: created
SQL script files               /home/orazkf/ogg/dirsql: created
Database definitions files     /home/orazkf/ogg/dirdef: created
Extract data files             /home/orazkf/ogg/dirdat: created
Temporary files                /home/orazkf/ogg/dirtmp: created
Stdout files                   /home/orazkf/ogg/dirout: created
5 打开归档，force logging，补充日志
源库：
SQL> select log_mode,supplemental_log_data_min,force_logging from v$database;

LOG_MODE     SUPPLEME FOR
------------ -------- ---
ARCHIVELOG   YES      YES
备注：由于实验为单向复制，所以只打开源库的这些信息即可。
6 创建ogg管理账户
源库：
SQL> create user ogguser identified by ogguser;
User created.
SQL> grant dba to ogguser;
Grant succeeded.
创建测试用户：
SQL> create user maomao identified by maomao default tablespace user1;
User created.
SQL> grant connect,resource to maomao;
Grant succeeded.
SQL> create table maomao.ogg_tab as select * from dba_objects;
Table created.
目标库：
SQL> create user ogguser identified by ogguser;
User created.
SQL> grant dba to ogguser;
Grant succeeded.
创建测试用户：
SQL> create user maomao identified by maomao default tablespace users;
User created.
SQL> grant connect,resource to maomao;
Grant succeeded.
SQL> create table maomao.ogg_tab as select * from dba_objects where 1=0;
Table created.
备注：这里目标库只创建表即可，不需要插入数据，配置完gg后，数据将会从源库中传递过来。
7. 配置源库和目标库GolgenGate
源库和目标库配置端口和启动服务：
GGSCI (I2TRNDB2) 2> edit params mgr
"/home/oratest/ogg/dirprm/mgr.prm" [New file]
port 7809---插入
"/home/oratest/ogg/dirprm/mgr.prm" [New file] 1 line, 10 characters --保存退出
GGSCI (I2TRNDB2) 3> start mgr
Manager started.
GGSCI (I2TRNDB2) 4> info all
Program     Status      Group       Lag at Chkpt Time Since Chkpt
MANAGER     RUNNING
备注：mgr复制管理gg整体概况，管理各个进程的启动与关闭，监控各个资源和进程的情况。
8 登陆
源库与目标库：
GGSCI (I2TRNDB2) 1> dblogin userid ogguser,password ogguser
Successfully logged into database.
9 源库添加需要传输的用户表
GGSCI (I2TRNDB2) 16> add trandata maomao.ogg_tab
2014-01-13 11:38:22 WARNING OGG-00869 No unique key is defined for table 'OGG_TAB'. All viable columns will be used to represent the key, but may not guarantee uniqueness. KEYCOLS may be used to define the key.
Logging of supplemental redo data enabled for table MAOMAO.OGG_TAB.
备注：主键和变更值输出到redo日志。所以capture的过程要求表有主键。没有主键的话也没有关系，这只是一个告警。另外，这里如果不做添加这步应该也是可以的。
GGSCI (I2TRNDB2) 17> info trandata maomao.*
Logging of supplemental redo log data is enabled for table MAOMAO.OGG_TAB.
Columns supplementally logged for table MAOMAO.OGG_TAB: OBJECT_TYPE, STATUS, NAMESPACE, EDITION_NAME, LAST_DDL_TIME, TEMPORARY, OWNER, OBJECT_NAME, OBJECT_ID, CREATED, GENERATED, SECONDARY, SUBOBJECT_NAME, DATA_OBJECT_ID, TIMESTAMP.
10 配置源数据库extract进程
添加extract进程ext1：
GGSCI (I2TRNDB2) 2> add extract ext1,sourceistable-----SOURCEISTABLE表示初始化整个表
GGSCI (I2TRNDB2) 3> info extract *,tasks
EXTRACT    EXT1      Initialized   2014-01-10 15:01   Status STOPPED
Checkpoint Lag       Not Available
Log Read Checkpoint Not Available
                     First Record         Record 0
Task                 SOURCEISTABLE
编辑ext1的配置信息：
GGSCI (I2TRNDB2) 4> edit params ext1
extract ext1
userid ogguser,password ogguser
rmthost 192.168.160.233, mgrport 7809
rmttask replicat,group rep1
table maomao.ogg_tab;
备注：192.168.160.233是目标库的ip。table那行也可使用maomao.*，他是支持通配符的。千万不要忘记加分号
11 目标数据库配置replicat进程
添加replicat进程rep1：
GGSCI (I2TRNDB1) 6> add replicat rep1,specialrun---------SPECIALRUN表示只抽取一次
GGSCI (I2TRNDB1) 8> INFO REPLICAT *, TASKS
REPLICAT   REP1      Initialized   2014-01-10 15:07   Status STOPPED
Checkpoint Lag       00:00:00 (updated 68:08:13 ago)
Log Read Checkpoint Not Available
Task                 SPECIALRUN
编辑rep1的配置信息：
GGSCI (I2TRNDB1) 9> edit params rep1
REPLICAT rep1
ASSUMETARGETDEFS---这里表示oracle-oracle的同结果传输
userid ogguser, password ogguser
DISCARDFILE ./dirrpt/rep1_gg2.dsc, PURGE
map maomao.*, target maomao.*;
12 启动传输
GGSCI (I2TRNDB2) 5> start extract ext1
Sending START request to MANAGER ...
EXTRACT EXT1 starting
GGSCI (I2TRNDB2) 6> info all
Program     Status      Group       Lag at Chkpt Time Since Chkpt
MANAGER     RUNNING
GGSCI (I2TRNDB2) 7> view report ext1
GGSCI (I2TRNDB1) 13> view report rep1
这里一次性抽取，只需要启动extract进程即可，不需要启动replicat进程，用info all的时候只能看到mgr进程，看不到extract等进程。

在源数据库再创建新表：
SQL> create table t3(id int);
Table created.
SQL> insert into t3 values(8);
1 row created.
SQL> commit;
Commit complete.
目标：
SQL> select * from t3;
no rows selected
查看原库信息：
GGSCI (I2TRNDB2) 1> dblogin userid ogguser,password ogguser
Successfully logged into database.
GGSCI (I2TRNDB2) 2> info trandata maomao.*
Logging of supplemental redo log data is enabled for table MAOMAO.OGG_TAB.
Columns supplementally logged for table MAOMAO.OGG_TAB: OBJECT_TYPE, STATUS, NAMESPACE, EDITION_NAME, LAST_DDL_TIME, TEMPORARY, OWNER, OBJECT_NAME, OBJECT_ID, CREATED, GENERATED, SECONDARY, SUBOBJECT_NAME, DATA_OBJECT_ID, TIMESTAMP.
Logging of supplemental redo log data is disabled for table MAOMAO.T3.
目标库：
GGSCI (I2TRNDB1) 3> info trandata maomao.*
Logging of supplemental redo log data is disabled for table MAOMAO.OGG_TAB.
Logging of supplemental redo log data is disabled for table MAOMAO.T3.
GGSCI (I2TRNDB2) 4> start extract ext1---启动ext1
Sending START request to MANAGER ...
EXTRACT EXT1 starting
查看目标库表的结果：
SQL> select * from t3;
ID
----------
8

ITPub博客

GolgenGate-一次性抽取

最新文章