在使用FGA的HANDLER呼叫Procedure時，注意不要在其中嵌入commit語句和DDL語句，否則在使用FGA的過程中將會邂逅難得一遇的600錯誤（ORA-00600: internal error code, arguments: [4412], [0x37A140E8], [0x37A13BCC], [], [], [], [], []），此問題歸因於Oracle的一個Bug，Bug號：6407682。模擬再現一下這個Bug。

1.資料庫版本資訊
sec@ora10g> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE    10.2.0.1.0      Production
TNS for Linux: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production

2.建立待審計表T
sec@ora10g> create table t (x number(10), y varchar2(10));

Table created.

sec@ora10g> create table mon_t (x date);

Table created.

3.建立FGA使用的儲存過程PROC_MON_T，注意這類我們加入了一條commit語句
sec@ora10g> create or replace procedure PROC_MON_T (schema_name varchar2,table_name varchar2, policy varchar2) as
2 begin
3 insert into mon_t values(sysdate);
4 commit;
5 end;
6 /

Procedure created.

4.建立FGA審計策略
sec@ora10g> conn / as sysdba
Connected.
sys@ora10g> begin
2 DBMS_FGA.ADD_POLICY (
3 object_schema => 'SEC',
4 object_name => 'T',
5 policy_name => 'audit_t',
6 audit_condition => 'X < 100',
7 audit_column => 'X',
8 HANDLER_SCHEMA=>'SEC',
9 HANDLER_MODULE=>'PROC_MON_T',
10 enable => TRUE,
11 statement_types => 'INSERT, UPDATE, DELETE'
12 );
13 end;
14 /

PL/SQL procedure successfully completed.

5.觸發審計
sys@ora10g> conn sec/sec
Connected.
sec@ora10g> insert into t values (1,'secooler');
insert into t values (1,'secooler')
    *
ERROR at line 1:
ORA-00600: internal error code, arguments: [4412], [0x37A140E8], [0x37A13BCC], [], [], [], [], []

此處已經丟擲了代號為4412的600錯誤。

6.alert日誌中記錄的資訊
Sat Jun 18 23:12:48 2011
Errors in file /oracle/ora10gR2/admin/ora10g/udump/ora10g_ora_6667.trc:
ORA-00600: internal error code, arguments: [4412], [0x37A140E8], [0x37A13BCC], [], [], [], [], []

7.trace檔案中的資訊
ora10g@secdb /home/oracle$ vi /oracle/ora10gR2/admin/ora10g/udump/ora10g_ora_6667.trc
/oracle/ora10gR2/admin/ora10g/udump/ora10g_ora_6667.trc
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
ORACLE_HOME = /oracle/ora10gR2/product/10.2.0/db_2
System name:    Linux
Node name:      secdb
Release:        2.6.18-194.el5
Version:        #1 SMP Mon Mar 29 20:06:41 EDT 2010
Machine:        i686
Instance name: ora10g
Redo thread mounted by this instance: 1
Oracle process number: 13
Unix process pid: 6667, image: oracle@secdb (TNS V1-V3)

*** ACTION NAME:() 2011-06-18 23:12:48.809
*** MODULE NAME:(SQL*Plus) 2011-06-18 23:12:48.809
*** SERVICE NAME:(SYS$USERS) 2011-06-18 23:12:48.809
*** SESSION ID:(153.1283) 2011-06-18 23:12:48.809
*** 2011-06-18 23:12:48.809
ksedmp: internal or fatal error
ORA-00600: internal error code, arguments: [4412], [0x37A140E8], [0x37A13BCC], [], [], [], [], []
Current SQL statement for this session:
insert into t values (:"SYS_B_0",:"SYS_B_1")
----- Call Stack Trace -----
calling              call     entry                argument values in hex
location             type     point                (? means dubious value)
-------------------- -------- -------------------- ----------------------------
ksedst()+27          call     ksedst1()            0 ? 1 ?
ksedmp()+557         call     ksedst()             0 ? B7F0C504 ? CBD2D20 ?
                                                   C763BF8 ? 0 ? D5E24A4 ?
ksfdmp()+19          call     ksedmp()             3 ? BFD91B24 ? AC152A0 ?
                                                   CBD2D20 ? 3 ? CB84398 ?
……此處省略其他僅4萬行資訊……

KSOLS: Begin dumping all object level stats elements
KSOLS: Done dumping all elements. Exiting.
Dump event group for SESSION
Dump event group for SYSTEM
-----------------------------------
Error during execution of handler in Fine Grained Auditing
Audit handler : begin SEC.PROC_MON_T(:sn, :on, :pl); end;
Error Number 1 : 600
Logon user     : SEC
Object Schema: SEC, Object Name: T, Policy Name: AUDIT_T

8.問題原因
在MOS上查到了這個Bug的描述資訊。文章描述該Bug在11.1.0.7和10.2.0.4版本上被確認。看樣子這個問題遠遠不侷限在這兩個版本上。
Bug 6407682 - ORA-600 [4412] performing DDL/commit inside an FGA handler [ID 6407682.8]

9.處理方法
可以暫時將“PROC_MON_T”中的commit語句去掉。針對此例也不影響資料提交。

10.小結
Oracle的小Bug無處不在，在體驗新功能的同時與Bug為伍亦是樂趣。當然這裡祝福大家不要在生產環境“觸雷”，要做到知之、避之！

Good luck.

secooler
11.06.18

-- The End --

【FGA】FGA handler中使用commit或DDL語句導致ORA-600 [4412]錯誤的再現及處理

相關文章