首页 > 代码库 > 【Oracle】OGG单向复制配置
【Oracle】OGG单向复制配置
实验环境:
源端:
Ip:192.168.40.10
DataBase:Oracle 11.2.0.1.0 ORCL
OS:OEL5.6
OGG:fbo_ggs_Linux_x86_ora11g_32bit
目标端:
Ip:192.168.40.128
DataBase:10.2.0.1.0 ORCL
OS:OEL5.6
OGG:fbo_ggs_Linux_x86_ora11g_32bit
首先配置好两台机子上的环境变量,在其中增加以下内容并使之生效:
export GGATE=$ORACLE_BASE/ogg
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib:$GGATE
并且均建立该路径:
mkdir $ORACLE_BASE/ogg
源端配置:
[oracle@badly9 ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Sat Jun 7 17:23:51 2014
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SYS@ORCL>archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 5
Next log sequence to archive 7
Current log sequence 7
SYS@ORCL>select supplemental_log_data_min from v$database;
SUPPLEME
--------
NO
SYS@ORCL>alter database add supplemental log data;
Database altered.
SYS@ORCL>select supplemental_log_data_min from v$database;
SUPPLEME
--------
YES
SYS@ORCL>alter database force logging;
Database altered.
SYS@ORCL>create tablespace tbs_ogg
2 datafile ‘/u01/app/oracle/oradata/ORCL/datafile/tbs_ogg01.dbf‘ size 200M
3 autoextend on;
Tablespace created.
SYS@ORCL>create user ogg identified by ogg default tablespace tbs_ogg;
User created.
SYS@ORCL>grant connect,resource,dba to ogg;
Grant succeeded.
SYS@ORCL>grant execute on utl_file to ogg;
Grant succeeded.
SYS@ORCL>grant select any dictionary,select any table to ogg;
Grant succeeded.
SYS@ORCL>grant alter any table to ogg;
Grant succeeded.
SYS@ORCL>grant flashback any table to ogg;
Grant succeeded.
SYS@ORCL>grant execute on DBMS_FLASHBACK to ogg;
Grant succeeded.
目标端:
SYS@ORCL>create tablespace tbs_ogg
2 datafile ‘/u01/app/oracle/oradata/ORCL/tbs_ogg01.dbf‘ size 200M
3 autoextend on;
Tablespace created.
SYS@ORCL>create user ogg identified by ogg default tablespace tbs_ogg;
User created.
SYS@ORCL>grant connect,resource,dba to ogg;
Grant succeeded.
SYS@ORCL>grant execute on utl_file to ogg;
Grant succeeded.
SYS@ORCL>grant insert any table to ogg;
Grant succeeded.
SYS@ORCL>grant delete any table to ogg;
Grant succeeded.
SYS@ORCL>grant update any table to ogg;
Grant succeeded.
源端和目标端
[oracle@badly9 ~]$ tar -xvf fbo_ggs_Linux_x86_ora11g_32bit.tar
UserExitExamples/
UserExitExamples/ExitDemo_passthru/
UserExitExamples/ExitDemo_passthru/Makefile_passthru.AIX
。。。略。。。
[oracle@badly9 ~]$ ./ggsci
源端:
GGSCI (badly9) 1> create subdirs
Creating subdirectories under current directory /u01/app/oracle/ogg
Parameter files /u01/app/oracle/ogg/dirprm: already exists
Report files /u01/app/oracle/ogg/dirrpt: created
Checkpoint files /u01/app/oracle/ogg/dirchk: created
Process status files /u01/app/oracle/ogg/dirpcs: created
SQL script files /u01/app/oracle/ogg/dirsql: created
Database definitions files /u01/app/oracle/ogg/dirdef: created
Extract data files /u01/app/oracle/ogg/dirdat: created
Temporary files /u01/app/oracle/ogg/dirtmp: created
Stdout files /u01/app/oracle/ogg/dirout: created
GGSCI (badly9) 2> edit params mgr
GGSCI (badly9) 3> view params mgr
port 7809
dynamicportlist 7800-8000
autorestart extract *,waitminutes 2,resetminutes 5
GGSCI (badly9) 4> start mgr
Manager started.
GGSCI (badly9) 5> edit params eora
GGSCI (badly9) 6> view params eora
extract eora
dynamicresolution
userid ogg,password ogg
exttrail /u01/app/oracle/ogg/dirdat/et
table scott.*;
GGSCI (badly9) 7>dblogin userid ogg, password ogg
Successfully logged into database.
GGSCI (badly9) 8>add trandata scott.*
GGSCI (badly9) 9> add extract eora,tranlog,begin now
EXTRACT added.
GGSCI (badly9) 10> add exttrail /u01/app/oracle/ogg/dirdat/et,extract eora
EXTTRAIL added.
GGSCI (badly9) 11> start extract eora
Sending START request to MANAGER ...
EXTRACT EORA starting
GGSCI (badly9) 12> edit params pump_so
GGSCI (badly9) 13> view params pump_so
extract pump_so
dynamicresolution
passthru
rmthost 192.168.40.128,mgrport 7809,compress
rmttrail /u01/app/oracle/ogg/dirdat/pt
table scott.*;
GGSCI (badly9) 14> add extract pump_so,exttrailsource /u01/app/oracle/ogg/dirdat/et
EXTRACT added.
GGSCI (badly9) 15> add rmttrail /u01/app/oracle/ogg/dirdat/pt,extract pump_so
RMTTRAIL added.
GGSCI (badly9) 16> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING EORA 00:00:00 00:00:02
EXTRACT RUNNING PUMP_SO 00:00:00 00:00:07
配置目标端进程:
因为目标端使用的是oracle 10g,启动ggsci的时候报错:
[oracle@jp ogg]$ ggsci
ggsci: error while loading shared libraries: libnnz11.so: cannot open shared object file: No such file or directory
增加软链接:
ln -s /u01/app/oracle/product/10.2.0/db_1/lib/libnnz10.so libnnz11.so
启动再次报错:
[oracle@jp ogg]$ ggsci
ggsci: error while loading shared libraries: libclntsh.so.11.1: cannot open shared object file: No such file or directory
再增加一个软链接:
ln -s /u01/app/oracle/product/10.2.0/db_1/lib/libclntsh.so.10.1 libclntsh.so.11.1
再次启动正常:
[oracle@jp 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 (jp) 1> create subdirs
Creating subdirectories under current directory /u01/app/oracle/ogg
Parameter files /u01/app/oracle/ogg/dirprm: already exists
Report files /u01/app/oracle/ogg/dirrpt: created
Checkpoint files /u01/app/oracle/ogg/dirchk: created
Process status files /u01/app/oracle/ogg/dirpcs: created
SQL script files /u01/app/oracle/ogg/dirsql: created
Database definitions files /u01/app/oracle/ogg/dirdef: created
Extract data files /u01/app/oracle/ogg/dirdat: created
Temporary files /u01/app/oracle/ogg/dirtmp: created
Stdout files /u01/app/oracle/ogg/dirout: created
GGSCI (jp) 2> edit params mgr
GGSCI (jp) 3> view params mgr
port 7809
dynamicportlist 7800-8000
autostart er *
autorestart extract *,waitminutes 2,retries 5
lagreporthours 1
laginfominutes 3
lagcriticalminutes 5
purgeoldextracts /u01/app/oracle/ogg/dirdat/rt*,usecheckpoints,minkeepdays 3
GGSCI (jp) 4> start mgr
Manager started.
GGSCI (jp) 5> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
GGSCI (jp) 12> edit params rep2
GGSCI (jp) 13> view params rep2
replicat rep2
userid ogg,password ogg
assumetargetdefs
reperror default,discard
discardfile ./dirrpt/rep1.dsc,append,megabytes 50
dynamicresolution
--map ogg.test,target ogg.test;
map scott.*, target scott.*;
GGSCI (jp) 9> dblogin userid ogg,password ogg;
Successfully logged into database.
GGSCI (jp) 14> add checkpointtable ogg.checkpoint
Successfully created checkpoint table ogg.checkpoint.
GGSCI (jp) 16> add replicat rep2,exttrail /u01/app/oracle/ogg/dirdat/pt,checkpointtable ogg.checkpoint
REPLICAT added.
GGSCI (jp) 17> start rep2
Sending START request to MANAGER ...
REPLICAT REP2 starting
GGSCI (jp) 18> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
REPLICAT RUNNING REP2 00:00:00 00:00:00
DML测试
源端:
SCOTT@ORCL>insert into dept values(33,‘TEST‘,‘TEST1‘);
1 row created.
SCOTT@ORCL>commit;
Commit complete.
目标端:
SCOTT@ORCL>select * from dept;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
33 TEST TEST1
测试成功。
注:实验中源端和目标端都已存在scott用户及相应表,所以没有数据初始化过程。