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

发表于2017年2月27日由紫砂壶

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分类目录，贴了标签。将固定链接加入收藏夹。