之前一直没有玩过goldengate,看到现在这个软件应用的越来越广泛于是也想尝鲜一把，这次主要是测试goldengate的单向复制功能，最后通过dml的复制结果来验证！
操作步骤如下:

1.环境描述

实验环境源端目标端

数据库 10.2.0.4.0 10.2.0.4.0

操作系统 centos5.8 centos5.8

主机名 sourcedb targetdb

2.安装goldengate(两端操作)

2.1解压软件包

[oracle@sourcedb ogg]$ tar -xvf fbo_ggs_Linux_x64_ora10g_64bit.tar -C /oradata/ogg

2.2 创建goldengate子目录

[oracle@sourcedb ogg]$ cd /oradata/ogg/
[oracle@sourcedb ogg]$ ./ggsci

GGSCI (sourcedb) 1> create subdirs

2.3设置用户环境变量

[oracle@sourcedb ~]$ cat .bash_profile
export ORACLE_BASE=/oracle/product
export ORACLE_HOME=$ORACLE_BASE/10.2/db_1
export ORACLE_SID=wilson
export PATH=$ORACLE_HOME/bin:$PATH
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/usr/lib:/usr/local/lib
export NLS_LANG=american_america.ZHS16GBK

3.配置oracle数据库

3.1配置归档模式(源端操作)

[oracle@sourcedb ~]$ sqlplus / as sysdba

SQL>shutdown immediate

SQL>startup mount

SQL>alter database archivelog

3.2配置补充日志(源端操作)

SQL> alter database add supplemental log data

3.3创建goldengate管理用户(两端操作)

SQL>create user ggs_admin identified by ggs_admin;

SQL>grant dba to ggs_admin;

4.添加表级的trandata(源端操作)

[oracle@sourcedb ogg]$ ./ggsci

GGSCI (sourcedb) 1> dblogin userid ggs_admin,password ggs_admin

GGSCI (sourcedb) 2> add trandata scott.emp

5.配置hosts文件(两端操作)

[oracle@sourcedb ~]$ cat /etc/hosts
127.0.0.1 localhost.localdomain localhost
::1 localhost6.localdomain6 localhost6
10.1.101.19 sourcedb
10.1.101.13 targetdb

6.配置goldengate的manager进程(两端操作)

[oracle@sourcedb ogg]$ ./ggsci

GGSCI (sourcedb) 1> edit params mgr

port 7809

purgeoldextracts ./dirdat/sa*, usecheckpoints, minkeephours 2

GGSCI (sourcedb) 2> start mgr

7.配置goldengate的extract进程(源端操作)
7.1配置extract进程

[oracle@sourcedb ogg]$ ./ggsci

GGSCI (sourcedb) 1> edit params edb01
extract edb01
setenv (ORACLE_SID=wilson)
userid ggs_admin,password ggs_admin
exttrail ./dirdat/sa
table scott.emp;

GGSCI (sourcedb) 2>add extract edb01,tranlog,begin now

GGSCI (sourcedb) 3>add exttrail ./dirdat/sa,extact edb01

GGSCI (sourcedb) 4>start extract edb01

7.2配置goldengate的pump进程
GGSCI (sourcedb) 5> edit params epmp01

extract epmp01
passthru
rmthost targetdb,mgrport 7809
rmttrail ./dirdat/ta

table scott.emp;

GGSCI (sourcedb) 6>add extract epmp01,exttrailsource ./dirdat/sa,begin now

GGSCI (sourcedb) 7>add rmttrail ./dirdat/ta,extact epmp01

GGSCI (sourcedb) 8>start extract epmp01

GGSCI (sourcedb) 9>info all

Program Status Group Lag at Chkpt Time Since Chkpt

MANAGER     RUNNING
EXTRACT     RUNNING     EDB01       00:00:00      00:00:08
EXTRACT     RUNNING     EPMP01      00:00:00      00:00:08

8.配置goldengate的replicat进程(目标端操作)

8.1添加checkpoint表

[oracle@targetdb ogg]$ ./ggsci

GGSCI (targetdb) 1> edit params ./GLOBALS
checkpointtable ggs_admin.tl

GGSCI (targetdb) 2> dblogin userid ggs_admin,password ggs_admin

GGSCI (targetdb) 3> add chweckpointtable ggs_admin.tl

8.2配置replicat进程

GGSCI (sourcedb) 4> edit params rdb01

replicat rdb01
setenv (ORACLE_SID=wilson)
userid ggs_admin,password ggs_admin
discardfile ./dirrpt/rdb01.dsc,purge
map scott.emp,target tl.emp;
assumetargetdefs

GGSCI (sourcedb) 5>add replicat rdb01,exttrail ./dirdat/ta

GGSCI (sourcedb) 6>start replicat rdb01

GGSCI (sourcedb) 7>info all

Program Status Group Lag at Chkpt Time Since Chkpt

MANAGER RUNNING
REPLICAT RUNNING RDB01 00:00:00 00:00:08

9.初始化数据(两端操作)

这一步我通过expdp/impdp将源端的scott.emp导入到目标端的tl.emp，这里就不演示了

10.验证dml复制(两端操作)

10.1dml操作前(源端操作)

[oracle@sourcedb ogg]$ sqlplus scott/tiger

SQL> select ename,sal from emp where ename='SMITH';

ENAME SAL
---------- ----------
SMITH 800

10.2dml操作前(目标端操作)
[oracle@targetdb ogg]$ sqlplus tl/tl

SQL> select ename,sal from emp where ename='SMITH';

ENAME SAL
---------- ----------
SMITH 800

10.3dml操作后(源端操作)

SQL> show user
USER is "SCOTT"

SQL> update emp set sal=8800 where ename='SMITH';

1 row updated.

SQL> commit;

Commit complete.

SQL> select ename,sal from emp where ename='SMITH';

ENAME SAL
---------- ----------
SMITH 8800
10.4dml操作后(目标端操作)

SQL> show user
USER is "TL"

SQL> select ename,sal from emp where ename='SMITH';

ENAME SAL
---------- ----------
SMITH 8800

OK,通过dml复制的结果发现更新的结果已经同步到目标端，实验成功！实验过程中出现过一个错误就是当在源端对emp表进行dml操作并提交后，发现目标端的数据并没有同步过来，此时的replicat进程已为abend状态，通过view replicat rdb01发现报告中提示"无法发现SCOTT.EMP的定义",解决方法为在replicat参数文件中添加assumetargetdefs选项即可。

ITPub博客

实战goldengate之ora-To-ora单向复制

最新文章