golendgate（2）

golendgate（2）安装与配置，抽取online redo log（不是归档日志），并且是基于主键同步一张表（hr.t1）的数据(ddl暂不同步)。

 

环境：

 oracle 11.2.0.3  源端

 oracle 10.2.0.5  目标端

  linux 5.4系统

 

源端与目标端都需要添加数据库附加日志

 

1.添加数据库附加日志：

alter database add supplemental log data;

切换日志，使附加日志生效.

alter system switch logfile;

2.验证数据库附加日志：

select  SUPPLEMENTAL_LOG_DATA_MIN, SQL> select SUPPLEMENTAL_LOG_DATA_MIN,
  2         SUPPLEMENTAL_LOG_DATA_PK,
  3         SUPPLEMENTAL_LOG_DATA_UI,
  4         SUPPLEMENTAL_LOG_DATA_FK,
  5         SUPPLEMENTAL_LOG_DATA_ALL
  6    from v$database;
 
SUPPLEMENTAL_LOG_DATA_MIN  SUPPLEMENTAL_LOG_DATA_PK SUPPLEMENTAL_LOG_DATA_UI SUPPLEMENTAL_LOG_DATA_FK SUPPLEMENTAL_LOG_DATA_ALL
------------------------- ------------------------ ------------------------ ------------------------ -------------------------
YES                       NO                       NO                       NO                       NO
 

 

源端操作

1.创建GGS用户

create tablespace ggs datafile '+DATA/dbking/datafile/ggs01.dbf'
size 100M
autoextend on
maxsize 1024M
extent management local
uniform. size 128k
segment space management auto;

create user ggs identified by ggs default tablespace ggs

2.给GGS用户赋予权限

grant dba to ggs;####完成配置之后回收.

grant create session,alter session to ggs;

grant resource to ggs;

grant connect to ggs;

grant select any dictionary to ggs;

grant flashback any table to ggs;

grant select any table to ggs;

grant execute on dbms_flashback to ggs;

3.创建trail文件目录

cd /u01/app/oracle/ggs/dirdat

mkdir -p trail/hr

4.创建ggs目录

GGSCI (dbhouse) 3> create subdirs

5. 配置GLOBALS

ggsci>edit params ./GLOBALS

GGSCHEMA ggs

6.配置mgr

ggsci>edit params mgr

添加如下内容：

PORT 7809
DYNAMICPORTLIST 7810-7820, 7830
AUTOSTART EXTRACT *
AUTORESTART EXTRACT *, RETRIES 4, WAITMINUTES 4
STARTUPVALIDATIONDELAY 5
PURGEOLDEXTRACTS /u01/app/oracle/ggs/dirdat/trail/hr/tt*, USECHECKPOINTS, MINKEEPHOURS 2

7. 启动mgr进程

GGSCI (dbhouse) 3> info all

Program     Status      Group       Lag           Time Since Chkpt

MANAGER     RUNNING                                          

GGSCI (dbhouse) 4> info all

Program     Status      Group       Lag           Time Since Chkpt

MANAGER     RUNNING 

8.配置extract抽取进程

  8.1 增加extract抽取进程以及关联trail文件

 GGSCI (dbhouse) 5> dblogin userid ggs, password ggs sysdba
Successfully logged into database.

 GGSCI (dbhouse) 6> add EXTRACT s_ex_hr, tranlog, begin now

2013-03-10 18:13:21  INFO    OGG-01749  Successfully registered EXTRACT S_EX_HR to start managing log retention at SCN 7241655.
EXTRACT added.

GGSCI (dbhouse) 7> add exttrail /u01/app/oracle/ggs/dirdat/trail/hr/tt, extract s_ex_hr
EXTTRAIL added.

  8.2 配置extract抽取进程

GGSCI (dbhouse) 8>edit params s_ex_hr

EXTRACT s_ex_hr
USERID ggs, PASSWORD ggs
TRANLOGOPTIONS ASMUSER sys@asm, ASMPASSWORD grid sysdba
EXTTRAIL /u01/app/oracle/ggs/dirdat/trail/hr/tt
TABLE hr.t1;

 

(

注意：

  由于我的源端是asm存储方式管理数据文件，故而需要额外的配置：

    a. oracle用户下tsnames.ora增加如下：

       ASM =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = dbhouse)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = +asm)
    )
  )

  b .必须是sys用户 sysdba权限访问asm实例

     [oracle@dbhouse admin]$ sqlplus sys/grid@asm as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Sun Mar 10 20:42:26 2013

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

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
With the Automatic Storage Management option

SQL>

   能够访问，说明配置没有问题

  c. listener监听器监听asm实例

     lsnrctl status

     Services Summary...
Service "+ASM" has 1 instance(s).
  Instance "+ASM", status READY, has 1 handler(s) for this service...

  d. 在extract抽取进程添加asmuser

    TRANLOGOPTIONS ASMUSER sys@asm, ASMPASSWORD grid

)

 

 8.3 验证extract抽取进程抽取online redo log运行正常

GGSCI (dbhouse) 36> info er *

EXTRACT    S_EX_HR   Last Started 2013-03-10 19:39   Status RUNNING
Checkpoint Lag       00:00:00 (updated 00:00:05 ago)
Log Read Checkpoint  Oracle Redo Logs
                     2013-03-10 20:34:49  Seqno 131, RBA 25529856

 

确定是抽取online redo log，而不是archiveg log

SQL> archive log list;
Database log mode        Archive Mode
Automatic archival        Enabled
Archive destination        /archivelog
Oldest online log sequence     129
Next log sequence to archive   131
Current log sequence        131
SQL>

 

切换日志

 

     SQL> alter system switch logfile;

System altered.

SQL> archive log list;
Database log mode        Archive Mode
Automatic archival        Enabled
Archive destination        /archivelog
Oldest online log sequence     130
Next log sequence to archive   132
Current log sequence        132

 

查看extract 抽取进程应该抽取online redo log 132

GGSCI (dbhouse) 37> info er *

EXTRACT    S_EX_HR   Last Started 2013-03-10 19:39   Status RUNNING
Checkpoint Lag       00:00:00 (updated 00:00:02 ago)
Log Read Checkpoint  Oracle Redo Logs
                     2013-03-10 20:52:05  Seqno 132, RBA 8192

 

9 .配置extract pump进程

   9.1 增加extract  pump进程以及关联local trail文件和remote trail文件

 

GGSCI (dbhouse) 6> add extract pump_hr, EXTTRAILSOURCE /u01/app/oracle/ggs/dirdat/trail/hr/tt
EXTRACT added.

GGSCI (dbhouse) 7> add RMTTRAIL /u01/app/oracle/ggs/dirdat/rtrail/hr/pp, extract pump_hr
RMTTRAIL added.

 

  9.2 配置extract pump进程

GGSCI (dbhouse) 18> view params pump_hr

EXTRACT pump_hr
USERID ggs, PASSWORD ggs
RMTHOST 10.23.5.71, MGRPORT 7809
RMTTRAIL /u01/app/oracle/ggs/dirdat/rtrail/hr/pp
PASSTHRU
TABLE hr.t1;

 

GGSCI (dbhouse) 19> info all

Program     Status      Group       Lag           Time Since Chkpt

MANAGER     RUNNING                                          
EXTRACT     RUNNING     PUMP_HR     00:00:00      00:14:42   
EXTRACT     RUNNING     S_EX_HR     00:00:00      00:00:03 

 

目标端操作

 

1.创建GGS用户

create tablespace ggs datafile '/u01/app/oracle/oradata/dbwdn/ggs01.dbf'
size 100M
autoextend on
maxsize 1024M
extent management local
uniform. size 128k
segment space management auto;

create user ggs identified by ggs default tablespace ggs

2.给GGS用户赋予权限

grant create session,alter session to ggs;
grant resource to ggs;
grant connect to ggs;
grant select any dictionary to ggs;
grant select any table to ggs;
grant insert any table to ggs;
grant update any table to ggs;
grant delete any table to ggs;
grant create table to ggs;
grant dba to ggs;

3.创建trail文件目录

cd /u01/app/oracle/ggs/dirdat

mkdir -p rtrail/hr

4.创建ggs目录

GGSCI (primary) 3> create subdirs

5.配置mgr

PORT 7809
DYNAMICPORTLIST 7810-7820, 7830
AUTOSTART REPLICAT *
AUTORESTART REPLICAT *, RETRIES 4, WAITMINUTES 4
STARTUPVALIDATIONDELAY 5
PURGEOLDEXTRACTS /u01/app/oracle/ggs/dirdat/rtrail/hr/pp*, USECHECKPOINTS, MINKEEPHOURS 2

6.增加checkpointtable

GGSCI (primary) 15> dblogin userid ggs,password ggs
Successfully logged into database.

GGSCI (primary) 16> add CHECKPOINTTABLE ggs.GGS_CHECKPOINT

Successfully created checkpoint table GGS.GGS_CHECKPOINT.

7.在GLOBALS全局配置中指定checkpointtable

GGSCI (primary) 17> EDIT PARAMS ./GLOBALS

CHECKPOINTTABLE ggs.GGS_CHECKPOINT

 8.配置Replicat复制进程

   8.1 增加Replicat复制进程以及关联trail文件

GGSCI (primary) 21> add REPLICAT rep_hr, EXTTRAIL /u01/app/oracle/ggs/dirdat/rtrail/hr/pp, CHECKPOINTTABLE ggs.GGS_CHECKPOINT
REPLICAT added.

   8.2 配置replicat复制进程

  GGSCI (primary) 24> view params rep_hr      

REPLICAT rep_hr
ASSUMETARGETDEFS
DISCARDFILE /u01/app/oracle/ggs/dirrpt/discard/rep.dsc, APPEND
USERID ggs, PASSWORD ggs
MAP hr.t1, TARGET hr.t1;

     8.3 验证replicat进程运行正常

    GGSCI (primary) 32> info all

Program     Status      Group       Lag           Time Since Chkpt

MANAGER     RUNNING                                          
REPLICAT    RUNNING     REP_HR      00:00:00      00:00:08 

 

 配置完成，下面同步一张表的数据

9.同步数据

 9.1 在源端增加表级别的附加日志

GGSCI (dbhouse) 38> add trandata hr.t1

Logging of supplemental redo log data is already enabled for table HR.T1.

GGSCI (dbhouse) 39>  info trandata hr.t1

Logging of supplemental redo log data is enabled for table HR.T1

 

  注意：在源端添加表级别的附加日志，也可alter table.....方式添加，如：

ALTER TABLE  hr.t1 ADD SUPPLEMENTAL LOG GROUP group_t1_pk (id) ALWAYS;

 

id字段上有主键或唯一键都可以.

 

(两种方式二选一.)

 

9.2  因为源端hr.t1 和目标端hr.t1是结构相同的空表，所以不需要做初始化，后续的发布有涉及到初始化数据。

    源端hr.t1表结构与目标端hr.t1表结构相同

SQL> desc hr.t1;
Name Type         Nullable Default Comments
---- ------------ -------- ------- --------
ID   INTEGER      Y                        
NAME VARCHAR2(10) Y                        
TIAN VARCHAR2(20) Y  

 

  9.3 在源端插入一条数据

insert into hr.t1 values(1,'中国','china');

commit;

 

SQL> select * from hr.t1;  

 ID NAME       TIAN
---------- ---------- --------------------
  1 中国       china

 

因为是抽取online redo log，所以应该马上在目标端看到这条数据.

 

9.3 查看目标端hr.t1

SQL> select * from hr.t1;

 ID NAME       TIAN
---------- ---------- --------------------
  1 中国       china

数据传过来了，测试成功.

 

 

 

 

 

 

 

 

 

 

 

 

 

 

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

转载于:http://blog.itpub.net/21266384/viewspace-755701/