[Oracle 11g r2(11.2.0.4.0)]Oracle Golden Gate Training-workshop3

最新推荐文章于 2022-09-28 16:01:28 发布

返璞归真素闲人

最新推荐文章于 2022-09-28 16:01:28 发布

阅读量421

点赞数

分类专栏： oracle

本文链接：https://blog.csdn.net/a743044559/article/details/78420600

版权

oracle 专栏收录该内容

103 篇文章 2 订阅

订阅专栏

learn by doing,less theory,more results.

install configure:

这里写图片描述

OGG单向DDL支持：

source 源端

1、禁用Recycle Bin:

如果数据库是10g，需要关闭recyclebin并重启；或者手工purge recyclebin。
oracle 11g没做要求。

2、安装ddl支持配置

2.1、创建同于记录DDL信息表

--脚本1：
SQL> @marker_setup.sql;
--输入我们之前创建的用户名：

2.2、创建相关的存储过程和触发器

--脚本2：
SQL> @ddl_setup.sql;
--提示输入GG的用户：
--这里让我们选择安装模式： install 和 reinstall 选择INITIALSETUP


SQL> @ddl_setup.sql;

Oracle GoldenGate DDL Replication setup script

Verifying that current user has privileges to install DDL Replication...

You will be prompted for the name of a schema for the Oracle GoldenGate database objects.
NOTE: For an Oracle 10g source, the system recycle bin must be disabled. For Oracle 11g and later, it can be enabled.
NOTE: The schema must be created prior to running this script.
NOTE: Stop all DDL replication before starting this installation.

Enter Oracle GoldenGate schema name:ogg

Working, please wait ...
Spooling to file ddl_setup_spool.txt

Checking for sessions that are holding locks on Oracle Golden Gate metadata tables ...

Check complete.

Using OGG as a Oracle GoldenGate schema name.

Working, please wait ...

DDL replication setup script complete, running verification script...
Please enter the name of a schema for the GoldenGate database objects:
Setting schema name to OGG

CLEAR_TRACE STATUS:

Line/pos                                 Error
---------------------------------------- -----------------------------------------------------------------
No errors                                No errors

CREATE_TRACE STATUS:

Line/pos                                 Error
---------------------------------------- -----------------------------------------------------------------
No errors                                No errors

TRACE_PUT_LINE STATUS:

Line/pos                                 Error
---------------------------------------- -----------------------------------------------------------------
No errors                                No errors

INITIAL_SETUP STATUS:

Line/pos                                 Error
---------------------------------------- -----------------------------------------------------------------
No errors                                No errors

DDLVERSIONSPECIFIC PACKAGE STATUS:

Line/pos                                 Error
---------------------------------------- -----------------------------------------------------------------
No errors                                No errors

DDLREPLICATION PACKAGE STATUS:

Line/pos                                 Error
---------------------------------------- -----------------------------------------------------------------
No errors                                No errors

DDLREPLICATION PACKAGE BODY STATUS:

Line/pos                                 Error
---------------------------------------- -----------------------------------------------------------------
1453/9                                   PL/SQL: SQL Statement ignored
1455/23                                  PL/SQL: ORA-00942: table or view does not exist
1464/9                                   PL/SQL: SQL Statement ignored
1466/23                                  PL/SQL: ORA-00942: table or view does not exist
1478/9                                   PL/SQL: SQL Statement ignored
1480/23                                  PL/SQL: ORA-00942: table or view does not exist
1485/9                                   PL/SQL: SQL Statement ignored
1487/23                                  PL/SQL: ORA-00942: table or view does not exist
1492/9                                   PL/SQL: SQL Statement ignored
1494/23                                  PL/SQL: ORA-00942: table or view does not exist
1499/9                                   PL/SQL: SQL Statement ignored

Line/pos                                 Error
---------------------------------------- -----------------------------------------------------------------
1501/23                                  PL/SQL: ORA-00942: table or view does not exist
1581/4                                   PL/SQL: SQL Statement ignored
1582/18                                  PL/SQL: ORA-00942: table or view does not exist
1584/4                                   PL/SQL: SQL Statement ignored
1585/18                                  PL/SQL: ORA-00942: table or view does not exist
1600/25                                  PL/SQL: ORA-00942: table or view does not exist
1600/4                                   PL/SQL: SQL Statement ignored
1602/25                                  PL/SQL: ORA-00942: table or view does not exist
1602/4                                   PL/SQL: SQL Statement ignored


DDL IGNORE TABLE
-----------------------------------
FAILED: Table does not exist

DDL IGNORE LOG TABLE
-----------------------------------
FAILED: Table does not exist

DDLAUX  PACKAGE STATUS:

Line/pos                                 Error
---------------------------------------- -----------------------------------------------------------------
No errors                                No errors

DDLAUX PACKAGE BODY STATUS:

Line/pos                                 Error
---------------------------------------- -----------------------------------------------------------------
0/0                                      PL/SQL: Compilation unit analysis terminated
1/21                                     PLS-00304: cannot compile body of 'DDLAUX' without its
                                         specification

1/21                                     PLS-00905: object OGG.DDLAUX is invalid


SYS.DDLCTXINFO  PACKAGE STATUS:

Line/pos                                 Error
---------------------------------------- -----------------------------------------------------------------
No errors                                No errors

SYS.DDLCTXINFO  PACKAGE BODY STATUS:

Line/pos                                 Error
---------------------------------------- -----------------------------------------------------------------
No errors                                No errors

DDL HISTORY TABLE
-----------------------------------
FAILED: Table does not exist

DDL HISTORY TABLE(1)
-----------------------------------
FAILED: Table does not exist

DDL DUMP TABLES
-----------------------------------
FAILED: Table does not exist

DDL DUMP COLUMNS
-----------------------------------
FAILED: Table does not exist

DDL DUMP LOG GROUPS
-----------------------------------
FAILED: Table does not exist

DDL DUMP PARTITIONS
-----------------------------------
FAILED: Table does not exist

DDL DUMP PRIMARY KEYS
-----------------------------------
FAILED: Table does not exist

DDL SEQUENCE
-----------------------------------
FAILED: Sequence does not exist

GGS_TEMP_COLS
-----------------------------------
FAILED: Table does not exist

GGS_TEMP_UK
-----------------------------------
FAILED: Table does not exist

DDL TRIGGER CODE STATUS:

Line/pos                                 Error
---------------------------------------- -----------------------------------------------------------------
126/9                                    PL/SQL: SQL Statement ignored
128/23                                   PL/SQL: ORA-00942: table or view does not exist
133/21                                   PL/SQL: ORA-02289: sequence does not exist
133/5                                    PL/SQL: SQL Statement ignored
657/14                                   PLS-00905: object OGG.DDLAUX is invalid
657/5                                    PL/SQL: Statement ignored
919/25                                   PL/SQL: ORA-00942: table or view does not exist
919/4                                    PL/SQL: SQL Statement ignored


DDL TRIGGER INSTALL STATUS
-----------------------------------
OK

DDL TRIGGER RUNNING STATUS
------------------------------------------------------------------------------------------------------------------------
ENABLED
FROM "OGG" ."GGS_SETUP"
            *
ERROR at line 2:
ORA-00942: table or view does not exist


FROM "OGG" ."GGS_SETUP"
            *
ERROR at line 2:
ORA-00942: table or view does not exist


FROM "OGG" ."GGS_SETUP"
            *
ERROR at line 2:
ORA-00942: table or view does not exist



LOCATION OF DDL TRACE FILE
------------------------------------------------------------------------------------------------------------------------
/u01/app/oracle/diag/rdbms/source/source/trace/ggs_ddl_trace.log

Analyzing installation status...


STATUS OF DDL REPLICATION
------------------------------------------------------------------------------------------------------------------------
ERRORS detected in installation of DDL Replication software components (6)

报了一堆错误，看不明白，暂时不处理。

2.3、创建DDL同步角色

--脚本3：
SQL> @role_setup.sql;

2.4、启用DDL触发器

--脚本4：
SQL> @ddl_enable.sql;

执行dml 语句

SQL> drop table t2;
drop table t2
*
ERROR at line 1:
ORA-04098: trigger 'SYS.GGS_DDL_TRIGGER_BEFORE' is invalid and failed
re-validation

查询相关资料，在11.2.0.4.0 需要设置:
1、ENABLE_GOLDENGATE_REPLICATION=true
2、
SQL > @ddl_disable;
SQL > GRANT CREATE TABLE,CREATE SEQUENCE TO OGG;
SQL > grant execute on utl_file to ogg;
SQL > @ddl_setup.sql;
SQL > @ddl_enable.sql;

2.5 、优化DDL 触发器性能

SQL>@ddl_pin ogg

3、修改source extract 参数

GGSCI (ogg1) 6> edit params ext1

extract ext1
dynamicresolution
setenv (NLS_.AL32UTF8)
userid ogg,password ogg
exttrail /u01/gg/dirdat/ex
ddl include all
ddloptions addtrandata, report
table bobo.*;

ddl include all：启劢ddl支持，并且包含所有的ddl操作

target 目标端

1、编辑target replicat 参数：

GGSCI (ogg2) 6> edit params rpt1

replicat rpt1
userid ogg,password oracle123
assumetargetdefs
reperror default,discard
discardfile /u01/gg/discards.dsc,append,megabytes 50
dynamicresolution
ddloptions report
ddlerror default ignore retryop maxretries 3 retrydelay 5
ddlerror default discard
ddlerror default ignore retryop
map bobo.*, target bobo.*;

注意：
1、如果在source端的过程或者函数有调用到别的用户资源，确保target有同样的权限。
2、在同步前，确认source 端业务用户已存在的表，因为atrget不会同步已存在的表数据。
3、如果在source执行create table语句，而没有创建主键，在ggser.log会有警告，创建主键主要是为了双向同步，如果单向同步可以忽略。

测试：

此种设置支持，create ,delete，drop ,alter table。

SQL> alter table sys_menu add remark varchar2(10);

Table altered.

查看源端source 日志
[root@localhost ~]# tail -f /u01/gg/ggser.log

2017-11-02 14:49:42  INFO    OGG-00487  Oracle GoldenGate Capture for Oracle, ext1.prm:  DDL operation included [include all], optype [ALTER], objtype [TABLE], objowner [BOBO], objname [SYS_MENU].
2017-11-02 14:49:42  INFO    OGG-00497  Oracle GoldenGate Capture for Oracle, ext1.prm:  Writing DDL operation to extract trail file.

查看目标端targe日志：
[root@localhost ~]# tail -f /u01/gg/ggser.log

2017-11-02 13:06:51  INFO    OGG-01408  Oracle GoldenGate Delivery for Oracle, rpt1.prm:  Restoring current schema for DDL operation to [ogg].
2017-11-02 14:50:05  INFO    OGG-00482  Oracle GoldenGate Delivery for Oracle, rpt1.prm:  DDL found, operation [alter table sys_menu add remark varchar2(10)  (size 45)].
2017-11-02 14:50:05  INFO    OGG-00489  Oracle GoldenGate Delivery for Oracle, rpt1.prm:  DDL is of mapped scope, after mapping new operation [alter table bobo."SYS_MENU" add remark varchar2(10)  (size 52)].
2017-11-02 14:50:05  INFO    OGG-01407  Oracle GoldenGate Delivery for Oracle, rpt1.prm:  Setting current schema for DDL operation to [bobo].
2017-11-02 14:50:05  INFO    OGG-00484  Oracle GoldenGate Delivery for Oracle, rpt1.prm:  Executing DDL operation.
2017-11-02 14:50:06  INFO    OGG-00483  Oracle GoldenGate Delivery for Oracle, rpt1.prm:  DDL operation successful.
2017-11-02 14:50:06  INFO    OGG-01408  Oracle GoldenGate Delivery for Oracle, rpt1.prm:  Restoring current schema for DDL operation to [ogg].

执行sql语句：
SQL> alter table sys_menu rename column remark to remarks;

Table altered.
查看源端source日志：

2017-11-02 14:59:56  INFO    OGG-01487  Oracle GoldenGate Capture for Oracle, ext1.prm:  DDL found, operation [alter table sys_menu rename column remark to remarks  (size 53)], start SCN [1395755], commit SCN [1395764] instance [source (1)], DDL seqno [67], marker seqno [559].
2017-11-02 14:59:56  INFO    OGG-00487  Oracle GoldenGate Capture for Oracle, ext1.prm:  DDL operation included [include all], optype [ALTER], objtype [TABLE], objowner [BOBO], objname [SYS_MENU].
2017-11-02 14:59:56  INFO    OGG-00497  Oracle GoldenGate Capture for Oracle, ext1.prm:  Writing DDL operation to extract trail file.

查看目标端target日志：

2017-11-02 15:00:18  INFO    OGG-00482  Oracle GoldenGate Delivery for Oracle, rpt1.prm:  DDL found, operation [alter table sys_menu rename column remark to remarks  (size 53)].
2017-11-02 15:00:18  INFO    OGG-00489  Oracle GoldenGate Delivery for Oracle, rpt1.prm:  DDL is of mapped scope, after mapping new operation [alter table bobo."SYS_MENU" rename column remark to remarks  (size 60)].
2017-11-02 15:00:18  INFO    OGG-01407  Oracle GoldenGate Delivery for Oracle, rpt1.prm:  Setting current schema for DDL operation to [bobo].
2017-11-02 15:00:18  INFO    OGG-00484  Oracle GoldenGate Delivery for Oracle, rpt1.prm:  Executing DDL operation.
2017-11-02 15:00:18  INFO    OGG-00483  Oracle GoldenGate Delivery for Oracle, rpt1.prm:  DDL operation successful.
2017-11-02 15:00:18  INFO    OGG-01408  Oracle GoldenGate Delivery for Oracle, rpt1.prm:  Restoring current schema for DDL operation to [ogg].

返璞归真素闲人

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
[Oracle 11g r2(11.2.0.4.0)]Oracle Golden Gate Training-workshop3

learn by doing,less theory,more results.install configure:开始DDL支持：禁用Recycle Bin:如果数据库是10g，需要关闭recyclebin并重启；或者手工purge recyclebin。 oracle 11g没做要求。--脚本1：SQL> @marker_setup.sql;--输入我们之前创建的用户名：--脚本2：SQ
复制链接

扫一扫