遭遇因Oracle Bug導致以sysoper許可權無法登陸資料庫的故障。將問題現象和處理方法記錄在此，供參考。

1.問題現象
1）sysdba許可權可以正常登入
ora10g@asdlabdb01 /home/oracle$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.3.0 - Production on Sun Dec 19 21:05:25 2010

Copyright (c) 1982, 2006, Oracle. All Rights Reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP and Data Mining Scoring Engine options

sys@ora10g>

2）sysoper許可權無法登入
（1）使用sqlplus嘗試登陸報錯
ora10g@asdlabdb01 /home/oracle$ sqlplus / as sysoper

SQL*Plus: Release 10.2.0.3.0 - Production on Sun Dec 19 21:05:35 2010

Copyright (c) 1982, 2006, Oracle. All Rights Reserved.

ERROR:
ORA-01075: you are currently logged on

Enter user-name:

（2）使用connect嘗試連線報錯
sys@ora10g> conn / as sysoper
ERROR:
ORA-01075: you are currently logged on

Warning: You are no longer connected to ORACLE.
sys@ora10g>

這裡莫名其妙的丟擲了“ORA-01075”錯誤，明明是第一次以sysoper許可權登入，為什麼報出這個錯誤。

2.問題原因
這是由於系統中存在logon型別的觸發器，並且該觸發器沒有對sysoper進行特殊處理導致的。問題本身是Oracle的一個Bug，該Bug在MOS中有記錄，請參考“Bug 3826570: GETTING ORA-01075: YOU ARE CURRENTLY LOGGED ON WHILE CONNECTING AS SYSOPER”。

獲取資料庫中logon觸發型別的trigger（注意查詢條件中的“空格”）。
sys@ora10g> select * from dba_triggers where TRIGGERING_EVENT='LOGON ';

OWNER                          TRIGGER_NAME                   TRIGGER_TYPE
------------------------------ ------------------------------ ----------------
TRIGGERING_EVENT
-----------------------------------------------------------------------------------------------------------------------------------
TABLE_OWNER                    BASE_OBJECT_TYPE TABLE_NAME                     COLUMN_NAME
------------------------------ ---------------- ------------------------------ ------------------------------
REFERENCING_NAMES
--------------------------------------------------------------------------------------------------------------------------------
WHEN_CLAUSE
-----------------------------------------------------------------------------------------------------------------------------------
STATUS
--------
DESCRIPTION
-----------------------------------------------------------------------------------------------------------------------------------
ACTION_TYPE
-----------
TRIGGER_BODY
-----------------------------------------------------------------------------------------------------------------------------------
SEC                            USER_CONNECT_AUDIT_TRIGGER     AFTER EVENT
LOGON
SYS                            DATABASE
REFERENCING NEW AS NEW OLD AS OLD

ENABLED
user_connect_audit_trigger
   after logon
   on database
PL/SQL
declare
   v_connection_ip varchar2 (20);
   v_host varchar2 (20);
begin
   select sys_context ('userenv', 'ip_address') into v_connection_ip from dual;
   select sys_context ('userenv', 'host') into v_host from dual;
   insert into audit_user_connect
     values   (user,
               sys_context ('userenv', 'sessionid'),
               v_host,
               v_connection_ip,
               sysdate,
               to_char (sysdate, 'hh24:mi:ss'));
   commit;
end;

該觸發器是我用於審計使用者登入而編寫的。沒想到正是因為這個觸發器觸發了Oracle Bug。小心，Bug無處不在。

3.問題處理
有兩種處理方法，最簡單的處理方法是disable或刪除掉觸發器，另外一種方法是在觸發器中對sysoper單獨處理。
處理方法如下：
（1）停用logon型別的觸發器
sys@ora10g> alter trigger SEC.USER_CONNECT_AUDIT_TRIGGER disable;

Trigger altered.

（2）重新嘗試連線成功
使用SQL*Plus嘗試連線成功。
ora10g@asdlabdb01 /home/oracle$ sqlplus / as sysoper

SQL*Plus: Release 10.2.0.3.0 - Production on Sun Dec 19 21:21:43 2010

Copyright (c) 1982, 2006, Oracle. All Rights Reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP and Data Mining Scoring Engine options

public@ora10g>

使用connect嘗試連線成功。
sys@ora10g> conn / as sysoper
Connected.
public@ora10g>

問題處理完畢。

4.小結
Oracle也在不斷的進步之中，因此遍佈到各個角落的Bug也是偶有遇到。不一定什麼時候便可以和Oracle的Bug邂逅。我們能做到沉著並冷靜的面對就好。

Good luck.

secooler
10.12.19

-- The End --

【問題處理】以SYSOPER許可權登入資料庫觸發ORA-01075錯誤

相關文章