Wednesday, June 12, 2013

TRUNCATE Aud$ table to prevent system tablespace from getting bigger...

My system tablespace was getting bigger everyday and i realized that  aud$ table was causing this problem and scheluding a  crontab-job truncating aud$ table on monday and thursday ..simply solved my problem..

with oracle user;

vi /home/oracle/scripts/truncate_aud_table.sh

then copy paste  into truncate_aud_table.sh

 #!/bin/bash
. /home/oracle/.bash_profile
output=`sqlplus -s "/ as sysdba" <<EOF
       set heading off feedback off verify off
       TRUNCATE TABLE aud$;
       exit
EOF
`
echo $output

after creating sh , give permissions

chmod 775  /home/oracle/scripts/truncate_aud_table.sh

and add it on crontab ;

00 15 * * 1,4 /home/oracle/scripts/audit_sil.sh > /home/oracle/scripts/audit_sil.log 2>&1


Will run on Monday and Thursday at 15:00 ..
Posted by at

2 comments:

  1. AnonymousOctober 22, 2013 at 6:38 AM

    Hi Erol,
    Many thanks for this. Yes my aud$ had 1755867 rows as seen with "select count(*) from aud$;" Is there a way though in sqlplus to turn off logging to this file rather than emptying it out every three or four days via a cronjob?
    -Michael

    ReplyDelete
    Replies
    1. erolcimenoracleOctober 23, 2013 at 12:44 AM

      Hi Michael;

      If you want turn off logging into AUD$ table then you need to disable auditing...But if you want to log user actions and doesnt want to effect your system tablespace then you can create new tablespace for audit trail location and use the below script to move new location...

      SQL> BEGIN
      DBMS_AUDIT_MGMT.set_audit_trail_location(
      audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
      audit_trail_location_value => 'AUDIT_NEW_TABLESPACE');
      END;

      Also you can turn off logging into aud$ completely by following the below steps..

      Check audit parameter

      SQL> show parameter audit

      NAME TYPE VALUE
      ------------------------------------ ----------- ------------------------------
      audit_file_dest string /u01/app/admin/dbdev/adump
      audit_sys_operations boolean FALSE
      audit_syslog_level string
      audit_trail string DB, EXTENDED <--------- As you see its enabled


      Disable auditing

      SQL> ALTER SYSTEM SET audit_trail='NONE' SCOPE=SPFILE;

      System altered.

      Shutdown database

      SQL> shutdown immediate;
      Database closed.
      Database dismounted.
      ORACLE instance shut down.

      SQL> startup
      ORACLE instance started.

      Total System Global Area 6714322944 bytes
      Fixed Size 2241064 bytes
      Variable Size 2885684696 bytes
      Database Buffers 3808428032 bytes
      Redo Buffers 17969152 bytes
      Database mounted.
      show Database opened.

      Check audit parameter again
      SQL> show parameter audit

      NAME TYPE VALUE
      ------------------------------------ ----------- ------------------------------
      audit_file_dest string /u01/app/admin/dbdev/adump
      audit_sys_operations boolean FALSE
      audit_syslog_level string
      audit_trail string NONE <----------- Disabled

      Good Luck..

      - Erol

      Delete

Subscribe to: Post Comments (Atom)