ITPub博客

首页 > Linux操作系统 > Linux操作系统 > Oracle 11g GoldenGate单向复制配置(DML)

Oracle 11g GoldenGate单向复制配置(DML)

原创 Linux操作系统 作者:sun642514265 时间:2013-07-29 11:05:35 0 删除 编辑
本文中将演示下使用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
 
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    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  
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 


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

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/28698327/viewspace-767337/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2013-05-16

  • 博文量
    34
  • 访问量
    193297