GoldenGate downstream方式实时抽取介绍

1.介绍

从GoldenGate 11g开始,Oracle引入了集成抽取模式,传统的抽取模式称为classic capture mode。

  • 在classic capture mode中,OGG的extract进程直接读取Oracle redo log,分析数据变化内容,存为ogg的trail file,利用pump把trail file传递到目标端,目标端把trail file翻译成sql应用到目标库。
  • 在integrated capture mode中,OGG的extract进程不再直接读取Oracle redo log,而是通过与log mining server整合来分析数据变化:log mining server负责以LCR的格式从数据库日志中捕获数据变化,然后extract进程再抓取数据存成trail file格式。integrated capture mode的这种改变带来的好处主要体现在兼容性方面:可以支持更多的数据类型,由于解析日志交与log mining server,所以extract不需要为Oracle RAC,ASM和TDE等做更多额外的配置。
  • integrated capture mode带来的另一个变化就是:开始支持本地和downstream两种配置选项。而在传统模式下,extract进程必须配置在源数据库上。
    本篇文章主要介绍,extract进程不在源数据库上的downstram实时抽取模式的配置。

    2. ogg downstraem模式架构


    在这个场景下,OGG的Extract运行在一个与生产完全独立的数据库服务器上,这个服务器通过LGWR ASYNC与生产库连接,从生产库获取redo日志,执行LogMining,并且存储LogMiner的元数据。
    Downstream模式的抽取进程,可以配置成实时抽取或通过归档日志抽取。
    实时抽取,只能配置1个生产库。
    如果downstream模式下有多个生产库需要被复制,只能使用归档日志模式的抽取

    3. ogg downstraem模式要求

  • source数据库库版本要求10.2.0.4以上
  • DownStream数据库版本要求11.2.0.3以上
  • 当前测试环境:

    role host version db_name db_unique_name arch
    source test1 12.1.0.2 t12c t12c yes
    downstream test2 12.1.0.2 s12c s12c yes

    4. 配置日志传输服务

    如果在downstream库上进行实时日志模式抽取,需要在downstream日志挖掘库上创建standby日志以便生产库使用Oracle DataGuard redo传输服务发送redo日志到downstream库。
    关于归档日志模式抽取,后面再另写一篇。

    4.1 准备tnsnames

  • 在source上配置日志传输服务到downstream库
  • 在source的tnsnames.ora文件中添加:

    S12C =
      (DESCRIPTION =
        (ADDRESS = (PROTOCOL = TCP)(HOST = test2)(PORT = 1621))
        (CONNECT_DATA =
          (SERVER = DEDICATED)
          (SERVICE_NAME = s12c)
        )
      )
    

    4.2 重建口令文件

    downstream库重建口令文件,注意ignorecase参数
    经过测试,只需要在downstream库上删除口令文件并重建带上ignorecase=y,无需重启downstream库实例,source库的口令文件无需重建
    $ orapwd password=oracle file=orapws12c ignorecase=y
    如果downstream库不使用ignorecase=y,在source库的alert日志中出现如下错误:

    Thu Mar 02 18:49:11 2017
    Error 1017 received logging on to the standby
    ------------------------------------------------------------
    Check that the primary and standby are using a password file
    and remote_login_passwordfile is set to SHARED or EXCLUSIVE, 
    and that the SYS password is same in the password files.
          returning error ORA-16191
    ------------------------------------------------------------
    

    表明redo无法传输到downstream库

    4.3 downstream库创建standby redo log

  • 检查source库上的日志
  • SQL> SELECT BYTES FROM GV$LOG;
     
         BYTES
    ----------
      52428800
      52428800
      52428800
     
    SQL> SELECT COUNT(GROUP#) FROM GV$LOG;
     
    COUNT(GROUP#)
    -------------
                3
    
  • 在downstream库上添加standby redo log
  • ALTER DATABASE ADD STANDBY LOGFILE GROUP 4 ('/data/oracle/oradata/s12c/slog1.rdo') SIZE 50M;
    ALTER DATABASE ADD STANDBY LOGFILE GROUP 5 ('/data/oracle/oradata/s12c/slog2.rdo') SIZE 50M;
    ALTER DATABASE ADD STANDBY LOGFILE GROUP 6 ('/data/oracle/oradata/s12c/slog3.rdo') SIZE 50M;
    ALTER DATABASE ADD STANDBY LOGFILE GROUP 7 ('/data/oracle/oradata/s12c/slog4.rdo') SIZE 50M;
    

    4.4 downstreams库上配置archive参数

    在downstraem库上设置归档日志路径:

    SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_1='LOCATION=/data/oracle/arch VALID_FOR=(ONLINE_LOGFILE,PRIMARY_ROLE)'  scope=both;
    SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='LOCATION=/data/oracle/standby_arch VALID_FOR=(STANDBY_LOGFILE,ALL_ROLES)'  scope=both;
    

    注意:downstream库也必须为归档模式,如果是非归档,则修改成归档。

    4.5 设置log_archive_config参数

    downstram实时抽取模式下,需要source库和downstream库上设置

    SQL> ALTER SYSTEM SET LOG_ARCHIVE_CONFIG='DG_CONFIG=(t12c,s12c)' scope=both;
    

    4.6 在生产库上配置archive参数

    SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=s12c ASYNC NOREGISTER VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) REOPEN=10 DB_UNIQUE_NAME=s12c' scope=both;
    SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE;
    SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_1='LOCATION=/data/oracle/arch VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=t12c' scope=both;
    

    5. OGG管理用户创建

    5.1 source库

    SQL> create user c##ogg identified by ogg ;
    User created.
    SQL> grant dba to c##ogg container=all;
    Grant succeeded.
    

    5.2 downstream库

    SQL> show con_name
    CON_NAME
    ------------------------------
    CDB$ROOT
    SQL> create user c##ogg identified by ogg ;
    User created.
    SQL> grant dba to c##ogg container=all;
    Grant succeeded.
    SQL> exec dbms_goldengate_auth.grant_admin_privilege('C##OGG',container=>'all');
    PL/SQL procedure successfully completed.
    

    5.3 在downstream服务器上向source库的表添加trandata

    在downstream库上配置tnsnames,如下:

    T12C_PDB1 =
      (DESCRIPTION =
        (ADDRESS = (PROTOCOL = TCP)(HOST = test1)(PORT = 1621))
        (CONNECT_DATA =
          (SERVER = DEDICATED)
          (SERVICE_NAME = pdb1)
        )
    

    在downstream库上的ggsci里,登录source的pdb1,然后添加trandata

    GGSCI (test2) 1> DBLOGIN USERID c##ogg@t12c_pdb1 password ogg
    Successfully logged into database PDB1.
    
    GGSCI (test2 as c##ogg@t12c/PDB1) 2> add trandata pei.books
    
    Logging of supplemental redo data enabled for table PDB1.PEI.BOOKS.
    TRANDATA for scheduling columns has been added on table 'PDB1.PEI.BOOKS'.
    TRANDATA for instantiation CSN has been added on table 'PDB1.PEI.BOOKS'.
    

    5.4 downstream添加extract

    downstram库修改参数

    SQL> alter system set enable_goldengate_replication=true scope=both;
    
    System altered.
    

    downstram库的ogg上添加extract

    GGSCI (test2) 1> DBLOGIN USERID c##ogg@t12c password ogg
    Successfully logged into database CDB$ROOT.
    
    GGSCI (test2 as c##ogg@t12c/CDB$ROOT) 2> MININGDBLOGIN USERID c##ogg password ogg
    Successfully logged into mining database.
    
    GGSCI (test2 as c##ogg@t12c/CDB$ROOT) 3> REGISTER EXTRACT ext1 database container(pdb1)
    
    2017-03-02 20:14:35  INFO    OGG-02003  Extract EXT1 successfully registered with database at SCN 1973842.
    
    GGSCI (test2 as c##ogg@t12c/CDB$ROOT) 5> ADD EXTRACT ext1, INTEGRATED TRANLOG, BEGIN NOW
    EXTRACT (Integrated) added.
    
    GGSCI (test2 as c##ogg@t12c/CDB$ROOT) 6> add exttrail ./dirdat/my,extract ext1,megabytes 100
    EXTTRAIL added.
    
    GGSCI (test2 as c##ogg@t12c/CDB$ROOT) 7> edit param ext1
    EXTRACT ext1
    SETENV (ORACLE_SID='s12c')
    USERID c##ogg@t12c PASSWORD ogg
    TRANLOGOPTIONS MININGUSER c##ogg MININGPASSWORD ogg
    TRANLOGOPTIONS INTEGRATEDPARAMS (downstream_real_time_mine Y)
    EXTTRAIL ./dirdat/my
    TABLE pdb1.pei.books;
    
    GGSCI (test2 as c##ogg@t12c/CDB$ROOT) 26> start extract ext1
    
    Sending START request to MANAGER ...
    EXTRACT EXT1 starting
    

    测试:

    [oracle@test2 dirdat]$ ls -lrt
    total 4
    -rw-r-----. 1 oracle oinstall 1378 Mar  2 20:24 my000000000
    

    在source库上插入数据:

    SQL> insert into books values(10,'cc');
    
    1 row created.
    
    SQL> commit;
    
    Commit complete.
    

    观察,已经OGG在downstram上已经捕获到数据

    [oracle@test2 dirdat]$ ls -lrt
    total 4
    -rw-r-----. 1 oracle oinstall 1907 Mar  2 20:25 my000000000
    

    关于紫砂壶

    感悟技术人生
    此条目发表在GoldenGate分类目录,贴了标签。将固定链接加入收藏夹。