Oracle goldengate 11g （一）【DML单向复制】

Oracle goldengate 11g （一）【DML单向复制】

for live reporting (one to one)

整体实验如图所释：

环境：

数据库版本：11.2.0.1

Oracle goldengate版本：11.2.1.0.1

Oracle goldengate软件下载地址

http://www.oracle.com/technetwork/middleware/goldengate/downloads/index.html

Oracle goldengate文档下载地址

http://www.oracle.com/technetwork/middleware/goldengate/documentation/index.html

 

安装整体思路

一：Oracle 数据库准备

二：Oracle goldengate系统准备

三：Oracle goldengate安装

四：Oracle goldengate测试

 

具体实施步骤

一：Oracle 数据库准备

1、 开启归档模式(单向复制仅source开启，双向复制需要source、target同时开启)

2、 开启supplemental log(单向复制仅source开启，双向复制需要source、target同时开启)

3、 Source、target建立oracle goldengate所需用户，权限

 

1、 开启归档模式(sysdba)

Select name,log_mode from v$database;   (--archive log list; 也可以查看是否归档--)

Shutdown immediate;

Startup mount;

Alter database archivelog;

Alter database open;

Select name,log_mode from v$database;

--LOG_MODE===>ARCHIVELOG(确认已经开启归档)

2、 开启supplemental log

Select supplemental_log_data_min from v$database;

Alter database add supplemental log data;

Select supplemental_log_data_min from v$database;

--SUPPLEME===>YES(确认已开启supplemental log)

3、Source、target建立oracle goldengate所需用户，权限

3-1、source database

Create tablespace ogg datafile ‘/opt/ogg.dbf’ size 500m autoextend on;

Create temporary tablespace ogg_temp tempfile ‘/opt/ogg_temp.dbf’ size 1g;

Create user ogg identified by oracle default tablespace ogg temporary tablespace ogg_temp;

Grant connect,resource,unlimited tablespace to ogg;

Grant alter any table to ogg;

Grant create session to ogg;

Grant flashback any table to ogg;

Grant execute on dbms_flashback to ogg;

Grant execute on utl_file to ogg;

Grant select any dictionary to ogg;

Grant select any table to ogg;

3-2、target database

Create tablespace ogg datafile ‘/opt/ogg.dbf’ size 500m autoextend on;

Create temporary tablespace ogg_temp tempfile ‘/opt/ogg_temp.dbf’ size 1g;

Create user ogg identified by oracle default tablespace ogg temporary tablespace ogg_temp;

Grant connect,resource,unlimited tablespace to ogg;

Grant execute on utl_file to ogg;

Grant insert any table to ogg;

Grant delete any table to ogg;

Grant update any table to ogg;

grant dba to ogg;

 

二：Oracle goldengate系统准备

1、 添加安装用户oracle的环境变量（注意安装目录权限）

1-1、source database

export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/opt/ogg

1-2、target database

export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/u01/ogg

2、 source,target均解压软件

unzip ogg112101_fbo_ggs_Linux_x86_ora11g_32bit.zip

tar -xvof fbo_ggs_Linux_x86_ora11g_32bit.tar

3、配置时间同步

 

三：Oracle goldengate安装

1、 创建subdirs ；source、target 都要建立

--(注意使用ggsci命令需在ogg HOME目录下执行)

cd /opt/ogg/

[oracle@doudou-NAS 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, x86, 32bit (optimized), Oracle 11g on Apr 23 2012 08:09:25

Copyright (C) 1995, 2012, Oracle and/or its affiliates. All rights reserved.

GGSCI (doudou-NAS) 1> create subdirs

Creating subdirectories under current directory /opt/ogg

Parameter files                /opt/ogg/dirprm: already exists

Report files                   /opt/ogg/dirrpt: created

Checkpoint files               /opt/ogg/dirchk: created

Process status files           /opt/ogg/dirpcs: created

SQL script files               /opt/ogg/dirsql: created

Database definitions files     /opt/ogg/dirdef: created

Extract data files             /opt/ogg/dirdat: created

Temporary files                /opt/ogg/dirtmp: created

Stdout files                   /opt/ogg/dirout: created

 

2、 Crete the manager parameter file

2-1、source database

GGSCI (doudou-NAS) 2> edit params mgr

GGSCI (doudou-NAS) 3> view params mgr  --使用view params查看manager参数

port 7809

dynamicportlist 7810-7900

autorestart extract *,retries 5 ,waitminutes 2

GGSCI (doudou-NAS) 31> start manager

Manager started.

GGSCI (doudou-NAS) 25> view report mgr  --使用view report 查看manager是否配置正确

***********************************************************************

                 Oracle GoldenGate Manager for Oracle

      Version 11.2.1.0.1 OGGCORE_11.2.1.0.1_PLATFORMS_120423.0230

   Linux, x86, 32bit (optimized), Oracle 11g on Apr 23 2012 04:23:29

Copyright (C) 1995, 2012, Oracle and/or its affiliates. All rights reserved.

                    Starting at 2013-06-18 14:24:58

***********************************************************************

Operating System Version:

Linux

Version #1 SMP Fri Jan 27 17:21:15 EST 2012, Release 2.6.18-308.el5

Node: doudou-NAS

Machine: i686

                         soft limit   hard limit

Address Space Size   :    unlimited    unlimited

Heap Size            :    unlimited    unlimited

File Size            :    unlimited    unlimited

CPU Time             :    unlimited    unlimited

Process id: 10966

Parameters...

port 7809

dynamicportlist 7810-7900

autorestart extract *,retries 5 ,waitminutes 2

***********************************************************************

**                     Run Time Messages                             **

***********************************************************************

2013-06-18 14:24:58  INFO    OGG-00983  Manager started (port 7809)

 

GGSCI (doudou-NAS) 27> info mgr status --使用info 命令查看manager当前状态

Manager is running (IP port doudou-NAS.7809).

2-2、target database

GGSCI (localhost.localdomain) 9> edit params mgr

port 7809

dynamicportlist 7810-7900

autostart er *

autorestart extract *,waitminutes 2,retries 5

lagreporthours 1

laginfominutes 3

lagcriticalminutes 5

purgeoldextracts /u01/ogg/dirdat/tt* ,usecheckpoints ,minkeephours 5

GGSCI (localhost.localdomain) 11> start mgr

Manager started.

 

3、Configure the primary extract group

GGSCI (doudou-NAS) 61> add extract ext1,tranlog ,begin now

EXTRACT added.

GGSCI (doudou-NAS) 68> add exttrail /opt/ogg/dirdat/tt,extract ext1

EXTTRAIL added.

GGSCI (doudou-NAS) 71> view params ext1

extract ext1

userid ogg,password oracle

exttrail /opt/ogg/dirdat/tt

TRANLOGOPTIONS asmuser sys@asm,asmpassword oracle  

--使用ASM存储redo log 或archive log 需要添加此行，如果没有此行会影响extract工作，注意配置静态静态和asm字符串--

table doudou.*;

 

4、configure the data pump on the source

GGSCI (doudou-NAS) 73> add extract pump1 ,exttrailsource /opt/ogg/dirdat/tt ,begin now

EXTRACT altered.

GGSCI (doudou-NAS) 74> add rmttrail /u01/ogg/dirdat/tt ,extract pump1

RMTTRAIL added.

GGSCI (doudou-NAS) 135> view params pump1

extract pump1

userid ogg,password oracle

rmthost 192.168.1.219,mgrport 7809

rmttrail /u01/ogg/dirdat/tt

table doudou.*;

 

5、configure the replicat group

5-1、configure checkpointtable

GGSCI (localhost.localdomain) 13> edit params ./GLOBALS

checkpointtable ogg.checkpoint

GGSCI (localhost.localdomain) 1> dblogin userid ogg,password oracle

Successfully logged into database.

GGSCI (localhost.localdomain) 2> add checkpointtable ogg.checkpoint

Successfully created checkpoint table ogg.checkpoint.

5-2、replicat group

GGSCI (localhost.localdomain) 3> add replicat rep1 ,exttrail /u01/ogg/dirdat/tt ,begin now

REPLICAT added.

GGSCI (localhost.localdomain) 9> view params rep1

replicat rep1

userid ogg ,password oracle

assumetargetdefs

map doudou.*,target doudou.*;

 

使用expdp,impdp 初始化数据

SQL>select current_scn from v$database ;

CURRENT_SCN

-----------

   1379205

[oracle@doudou-NAS ~]$expdp doudou/oracle@doudou directory=dump dumpfile=doudou.dmp schemas=doudou flashback_scn=1379205

GGSCI (localhost.localdomain) 3>start replicat rep1, aftercsn 1379205

5-3、开启GG同步

Targar database

GGSCI (localhost.localdomain) 1> dblogin userid ogg,password oracle

Successfully logged into database.

GGSCI (localhost.localdomain) 2> add trandata doudou.*

2013-06-18 17:17:59  WARNING OGG-00869  No unique key is defined for table ‘DOUDOU’. All viable columns will be used to represent the key, but may not guarantee uniqueness.  KEYCOLS may be used to define the key.

Logging of supplemental redo data enabled for table DOUDOU.DOUDOU.

2013-06-18 17:17:59  WARNING OGG-00869  No unique key is defined for table ‘TEST’. All viable columns will be used to represent the key, but may not guarantee uniqueness.  KEYCOLS may be used to define the key.

Logging of supplemental redo data enabled for table DOUDOU.TEST.

 

四：Oracle goldengate 测试

Source database

SQL> desc test;

 Name                                      Null?    Type

 ----------------------------------------- -------- ----------------------------

 NAME                                               VARCHAR2(200)

 ID                                                 NUMBER

SQL> select count(*) from test;

  COUNT(*)

----------

         0

SQL> insert into test values (‘兜兜’,1);

1 row created.

SQL> commit;

Commit complete.

--commit 开始计时

Target database

SQL> select * from test; 

no rows selected

SQL> /

no rows selected

SQL> /

no rows selected

SQL> /

no rows selected

SQL> /

no rows selected

SQL> /

NAME

--------------------------------------------------------------------------------

        ID

----------

兜兜

         1

--约5秒后，数据同步过来

 

总结：

    Oracle goldengate DML 单向复制成功。

    Oracle goldengate 一条数据同步应用就用了5秒，看来oracle goldengate优化会有更有研究深度。期待……