Linux下單機OGG同步Oracle 11g DB測試

Linux下單機OGG同步Oracle 11g DB測試

一、安裝goldengate軟體
1.測試環境：
OS: RedHat 6 64bit
DB: Oracle 11.2.0.3 64bit
查看OS和DB版本
[oracle@redhat6 ~]$ uname -a
Linux redhat6 2.6.32-131.0.15.el6.x86_64 #1 SMP Tue May 10 15:42:40 EDT 2011 x86_64 x86_64 x86_64 GNU/Linux
SQL> select * from v$version where rownum=1;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

1.2.安裝OGG
[oracle@redhat6 ~]$ cd /u01/ogg
[oracle@redhat6 ogg]$unzip ogg112101_fbo_ggs_Linux_x64_ora11g_64bit.zip
[oracle@redhat6 ogg]$ tar -xvf fbo_ggs_Linux_x64_ora11g_64bit.tar
1.3.添加環境變數
[oracle@redhat6 ogg]$ vi /home/oracle/.bash_profile
LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib
export LD_LIBRARY_PATH
export PATH=/u01/ogg:$PATH
export GGATE=/u01/ogg
1.4.使用ggsci工具建立目錄
[oracle@redhat6 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, x64, 64bit (optimized), Oracle 11g on Apr 23 2012 08:32:14

Copyright (C) 1995, 2012, Oracle and/or its affiliates. All rights reserved.

GGSCI (redhat6) 1> create subdirs
以上配置須SOURCE和TARGET端都進行
二、配置源(SOURCE)資料庫
Goldengate通過抓取源端資料庫重做日誌進行分析，將擷取的資料應用到目標端，實現資料同步。因此，來源資料庫需要必須處于歸檔模式，並啟用附加        日誌和強制日誌。
(1)歸檔模式、
SQL> archive log list;
Database log mode              Archive Mode
Automatic archival            Enabled
Archive destination            /u01/app/oracle/archlog
Oldest online log sequence    12
Next log sequence to archive  14
Current log sequence          14
SQL> select log_mode,supplemental_log_data_min,force_logging from v$database;

LOG_MODE    SUPPLEME FOR
------------ -------- ---
ARCHIVELOG  YES      NO
(2)強制日誌
SQL> alter database force logging;
Database altered.
(3)附加日誌
SQL>alter database add supplemental log data;
SQL> select log_mode,supplemental_log_data_min,force_logging from v$database;

LOG_MODE    SUPPLEME FOR
------------ -------- ---
ARCHIVELOG  YES      YES
2.2禁用RecycleBin(oracle10g ogg需要禁用，oracle11g ogg不要求)
SQL> alter system set recyclebin=off scope=spfile;
System altered.
SQL> shutdown immediate
2.3建立存放DDL資訊的使用者並授權
SQL> create user ggate identified by ggate default tablespace users temporary tablespace temp;
User created.
SQL> grant connect,resource to ggate;
Grant succeeded.
SQL> grant execute on utl_file to ggate;
Grant succeeded.
[oracle@redhat6 ~]$ cd $GGATE
[oracle@redhat6 ggate]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Mon Dec 9 04:55:44 2013
Copyright (c) 1982, 2011, Oracle.  All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> @marker_setup.sql;  ---輸入之前建立的使用者ggate
SQL> @ddl_setup.sql;    ---輸入之前建立的使用者ggate
SQL> @role_setup.sql;  ---輸入之前建立的使用者ggate
SQL>grant GGS_GGSUSER_ROLE to ggate;
SQL>@ddl_enable.sql;
三、測試OGG
3.1 分別在SOURCE DB和TARGET DB上建立測試使用者
SOURCE DB:
SQL> create user source identified by oracle default tablespace users temporary tablespace temp;
User created.
SQL> grant connect,resource,dba to source;
Grant succeeded.
TARGET DB:
SQL> create user targer identified by oracle default tablespace tbs_hjj temporary tablespace temp;
User created.
SQL> grant connect,resource,dba to targer;
Grant succeeded.
3.2 在SOURCE和TARGET分別配置MANAGER
遠端和目標端都做同樣的操作
GGSCI (redhat6) 1> info all

Program    Status      Group      Lag at Chkpt  Time Since Chkpt

MANAGER    STOPPED

GGSCI (redhat6) 2> edit params mgr

GGSCI (redhat6) 3> start mgr

Manager started.

GGSCI (redhat6) 4> info all

Program    Status      Group      Lag at Chkpt  Time Since Chkpt

MANAGER    RUNNING
3.3 配置SOURCE DB的複製隊列
串連到資料庫，測試連接：
GGSCI (redhat6) 5> dblogin userid ggate@hjj,password ggate
Successfully logged into database.
增加一個抽取extract:
GGSCI (redhat6) 6> add extract ext1,tranlog,begin now
EXTRACT added.
GGSCI (redhat6) 7> add exttrail /u01/ogg/dirdat/lt,extract ext1
EXTTRAIL added.
GGSCI (redhat6) 8> edit params ext1
extract ext1
userid ggate@hjj,password ggate
rmthost redhat6,mgrport 7809
rmttrail /u01/ogg/dirdat/lt
ddl include mapped objname source.*;
table source.*;
GGSCI (redhat6) 2> info all

Program    Status      Group      Lag at Chkpt  Time Since Chkpt

MANAGER    RUNNING
EXTRACT    RUNNING    EXT1        00:00:00      00:00:10
3.4 配置TARGET DB的同步隊列
GGSCI (redhat6) 2> edit params ./GLOBAL
GGSCHEMA ggate
CHECKPOINTTABLE ggate.checkpoint
GGSCI (redhat6) 3> dblogin userid ggate@hjj,password ggate
Successfully logged into database.
GGSCI (redhat6) 4> add checkpointtable ggate.checkpoint
Successfully created checkpoint table ggate.checkpoint.
建立同步隊列
GGSCI (redhat6) 1> add replicat rep1,exttrail /u01/ogg/dirdat/lt,checkpointtable ggate.checkpoint
REPLICAT added.
GGSCI (redhat6) 5> edit params rep1
replicat rep1
ASSUMETARGETDEFS
userid ggate@hjj,password ggate
discardfile /u01/ogg/dirdat/rep1_discard.txt,append, megabytes 10
DDL
map source.*, target targer.*;
3.5 開啟同步
(1)SOURCE端：
GGSCI (redhat6) 10> start extract ext1
Sending START request to MANAGER ...
EXTRACT EXT1 starting

GGSCI (redhat6) 11> info all

Program    Status      Group      Lag at Chkpt  Time Since Chkpt
MANAGER    RUNNING
EXTRACT    RUNNING    EXT1        00:07:38      00:00:00

(2)TARGET端：
GGSCI (redhat6) 3> start replicat rep1
Sending START request to MANAGER ...
REPLICAT REP1 starting

GGSCI (redhat6) 4> info all
Program    Status      Group      Lag at Chkpt  Time Since Chkpt

MANAGER    RUNNING
EXTRACT    RUNNING    EXT1        00:00:00      00:00:05
REPLICAT    RUNNING    REP1        00:00:00      00:00:01
3.6 測試資料複製
(1)SOURCE DB:
[oracle@redhat6 ogg]$ sqlplus source/oracle

SQL*Plus: Release 11.2.0.3.0 Production on Mon Dec 9 20:09:11 2013

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> create table hjj as select * from sys.all_users;

Table created.
(2)TARGET DB 查看資料同步情況:
[oracle@redhat6 ogg]$ sqlplus targer/oracle

SQL*Plus: Release 11.2.0.3.0 Production on Mon Dec 9 20:09:26 2013

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select count(*) from hjj;

  COUNT(*)
----------
        12

在SOURCE端執行：
SQL> insert into hjj select * from sys.all_users;

12 rows created.

SQL> commit;

Commit complete.

在TARGET查看同步情況
SQL> select count(*) from hjj;

  COUNT(*)
----------
        24
Oracle到Oracle的單向複製配置完成。