开发同事跟我说登录已经报错了，我去试了下，发现果然报错，如下所示：
SQL> conn powerdesk/wdpassword ERROR: ORA-00604: error occurred at recursive SQL level 1 ORA-04045: errors during recompilation/revalidation of SYS.AUD$ ORA-08243: recursive audit operation attempted ORA-02002: error while writing to audit trail ORA-00604: error occurred at recursive SQL level 1 ORA-04045: errors during recompilation/revalidation of SYS.AUD$ ORA-08243: recursive audit operation attempted
Warning: You are no longer connected to ORACLE. SQL>
|
据分析应该是开启了审计功能，用户登录的时候，需要记录审计信息，往审计表SYS.AUD$里面写入信息报错了，所以只要解决审计的相关问题即可解决登录问题。
查看后台alert log，没有发现异常信息，怀疑是审计表空间已经满了，所以准备采用清空审计表的措施来释放资源，尝试下。
SQL> show parameter audit_trail;
NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ audit_trail string DB SQL> show parameter audit;
NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ audit_file_dest string /oracle/app/oracle/admin/ystes tdb/adump audit_sys_operations boolean FALSE audit_syslog_level string audit_trail string DB SQL>
|
先备份审计表、然后清空审计表：
SQL> CREATE TABLE backup_aud$ AS SELECT * from sys.aud$;
Table created.
SQL> truncate table sys.aud$; truncate table sys.aud$ * ERROR at line 1: ORA-00942: table or view does not exist
SQL>
|
看到清空失败了，显然有别的隐患，估计是审计表的seg有故障了。
原来准备去核查审计表的seg信息，但是同事在催，说他着急用，希望我快速解决，所以我想到了一招，直接关闭审计尝试下：
# 开始关闭审计 SQL> alter system set audit_trail = NONE scope=spfile;
System altered.
SQL>
# 然后重启数据库 SQL> create pfile from spfile;
File created.
SQL> shutdown immediate; Database closed. Database dismounted. ORACLE instance shut down. SQL> startup; ORACLE instance started.
Total System Global Area 1603411968 bytes Fixed Size 2213776 bytes Variable Size 704645232 bytes Database Buffers 872415232 bytes Redo Buffers 24137728 bytes Database mounted. Database opened. SQL> exit Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options
# 然后重试登录，成功，OK，问题解决。 [oracle@hch_test_121_61 admin]$ rlwrap sqlplus powerdesk/wdpassword@ystestdb
SQL*Plus: Release 11.2.0.1.0 Production on Mon Oct 24 17:16:34 2016
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> exit Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options [oracle@hch_test_121_61 admin]$ rlwrap sqlplus plas/plas610418@ystestdb;
SQL*Plus: Release 11.2.0.1.0 Production on Mon Oct 24 17:16:53 2016
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> # 确认审计已经关闭 SQL> show parameter audit_trail
NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ audit_trail string NONE SQL>
[oracle@hch_test_121_61 admin]$
|
审计开启：
SQL> alter system set audit_sys_operations=TRUE scope=spfile;--审计管理用户(以sysdba/sysoper角色登陆) SQL> alter system set audit_trail=db,extended scope=spfile;
|
重启实例：
SQL> show parameter audit NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ audit_file_dest string /oracle/app/oracle/admin/ORCL/adump audit_sys_operations boolean TRUE audit_syslog_level string audit_trail string DB, EXTENDED （完成）
|
审计关闭：
SQL> conn /as sysdba SQL> show parameter audit SQL> alter system set audit_trail = NONE scope=spfile;
|