Oracle Golden Gate

参考视频:bbk5932、bbk5933

参考链接:http://www.juliandyke.com/Blog/?p=267

实验环境：

　　1、Linux ~ CentOS 6.4x86(32 bits)

　　2、Oracle Database 11.2.0.3

　　3、GoldenGate 11.2.1.0.1

　　4、Source(192.168.137.54)-> Target(192.168.137.55)

Oracle Golden Gate原理结构图

goldentgate的六大进程:

source side：

extract progress

pump progress

source database mangager process

source side：

collect process

replicate process

target database manager progress

Configure workflow

create ogg schema
configure manager
configure extract
configure exttrail
configure data pump
create checkpoint table
configure replicat
start ogg

一、开启开关

开启force logging开关

oggsource-> sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Sun Jul 28 01:38:16 2013

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


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

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

SQL>

View Code

开启supplemental_log_data_min开关

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

SQL>

View Code

SQL> alter system switch logfile;

System altered.

SQL>

两个开关开启后，进行一次日志切换;

将上述三个步骤在oggtarget上照猫画虎执行一遍.

二、创建业务用户

SQL> show user
USER is "SYS"
SQL> create user us01 identified by us01 default tablespace users;

User created.

SQL> grant connect,resource to us01;

Grant succeeded.

SQL> conn us01/us01
Connected.
SQL> create table t1 (id int primary key ,name char(10));

Table created.

SQL>

View Code

注意:需要在source、target上执行一遍；

三、创建表空间、创建管理用户(source、target都要执行)

source database

SQL> col name format a60
SQL> select ts#,name from v$datafile;

 TS# NAME
---------- ------------------------------------------------------------
0 /u01/app/oracle/oradata/source/system01.dbf
 1 /u01/app/oracle/oradata/source/sysaux01.dbf
 2 /u01/app/oracle/oradata/source/undotbs01.dbf
 4 /u01/app/oracle/oradata/source/users01.dbf
 6 /u01/app/oracle/oradata/source/example01.dbf

SQL> create tablespace gg datafile '/u01/app/oracle/oradata/source/gg01.dbf' size 100m autoextend on;

Tablespace created.

SQL> create user ggadmin identified by ggadmin default tablespace gg;

User created.

SQL> grant connect,resource,dba to ggadmin;

Grant succeeded.

SQL>

View Code

target database

SQL> create tablespace gg datafile '/u01/app/oracle/oradata/target/gg01.dbf' size 100m autoextend on;

Tablespace created.

SQL> create user ggadmin identified by ggadmin default tablespace gg;

User created.

SQL> grant connect,resource,dba to ggadmin;

Grant succeeded.

SQL>

View Code

安装oracle goldengate

oggsource-> ./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 (oggsource) 1> 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: created
Checkpoint files               /u01/app/oracle/ogg/dirchk: created
Process status files           /u01/app/oracle/ogg/dirpcs: created
SQL script files               /u01/app/oracle/ogg/dirsql: created
Database definitions files     /u01/app/oracle/ogg/dirdef: created
Extract data files             /u01/app/oracle/ogg/dirdat: created
Temporary files                /u01/app/oracle/ogg/dirtmp: created
Stdout files                   /u01/app/oracle/ogg/dirout: created


GGSCI (oggsource) 2>

View Code

注意，要在oracle source database及oracle target database上都要执行一遍.

创建角色

SQL> !pwd
/u01/app/oracle/ogg

SQL> @role_setup.sql

GGS Role setup script

This script will drop and recreate the role GGS_GGSUSER_ROLE
To use a different role name, quit this script and then edit the params.sql script to change the gg_role parameter to the preferred name. (Do not run the script.)

You will be prompted for the name of a schema for the GoldenGate database objects.
NOTE: The schema must be created prior to running this script.
NOTE: Stop all DDL replication before starting this installation.

Enter GoldenGate schema name:ggadmin
Wrote file role_setup_set.txt

PL/SQL procedure successfully completed.


Role setup script complete

Grant this role to each user assigned to the Extract, GGSCI, and Manager processes, by using the following SQL command:

GRANT GGS_GGSUSER_ROLE TO <loggedUser>

where <loggedUser> is the user assigned to the GoldenGate processes.
SQL> grant GGS_GGSUSER_ROLE TO ggadmin;

Grant succeeded.

SQL>

View Code

注意:在target database上不需要再创建role

配置mgr进程

[root@oggsource ~]# su - oracle
oggsource-> cd $ORACLE_BASE/ogg
oggsource-> ./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 (oggsource) 1> edit params mgr
PORT 7809

"dirprm/mgr.prm" [New] 1L, 10C written


GGSCI (oggsource) 2> start mgr

Manager started.


GGSCI (oggsource) 3> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING                                           


GGSCI (oggsource) 4>

View Code

配置extract进程

GGSCI (oggsource) 4> edit params ex1


EXTRACT ex1
USERID ggadmin,PASSWORD ggadmin
EXTTRAIL /u01/app/oracle/ogg/dirdat/ex
TABLE us01.*;

"dirprm/ex1.prm" [New] 4L, 97C written


GGSCI (oggsource) 5>

配置extract进程

GGSCI (oggsource) 5> view params ex1

EXTRACT ex1
USERID ggadmin,PASSWORD ggadmin
EXTTRAIL /u01/app/oracle/ogg/dirdat/ex
TABLE us01.*;


GGSCI (oggsource) 6>

view params ex1

配置extract进程之datapump进程

GGSCI (oggsource) 6> edit params dp1


EXTRACT dp1
USERID ggadmin,PASSWORD ggadmin
RMTHOST 192.168.1.196,MGRPORT 7809
RMTTRAIL /u01/app/oracle/ogg/dirdat/rt
TABLE us01.*;


GGSCI (oggsource) 7> view params dp1

EXTRACT dp1
USERID ggadmin,PASSWORD ggadmin
RMTHOST 192.168.1.196,MGRPORT 7809
RMTTRAIL /u01/app/oracle/ogg/dirdat/rt
TABLE us01.*;


GGSCI (oggsource) 8>

data pump进程

目标数据库创建params GLOBALS

GGSCI (oggtarget) 5> edit params ./GLOBALS


GGSCHEMA ggadmin
CHECKPOINTTABLE ggadmin.chktbl

"./GLOBALS" [New] 2L, 48C written


GGSCI (oggtarget) 6>

View Code

目标数据库创建chktbl表

GGSCI (oggtarget) 6> dblogin userid ggadmin,password ggadmin
Successfully logged into database.

GGSCI (oggtarget) 7> add checkpointtable ggadmin.chktbl

Successfully created checkpoint table ggadmin.chktbl.

GGSCI (oggtarget) 9> view params ./GLOBALS

GGSCHEMA ggadmin
CHECKPOINTTABLE ggadmin.chktbl


GGSCI (oggtarget) 10>

View Code

目标数据库下，连接到ggadmin中，查看创建的object信息

SQL> conn ggadmin/ggadmin
Connected.

SQL> set linesize 200

SQL> col object_name for a30
SQL> select object_name,object_type from user_objects;

OBJECT_NAME                    OBJECT_TYPE
------------------------------ -------------------
SYS_C0011321                   INDEX
CHKTBL_LOX                     TABLE
SYS_C0011315                   INDEX
CHKTBL                         TABLE

SQL>

View Code

目标数据库下，配置replicat进程

GGSCI (oggtarget) 11> edit params rep1


REPLICAT rep1
USERID ggadmin,PASSWORD ggadmin
ASSUMETARGETDEFS
DISCARDFILE /u01/app/oracle/ogg/discards,PURGE
MAP us01.*,TARGET us01.*


GGSCI (oggtarget) 12> view params rep1

REPLICAT rep1
USERID ggadmin,PASSWORD ggadmin
ASSUMETARGETDEFS
DISCARDFILE /u01/app/oracle/ogg/discards,PURGE
MAP us01.*,TARGET us01.*;


GGSCI (oggtarget) 13>

View Code

要注意这句(MAP us01.*,TARGET us01.*;)后面一定要添加;号,否则目标数据库,replicat进程就会无法启动.错误内容如下:

2013-07-28 18:44:11  ERROR   OGG-00396  Oracle GoldenGate Delivery for Oracle, rep1.prm:  Command not terminated by semi-colon.
2013-07-28 18:44:11  ERROR   OGG-01668  Oracle GoldenGate Delivery for Oracle, rep1.prm:  PROCESS ABENDING.

来自于日志文件ggserr.log

而且，搭建oracle golden gate还要注意的就是在执行类似于（add replicat rep1,exttrail /u01/app/oracle/ogg/dirdat/rt checkpointtable ggadmin.chktbl）这样的命令时，后面不要加;号，否则也会不成功；有点嘚瑟.

源数据库，执行add trandata 命令

GGSCI (oggsource) 8> dblogin userid ggadmin,password ggadmin
Successfully logged into database.

GGSCI (oggsource) 10> add trandata us01.t

Logging of supplemental redo data enabled for table US01.T.

GGSCI (oggsource) 11>

View Code

源数据库，添加extract组，组名叫ex1；传输redo log 信息,从现在开始传

GGSCI (oggsource) 11> add extract ex1,tranlog,begin now
EXTRACT added.


GGSCI (oggsource) 12>

View Code

源数据库，添加trail文件，添加组

GGSCI (oggsource) 12> add exttrail /u01/app/oracle/ogg/dirdat/ex,extract ex1
EXTTRAIL added.

View Code

源数据库，添加duplicat组

GGSCI (oggsource) 14> add extract dp1 exttrailsource /u01/app/oracle/ogg/dirdat/ex                   
EXTRACT added.


GGSCI (oggsource) 15> view params dp1

EXTRACT dp1
USERID ggadmin,PASSWORD ggadmin
RMTHOST 192.168.1.196,MGRPORT 7809
RMTTRAIL /u01/app/oracle/ogg/dirdat/rt
TABLE us01.*;


GGSCI (oggsource) 16>

View Code

源数据库，添加dp1

GGSCI (oggsource) 16> add rmttrail /u01/app/oracle/ogg/dirdat/rt,extract dp1
RMTTRAIL added.

View Code

目标数据库添加replicat进程

GGSCI (oggtarget) 1> add replicat rep1,exttrail /u01/app/oracle/ogg/dirdat/rt checkpointtable ggadmin.chktbl
REPLICAT added.


GGSCI (oggtarget) 2>

View Code

源数据库启动extract进程

GGSCI (oggsource) 1> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING                                           
EXTRACT     STOPPED     DP1         00:00:00      00:11:02    
EXTRACT     STOPPED     EX1         00:00:00      00:16:55    


GGSCI (oggsource) 2> start extract ex1

Sending START request to MANAGER ...
EXTRACT EX1 starting


GGSCI (oggsource) 3> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING                                           
EXTRACT     STOPPED     DP1         00:00:00      00:11:23    
EXTRACT     RUNNING     EX1         00:17:15      00:00:00    


GGSCI (oggsource) 4> start extract dp1

Sending START request to MANAGER ...
EXTRACT DP1 starting


GGSCI (oggsource) 5> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING                                           
EXTRACT     RUNNING     DP1         00:00:00      00:11:45    
EXTRACT     RUNNING     EX1         00:00:00      00:00:01    


GGSCI (oggsource) 6>

View Code

ogg->target 关闭服务

GGSCI (oggtarget) 1> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING                                           
REPLICAT    RUNNING     REP1        00:00:00      00:00:03    


GGSCI (oggtarget) 2> stop replicat rep1

Sending STOP request to REPLICAT REP1 ...
Request processed.


GGSCI (oggtarget) 3> stop manager 
Manager process is required by other GGS processes.
Are you sure you want to stop it (y/n)? y

Sending STOP request to MANAGER ...
Request processed.
Manager stopped.


GGSCI (oggtarget) 4>

View Code

/*新增数据表*/

在源数据库创建表，目标数据库同样创建一张一样的table；源数据库对数据的更改变动情况会实时传到目标数据库.

OGG监控管理