Oracle goldengate 11g (一)【DML单向复制】
for live reporting (one to one)
整体实验如图所释:
环境:
数据库版本:11.2.0.1
Oracle goldengate版本:11.2.1.0.1
Oracle goldengate软件下载地址
http://www.oracle.com/technetwork/middleware/goldengate/downloads/index.html
Oracle goldengate文档下载地址
http://www.oracle.com/technetwork/middleware/goldengate/documentation/index.html
安装整体思路
一:Oracle 数据库准备
二:Oracle goldengate系统准备
具体实施步骤
一:Oracle 数据库准备
1、 开启归档模式(单向复制仅source开启,双向复制需要source、target同时开启)
2、 开启supplemental log(单向复制仅source开启,双向复制需要source、target同时开启)
3、 Source、target建立oracle goldengate所需用户,权限
1、 开启归档模式(sysdba)
Select name,log_mode from v$database; (--archive log list; 也可以查看是否归档--)
Shutdown immediate;
Startup mount;
Alter database archivelog;
Alter database open;
Select name,log_mode from v$database;
--LOG_MODE===>ARCHIVELOG(确认已经开启归档)
2、 开启supplemental log
Select supplemental_log_data_min from v$database;
Alter database add supplemental log data;
Select supplemental_log_data_min from v$database;
--SUPPLEME===>YES(确认已开启supplemental log)
3、Source、target建立oracle goldengate所需用户,权限
3-1、source database
Create tablespace ogg datafile ‘/opt/ogg.dbf’ size 500m autoextend on;
Create temporary tablespace ogg_temp tempfile ‘/opt/ogg_temp.dbf’ size 1g;
Create user ogg identified by oracle default tablespace ogg temporary tablespace ogg_temp;
Grant connect,resource,unlimited tablespace to ogg;
Grant alter any table to ogg;
Grant create session to ogg;
Grant flashback any table to ogg;
Grant execute on dbms_flashback to ogg;
Grant execute on utl_file to ogg;
Grant select any dictionary to ogg;
Grant select any table to ogg;
3-2、target database
Create tablespace ogg datafile ‘/opt/ogg.dbf’ size 500m autoextend on;
Create temporary tablespace ogg_temp tempfile ‘/opt/ogg_temp.dbf’ size 1g;
Create user ogg identified by oracle default tablespace ogg temporary tablespace ogg_temp;
Grant connect,resource,unlimited tablespace to ogg;
Grant execute on utl_file to ogg;
Grant insert any table to ogg;
Grant delete any table to ogg;
Grant update any table to ogg;
grant dba to ogg;
二:Oracle goldengate系统准备
1、 添加安装用户oracle的环境变量(注意安装目录权限)
1-1、source database
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/opt/ogg
1-2、target database
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/u01/ogg
2、 source,target均解压软件
unzip ogg112101_fbo_ggs_Linux_x86_ora11g_32bit.zip
tar -xvof fbo_ggs_Linux_x86_ora11g_32bit.tar
3、配置时间同步
三:Oracle goldengate安装
1、 创建subdirs ;source、target 都要建立
--(注意使用ggsci命令需在ogg HOME目录下执行)
cd /opt/ogg/
[oracle@doudou-NAS ogg]$ ggsci
Oracle GoldenGate Command Interpreter for Oracle
Version 11.2.1.0.1 OGGCORE_11.2.1.0.1_PLATFORMS_120423.0230_FBO
Linux, x86, 32bit (optimized), Oracle 11g on Apr 23 2012 08:09:25
Copyright (C) 1995, 2012, Oracle and/or its affiliates. All rights reserved.
GGSCI (doudou-NAS) 1> create subdirs
Creating subdirectories under current directory /opt/ogg
Parameter files /opt/ogg/dirprm: already exists
Report files /opt/ogg/dirrpt: created
Checkpoint files /opt/ogg/dirchk: created
Process status files /opt/ogg/dirpcs: created
SQL script files /opt/ogg/dirsql: created
Database definitions files /opt/ogg/dirdef: created
Extract data files /opt/ogg/dirdat: created
Temporary files /opt/ogg/dirtmp: created
Stdout files /opt/ogg/dirout: created
2、 Crete the manager parameter file
2-1、source database
GGSCI (doudou-NAS) 2> edit params mgr
GGSCI (doudou-NAS) 3> view params mgr --使用view params查看manager参数
port 7809
dynamicportlist 7810-7900
autorestart extract *,retries 5 ,waitminutes 2
GGSCI (doudou-NAS) 31> start manager
Manager started.
GGSCI (doudou-NAS) 25> view report mgr --使用view report 查看manager是否配置正确
***********************************************************************
Oracle GoldenGate Manager for Oracle
Version 11.2.1.0.1 OGGCORE_11.2.1.0.1_PLATFORMS_120423.0230
Linux, x86, 32bit (optimized), Oracle 11g on Apr 23 2012 04:23:29
Copyright (C) 1995, 2012, Oracle and/or its affiliates. All rights reserved.
Starting at 2013-06-18 14:24:58
***********************************************************************
Operating System Version:
Linux
Version #1 SMP Fri Jan 27 17:21:15 EST 2012, Release 2.6.18-308.el5
Node: doudou-NAS
Machine: i686
soft limit hard limit
Address Space Size : unlimited unlimited
Heap Size : unlimited unlimited
File Size : unlimited unlimited
CPU Time : unlimited unlimited
Process id: 10966
Parameters...
port 7809
dynamicportlist 7810-7900
autorestart extract *,retries 5 ,waitminutes 2
***********************************************************************
** Run Time Messages **
***********************************************************************
2013-06-18 14:24:58 INFO OGG-00983 Manager started (port 7809)
GGSCI (doudou-NAS) 27> info mgr status --使用info 命令查看manager当前状态
Manager is running (IP port doudou-NAS.7809).
2-2、target database
GGSCI (localhost.localdomain) 9> edit params mgr
port 7809
dynamicportlist 7810-7900
autostart er *
autorestart extract *,waitminutes 2,retries 5
lagreporthours 1
laginfominutes 3
lagcriticalminutes 5
purgeoldextracts /u01/ogg/dirdat/tt* ,usecheckpoints ,minkeephours 5
GGSCI (localhost.localdomain) 11> start mgr
Manager started.
3、Configure the primary extract group
GGSCI (doudou-NAS) 61> add extract ext1,tranlog ,begin now
EXTRACT added.
GGSCI (doudou-NAS) 68> add exttrail /opt/ogg/dirdat/tt,extract ext1
EXTTRAIL added.
GGSCI (doudou-NAS) 71> view params ext1
userid ogg,password oracle
exttrail /opt/ogg/dirdat/tt
TRANLOGOPTIONS asmuser sys@asm,asmpassword oracle
--使用ASM存储redo log 或archive log 需要添加此行,如果没有此行会影响extract工作,注意配置静态静态和asm字符串--
table doudou.*;
4、configure the data pump on the source
GGSCI (doudou-NAS) 73> add extract pump1 ,exttrailsource /opt/ogg/dirdat/tt ,begin now
EXTRACT altered.
GGSCI (doudou-NAS) 74> add rmttrail /u01/ogg/dirdat/tt ,extract pump1
RMTTRAIL added.
GGSCI (doudou-NAS) 135> view params pump1
extract pump1
userid ogg,password oracle
rmthost 192.168.1.219,mgrport 7809
rmttrail /u01/ogg/dirdat/tt
table doudou.*;
5、configure the replicat group
5-1、configure checkpointtable
GGSCI (localhost.localdomain) 13> edit params ./GLOBALS
checkpointtable ogg.checkpoint
GGSCI (localhost.localdomain) 1> dblogin userid ogg,password oracle
Successfully logged into database.
GGSCI (localhost.localdomain) 2> add checkpointtable ogg.checkpoint
Successfully created checkpoint table ogg.checkpoint.
5-2、replicat group
GGSCI (localhost.localdomain) 3> add replicat rep1 ,exttrail /u01/ogg/dirdat/tt ,begin now
REPLICAT added.
GGSCI (localhost.localdomain) 9> view params rep1
replicat rep1
userid ogg ,password oracle
assumetargetdefs
map doudou.*,target doudou.*;
使用expdp,impdp 初始化数据
SQL>select current_scn from v$database ;
CURRENT_SCN
-----------
1379205
[oracle@doudou-NAS ~]$expdp doudou/oracle@doudou directory=dump dumpfile=doudou.dmp schemas=doudou flashback_scn=1379205
GGSCI (localhost.localdomain) 3>start replicat rep1, aftercsn 1379205
5-3、开启GG同步
Targar database
GGSCI (localhost.localdomain) 1> dblogin userid ogg,password oracle
Successfully logged into database.
GGSCI (localhost.localdomain) 2> add trandata doudou.*
2013-06-18 17:17:59 WARNING OGG-00869 No unique key is defined for table ‘DOUDOU’. All viable columns will be used to represent the key, but may not guarantee uniqueness. KEYCOLS may be used to define the key.
Logging of supplemental redo data enabled for table DOUDOU.DOUDOU.
2013-06-18 17:17:59 WARNING OGG-00869 No unique key is defined for table ‘TEST’. All viable columns will be used to represent the key, but may not guarantee uniqueness. KEYCOLS may be used to define the key.
Logging of supplemental redo data enabled for table DOUDOU.TEST.
四:Oracle goldengate 测试
Source database
SQL> desc test;
Name Null? Type
----------------------------------------- -------- ----------------------------
NAME VARCHAR2(200)
ID NUMBER
SQL> select count(*) from test;
COUNT(*)
----------
0
SQL> insert into test values (‘兜兜’,1);
1 row created.
SQL> commit;
Commit complete.
--commit 开始计时
Target database
SQL> select * from test;
no rows selected
SQL> /
no rows selected
SQL> /
no rows selected
SQL> /
no rows selected
SQL> /
no rows selected
SQL> /
NAME
--------------------------------------------------------------------------------
ID
----------
兜兜
1
--约5秒后,数据同步过来
总结:
Oracle goldengate DML 单向复制成功。
Oracle goldengate 一条数据同步应用就用了5秒,看来oracle goldengate优化会有更有研究深度。期待……