本文中将演示下使用ogg在两台oracle 10g数据库服务器间实现单向复制的配置！
一：环境介绍
db1：source端
ip地址：192.168.123.10
数据库版本：10.2.0.1 64 bit
操作系统版本：centos 5.4 64 bit
ogg版本：fbo_ggs_Linux_x64_ora10g_64bit.tar
db2: target端
ip地址：192.168.123.20
数据库版本：10.2.0.1 64 bit
操作系统版本：centos 5.4 64 bit
ogg版本：fbo_ggs_Linux_x64_ora10g_64bit.tar
二：准备工作，在source和target端都配置
1：配置环境变量和tnsnames.ora文件
[oracle@db1 ~]$ tail .bash_profile
exportORACLE_SID=db1
exportORACLE_BASE=/u01/app/oracle
exportORACLE_HOME=$ORACLE_BASE/product/10.2.0/db1
exportPATH=$ORACLE_HOME/bin:$PATH
exportNLS_LANG=AMERICAN_AMERICA.AL32UTF8
exportNLS_DATE_FORMAT='yyyy-mm-dd-hh24:mi:ss'
exportEDITOR=vim
exportTNS_ADMIN=$ORACLE_HOME/network/admin
exportLD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib
exportGGATE=$ORACLE_BASE/ogg
[oracle@db1 ~]$ source .bash_profile
[oracle@db1 ~]$ cat $TNS_ADMIN/tnsnames.ora
DB1=
(DESCRIPTION=
(ADDRESS= (PROTOCOL=TCP)(HOST=db1)(PORT=1521))
(CONNECT_DATA=
(SERVER=DEDICATED)
(SERVICE_NAME=db1)
)
)
DB2=
(DESCRIPTION=
(ADDRESS= (PROTOCOL=TCP)(HOST=db2)(PORT=1521))
(CONNECT_DATA=
(SERVER=DEDICATED)
(SERVICE_NAME=db2)
)
)
2：确定数据库运行在归档模式，开启数据库附加日志，打开force logging,创建用于复制的数据库账号ogg，为了方便，这里直接赋予dba权限，如果对数据库安全要求高，可以去查询ogg文档，赋予复制需要的最小权限！(source和target端做相同的操作)
[oracle@db1 ~]$ sqlplus /nolog
SQL>conn /as sysdba
Connected.
SQL>archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 1
Next log sequence to archive 2
Current log sequence 2
SQL>select supplemental_log_data_min from v$database;
SUPPLEME
--------
NO
SQL>alter database add supplemental log data;
Database altered.
SQL>select supplemental_log_data_min from v$database;
SUPPLEME
--------
YES
SQL>alter database force logging;
Database altered.
SQL>create tablespace tbs_ogg;
Tablespace created.
SQL>create user ogg identified by ogg default tablespace tbs_ogg temporary tablespace temp account unlock;
User created.
SQL>grant connect,resource,dba to ogg;
Grant succeeded
备注： 在oracle中可以通过rowid来定位某条记录，但是目标端的数据库和源端数据库的数据库可能完全不一样，所以无法通过rowid来确定源端数据库的逻辑变化，这时附加日志supplemental log便登上了表演的舞台。数据库在开启附加日志功能后，对于源端的修改操作，oracle会同时追加能够唯一标示记录的列到redo log。这样目标端数据库就可以知道源端发生了哪些具体的变化。
三：安装ogg软件，启动mgr管理进程，source和target端做相同的操作
[oracle@db1 ~]$ mkdir $GGATE
[oracle@db1 ~]$ cd $GGATE
[oracle@db1 ogg]$ tar -xvf /home/oracle/fbo_ggs_Linux_x64_ora10g_64bit.tar
[oracle@db1 ogg]$ ./ggsci
GGSCI (db1) 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 (db1) 2>edit params mgr
GGSCI (db1) 3>view params mgr
PORT 7809
GGSCI (db1) 4>start mgr
Manager started.
GGSCI (db1) 5>info mgr
Manager is running (IP port db1.7809).
四：准备测试用户和表
SQL>conn /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>conn hr/hr
Connected.
SQL>create table t1 as select * from dba_objects;
Table created.
SQL>alter table t1 add constraint pk_t1 primary key(object_id);
Table altered.
SQL>select count(*) from t1; //source端
COUNT(*)
----------
50315
SQL>select count(*) from t1; //target端，只复制表定义，不填充数据
COUNT(*)
----------
0
五:初始化加载数据,在异构数据库平台(例如oracle-mysql)，这个功能显得非常的有用！而在oracle-oracle的数据复制条件下，oracle推荐使用expdp/impdp工具
1.source端添加extract进程
GGSCI (db1) 1>add extract einig1,sourceistable //sourceistable代表直接从表中读取数据
EXTRACT added.
GGSCI (db1) 2>edit params einig1 //einig1代表extract initial load group 1缩写
GGSCI (db1) 3>view params einig1
extract einig1
setenv (NLS_LANG=AMERICAN_AMERICA.AL32UTF8)
userid ogg,password ogg
rmthost 192.168.123.20,mgrport 7809
rmttask replicat,group rinig1
table hr.t1;
2.target端添加replicat进程
GGSCI (db2) 1>add replicat rinig1,specialrun //specialrun代表只运行一次
REPLICAT added.
GGSCI (db2) 2>edit params rinig1 //rinig1代表replicat initial load group 1缩写
GGSCI (db2) 3>view params rinig1 //rinig1的名字必须同source端定义的group名字相同
replicat rinig1
setenv (NLS_LANG=AMERICAN_AMERICA.AL32UTF8)
assumetargetdefs
userid ogg,password ogg
discardfile ./dirrpt/rinig1.dsc,purge
map hr.*,target hr.*;
3.source端启动extract进程，查看日志输出
GGSCI (db1) 4>start extract einig1
Sending START request to MANAGER ...
EXTRACT EINIG1 starting
GGSCI (db1) 5> view report einig1
2012-06-20 09:40:55 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 10g on Apr 23 2012 07:44:10
Copyright (C) 1995, 2012, Oracle and/or its affiliates. All rights reserved.
Starting at 2012-06-20 09:40:55
***********************************************************************
Operating System Version:
Linux
Version #1 SMP Thu Sep 3 03:28:30 EDT 2009, Release 2.6.18-164.el5
Node: db1
Machine: x86_64
soft limit hard limit
Address Space Size : unlimited unlimited
Heap Size : unlimited unlimited
File Size : unlimited unlimited
CPU Time : unlimited unlimitedProcess id: 26185
Description:
***********************************************************************
** Running with the following parameters **
***********************************************************************
2012-06-20 09:40:55 INFO OGG-03035 Operating system character set identified as UTF-8. Locale: en_US, LC_ALL:
.
extract einig1
setenv (NLS_LANG=AMERICAN_AMERICA.AL32UTF8)
Set environment variable (NLS_LANG=AMERICAN_AMERICA.AL32UTF8)
userid ogg,password ***
rmthost 192.168.123.20,mgrport 7809
rmttask replicat,group rinig1
table hr.t1;
Using the following key columns for source table HR.T1: OBJECT_ID.
2012-06-20 09:40:59 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/app/oracle/ogg/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 10g Enterprise Edition Release 10.2.0.1.0 - 64bi
PL/SQL Release 10.2.0.1.0 - Production
CORE 10.2.0.1.0 Production
TNS for Linux: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production
Database Language and Character Set:
NLS_LANG = "AMERICAN_AMERICA.AL32UTF8"
NLS_LANGUAGE = "AMERICAN"
NLS_TERRITORY = "AMERICA"
NLS_CHARACTERSET = "AL32UTF8"
Processing table HR.T1
***********************************************************************
* ** Run Time Statistics ** *
***********************************************************************
Report at 2012-06-20 09:42:26 (activity since 2012-06-20 09:40:59)
Output to rinig1:
From Table HR.T1:
# inserts: 50315
# updates: 0
# deletes: 0
# discards: 0
REDO Log Statistics
Bytes parsed 0
Bytes output 13168227
4：target端验证
[oracle@db2 ogg]$ sqlplus hr/hr
SQL>select count(*) from t1;
COUNT(*)
----------
50315
六：配置db1,db2间的实时同步复制
1：在source上配置extract进程，进程的名字不能超过8个字符
GGSCI (db1) 1>edit params eora_t1
GGSCI (db1) 2>view params eora_t1
extract eora_t1
setenv (NLS_LANG=AMERICAN_AMERICA.AL32UTF8)
userid ogg,password ogg
exttrail ./dirdat/aa
table hr.*;
2：开启hr用户下所有表的附加日志
GGSCI (db1) 3>dblogin userid ogg, password ogg
Successfully logged into database.
GGSCI (db1) 4>add trandata hr.*
3：添加extract进程，添加trail文件，文件名前缀不能超过2个字符
GGSCI (db1) 5>add extract eora_t1,tranlog,begin now
EXTRACT added.
GGSCI (db1) 6>add exttrail ./dirdat/aa,extract eora_t1,megabytes 100 //添加trail文件
EXTTRAIL added.
GGSCI (db1) 7>start extract eora_t1
Sending START request to MANAGER ...
EXTRACT EORA_T1 starting
GGSCI (db1) 8>info extract eora_t1
EXTRACT EORA_T1 Last Started 2012-06-20 10:06 Status RUNNING
Checkpoint Lag 00:00:00 (updated 00:00:02 ago)
Log Read Checkpoint Oracle Redo Logs
2012-06-20 10:06:36 Seqno 3, RBA 21804544
SCN 0.562134 (562134)
GGSCI (db1) 9>info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING EORA_T1 00:08:24 00:00:05
4：添加pump进程
GGSCI (db1) 10>edit params pora_t1
GGSCI (db1) 11>view params pora_t1
extract pora_t1
setenv (NLS_LANG=AMERICAN_AMERICA.AL32UTF8)
passthru
rmthost 192.168.123.20,mgrport 7809
rmttrail ./dirdat/pa
table hr.*;
GGSCI (db1) 12>add extract pora_t1,exttrailsource ./dirdat/aa //这里aa文件名同前面extract进程参数文件中定义的trail文件名一
致
EXTRACT added.
GGSCI (db1) 13>add rmttrail ./dirdat/pa,extract pora_t1,megabytes 100 //添加传输到target数据库的trail问文件名，应该同参数文
件中描述的一致
RMTTRAIL added.
GGSCI (db1) 14>start extract pora_t1
Sending START request to MANAGER ...
EXTRACT PORA_T1 starting
GGSCI (db1) 15>info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING EORA_T1 00:00:00 00:00:02
EXTRACT RUNNING PORA_T1 00:00:00 00:00:22
4：在target端添加检查表，配置replicat进程
GGSCI (db2) 1>edit params ./GLOBALS
GGSCI (db2) 2>view params ./GLOBALS
checkpointtable ogg.ggschkpt
GGSCI (db2) 3>exit //这里需要退出ggsci终端
[oracle@db2 ~]$ sqlplus ogg/ogg
SQL>select tname from tab;
no rows selected
[oracle@db2 ogg]$ ggsci
GGSCI (db2) 1>dblogin userid ogg,password ogg
Successfully logged into database.
GGSCI (db2) 2>add checkpointtable
No checkpoint table specified, using GLOBALS specification (ogg.ggschkpt)...
Successfully created checkpoint table ogg.ggschkpt.
SQL>select tname from tab;
TNAME
------------------------------
GGSCHKPT
GGSCHKPT_LOX
GGSCI (db2) 3>edit params rora_t1
GGSCI (db2) 4>view params rora_t1
replicat rora_t1
setenv (NLS_LANG=AMERICAN_AMERICA.AL32UTF8)
userid ogg,password ogg
handlecollisions
assumetargetdefs
discardfile ./dirrpt/rora_t1.dsc,purge
map hr.* ,target hr.*;
GGSCI (db2) 5>add replicat rora_t1,exttrail ./dirdat/pa
REPLICAT added.
GGSCI (db2) 6>start replicat rora_t1
Sending START request to MANAGER ...
REPLICAT RORA_T1 starting
GGSCI (db2) 7>info replicat rora_t1
REPLICAT RORA_T1 Last Started 2012-06-20 10:21 Status RUNNING
Checkpoint Lag 00:00:00 (updated 00:00:03 ago)
Log Read Checkpoint File ./dirdat/t1000000
First Record RBA 0
GGSCI (db2) 8>info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
REPLICAT RUNNING RORA_T1 00:00:00 00:00:05
备注：
在target端配置replicat进程之前，通常需要在目标端的数据库中创建一个checkpoint表，这个表是基于ogg checkpoint文件的，它记录了所有ogg可恢复的checkpoint以及sequence，这个操作不是必须的，但oracle强烈建议使用它，因为它可以使得checkpoint包含在replicat的事务中，保证了可以从各类失败场景中恢复！
七：测试同步
1：插入数据
[oracle@db1 ogg]$ sqlplus hr/hr
SQL>desc t1
Name Null? Type
----------------------------------------- -------- ----------------------------
OWNER VARCHAR2(30)
OBJECT_NAME VARCHAR2(128)
SUBOBJECT_NAME VARCHAR2(30)
OBJECT_ID NOT NULL NUMBER
DATA_OBJECT_ID NUMBER
OBJECT_TYPE VARCHAR2(19)
CREATED DATE
LAST_DDL_TIME DATE
TIMESTAMP VARCHAR2(19)
STATUS VARCHAR2(7)
TEMPORARY VARCHAR2(1)
GENERATED VARCHAR2(1)
SECONDARY VARCHAR2(1)
SQL>select max(object_id) from t1;
MAX(OBJECT_ID)
--------------
52504
SQL>insert into t1 (object_id,object_name) values (52505,'ogg_test');
1 row created.
SQL>commit;
Commit complete.
SQL>conn hr/hr@db2
Connected.
SQL>select max(object_id) from t1;
MAX(OBJECT_ID)
--------------
52505
2：抽取trail文件中可打印的内容分析
[root@db1 dirdat]# pwd
/u01/app/oracle/ogg/dirdat
[root@db1 dirdat]# strings aa000000
uri:db1::u01:app:oracle:ogg6
./dirdat/aa0000007
564200
Linux1
db12
2.6.18-164.el53
"#1 SMP Thu Sep 3 03:28:30 EDT 20094
x86_642
DB12
db13
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bi
PL/SQL Release 10.2.0.1.0 - Production
CORE 10.2.0.1.0 Production
TNS for Linux: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production
EORA_T11
?Version 11.2.1.0.1 OGGCORE_11.2.1.0.1_PLATFORMS_120423.0230_FBO4
HR.T1
ogg_test
52505
1900-01-01:00:00:00
1900-01-01:00:00:00
AAAM0YAAEAAAARlAAA
5642006
2.46.299Z
[root@db2 dirdat]# pwd
/u01/app/oracle/ogg/dirdat
[root@db2 dirdat]# strings pa000000
uri:db1::u01:app:oracle:ogg5
uri:db1::u01:app:oracle:ogg6
./dirdat/pa0000007
Linux1
db12
2.6.18-164.el53
"#1 SMP Thu Sep 3 03:28:30 EDT 20094
x86_642
DB12
db13
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bi
PL/SQL Release 10.2.0.1.0 - Production
CORE 10.2.0.1.0 Production
TNS for Linux: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production
EORA_T11
?Version 11.2.1.0.1 OGGCORE_11.2.1.0.1_PLATFORMS_120423.0230_FBO4
HR.T1
ogg_test
52505
1900-01-01:00:00:00
1900-01-01:00:00:00
AAAM0YAAEAAAARlAAA
5642006
2.46.299Z
3：删除测试
SQL>conn hr/hr
Connected.
SQL>delete from t1 where object_id>1000;
49362 rows deleted.
SQL>commit;
Commit complete
SQL>select max(object_id) from t1;
MAX(OBJECT_ID)
--------------
1000
SQL>conn hr/hr@db2
Connected.
SQL>select max(object_id) from t1;
MAX(OBJECT_ID)
--------------
1000
本文的配置中不涉及ddl操作的复制，这部分内容将在后续的文章中进行介绍！
扫一扫
588
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
