oracle gg 简单使用
来源：互联网 发布：linux监控软件对比 编辑：程序博客网 时间：2024/04/28 08:28
一、Oracle gg介绍
下载地址：http://www.oracle.com/technetwork/middleware/goldengate/downloads/index.html
官方参考资料地址：http://docs.oracle.com/cd/E35209_01/index.htm
GoldenGate Transactional Data Management (TDM)，可以在异构的IT基础结构之间实现大量数据的秒一级的数据捕捉、转换和投递。GoldenGate可以支持几乎所有常用操作系统如和数据库平台，
GoldenGate软件提供了一个单一的平台，这个平台可以为任何企业环境实现秒一级的灾难备份。GoldenGate是一种基于于日志的结构化数据复制方式，它通过解析源数据库在线日志或归档日志获得数据的增删改变化（数据量只有日志的四分之一左右），再将这些变化应用到目标数据库，实现源数据库与目标数据库同步、双活。
GoldenGate TDM的数据复制过程如下：
1、利用捕捉进程(Extract Process)在源系统端读取Online Redo Log戒Archive Log，然后进行解析，只提取其中数据的变化如增、删、改操作，开将相关信息转换为GoldenGate TDM自定义的中间格式存放在队列文件(trail file)中。再利用传送进程将队列文件通过TCP/IP传送到目标系统。捕捉进程在每次读完log中的数据变化开在数据传送到目标系统后，会写检查点，记录当前完成捕捉的log位置，检查点的存在可以使捕捉进程在中止开恢复后可从检查点位置继续复制。
2、目标系统接受数据变化开缓存到GoldenGate TDM队列当中，队列为系列临时存储数据变化的文件，等待投递迚程读取数据。
3、GoldenGate TDM投递过程(replicat process)从队列中读取数据变化开创建对应的SQL语句，通过数据库的本地接口执行，提交到数据库成功后更新自己的检查点，记彔已经完成复制的位置，数据的复制过程最终完成。
由此可见，GoldenGate TDM是一种基于软件的数据复制方式，它从数据库的日志解析数据的变化（数据量只有日志的四分之一左右）。GoldenGate TDM将数据变化转化为自己的格式，直接通过TCP/IP网络传输，无需依赖于数据库自身的传递方式，而丏可以通过高达10:1的压缩率对数据迚行压缩，可以大大降低带宽需求。在目标端，GoldenGate TDM可以通过交易重组，分批加载等技术手段大大加快数据投递的速度和效率，降低目标系统的资源占用，可以在亚秒级实现大量数据的复制，并且目标端数据库是活动的。
另外它支持的系统
包括常见的windows linux，unix，数据库有sql server db2，mysql等主流
二、实现
说明，本文实现简单的单向复制
资源
原端：
Linux rel-135 2.6.39-200.24.1.el6uek.x86_64 #1 SMP Sat Jun 23 02:39:07 EDT 2012 x86_64 x86_64 x86_64 GNU/Linux
Oracle 11g r2
Oracle_sid: db11g
目标：
Linux fedora.localdomain 3.3.4-5.fc17.x86_64 #1 SMP Mon May 7 17:29:34 UTC 2012 x86_64 x86_64 x86_64 GNU/Linux
Oracle 11g r2
Oracle_sid: orcl
搭建步骤：
1、 创建帐户和授权（原目标端）
create user ogg identified by ogg；
grant connect,resource to ogg;
grant ALTER ANY TABLE, ALTER SESSION, CREATE SESSION, FLASHBACK ANY TABLE, SELECT ANY DICTIONARY, SELECT ANY TABLE, RESOURCE, drop ANY TABLE, delete any table, execute on dbms_flashback, to ogg
2、 使用归档模式等参数—原端
SQL> startup mount
SQL> alter database archivelog;
SQL> alter database force logging;
SQL> alter database add supplemental log data;
SQL> alter database add supplemental log data(primary key,unique,foreign key)columns;
3、 安装gg
解压到指定文件夹，我的在/u01/app/oracle/ogg下
然后启动ogg，如果使用./ggsci报错，需要检查环境变量中设置是否正确，我的是
LD_LIBRARY_PATH=$ORACLE_HOME/bin:/bin:/usr/bin:/usr/local/bin:/$ORACLE_HOME/lib:/lib:/usr/lib;
最后执行一些语句，只用超管登陆数据库
SQL> @marker_setup
SQL> alter system set recyclebin=off；
SQL> @ddl_setup
SQL> @role_setup
SQL> grant ggs_ggsuser_role to ogg;
SQL> @ddl_enable
SQL> @?/rdbms/admin/dbmspool.sql
最后创建目录，登陆ogg，执行，我这是为了演示，其实我已经创建完了
GSCI (rel-135) 46> create subdirs
Creating subdirectories under current directory /u01/app/oracle/ogg
Parameter files /u01/app/oracle/ogg/dirprm: already exists
Report files /u01/app/oracle/ogg/dirrpt: already exists
Checkpoint files /u01/app/oracle/ogg/dirchk: already exists
Process status files /u01/app/oracle/ogg/dirpcs: already exists
SQL script files /u01/app/oracle/ogg/dirsql: already exists
Database definitions files /u01/app/oracle/ogg/dirdef: already exists
Extract data files /u01/app/oracle/ogg/dirdat: already exists
Temporary files /u01/app/oracle/ogg/dirtmp: already exists
Stdout files /u01/app/oracle/ogg/dirout: already exists
4、 配置
原端
在ogg控制台中
GGSCI (rel-135) 47> edit params mgr
Port 7950
启动manager
GGSCI (rel-135) 7>start mgr
使用ogg登陆
GGSCI (rel-135) 8>dblogin userid ogg, password ogg
GGSCI (rel-135)9> add trandata scott.t1
注意t1一定要有主键，唯一或者是外键
GGSCI (rel-135)10>ADD EXTRACT extr01,TRANLOG,BEGIN NOW
GGSCI (rel-135)11>ADD EXTTRAIL ./dirdat/VA,EXTRACT extr01,megabytes 100
GGSCI (rel-135)12>editparams extr01
extract extr01
SETENV (NLS_LANG=AMERICAN_AMERICA.ZHS16GBK)
setenv(ORALCE_SID=DB11G)
userid ogg,password ogg
dynamicresolution
tranlogoptions altarchivelogdest ./arch01
ddl include all
EXTTRAIL ./dirdat/VA
table scott.t1;
GGSCI (rel-135)13>ADD EXTRACT pump01,EXTTRAILSOURCE ./dirdat/VA
GGSCI (rel-135)14> ADD RMTTRAIL ./dirdat/VA,EXTRACT pump01
GGSCI (rel-135)15> edit params pump01
EXTRACT pump01
passthru
DYNAMICRESOLUTION
RMTHOST 192.168.9.79, MGRPORT 7950, COMPRESS
RMTTRAIL /u01/app/oracle/ogg/dirdat/wf
table scott.*;
启动进程start *
GGSCI (rel-135) 45> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING EXTR01 00:00:00 00:00:09
EXTRACT RUNNING PUMP01 00:00:00 00:00:07
目标端：
GGSCI (fedora.localdomain) 2> edit params mgr
port 7950
GGSCI (fedora.localdomain) 3> start mgr
Manager started.
GGSCI (fedora.localdomain) 4>add replicat trep01,exttrail ./dirdat/VA,nodbcheckpoint
REPLICAT added.
GGSCI (fedora.localdomain) 5> edit params trep01
--Replicat group --
replicat trep01
SETENV (NLS_LANG=AMERICAN_AMERICA.ZHS16GBK)
SETENV (ORACLE_SID=orcl)
--target database login --
userid ogg, password ogg
--source and target definitions
ASSUMETARGETDEFS
ALLOWNOOPUPDATES
--file for dicarded transaction --
discardfile ./dirrpt/discard_VA.dsc,PURGE
--ddl support DDL
DDL INCLUDE MAPPED
--DDLERROR DEFAULT IGNORE RETRYOP
--Specify table mapping ---
map scott.t1, target scott.t1;
GGSCI (fedora.localdomain) 6>start *
GGSCI (fedora.localdomain) 7> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
REPLICAT RUNNING TREP01 00:00:00 00:00:08
三、测试
1、原端使用scott登陆，
然后SQL> insert into t1 values(3,'peng');
1 row created.
SQL> commit;
Commit complete.
2、目标端查询
QL> select * from t1;
no rows selected
SQL> /
no rows selected
SQL> /
no rows selected
SQL> /
no rows selected
SQL> /
ID NAME
---------- ----------
3 peng
四、检查状态
1、进程运行状态ogg命令中查询info all
GGSCI (rel-135) 45> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING EXTR01 00:00:00 00:00:09
EXTRACT RUNNING PUMP01 00:00:00 00:00:07
2、extr01状态
GGSCI (rel-135) 48> stats extr01
Sending STATS request to EXTRACT EXTR01 ...
Start of Statistics at 2014-01-21 19:13:40.
DDL replication statistics (for all trails):
*** Total statistics since extract started ***
Operations 0.00
Mapped operations 0.00
Unmapped operations 0.00
Other operations 0.00
Excluded operations 0.00
Output to ./dirdat/VA:
Extracting from OGG.GGS_MARKER to OGG.GGS_MARKER:
*** Total statistics since 2014-01-21 08:22:38 ***
No database operations have been performed.
*** Daily statistics since 2014-01-21 08:22:38 ***
No database operations have been performed.
*** Hourly statistics since 2014-01-21 19:00:00 ***
No database operations have been performed.
*** Latest statistics since 2014-01-21 08:22:38 ***
No database operations have been performed.
Extracting from SCOTT.T1 to SCOTT.T1:
*** Total statistics since 2014-01-21 08:22:38 ***
Total inserts 1.00
Total updates 0.00
Total deletes 0.00
Total discards 0.00
Total operations 1.00
*** Daily statistics since 2014-01-21 08:22:38 ***
Total inserts 1.00
Total updates 0.00
Total deletes 0.00
Total discards 0.00
Total operations 1.00
*** Hourly statistics since 2014-01-21 19:00:00 ***
No database operations have been performed.
*** Latest statistics since 2014-01-21 08:22:38 ***
Total inserts 1.00
Total updates 0.00
Total deletes 0.00
Total discards 0.00
Total operations 1.00
End of Statistics.
3、pump01状态
GGSCI (rel-135) 3> stats pump01
Sending STATS request to EXTRACT PUMP01 ...
Start of Statistics at 2014-01-21 19:16:40.
Output to ./dirdat/VA:
Extracting from SCOTT.T1 to SCOTT.T1:
*** Total statistics since 2014-01-21 16:48:07 ***
Total inserts 1.00
Total updates 0.00
Total deletes 0.00
Total discards 0.00
Total operations 1.00
*** Daily statistics since 2014-01-21 16:48:07 ***
Total inserts 1.00
Total updates 0.00
Total deletes 0.00
Total discards 0.00
Total operations 1.00
*** Hourly statistics since 2014-01-21 19:00:00 ***
No database operations have been performed.
*** Latest statistics since 2014-01-21 16:48:07 ***
Total inserts 1.00
Total updates 0.00
Total deletes 0.00
Total discards 0.00
Total operations 1.00
End of Statistics.
目标端：
Trep01状态
GGSCI (fedora.localdomain) 3> stats trep01
Sending STATS request to REPLICAT TREP01 ...
Start of Statistics at 2014-01-21 11:18:57.
DDL replication statistics:
*** Total statistics since replicat started ***
Operations 0.00
Mapped operations 0.00
Unmapped operations 0.00
Other operations 0.00
Excluded operations 0.00
Errors 0.00
Retried errors 0.00
Discarded errors 0.00
Ignored errors 0.00
Replicating from SCOTT.T1 to SCOTT.T1:
*** Total statistics since 2014-01-20 21:58:42 ***
Total inserts 2.00
Total updates 0.00
Total deletes 0.00
Total discards 0.00
Total operations 2.00
*** Daily statistics since 2014-01-21 00:00:00 ***
Total inserts 1.00
Total updates 0.00
Total deletes 0.00
Total discards 0.00
Total operations 1.00
*** Hourly statistics since 2014-01-21 11:00:00 ***
No database operations have been performed.
*** Latest statistics since 2014-01-20 21:58:42 ***
Total inserts 2.00
Total updates 0.00
Total deletes 0.00
Total discards 0.00
Total operations 2.00
End of Statistics.
五、维护
1) 启动manager：start manager
2) 启动extract：start extract extn
3) 启动replicat：start replicat repn
4) 停止manager：stop manager
5) 停止 extract：stop extract extn
6) 停止replicat：stop replicat repn
7) 删除extract：delete extract extn
8) 删除replicat：delete extract repn
9) 查看ogg状态：info all
10) 修改manager参数：edit params mgr
11) 修改extract参数：edit params extn
12) 修改replicat参数：edit params repn
13、修改全局参数：edit params ./GLOBAL
当然以上参数可以直接使用delete group或者start/stop group命令启动关闭，删除这些项
- oracle gg 简单使用
- Oracle Golden Gate 系列五 -- GG 使用配置 说明
- Oracle Golden Gate 系列五 -- GG 使用配置 说明
- ORACLE(rac,dg,gg)
- oracle gg心得
- oracle HA,RAC,DG,GG
- gg
- gg
- GG
- gg
- gg
- gg
- gg
- gg
- gg
- gg
- gg
- gg
- C# WinForm多线程（一）----- Thread类库
- png压缩软件
- Longest Common Prefix
- c#中多线程访问winform控件的若干问题
- 弟弟的作业
- oracle gg 简单使用
- 正则表达式30分钟入门教程
- Spring Web Flow 2.0 入门详解
- C#的WinForm多线程应用(转)
- How to Interpret the OS stats section of an AWR report (文档 ID 762526.1)
- ThreadLocal详解
- 好文章——Web应用的组件化开发（二）
- c# winform多线程的小例子
- apple id几种账号的区别，企业账号，个人账号
