Oracle GoldenGate 学习教程二、配置和使用

GoldenGate通过自身的数据加工、处理、发送、应用以及借用RDBMS数据库系统的优势保证数据的可同步性和准确性，并在错误和故障发生

阅读导航

 

配置和使用GoldenGate的步骤

 

1 配置数据库支持GoldenGate

 

1.1 OGG用户和权限分配

　　GoldenGate需要从在线日子或归档日志抽取捕获系统的变更数据信息，这些信息可能来源于业务用户，可能来源于系统用户，为了使GoldenGate能够抽取这些数据应为GoldenGate创建独立的用户和分配必要的权限以满足系统运行需求，这些权限包括读取业务用户表数据的权限、读取系统表的权限、执行某个系统包的权限等，以下脚步创建GoldenGate用户ogg_owner(源用户)、ogg_trg(目标用户)和GoldenGate角色ogg_role:

[Oracle@sywu ~]$ sqlplus / as sysdba SQL*Plus: Release 11.2.0.3.0 Production on Fri Aug 21 14:11:04 2015 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, Automatic Storage Management, OLAP, Data Mining and Real Application Testing options SYS@sydb>create tablespace tbs01 datafile '+oradata' size 10m autoextend on uniform size 2m / create user ogg_owner identified by ogg_owner default tablespace tbs01 quota unlimited on tbs01 / create user ogg_trg identified by ogg_trg default tablespace tbs01 quota unlimited on tbs01 / create role ogg_role /

为易管理和维护统一将权限赋予角色ogg_role:

grant CREATE SESSION, ALTER SESSION, ALTER SYSTEM, RESOURCE, SELECT ANY DICTIONARY, FLASHBACK ANY TABLE, SELECT ANY TABLE, SELECT ANY TRANSACTION, insert any table, update any table, drop any table, CREATE TABLE to ogg_role; grant SELECT on dba_clusters to ogg_role; grant SELECT on V_$DATABASE to ogg_role; grant select on sys.logmnr_buildlog to ogg_role; grant EXECUTE on DBMS_FLASHBACK to ogg_role; grant execute on DBMS_CAPTURE_ADM to ogg_role; grant execute on DBMS_STREAMS to ogg_role; grant EXECUTE_CATALOG_ROLE to ogg_role;

然后再将ogg_role 角色赋予ogg_owner(源用户)和ogg_trg(目标用户):

grant ogg_role to ogg_owner; grant ogg_role to ogg_trg;

源用户测试表和数据:

SYS@sydb>create table ogg_owner.togg(id primary key,name,type,CREATED,update_date) 2 as 3 select object_id,object_name,object_type,CREATED,sysdate from dba_objects 4 where rownum1001 5 /

 

1.2 数据库附加日志

附加日志级别分为:

 

1.2.1 数据库级别的附加日志

　　因为GoldenGate需要抽取捕获变更数据信息和元数据信息，这些信息需要记录日志，并且因为日志总是持续增长的，这些日志增长的信息对于GoldenGate是必须记录的，所以必须在GoldenGate进程启动前开启数据库级别的附加日志；
检查数据库是否开启附加日志:

SYS@sydb>SELECT supplemental_log_data_min, force_logging FROM v$database; SUPPLEME FOR -------- --- NO NO

开启数据库级别的附加日志:

SYS@sydb>alter database add supplemental log data; SYS@sydb>ALTER DATABASE FORCE LOGGING; SYS@sydb>SELECT supplemental_log_data_min, force_logging FROM v$database; SUPPLEME FOR -------- --- YES YES Elapsed: 00:00:00.00 SYS@sydb>alter system switch logfile; System altered. Elapsed: 00:00:00.09

 

1.2.2 用户级别的附加日志

　　开启用户级别的附加日志的管理用户必须具有执行DBMS_CAPTURE_ADM包的权限；可以在GoldenGate GGSCI命令行下登录到数据库管理用户为其它的用户开启附加日志；
在GGSCI命令行下使用DBLOGIN命令登录到数据库管理用户

GGSCI (sywu) 1> dblogin userid ogg_owner,password ogg_owner Successfully logged into database.

为用户启用附加日志

GGSCI (sywu as ogg_owner@sydb) 2> add schematrandata sywu 2015-08-24 14:56:26 INFO OGG-01788 SCHEMATRANDATA has been added on schema sywu. 2015-08-24 14:56:27 INFO OGG-01976 SCHEMATRANDATA for scheduling columns has been added on schema sywu.

取消用户附加日志

GGSCI (sywu as ogg_owner@sydb) 6> delete schematrandata sywu 2015-08-25 20:14:49 INFO OGG-01792 SCHEMATRANDATA has been deleted on schema sywu. 2015-08-25 20:14:49 INFO OGG-01979 SCHEMATRANDATA for scheduling columns has been deleted on schema sywu.

 

1.2.3 表级别的附加日志

　　在没有启用用户级别的附加日志的情况下表级别的主键或唯一索引附加日志对于GoldenGate是必须的；在某些情况下即使你启用了用户级别的附加日志，你也可以启用表级别的附加日志使主键附加日志替代每一个在用户级别为GoldenGate指定的键；必须在表没有主键或唯一索引的情况下启用表级别的附加日志。
在GGSCI命令行下使用dblogin登录到数据库管理用户

GGSCI (sywu) 1> dblogin userid ogg_owner,password ogg_owner Successfully logged into database.

为表启用附加日志

GGSCI (sywu as ogg_owner@sydb) 3> add trandata ogg_owner.togg Logging of supplemental redo data enabled for table OGG_OWNER.TOGG. TRANDATA for scheduling columns has been added on table 'OGG_OWNER.TOGG'.

注意:仅当该表有主键或唯一索引时使用上面的命令启用表级别的附加日志，如果表中没有主键或唯一索引时则必须指定一个或多个或者全部列做为主键，在GoldenGate这些键的作用是过滤重复的数据。

SYS@sydb>create table ogg_owner.togg_nokey(id,name,type,CREATED,update_date) 2 as 3 select object_id,object_name,object_type,CREATED,sysdate from dba_objects where rownum1001 5 /

错误的启用没有主键的表级别附加日志:

GGSCI (sywu as ogg_owner@sydb) 4> add trandata ogg_owner.togg_nokey 2015-08-24 16:05:01 WARNING OGG-06439 No unique key is defined for table TOGG_NOKEY. All viable columns will be used to represent the key, but may not guarantee uniqueness. KEYCOLS may be used to define the key. Logging of supplemental redo data enabled for table OGG_OWNER.TOGG_NOKEY. TRANDATA for scheduling columns has been added on table 'OGG_OWNER.TOGG_NOKEY'.