Drop goldengate user times ORA-00604 ORA-20782 ORA-06512 problems resolved, ora06512

Drop goldengate user times ORA-00604 ORA-20782 ORA-06512 problems resolved, ora06512
1. Problem

SQL> drop user goldengate cascade;

When Drop goldengate user, the ORA-00604 ORA-20782 ORA-06512 error is reported, the specific error content is as follows:

Drop user goldengate 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 261 ORA-06512: at line 1111

2. Cause Analysis

When OGG is installed, the DDL capture function is configured and enabled, while the DDL capture function of OGG is implemented by the DDL trigger, and the DDL is in the enabled state, drop goldengate user operations are also DDL operations, so ORA-00604 ORA-20782 errors are generated

3. Verify the DDL trigger status

SQL> set linesize 999

SQL> select owner, trigger_name, trigger_type, triggering_event, status from dba_triggers where trigger_name like 'ggs % ';

OWNER TRIGGER_NAME TRIGGER_TYPE TRIGGERING_EVENT STATUS --------------------------------------------------------------------------------------------------------- SYS GGS_DDL_TRIGGER_BEFORE BEFORE EVENT DDL ENABLED

4. delete a trigger

SQL> drop trigger sys. GGS_DDL_TRIGGER_BEFORE

5. Try again to delete the user

SQL> drop user goldengate cascade;

Drop user goldengate 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

An error is reported again, but the error message has changed.

The error indicates an attempt to create, change, or delete an index in a temporary table in use.

6. Find the session using the temporary table and killsession

SQL> select 'alter system kill session ''' | sid | ',' | serial # | ''' from v $ session where sidin

(Select sid from v $ lock where id1in

(Select object_id from dba_objectswhere object_name in (select table_name from dba_tableswhere owner = 'goldengat ')));

7. delete a user again

SQL> drop user goldengate cascade;

User dropped.

 

Deleted successfully.

 

 

Author: LI Junjie (Network Name: Step-by-Step), engaged in "system architecture, operating system, storage device, database, middleware, application" six levels of systematic performance optimization work

Join the system performance optimization professional group to discuss performance optimization technologies. GROUP: 258187244