报错信息
根据客户提供的报错信息， 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.