『ORACLE』Oracle GoldenGate搭建（11g）

Oracle GoldenGate 实现原理是通过抽取源端的redo log 或者 archive log ，然后通过TCP/IP投递到目标端，最后解析还原应用到目标端，使目标端实现 同源端数据同步。

 

一、环境准备

1、

源端—>目标端	Oracle—>Oracle单向同步
数据库版本（源端、目标端）	Oracle11.2.0.4
源端IP	192.0.2.5
目标端IP	192.0.2.6
ogg安装目录	/u01/app/ogg

2、创建安装目录

①源端

[oracle@enmo1 ~]$ mkdir -p /u01/app/ogg
①目标端

[oracle@enmo2 ~]$ mkdir -p /u01/app/ogg

3、上传安装介质并解压

①源库、目标库均上传ogg安装介质
[oracle@enmo1 ~]$ cd /u01/app/ogg/

[oracle@enmo1 ogg]$ rz
rz waiting to receive.
Starting zmodem transfer. Press Ctrl+C to cancel.
Transferring ogg112101_fbo_ggs_Linux_x64_ora11g_64bit.zip...
100% 87096 KB 17419 KB/sec 00:00:05 0 Errors

[oracle@enmo1 ogg]$ ls
ogg112101_fbo_ggs_Linux_x64_ora11g_64bit.zip

 

[oracle@enmo2 ~]$ cd /u01/app/ogg/

[oracle@enmo2 ogg]$ rz
rz waiting to receive.
Starting zmodem transfer. Press Ctrl+C to cancel.
Transferring ogg112101_fbo_ggs_Linux_x64_ora11g_64bit.zip...
100% 87096 KB 14516 KB/sec 00:00:06 0 Errors

[oracle@enmo2 ogg]$ ls
ogg112101_fbo_ggs_Linux_x64_ora11g_64bit.zip

②源库解压
[oracle@enmo1 ogg]$ unzip ogg112101_fbo_ggs_Linux_x64_ora11g_64bit.zip

[oracle@enmo1 ogg]$ tar -xvf fbo_ggs_Linux_x64_ora11g_64bit.tar 

③目标库解压

[oracle@enmo2 ogg]$ unzip ogg112101_fbo_ggs_Linux_x64_ora11g_64bit.zip

[oracle@enmo2 ogg]$ tar -xvf fbo_ggs_Linux_x64_ora11g_64bit.tar 

4、配置环境变量

源库和目标库均增加如下信息

vi ~/.bash_profile

export GG_HOME=/u01/app/ogg

export PATH=$PATH:$HOME/bin:$ORACLE_HOME/bin:$GG_HOME

export LD_LIBRARY_PATH=$ORACLE_HOME/lie:/lib:/usr/lib

alias ggsci='cd $GG_HOME;ggsci'

二、源端数据库配置

1、数据库处于归档模式

[oracle@enmo1 ogg]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Sun May 7 21:32:09 2017

Copyright (c) 1982, 2013, Oracle. All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.

Total System Global Area 830930944 bytes
Fixed Size 2257800 bytes
Variable Size 541068408 bytes
Database Buffers 281018368 bytes
Redo Buffers 6586368 bytes
Database mounted.
SQL> alter database archivelog;

Database altered.

SQL> alter database open;

Database altered.

2、打开强制生成日志

SQL> select force_logging from v$database;

FOR
---
NO

SQL> alter database force logging;

Database altered.

SQL> select force_logging from v$database;

FOR
---
YES

3、打开补充日志

在正常情况下，oracle是用rowid来唯一标示一行记录的，但对于goldengate来说是不够的，需要开附加日志。

SQL> select supplemental_log_data_min from v$database;

SUPPLEME
--------
NO

SQL> alter database add supplemental log data;

Database altered.

SQL> select supplemental_log_data_min from v$database;

SUPPLEME
--------
YES

4、修改参数

这个参数在11.2.0.4和12.1.0.2以后才出现。目的是为了更好的监视你是用的OGG，所以把OGG绑定到DB中，只有设置了改参数为true，才能使用OGG的一些功能。

SQL> show parameter enable_goldengate;

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
enable_goldengate_replication boolean FALSE
SQL> alter system set enable_goldengate_replication=true;

System altered.

SQL> show parameter enable_goldengate;

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
enable_goldengate_replication boolean TRUE

5、创建goldengate数据库用户

①创建一个专属于OGG的表空间（非必须）

SQL> create tablespace ts_ogg datafile'/u01/app/oracle/oradata/enmo1/ts_ogg.dbf'size 200M;

Tablespace created.

SQL> create user ogg identified by oracle default tablespace ts_ogg;

User created.

②授予用户相关权限

grant resource to ogg;

grant create session,alter session to ogg;

grant select any dictionary to ogg;

grant flashback any table to ogg;

grant alter any table to ogg;

grant select any table to ogg;

grant execute on dbms_flashback to ogg;

6、准备测试数据

①创建测试用户，并授予基本权限

SQL> create user dt identified by oracle;

User created.

SQL> grant connect,resource to dt;

Grant succeeded.

SQL> grant select on scott.dept to dt;

Grant succeeded.

SQL> grant select on scott.emp to dt;

Grant succeeded.

②创建测试表，并增加主键

SQL> conn dt/oracle
Connected.
SQL> create table mydept as select * from scott.dept;

Table created.

SQL> create table myemp as select * from scott.emp;

Table created.

SQL> alter table mydept add primary key(deptno);

Table altered.

SQL> alter table myemp add primary key(empno);

Table altered.

③数据导出，用于目标端

[oracle@enmo1 ogg]$ exp dt/oracle file=/home/oracle/dt.dmp tables=mydept,myemp rows=y