goldengate开启DDL配置@ddl_setup.sql执行报错

开启DDL配置:
1、关闭数据库回收站:

SQL>alter system set recyclebin=off scope=both;

2、编辑globals参数文件,标明支持DDL的GG对象存放在哪个schema下:

GGSCI>view param ./globals
GGSCHEMA ogg  

如果由于某些特殊的需求自定义DDL相关对象的名称的话,可以通过重定义位于GoldenGate软件安装目录下params.sql文件中的相关变量值,再配置GLOBALS,新增对象名称的对应关系,例如:

MARKERTABLE <new_table_name>
DDLTABLE <new_table_name>

前者表示Marker表,后一个表示DDL历史表。
一般情况下不需要修改,ORACLE也建议保持数据库对象默认值,该操作为可选操作
3、执行所需脚本
备忘清单

marker_setup.sql
ddl_setup.sql
role_setup.sql
Grant the role to all GoldenGate Extract users
ddl_enable.sql
install and use the optional performance tool
$ cd /home/oracle/ogg
$ sqlplus / as sysdba;
marker_setup.sql

该脚本用于创建DDL的marker表GGS_MARKER,用于存储DDL信息,该表只进行insert操作

SQL> @marker_setup
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: ogg
Marker setup table script complete, running verification script...
Please enter the name of a schema for the GoldenGate database objects:
Setting schema name to OGG
MARKER TABLE
-------------------------------
OK
MARKER SEQUENCE
-------------------------------
OK
Script complete.

ddl_setup.sql该脚本创建了进行DDL复制抽取和复制所需的对象

SQL> @ddl_setup
GoldenGate DDL Replication setup script
Verifying that current user has privileges to install DDL Replication...
Checking user sessions...
Check complete.
You will be prompted for the name of a schema for the 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 GoldenGate schema name: ogg
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
Using ogg as a GoldenGate schema name, INITIALSETUP as a mode of installation.
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
DDLORA_GETTABLESPACESIZE STATUS:
.....................
.....................
Analyzing installation status...
STATUS OF DDL REPLICATION
-----------------------------------------------------------------------------------
SUCCESSFUL installation of DDL Replication software components
Script complete.

role_setup.sql创建DDL复制所需的对象

SQL> @role_setup
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: ogg
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 <loggedUser>
where <loggedUser> is the user assigned to the GoldenGate processes.

按照上面返回的提示,执行授权操作,注意是将权限授予执行Extract/GGSCI或其它管理操作的用户,我们这里的情况看显然是ggate,执行命令如下:

SQL> GRANT GGS_GGSUSER_ROLE TO goldengate;
Grant succeeded.

ddl_enable.sql启用DDL复制,实质上是创建触发器,用以想MARKER和HISTORY表插入DDL信息

SQL> @ddl_enable
Trigger altered.

安装性能优化工具 (可选项)
要提高DDL触发器的性能,可以通过ddl_pin脚本,该脚本会将触发器使用的包加载到内存,以此提高效率。该脚本执行时需要引用dbms_shared_pool系统包,因此在使用ddl_pin脚本前需要确保dbms_shared_pool可用。

SQL> @?/rdbms/admin/dbmspool.sql
Package created.
Grant succeeded.
View created.
Package body created.

执行ddl_pin脚本需要指定GoldenGate管理员schema名称,例如:

SQL> @ddl_pin goldengate
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.

开启DDL配置@ddl_setup.sql执行报错的解决方案:
–先禁用DDL

@ddl_disable.sql

–再显示赋权

grant create table,create sequence to goldengate;

然后重新执行@ddl_setup.sql即可解决!