因OGG ddl触发器 导致所有user不能ddl操作

描述：

   2014-06-05： 早上过来，通过操作，所有user用户，包括sys,system dba用户组，不能对用户进行类似ddl 操作，

包括 grant、alter、impdp/expdp、drop、truncate等操作：即使所属user用户有对应的权限，表空间等，告警日志alter正常：

一  报错日志：

每次DDL 操作，都设计触发报错ORA-604  / ORA-1950

二 分别通过DBA_USERS. V$OPEN_CURSOR, V$TABLESPACE,V$DATAFILE 查看对应信息，都显示正常：

三 查看ogg 应用，通过前任DBA 了解，说已经禁用掉，但通过气日志，发现还有信息在写入：

SESS 4294967295-2014-06-05 14:22:49 : DDL : Cleaning up marker sequence [25933]

SESS 4294967295-2014-06-05 14:22:49 : DDL : Cleaned up [0] rows from marker table

SESS 4294967295-2014-06-05 14:22:49 : DDL : Cleaning up DDL sequence [25452]

SESS 4294967295-2014-06-05 14:22:49 : DDL : Cleaned up [0] rows from DDL table

SESS 4294967295-2014-06-05 14:29:43 : DDL : ************************* Start of log for DDL sequence [25453], v[ OGGCORE_12.1.2.0.0_PLATFORMS_130924.1316] trace level [0], owner schema of DDL package [GOLDENGATE], objtype [TABLESPACE] name [SYS.GOLDENGATE]

SESS 4294967295-2014-06-05 14:29:43 : DDLTRACE1 : Before Trigger: point in execution = [1.0], objtype [TABLESPACE] name [SYS.GOLDENGATE]

SESS 4294967295-2014-06-05 14:29:43 : DDL : DDL operation [drop tablespace goldengate including contents and datafiles ], sequence [25453], DDL type [DROP] TABLESPACE, real object type [TABLESPACE], validity [], object ID [], object [SYS.GOLDENGATE], real object [SYS.GOLDENGATE], base object schema [], base object name [], logged as [SYS]

SESS 4294967295-2014-06-05 14:29:43 : DDL : insertToMarker: :ORA-01950: no privileges on tablespace 'GOLDENGATE'

SESS 4294967295-2014-06-05 14:29:43 : DDL : saveMarkerDDL: :ORA-01950: no privileges on tablespace 'GOLDENGATE'

SESS 4294967295-2014-06-05 14:29:43 : DDL : Trigger sys.GGS_DDL_TRIGGER_BEFORE :Error processing DDL operation [drop tablespace goldengate including contents and datafiles ], error ORA-01950: no privileges on tablespace 'GOLDENGATE', error stack: ORA-06512: at "GOLDENGATE.DDLREPLICATION", line 1272

ORA-06512: at line 834

SESS 4294967295-2014-06-05 14:29:43 : DDL : Cleaning up marker sequence [25934]

SESS 4294967295-2014-06-05 14:29:43 : DDL : Cleaned up [0] rows from marker table

SESS 4294967295-2014-06-05 14:29:43 : DDL : Cleaning up DDL sequence [25453]

SESS 4294967295-2014-06-05 14:29:43 : DDL : Cleaned up [0] rows from DDL table

四  因DDL 操作触发报错，由此想到是否audit 审计打开，发现可以忽略次产生的影响。

CIFPAY@cifpay >show parameter audit

NAME TYPE VALUE

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

audit_file_dest string /dba/app/oracle/admin/cifpay/a

       dump

audit_sys_operations boolean FALSE

audit_syslog_level string

audit_trail string DB

五  通过v$open_cursor  ,发现日志，设计ogg 的一些触发机制序列：

36 000000045EEDD720 1145 SYS 0000000459CC23D8 3485739311 01uy9sb7w8a9g begin dbms_aqadm_sys.remove_all_nondurablesub(:1, :2); OPEN-RECURSIVE

37 000000045EB24620 303 SYS 000000045D8833F8 1875257684 1kh2am1rwcaan SELECT "GOLDENGATE" . "GGS_DDL_SEQ" .NEXTVAL FROM DUAL PL/SQL CURSOR CACHED

38 0000000456B32648 131 SYS 000000045D8833F8 1875257684 1kh2am1rwcaan SELECT "GOLDENGATE" . "GGS_DDL_SEQ" .NEXTVAL FROM DUAL PL/SQL CURSOR CACHED

39 0000000457041948 1399 SYS 000000045D8833F8 1875257684 1kh2am1rwcaan SELECT "GOLDENGATE" . "GGS_DDL_SEQ" .NEXTVAL FROM DUAL PL/SQL CURSOR CACHED

40 0000000457041948 1399 SYS 000000045D8833F8 1875257684 1kh2am1rwcaan SELECT "GOLDENGATE" . "GGS_DDL_SEQ" .NEXTVAL FROM DUAL PL/SQL CURSOR CACHED

41 0000000457041948 1399 SYS 000000045D8833F8 1875257684 1kh2am1rwcaan SELECT "GOLDENGATE" . "GGS_DDL_SEQ" .NEXTVAL FROM DUAL PL/SQL CURSOR CACHED

42 0000000454C15668 540 SYS 000000045D8833F8 1875257684 1kh2am1rwcaan SELECT "GOLDENGATE" . "GGS_DDL_SEQ" .NEXTVAL FROM DUAL PL/SQL CURSOR CACHED

43 0000000454C15668 540 SYS 000000045D8833F8 1875257684 1kh2am1rwcaan SELECT "GOLDENGATE" . "GGS_DDL_SEQ" .NEXTVAL FROM DUAL PL/SQL CURSOR CACHED

44 0000000456D39B08 637 SYS 000000045BDD50F8 3489409389 aykvshm7zsabd select size_for_estimate, size_factor * OPEN-RECURSIVE

45 000000045EB2A7E0 297 SYS 00000004599B8CF0 356401299 9m7787camwh4m begin :id := sys.dbms_transaction.local_transaction_id; end; DICTIONARY LOOKUP CURSOR CACHED

六  查了百度，google，类似的问题几乎没有，在涉及ORA-604报错，多半说是Oracle内部问题，说是，一条sql 执行后，Oracle内部需要做一些复杂的转换，到时类似一些视图，或者dba_* 表不存在所致， 需要修改initORACLE_SID.ORA 参数:

引用网上解决方法：

ORA-00604 error occurred at recursive SQL level string 
　　Cause:An error occurred while processing a recursive SQL statement (a statement applying to internal dictionary tables). 
　　Action:If the situation described in the next error on the stack can be corrected, do so; otherwise contact Oracle 　Customer Support. 
　　ORA-00604: 递归某个SQL 层时出现错误 
　　原因:在运行一条递归SQL语句(该语句将应用于对内部表或数据字典的操作)时，发生错误。 
　　方案:如果上述描述的错误所在栈可以被修复，则修复并继续运行;否则，请联系Oracle客服。当然，那是Oracle官方的解决办法。我曾经记得有个高手总结了关于ORA-00604/ORA-04031问题的解决：
　　修改INIT.ora 
　　添加 
 　　_db_handles_cached = 0

　　并重新启动数据库. 
　　分析:ORA-00604这个信息表明，在数据库执行内部SQL语句时，发生了错误。比如，要往表中插入一行数据，但没有可扩展的空间。ORACLE于是去查寻，哪儿可以建立下一个扩展空间，它有多大小，但没有成功。一般在发生ORA-00604错误时，还伴随着其它的错误，例　　如:ORA-1547等。 
　　首先，应当检查警告文件alertSID.log，查找有关ORA-600类的信息。 
　　该错误最常见的原因是数据库文件initSID.ora中的参数OPEN_CURSORS值太小。可以修改initSID.ora文件，OPEN_CURSORS的值一般为255。修改完后，宕下ORACLE，再重新启动。 
　　还可以设置并启动数据

七  以上方法，需要重启库，这对生产数据库来说，与其不方便，而且很麻烦， 因公司前段应用设计nginx, jboss, 重启数据库后，需要重启jboss服务。 最后通过吴sir 大湿的帮助，mos账号查到这篇类似文章：

DDL fails because of GoldenGate DDL trigger with error: ORA-01950: no privileges on tablespace (文档 ID 1513413.1)转到底部

修改时间:2013-1-7类型:PROBLEM

In this Document

Symptoms

Cause

Solution

APPLIES TO:Oracle GoldenGate - Version 11.2.1.0.5 and later

Information in this document applies to any platform.

SYMPTOMSIt all started when I trying to create a package (including two procedures) in a schema called 'DB_TEAM' which default tablespace is TOOLS. When I try to create the package as SYS I get the following error:

CREATE OR REPLACE PACKAGE MYAPPS.my_package

*

ERROR at line 1:

ORA-00604: error occurred at recursive SQL level 1

ORA-01950: no privileges on tablespace 'GOLDENGATE'

ORA-06512: at line 999 I then tried to grant quota to MYAPPS on goldengate tablespace:

alter user db_team quota unlimited on goldengate;

*

ERROR at line 1:

ORA-00604: error occurred at recursive SQL level 1

ORA-01950: no privileges on tablespace 'GOLDENGATE'

ORA-06512: at line 999 We have a tablespace named GOLDENGATE and we have a user named GOLDENGATE with this tablespace as a default tablespace. The tablespace also contains objects used by GoldenGate. I have also tried to grant quota on goldengate to SYS:

alter user sys quota unlimited on goldengate;

*

ERROR at line 1:

ORA-00604: error occurred at recursive SQL level 1

ORA-01950: no privileges on tablespace 'GOLDENGATE'

ORA-06512: at line 999 CAUSE This is quota privilege issue on the schema where GoldenGate DDL is installed, which is 'goldengate' in above case.SOLUTIONThe DDL trigger needs to be disabled before altering the user with unlimited quota privilege, because 'alter user' itself is a DDL. This will require a application DDL down time, i.e., when the DDL trigger is disabled, no DDL will be captured by GoldenGate.

login as sysdbaalter trigger GGS_DDL_TRIGGER_BEFORE disable;alter user goldengate quota unlimited on goldengate;alter trigger GGS_DDL_TRIGGER_BEFORE enable;

八 ： 问题明显了，禁用或者删除这OGG 用户的ddl 触发器，可以解决。