本文中将演示下使用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

export ORACLE_SID=db1

export ORACLE_BASE=/u01/app/oracle

export ORACLE_HOME=$ORACLE_BASE/product/10.2.0/db1

export PATH=$ORACLE_HOME/bin:$PATH

export NLS_LANG=AMERICAN_AMERICA.AL32UTF8

export NLS_DATE_FORMAT='yyyy-mm-dd-hh24:mi:ss'

export EDITOR=vim

export TNS_ADMIN=$ORACLE_HOME/network/admin

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

export GGATE=$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> create 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

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 unlimited

Process 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

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

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

注：本文根据“51CTO斩月博客” 进行整理。

ITPub博客

Oracle 11g GoldenGate单向复制配置（DML）

最新文章