10.Oracle Golden Date(ogg)的搭建和管理
程序员文章站
2023-02-18 10:27:08
一. GoldenGate 概述 GoldenGate现在是业内成熟的数据容灾与复制产品;GoldenGate是一种基于日志的结构化数据复制方式,它通过解析源数据库在线日志或归档日志获得数据的增删改变化(数据量只有日志的四分之一左右),再将这些变化应用到目标数据库,实现源数据库与目标数据库同步、双活 ......
一. goldengate 概述
goldengate现在是业内成熟的数据容灾与复制产品;goldengate是一种基于日志的结构化数据复制方式,它通过解析源数据库在线日志或归档日志获得数据的增删改变化(数据量只有日志的四分之一左右),再将这些变化应用到目标数据库,实现源数据库与目标数据库同步、双活。
二. goldengate 搭建
目标在linux系统下安装oracle;利用goldengate 实现数据同步;可复制dml和ddl操作
环境如下:本环境目标端是源端的克隆;步骤请参考第8篇
源端 | 目标端 | |
操作系统 | linux6.5 | linux6.5 |
ip | 192.168.1.235 | 192.168.1.221 |
数据库版本 | 11.2.0.4.0 | 11.2.0.4.0 |
goldengate版本 | 11.2.1.0.1 | 11.2.1.0.1 |
useradd -u 1003 -g oinstall -g dba ogg passwd ogg
2.2 修改 goldengate 系统用户环境变量
source /home/oracle/.bash_profile # or export oracle_base=/u01/app export oracle_home=$oracle_base/oracle export oracle_sid=ora221 export path=$path:$home/bin:$oracle_home/bin/data/ogg export nls_lang=american_america.utf8 export ld_library_path=$oracle_home/lib:$ld_library_path alias sqlplus='rlwrap sqlplus' alias ggsci='rlwrap ggsci'
mkdir -p /data/ogg chown -r ogg:oinstall /data/ogg
unzip fbo_ggs_linux_x64_ora11g_64bit.tar.zip tar -xvf fbo_ggs_linux_x64_ora11g_64bit.tar -c /data/ogg
create tablespace tsp_ogg datafile '/data/oracle/data/ogg01.dbf' size 100m autoextend on;
create user ogg identified by ogg default tablespace tsp_ogg; grant dba to ogg; grant create table,create sequence to ogg;
alter system set log_archive_dest_1 = 'location=/data/arch' scope = spfile; shutdown immediate; startup mount alter database archivelog; alter database open;
还需要开启 force log 和 supplemental log
alter database force logging; alter database add supplemental log data;
最后查询结果如下:
sql> select name,open_mode,force_logging,supplemental_log_data_min from v$database; name open_mode for suppleme --------- -------------------- --- -------- ora235 read write yes yes
@marker_setup.sql @ddl_setup.sql @role_setup.sql @ddl_enable.sql
在11.2.0.4.0版本;需要设置
alter system set enable_goldengate_replication = true scope=both;
只有commit之后的日志信息才会被capture进程捕获,未提交的事务ogg不会捕获。
[ogg@oracle235 ogg]$ 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 (oracle235) 1> create subdirs creating subdirectories under current directory /data/ogg parameter files /data/ogg/dirprm: already exists report files /data/ogg/dirrpt: created checkpoint files /data/ogg/dirchk: created process status files /data/ogg/dirpcs: created sql script files /data/ogg/dirsql: created database definitions files /data/ogg/dirdef: created extract data files /data/ogg/dirdat: created temporary files /data/ogg/dirtmp: created stdout files /data/ogg/dirout: created
ggsci (oracle235) 2> edit params ./globals checkpointtable ogg.ggschkpt ggsci (oracle235) 3> exit [ogg@oracle235 ogg]$ 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 (oracle235) 1> dblogin userid ogg, password ogg successfully logged into database. ggsci (oracle235) 2> add checkpointtable no checkpoint table specified, using globals specification (ogg.ggschkpt)... successfully created checkpoint table ogg.ggschkpt.
[ogg@oracle235 ogg]$ 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 (oracle235) 1> edit params mgr
输入内容如下:
port 7809 dynamicportlist 7800-8000 autorestart extract *, waitminutes 2, resetminutes 5 purgeoldextracts /data/ogg/dirdat, usecheckpoints, minkeepdays 3
ggsci (oracle235) 2> start mgr manager started. ggsci (oracle235) 3> info mgr manager is running (ip port oracle235.7809).
ggsci (oracle235) 1> edit params eora_1 extract eora_1 setenv (nls_lang=american_america.utf8) userid ogg, password ogg exttrail /data/ogg/dirdat/aa table lottu.*;
在源端用 add extract 命令创建 extract 组;用add exttrail 命令创建本地 trail 文件
ggsci (oracle235) 2> add extract eora_1, tranlog, begin now extract added. ggsci (oracle235) 3> add exttrail /data/ogg/dirdat/aa, extract eora_1, megabytes 5 exttrail added.
启动 extract 进程;在 ggsci 中:可以使用 add, alter, cleanup, delete, info, kill命令管理extract进程
ggsci (oracle235) 4> start extract eora_1 sending start request to manager ... extract eora_1 starting ggsci (oracle235) 5> info eora_1 extract eora_1 last started 2018-08-23 00:52 status running checkpoint lag 00:06:20 (updated 00:00:04 ago) log read checkpoint oracle redo logs 2018-08-23 00:46:36 seqno 12, rba 34652672 scn 0.0 (0)
ggsci (oracle235) 6> edit params pora_1 extract pora_1 setenv (nls_lang=american_america.utf8) passthru rmthost 192.168.1.221, mgrport 7809 rmttrail /data/ogg/dirdat/pa table lottu.*;
用 add extract 指定本地 trail 文件
ggsci (oracle235) 7> add extract pora_1, exttrailsource /data/ogg/dirdat/aa extract added.
用 add rmttrail 指定远程 trail 文件
ggsci (oracle235) 8> add rmttrail /data/ogg/dirdat/pa, extract pora_1, megabytes 5 rmttrail added.
启动 pump 进程
ggsci (oracle235) 9> start extract pora_1 sending start request to manager ... extract pora_1 starting ggsci (oracle235) 11> info all program status group lag at chkpt time since chkpt manager running extract running eora_1 00:00:00 00:00:07 extract running pora_1 00:00:00 00:00:02
ggsci (oracle221) 2> edit params rora_1 replicat rora_1 setenv (nls_lang=american_america.utf8) userid ogg, password ogg handlecollisions assumetargetdefs discardfile /data/ogg/dirrpt/rora_aa.rpt, append map lottu.*, target lottu.*;
在目标端使用 add replicat 添加 replicat 进程
ggsci (oracle221) 8> add replicat rora_1, exttrail /data/ogg/dirdat/pa replicat added.
使用 start replicat 启动 replicat 进程
ggsci (oracle221) 11> start replicat rora_1 sending start request to manager ... replicat rora_1 starting ggsci (oracle221) 12> info all program status group lag at chkpt time since chkpt manager running replicat running rora_1 00:00:00 00:00:00
alter system set recyclebin=off scope=both;
ggsci (oracle235) 19> view params ./globals ggschema ogg
停mgr,extract进程;
配置extract配置文件;在配置文件添加一下内容:
ddl include all ddloptions addtrandata,report
停mgr,replicat进程;
配置replicat配置文件;在配置文件添加一下内容:
ddl include mapped ddloptions report
配置完成;在重启各进程进行验证。