11.2.0.1升级到11.2.0.4后，进行验证，建表报错： ora-04045 & ora-00604 & ora-06508
最后通过ogg重建ddl功能解决
报错详情如下
sql> create table t(a int);
create table t(a int)
*
error at line 1:
ora-00604: error occurred at recursive sql level 1
ora-04045: errors during recompilation/revalidation of ggs.ddlreplication
ora-06508: pl/sql: could not find program unit being called:
"ggs.ddlreplication"
ora-06512: at line 1181
ora-04067: not executed, package body "ggs.ddlreplication" does not exist
ora-06508: pl/sql: could not find program unit being called:
"ggs.ddlreplication"
ora-06508: pl/sql: could not find program unit being called:
"ggs.ddlreplication"
ora-06512: at line 1042
ora-04045: errors during recompilation/revalidation of ggs.ddlreplication
ora-04067: not executed, package body "ggs.ddlreplication" does not exist
ora-06508: pl/sql: could not find program unit being called:
"ggs.ddlreplication"
引起原因
数据库升级，未先关掉ogg的ddl功能导致（11.2.0.1升级到11.2.0.4）
解决办法
ps:若ogg进程还在运行，先关掉（使用stop xx 命令）
ggsci (dbtest) 1> info all program status group lag at chkpt time since chkpt manager stopped extract abended dump 00:00:03 132:08:27 extract abended ext1 00:00:00 132:08:20 replicat stopped rep 71:33:43 47:42:56 ggsci (dbtest) 2>
从ogg的主目录进入sqlplus, 使用ddl_disable.sql禁用ddl触发器
[oracle@dbtest ggs]$ pwd
/mnt/oggs/ggs
[oracle@dbtest ggs]$ sqlplus / as sysdba
sql*plus: release 11.2.0.4.0 production on wed jul 8 16:14:16 2020
米乐app官网下载 copyright (c) 1982, 2013, oracle. all rights reserved.
connected to:
oracle database 11g enterprise edition release 11.2.0.4.0 - 64bit production
with the partitioning, olap, data mining and real application testing options
sql> @ddl_disable.sql
trigger altered.
使用ddl_remove.sql移除ddl触发器，ddl历史和标记表，这个脚本会生成一个ddl_remove_spool.txt文件，记录了脚本的输出，还有一个ddl_remove_set.txt文件，记录了当前的用户环境设置，以防debug之用。
sql> @ddl_remove.sql ddl replication removal script. warning: this script removes all ddl replication objects and data. 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. enter oracle goldengate schema name:ogg working, please wait ... spooling to file ddl_remove_spool.txt script complete.
使用marker_remove.sql 移除对oracle goldenget marker系统的支持
sql> @marker_remove.sql marker removal script. warning: this script removes all marker objects and data. 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. enter oracle goldengate schema name:ggs pl/sql procedure successfully completed. sequence dropped. table dropped. script complete.
使用marker_setup.sql 安装marker
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:ggs
marker setup table script complete, running verification script...
please enter the name of a schema for the goldengate database objects:
setting schema name to ggs
marker table
-------------------------------
ok
marker sequence
-------------------------------
ok
script complete.
给ogg权限（重建不需要执行）
grant create table,create sequence to ogg;
安装ddl触发器
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:ggs
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 ggs 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 ggs
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
---------------------------------------- -----------------------------------------------------------------
no errors no errors
ddl ignore table
-----------------------------------
ok
ddl ignore log table
-----------------------------------
ok
ddlaux package status:
line/pos error
---------------------------------------- -----------------------------------------------------------------
no errors no errors
ddlaux package body status:
line/pos error
---------------------------------------- -----------------------------------------------------------------
no errors no errors
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
-----------------------------------
ok
ddl history table(1)
-----------------------------------
ok
ddl dump tables
-----------------------------------
ok
ddl dump columns
-----------------------------------
ok
ddl dump log groups
-----------------------------------
ok
ddl dump partitions
-----------------------------------
ok
ddl dump primary keys
-----------------------------------
ok
ddl sequence
-----------------------------------
ok
ggs_temp_cols
-----------------------------------
ok
ggs_temp_uk
-----------------------------------
ok
ddl trigger code status:
line/pos error
---------------------------------------- -----------------------------------------------------------------
no errors no errors
ddl trigger install status
-----------------------------------
ok
ddl trigger running status
------------------------------------------------------------------------------------------------------------------------
enabled
staymetadata in trigger
------------------------------------------------------------------------------------------------------------------------
off
ddl trigger sql tracing
------------------------------------------------------------------------------------------------------------------------
0
ddl trigger trace level
------------------------------------------------------------------------------------------------------------------------
0
location of ddl trace file
------------------------------------------------------------------------------------------------------------------------
/mnt/vdb1/oracle/diag/rdbms/nip/nip/trace/ggs_ddl_trace.log
analyzing installation status...
version of ddl replication
------------------------------------------------------------------------------------------------------------------------
oggcore_12.1.2.0.0_platforms_130924.1316
status of ddl replication
------------------------------------------------------------------------------------------------------------------------
successful installation of ddl replication software components
script complete.
安装角色
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:ggs
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.
启动ddl触发器
sql> @ddl_enable.sql trigger altered.
验证测试（新建表->插入数据->查询->清空表->删除表）
sql> create table t(a int);
table created.
sql> insert into t value(1);
insert into t value(1)
*
error at line 1:
ora-00928: missing select keyword
sql> insert into t values (1);
1 row created.
sql> commit;
commit complete.
sql> select * from t;
a
----------
1
1 row selected.
sql> truncate table t;
table truncated.
sql> select * from t;
no rows selected
sql> drop table t;
table dropped.
sql> select * from t;
select * from t
*
error at line 1:
ora-00942: table or view does not exist
sql>
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【米乐app官网下载的版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。