本文中將演示下使用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斬月部落格” 進行整理。

Oracle 11g GoldenGate單向複製配置（DML）

相關文章