dg环境连接ORA-00604,ORA-16000: database open for read-only access

最新推荐文章于 2021-08-26 15:05:21 发布

weixin_34006965

最新推荐文章于 2021-08-26 15:05:21 发布

阅读量1.7k

点赞数

CC 4.0 BY-SA版权

文章标签：数据库 java

原文链接：http://www.cnblogs.com/lvcha001/p/10858305.html

本文探讨了Oracle数据库中审计功能的配置与影响，以及触发器导致的读写权限问题。通过实测，揭示了审计功能对连接的影响，并详细记录了解决由触发器引发的错误ORA-00604和ORA-16000的过程。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

报错信息

根据客户提供的报错信息，

ORA-00604: error occurred at recursive SQL level 1
ORA-16000: database open for read-only access

怀疑是触发器导致的问题，自己的测试环境进行测试。

测试一、dg环境开启审计参数是否影响

[oracle@adg1 dbs]$ sqlplus tt/tt@adgtns
SQL*Plus: Release 11.2.0.4.0 Production on Sat May 4 16:56:55 2019
SQL> 
audit_trail string OS

SQL> alter system set audit_trail=db scope=spfile;
System altered.
SQL> startup force;
DG环境，备库如果开启审计功能，配置db将自动转换为OS

SQL> audit select,insert,update,delete on scott.emp by access;
Audit succeeded

SQL> conn tt/tt
Connected.
SQL> select * from scott.emp where rownum=1;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- --------------------
7369 SMITH CLERK 7902 17-DEC-80 800 20
SQL> conn / as sysdba
Connected.
SQL> show parameter audit
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
audit_file_dest string /u01/app/oracle/admin/tt/adump

测试发现，备库开启审计功能，并不会导致无法连接。
Sat May 4 17:31:00 2019 +08:00
LENGTH: "267"
SESSIONID:[10] "4294967295" ENTRYID:[1] "1" STATEMENT:[1] "1" USERID:[2] "TT" USERHOST:[4] "adg1" TERMINAL:[5]
"pts/1" ACTION:[3] "100" RETURNCODE:[1] "0" COMMENT$TEXT:[26] "Authenticated by: DATABASE" OS$USERID:[6]
"oracle" DBID:[10] "1969706720" PRIV$USED:[1] "5"
Sat May 4 17:31:40 2019 +08:00
LENGTH: "263"
SESSIONID:[10] "4294967295" ENTRYID:[1] "2" STATEMENT:[2] "10" USERID:[2] "TT" USERHOST:[4] "adg1" TERMINAL:[5]
"pts/1" ACTION:[1] "3" RETURNCODE:[1] "0" OBJ$CREATOR:[5] "SCOTT" OBJ$NAME:[3] "EMP" OS$USERID:[6]
"oracle" DBID:[10] "1969706720" PRIV$USED:[2] "47"
经过测试，备库开启审计，不会影响连接。

测试二、手工创建登录触发器

主库创建触发器
create table UC_LOGON_OFF
(
user_id VARCHAR2(30),
session_id NUMBER(8),
host VARCHAR2(30),
last_program VARCHAR2(48),
last_action VARCHAR2(32),
last_module VARCHAR2(32),
logon_day DATE,
logon_time VARCHAR2(10),
logoff_day DATE,
logoff_time VARCHAR2(10),
elapsed_minutes NUMBER(8),
sid NUMBER(8),
serial NUMBER(8)
)
tablespace USERS
pctfree 10
initrans 1
maxtrans 255;

CREATE OR REPLACE TRIGGER TRIG_LOGON_AUDIT
AFTER LOGON
ON DATABASE
BEGIN
INSERT INTO sys.uc_logon_off
select USER,
SYS_CONTEXT ('USERENV', 'SESSIONID'),
SYS_CONTEXT ('USERENV', 'HOST'),
NULL,
NULL,
NULL,
SYSDATE,
TO_CHAR (SYSDATE, 'yyyy-mm-dd hh24:mi:ss'),
NULL,
NULL,
NULL,
NULL, null from dual;
END;


alter table uc_logon_off modify logon_time varchar2(30);
SQL> alter system switch logfile;

--备库开启MRP同步应用，测试前tt可以连接
recover managed standby database disconnect from session;
SQL> conn tt/tt
ERROR:
ORA-00604: error occurred at recursive SQL level 1
ORA-16000: database open for read-only access
ORA-06512: at line 2
Warning: You are no longer connected to ORACLE.

alert.log 
Sat May 04 17:10:33 2019
Errors in file /u01/app/oracle/diag/rdbms/tt/tt/trace/tt_ora_16392.trc:
ORA-00604: error occurred at recursive SQL level 1
ORA-16000: database open for read-only access
ORA-06512: at line 2

主库处理，禁用触发器
SQL> select OWNER,TRIGGER_NAME,TRIGGERING_EVENT,TABLE_OWNER,TABLE_NAME,STATUS,ACTION_TYPE from dba_triggers where TRIGGER_NAME='TRIG_LOGON_AUDIT'
OWNER TRIGGER_NAME TRIGGERING_EVENT TABLE_OWNER TABLE_NAME STATUS ACTION_TYPE
---------- ------------------------------ -------------------- ------------------------------ ------------------------------ -------- -----------
SYS TRIG_LOGON_AUDIT LOGON SYS ENABLED PL/SQL

ALTER TRIGGER "SYS"."TRIG_LOGON_AUDIT" DISABLE;
--备库OK
SQL> conn tt/tt
Connected.

转载于:https://www.cnblogs.com/lvcha001/p/10858305.html