GoldenGate学习笔记(7)_DDL支持安装与测试

 

<>P35

<>P150

 

 

1 DDL Support 安装要求

 

To install the GoldenGate DDL environment, you will be installing the database objects.

 

 

Do not include any GoldenGate-installed DDL objects in a DDL parameter, in a TABLE parameter, or in a MAP parameter, nor in a TABLEEXCLUDE or MAPEXCLUDE parameter. Make certain that wildcard specifications in those parameters do not include GoldenGate-installed DDL objects. These objects must not be part of the GoldenGate configuration, but the Extract process must be aware of operations on them, and that is why you must not explicitly exclude them from the configuration with an EXCLUDE, TABLEEXCLUDE, or MAPEXCLUDE parameter statement. (上表中所安装的所有database object不能出现在任何GG的配置，参数文件中．所以必须小心，假定这些对象安装在goldengate Schema下，则参数文件中不能用goldengate.*，　这样会包含DDL Support Object)

 

 

2 安装步骤( Source端与Target端都要安装 ?)

 

1 choose a GoldenGate schema or another schema for the DDL object.(goldengate)

 

2 choose a tablespace for the DDL objects. (users)

 

3 edit GLOBALS file

 

 

GGSCHEMA goldengate

 

4 turn off the Oracle recycle bin.

 

On Oracle 10g and up, system recycle bin must be disabled.

 

To turn off the recycle bin:

● Oracle 10g Release 2 and later: Set the RECYCLEBIN initialization parameter to OFF.

● Oracle 10g Release 1: Set the _RECYCLEBIN initialization parameter to FALSE.

 

 

5 change directories to the GoldenGate installation directory (cd /u01/ggs/)

 

6 sqlplus /nolog

 conn / as sysdba

 

7 run marker_setup.sql, enter GoldenGate schema(goldengate)

 

SQL> run marker_setup.sql

SP2-0103: Nothing in SQL buffer to run.

这什么问题?

 

Run 不对．

SQL> @marker_setup.sql

Marker setup script

 

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

NOTE: The schema must be created prior to running this script.

NOTE: Stop all DDL replication before starting this installation.

 

Enter GoldenGate schema name:goldengate

 

PL/SQL procedure successfully completed.

 

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

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

Setting schema name to GOLDENGATE

 

MARKER TABLE

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

OK

 

 

MARKER SEQUENCE

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

OK

 

 

Script. complete.

 

 

8 run ddl_setup.sql

 

enter schema:goldengate

choose installation mode: INITIALSETUP

 

SQL> @ddl_setup.sql

 

GoldenGate DDL Replication setup script

 

Enter GoldenGate schema name:goldengate

 

You will be prompted for the mode of installation.

To install or reinstall DDL replication, enter INITIALSETUP

To upgrade DDL replication, enter NORMAL

Enter mode of installation:INITIALSETUP

Working, please wait ...

Spooling to file ddl_setup_spool.txt

SUCCESSFUL installation of DDL Replication software components

 

 

Script. complete.

 

9 run role_setup.sql

 

SQL> @role_setup.sql

 

GGS Role setup script

 

This script. will drop and recreate the role GGS_GGSUSER_ROLE

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.)

 

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

NOTE: The schema must be created prior to running this script.

NOTE: Stop all DDL replication before starting this installation.

 

Enter GoldenGate schema name:goldengate

Wrote file role_setup_set.txt

 

PL/SQL procedure successfully completed.

 

 

Role setup script. complete

 

Grant this role to each user assigned to the Extract, GGSCI, and Manager processes, by using the following SQL command:

 

GRANT GGS_GGSUSER_ROLE TO

 

where is the user assigned to the GoldenGate processes.

 

 

 

10 grant role(GGS_GGSUSER_ROLE) to all GoldenGate Extract users;

 

SQL> GRANT GGS_GGSUSER_ROLE TO goldengate;

 

Grant succeeded.

 

11 run ddl_enable.sql

 

 

SQL> @ddl_enable.sql

 

Trigger altered.

 

 

两端都安装完成.

 

 

3 配置

3.1 Supported Oracle objects and operations for DDL replication
clusters

functions

indexes

packages

procedure

tables

tablespaces

roles

sequences

synonyms

triggers

types

views

materialized views

users

 

3.2 Non-supported Oracle objects and operations for DDL replication

 

Oracle-reserved schemas

Oracle recycle bin

 

3.3 Limitation of GG DDL Support

1)  GoldenGate supports DDL replication for uni-directional configurations. Bi-directional

replication of DDL is not supported.(只支持单向复制)

 

2)      GoldenGate supports DDL synchronization only in a like-to-like configuration.GoldenGate DDL support requires the following:

l         Source and target object definitions must be identical.

l         The ASSUMETARGETDEFS parameter must be used in the Replicat parameter file. Replicat will abend if objects are configured for DDL support and the SOURCEDEFS parameter is being used.( 必须在Replicat 参数文件中指定ASSUMETARGETDEFS 参数)

4 测试

在HR.test上添加一列

 

Source 端

Extmydb参数文件添加DDL support:

 

DDL include all

 

Dpemydb参数文件不变

TABLE HR.*;

 

 

Target 端 repmydb参数文件添加

DDL &

INCLUDE ALL

 

 

SQL> alter table test add sex varchar2(2);

 

Table altered.

 

Source 端

SQL> select * from test;

 

        ID NAME                 SE

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

         2 gdut

 

Target 端

SQL> select * from test;

 

        ID NAME                 SE

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

         2 gdut

 

更改成功

 

对DDL的支持细节比较多，

更多的内容参考文档<>P150

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

转载于:http://blog.itpub.net/10248702/viewspace-624579/