ITPub博客
首页 博客 专栏 学院 社区 IT新媒体评选
发博

首页 > 数据库 > Oracle > (转)GoldenGate配置DDL复制

(转)GoldenGate配置DDL复制

原创 Oracle 作者：wg0411 时间：2017-04-27 15:32:57 0 删除 编辑

在上一篇文件中的OGG单向复制配置不支持DLL的同步，只支持DML，因而本文在之前的基础上增加对DDL语句的复制，下面是简要配置过程记录！

一：验证之前的配置不支持DDL复制，这里在source端，新建一张表，发现无法复制到target端！target端也新建相同的表后，DML操作可以成功复制


点击(此处)折叠或打开

  1. [root@db1 ~]# su - oracle
  2. [oracle@db1 ~]$ sqlplus hr/hr
  3. SQL> create table t2 (id number primary key,name varchar2(20));
  4. Table created.
  5. SQL> conn hr/hr@db2
  6. Connected.
  7. SQL> select tname from tab where tname='T2';
  8. no rows selected
  9.  
  10. SQL> create table t2 (id number primary key,name varchar2(20));
  11. Table created.
  12.  
  13. SQL> conn hr/hr
  14. Connected.
  15. SQL> insert into t2 values (1,'one');
  16. 1 row created.
  17.  
  18. SQL> commit;
  19. Commit complete.
  20.  
  21. SQL> conn hr/hr@db2
  22. Connected.
  23. SQL> select * from t2;
  24.  
  25.         ID NAME
  26. ---------- --------------------
  27.          1 one


二：开始配置OGG支持DDL复制(在source端操作)
1：赋予ogg用户相应的权限，修改全局配置文件添加ggschema参数

点击(此处)折叠或打开

  1. SQL> conn /as sysdba
  2. Connected.
  3. SQL> grant execute on utl_file to ogg;
  4. Grant succeeded.
  5.  
  6. [oracle@db1 ~]$ cd $GGATE
  7. [oracle@db1 ogg]$ ggsci
  8. GGSCI (db1) 1> edit param ./GLOBALS
  9.  
  10. GGSCI (db1) 2> view param ./GLOBALS
  11. ggschema ogg



2：运行相关的sql脚本

点击(此处)折叠或打开

  1. [oracle@db1 ~]$ cd $GGATE
  2. [oracle@db1 ogg]$ sqlplus /nolog
  3. SQL> conn /as sysdba
  4. Connected.
  5. SQL> @marker_setup.sql
  6.  
  7. Marker setup script
  8. You will be prompted for the name of a schema for the Oracle GoldenGate database objects.
  9. NOTE: The schema must be created prior to running this script.
  10. NOTE: Stop all DDL replication before starting this installation.
  11.  
  12. Enter Oracle GoldenGate schema name:ogg
  13.  
  14. Marker setup table script complete, running verification script...
  15. Please enter the name of a schema for the GoldenGate database objects:
  16. Setting schema name to OGG
  17.  
  18. MARKER TABLE
  19. -------------------------------
  20. OK
  21.  
  22. MARKER SEQUENCE
  23. -------------------------------
  24. OK
  25.  
  26. Script complete.
  27.  
  28. SQL> show parameter recyclebin;
  29.  
  30. NAME TYPE VALUE
  31. ------------------------------------ ----------- ------------------------------
  32. recyclebin string on
  33.  
  34. SQL> alter system set recyclebin=off;
  35. System altered.
  36.  
  37. SQL> show parameter recyclebin;
  38.  
  39. NAME TYPE VALUE
  40. ------------------------------------ ----------- ------------------------------
  41. recyclebin string OFF
  42.  
  43. SQL> @ddl_setup.sql
  44. Oracle GoldenGate DDL Replication setup script
  45. Verifying that current user has privileges to install DDL Replication...
  46. You will be prompted for the name of a schema for the Oracle GoldenGate database objects.
  47. NOTE: For an Oracle 10g source, the system recycle bin must be disabled. For Oracle 11g and later, it can be enabled.
  48. NOTE: The schema must be created prior to running this script.
  49. NOTE: Stop all DDL replication before starting this installation.
  50. Enter Oracle GoldenGate schema name:ogg
  51.  
  52. Working, please wait ...
  53. Spooling to file ddl_setup_spool.txt
  54.  
  55. Checking for sessions that are holding locks on Oracle Golden Gate metadata tables ...
  56. Check complete.
  57. Using OGG as a Oracle GoldenGate schema name.
  58. Working, please wait ...
  59. RECYCLEBIN must be empty.
  60. This installation will purge RECYCLEBIN for all users.
  61. To proceed, enter yes. To stop installation, enter no.
  62.  
  63. Enter yes or no:yes
  64. ————————其他输出省略————————
  65.  
  66. STATUS OF DDL REPLICATION
  67. ---------------------------------------------------------------------------------------
  68. SUCCESSFUL installation of DDL Replication software components
  69.  
  70. Script complete.
  71.  
  72. SQL> @role_setup.sql
  73. GGS Role setup script
  74. This script will drop and recreate the role GGS_GGSUSER_ROLE
  75. To use a different role name, quit this script and then edit the params.sql script to change the gg_role parameter to the preferred name. (Do not run the script.)
  76.  
  77. You will be prompted for the name of a schema for the GoldenGate database objects.
  78. NOTE: The schema must be created prior to running this script.
  79. NOTE: Stop all DDL replication before starting this installation.
  80.  
  81. Enter GoldenGate schema name:ogg
  82. Wrote file role_setup_set.txt
  83. PL/SQL procedure successfully completed.
  84.  
  85. Role setup script complete
  86.  
  87. Grant this role to each user assigned to the Extract, GGSCI, and Manager processes, by using the following SQL command:
  88. GRANT GGS_GGSUSER_ROLE TO <loggedUser>
  89. where <loggedUser> is the user assigned to the GoldenGate processes.
  90.  
  91. SQL> grant ggs_ggsuser_role to ogg;
  92. Grant succeeded.
  93.  
  94. SQL> @ddl_enable.sql
  95. Trigger altered.
  96.  
  97. SQL> @?/rdbms/admin/dbmspool.sql
  98. Package created.
  99. Grant succeeded.
  100. View created.
  101. Package body created.
  102.  
  103. SQL> @ddl_pin.sql ogg
  104. PL/SQL procedure successfully completed.
  105. PL/SQL procedure successfully completed.
  106. PL/SQL procedure successfully completed.



3：source端修改extract进程的params文件，添加"ddl include all"参数，重启extract进程

点击(此处)折叠或打开

  1. GGSCI (db1) 1> view params eora_t1
  2. extract eora_t1
  3. setenv (NLS_LANG=AMERICAN_AMERICA.AL32UTF8)
  4. ddl include all
  5. userid ogg,password ogg
  6. exttrail ./dirdat/aa
  7. table hr.*;
  8.  
  9. GGSCI (db1) 2> stop extract eora_t1
  10. Sending STOP request to EXTRACT EORA_T1 ...
  11. Request processed.
  12.  
  13. GGSCI (db1) 3> start extract eora_t1
  14. Sending START request to MANAGER ...
  15. EXTRACT EORA_T1 starting
  16.  
  17. GGSCI (db1) 4> info extract eora_t1
  18. EXTRACT EORA_T1 Last Started 2012-06-20 15:42 Status RUNNING
  19. Checkpoint Lag 00:00:00 (updated 00:00:10 ago)
  20. Log Read Checkpoint Oracle Redo Logs
  21.                      2012-06-20 15:42:58 Seqno 3, RBA 50044416
  22.                      SCN 0.567478 (567478)



4：target端修改replicat进程的params文件，添加"ddl include all"和"ddlerror default ignore retryop maxretries 3 retrydelay 5" 参数，重启replicat进程

点击(此处)折叠或打开

  1. [root@db2 ~]# su - oracle
  2. [oracle@db2 ~]$ cd $GGATE
  3. [oracle@db2 ogg]$ ggsci
  4. GGSCI (db2) 1> edit params rora_t1
  5. GGSCI (db2) 2> view params rora_t1
  6. replicat rora_t1
  7. setenv (NLS_LANG=AMERICAN_AMERICA.AL32UTF8)
  8. ddl include all
  9. ddlerror default ignore retryop maxretries 3 retrydelay 5
  10. userid ogg,password ogg
  11. handlecollisions
  12. assumetargetdefs
  13. discardfile ./dirrpt/rora_t1.dsc,purge
  14. map hr.* ,target hr.*;
  15.  
  16. GGSCI (db2) 3> stop replicat rora_t1
  17. Sending STOP request to REPLICAT RORA_T1 ...
  18. Request processed.
  19.  
  20. GGSCI (db2) 4> start replicat rora_t1
  21. Sending START request to MANAGER ...
  22. REPLICAT RORA_T1 starting
  23.  
  24. GGSCI (db2) 5> info replicat rora_t1
  25. REPLICAT RORA_T1 Last Started 2012-06-20 15:50 Status RUNNING
  26. Checkpoint Lag 00:00:00 (updated 00:00:00 ago)
  27. Log Read Checkpoint File ./dirdat/pa000000
  28.                      First Record RBA 4780973



三：测试

点击(此处)折叠或打开

  1. [oracle@db1 ogg]$ sqlplus hr/hr
  2. SQL> alter table t2 add location varchar2(200);
  3. Table altered.
  4.  
  5. SQL> conn hr/hr@db2
  6. Connected.
  7. SQL> desc t2
  8.  Name    Null     Type
  9.  ----------------------------------------- -------- ----------------------------
  10.  ID    NOT NULL   NUMBER
  11.  NAME             VARCHAR2(20)
  12.  LOCATION         VARCHAR2(200)
  13.  
  14. SQL> conn hr/hr
  15. Connected.
  16. SQL> create table t3 as select object_id,object_name from dba_objects;
  17.  
  18. Table created.
  19.  
  20. SQL> conn hr/hr@db2
  21. Connected.
  22. SQL> select tname from tab where tname='T3';
  23.  
  24. TNAME
  25. ------------------------------
  26. T3
  27.  
  28. SQL> desc t3;
  29.  Name     Null     Type
  30.  ----------------------------------------- -------- ----------------------------
  31.  OBJECT_ID          NUMBER
  32.  OBJECT_NAME        VARCHAR2(128)



来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/29578568/viewspace-2138117/ ，如需转载，请注明出处，否则将追究法律责任。

0
0
上一篇： GoldenGate DDL配置详解
下一篇： （转）GoldenGate配置 之DDL复制
请登录后发表评论 登录
全部评论
wg0411

注册时间：2014-03-31

  • 博文量
    235
  • 访问量
    536103

最新文章

支持我们 作者招募 用户协议 FAQ Contact Us

北京盛拓优讯信息技术有限公司. 版权所有  京ICP备16024965号-8 北京市公安局海淀分局网监中心备案编号：11010802021510 niuxiaotong@pcpop.com 17352615567 未成年人举报专区

广播电视节目制作经营许可证(京) 字第1234号 中国互联网协会会员

违法和不良信息举报电话:18101376593