goldengate 觸發器導致oracle 表空間不能online

OS：linux

oracle version：11.2.0.1

由於磁碟空間問題，將一個表空間offline ，在將表空間online 的時候，報錯如下，

alter tablespace tbs_name online
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-00376: file 179 cannot be read at this time
ORA-01110: data file 179:
'/opt/oracle/oradata/ora/tbs_data_03.dbf'
ORA-06512: at line 957
ORA-00376: file 179 cannot be read at this time
ORA-01110: data file 179:
'/opt/oracle/oradata/ora/tbs_data_03.dbf'

檢視'/opt/oracle/oradata/ora/tbs_data_03.dbf' 檔案，也存在

ll /opt/oracle/oradata/ora/tbs_data_03.dbf

-rw-r----- 1 oracle dba 26843553792 Jul 20 10:25 tbs_data_03.dbf'

SQL> recover datafile 179;
ORA-00283: recovery session canceled due to errors
ORA-00264: no recovery required

確認檔案不需要恢復。

沒有找到任何思路。去trace 目錄下，看看有沒有線索

在該目錄下，發現一個異常的檔案ggs_ddl_trace.log，開啟發現如下內容：

SESS 16893430-2011-07-20 11:28:06 : DDL : Cleaning up DDL sequence []
SESS 16893430-2011-07-20 11:28:06 : DDL : Cleaned up [0] rows from DDL table
SESS 16893431-2011-07-20 11:28:06 : DDL : setTracing: :ORA-00376: file 179 cannot be read at this time
ORA-01110: data file 179: /opt/oracle/oradata/ora/tbs_data_03.dbf''
SESS 16893431-2011-07-20 11:28:06 : DDL : Trigger sys.GGS_DDL_TRIGGER_BEFORE :Error processing DDL operation [], error ORA-00376: file 179 cannot be read at this time
ORA-01110: data file 179: '/opt/oracle/oradata/ora/tbs_data_03.dbf'', error stack: ORA-06512: at "GGDATA.DDLREPLICATION", line 1057
ORA-06512: at line 92

很明顯sys.GGS_DDL_TRIGGER_BEFORE 這個trigger 導致了這個問題。

alter trigger sys.GGS_DDL_TRIGGER_BEFORE  disable;

alter tablespace tbs_name online;

成功。

資料庫的日誌目錄下，有很多隱含的資訊，對我們處理問題有很大的幫助！