一.1  BLOG文档结构图

 

image

 

一.2  前言部分

 

一.2.1  导读

各位技术爱好者,看完本文后,你可以掌握如下的技能,也可以学到一些其它你所不知道的知识,~O(∩_∩)O~:

① OGG的单向DML实时复制功能

② 数据库的静默安装

③ OGG的安装

④ OGG下数据的初始化工作

 

 

注意:本篇BLOG中代码部分需要特别关注的地方我都用黄色背景和红色字体来表示,比如下边的例子中,thread 1的最大归档日志号为33thread 2的最大归档日志号为43是需要特别关注的地方。

  List of Archived Logs in backup set 11

  Thrd Seq     Low SCN    Low Time            Next SCN   Next Time

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

  1    32      1621589    2015-05-29 11:09:52 1625242    2015-05-29 11:15:48

  1    33      1625242    2015-05-29 11:15:48 1625293    2015-05-29 11:15:58

  2    42      1613951    2015-05-29 10:41:18 1625245    2015-05-29 11:15:49

  2    43      1625245    2015-05-29 11:15:49 1625253    2015-05-29 11:15:53

 

 

 

 

 

本文如有错误或不完善的地方请大家多多指正,ITPUB留言或QQ皆可,您的批评指正是我写作的最大动力。

一.2.2  实验环境介绍

 

项目

source db

target  db

db 类型

单实例

单实例

db version

11.2.0.3

11.2.0.3

db 存储

FS type

FS type

ORACLE_SID

ogg1

ogg2

db_name

ogg1

ogg2

主机IP地址:

192.168.59.129

192.168.59.130

OS版本及kernel版本

RHEL6.5 64位,2.6.32-504.16.2.el6.x86_64

RHEL6.5 64位,2.6.32-504.16.2.el6.x86_64

OGG版本

11.2.1.0.1 64

11.2.1.0.1 64

OS hostname

orcltest

rhel6_lhr

 

 

 

一.2.3  相关参考文章链接

 

OGGOGG的下载和安装篇:http://blog.itpub.net/26736162/viewspace-1693241/

 

 

 

一.2.4  本文简介

 

 

本文基于OGGDML复制功能,后续会推出系列的OGG配置,包括DDL复制,双向复制,还有rac到单实例的复制等等,主要参考网址为:http://ylw6006.blog.51cto.com/all/470441/16 ,非常感谢斩月大师。

 

 

 

 

 

一.3  实验部分

 

一.3.1  实验目标

 

配置2台服务器,搭建OGG,实现hr用户下的数据dml复制功能。

 

wps6B0E.tmp 

 

 

一.3.2  准备工作,在sourcetarget端都配置

 

一.3.2.1  确保/etc/hosts配置正确

首先在sourcetarget端确保/etc/hosts中的主机名到ip地址的解析是正确的,且127.0.0.1没有指向本主机名,这里以target端为例:

[oracle@rhel6_lhr ~]$ hostname

rhel6_lhr

[oracle@rhel6_lhr ~]$ more /etc/hosts

127.0.0.1   localhost localhost.localdomain localhost4 localhost4.localdomain4

::1         localhost localhost.localdomain localhost6 localhost6.localdomain6

 

192.168.59.130 rhel6_lhr

[oracle@rhel6_lhr ~]$ ifconfig

eth0      Link encap:Ethernet  HWaddr 00:0C:29:C0:A7:56 

          inet addr:192.168.59.130  Bcast:192.168.59.255  Mask:255.255.255.0

          inet6 addr: fe80::20c:29ff:fec0:a756/64 Scope:Link

          UP BROADCAST RUNNING MULTICAST  MTU:1500  Metric:1

          RX packets:650153 errors:0 dropped:0 overruns:0 frame:0

          TX packets:160873 errors:0 dropped:0 overruns:0 carrier:0

          collisions:0 txqueuelen:1000

          RX bytes:824479355 (786.2 MiB)  TX bytes:16495312 (15.7 MiB)

 

lo        Link encap:Local Loopback 

          inet addr:127.0.0.1  Mask:255.0.0.0

          inet6 addr: ::1/128 Scope:Host

          UP LOOPBACK RUNNING  MTU:65536  Metric:1

          RX packets:104417 errors:0 dropped:0 overruns:0 frame:0

          TX packets:104417 errors:0 dropped:0 overruns:0 carrier:0

          collisions:0 txqueuelen:0

          RX bytes:26855022 (25.6 MiB)  TX bytes:26855022 (25.6 MiB)

 

[oracle@rhel6_lhr ~]$

 

一.3.2.2  dbca创建2套新环境用于实验

source端:

[oracle@orcltest ~]$ dbca -silent -createDatabase -templateName General_Purpose.dbc -gdbname ogg1 -sid ogg1 -sysPassword lhr -systemPassword lhr -responseFile NO_VALUE -datafileDestination /u01/app/oracle/oradata -redoLogFileSize 50 -recoveryAreaDestination /u01/app/oracle/flash_recovery_area -storageType FS -characterSet ZHS16GBK -nationalCharacterSet AL16UTF16 -sampleSchema true -memoryPercentage 30 -totalMemory 200 -databaseType OLTP -emConfiguration NONE  -automaticMemoryManagement true

 

Copying database files

1% complete

3% complete

10% complete

17% complete

24% complete

35% complete

Creating and starting Oracle instance

37% complete

42% complete

47% complete

52% complete

53% complete

56% complete

58% complete

Registering database with Oracle Restart

64% complete

Completing Database Creation

68% complete

71% complete

75% complete

85% complete

96% complete

100% complete

Look at the log file "/u02/app/oracle/cfgtoollogs/dbca/ogg1/ogg1.log" for further details.

[oracle@orcltest ~]$

[oracle@orcltest ~]$ ps -ef|grep ora_

oracle   20620     1  0 10:18 ?        00:00:00 ora_pmon_ogg1

oracle   20622     1  0 10:18 ?        00:00:00 ora_psp0_ogg1

oracle   20624     1  0 10:19 ?        00:00:00 ora_vktm_ogg1

oracle   20628     1  0 10:19 ?        00:00:00 ora_gen0_ogg1

oracle   20630     1  0 10:19 ?        00:00:00 ora_diag_ogg1

oracle   20632     1  0 10:19 ?        00:00:00 ora_dbrm_ogg1

oracle   20634     1  0 10:19 ?        00:00:00 ora_dia0_ogg1

oracle   20636     1  0 10:19 ?        00:00:00 ora_mman_ogg1

oracle   20638     1  0 10:19 ?        00:00:00 ora_dbw0_ogg1

oracle   20640     1  0 10:19 ?        00:00:00 ora_lgwr_ogg1

oracle   20642     1  0 10:19 ?        00:00:00 ora_ckpt_ogg1

oracle   20644     1  0 10:19 ?        00:00:00 ora_smon_ogg1

oracle   20646     1  0 10:19 ?        00:00:00 ora_reco_ogg1

oracle   20648     1  0 10:19 ?        00:00:00 ora_mmon_ogg1

oracle   20650     1  0 10:19 ?        00:00:00 ora_mmnl_ogg1

oracle   20652     1  0 10:19 ?        00:00:00 ora_d000_ogg1

oracle   20654     1  0 10:19 ?        00:00:00 ora_s000_ogg1

oracle   20690     1  0 10:19 ?        00:00:00 ora_arc0_ogg1

oracle   20692     1  0 10:19 ?        00:00:00 ora_arc1_ogg1

oracle   20694     1  0 10:19 ?        00:00:00 ora_arc2_ogg1

oracle   20696     1  0 10:19 ?        00:00:00 ora_arc3_ogg1

oracle   20698     1  0 10:19 ?        00:00:00 ora_qmnc_ogg1

oracle   20729     1  0 10:19 ?        00:00:00 ora_cjq0_ogg1

oracle   20744     1  0 10:19 ?        00:00:00 ora_q000_ogg1

oracle   20746     1  0 10:19 ?        00:00:00 ora_q001_ogg1

oracle   20768 19494  0 10:21 pts/0    00:00:00 grep ora_

[oracle@orcltest ~]$

 

 

 

target端:

[oracle@rhel6_lhr ~]$ dbca -silent -createDatabase -templateName General_Purpose.dbc -gdbname ogg2 -sid ogg2 -sysPassword lhr -systemPassword lhr -responseFile NO_VALUE -datafileDestination /u01/app/oracle/oradata -redoLogFileSize 50 -recoveryAreaDestination /u01/app/oracle/flash_recovery_area -storageType FS -characterSet ZHS16GBK -nationalCharacterSet AL16UTF16 -sampleSchema true -memoryPercentage 30 -totalMemory 200 -databaseType OLTP -emConfiguration NONE  -automaticMemoryManagement true

Copying database files

1% complete

3% complete

10% complete

17% complete

24% complete

31% complete

35% complete

Creating and starting Oracle instance

37% complete

42% complete

47% complete

52% complete

53% complete

56% complete

58% complete

Registering database with Oracle Restart

64% complete

Completing Database Creation

68% complete

71% complete

75% complete

85% complete

96% complete

100% complete

Look at the log file "/u01/app/oracle/cfgtoollogs/dbca/ogg2/ogg2.log" for further details.

You have new mail in /var/spool/mail/oracle

[oracle@rhel6_lhr ~]$ ps -ef|grep ora_

oracle   12227     1  0 20:12 ?        00:00:00 ora_pmon_ogg2

oracle   12229     1  0 20:12 ?        00:00:00 ora_psp0_ogg2

oracle   12231     1  1 20:12 ?        00:00:03 ora_vktm_ogg2

oracle   12235     1  0 20:12 ?        00:00:00 ora_gen0_ogg2

oracle   12237     1  0 20:12 ?        00:00:00 ora_diag_ogg2

oracle   12239     1  0 20:12 ?        00:00:00 ora_dbrm_ogg2

oracle   12241     1  0 20:12 ?        00:00:00 ora_dia0_ogg2

oracle   12243     1  0 20:12 ?        00:00:00 ora_mman_ogg2

oracle   12245     1  0 20:12 ?        00:00:00 ora_dbw0_ogg2

oracle   12247     1  0 20:12 ?        00:00:00 ora_lgwr_ogg2

oracle   12249     1  0 20:12 ?        00:00:00 ora_ckpt_ogg2

oracle   12251     1  0 20:12 ?        00:00:00 ora_smon_ogg2

oracle   12253     1  0 20:12 ?        00:00:00 ora_reco_ogg2

oracle   12255     1  0 20:12 ?        00:00:00 ora_mmon_ogg2

oracle   12257     1  0 20:12 ?        00:00:00 ora_mmnl_ogg2

oracle   12259     1  0 20:12 ?        00:00:00 ora_d000_ogg2

oracle   12261     1  0 20:12 ?        00:00:00 ora_s000_ogg2

oracle   12289     1  0 20:12 ?        00:00:00 ora_arc0_ogg2

oracle   12291     1  0 20:12 ?        00:00:00 ora_arc1_ogg2

oracle   12293     1  0 20:12 ?        00:00:00 ora_arc2_ogg2

oracle   12295     1  0 20:12 ?        00:00:00 ora_arc3_ogg2

oracle   12297     1  0 20:12 ?        00:00:00 ora_qmnc_ogg2

oracle   12330     1  0 20:12 ?        00:00:00 ora_cjq0_ogg2

oracle   12347     1  0 20:13 ?        00:00:00 ora_q000_ogg2

oracle   12349     1  0 20:13 ?        00:00:00 ora_q001_ogg2

oracle   12492  7669  0 20:15 pts/2    00:00:00 grep ora_

[oracle@rhel6_lhr ~]$

 

 

 

一.3.2.3  配置环境变量listenertnsnames.ora文件

该步骤确保ORACLEOGG的环境变量正确,listener启动即可。

[oracle@orcltest ~]$ more .bash_profile

# .bash_profile

 

# Get the aliases and functions

if [ -f ~/.bashrc ]; then

        . ~/.bashrc

fi

 

 

# User specific environment and startup programs

 

export PATH=PATH:/sbin/:PATH:/sbin/:HOME/bin

 

 

# for oracle user

 

unset USERNAME

 

export EDITOR=vi

export ORACLE_SID=ogg1

export ORACLE_BASE=/u02/app/oracle

export ORACLE_HOME=$ORACLE_BASE/product/11.2.0/dbhome_1

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

export PATH=ORACLEHOME/bin:ORACLEHOME/bin:PATH

export CLASSPATH=ORACLEHOME/JRE:ORACLEHOME/JRE:ORACLE_HOME/jlib:ORACLEHOME/rdbms/jlib:ORACLEHOME/rdbms/jlib:ORACLE_HOME/network/jlib

export TNS_ADMIN=$ORACLE_HOME/network/admin

 

umask 022

 

#export NLS_LANG="SIMPLIFIED CHINESE_CHINA.AL32UTF8"

export NLS_LANG=AMERICAN_AMERICA.ZHS16GBK

export SQLPATH=$ORACLE_HOME/sqlplus/admin

 

alias sqlplus='rlwrap sqlplus'

alias rman='rlwrap rman'

alias asmcmd='rlwrap asmcmd'

alias alert_log='tail -200f ORACLEBASE/diag/rdbms/ORACLEBASE/diag/rdbms/ORACLE_SID/ORACLE_SID/trace/alert_ORACLE_SID/trace/alert_ORACLE_SID.log'

 

 

export OGG_HOME=/u01/gg11

export PATH=OGGHOME:OGGHOME:ORACLE_HOME/bin:$PATH

export LD_LIBRARY_PATH=OGGHOME:OGGHOME:ORACLE_HOME/lib:$LD_LIBRARYPATH

 

alias ggsci='rlwrap ggsci'

 

export NLS_DATE_FORMAT='YYYY-MM-DD HH24:mi:ss'

[oracle@orcltest ~]$

[oracle@orcltest ~]moremoreTNS_ADMIN/tnsnames.ora

# tnsnames.ora Network Configuration File: /u02/app/oracle/product/11.2.0/dbhome_1/network/admin/tnsnames.ora

# Generated by Oracle configuration tools.

 

 

OGG1 =

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.59.129)(PORT = 1521))

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = ogg1)

    )

  )

OGG2 =

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.59.130)(PORT = 1521))

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = ogg2)

    )

  )

 

[oracle@orcltest ~]$

[oracle@orcltest ~]$ tnsping ogg1

 

TNS Ping Utility for Linux: Version 11.2.0.3.0 - Production on 10-JUN-2015 10:29:54

 

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

 

Used parameter files:

/u02/app/oracle/product/11.2.0/dbhome_1/network/admin/sqlnet.ora

 

 

Used TNSNAMES adapter to resolve the alias

Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.59.129)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = ogg1)))

OK (0 msec)

[oracle@orcltest ~]$ tnsping ogg2

 

TNS Ping Utility for Linux: Version 11.2.0.3.0 - Production on 10-JUN-2015 10:30:02

 

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

 

Used parameter files:

/u02/app/oracle/product/11.2.0/dbhome_1/network/admin/sqlnet.ora

 

 

Used TNSNAMES adapter to resolve the alias

Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.59.130)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = ogg2)))

OK (0 msec)

[oracle@orcltest ~]$ sqlplus sys/lhr@ogg1 as sysdba

 

SQL*Plus: Release 11.2.0.3.0 Production on Wed Jun 10 10:30:13 2015

 

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>

SQL> exit

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

[oracle@orcltest ~]$ sqlplus sys/lhr@ogg2 as sysdba

 

SQL*Plus: Release 11.2.0.3.0 Production on Wed Jun 10 10:30:21 2015

 

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> exit

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

[oracle@orcltest ~]$

 

 

一.3.2.4  数据库设置

这里需要确保source数据库运行在归档模式,开启最小数据库附加日志可以不用打开force logging,但是建议打开这个功能,然后在sourcetarget创建用于复制的数据库账号ggusr,为了方便,这里直接赋予dba权限,如果对数据库安全要求高,可以去查询ogg文档,赋予复制需要的最小权限!

source端:

[oracle@orcltest ~]$ sqlplus sys/lhr@ogg1 as sysdba

 

SQL*Plus: Release 11.2.0.3.0 Production on Wed Jun 10 10:31:52 2015

 

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> show parameter name

 

NAME                                 TYPE        VALUE

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

db_file_name_convert                 string

db_name                              string      ogg1

db_unique_name                       string      ogg1

global_names                         boolean     FALSE

instance_name                        string      ogg1

lock_name_space                      string

log_file_name_convert                string

processor_group_name                 string

service_names                        string      ogg1

SQL> archive log list;

Database log mode              Archive Mode

Automatic archival             Enabled

Archive destination            USE_DB_RECOVERY_FILE_DEST

Oldest online log sequence     3

Next log sequence to archive   5

Current log sequence           5

SQL> select name,SUPPLEMENTAL_LOG_DATA_MIN, FORCE_LOGGING from v$database;

 

NAME      SUPPLEME FOR

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

OGG1      NO       NO

 

SQL> alter database add supplemental log data ;

 

Database altered.

 

SQL> alter system switch logfile;

 

System altered.

 

SQL>  select name,SUPPLEMENTAL_LOG_DATA_MIN, FORCE_LOGGING from v$database;

 

NAME      SUPPLEME FOR

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

OGG1      YES      NO

 

SQL> select name from v$datafile;

 

NAME

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

/u01/app/oracle/oradata/ogg1/system01.dbf

/u01/app/oracle/oradata/ogg1/sysaux01.dbf

/u01/app/oracle/oradata/ogg1/undotbs01.dbf

/u01/app/oracle/oradata/ogg1/users01.dbf

/u01/app/oracle/oradata/ogg1/example01.dbf

 

SQL> create tablespace  TS_OGG  datafile '/u01/app/oracle/oradata/ogg1/ts_ogg01.dbf' size 50M ;

 

Tablespace created.

 

SQL> create user ggusr identified by lhr default tablespace TS_OGG;

 

User created.

 

SQL> grant  resource, connect, dba to ggusr;

 

Grant succeeded.

 

SQL>

 

target端:

[oracle@orcltest ~]$ sqlplus sys/lhr@ogg2 as sysdba

 

SQL*Plus: Release 11.2.0.3.0 Production on Wed Jun 10 10:37:08 2015

 

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> show parameter name

 

NAME                                 TYPE        VALUE

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

db_file_name_convert                 string

db_name                              string      ogg2

db_unique_name                       string      ogg2

global_names                         boolean     FALSE

instance_name                        string      ogg2

lock_name_space                      string

log_file_name_convert                string

processor_group_name                 string

service_names                        string      ogg2

SQL> archive log list;

Database log mode              Archive Mode

Automatic archival             Enabled

Archive destination            USE_DB_RECOVERY_FILE_DEST

Oldest online log sequence     3

Next log sequence to archive   5

Current log sequence           5

SQL>  select name,SUPPLEMENTAL_LOG_DATA_MIN, FORCE_LOGGING from v$database;

 

NAME      SUPPLEME FOR

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

OGG2      NO       NO

 

SQL> select name from v$datafile;

 

NAME

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

/u01/app/oracle/oradata/ogg2/system01.dbf

/u01/app/oracle/oradata/ogg2/sysaux01.dbf

/u01/app/oracle/oradata/ogg2/undotbs01.dbf

/u01/app/oracle/oradata/ogg2/users01.dbf

/u01/app/oracle/oradata/ogg2/example01.dbf

 

SQL> create tablespace  TS_OGG  datafile '/u01/app/oracle/oradata/ogg2/ts_ogg01.dbf' size 50M ;

 

Tablespace created.

 

SQL> create user ggusr identified by lhr default tablespace TS_OGG;

 

User created.

 

SQL> grant  resource, connect, dba to ggusr;

 

Grant succeeded.

 

SQL>

 

 

备注: 在oracle中可以通过rowid来定位某条记录,但是目标端的数据库和源端数据库的数据库可能完全不一样,所以无法通过rowid来确定源端数据库的逻辑变化,这时附加日志supplemental log便登上了表演的舞台。数据库在开启附加日志功能后,对于源端的修改操作,oracle会同时追加能够唯一标示记录的列到redo log。这样目标端数据库就可以知道源端发生了哪些具体的变化。

 

一.3.3  安装ogg软件,配置及启动mgr管理进程,sourcetarget端做相同的操作

以下操作在sourcetarget端都需要做:

一.3.3.1  安装OGG软件

 

参考:Oracle_lhr_gg安装.doc

http://blog.itpub.net/26736162/viewspace-1693241/

 

一.3.3.2  配置及启动mgr管理进程

sourcetarget端均操作:

 

[oracle@orcltest gg11]cdcdOGG_HOME

[oracle@orcltest gg11]$ 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 (orcltest) 1> edit params mgr

PORT 7809

~

~

。。。。。。。。。。。。。

 

~

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

 

 

GGSCI (orcltest) 2> view params mgr

 

PORT 7809

 

 

GGSCI (orcltest) 3> info all

 

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

 

MANAGER     STOPPED                                          

 

 

GGSCI (orcltest) 4> start mgr

 

Manager started.

 

 

GGSCI (orcltest) 5> info mgr

 

Manager is running (IP port orcltest.7809).

 

GGSCI (orcltest) 6> view report mgr

 

 

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

                 Oracle GoldenGate Manager for Oracle

      Version 11.2.1.0.1 OGGCORE_11.2.1.0.1_PLATFORMS_120423.0230

   Linux, x64, 64bit (optimized), Oracle 11g on Apr 23 2012 04:27:29

 

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

 

 

                    Starting at 2015-06-10 10:49:05

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

 

Operating System Version:

Linux

Version #1 SMP Sun Nov 10 22:19:54 EST 2013, Release 2.6.32-431.el6.x86_64

Node: orcltest

Machine: x86_64

                         soft limit   hard limit

Address Space Size   :    unlimited    unlimited

Heap Size            :    unlimited    unlimited

File Size            :    unlimited    unlimited

CPU Time             :    unlimited    unlimited

 

Process id: 21192

 

Parameters...

 

PORT 7809

 

 

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

**                     Run Time Messages                             **

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

 

 

2015-06-10 10:49:05  INFO    OGG-00983  Manager started (port 7809).

 

 

 

 

ogg告警日志内容(/u01/gg11/ggserr.log):

2015-06-09 20:46:45  INFO    OGG-00987  Oracle GoldenGate Command Interpreter for Oracle:  GGSCI command (oracle): edit params mgr.

2015-06-09 20:46:58  INFO    OGG-00987  Oracle GoldenGate Command Interpreter for Oracle:  GGSCI command (oracle): start mgr.

2015-06-09 20:46:58  INFO    OGG-00983  Oracle GoldenGate Manager for Oracle, mgr.prm:  Manager started (port 7809).

 

 

 

 

一.3.3.3  准备测试用户及表

解锁hr用户,并在hr用户下创建表t1

[oracle@orcltest gg11]$ sqlplus sys/lhr@ogg1 as sysdba

 

SQL*Plus: Release 11.2.0.3.0 Production on Wed Jun 10 10:56:45 2015

 

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> alter user hr identified by hr account unlock;

 

User altered.

 

SQL> grant connect,resource,select_catalog_role to hr;

 

Grant succeeded.

 

SQL> create table hr.t1 as select * from dba_objects;

 

Table created.

 

SQL> alter table hr.t1 add constraint pk_t1 primary key(object_id); 

 

Table altered.

 

SQL> select count(*) from hr.t1;

 

  COUNT(*)

----------

    75203

 

SQL>

SQL> conn  sys/lhr@ogg2  as sysdba

Connected.

SQL>  alter user hr identified by hr account unlock;

 

User altered.

 

SQL>  grant connect,resource,select_catalog_role to hr;

 

Grant succeeded.

 

SQL>  create table hr.t1 as select * from dba_objects where 1=2;

 

Table created.

 

SQL> alter table hr.t1 add constraint pk_t1 primary key(object_id); 

 

Table altered.

 

SQL> select count(*) from hr.t1;

 

  COUNT(*)

----------

         0

 

SQL>

 

一.3.4  初始化数据

初始化加载数据,在异构数据库平台(例如oracle-mysql),这个功能显得非常的有用!而在oracle-oracle的数据复制条件下,oracle推荐使用expdp/impdp工具

 

一.3.4.1  source端添加extract进程

 

GGSCI (orcltest) 1> add extract einig1,sourceistable   //sourceistable代表直接从表中读取数据  

EXTRACT added.

 

 

GGSCI (orcltest) 2> info all

 

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

 

MANAGER     RUNNING                                          

 

 

GGSCI (orcltest) 3info einig1,task

 

EXTRACT    EINIG1    Initialized   2015-06-10 11:03   Status STOPPED

Checkpoint Lag       Not Available

Log Read Checkpoint  Not Available

                     First Record         Record 0

Task                 SOURCEISTABLE

 

 

GGSCI (orcltest) 4info einig1,showch

 

EXTRACT    EINIG1    Initialized   2015-06-10 11:03   Status STOPPED

Checkpoint Lag       Not Available

Log Read Checkpoint  Not Available

                     First Record         Record 0

Task                 SOURCEISTABLE

 

 

Current Checkpoint Detail:

 

Read Checkpoint #1

 

  Database Table

 

  Startup Checkpoint (starting position in the data source):

    Record #: 0

    Timestamp: Not Available

    Table:

 

  Current Checkpoint (position of last record read in the data source):

    Record #: 0

    Timestamp: Not Available

    Table:

 

CSN state information:

  CRC: 7F-47-3E-B8

  CSN: Not available

 

Header:

  Version = 2

  Record Source = U

  Type = 3

  # Input Checkpoints = 1

  # Output Checkpoints = 0

 

File Information:

  Block Size = 2048

  Max Blocks = 100

  Record Length = 2048

  Current Offset = 0

 

Configuration:

  Data Source = 2

  Transaction Integrity = 0

  Task Type = 1

 

Status:

  Start Time = 2015-06-10 11:03:37

  Last Update Time = 2015-06-10 11:03:37

  Stop Status = G

  Last Result = 0

 

 

 

GGSCI (orcltest) 5>

 

GGSCI (orcltest) 5>  edit params einig1  //einig1代表extract initial load group 1缩写 

extract einig1

setenv (NLS_LANG=AMERICAN_AMERICA.ZHS16GBK)

userid ggusr,password lhr

rmthost 192.168.59.130,mgrport 7809

rmttask replicat,group rinig1

extract einig1

setenv (ORACLE_SID=ogg1)

setenv (NLS_LANG=AMERICAN_AMERICA.ZHS16GBK)

userid ggusr,password lhr

rmthost 192.168.59.130,mgrport 7809

rmttask replicat,group rinig1

table hr.t1;

~

~

~

。。。。。。。。。。。。。。。。。。

~

~

~

"dirprm/einig1.prm" 7L, 189C written

 

 

GGSCI (orcltest) 6> view params einig1

 

extract einig1

setenv (ORACLE_SID=ogg1)

setenv (NLS_LANG=AMERICAN_AMERICA.ZHS16GBK)

userid ggusr,password lhr

rmthost 192.168.59.130,mgrport 7809

rmttask replicat,group rinig1

table hr.t1;

 

 

GGSCI (orcltest) 7>

 

 

GGSCI (orcltest) 9> info *,task

 

EXTRACT    EINIG1    Initialized   2015-06-10 11:03   Status STOPPED

Checkpoint Lag       Not Available

Log Read Checkpoint  Not Available

                     First Record         Record 0

Task                 SOURCEISTABLE

 

 

GGSCI (orcltest) 10>

 

 

 

 

一.3.4.2  target端添加replicat进程

 

[oracle@rhel6_lhr gg11]$ 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 (rhel6_lhr) 1> info *,task

No ER groups exist.

 

GGSCI (rhel6_lhr) 2> add replicat rinig1,specialrun  //specialrun代表只运行一次

REPLICAT added.

 

 

GGSCI (rhel6_lhr) 3> info *,task

 

REPLICAT   RINIG1    Initialized   2015-06-09 22:18   Status STOPPED

Checkpoint Lag       00:00:00 (updated 00:00:03 ago)

Log Read Checkpoint  Not Available

Task                 SPECIALRUN

 

 

GGSCI (rhel6_lhr) 4> info all

 

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

 

MANAGER     RUNNING                                          

 

 

GGSCI (rhel6_lhr) 5> edit params rinig1  //rinig1的名字必须同source端定义的group名字相同,rinig1代表replicat initial load group 1缩写 

 

 

 

GGSCI (rhel6_lhr) 6> view params rinig1

 

replicat rinig1

setenv (ORACLE_SID=ogg2)

setenv (ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1)

setenv (NLS_LANG=AMERICAN_AMERICA.ZHS16GBK)

assumetargetdefs

userid ggusr,password lhr

discardfile ./dirrpt/rinig1.dsc,purge

map hr.*,target hr.*;

 

 

GGSCI (rhel6_lhr) 7>

GGSCI (rhel6_lhr) 7> info einig1,task

ERROR: Invalid command.

 

GGSCI (rhel6_lhr) 8> info rinig1

 

REPLICAT   RINIG1    Initialized   2015-06-09 22:18   Status STOPPED

Checkpoint Lag       00:00:00 (updated 00:03:33 ago)

Log Read Checkpoint  Not Available

Task                 SPECIALRUN

 

 

GGSCI (rhel6_lhr) 9> info rinig1,task

 

REPLICAT   RINIG1    Initialized   2015-06-09 22:18   Status STOPPED

Checkpoint Lag       00:00:00 (updated 00:03:52 ago)

Log Read Checkpoint  Not Available

Task                 SPECIALRUN

 

 

GGSCI (rhel6_lhr) 10> info rinig1,showch

 

REPLICAT   RINIG1    Initialized   2015-06-09 22:18   Status STOPPED

Checkpoint Lag       00:00:00 (updated 00:03:58 ago)

Log Read Checkpoint  Not Available

Task                 SPECIALRUN

 

 

Current Checkpoint Detail:

 

Read Checkpoint #1

 

SPECIALRUN task -- ** Initialized **

CSN state information:

  CRC: AF-F4-5D-35

  CSN: Not available

 

Header:

  Version = 2

  Record Source = U

  Type = -1

  # Input Checkpoints = 1

  # Output Checkpoints = 0

 

File Information:

  Block Size = 2048

  Max Blocks = 100

  Record Length = 2048

  Current Offset = 0

 

Configuration:

  Data Source = -1

  Transaction Integrity = -1

  Task Type = 2

 

Status:

  Start Time = 2015-06-09 22:18:34

  Last Update Time = 2015-06-09 22:18:34

  Stop Status = G

  Last Result = 0

 

 

 

GGSCI (rhel6_lhr) 11>

 

 

 

一.3.4.3  source端启动extract进程,查看日志输出

 

GGSCI (orcltest) 1> start extract einig1

 

Sending START request to MANAGER ...

EXTRACT EINIG1 starting

 

 

GGSCI (orcltest) 2> info *,task

 

EXTRACT    EINIG1    Initialized   2015-06-10 11:03   Status RUNNING

Checkpoint Lag       Not Available

Log Read Checkpoint  Not Available

                     First Record         Record 0

Task                 SOURCEISTABLE

 

 

GGSCI (orcltest) 3> view report einig1

 

 

2015-06-10 12:40:54  INFO    OGG-01017  Wildcard resolution set to IMMEDIATE because SOURCEISTABLE is used.

 

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

                 Oracle GoldenGate Capture 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:42:16

 

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

 

 

                    Starting at 2015-06-10 12:40:54

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

 

Operating System Version:

Linux

Version #1 SMP Sun Nov 10 22:19:54 EST 2013, Release 2.6.32-431.el6.x86_64

Node: orcltest

Machine: x86_64

                         soft limit   hard limit

Address Space Size   :    unlimited    unlimited

Heap Size            :    unlimited    unlimited

File Size            :    unlimited    unlimited

CPU Time             :    unlimited    unlimited

 

Process id: 21707

 

Description:

 

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

**            Running with the following parameters                  **

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

 

2015-06-10 12:40:54  INFO    OGG-03035  Operating system character set identified as UTF-8. Locale: en_US, LC_ALL:.

extract einig1

setenv (ORACLE_SID=ogg1)

Set environment variable (ORACLE_SID=ogg1)

setenv (ORACLE_HOME=/u02/app/oracle/product/11.2.0/dbhome_1)

Set environment variable (ORACLE_HOME=/u02/app/oracle/product/11.2.0/dbhome_1)

setenv (NLS_LANG=AMERICAN_AMERICA.ZHS16GBK)

Set environment variable (NLS_LANG=AMERICAN_AMERICA.ZHS16GBK)

userid ggusr,password ***

rmthost 192.168.59.130,mgrport 7809

rmttask replicat,group rinig1

table hr.t1;

Using the following key columns for source table HR.T1: OBJECT_ID.

 

 

2015-06-10 12:40:55  INFO    OGG-01815  Virtual Memory Facilities for: COM

    anon alloc: mmap(MAP_ANON)  anon free: munmap

    file alloc: mmap(MAP_SHARED)  file free: munmap

    target directories:

    /u01/gg11/dirtmp.

 

CACHEMGR virtual memory values (may have been adjusted)

CACHESIZE:                               64G

CACHEPAGEOUTSIZE (normal):                8M

PROCESS VM AVAIL FROM OS (min):         128G

CACHESIZEMAX (strict force to disk):     96G

 

Database Version:

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

PL/SQL Release 11.2.0.3.0 - Production

CORE    11.2.0.3.0      Production

TNS for Linux: Version 11.2.0.3.0 - Production

NLSRTL Version 11.2.0.3.0 - Production

 

Database Language and Character Set:

NLS_LANG         = "AMERICAN_AMERICA.ZHS16GBK"

NLS_LANGUAGE     = "AMERICAN"

NLS_TERRITORY    = "AMERICA"

NLS_CHARACTERSET = "ZHS16GBK"

 

Processing table HR.T1

 

 

GGSCI (orcltest) 4> view report einig1

 

 

2015-06-10 12:40:54  INFO    OGG-01017  Wildcard resolution set to IMMEDIATE because SOURCEISTABLE is used.

 

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

                 Oracle GoldenGate Capture 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:42:16

 

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

 

 

                    Starting at 2015-06-10 12:40:54

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

 

Operating System Version:

Linux

Version #1 SMP Sun Nov 10 22:19:54 EST 2013, Release 2.6.32-431.el6.x86_64

Node: orcltest

Machine: x86_64

                         soft limit   hard limit

Address Space Size   :    unlimited    unlimited

Heap Size            :    unlimited    unlimited

File Size            :    unlimited    unlimited

CPU Time             :    unlimited    unlimited

 

Process id: 21707

 

Description:

 

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

**            Running with the following parameters                  **

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

 

2015-06-10 12:40:54  INFO    OGG-03035  Operating system character set identified as UTF-8. Locale: en_US, LC_ALL:.

extract einig1

setenv (ORACLE_SID=ogg1)

Set environment variable (ORACLE_SID=ogg1)

setenv (ORACLE_HOME=/u02/app/oracle/product/11.2.0/dbhome_1)

Set environment variable (ORACLE_HOME=/u02/app/oracle/product/11.2.0/dbhome_1)

setenv (NLS_LANG=AMERICAN_AMERICA.ZHS16GBK)

Set environment variable (NLS_LANG=AMERICAN_AMERICA.ZHS16GBK)

userid ggusr,password ***

rmthost 192.168.59.130,mgrport 7809

rmttask replicat,group rinig1

table hr.t1;

Using the following key columns for source table HR.T1: OBJECT_ID.

 

 

2015-06-10 12:40:55  INFO    OGG-01815  Virtual Memory Facilities for: COM

    anon alloc: mmap(MAP_ANON)  anon free: munmap

    file alloc: mmap(MAP_SHARED)  file free: munmap

    target directories:

    /u01/gg11/dirtmp.

 

CACHEMGR virtual memory values (may have been adjusted)

CACHESIZE:                               64G

CACHEPAGEOUTSIZE (normal):                8M

PROCESS VM AVAIL FROM OS (min):         128G

CACHESIZEMAX (strict force to disk):     96G

 

Database Version:

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

PL/SQL Release 11.2.0.3.0 - Production

CORE    11.2.0.3.0      Production

TNS for Linux: Version 11.2.0.3.0 - Production

NLSRTL Version 11.2.0.3.0 - Production

 

Database Language and Character Set:

NLS_LANG         = "AMERICAN_AMERICA.ZHS16GBK"

NLS_LANGUAGE     = "AMERICAN"

NLS_TERRITORY    = "AMERICA"

NLS_CHARACTERSET = "ZHS16GBK"

 

Processing table HR.T1

 

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

*                   ** Run Time Statistics **                         *

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

 

 

Report at 2015-06-10 12:42:01 (activity since 2015-06-10 12:40:55)

 

Output to rinig1:

 

From Table HR.T1:

       #                   inserts:     75203

       #                   updates:         0

       #                   deletes:         0

       #                  discards:         0

 

 

REDO Log Statistics

  Bytes parsed                    0

  Bytes output             21007968

 

GGSCI (orcltest) 5> info *,task   //再次查看的时候,数据已经初始化完成。

 

EXTRACT    EINIG1    Last Started 2015-06-10 12:41   Status STOPPED

Checkpoint Lag       Not Available

Log Read Checkpoint  Table HR.T1

                     2015-06-10 12:42:00  Record 75203

Task                 SOURCEISTABLE

 

 

GGSCI (orcltest) 6>

 

 

第一次查看日志,Processing table HR.T1表示正在处理,第二次查看出现Run Time Statistics 信息,表示数据已经初始化完成,insert75203条数据。

 

 

一.3.4.4  target端查看日志,并且数据量验证

 

GGSCI (rhel6_lhr) 1> view report rinig1

 

 

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

                 Oracle GoldenGate Delivery 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:48:07

 

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

 

 

                    Starting at 2015-06-09 22:34:42

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

 

Operating System Version:

Linux

Version #1 SMP Tue Apr 21 08:37:59 PDT 2015, Release 2.6.32-504.16.2.el6.x86_64

Node: rhel6_lhr

Machine: x86_64

                         soft limit   hard limit

Address Space Size   :    unlimited    unlimited

Heap Size            :    unlimited    unlimited

File Size            :    unlimited    unlimited

CPU Time             :    unlimited    unlimited

 

Process id: 19600

 

Description:

 

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

**            Running with the following parameters                  **

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

 

2015-06-09 22:34:47  INFO    OGG-03035  Operating system character set identified as UTF-8. Locale: en_US, LC_ALL:.

replicat rinig1

setenv (ORACLE_SID=ogg2)

Set environment variable (ORACLE_SID=ogg2)

setenv (ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1)

Set environment variable (ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1)

setenv (NLS_LANG=AMERICAN_AMERICA.ZHS16GBK)

Set environment variable (NLS_LANG=AMERICAN_AMERICA.ZHS16GBK)

assumetargetdefs

userid ggusr,password ***

discardfile ./dirrpt/rinig1.dsc,purge

map hr.*,target hr.*;

 

2015-06-09 22:34:48  INFO    OGG-01815  Virtual Memory Facilities for: COM

    anon alloc: mmap(MAP_ANON)  anon free: munmap

    file alloc: mmap(MAP_SHARED)  file free: munmap

    target directories:

    /u01/gg11/dirtmp.

 

CACHEMGR virtual memory values (may have been adjusted)

CACHESIZE:                                2G

CACHEPAGEOUTSIZE (normal):                8M

PROCESS VM AVAIL FROM OS (min):           4G

CACHESIZEMAX (strict force to disk):   3.41G

 

Database Version:

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

PL/SQL Release 11.2.0.3.0 - Production

CORE    11.2.0.3.0      Production

TNS for Linux: Version 11.2.0.3.0 - Production

NLSRTL Version 11.2.0.3.0 - Production

 

Database Language and Character Set:

NLS_LANG         = "AMERICAN_AMERICA.ZHS16GBK"

NLS_LANGUAGE     = "AMERICAN"

NLS_TERRITORY    = "AMERICA"

NLS_CHARACTERSET = "ZHS16GBK"

 

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

**                     Run Time Messages                             **

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

 

 

Wildcard MAP resolved (entry hr.*):

  map "HR"."T1",target hr."T1";

Using following columns in default map by name:

  OWNER, OBJECT_NAME, SUBOBJECT_NAME, OBJECT_ID, DATA_OBJECT_ID,

  OBJECT_TYPE, CREATED, LAST_DDL_TIME, TIMESTAMP, STATUS, TEMPORARY,

  GENERATED, SECONDARY, NAMESPACE, EDITION_NAME

Using the following key columns for target table HR.T1: OBJECT_ID.

 

 

 

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

*                   ** Run Time Statistics **                         *

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

 

 

Report at 2015-06-09 22:35:53 (activity since 2015-06-09 22:34:49)

 

From Table HR.T1 to HR.T1:

       #                   inserts:     75203

       #                   updates:         0

       #                   deletes:         0

       #                  discards:         0

 

 

CACHE OBJECT MANAGER statistics

 

CACHE MANAGER VM USAGE

vm current     =      0    vm anon queues =      0

vm anon in use =      0    vm file        =      0

vm used max    =      0    ==> CACHE BALANCED

 

CACHE CONFIGURATION

cache size       =   2G   cache force paging = 3.41G

buffer min       =  64K   buffer highwater   =   8M

pageout eligible size =   8M

 

================================================================================

RUNTIME STATS FOR SUPERPOOL

 

CACHE Transaction Stats

trans active   =      0    max concurrent =      0

non-zero total =      0    trans total    =      0

 

CACHE File Caching

disk current   =      0    disk total  =      0

disk caching   =      0    file cached =      0

file retrieves =      0

 

CACHE MANAGEMENT

buffer links  =      0   anon gets   =      0

forced unmaps =      0   cnnbl try   =      0

cached out    =      0   force out   =      0

 

Allocation Request Distribution

< 128B:       0

128B:        0         0     | 512B:        0         0

   2K:        0         0     |   8K:        0         0

  32K:        0         0     | 128K:        0         0

512K:        0         0     |   2M:        0         0

   8M:        0         0     |  32M:        0         0

128M:        0         0     | 512M:        0         0

   2G:        0         0     |   8G:        0

 

Cached Transaction Size Distribution

    0:        0

< 4K:        0

   4K:        0         0     |  16K:        0         0

  64K:        0         0     | 256K:        0         0

   1M:        0         0     |   4M:        0         0

  16M:        0         0     |  64M:        0         0

256M:        0         0     |   1G:        0         0

   4G:        0         0     |  16G:        0         0

  64G:        0         0     | 256G:        0         0

   1T:        0         0     |   4T:        0         0

  16T:        0         0     |  64T:        0         0

256T:        0         0     |1024T:        0         0

 

================================================================================

CUMULATIVE STATS FOR SUPERPOOL

 

CACHE Transaction Stats

trans active   =      0    max concurrent =      0

non-zero total =      0    trans total    =      0

 

CACHE File Caching

disk current   =      0    disk total  =      0

disk caching   =      0    file cached =      0

file retrieves =      0

 

CACHE MANAGEMENT

buffer links  =      0   anon gets   =      0

forced unmaps =      0   cnnbl try   =      0

cached out    =      0   force out   =      0

 

Allocation Request Distribution

< 128B:       0

128B:        0         0     | 512B:        0         0

   2K:        0         0     |   8K:        0         0

  32K:        0         0     | 128K:        0         0

512K:        0         0     |   2M:        0         0

   8M:        0         0     |  32M:        0         0

128M:        0         0     | 512M:        0         0

   2G:        0         0     |   8G:        0

 

Cached Transaction Size Distribution

    0:        0

< 4K:        0

   4K:        0         0     |  16K:        0         0

  64K:        0         0     | 256K:        0         0

   1M:        0         0     |   4M:        0         0

  16M:        0         0     |  64M:        0         0

256M:        0         0     |   1G:        0         0

   4G:        0         0     |  16G:        0         0

  64G:        0         0     | 256G:        0         0

   1T:        0         0     |   4T:        0         0

  16T:        0         0     |  64T:        0         0

256T:        0         0     |1024T:        0         0

 

 

QUEUE Statistics:

num queues    =     15     default index =      0

cur len       =      0     max len       =      0

q vm current  =      0     vm max        =      0

q hits        =      0     q misses      =      0

 

queue size  q hits  curlen  maxlen     cannibalized

  0   64K      0       0       0       0

  1  128K      0       0       0       0

  2  256K      0       0       0       0

  3  512K      0       0       0       0

  4    1M      0       0       0       0

  5    2M      0       0       0       0

  6    4M      0       0       0       0

  7    8M      0       0       0       0

  8   16M      0       0       0       0

  9   32M      0       0       0       0

10   64M      0       0       0       0

11  128M      0       0       0       0

12  256M      0       0       0       0

13  512M      0       0       0       0

14    1G      0       0       0       0

 

================================================================================

RUNTIME STATS FOR CACHE POOL #0

POOL INFO   group: rinig1  id: p19600_BLOB

trans active  =       0   trans concurrent (max) =     0

trans total   =       0   (0 )

flag          = 0x00000030

last error    = (0=<none>)

 

Allocation Request Distribution

< 128B:       0

128B:        0         0     | 512B:        0         0

   2K:        0         0     |   8K:        0         0

  32K:        0         0     | 128K:        0         0

512K:        0         0     |   2M:        0         0

   8M:        0         0     |  32M:        0         0

128M:        0         0     | 512M:        0         0

   2G:        0         0     |   8G:        0

 

================================================================================

CUMULATIVE STATS FOR CACHE POOL #0

POOL INFO   group: rinig1  id: p19600_BLOB

trans active  =       0   trans concurrent (max) =     0

trans total   =       0   (0 )

flag          = 0x00000030

last error    = (0=<none>)

 

Allocation Request Distribution

< 128B:       0

128B:        0         0     | 512B:        0         0

   2K:        0         0     |   8K:        0         0

  32K:        0         0     | 128K:        0         0

512K:        0         0     |   2M:        0         0

   8M:        0         0     |  32M:        0         0

128M:        0         0     | 512M:        0         0

   2G:        0         0     |   8G:        0

 

 

QUEUE Statistics:

num queues    =     15     default index =      0

cur len       =      0     max len       =      0

q vm current  =      0     vm max        =      0

q hits        =      0     q misses      =      0

 

queue size  q hits  curlen  maxlen     cannibalized

  0   64K      0       0       0       0

  1  128K      0       0       0       0

  2  256K      0       0       0       0

  3  512K      0       0       0       0

  4    1M      0       0       0       0

  5    2M      0       0       0       0

  6    4M      0       0       0       0

  7    8M      0       0       0       0

  8   16M      0       0       0       0

  9   32M      0       0       0       0

10   64M      0       0       0       0

11  128M      0       0       0       0

12  256M      0       0       0       0

13  512M      0       0       0       0

14    1G      0       0       0       0

 

================================================================================

RUNTIME STATS FOR CACHE POOL #0

POOL INFO   group: rinig1  id: p19600_BLOB

trans active  =       0   trans concurrent (max) =     0

trans total   =       0   (0 )

flag          = 0x00000030

last error    = (0=<none>)

 

Allocation Request Distribution

< 128B:       0

128B:        0         0     | 512B:        0         0

   2K:        0         0     |   8K:        0         0

  32K:        0         0     | 128K:        0         0

512K:        0         0     |   2M:        0         0

   8M:        0         0     |  32M:        0         0

128M:        0         0     | 512M:        0         0

   2G:        0         0     |   8G:        0

 

================================================================================

CUMULATIVE STATS FOR CACHE POOL #0

POOL INFO   group: rinig1  id: p19600_BLOB

trans active  =       0   trans concurrent (max) =     0

trans total   =       0   (0 )

flag          = 0x00000030

last error    = (0=<none>)

 

Allocation Request Distribution

< 128B:       0

128B:        0         0     | 512B:        0         0

   2K:        0         0     |   8K:        0         0

  32K:        0         0     | 128K:        0         0

512K:        0         0     |   2M:        0         0

   8M:        0         0     |  32M:        0         0

128M:        0         0     | 512M:        0         0

   2G:        0         0     |   8G:        0

 

 

 

GGSCI (rhel6_lhr) 2> info *,task

 

REPLICAT   RINIG1    Initialized   2015-06-09 22:18   Status STOPPED

Checkpoint Lag       00:00:00 (updated 00:23:29 ago)

Log Read Checkpoint  Not Available

Task                 SPECIALRUN

 

 

GGSCI (rhel6_lhr) 3>

 

 

数据量验证:

[oracle@orcltest ~]$ sqlplus sys/lhr@ogg2 as sysdba

 

SQL*Plus: Release 11.2.0.3.0 Production on Wed Jun 10 12:41:48 2015

 

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 count(*) from hr.t1;

 

  COUNT(*)

----------

     75203

一.3.5  配置OGG的实时同步功能

 

一.3.5.1  开启hr用户下所有表的附加日志

打开需要传输的schema的表的附加日志:

[oracle@orcltest gg11]$ 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 (orcltest) 1> dblogin userid ggusr, password lhr

ERROR: Unable to connect to database using user ggusr. Please check privileges.

ORA-01034: ORACLE not available

ORA-27101: shared memory realm does not exist

Linux-x86_64 Error: 2: No such file or directory.

 

GGSCI (orcltest) 2> dblogin userid ggusr@ogg1, password lhr

Successfully logged into database.

 

GGSCI (orcltest) 3> info  trandata hr.*

 

Logging of supplemental redo log data is disabled for table HR.COUNTRIES.

 

Logging of supplemental redo log data is disabled for table HR.DEPARTMENTS.

 

Logging of supplemental redo log data is disabled for table HR.EMPLOYEES.

 

Logging of supplemental redo log data is disabled for table HR.JOBS.

 

Logging of supplemental redo log data is disabled for table HR.JOB_HISTORY.

 

Logging of supplemental redo log data is disabled for table HR.LOCATIONS.

 

Logging of supplemental redo log data is disabled for table HR.REGIONS.

 

Logging of supplemental redo log data is disabled for table HR.T1.

 

GGSCI (orcltest) 4> add trandata hr.*

 

Logging of supplemental redo data enabled for table HR.COUNTRIES.

 

Logging of supplemental redo data enabled for table HR.DEPARTMENTS.

 

Logging of supplemental redo data enabled for table HR.EMPLOYEES.

 

Logging of supplemental redo data enabled for table HR.JOBS.

 

Logging of supplemental redo data enabled for table HR.JOB_HISTORY.

 

Logging of supplemental redo data enabled for table HR.LOCATIONS.

 

Logging of supplemental redo data enabled for table HR.REGIONS.

 

Logging of supplemental redo data enabled for table HR.T1.

 

GGSCI (orcltest) 5>

GGSCI (orcltest) 5> info  trandata hr.*

 

Logging of supplemental redo log data is enabled for table HR.COUNTRIES.

 

Columns supplementally logged for table HR.COUNTRIES: COUNTRY_ID.

 

Logging of supplemental redo log data is enabled for table HR.DEPARTMENTS.

 

Columns supplementally logged for table HR.DEPARTMENTS: DEPARTMENT_ID.

 

Logging of supplemental redo log data is enabled for table HR.EMPLOYEES.

 

Columns supplementally logged for table HR.EMPLOYEES: EMPLOYEE_ID.

 

Logging of supplemental redo log data is enabled for table HR.JOBS.

 

Columns supplementally logged for table HR.JOBS: JOB_ID.

 

Logging of supplemental redo log data is enabled for table HR.JOB_HISTORY.

 

Columns supplementally logged for table HR.JOB_HISTORY: EMPLOYEE_ID, START_DATE.

 

Logging of supplemental redo log data is enabled for table HR.LOCATIONS.

 

Columns supplementally logged for table HR.LOCATIONS: LOCATION_ID.

 

Logging of supplemental redo log data is enabled for table HR.REGIONS.

 

Columns supplementally logged for table HR.REGIONS: REGION_ID.

 

Logging of supplemental redo log data is enabled for table HR.T1.

 

Columns supplementally logged for table HR.T1: OBJECT_ID.

 

一.3.5.2  source添加及配置extract进程,进程的名字不能超过8个字符;添加trail文件,文件名前缀不能超过2个字符

 

一、 添加并配置extract进程

 

GGSCI (orcltest) 1> add extract eora_hr,tranlog,begin now

EXTRACT added.

 

 

GGSCI (orcltest) 2> info all

 

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

 

MANAGER     RUNNING                                          

EXTRACT     STOPPED     EORA_HR     00:00:00      00:00:06  

 

GGSCI (orcltest) 1> edit params eora_hr 

extract eora_hr

setenv (NLS_LANG=AMERICAN_AMERICA.ZHS16GBK)

ddl include all

userid ggusr, password lhr

exttrail ./dirdat/aa

extract eora_t1

setenv (ORACLE_SID=ogg1)

setenv (ORACLE_HOME=/u02/app/oracle/product/11.2.0/dbhome_1)

setenv (NLS_LANG=AMERICAN_AMERICA.ZHS16GBK)

userid ggusr,password lhr

exttrail ./dirdat/hr

table hr.*;

~

~

~

。。。。。。。。。。。。。

~

~

~

~

"dirprm/eora_hr.prm" 7L, 213C written

 

 

GGSCI (orcltest) 2> view params eora_t1 

 

extract eora_hr 

setenv (ORACLE_SID=ogg1)

setenv (ORACLE_HOME=/u02/app/oracle/product/11.2.0/dbhome_1)

setenv (NLS_LANG=AMERICAN_AMERICA.ZHS16GBK) 

userid ggusr,password lhr

exttrail ./dirdat/hr 

table hr.*;

 

 

GGSCI (orcltest) 3>

 

 

二、 添加trail文件,文件名前缀不能超过2个字符

GGSCI (orcltest) 6> add exttrail ./dirdat/hr,extract eora_hr,megabytes 100

EXTTRAIL added.

 

 

GGSCI (orcltest) 7> info all

 

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

 

MANAGER     RUNNING                                          

EXTRACT     STOPPED     EORA_HR     00:00:00      00:05:08   

 

 

GGSCI (orcltest) 8> start extract eora_hr

 

Sending START request to MANAGER ...

EXTRACT EORA_HR starting

 

 

GGSCI (orcltest) 9> info all

 

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

 

MANAGER     RUNNING                                          

EXTRACT     RUNNING     EORA_HR     00:00:00      00:00:08   

 

 

GGSCI (orcltest) 10>

 

GGSCI (orcltest) 10> info extract eora_hr

 

EXTRACT    EORA_HR   Last Started 2015-06-10 14:57   Status RUNNING

Checkpoint Lag       00:00:00 (updated 00:00:07 ago)

Log Read Checkpoint  Oracle Redo Logs

                     2015-06-10 14:57:05  Seqno 7, RBA 5097472

                     SCN 0.1098894 (1098894)

 

 

GGSCI (orcltest) 11>

 

 

 

 

一.3.5.3  添加并启动pump进程

GGSCI (orcltest) 11> edit params pora_hr

extract pora_hr

setenv (ORACLE_SID=ogg1)

setenv (ORACLE_HOME=/u02/app/oracle/product/11.2.0/dbhome_1)

setenv (NLS_LANG=AMERICAN_AMERICA.ZHS16GBK)

passthru

rmthost 192.168.59.130,mgrport 7809

rmttrail ./dirdat/pa

table hr.*;

~

~

~

。。。。。。。。。。。。。。

~

~

~

~

~

"dirprm/pora_hr.prm" [New] 8L, 235C written

 

 

GGSCI (orcltest) 12> view params pora_hr

 

extract pora_hr 

setenv (ORACLE_SID=ogg1)

setenv (ORACLE_HOME=/u02/app/oracle/product/11.2.0/dbhome_1)

setenv (NLS_LANG=AMERICAN_AMERICA.ZHS16GBK)  

passthru 

rmthost 192.168.59.130,mgrport 7809 

rmttrail ./dirdat/pa 

table hr.*;

 

 

GGSCI (orcltest) 13> add extract pora_hr,exttrailsource ./dirdat/hr   //这里的hr文件名同前面extract进程参数文件中定义的trail文件名一 

EXTRACT added.

 

 

GGSCI (orcltest) 19> info all

 

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

 

MANAGER     RUNNING                                          

EXTRACT     RUNNING     EORA_HR     00:00:00      00:00:00   

EXTRACT     STOPPED     PORA_HR     00:00:00      00:00:02   

 

 

GGSCI (orcltest) 20>  add rmttrail ./dirdat/pa,extract pora_hr,megabytes 100    //添加传输到target数据库的trail文件名,应该同参数文件中描述的一致 

 

RMTTRAIL added.

 

 

GGSCI (orcltest) 21>

GGSCI (orcltest) 21> start extract PORA_HR

 

Sending START request to MANAGER ...

EXTRACT PORA_HR starting

 

 

GGSCI (orcltest) 22> info all

 

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

 

MANAGER     RUNNING                                          

EXTRACT     RUNNING     EORA_HR     00:00:00      00:00:04   

EXTRACT     RUNNING     PORA_HR     00:00:00      00:03:47   

 

 

GGSCI (orcltest) 23>

 

GGSCI (orcltest) 24> view report PORA_HR

 

 

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

                 Oracle GoldenGate Capture 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:42:16

 

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

 

 

                    Starting at 2015-06-10 15:10:37

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

 

Operating System Version:

Linux

Version #1 SMP Sun Nov 10 22:19:54 EST 2013, Release 2.6.32-431.el6.x86_64

Node: orcltest

Machine: x86_64

                         soft limit   hard limit

Address Space Size   :    unlimited    unlimited

Heap Size            :    unlimited    unlimited

File Size            :    unlimited    unlimited

CPU Time             :    unlimited    unlimited

 

Process id: 22326

 

Description:

 

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

**            Running with the following parameters                  **

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

 

2015-06-10 15:10:37  INFO    OGG-03035  Operating system character set identified as UTF-8. Locale: en_US, LC_ALL:.

extract pora_hr

setenv (ORACLE_SID=ogg1)

Set environment variable (ORACLE_SID=ogg1)

setenv (ORACLE_HOME=/u02/app/oracle/product/11.2.0/dbhome_1)

Set environment variable (ORACLE_HOME=/u02/app/oracle/product/11.2.0/dbhome_1)

setenv (NLS_LANG=AMERICAN_AMERICA.ZHS16GBK)

Set environment variable (NLS_LANG=AMERICAN_AMERICA.ZHS16GBK)

passthru

rmthost 192.168.59.130,mgrport 7809

rmttrail ./dirdat/pa

table hr.*;

 

2015-06-10 15:10:37  INFO    OGG-01815  Virtual Memory Facilities for: COM

    anon alloc: mmap(MAP_ANON)  anon free: munmap

    file alloc: mmap(MAP_SHARED)  file free: munmap

    target directories:

    /u01/gg11/dirtmp.

 

CACHEMGR virtual memory values (may have been adjusted)

CACHESIZE:                               64G

CACHEPAGEOUTSIZE (normal):                8M

PROCESS VM AVAIL FROM OS (min):         128G

CACHESIZEMAX (strict force to disk):     96G

 

2015-06-10 15:10:42  INFO    OGG-01226  Socket buffer size set to 27985 (flush size 27985).

 

2015-06-10 15:10:42  INFO    OGG-01052  No recovery is required for target file ./dirdat/pa000000, at RBA 0 (file not opened).

 

2015-06-10 15:10:42  INFO    OGG-01478  Output file ./dirdat/pa is using format RELEASE 11.2.

 

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

**                     Run Time Messages                             **

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

 

Opened trail file ./dirdat/hr000000 at 2015-06-10 15:10:42

 

 

GGSCI (orcltest) 25>

 

 

 

一.3.5.4  target端添加检查表,配置replicat进程

 

一、 添加检查表

 

GGSCI (rhel6_lhr) 12>  edit params ./GLOBALS 

 

 

 

GGSCI (rhel6_lhr) 13> view params ./GLOBALS

 

checkpointtable ggusr.rep_demo_ckpt

 

 

GGSCI (rhel6_lhr) 14>

 

GGSCI (rhel6_lhr) 15> dblogin userid ggusr, password lhr

ERROR: Unable to connect to database using user ggusr. Please check privileges.

ORA-00942: table or view does not exist.

 

GGSCI (rhel6_lhr) 16dblogin userid ggusr@ogg2, password lhr

Successfully logged into database.

 

GGSCI (rhel6_lhr) 3> add checkpointtable ggusr.rep_demo_ckpt

 

Successfully created checkpoint table ggusr.rep_demo_ckpt.

 

 

GGSCI (rhel6_lhr) 4> add checkpointtable ggusr.rep_demo_ckpt

ERROR: OCI Error ORA-00955: name is already used by an existing object (status = 955). Creating checkpoint table ggusr.rep_demo_ckpt, SQL <CREATE TABLE ggusr.rep_demo_ckpt (  group_name VARCHAR2(8) NOT NULL,  group_key NUMBER(19) NOT NULL,  seqno NUMBER(10),  rba NUMBER(19) NOT NULL,  audit_ts VARCHAR2(29),  create_ts DATE NOT NULL,  last_update_ts DATE NOT NULL,  current_dir VARCHAR2(255) NOT NULL,  log_csn VARCHAR2(129),  log_xid VARCHAR2(129),  log_cmplt_csn VARCHAR2(129),  log_cmplt_xids VARCHAR2(2000),  version NUMBER(3),  PRIMARY KEY (group_name, group_key))>.

 

[oracle@rhel6_lhr admin]$ sqlplus ggusr/lhr@ogg2

 

SQL*Plus: Release 11.2.0.3.0 Production on 星期三 6月 10 01:19:16 2015

 

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

 

 

连接到:

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

 

01:19:16 SQL> select tname from tab;

 

TNAME

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

REP_DEMO_CKPT

REP_DEMO_CKPT_LOX

 

已用时间:  00: 00: 00.01

01:19:19 SQL>

 

 

select * from REP_DEMO_CKPT;

wps6B2E.tmp 

 

备注:
在target端配置replicat进程之前,通常需要在目标端的数据库中创建一个checkpoint表,这个表是基于ogg checkpoint文件的,它记录了所有ogg可恢复的checkpoint以及sequence,这个操作不是必须的,但oracle强烈建议使用它,因为它可以使得checkpoint包含在replicat的事务中,保证了可以从各类失败场景中恢复!

 

二、 添加并启动replicat进程

 

GGSCI (rhel6_lhr) 1>  edit params rora_hr

 

 

 

GGSCI (rhel6_lhr) 2> view params rora_hr

 

replicat rora_hr

setenv (ORACLE_SID=ogg2)

setenv (ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1)

setenv (NLS_LANG=AMERICAN_AMERICA.ZHS16GBK)

userid ggusr,password lhr 

handlecollisions 

assumetargetdefs 

discardfile ./dirrpt/rora_hr.dsc,purge 

map hr.* ,target hr.*;

 

 

GGSCI (rhel6_lhr) 3> add replicat rora_hr,exttrail ./dirdat/pa

REPLICAT added.

 

 

GGSCI (rhel6_lhr) 4> info all

 

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

 

MANAGER     RUNNING                                          

REPLICAT    STOPPED     RORA_HR     00:00:00      00:00:05   

 

 

GGSCI (rhel6_lhr) 5> start replicat rora_hr   //或者start RORA_HR

 

Sending START request to MANAGER ...

REPLICAT RORA_HR starting

 

GGSCI (rhel6_lhr) 6> info all

 

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

 

MANAGER     RUNNING                                          

REPLICAT    RUNNING     RORA_HR     00:00:00      00:00:01   

 

 

GGSCI (rhel6_lhr) 7> info replicat rora_hr

 

REPLICAT   RORA_HR   Last Started 2015-06-10 01:50   Status RUNNING

Checkpoint Lag       00:00:00 (updated 00:00:05 ago)

Log Read Checkpoint  File ./dirdat/pa000000

                     First Record  RBA 0

 

 

GGSCI (rhel6_lhr) 8> view report rora_hr

 

 

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

                 Oracle GoldenGate Delivery 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:48:07

 

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

 

 

                    Starting at 2015-06-10 01:50:28

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

 

Operating System Version:

Linux

Version #1 SMP Tue Apr 21 08:37:59 PDT 2015, Release 2.6.32-504.16.2.el6.x86_64

Node: rhel6_lhr

Machine: x86_64

                         soft limit   hard limit

Address Space Size   :    unlimited    unlimited

Heap Size            :    unlimited    unlimited

File Size            :    unlimited    unlimited

CPU Time             :    unlimited    unlimited

 

Process id: 30015

 

Description:

 

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

**            Running with the following parameters                  **

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

 

2015-06-10 01:50:28  INFO    OGG-03035  Operating system character set identified as UTF-8. Locale: en_US, LC_ALL:.

replicat rora_hr

setenv (ORACLE_SID=ogg2)

Set environment variable (ORACLE_SID=ogg2)

setenv (ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1)

Set environment variable (ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1)

setenv (NLS_LANG=AMERICAN_AMERICA.ZHS16GBK)

Set environment variable (NLS_LANG=AMERICAN_AMERICA.ZHS16GBK)

userid ggusr@ogg2,password ***

handlecollisions

assumetargetdefs

discardfile ./dirrpt/rora_hr.dsc,purge

map hr.* ,target hr.*;

 

2015-06-10 01:50:28  INFO    OGG-01815  Virtual Memory Facilities for: COM

    anon alloc: mmap(MAP_ANON)  anon free: munmap

    file alloc: mmap(MAP_SHARED)  file free: munmap

    target directories:

    /u01/gg11/dirtmp.

 

CACHEMGR virtual memory values (may have been adjusted)

CACHESIZE:                                2G

CACHEPAGEOUTSIZE (normal):                8M

PROCESS VM AVAIL FROM OS (min):           4G

CACHESIZEMAX (strict force to disk):   3.41G

 

Database Version:

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

PL/SQL Release 11.2.0.3.0 - Production

CORE    11.2.0.3.0      Production

TNS for Linux: Version 11.2.0.3.0 - Production

NLSRTL Version 11.2.0.3.0 - Production

 

Database Language and Character Set:

NLS_LANG         = "AMERICAN_AMERICA.ZHS16GBK"

NLS_LANGUAGE     = "AMERICAN"

NLS_TERRITORY    = "AMERICA"

NLS_CHARACTERSET = "ZHS16GBK"

 

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

**                     Run Time Messages                             **

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

 

Opened trail file ./dirdat/pa000000 at 2015-06-10 01:50:28

 

 

GGSCI (rhel6_lhr) 9>

 

 

 

 

 

一.3.5.5  测试OGG的实时同步功能

 

 

一、 源库插入数据分析

 

[oracle@orcltest ~]$ sqlplus hr/hr@ogg1

 

SQL*Plus: Release 11.2.0.3.0 Production on Wed Jun 10 16:01:33 2015

 

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 max(object_id) from t1;

 

MAX(OBJECT_ID)

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

         76834

 

SQL>  insert into t1 (object_id,object_name) values (76835,'ogg_test');

 

1 row created.

 

SQL> commit;

 

Commit complete.

 

SQL> select max(object_id) from t1;

 

MAX(OBJECT_ID)

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

         76835

 

SQL>  conn hr/hr@ogg2

Connected.

SQL> select max(object_id) from t1;

 

MAX(OBJECT_ID)

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

         76835

 

SQL> select object_name from  t1 where object_id=76835;

 

OBJECT_NAME

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

ogg_test

 

SQL>

 

可以看到数据实时同步了,我们看看备库的检查表的数据:

select * from REP_DEMO_CKPT;

wps6B3F.tmp 

 

二、 抽取trail文件中可打印的内容分析

[oracle@rhel6_lhr gg11]$ cd dirdat

[oracle@rhel6_lhr dirdat]$ ll

total 4

-rw-rw-rw- 1 oracle oinstall 1320 Jun 10 01:57 pa000000

[oracle@rhel6_lhr dirdat]$ strings pa000000

uri:orcltest::u01:gg115

uri:orcltest::u01:gg116

./dirdat/pa0000007

Linux1

orcltest2

2.6.32-431.el6.x86_643

##1 SMP Sun Nov 10 22:19:54 EST 20134

x86_642

OGG12

ogg13

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

PL/SQL Release 11.2.0.3.0 - Production

CORE    11.2.0.3.0      Production

TNS for Linux: Version 11.2.0.3.0 - Production

NLSRTL Version 11.2.0.3.0 - Production

11.2.0.3.09

EORA_HR1

?Version 11.2.1.0.1 OGGCORE_11.2.1.0.1_PLATFORMS_120423.0230_FBO4

HR.T1

ogg_test

76835

1900-01-01:00:00:00

1900-01-01:00:00:00

AAASwiAAEAAAAbsAAA

11017306

1.18.628Z

[oracle@rhel6_lhr dirdat]$

 

 

 

三、 删除测试

[oracle@orcltest ~]$ sqlplus hr/hr@ogg1

 

SQL*Plus: Release 11.2.0.3.0 Production on Wed Jun 10 16:09:55 2015

 

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> delete from t1 where object_id > 1000;

 

74206 rows deleted.

 

SQL> commit;

 

Commit complete.

 

SQL> select max(object_id) from t1;

 

MAX(OBJECT_ID)

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

          1000

 

SQL> conn hr/hr@ogg2

Connected.

SQL> select max(object_id) from t1; 

 

MAX(OBJECT_ID)

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

          1000

 

SQL>

以上的配置只针对DML的同步。

 

 

一.3.6  实验总结

 

本次实验内容较多,需要读者非常仔细的研读理解,若有疑问,可给我留言。