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,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> # 确认审计已经关闭
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;
（编辑：李大同）
【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!
