GoldenGate从Oracle 12c多租户到MySQL主从

1.技术说明

  • 在Oracle多租户环境下,GoldenGate的抽取进程只能使用集成(integrated)模式,而不能使用经典(claasic capture)模式。
  • GoldenGate的集成模式可以与Oracle database的 log mining server集成在一起,从本地或者 downstream mining database 中,
  • 以逻辑改变记录(logical change records,即:LCR)的方式接收变化数据。

  • CDB包含多个PDB,源端只需部署一个抽取进程就可访问所有pdb redo,不需要为每个pdb单独配置extract
  • 源端要使用common user,即c##ogg这种用户来访问源端DB,这样能访问数据库的redo log 和 所有多租户。
  • 1.1 GoldenGate经典模式架构


    日志解析是在数据库外完成

    1.2 GoldenGate集成模式架构

    2. 环境说明

    2.1 GoldenGate版本

  • ogg for mysql
  • [mysql@test1 ogg_target]$ ./ggsci
    
    Oracle GoldenGate Command Interpreter for MySQL
    Version 12.2.0.1.1 OGGCORE_12.2.0.1.0_PLATFORMS_151211.1401
    Linux, x64, 64bit (optimized), MySQL Enterprise on Dec 11 2015 16:23:51
    Operating system character set identified as UTF-8.
    
  • ogg for oracle12c
  • [oracle@test1 ogg_source]$ ./ggsci
    
    Oracle GoldenGate Command Interpreter for Oracle
    Version 12.2.0.1.1 OGGCORE_12.2.0.1.0_PLATFORMS_151211.1401_FBO
    Linux, x64, 64bit (optimized), Oracle 12c on Dec 12 2015 02:56:48
    Operating system character set identified as UTF-8.
    
    Copyright (C) 1995, 2015, Oracle and/or its affiliates. All rights reserved.
    

    2.2 主机说明

    IP 主机
    192.168.1.45 test1
    192.168.1.46 test2

    在45和46之间配置了MySQL主从复制,45为主,46为从
    在45机器上安装了Oracle 12c多租户,OGG从45上的Oracle复制到45上的MySQL,MySQL再由46(从)同步45(主)

    2.3 数据库版本

  • 源:Oracle 12c
  • SQL> select * from v$version;
    
    BANNER                                                                               CON_ID
    -------------------------------------------------------------------------------- ----------
    Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production              0
    PL/SQL Release 12.1.0.2.0 - Production                                                    0
    CORE    12.1.0.2.0      Production                                                                0
    TNS for Linux: Version 12.1.0.2.0 - Production                                            0
    NLSRTL Version 12.1.0.2.0 - Production                                                    0
    
  • 目标:MySQL
  • root@localhost[(none)]> select version();
    +------------+
    | version()  |
    +------------+
    | 5.7.17-log |
    +------------+
    1 row in set (0.02 sec)
    

    2.4 GoldenGate安装位置

    安装位置 安装用户
    源:test1主机:/data/ogg_source 属主:oracle:oinstall
    目标:test1主机:/data/ogg_target 属主:mysql:mysql

    3. GoldenGate初始化

    3.1 目标端(MySQL)

  • 创建目录
  • GGSCI (test1) 4> create subdirs
    
    Creating subdirectories under current directory /data/ogg_target
    
    Parameter files                /data/ogg_target/dirprm: created
    Report files                   /data/ogg_target/dirrpt: created
    Checkpoint files               /data/ogg_target/dirchk: created
    Process status files           /data/ogg_target/dirpcs: created
    SQL script files               /data/ogg_target/dirsql: created
    Database definitions files     /data/ogg_target/dirdef: created
    Extract data files             /data/ogg_target/dirdat: created
    Temporary files                /data/ogg_target/dirtmp: created
    Credential store files         /data/ogg_target/dircrd: created
    Masterkey wallet files         /data/ogg_target/dirwlt: created
    Dump files                     /data/ogg_target/dirdmp: created
    
  • 配置MGR
  • GGSCI (test1) 5> edit params mgr
    Port 7810
    DYNAMICPORTLIST 7840-7849
    
    GGSCI (test1) 6> start mgr
    Manager started.
    
    GGSCI (test1) 7> info all
    
    Program     Status      Group       Lag at Chkpt  Time Since Chkpt
    
    MANAGER     RUNNING    
    
  • 创建ogg检查点表
  • GGSCI (test1) 8> dblogin sourcedb test userid root
    Password: 
    
    2017-02-24 16:20:12  WARNING OGG-00769  MySQL Login failed: . SQL error (2002). Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2).
    ERROR: Failed to connect to MySQL database engine for HOST localhost, DATABASE , USER root, PORT 3306.
    

    添加链接文件

    [root@test1 ~]# ln -s /data/mysql/mysql.sock /tmp/mysql.sock
    

    或者设置环境变量

    $ export MYSQL_UNIX_PORT=/data/mysql/mysql.sock
    

    重新登录

    GGSCI (test1) 10> dblogin sourcedb test userid root
    Password: 
    Successfully logged into database.
    
    GGSCI (test1 DBLOGIN as root) 11> add checkpointtable test.checkpoint
    
    Successfully created checkpoint table test.checkpoint.
    

    3.2 源端(Oracle 12c多租户)

  • 开启归档
  • SQL> alter system set log_archive_dest_1='location=/data/oracle/arch' scope=both;
    
    System altered.
    
    SQL> archive log list
    Database log mode              No Archive Mode
    Automatic archival             Disabled
    Archive destination            /data/oracle/arch
    Oldest online log sequence     299
    Current log sequence           301
    SQL> show pdbs         
    
        CON_ID CON_NAME                       OPEN MODE  RESTRICTED
    ---------- ------------------------------ ---------- ----------
             2 PDB$SEED                       READ ONLY  NO
             3 PDB1                           READ WRITE NO
             4 PDB2                           READ WRITE NO
    SQL> shutdown immediate
    Database closed.
    Database dismounted.
    ORACLE instance shut down.
    SQL> startup mount;
    ORACLE instance started.
    
    Total System Global Area 6442450944 bytes
    Fixed Size                  5298088 bytes
    Variable Size            1543506008 bytes
    Database Buffers         4882169856 bytes
    Redo Buffers               11476992 bytes
    Database mounted.
    SQL> alter database archivelog;
    
    Database altered.
    
    SQL> alter database open;
    
    Database altered.
    
    SQL> show pdbs
    
        CON_ID CON_NAME                       OPEN MODE  RESTRICTED
    ---------- ------------------------------ ---------- ----------
             2 PDB$SEED                       READ ONLY  NO
             3 PDB1                           MOUNTED
             4 PDB2                           MOUNTED
    SQL> alter pluggable database all open;
    
    Pluggable database altered.
    
  • 开启追加日志
  • SQL> alter database add supplemental log data(primary key,unique) columns;
    
    Database altered.
    
  • 开启force loggin
  • SQL> ALTER DATABASE FORCE LOGGING; 
    
    Database altered.
    
  • 创建OGG用户
  • 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.
    
  • 设置系统参数
  • SQL> alter system set enable_goldengate_replication=true scope=both;
    
    System altered.
    

    4. 抽取样例表

    4.1 源端(oracle)

    $ sqlplus pei/123456@192.168.1.45:1621/pdb1
    
    SQL> create table books
      2  ( id int,
      3    name varchar2(10),
      4    primary key(id)
      5  );
    
    Table created.
    

    4.2 目标端(MySQL)

    [mysql@test1 ~]$ mysql -uroot -p123456
    Welcome to the MySQL monitor.  Commands end with ; or \g.
    Your MySQL connection id is 10
    Server version: 5.7.17-log MySQL Community Server (GPL)
    
    Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.
    
    Oracle is a registered trademark of Oracle Corporation and/or its
    affiliates. Other names may be trademarks of their respective
    owners.
    
    Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
    
    root@localhost[(none)]> use test
    Database changed
    
    root@localhost[test]> create table books
        -> (id int,
        ->  name varchar(10),
        ->  primary key(id)
        -> );
    Query OK, 0 rows affected (0.07 sec)
    

    5. 生成模型定义文件

    由于是Oracle到MySQL的复制,所以在目标端必须要用源端的模型定义文件
    在源端生成定义文件如下:

    GGSCI (test1) 1> edit params defgendefsfile ./dirdef/defgen.defs
    userid c##ogg, password ogg
    SOURCECATALOG pdb1
    TABLE pei.books;
    
    [oracle@test1 ogg_source]$ ./defgen paramfile ./dirprm/defgen.prm
    
    ***********************************************************************
            Oracle GoldenGate Table Definition Generator for Oracle
          Version 12.2.0.1.1 OGGCORE_12.2.0.1.0_PLATFORMS_151211.1401
       Linux, x64, 64bit (optimized), Oracle 12c on Dec 11 2015 17:22:38
     
    Copyright (C) 1995, 2015, Oracle and/or its affiliates. All rights reserved.
    
    
                        Starting at 2017-02-25 10:15:44
    ***********************************************************************
    
    Operating System Version:
    Linux
    Version #1 SMP Mon Oct 24 10:22:33 EDT 2016, Release 2.6.32-642.6.2.el6.x86_64
    Node: test1
    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: 55941
    
    ***********************************************************************
    **            Running with the following parameters                  **
    ***********************************************************************
    defsfile ./dirdef/defgen.defs
    userid c##ogg, password ***
    SOURCECATALOG pdb1
    TABLE pei.books;
    Default source catalog name pdb1 will be used for table specification pei.books.
    Retrieving definition for PDB1.PEI.BOOKS.
    
    
    Definitions generated for 1 table in ./dirdef/defgen.defs.
    

    把生成的定义文件复制到OGG目标端

    [root@test1 ~]# cp /data/ogg_source/dirdef/defgen.defs /data/ogg_target/dirdef/
    [root@test1 ~]# chown mysql:mysql /data/ogg_target/dirdef/defgen.defs
    

    6. 配置源端

  • 创建目录
  • GGSCI (test1) 4> create subdirs
    
    Creating subdirectories under current directory /data/ogg_source
    
    Parameter files                /data/ogg_source/dirprm: created
    Report files                   /data/ogg_source/dirrpt: created
    Checkpoint files               /data/ogg_source/dirchk: created
    Process status files           /data/ogg_source/dirpcs: created
    SQL script files               /data/ogg_source/dirsql: created
    Database definitions files     /data/ogg_source/dirdef: created
    Extract data files             /data/ogg_source/dirdat: created
    Temporary files                /data/ogg_source/dirtmp: created
    Credential store files         /data/ogg_source/dircrd: created
    Masterkey wallet files         /data/ogg_source/dirwlt: created
    Dump files                     /data/ogg_source/dirdmp: created
    
  • 配置MGR
  • GGSCI (test1) 5> edit params mgr
    Port 7809
    DYNAMICPORTLIST 7830-7839
    
    GGSCI (test1) 6> start mgr
    Manager started.
    
    GGSCI (test1) 7> info all
    
    Program     Status      Group       Lag at Chkpt  Time Since Chkpt
    
    MANAGER     RUNNING    
    
  • 添加表级追加日志
  • GGSCI (test1) 12> dblogin userid c##ogg@pdb1, password ogg
    Successfully logged into database PDB1.
    
    GGSCI (test1 as c##ogg@t12c/PDB1) 14> add trandata pdb1.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'.
    
  • 添加抽取进程
  • GGSCI (test1) 8> add extract ex_ora,integrated tranlog,begin now 
    EXTRACT added.
    
    GGSCI (test1) 9> add exttrail ./dirdat/my,extract ex_ora,megabytes 100
    EXTTRAIL added.
    
    GGSCI (test1) 11> edit param ex_ora
    
    EXTRACT ex_ora SETENV (ORACLE_SID='t12c') 
    userid c##ogg, password ogg 
    
    EXTTRAIL ./dirdat/my
    TABLE pdb1.pei.books;
    -- SOURCECATALOG pdb1
    -- TABLE pei.books;
    
    GGSCI (test1 as c##ogg@t12c/PDB1) 43> dblogin userid c##ogg, password ogg
    Successfully logged into database CDB$ROOT.
    
    GGSCI (test1 as c##ogg@t12c/CDB$ROOT) 44> register extract ex_ora database container(pdb1,pdb2)
    
    2017-02-24 17:15:30  INFO    OGG-02003  Extract ex_ora successfully registered with database at SCN 5093503.
    
  • 添加传输进程
  • GGSCI (test1 as c##ogg@t12c/CDB$ROOT) 48> add extract dp_ora, exttrailsource ./dirdat/my
    EXTRACT added.
    
    GGSCI (test1 as c##ogg@t12c/CDB$ROOT) 49> add rmttrail ./dirdat/my, extract dp_ora, megabytes 100
    RMTTRAIL added.
    
    GGSCI (test1 as c##ogg@t12c/CDB$ROOT) 50> edit param dp_ora
    Extract  dp_ora
    Userid c##ogg,password ogg
    rmthost 192.168.1.45, mgrport 7810
    rmttrail ./dirdat/my
    passthru
    TABLE pdb1.pei.books;
    
  • 启动抽取和传输进程
  • GGSCI (test1 as c##ogg@t12c/CDB$ROOT) 51> start ex_ora
    GGSCI (test1 as c##ogg@t12c/CDB$ROOT) 52> start dp_ora  
    

    7. 配置目标端

  • 添加应用进程
  • GGSCI (test1 DBLOGIN as root) 2> add replicat rp_my,exttrail ./dirdat/my, checkpointtable test.checkpoint
    REPLICAT added.
    
    GGSCI (test1 DBLOGIN as root) 3> edit param rp_myREPLICAT rp_my
    SETENV (MYSQL_UNIX_PORT=/data/mysql/mysql.sock)
    targetdb test@localhost:3306 userid root password 123456
    sourcedefs /data/ogg_target/dirdef/defgen.defs
    SQLEXEC "select CURRENT_TIME();" EVERY 10 MINUTES
    REPORT AT  6:00
    discardfile ./dirrpt/rp_my.dsc,megabytes 10
    MAP pdb1.pei.books, TARGET  test.books;
    
  • 启动应用进程
  • GGSCI (test1) 21> start rp_my
    
    Sending START request to MANAGER ...
    REPLICAT rp_my starting
    

    关于紫砂壶

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