源端安装Linux64:
[oracle@sgpc146 ggs]$ pwd
/s01/ggs
[oracle@sgpc146 ggs]$ ll
total 268088
-rw-r--r-- 1 root root 274247680 Oct 10 20:19 fbo_ggs_Linux_x64_ora11g_64bit.tar
[oracle@sgpc146 ggs]$ tar -xvof fbo_ggs_Linux_x64_ora11g_64bit.tar
[oracle@sgpc146 ggs]$ ./ggsci
Oracle GoldenGate Command Interpreter for Oracle
Version 11.1.1.1.2 OGGCORE_11.1.1.1.2_PLATFORMS_111004.2100
Linux, x64, 64bit (optimized), Oracle 11g on Oct 4 2011 23:49:46
Copyright (C) 1995, 2011, Oracle and/or its affiliates. All rights reserved.
GGSCI (sgpc146) 1> CREATE SUBDIRS
Creating subdirectories under current directory /s01/ggs
Parameter files /s01/ggs/dirprm: created
Report files /s01/ggs/dirrpt: created
Checkpoint files /s01/ggs/dirchk: created
Process status files /s01/ggs/dirpcs: created
SQL script files /s01/ggs/dirsql: created
Database definitions files /s01/ggs/dirdef: created
Extract data files /s01/ggs/dirdat: created
Temporary files /s01/ggs/dirtmp: created
Veridata files /s01/ggs/dirver: created
Veridata Lock files /s01/ggs/dirver/lock: created
Veridata Out-Of-Sync files /s01/ggs/dirver/oos: created
Veridata Out-Of-Sync XML files /s01/ggs/dirver/oosxml: created
Veridata Parameter files /s01/ggs/dirver/params: created
Veridata Report files /s01/ggs/dirver/report: created
Veridata Status files /s01/ggs/dirver/status: created
Veridata Trace files /s01/ggs/dirver/trace: created
Stdout files /s01/ggs/dirout: created
GGSCI (sgpc146) 2>
SQL> create user ggs identified by ggs default tablespace users temporary tablespace temp quota unlimited on users ;
User created.
Elapsed: 00:00:00.06
SQL> grant dba to ggs;
Grant succeeded.
Elapsed: 00:00:00.02
SQL>
SQL> alter database archivelog;
Database altered.
Elapsed: 00:00:00.04
SQL>
SQL> select supplemental_log_data_min from v$database;
SUPPLEME
--------
NO
Elapsed: 00:00:00.00
SQL> alter database add supplemental log data;
Database altered.
Elapsed: 00:00:00.02
SQL> select supplemental_log_data_min from v$database;
SUPPLEME
--------
YES
Elapsed: 00:00:00.01
SQL>
SQL> alter system set "recyclebin"=off;
alter system set "recyclebin"=off
*
ERROR at line 1:
ORA-02096: specified initialization parameter is not modifiable with this option
Elapsed: 00:00:00.00
SQL>
SQL> alter system set recyclebin=off scope=spfile;
System altered.
Elapsed: 00:00:00.03
SQL> startup force;
ORACLE instance started.
Total System Global Area 2.1179E+10 bytes
Fixed Size 2237648 bytes
Variable Size 9999223600 bytes
Database Buffers 1.1140E+10 bytes
Redo Buffers 36995072 bytes
Database mounted.
Database opened.
SQL> show parameter recyclebin
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
recyclebin string OFF
SQL>
单向复制不需要这个:
GGSCI (sgpc146) 2> edit params ./GLOBALS
CHECKPOINTTABLE ggs.ggchkptable
~
GGSCI (sgpc146) 2> dblogin userid ggs,password ggs
Successfully logged into database.
GGSCI (sgpc146) 3> add checkpointtable ggs.checkpoint
Successfully created checkpoint table GGS.CHECKPOINT.
--删除
GGSCI (sgpc146) 42> delete CHECKPOINTTABLE ggs.ggchkptable
This checkpoint table may be required for other installations. Are you sure you want to delete this checkpoint table? y
Successfully deleted checkpoint table GGS.GGCHKPTABLE.
GGSCI (sgpc146) 43>
GGSCI (sgpc146) 4>
GGSCI (sgpc146) 4> edit params mgr
port 7809
dynamicportlist 7800-8000
autorestart extract *,waitminutes 2,resetminutes 5
~
~
GGSCI (sgpc146) 5> start mgr
Manager started.
GGSCI (sgpc146) 5> info all
Program Status Group Lag Time Since Chkpt
MANAGER RUNNING
GGSCI (sgpc146) 6> edit params eora
extract eora
dynamicresolution
userid ggs,password ggs
exttrail /s01/ggs/dirdat/et
table "000".*;
GGSCI (sgpc146) 9> alter extract eora,tranlog,begin now
ERROR: EXTRACT EORA does not exist.
GGSCI (sgpc146) 13> add extract eora,tranlog,begin now
EXTRACT added.
GGSCI (sgpc146) 14> info extract eora
EXTRACT EORA Initialized 2011-10-10 23:16 Status STOPPED
Checkpoint Lag 00:00:00 (updated 00:01:03 ago)
Log Read Checkpoint Oracle Redo Logs
2011-10-10 23:16:14 Seqno 0, RBA 0
GGSCI (sgpc146) 15>
GGSCI (sgpc146) 2> add exttrail /s01/ggs/dirdat/et,extract eora
EXTTRAIL added.
GGSCI (sgpc146) 3> info all
Program Status Group Lag Time Since Chkpt
MANAGER RUNNING
EXTRACT STOPPED EORA 00:00:00 00:09:08
GGSCI (sgpc146) 4> start extract eora
Sending START request to MANAGER ...
EXTRACT EORA starting
GGSCI (sgpc146) 5> info all
Program Status Group Lag Time Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING EORA 00:09:21 00:00:05
GGSCI (sgpc146) 6>
用add,alter,cheanup,delete,info,kill命令管理extract进程。
GGSCI (sgpc146) 6> edit params pump_so
extract pump_so
dynamicresolution
passthru
rmthost 129.100.253.107,mgrport 7809,compress
rmttrail C:\ggs\dirdat\pt
table "000".*;
~
~
~
~
~
~
"dirprm/pump_so.prm" [New] 6L, 130C written
GGSCI (sgpc146) 7> add extract pump_so,exttrailsource /s01/ggs/dirdat/et
EXTRACT added.
GGSCI (sgpc146) 8>
GGSCI (sgpc146) 8> add rmttrail C:\ggs\dirdat\pt,extract pump_so
RMTTRAIL added.
GGSCI (sgpc146) 9>
目标端安装Win64
C:\ggs>install addservice addevents
Oracle GoldenGate messages installed successfully.
Service 'GGSMGR' created.
Install program terminated normally.
C:\ggs>ggsci
GGSCI (JSceshi) 1> create subdirs
Creating subdirectories under current directory C:\ggs
Parameter files C:\ggs\dirprm: created
Report files C:\ggs\dirrpt: created
Checkpoint files C:\ggs\dirchk: created
Process status files C:\ggs\dirpcs: created
SQL script files C:\ggs\dirsql: created
Database definitions files C:\ggs\dirdef: created
Extract data files C:\ggs\dirdat: created
Temporary files C:\ggs\dirtmp: created
Veridata files C:\ggs\dirver: created
Veridata Lock files C:\ggs\dirver\lock: created
Veridata Out-Of-Sync files C:\ggs\dirver\oos: created
Veridata Out-Of-Sync XML files C:\ggs\dirver\oosxml: created
Veridata Parameter files C:\ggs\dirver\params: created
Veridata Report files C:\ggs\dirver\report: created
Veridata Status files C:\ggs\dirver\status: created
Veridata Trace files C:\ggs\dirver\trace: created
Stdout files C:\ggs\dirout: created
GGSCI (JSceshi) 2> edit param mgr
port 7809
dynamicportlist 7800-8000
autostart er *
autorestart extract *,waitminutes 2,retries 5
lagreporthours 1
laginfominutes 3
lagcriticalminutes 5
purgeoldextracts C:\ggs\rt*,usecheckpoints,minkeepdays 3
GGSCI (JSceshi) 3> edit param rep1
replicat rep1
userid ggs,password ggs
assumetargetdefs
reperror default,discard
discardfile C:\ggs\dirrpt\rep1.dsc,append,megabytes 50
dynamicresolution
map "000".*,target "000".*;
SQL> create user ggs identified by ggs default tablespace users temporary tablespace temp quota unlimited on users ;
User created.
Elapsed: 00:00:00.06
SQL> grant dba to ggs;
Grant succeeded.
Elapsed: 00:00:00.02
GGSCI (JSceshi) 5> add replicat rep1,exttrail C:\ggs\dirdat\pt
ERROR: No checkpoint table specified for ADD REPLICAT.
GGSCI (JSceshi) 6> add replicat rep1,exttrail C:\ggs\dirdat\pt,nodbcheckpoint
REPLICAT added.
edit params rep1
replicat rep1
userid ggs@SGERP5_107,password ggs
assumetargetdefs
reperror default,discard
discardfile C:\ggs\dirrpt\rep1.dsc,append,megabytes 50
dynamicresolution
map emos.*, target emos.*;
--MAP emos.tb_cust, TARGET emos.tb_cust;
测试:
create table "000".t1(id int primary key);
INSERT into "000".t1 values(1);
commit;
select * from "000".t1;
GGSCI (sgpc146) 26> stats *
Sending STATS request to EXTRACT EORA ...
No active extraction maps.
Sending STATS request to EXTRACT PUMP_SO ...
No active extraction maps.
GGSCI (sgpc146) 27> info all
Program Status Group Lag Time Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING EORA 00:00:00 00:00:05
EXTRACT RUNNING PUMP_SO 00:00:00 00:00:00
GGSCI (sgpc146) 28>
GGSCI (JSceshi) 23> info all
Program Status Group Lag Time Since Chkpt
MANAGER RUNNING
REPLICAT RUNNING REP1 00:00:00 00:00:00
GGSCI (JSceshi) 24> info replicat *
REPLICAT REP1 Last Started 2012-10-11 13:18 Status RUNNING
Checkpoint Lag 00:00:00 (updated 00:00:09 ago)
Log Read Checkpoint File C:\ggs\dirdat\pt000002
2012-10-11 13:29:16.446465 RBA 1172
GGSCI (JSceshi) 25>
stats *
info *
GGSCI (sgpc146) 34> info extract eora, showch
EXTRACT EORA Last Started 2012-10-12 13:20 Status RUNNING
Checkpoint Lag 00:00:00 (updated 00:00:09 ago)
Log Read Checkpoint Oracle Redo Logs
2012-10-12 13:34:02 Seqno 1995, RBA 19598848
Current Checkpoint Detail:
Read Checkpoint #1
Oracle Redo Log
说明:
GGSCI>create subdirs --不需要指定路径,默认在当前目录下(所以进入ggsci的时候,一定要在gg的当前目录)
新建出来的目录有:
name Purpose
--dirchk Checkpoint files存放检查点(Checkpoint)文件
(和Oracle的checkpoint含义不同,如oralce的一个实例失败,从最近一次的checkpoint开始recorver, oracle的一次
checkpoint是让dbwrt进程把被修改的数据从数据缓冲区中写入数据文件。而OGG的checkpoint是OGG捕捉了很多事务日志,哪些
被传输了,哪些未被传输。每隔一段时间,执行一次checkpoint,之前的所有数据都要传送过去。如果soure or target db断电了,
那下次启动的时候,就要支持断点续传,那那个断点位置就是最后一次OGG执行checkpoint的位置)
--dirdat GoldenGate trails, 存放Trail与Extract文件
--dirdef Data Definition files,过DEFGEN工具生成的源或目标的数据定义文件
--dirprm Parameter files, 存放参数文件
--dirpcs Process status files, 存放进程状态文件
--dirrpt Report files, 存放进程报告文件
--dirsql SQL script files,存放SQL脚本文件
--dirtmp Temporary files,当事务所需要的内存超过已分配内存时,缺省存储于此
--在源端设置哪些表被加入到TRANDATA
Enable transaction data change capture for these two table in source system
GGSCI>DBLOGIN USERID ogg, PASSWORD ogg #dblogin是OGG里面的一个命令
GGSCI>ADD TRANDATA scott.EMP_OGG #ADD TRANDATA是将源端和目标端的两张对应表执行第一次同步(即初始化同步操作)
GGSCI>ADD TRANDATA scott.DEPT_OGG #删除表补全日志
如果该表没有主键或唯一索引,那么指定唯一标示的字段,否则会用所有的字段来标示,同时其还有字段列数限制
GGSCI>ADD TRANDATA OWNER.table_name, nokey, cols(column,column,....)
Verify that supplemental logging has been turned on for these tables (验证一下归档日志是否确实被打开)
GGSCI>INFO TRANDATA scott.emp* #查看scott用户中以emp开头的表是否打开了trandata设置,是否会执行初始化同步
Logging of supplemental redo log data is disabled[未打开](enabled[已打开])for table SCOTT.EMP1
附加解释:这里的TRANDATA和5.2的alter database add supplemental log data的区别是只有在库级的附加日志打开的情况下,表级的附加日志才有效果。
表级补全日志需要在库级最小补全日志打开的情况下才起作用,只开启库级最小补全日志,redolog记录的信息还不够全面,必须再使用
add trandata开始表级的补全日志以获得必要的信息。
需要同步的表必须设置本步骤的补全日志,否则后面数据可能不能同步成功