ORACLE 审计引发ORA-04045: errors during recompilation/revalidation of SYS.AUD$问题

 

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   # 然后重试登录，成功，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]$

 

 

 

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;