standby database 中的登录触发器导致 ORA-00604 ORA-16000

最新推荐文章于 2022-09-21 17:13:46 发布

milia

最新推荐文章于 2022-09-21 17:13:46 发布

阅读量5.5k

点赞数

分类专栏： oracle学习文章标签： database session terminal oracle events action

本文链接：https://blog.csdn.net/milia/article/details/3184942

版权

oracle学习专栏收录该内容

20 篇文章 0 订阅

订阅专栏

前段事件公司dba说登录 standby库用非dba 身份进入时报出ORA-00604 ORA-16000错误，经过核查，发现是登录触发器的原因，现将该错复现如下：

在primary库中存在登录触发器如下：

conn milia/xxxx

create table session_history
(
USERNAME   varchar2(20),
SID    number,
AUDSID    number,
OSUSER    varchar2(30),
ACTION   varchar2(100),
LOGIN_TIME   date,
LOGOFF_TIME   date,
IP   varchar2(20),
TERMINAL   varchar2(30),
PROGRAM   varchar2(100)
)

CREATE OR REPLACE TRIGGER LOGON_HISTORY
AFTER LOGON ON DATABASE --WHEN (USER='EQSP') --ONLY FOR USER 'EQSP'
BEGIN
insert into session_history
select username,sid,AUDSID,OSUSER,ACTION,SYSDATE,null,SYS_CONTEXT('USERENV','IP_ADDRESS'),TERMINAL,PROGRAM
from v$session s
where audsid = userenv( 'sessionid' )
and s.USERNAME NOT IN ('SYSTEM','SYS','REPADMIN')
AND S.USERNAME IS NOT NULL;
END;

standy database以只读方式打开

conn milia/xxxx

连接成功了（因为milia是dba权限的用户，即使报错也可以绕开登录触发器的错误登录系统），但是查看alert日志，报错误如下：

Errors in file /opt/ora9/product/9.2/rdbms/log/test_ora_2410.trc:
ORA-00604: error occurred at recursive SQL level 1
ORA-16000: database open for read-only access
ORA-06512: at line 2
***Warning - Executing transaction without active Undo Tablespace

trace 登录过程

alter session set events '10046 trace name context forever ,level 12'

conn milia/miniyal

alter session set events '10046 trace name context off' ;

查看trace文件

/opt/ora9/product/9.2/rdbms/log/test_ora_2410.trc
Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 - Production
ORACLE_HOME = /opt/ora9/product/9.2
System name:    Linux
Node name:      localhost.localdomain
Release:        2.4.21-4.EL
Version:        #1 Fri Oct 3 18:13:58 EDT 2003
Machine:        i686
Instance name: test
Redo thread mounted by this instance: 1
Oracle process number: 10
Unix process pid: 2410, image: oracle@localhost.localdomain (TNS V1-V3)

*** SESSION ID:(9.5) 2008-10-29 21:49:33.801
Skipped error 604 during the execution of MILIA.LOGON_HISTORY
*** 2008-10-29 21:49:33.802
ksedmp: internal or fatal error
ORA-00604: error occurred at recursive SQL level 1
ORA-16000: database open for read-only access
ORA-06512: at line 2

发现确实是登录触发器的问题