Enable Auditing on database

Enabling auditing on database is simple but will require an outage to the instance and space under system tablespace.


SQL> connect / as sysdba
Connected.

SQL> alter system set audit_trail=DB scope=spfile ;

System altered.

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

Total System Global Area  535662592 bytes
Fixed Size                  1337720 bytes
Variable Size             390071944 bytes
Database Buffers          138412032 bytes
Redo Buffers                5840896 bytes
Database mounted.
Database opened.
SQL>
SQL> connect dummy/dummy
ERROR:
ORA-01017: invalid username/password; logon denied


Warning: You are no longer connected to ORACLE.
SQL>
SQL> conn / as sysdba
Connected.
SQL>

SQL> col OS_USERNAME for a15
SQL> col userhost for a30
SQL>
SQL> select os_username,
  2  username,
  3  userhost,
  4  to_char(timestamp,'mm/dd/yyyy hh24:mi:ss') timestamp,
  5  returncode
  6  from dba_audit_session
  7  where action_name = 'LOGON'
  8  and returncode > 0
  9  order by TIMESTAMP desc;


OS_USERNAME     USERNAME                       USERHOST                       TIMESTAMP           RETURNCODE
--------------- ------------------------------ ------------------------------ ------------------- ----------
oracle          DUMMY                          HostName                       07/09/2012 12:29:56       1017
 
The size of the audit table can be managed, we can purge the old logs as below:


 SQL> truncate table dba_audit_session;
truncate table dba_audit_session
               *
ERROR at line 1:
ORA-01702: a view is not appropriate here


SQL> select count(*) from SYS.AUD$;

  COUNT(*)
----------
    890947

SQL> select count(*) from dba_audit_session;

  COUNT(*)
----------
    890827

SQL> truncate table SYS.AUD$;

Table truncated.

SQL> select count(*) from dba_audit_session;

  COUNT(*)
----------
         0

SQL>

LRM-00112: multiple values not allowed for parameter 'userid'

Getting error while exporting


exp userid='/ as sysdba' full=y file=/dev/null log=/stage/$ORACLE_SID.log direct=y statistics=none
LRM-00112: multiple values not allowed for parameter 'userid'

EXP-00019: failed to process parameters, type 'EXP HELP=Y' for help
EXP-00000: Export terminated unsuccessfully

This is a syntax error which can be fixed by replacing userid='/ as sysdba' with userid="'/ as sysdba'"


Following command will work

exp userid="'/ as sysdba'" full=y file=/dev/null log=/stage/$ORACLE_SID.log direct=y statistics=none