Go教程

Goldengate同步Oracle数据到Oracle

本文主要是介绍Goldengate同步Oracle数据到Oracle,对大家解决编程问题具有一定的参考价值,需要的程序猿们随着小编来一起学习吧!

Goldengate同步Oracle数据到Oracle

OGG进程
捕获进程(源端):捕获online redo log或者archived log中增量事务日志
传输进程(源端):把目标端落地的trail文件通过配置的路由信息传输到目标端
网络传输:tcp/ip协议,传输过程可以对传输的文件进行压缩、加密
投递进程(目标端):把所接收的队列文件信息拼成SQL语句,并不是复制SQL,而是复制逻辑变化,并且在目标库提交

源端配置:MGR、捕获进程、捕获进程参数、捕获进程本地队列、传输进程、传输进程参数、传输进程远程队列
目标端配置:MGR、投递进程、投递进程参数

1、源端,目标端分别安装数据库软件和dbca建库(源端、目标端)
2、源端数据库开启归档(源)
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.
Total System Global Area 776646656 bytes
Fixed Size 2257272 bytes
Variable Size 507514504 bytes
Database Buffers 264241152 bytes
Redo Buffers 2633728 bytes
Database mounted.
SQL> alter database archivelog;
Database altered.

SQL> alter database add supplemental log data;
Database altered.

SQL> alter database open;
Database altered.

SQL> select name,LOG_MODE,SUPPLEMENTAL_LOG_DATA_MIN from v$database;
NAME LOG_MODE SUPPLEME


OGGDB ARCHIVELOG YES

切换日志以使附加日志生效:
SQL> alter system archive log current;
System altered.

3、源端、目标端数据库添加goldengate用户独用的表空间(源端、目标端)
源端:
SQL> create tablespace oggdata datafile '/u01/app/oracle/oradata/oggdb/oggdata.dbf' size 1G autoextend off;
Tablespace created.

目标端:
SQL> create tablespace oggdata datafile '/u01/app/oracle/oradata/destdb/oggdata.dbf' size 1G autoextend off;
Tablespace created.

4、源端、目标端创建goldengate用户,授权并指定默认表空间(这里做测试给DBA权限)(源端、目标端)
源端:
SQL> create user goldengate identified by goldengate default tablespace oggdata account unlock;
User created.

SQL> grant dba to goldengate;
Grant succeeded.

目标端:
SQL> create user goldengate identified by goldengate default tablespace oggdata account unlock;
User created.

SQL> grant dba to goldengate;
Grant succeeded.

5、源端、目标端上传ogg安装包并解压(源端、目标端)
mkdir -p /home/oracle/ogg
tar -xvf fbo_ggs_Linux_x64_ora11g_64bit.tar

6、源端、目标端修改oracle用户环境变量(源端、目标端)
源端:
[oracle@dgdb1 ogg]$ vi ~/.bash_profile

.bash_profile

Get the aliases and functions

if [ -f ~/.bashrc ]; then
. ~/.bashrc
fi

User specific environment and startup programs

export PATH=$PATH:$HOME/bin
export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1
export ORACLE_SID=oggdb
export DB_UNQNAME_NAME=oggdb
export PATH=$PATH:$ORACLE_HOME/bin
export OGG_HOME=/home/oracle/ogg
export PATH=$PATH:$OGG_HOME
export LD_LIBRARY_PATH=$ORACLE_HOME/lib

目标端:
[oracle@dgdb2 ~]$ cat .bash_profile

.bash_profile

Get the aliases and functions

if [ -f ~/.bashrc ]; then
. ~/.bashrc
fi

User specific environment and startup programs

export PATH=$PATH:$HOME/bin
export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1
export ORACLE_SID=destdb
export DB_UNQNAME_NAME=destdb
export PATH=$PATH:$ORACLE_HOME/bin
export OGG_HOME=/home/oracle/ogg
export PATH=$PATH:$OGG_HOME
export LD_LIBRARY_PATH=$ORACLE_HOME/lib

7、源端、目标端执行ggsci(源端、目标端)
[oracle@dgdb1 ogg]$ ggsci
Oracle GoldenGate Command Interpreter for Oracle
Version 11.2.1.0.3 14400833 OGGCORE_11.2.1.0.3_PLATFORMS_120823.1258_FBO
Linux, x64, 64bit (optimized), Oracle 11g on Aug 23 2012 20:20:21
Copyright (C) 1995, 2012, Oracle and/or its affiliates. All rights reserved.
GGSCI (dgdb1) 1>
这样ogg才算安装成功。
注意:ggsci必须在ogg安装目录执行,否则会报错文件目录不存在。

8、源端、目标端ggsci环境下创建goldengate子目录(源端、目标端)
GGSCI (dgdb1) 2> create subdirs
Creating subdirectories under current directory /home/oracle/ogg

Parameter files /home/oracle/ogg/dirprm: already exists
Report files /home/oracle/ogg/dirrpt: created
Checkpoint files /home/oracle/ogg/dirchk: created
Process status files /home/oracle/ogg/dirpcs: created
SQL script files /home/oracle/ogg/dirsql: created
Database definitions files /home/oracle/ogg/dirdef: created
Extract data files /home/oracle/ogg/dirdat: created
Temporary files /home/oracle/ogg/dirtmp: created
Stdout files /home/oracle/ogg/dirout: created

GGSCI (dgdb2) 1> create subdirs
Creating subdirectories under current directory /home/oracle/ogg

Parameter files /home/oracle/ogg/dirprm: already exists
Report files /home/oracle/ogg/dirrpt: created
Checkpoint files /home/oracle/ogg/dirchk: created
Process status files /home/oracle/ogg/dirpcs: created
SQL script files /home/oracle/ogg/dirsql: created
Database definitions files /home/oracle/ogg/dirdef: created
Extract data files /home/oracle/ogg/dirdat: created
Temporary files /home/oracle/ogg/dirtmp: created
Stdout files /home/oracle/ogg/dirout: created

dirchk:用于存放各个进程的检查点
dirdat:用于存放数据队列文件
dirprm:用于存放各进程参数文件
dirrpt:用于存放各进程报告
dirpcs:存放各个正在运行的进程信息

9、源端配置mgr进程,并启动mgr进程(源)
GGSCI (dgdb1) 3> edit param mgr
port 7809

GGSCI (dgdb1) 4> view param mgr
port 7809

GGSCI (dgdb1) 5> start mgr
Manager started.

查看进程状态
GGSCI (dgdb1) 6> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING

10、源端添加表级附加日志(源)
ggsci>dblogin userid goldengate, password goldengate
ggsci>add trandata test.*

11、配置抽取进程(源)
GGSCI> add ext exta, tranlog, begin now

12、为抽取进程配置队列(源)
GGSCI> add exttrail /home/oracle/ogg/dirdat/la, ext exta, MEGABYTES 20 ---(本地队列)
此时exttrail指定的是本地队列位置。

13、配置传输进程(pump进程)(源)
GGSCI> add extract dpea, EXTTRAILSOURCE /home/oracle/ogg/dirdat/la
此时EXTTRAILSOURCE指定的是本地队列位置。

14、为传输进程配置远程队列(源)
GGSCI> add rmttrail /home/oracle/ogg/dirdat/ra, ext dpea, MEGABYTES 20
队列位置是在目标主机上的位置

15、配置抽取进程参数(源)
GGSCI>edit param exta
EXTRACT exta
setenv ( NLS_LANG = AMERICAN_AMERICA.ZHS16GBK )
setenv (ORACLE_SID = oggdb)
USERID goldengate, PASSWORD goldengate
EXTTRAIL /home/oracle/ogg/dirdat/la
dynamicresolution
table test.*;

16、配置传输进程参数(pump)(源)
GGSCI>edit param dpea
extract dpea
setenv ( NLS_LANG = AMERICAN_AMERICA.ZHS16GBK )
passthru
rmthost 192.168.12.51,mgrport 7809, compress
rmttrail /home/oracle/ogg/dirdat/ra
dynamicresolution
table test.*;

17、启动源端进程(源)
GGSCI>start ext *
也可以根据进程名分别启动
GGSCI>start exta
GGSCI>start dpea

18、初始化(源端、目标端)
源端、目标端添加test用户,并创建表test
SQL> create user test identified by test account unlock;
User created.

SQL> grant dba to test;
Grant succeeded.

SQL> conn test/test
Connected.

SQL> create table test (id int);
Table created.

19、添加mgr进程(目标端)
GGSCI (dgdb2) 5> edit param mgr
port 7809

启动mgr进程
GGSCI (dgdb2) 6> start mgr
Manager started.

GGSCI (dgdb2) 7> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING

20、添加投递进程(目标端)
GGSCI> add rep repa, exttrail /home/oracle/ogg/dirdat/ra, nodbcheckpoint

21、配置投递进程参数(目标端)
GGSCI>edit param repa
replicat repa
setenv ( NLS_LANG = AMERICAN_AMERICA.ZHS16GBK)
setenv (ORACLE_SID = destdb)
userid goldengate, password goldengate
reperror default,abend
discardfile /home/oracle/ogg/dirrpt/repa.dsc,append, megabytes 10
assumetargetdefs
dynamicresolution
map test., target test.;

启动repa
GGSCI (dgdb2) 14> start repa

Sending START request to MANAGER ...
REPLICAT REPA starting

GGSCI (dgdb2) 15> info all
Program Status Group Lag at Chkpt Time Since Chkpt

MANAGER RUNNING
REPLICAT RUNNING REPA 00:00:00 00:00:05

22、测试
主库:
SQL> insert into test values (1);
1 row created.

SQL> commit;
Commit complete.

主库查看进程状态:
GGSCI (dgdb1) 28> stats exta
stats exta
Sending STATS request to EXTRACT EXTA ...
Start of Statistics at 2019-11-24 08:38:49.
Output to /home/oracle/ogg/dirdat/la:
Extracting from TEST.TEST to TEST.TEST:

*** Total statistics since 2019-11-24 08:38:49 ***
Total inserts 1.00
Total updates 0.00
Total deletes 0.00
Total discards 0.00
Total operations 1.00

*** Daily statistics since 2019-11-24 08:38:49 ***
Total inserts 1.00
Total updates 0.00
Total deletes 0.00
Total discards 0.00
Total operations 1.00

*** Hourly statistics since 2019-11-24 08:38:49 ***
Total inserts 1.00
Total updates 0.00
Total deletes 0.00
Total discards 0.00
Total operations 1.00

*** Latest statistics since 2019-11-24 08:38:49 ***
Total inserts 1.00
Total updates 0.00
Total deletes 0.00
Total discards 0.00
Total operations 1.00

End of Statistics.

GGSCI (dgdb1) 43> stats dpea
Sending STATS request to EXTRACT DPEA ...
Start of Statistics at 2019-11-24 08:49:31.
Output to /home/oracle/ogg/dirdat/ra:
Extracting from TEST.TEST to TEST.TEST:
*** Total statistics since 2019-11-24 08:49:10 ***
Total inserts 1.00
Total updates 0.00
Total deletes 0.00
Total discards 0.00
Total operations 1.00

*** Daily statistics since 2019-11-24 08:49:10 ***
Total inserts 1.00
Total updates 0.00
Total deletes 0.00
Total discards 0.00
Total operations 1.00

*** Hourly statistics since 2019-11-24 08:49:10 ***
Total inserts 1.00
Total updates 0.00
Total deletes 0.00
Total discards 0.00
Total operations 1.00

*** Latest statistics since 2019-11-24 08:49:10 ***
Total inserts 1.00
Total updates 0.00
Total deletes 0.00
Total discards 0.00
Total operations 1.00
End of Statistics.
源端已经捕获新增数据

目标端查看进程状态:
GGSCI (dgdb2) 21> stats repa
Sending STATS request to REPLICAT REPA ...
Start of Statistics at 2019-11-24 08:49:44.
Replicating from TEST.TEST to TEST.TEST:
*** Total statistics since 2019-11-24 08:49:13 ***
Total inserts 1.00
Total updates 0.00
Total deletes 0.00
Total discards 0.00
Total operations 1.00

*** Daily statistics since 2019-11-24 08:49:13 ***
Total inserts 1.00
Total updates 0.00
Total deletes 0.00
Total discards 0.00
Total operations 1.00

*** Hourly statistics since 2019-11-24 08:49:13 ***
Total inserts 1.00
Total updates 0.00
Total deletes 0.00
Total discards 0.00
Total operations 1.00

*** Latest statistics since 2019-11-24 08:49:13 ***
Total inserts 1.00
Total updates 0.00
Total deletes 0.00
Total discards 0.00
Total operations 1.00

End of Statistics.
目标端也已经投递源端传输过来的数据。

目标库查询test数据:
SQL> select * from test;
ID

1

OGG常用命令:
添加进程、队列:add
启动进程:start exta
编辑参数:edit param exta
查看参数:view param exta
查看进程信息:info exta
查看报错信息:view report exta
查看进程状态:info all
查看具体进程状态:stats exta
以上exta为进程名

备注:
1、参数指定的端口必须一致
2、注意防火墙和互信是否配置正确

这篇关于Goldengate同步Oracle数据到Oracle的文章就介绍到这儿,希望我们推荐的文章对大家有所帮助,也希望大家多多支持为之网!