oracle GoldenGate系列：（一）ogg的安装与配置

  • 西红柿炒番茄QAQ

    2016-10-15 21:07:51
  • Oracle
  • 原创
一、安装Oracle goldengate
直接解压即可
  1. [oracle@tomato-@ORACLE_SID ggs]$ ls
  2. fbo_ggs_Linux_x86_ora11g_32bit.tar 
  3. [oracle@tomato-@ORACLE_SID ggs]$ tar -xvf fbo_ggs_Linux_x86_ora11g_32bit.tar
  4. [oracle@tomato-@ORACLE_SID ggs]$ rm -rf fbo_ggs_Linux_x86_ora11g_32bit.tar
二、数据库中的配置与准备
打开数据库最小附加日志（源端）

  1. SQL> select supplemental_log_data_min from v$database;

  3. SUPPLEME
  4. --------
  5. NO

  7. SQL> alter database add supplemental log data;

  9. Database altered.

  11. SQL> ALTER SYSTEM ARCHIVE LOG CURRENT;
  12. System altered.

建立OGG数据库用户并授予权限（源端）

  1. SQL> CREATE USER goldengate IDENTIFIED BY goldengate;

  3. User created.

  5. SQL> GRANT CONNECT TO goldengate;

  7. Grant succeeded.

  9. SQL> GRANT CREATE SESSION TO goldengate;

  11. Grant succeeded.

  13. SQL> GRANT ALTER SESSION TO goldengate;

  15. Grant succeeded.

  17. SQL> GRANT RESOURCE TO goldengate;

  19. Grant succeeded.

  21. SQL> GRANT SELECT ANY DICTIONARY TO goldengate;

  23. Grant succeeded.

  25. SQL> GRANT SELECT ANY TABLE TO goldengate;

  27. Grant succeeded.

  29. SQL> GRANT FLASHBACK ANY TABLE TO goldengate;

  31. Grant succeeded.

  33. SQL> GRANT ALTER ANY TABLE TO goldengate;

  35. Grant succeeded.
建立OGG数据库用户并授予权限（目标端）
这里goldengate所需要的权限要比源端的多
  1. SQL> CREATE USER goldengate IDENTIFIED BY goldengate;

  3. User created.

  5. SQL> GRANT ALTER SESSION TO goldengate;

  7. Grant succeeded.

  9. SQL> GRANT CREATE SESSION TO goldengate;

  11. Grant succeeded.

  13. SQL> GRANT CONNECT TO goldengate;

  15. Grant succeeded.

  17. SQL> GRANT RESOURCE TO goldengate;

  19. Grant succeeded.

  21. SQL> GRANT SELECT ANY DICTIONARY TO goldengate;

  23. Grant succeeded.

  25. SQL> GRANT SELECT ANY TABLE TO goldengate;

  27. Grant succeeded.

  29. SQL> GRANT FLASHBACK ANY TABLE TO goldengate;

  31. Grant succeeded.

  33. SQL> GRANT INSERT ANY TABLE TO goldengate;

  35. Grant succeeded.

  37. SQL> GRANT UPDATE ANY TABLE TO goldengate;

  39. Grant succeeded.

  41. SQL> GRANT DELETE ANY TABLE TO goldengate;

  43. Grant succeeded.

三、goldengate中的配置
1.目标和源的准备
执行./ggsci
  1. [oracle@tomato-@ORACLE_SID ggs]$ ./ggsci
  2. ./ggsci: error while loading shared libraries: libnnz11.so: cannot open shared object file: No such file or directory
ps：若是报错，在oracle的profile文件加入一行语句即可
  1. [oracle@tomato-@ORACLE_SID ggs]$ vi /home/oracle/profile
  2. export LD_LIBRARY_PATH=$ORACLE_HOME/lib
  3. [oracle@tomato-@ORACLE_SID ggs]$ . /home/oracle/profile
  4. [oracle@tomato-@ORACLE_SID ggs]$ ./ggsci
  5. Oracle GoldenGate Command Interpreter for Oracle
  6. Version 11.2.1.0.1 OGGCORE_11.2.1.0.1_PLATFORMS_120423.0230_FBO
  7. Linux, x86, 32bit (optimized), Oracle 11g on Apr 23 2012 08:09:25

  9. Copyright (C) 1995, 2012, Oracle and/or its affiliates. All rights reserved.

  11. GGSCI (tomato.oracle.com) 1>
创建所需文件夹
  1. GGSCI (tomato.oracle.com) 1> create subdirs

  3. Creating subdirectories under current directory /oracle/ggs

  5. Parameter files /oracle/ggs/dirprm: already exists
  6. Report files /oracle/ggs/dirrpt: created
  7. Checkpoint files /oracle/ggs/dirchk: created
  8. Process status files /oracle/ggs/dirpcs: created
  9. SQL script files /oracle/ggs/dirsql: created
  10. Database definitions files /oracle/ggs/dirdef: created
  11. Extract data files /oracle/ggs/dirdat: created
  12. Temporary files /oracle/ggs/dirtmp: created
  13. Stdout files /oracle/ggs/dirout: created
配置Manager进程并打开
  1. GGSCI (potato.oracle.com) 2> edit param mgr
  2. port 7809              --指定端口号
  3. GGSCI (linfan) 7> START MGR

  5. Manager started.
2.目标端的配置
加入trandata（表级附加日志）

  1. GGSCI (potato.oracle.com) 4> dblogin userid goldengate , password goldengate
  2. Successfully logged into database.
  3. GGSCI (potato.oracle.com) 5> add trandata lala.*

  5. 2017-03-06 20:11:24 WARNING OGG-00869 No unique key is defined for table 'STUDENT'. All viable columns will be used to represent the key, but may not guarantee uniqueness. KEYCOLS may be used to define the key.

  7. Logging of supplemental redo data enabled for table LALA.STUDENT.
ps：这里的WARNING是因为lala用户下的student表没有主键，所以把所有的列都加入附加日志
添加EXTRACT进程并配置

  1. GGSCI (potato.oracle.com) 6> ADD EXTRACT capdb1, TRANLOG, BEGIN NOW                         ---添加进程
  2. EXTRACT added.
  3. GGSCI (potato.oracle.com) 7> ADD EXTTRAIL /oracle/ggs/dirdat/lt, EXTRACT capdb1             ---改进使用本地队列
  4. EXTTRAIL added.
  5. GGSCI (potato.oracle.com) 8> EDIT PARAM capdb1                                              ---配置进程参数
  6. EXTRACT capdb1
  7. SETENV (ORACLE_HOME = "/u01/app/oracle/product/11.2.0/dbhome_1")
  8. SETENV (ORACLE_SID = DB1) 
  9. USERID goldengate, PASSWORD goldengate
  10. EXTTRAIL /oracle/ggs/dirdat/lt
  11. TABLE lala.*
添加配置pump进程
  1. GGSCI (potato.oracle.com) 9> ADD EXTRACT pumpdb12, EXTTRAILSOURCE /oracle/ggs/dirdat/lt, BEGIN NOW
  2. EXTRACT added.

  4. GGSCI (potato.oracle.com) 10> ADD RMTTRAIL /oracle/ggs/dirdat/rt, EXTRACT pumpdb12
  5. RMTTRAIL added.

  7. GGSCI (potato.oracle.com) 11> EDIT PARAM pumpdb12
  8. EXTRACT pumpdb12
  9. SETENV (ORACLE_HOME = "/u01/app/oracle/product/11.2.0/dbhome_1") 
  10. SETENV (ORACLE_SID = DB1) 
  11. USERID goldengate, PASSWORD goldengate
  12. RMTHOST tomato, MGRPORT 7809
  13. rmttrail /oracle/ggs/dirdat/rt
  14. TABLE lala.*
将配置好的两个进程开启
  1. GGSCI (potato.oracle.com) 49> start capdb1

  3. Sending START request to MANAGER ...
  4. EXTRACT CAPDB1 starting

  6. GGSCI (potato.oracle.com) 50> start pumpdb12

  8. Sending START request to MANAGER ...
  9. EXTRACT PUMPDB12 starting

  11. GGSCI (potato.oracle.com) 51> info all

  13. Program Status Group Lag at Chkpt Time Since Chkpt

  15. MANAGER RUNNING 
  16. EXTRACT RUNNING CAPDB1 00:38:04 00:00:01 
  17. EXTRACT RUNNING PUMPDB12 00:00:00 00:00:00
3.配置源端
添加进程REPLICAT进程
  1. GGSCI (tomato.oracle.com) 10> dblogin userid goldengate , password goldengate                   ---首先先要登录
  2. Successfully logged into database.
  3.  
  4. GGSCI (tomato.oracle.com) 11> ADD CHECKPOINTTABLE goldengate.checkpoint                          ---添加检查点表

  6. Successfully created checkpoint table goldengate.checkpoint.

  8. GGSCI (tomato.oracle.com) 12> ADD REPLICAT repdb2, EXTTRAIL /oracle/ggs/dirdat/rt, BEGIN NOW, CHECKPOINTTABLE goldengate.checkpoint
  9. REPLICAT added.                                                                                ---添加REPLICAT进程，并且使用CHECKPOINTTABLE方式
  10. GGSCI (tomato.oracle.com) 13> EDIT PARAM repdb2                                                ---配置REPLICAT进程
  11. REPLICAT repdb2
  12. SETENV (ORACLE_HOME = "/u01/app/oracle/product/11.2.0/dbhome_1")
  13. SETENV (ORACLE_SID = DB2)
  14. ASSUMETARGETDEFS
  15. USERID goldengate, PASSWORD goldengate
  16. MAP lala.*, TARGET lala.*
开启该进程
  1. GGSCI (tomato.oracle.com) 14> start repdb2

  3. Sending START request to MANAGER ...
  4. REPLICAT REPDB2 starting

  6. GGSCI (tomato.oracle.com) 15> info all

  8. Program Status Group Lag at Chkpt Time Since Chkpt

  10. MANAGER RUNNING 
  11. REPLICAT RUNNING REPDB2 00:00:00 00:00:03
四、进行环境测试
源端插入一条数据

  1. SQL> insert into student values(1,'zhangsan');

  3. 1 row created.

  5. SQL> commit
  6. Commit complete.
目标端查看得到这条数据

  1. GGSCI (tomato.oracle.com) 18> stats repdb2

  3. Sending STATS request to REPLICAT REPDB2 ...

  5. Start of Statistics at 2017-03-06 23:49:04.

  7. Replicating from LALA.STUDENT to LALA.STUDENT:

  9. *** Total statistics since 2017-03-06 21:42:56 ***
  10.         Total inserts 1.00
  11.         Total updates 0.00
  12.         Total deletes 0.00
  13.         Total discards 0.00
  14.         Total operations 1.00

  16. *** Daily statistics since 2017-03-06 21:42:56 ***
  17.         Total inserts 1.00
  18.         Total updates 0.00
  19.         Total deletes 0.00
  20.         Total discards 0.00
  21.         Total operations 1.00

  23. *** Hourly statistics since 2017-03-06 23:00:00 ***

  25.         No database operations have been performed.

  27. *** Latest statistics since 2017-03-06 21:42:56 ***
  28.         Total inserts 1.00
  29.         Total updates 0.00
  30.         Total deletes 0.00
  31.         Total discards 0.00
  32.         Total operations 1.00

  34. End of Statistics.

  1. SQL> select * from student;

  3.         ID NAME
  4. ---------- ---------------
  5.          1 zhangsan

环境搭建成功


请使用浏览器的分享功能分享到微信等