报错信息
根据客户提供的报错信息， ORA-: error occurred at recursive SQL level
ORA-: database open for read-only access
怀疑是触发器导致的问题，自己的测试环境进行测试。
测试一、dg环境开启审计参数是否影响
[oracle@adg1 dbs]$ sqlplus tt/tt@adgtns
SQL*Plus: Release 11.2.0.4. Production on Sat May ::
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=;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- --------------------
SMITH CLERK -DEC-
SQL> conn / as sysdba
Connected.
SQL> show parameter audit
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
audit_file_dest string /u01/app/oracle/admin/tt/adump 测试发现，备库开启审计功能，并不会导致无法连接。
Sat May :: +:
LENGTH: ""
SESSIONID:[] "" ENTRYID:[] "" STATEMENT:[] "" USERID:[] "TT" USERHOST:[] "adg1" TERMINAL:[]
"pts/1" ACTION:[] "" RETURNCODE:[] "" COMMENT$TEXT:[] "Authenticated by: DATABASE" OS$USERID:[]
"oracle" DBID:[] "" PRIV$USED:[] ""
Sat May :: +:
LENGTH: ""
SESSIONID:[] "" ENTRYID:[] "" STATEMENT:[] "" USERID:[] "TT" USERHOST:[] "adg1" TERMINAL:[]
"pts/1" ACTION:[] "" RETURNCODE:[] "" OBJ$CREATOR:[] "SCOTT" OBJ$NAME:[] "EMP" OS$USERID:[]
"oracle" DBID:[] "" PRIV$USED:[] ""
经过测试，备库开启审计，不会影响连接。
测试二、手工创建登录触发器
主库创建触发器
create table UC_LOGON_OFF
(
user_id VARCHAR2(),
session_id NUMBER(),
host VARCHAR2(),
last_program VARCHAR2(),
last_action VARCHAR2(),
last_module VARCHAR2(),
logon_day DATE,
logon_time VARCHAR2(),
logoff_day DATE,
logoff_time VARCHAR2(),
elapsed_minutes NUMBER(),
sid NUMBER(),
serial NUMBER()
)
tablespace USERS
pctfree
initrans
maxtrans ; 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();
SQL> alter system switch logfile;
--备库开启MRP同步应用，测试前tt可以连接
recover managed standby database disconnect from session;
SQL> conn tt/tt
ERROR:
ORA-: error occurred at recursive SQL level
ORA-: database open for read-only access
ORA-: at line
Warning: You are no longer connected to ORACLE. alert.log
Sat May ::
Errors in file /u01/app/oracle/diag/rdbms/tt/tt/trace/tt_ora_16392.trc:
ORA-: error occurred at recursive SQL level
ORA-: database open for read-only access
ORA-: at line
主库处理，禁用触发器
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.