【ORACLE 高可用】使用GOLDENGATE 配置Oracle-MySQL的异构复制

使用GOLDENGATE 配置Oracle-MySQL的异构复制




1. 下载  mysql and goldengate for mysql:

https://edelivery.oracle.com/EPD/Download/get_form?egroup_aru_number=14841440
https://edelivery.oracle.com/EPD/Search/handle_go


2. 安装：MYSQL :
    rpm -ivh /download/MySQL-server-advanced-5.6.13-1.rhel5.i386.rpm
    rpm -ivh /download/MySQL-client-advanced-5.6.13-1.rhel5.i386.rpm

3. 安装goldengate
    $ Su – oracle $ mkdir -p /opt/app/ggs/11.2
    $ chown -R oracle:oinstall /opt/app/ggs/11.2  $ chmod -R 775 /opt/app/ggs/11.2
    $ cd /opt/app/ggs/11.2 
    $ unzip V32420-01.zip
    $ tar xvf ggs_Linux_x86_MySQL_32bit.tar
    cd /opt/app/ggs/11.2
    [oracle@dg2 11.2]$./ggsci
    

4 配置源端服务器
    
     4.1 在源端服务器配置好GOLDENGATE

    GGSCI (dg1) 1> create subdirs

    Creating subdirectories under current directory /opt/app/ggs/11.2

    Parameter files                /opt/app/ggs/11.2/dirprm: already exists
    Report files                   /opt/app/ggs/11.2/dirrpt: created
    Checkpoint files               /opt/app/ggs/11.2/dirchk: created
    Process status files           /opt/app/ggs/11.2/dirpcs: created
    SQL script files               /opt/app/ggs/11.2/dirsql: created
    Database definitions files     /opt/app/ggs/11.2/dirdef: created
    Extract data files             /opt/app/ggs/11.2/dirdat: created
    Temporary files                /opt/app/ggs/11.2/dirtmp: created
    Stdout files                   /opt/app/ggs/11.2/dirout: created


    GGSCI (dg1) 2>


    4.2 建立用户,并赋权限：
    主库：
        SQL> create tablespace ggs_tbs datafile '/opt/app/oracle/oradata/DB1/ggs_tbs01.dbf' size 50m;

        SQL> create user ggs identified by ggs default tablespace ggs_tbs;
        User created.
        SQL> grant create session,alter session to ggs;

        Grant succeeded.

        SQL> grant execute on utl_file to ggs;

        Grant succeeded.

        SQL> grant select any dictionary, select any table to ggs;

        SQL> grant alter any table to ggs;

        Grant succeeded.

        SQL> grant flashback any table to ggs;

        Grant succeeded.

        SQL> grant select any transaction to ggs;

        Grant succeeded.

        SQL> grant sysdba to ggs;

        Grant succeeded.

        SQL> grant create table,insert any table,lock any table to ggs;

        Grant succeeded.

        SQL> grant execute on dbms_flashback to ggs;

        Grant succeeded.

    4.3 源、目标库打开辅助日志

    SQL> alter database force logging;
    alter database force logging
    *
    ERROR at line 1:
    ORA-12920: database is already in force logging mode


    SQL> alter database add supplemental log data;

    Database altered.

    SQL> select supplemental_log_data_min from v$database;

    SUPPLEMENTAL_LOG_DATA_MI
    ------------------------
    YES


    4.4 源库支持sequence

    在源库、目标库上执行：
    GGSCI (dg2) 2> edit params ./globals
    在统计模式下输入并保存：ggschema ggs

    在SQLPLUS 下去运行：
    sql> @sequence.sql 根据提示输入：gg


    4.5 支持ddl复制

     4.5.1 主库配置
    cd /ggs/11.2
    sqlplus / as sysdba
    sql> alter system set recyclebin=off deferred scope=both;  #必须，针对ddl复制
    sql> @marker_setup.sql prompt: ggs
    sql> @ddl_setup.sql prompt: ggs
    sql> @role_setup.sql
    sql> grant GGS_GGSUSER_ROLE to ggs;
    SQL> @ddl_enable.sql
    10g需要安装dbms_share_pool包：
    sql> @?/rdbms/admin/dbmspool.sql sql> @ddl_pin ggs;


    4.6 配置MANAGER
    主库、目标库配置：
    [oracle@dg1 11.2]$ ./ggsci
    Oracle GoldenGate Command Interpreter for Oracle
    Version 11.2.1.0.1 OGGCORE_11.2.1.0.1_PLATFORMS_120423.0230_FBO
    Linux, x86, 32bit (optimized), Oracle 11g on Apr 23 2012 08:09:25
    Copyright (C) 1995, 2012, Oracle and/or its affiliates. All rights reserved.

    GGSCI (dg1) 1> edit params mgr
    GGSCI (dg1) 2> start mgr
    Manager started.
    GGSCI (dg1) 3> info mgr
    Manager is running (IP port dg1.7809).
    GGSCI (dg1) 4>



     4.7 添加extract

    GGSCI (dg1) 1> add extract ext2my,tranlog,begin now
    EXTRACT added.
    GGSCI (dg1) 2> add exttrail ./dirdat/my,extract ext2my,megabytes 100
    EXTTRAIL added.
    GGSCI (dg1) 3> edit params ext2my

        EXTRACT  ext2my

        USERID ggs,  PASSWORD ggs
        TRANLOGOPTIONS  EXCLUDEUSER ggs
        RMTHOST  192.168.10.85, MGRPORT 7809
        RMTTRAIL  ./dirdat/my
        TABLE  hr.* ;


    GGSCI (dg1) 5> info all

    Program     Status      Group       Lag at Chkpt  Time Since Chkpt

    MANAGER     STOPPED                                           
    EXTRACT     ABENDED     EXT1        00:00:00      17:48:59    
    EXTRACT     STOPPED     EXT2MY      00:00:00      00:00:46    
    REPLICAT    ABENDED     REP2        00:00:00      17:48:45    

    4.8 添加对象定义文件
    GGSCI (dg1) 7> edit param defgen

    GGSCI (dg1) 9> view param defgen

    defsfile /opt/app/ggs/11.2/dirdef/ext2my.def
    userid ggs,password ggs
    table hr.*;

    生成定义文件并拷贝到目标服务器
    ./defgen paramfile dirprm/defgen.prm

        [oracle@dg1 11.2]$ cat dirdef/ext2my.def
        *+- Defgen version 2.0, Encoding UTF-8
        *
        * Definitions created/modified  2013-08-31 16:49
        *
        *  Field descriptions for each column entry:
        *
        *     1    Name
        *     2    Data Type
        *     3    External Length
        *     4    Fetch Offset
        *     5    Scale
        *     6    Level
        *     7    Null
        *     8    Bump if Odd
        *     9    Internal Length
        *    10    Binary Length
        *    11    Table Length
        *    12    Most Significant DT
        *    13    Least Significant DT
        *    14    High Precision
        *    15    Low Precision
        *    16    Elementary Item
        *    17    Occurs
        *    18    Key Column
        *    19    Sub Data Type
        *
        Database type: ORACLE
        Character set ID: windows-936
        National character set ID: UTF-16
        Locale: neutral
        Case sensitivity: 14 14 14 14 14 14 14 14 14 14 14 14 11 14 14 14
        *
        Definition for table HR.DEPARTMENT
        Record length: 104
        Syskey: 0
        Columns: 3
        DEP_ID    134     11        0  0  0 1 0      8      8      8 0 0 0 0 1    0 1 3
        DEP_NAME   64     30       12  0  0 1 0     30     30      0 0 0 0 0 1    0 0 0
        DEP_DESC   64     50       48  0  0 1 0     50     50      0 0 0 0 0 1    0 0 0
        End of definition
        *
        Definition for table HR.EMPLOYEE
        Record length: 48
        Syskey: 0
        Columns: 2
        EMP_ID    134     11        0  0  0 1 0      8      8      8 0 0 0 0 1    0 1 3
        EMP_NAME   64     30       12  0  0 1 0     30     30      0 0 0 0 0 1    0 0 0
        End of definition
        *
        Definition for table HR.TST
        Record length: 12
        Syskey: 0
        Columns: 1
        ID  134      8        0  0  0 1 0      8      8      8 0 0 0 0 1    0 1 3
        End of definition



    scp /opt/app/ggs/11.2/dirdef/ext2my.def ractdg3:/opt/app/ggs/11.2/dirdef/ext2my.def
    


5 配置目标端服务器

     5.1 配置MYSQL SERVER
    # mysql -u root -p password '123456'
    Enter password:
    mysqladmin: connect to server at 'localhost' failed
    error: 'Access denied for user 'root'@'localhost' (using password: YES)'
    

    修改MYSQL 密码 操作如下：

    # /etc/init.d/mysql stop
    # mysqld_safe --user=mysql --skip-grant-tables --skip-networking &
    # mysql -u root mysql
    mysql> UPDATE user SET Password=PASSWORD('123456') where USER='root' and host='root' or host='localhost';//把空的用户密码都修改成非空的密码就行了。
    mysql> FLUSH PRIVILEGES;
    mysql> quit # /etc/init.d/mysqld restart
    # mysql -uroot -p
    Enter password: <输入新设的密码123456>
    
    MySql5.6操作时报错：You must SET PASSWORD before executing this statement解决
    mysql>  SET PASSWORD = PASSWORD('123456');

    Query OK, 0 rows affected (0.03 sec)
     update user set password=password('123456') where user='root' and host='root' or host='localhost';

    mysql> create database hr;
    Query OK, 1 row affected (0.01 sec)

    mysql> show databases;
    +--------------------+
    | Database           |
    +--------------------+
    | information_schema |
    | hr                 |
    | mysql              |
    | performance_schema |
    | test               |
    +--------------------+
    5 rows in set (0.00 sec)

    mysql>


     5.2 配置目标端
    GGSCI (ractdg3) 9> create subdirs

    Creating subdirectories under current directory /opt/app/ggs/11.2

    Parameter files                /opt/app/ggs/11.2/dirprm: already exists
    Report files                   /opt/app/ggs/11.2/dirrpt: created
    Checkpoint files               /opt/app/ggs/11.2/dirchk: created
    Process status files           /opt/app/ggs/11.2/dirpcs: created
    SQL script files               /opt/app/ggs/11.2/dirsql: created
    Database definitions files     /opt/app/ggs/11.2/dirdef: created
    Extract data files             /opt/app/ggs/11.2/dirdat: created
    Temporary files                /opt/app/ggs/11.2/dirtmp: created
    Stdout files                   /opt/app/ggs/11.2/dirout: created



    GGSCI (ractdg3) 5> edit params mgr


    GGSCI (ractdg3) 10> view params mgr


        Port 7809
        --DYNAMICPORTLIST 7840-7939
        --AUTOSTART ER *
        --AUTORESTART EXTRACT *,RETRIES 5,WAITMINUTES 3
        --PURGEOLDEXTRACTS ./dirdat/*,usecheckpoints, minkeepdays 3
        --LAGREPORTHOURS 1
        --LAGINFOMINUTES 30
        --LAGCRITICALMINUTES 45

    GGSCI (ractdg3) 10> edit param globals

    GGSCI (ractdg3) 11> view param globals

    checkpointtable hr.checkpoint


    GGSCI (ractdg3) 12>


    5.3创建checkpoint表
    ggsci>dblogin sourcedb hr userid root

    (这里纠结了很久，MYSQL 登录总是报错：ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2)
    vi /usr/my.cnf
    添加以下内容后
    socket =/tmp/mysql.sock
    
    在ggsci 登录 没有问题了，但在LINUX下，登录又报ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2)
    在LINUX下登录，只好先指定 mysql -u root -p -S  '/tmp/mysql.sock'
    进行登录，后面再另行解决。
    )

    ggsci>add checkpointtable hr.checkpoint
    ggsci> edit params ./GLOBALS  #GLOBALS必须大写，编辑GLOBALS需要推出ggsci再进入，输入：
    CHECKPOINTTABLE hr.checkpoint 

    查看CHECKPOINT TABLE
    mysql> use hr
    Reading table information for completion of table and column names
    You can turn off this feature to get a quicker startup with -A

    Database changed
    mysql> show tables;
    +--------------+
    | Tables_in_hr |
    +--------------+
    | checkpoint   |
    +--------------+
    1 row in set (0.00 sec)

    mysql>

     5.4 创建replicat：

    GGSCI (dg2) 10> add replicat rep2my,exttrail /opt/app/ggs/11.2/dirdat/my,checkpointtable hr.checkpoint
    REPLICAT added.

    GGSCI (ractdg3) 8> info all

    Program     Status      Group       Lag at Chkpt  Time Since Chkpt

    MANAGER     RUNNING                                           
    REPLICAT    STOPPED     REP2MY      00:00:00      00:00:02    


    GGSCI (ractdg3) 9> edit params rep2my

    GGSCI (ractdg3) 10> view params rep2my

    REPLICAT rep2my
    sourcedefs /opt/app/ggs/11.2/dirdef/ext2my.def
    --APPLYNOOPUPDATES
    --HANDLECOLLISIONS
    SOURCEDB hr,userid root,password 123456
    discardfile /opt/app/ggs/11.2/dirrpt/rep2my_discard.log,megabytes 10
    MAP hr.*, TARGET  hr.*;
        
    GGSCI (ractdg3) 19> start REPLICAT REP2MY
    GGSCI (ractdg3) 20> info all

    Program     Status      Group       Lag at Chkpt  Time Since Chkpt

    MANAGER     RUNNING                                           
    REPLICAT    RUNNING     REP2MY      00:00:00      00:00:05

     5.5 添加同步表并测试
    [root@ractdg3 11.2]# mysql -u root -p -S '/tmp/mysql.sock'
    Enter password:
    Welcome to the MySQL monitor.  Commands end with ; or \g.
    Your MySQL connection id is 5
    Server version: 5.6.13-enterprise-commercial-advanced MySQL Enterprise Server - Advanced Edition (Commercial)

    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.

    mysql> use hr;
    Reading table information for completion of table and column names
    You can turn off this feature to get a quicker startup with -A
    mysql> create table tst(id integer,constraint pk_tst_id primary key (id));
    Query OK, 0 rows affected (0.02 sec)

    mysql> show tables;
    +--------------+
    | Tables_in_hr |
    +--------------+
    | checkpoint   |
    | tst          |
    +--------------+
    2 rows in set (0.00 sec)

    mysql> create table TST(ID INT,CONSTRAINT PK_TST_ID PRIMARY KEY(ID));
    
    mysql> DESC TST;
    +-------+---------+------+-----+---------+-------+
    | Field | Type    | Null | Key | Default | Extra |
    +-------+---------+------+-----+---------+-------+
    | ID    | int(11) | NO   | PRI | 0       |       |
    +-------+---------+------+-----+---------+-------+
    1 row in set (0.00 sec)

    mysql> select * from TST;
    +----+
    | ID |
    +----+
    |  3 |
    +----+
    1 row in set (0.00 sec)

    看到数据已同步过来了，MYSQL 中，表名有大小写区分，开始报表名TST找不到，是在建立表中使用的是tablename:tst