1、登录报错信息
开发同事跟我说登录已经报错了，我去试了下，发现果然报错，如下所示：
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$里面写入信息报错了，所以只要解决审计的相关问题即可解决登录问题。
2、情况审计表
查看后台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有故障了。
3、关闭审计
原来准备去核查审计表的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 [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,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,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,Data Mining and Real Application Testing options sql> show parameter audit_trail NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ audit_trail string NONE sql> [oracle@hch_test_121_61 admin]$ |
4、审计相关操作
审计开启：
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; |
