【OGG】OGG的单向DML复制配置(一)
【OGG】OGG的单向DML复制配置(一)
一.1 BLOG文档结构图
一.2 前言部分
一.2.1 导读
各位技术爱好者，看完本文后，你可以掌握如下的技能，也可以学到一些其它你所不知道的知识，~O(∩_∩)O~：
① OGG的单向DML实时复制功能
② 数据库的静默安装
③ OGG的安装
④ OGG下数据的初始化工作
注意：本篇BLOG中代码部分需要特别关注的地方我都用黄色背景和红色字体来表示，比如下边的例子中，thread 1的最大归档日志号为33，thread 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 相关参考文章链接
【OGG】OGG的下载和安装篇：http://blog.itpub.net/26736162/viewspace-1693241/
一.2.4 本文简介
本文基于OGG的DML复制功能，后续会推出系列的OGG配置，包括DDL复制，双向复制，还有rac到单实例的复制等等，主要参考网址为：http://ylw6006.blog.51cto.com/all/470441/16 ，非常感谢斩月大师。
一.3 实验部分
一.3.1 实验目标
配置2台服务器，搭建OGG,实现hr用户下的数据dml复制功能。
一.3.2 准备工作，在source和target端都配置
一.3.2.1 确保/etc/hosts配置正确
首先在source和target端确保/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 配置环境变量、listener和tnsnames.ora文件
该步骤确保ORACLE和OGG的环境变量正确，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/:$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=$ORACLE_HOME/bin:$PATH
export CLASSPATH=$ORACLE_HOME/JRE:$ORACLE_HOME/jlib:$ORACLE_HOME/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 $ORACLE_BASE/diag/rdbms/$ORACLE_SID/$ORACLE_SID/trace/alert_$ORACLE_SID.log'
export OGG_HOME=/u01/gg11
export PATH=$OGG_HOME:$ORACLE_HOME/bin:$PATH
export LD_LIBRARY_PATH=$OGG_HOME:$ORACLE_HOME/lib:$LD_LIBRARYPATH
alias ggsci='rlwrap ggsci'
export NLS_DATE_FORMAT='YYYY-MM-DD HH24:mi:ss'
[oracle@orcltest ~]$
[oracle@orcltest ~]$ more $TNS_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,但是建议打开这个功能，然后在source和target端都创建用于复制的数据库账号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管理进程，source和target端做相同的操作
以下操作在source和target端都需要做：
一.3.3.1 安装OGG软件
http://blog.itpub.net/26736162/viewspace-1693241/
一.3.3.2 配置及启动mgr管理进程
source和target端均操作：
[oracle@orcltest gg11]$ cd $OGG_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) 3> info 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) 4> info 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 信息，表示数据已经初始化完成，insert了75203条数据。
一.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) 16> dblogin 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;
备注：
在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;
二、 抽取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 实验总结
本次实验内容较多，需要读者非常仔细的研读理解，若有疑问，可给我留言。
一.4 About Me
...........................................................................................................................................................................................
本文作者：小麦苗，只专注于数据库的技术，更注重技术的运用
ITPUB BLOG：http://blog.itpub.net/26736162
本文地址：http://blog.itpub.net/26736162/viewspace-1696020/
本文pdf版：http://yunpan.cn/QCwUAI9bn7g7w 提取码：af2d
QQ：642808185 若加QQ请注明你所正在读的文章标题
创作时间地点：2015-06-10 09:00~ 2015-06-10 19:00 于外汇交易中心
<版权所有，文章允许转载，但须以链接方式注明源地址，否则追究法律责任!>
...........................................................................................................................................................................................
|
............................................................................................ ● 小麦苗的微店：https://weidian.com/s/793741433?wfr=c&ifr=shopdetail ............................................................................................ 使用微信客户端扫描下面的二维码来关注小麦苗的微信公众号（DB宝），学习最实用的数据库技术。
|
