Friday, March 6, 2015

GoldenGate - Error ORA-20782 while deleting Golden Gate User


Error ORA-20782 while deleting Golden Gate User

While dropping GoldenGate replicated user, I got an error.  Details are given below.

SQL> select distinct owner
     from dba_segments
     where owner in (select username
                from dba_users
                where default_tablespace not in ('SYSTEM','SYSAUX')
                ) ;


OWNER
------------------------------
GGTEST
OGGSOURCE
OGGTARGET


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

Solution

Since I had run the ddl_setup.sql which created the DDL trigger for Golden Gate user schema.
So before deleting the Golden Gate replicated user, we need to drop the Golden Gate DDL trigger first.

SQL> SELECT * FROM DBA_TRIGGERS
WHERE OWNER='GGTEST';
no rows selected

SQL>  SELECT A.OBJ#, A.SYS_EVTS, B.NAME
FROM TRIGGER$ A,OBJ$ B
WHERE A.SYS_EVTS > 0
AND A.OBJ#=B.OBJ#
AND BASEOBJECT = 0;

      OBJ#   SYS_EVTS NAME
---------- ---------- ------------------------------
     11990     524256 LOGMNRGGC_TRIGGER
     13175       4096 AW_TRUNC_TRG
     13177       8192 AW_REN_TRG
     13179        128 AW_DROP_TRG
     13885       8416 NO_VM_DDL
     13886        128 NO_VM_DROP_A
     55420         64 CDC_ALTER_CTABLE_BEFORE
     55421         32 CDC_CREATE_CTABLE_AFTER
     55422         32 CDC_CREATE_CTABLE_BEFORE
     55423        128 CDC_DROP_CTABLE_BEFORE
     56322         96 EXPFIL_RESTRICT_TYPEEVOLVE
     56323       8256 EXPFIL_ALTEREXPTAB_MAINT
     57549       4224 XDB_PI_TRIG
     56320        128 EXPFIL_DROPOBJ_MAINT
     56321        128 EXPFIL_DROPUSR_MAINT
     58846       4096 RLMGR_TRUNCATE_MAINT
     63703        128 SDO_DROP_USER
     63971         32 SDO_ST_SYN_CREATE
     63807        128 SDO_TOPO_DROP_FTBL
     68065     524256 SDO_GEOR_BDDL_TRIGGER
     68066     524256 SDO_GEOR_ADDL_TRIGGER
     68143        128 SDO_NETWORK_DROP_USER
     71613          1 MGMT_STARTUP
     76915     524256 GGS_DDL_TRIGGER_BEFORE

24 rows selected.

SQL> drop trigger GGS_DDL_TRIGGER_BEFORE;
Trigger dropped.

SQL> drop user GGTEST cascade;
User dropped.


No comments:

Post a Comment