GoldenGate单向复制配置

最新推荐文章于 2021-04-02 19:50:34 发布

weixin_33853794

最新推荐文章于 2021-04-02 19:50:34 发布

阅读量91

点赞数

文章标签：数据库开发工具网络

本文链接：https://blog.csdn.net/weixin_33853794/article/details/85124859

版权

本文中将演示下使用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> 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    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：插入数据