【OGG】OGG的单向复制配置-支持DDL(二)

实验环境介绍

实验部分

实验目标

配置2台服务器，搭建OGG,实现hr用户下的数据ddl和DML复制功能。

先验证之前的配置不支持DDL复制

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

[oracle@orcltest ~]$ sqlplus hr/hr@ogg1

SQL*Plus: Release 11.2.0.3.0 Production on Wed Jun 10 16:18:03 2015

Copyright (c) 1982, 2011, Oracle. All rights reserved.

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> create table t2 (id number primary key,name varchar2(20));

Table created.

SQL> conn hr/hr@ogg2

Connected.

SQL> select tname from tab where tname='T2';

no rows selected

SQL> create table t2 (id number primary key,name varchar2(20));

Table created.

SQL> conn hr/hr@ogg1

Connected.

SQL> insert into t2 values (1,'one');

1 row created.

SQL> commit;

Commit complete.

SQL> conn hr/hr@ogg2

Connected.

SQL> select * from t2;

ID NAME

---------- --------------------

1 one

SQL>

此时source库：

GGSCI (orcltest) 26> dblogin userid ggusr@ogg1,password lhr

Successfully logged into database.

GGSCI (orcltest) 29> info trandata hr.t2

Logging of supplemental redo log data is disabled for table HR.T2.

GGSCI (orcltest) 30>

开始配置OGG支持DDL复制(在source端操作)

赋予ggusr用户相应的权限，修改全局配置文件添加ggschema参数

[oracle@orcltest ~]$ sqlplus sys/lhr@ogg1 as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Wed Jun 10 16:27:05 2015

Copyright (c) 1982, 2011, Oracle. All rights reserved.

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> grant execute on utl_file to ggusr;

Grant succeeded.

SQL>

[oracle@orcltest gg11]$ ggsci

Oracle GoldenGate Command Interpreter for Oracle

Version 11.2.1.0.1 OGGCORE_11.2.1.0.1_PLATFORMS_120423.0230_FBO

Linux, x64, 64bit (optimized), Oracle 11g on Apr 23 2012 08:32:14

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

GGSCI (orcltest) 1> edit param ./GLOBALS

GGSCHEMA ggusr

~

~

~

~

。。。。。。。。。。。。。。。

~

~

~

~

"./GLOBALS" 1L, 15C written

GGSCI (orcltest) 2> view param ./GLOBALS

GGSCHEMA ggusr

GGSCI (orcltest) 3>

运行相关的sql脚本

如果想使用DDL功能，需要在之前运行支持DDL的相关脚本。

1.@marker_setup.sql

2.@ddl_setup.sql

3.@role_setup.sql

4.GRANT GGS_GGSUSER_ROLE TO gguser

5.@ddl_enable.sql

6.@?/rdbms/admin/dbmspool.sql

7.@ddl_pin.sql ggusr

[oracle@orcltest gg11]$ sqlplus sys/lhr@ogg1 as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Wed Jun 10 16:30:45 2015

Copyright (c) 1982, 2011, Oracle. All rights reserved.

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> @marker_setup.sql

Marker setup script

You will be prompted for the name of a schema for the Oracle GoldenGate database objects.

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:ggusr

Marker setup table script complete, running verification script...

Please enter the name of a schema for the GoldenGate database objects:

Setting schema name to GGUSR

MARKER TABLE

-------------------------------

OK

MARKER SEQUENCE

-------------------------------

OK

Script complete.

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:ggusr

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 GGUSR 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 GGUSR

CLEAR_TRACE STATUS:

Line/pos Error

-------------------- -----------------------------------------------------------------

No errors No errors

CREATE_TRACE STATUS:

后续精彩内容已被小麦苗无情隐藏，请输入验证码解锁本站所有文章！

验证码：

请先关注本站微信公众号，然后回复“验证码”，获取验证码。在微信里搜索“DB宝”或者“www_xmmup_com”或者微信扫描右侧二维码都可以关注本站微信公众号。