ORA-20782: Oracle GoldenGate DDL Replication Error: Code :ORA-20782: Cannot DROP object used in Oracle GoldenGate replication while trigger is enabled.

Steps to drop goldengate user/schema when DDL trigger enabled.

If we try to drop goldengate user we will get below error message as below.

SQL> drop user ogguser cascade;
drop user ogguser cascade
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 2
ORA-20782: Oracle GoldenGate DDL Replication Error: Code :ORA-20782: Cannot
DROP object used in Oracle GoldenGate replication while trigger is enabled.
Consult Oracle GoldenGate documentation and/or call Oracle GoldenGate Technical
Support if you wish to do so., error stack: ORA-06512: at line 305
ORA-06512: at line 1267

There are two options to drop it. 

1. Drop trigger manually and then drop goldengate user.

2. Run goldengate scripts to disable/remove trigger and remove marker.

Option 1 Demo:-

SQL> select * from dba_triggers where owner='OGGUSER';

no rows selected

SQL> select a. obj#, a.sys_evts, b.name from trigger$ a, obj$ b

  2  where a.sys_evts >0 

  3  and a.obj#=b.obj#

  4  and baseobject=0;

      OBJ#   SYS_EVTS NAME

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

     13999    96 SYSLSBY_EDS_DDL_TRIG

     16766     524256 LOGMNRGGC_TRIGGER

     18195 4096 AW_TRUNC_TRG

     18197 8192 AW_REN_TRG

     18199   128 AW_DROP_TRG

     19200 4224 XDB_PI_TRIG

     20279 12512 NO_VM_DDL

     20280   128 NO_VM_DROP_A

     73495   128 OJDS$ROLE_TRIGGER$

     76034    64 CDC_ALTER_CTABLE_BEFORE

     76035    32 CDC_CREATE_CTABLE_AFTER

     76036    32 CDC_CREATE_CTABLE_BEFORE

     76037   128 CDC_DROP_CTABLE_BEFORE

     86599     524256 SDO_GEOR_ADDL_TRIGGER

     86687   128 SDO_NETWORK_DROP_USER

     86598     524256 SDO_GEOR_BDDL_TRIGGER

     86340   128 SDO_TOPO_DROP_FTBL

     83383   128 SDO_DROP_USER

     83659    32 SDO_ST_SYN_CREATE

     87298   128 LBAC$AFTER_DROP

     87299    64 LBAC$BEFORE_ALTER

     87300    32 LBAC$AFTER_CREATE

     92054     524256 GGS_DDL_TRIGGER_BEFORE

select owner, trigger_name, trigger_type, triggering_event, table_owner, table_name, base_object_type from dba_triggers where trigger_name='GGS_DDL_TRIGGER_BEFORE'

OWNER    TRIGGER_NAME      TRIGGER_TYPE     TRIGGERING_EVENT    TABLE_OWNER       TABLE_NAME BASE_OBJECT_TYPE

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

SYS    GGS_DDL_TRIGGER_BEFORE    BEFORE EVENT     DDL    SYS DATABASE

SQL> drop trigger sys.GGS_DDL_TRIGGER_BEFORE;

Trigger dropped.

SQL> drop user ogguser cascade;

User dropped.

SQL> 

Option 2 demo:- 

Go to OGG Installation location and open SQL prompt and login as sysdba 

run below 3 scripts

1)@ddl_disable.sql
2)@ ddl_remove.sql
3)@marker_remove.sql

SQL> drop user ogguser cascade;

drop user ogguser cascade

*

ERROR at line 1:

ORA-00604: error occurred at recursive SQL level 2

ORA-20782: Oracle GoldenGate DDL Replication Error: Code :ORA-20782: Cannot

DROP object used in Oracle GoldenGate replication while trigger is enabled.

Consult Oracle GoldenGate documentation and/or call Oracle GoldenGate Technical

Support if you wish to do so., error stack: ORA-06512: at line 305

ORA-06512: at line 1267

SQL> @ddl_disable.sql

Trigger altered.

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

Working, please wait ...

Spooling to file ddl_remove_spool.txt

Script complete.

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

PL/SQL procedure successfully completed.

Sequence dropped.

Table dropped.

Script complete.

SQL> --- now we can drop the goldengate user/schema

SQL> 

SQL> drop user ogguser cascade;

User dropped.

Sometimes , if you get below error message after drop trigger, please disconnect/logout of current session and  open new session and try to drop user as shown below.

SQL> drop user ogguser cascade;

drop user ogguser cascade

*

ERROR at line 1:

ORA-00604: error occurred at recursive SQL level 1

ORA-14452: attempt to create, alter or drop an index on temporary table already in use

SQL> exit

Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,

Advanced Analytics and Real Application Testing options

[oracle@rac1 gghome]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Thu Dec 20 03:38:36 2018

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

Connected to:

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,

Advanced Analytics and Real Application Testing options

SQL> drop user ogguser cascade;

User dropped.

SQL> 

hope, this helps.

References:-

Unable to drop GoldenGate user: ORA-20782: Cannot DROP object used in Oracle GoldenGate replication while trigger is enabled (Doc ID 1924642.1)

http://orabliss.blogspot.com/2015/08/dropping-goldengate-user-schema.html

http://dbarohit.blogspot.com/2013/07/error-ora-20782-while-deleting-golden.html