Drop goldengate使用者時報ORA-00604 ORA-20782 ORA-06512問題解決,ora06512
1、問題現象
SQL> drop user goldengate cascade;
Drop goldengate使用者時,報ORA-00604 ORA-20782 ORA-06512錯誤,具體報錯內容如下:
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、原因分析
由於在安裝OGG時,配置並開啟了DDL捕獲功能,而OGG的DDL捕獲,是依賴DDL觸發器實現的,DDL處於enabled狀態,drop goldengate user操作也屬於DDL操作,所以產生ORA-00604 ORA-20782錯誤
3、驗證DDL觸發器狀態
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、刪除觸發器
SQL>drop trigger sys.GGS_DDL_TRIGGER_BEFORE
5、再次嘗試刪除使用者
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 |
再次報錯,但是報錯內容發生了改變
報錯意思為:試圖建立,更改或刪除正在使用的暫存資料表中的索引
6、找出正在使用暫存資料表的會話,並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='GOLDENGATE')));
7、再次刪除使用者
SQL> drop user goldengate cascade;
User dropped.
刪除成功。
本文作者:黎俊傑(網名:踩點),從事”系統架構、作業系統、存放裝置、資料庫、中介軟體、應用程式“六個層面系統性的效能最佳化工作
歡迎加入 系統效能最佳化專業群,共同探討效能最佳化技術。群號:258187244