Oracle Audit aud$ Database login exception caused by too large

Today, provincial technicians reflect database login exceptions.

Query Oerr, found that this error is a general hint, may cause a database is not registered, local file configuration problems and so on. Because the usual connection and no problem, is an unexpected situation, so the configuration problem is excluded.

Telnet query Listener, found that there is no problem listening, but when attempting to log on locally with the Ora 00020 error

[Email protected]:~> Sqlplus/as sysdba

Sql*plus:release 11.2.0.4.0 Production on Mon APR 25 10:40:08 2016

Copyright (c) 1982, Oracle. All rights reserved.

ERROR:

Ora-00020:maximum number of processes (exceeded)

Enter User-name:

This indicates that the number of processes exceeds the database setting. There is no problem trying to log on to another node.

Then the application should not have a problem, because at least one node is available.

To find the root cause of the problem, I connected an instance of node 2 from another server using an easy connection, resulting in Ora 01653

[Email Protected]:/myimp/aud> sqlplus yy/[email protected]:1521/xxxx
Sql*plus:release 11.2.0.4.0 Production on Mon APR 25 10:04:32 2016
Copyright (c) 1982, Oracle. All rights reserved.
ERROR:
Ora-00604:error occurred at recursive SQL level 1
Ora-01653:unable to extend table SYS. aud$ by 8192 in Tablespace SYSTEM
Ora-02002:error while writing to audit trail
Ora-00604:error occurred at recursive SQL level 1
Ora-01653:unable to extend table SYS. aud$ by 8192 in Tablespace SYSTEM
Enter User-name:

The problem is obvious, the system table space should be exploded. And aud$ is audit-related. Therefore, query the system tablespace usage and find the table with the largest amount of data in the system table space.

sql> col file_name for  a50  
sql> select file_name,bytes/1024/1024/1024  Gb from dba_data_files where tablespace_name=
  
file_name                                                    GB  
-------------------------------------------------- ----------  
+ datadg/data/datafile/system.259.783425779         31.9726563   

SQL> select * FROM (
2 Select table_name,blocks*8192/1024/1024/1024 GB from User_tables where blocks are not NULL for ORDER BY 2 DESC)
3 where RowNum<10;
TABLE_NAME GB
------------------------------ ----------
aud$ 27.4380493
idl_ub1$. 257354736
Wrm$_snapshot_details. 232673645
Wri$_adv_objects. 193763733
histgrm$. 130683899
Wrh$_active_session_history. 11491394
Wrh$_filestatxs. 112823486
obj$. 068336487
source$. 066230774
9 rows selected.

As you can see, the system table space has reached the upper limit of 32G, and the audit table aud$ accounted for 27G.

Review the audit rules and you can see that the database audits each connection.

Now it's clear. New connection because the audit policy needs to write to the aud$ table of the system tablespace, the data cannot be written because the system tablespace has reached the space quota, causing the connection to fail.

Database is urgently available, and the table due to bug problems can not be exported with data pump, only exp, time is too long, so direct truncate operation.

After truncating the aud$, connect the database locally from Node 1 to normal. However, connecting a library node 1 instance from library B still reports the Ora 00020 error. To view the number of node 1 processes

SQL> select COUNT (*) from v$process;
COUNT (*)
----------
1198

View the parameter is 1200 and the number of Node 2 processes is 121. Therefore, the tnsnames.ora that are suspected of being provisioned are not using lb, causing all connections to only go to Node 1.

Currently node 1 cannot be connected because the previous connection was hung here, causing the connection to be congested. After stopping node One, the B library can be connected to a library remotely.

SQL> Show parameter Process
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
Aq_tm_processes Integer 1
Cell_offload_processing Boolean TRUE
db_writer_processes Integer 16
Gcs_server_processes Integer 6
Global_txn_processes Integer 1
job_queue_processes Integer 1000
Log_archive_max_processes Integer 4
Processes integer 1200
Processor_group_name string
SQL> select COUNT (*) from v$process;
COUNT (*)
----------
121

After restarting, the number of node 1 processes drops down and can be connected normally.

Oracle Audit aud$ Database login exception caused by too large