【问题处理】以SYSOPER权限登录数据库触发ORA-01075错误

最新推荐文章于 2021-04-12 02:54:17 发布

cuanchuwei1207

最新推荐文章于 2021-04-12 02:54:17 发布

阅读量529

点赞数

文章标签：数据库

遭遇因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 --

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/519536/viewspace-682188/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/519536/viewspace-682188/