Steps to define audition of user connections

1. Check whether audition is enabled on the database. If not then define this using the following steps:
   
   sys@DB1> show parameter audit_trail
   
   NAME                              TYPE        VALUE
   ——————————— ———– ——————————
   audit_trail                       string      NONE
   
   sys@DB1> alter system set audit_trail=db scope=spfile;
   
   System altered.
   
   sys@DB1> shutdown immediate
   Database closed.
   Database dismounted.
   ORACLE instance shut down.
   
   sys@DB1> startup
   ORACLE instance started.
   Total System Global Area   197132288 bytes
   Fixed Size                   1248068 bytes
   Variable Size               62915772 bytes
   Database Buffers           130023424 bytes
   Redo Buffers                 2945024 bytes
   Database mounted.
   Database opened.
   
   sys@DB1>  show parameter audit_trail
   
   NAME                             TYPE        VALUE
   ——————————– ———– ——————————
   audit_trail                      string      DB
   
   
   
2. Create table AUD_HIST which will store all history logons:
   
   sys@DB1>  create table sys.aud_hist
              tablespace users as
             select * from sys.aud$
             where 1 = 2;
   
   Table created.
   
   
   
3. Create scheduler job which will move all the records of SYS.AUD$ to SYS.AUD_HIST:
   
   Declare
      plsql_block VARCHAR2(4000);
   begin
   plsql_block := ‘
      DECLARE LAST_REC_TIME DATE;
        BEGIN
           SELECT NVL(MAX(TIMESTAMP#),TO_DATE(’||chr(39)||’1/1/1901′||chr(39)||’,'||chr(39)||’DD/MM/YYYY’||chr(39)||’))
                INTO LAST_REC_TIME 
              FROM SYS.AUD$;
              INSERT INTO SYS.AUD_HIST
                  SELECT * FROM SYS.AUD$ 
              WHERE TIMESTAMP# <= LAST_REC_TIME; 
              DELETE SYS.AUD$
                  WHERE TIMESTAMP# <= LAST_REC_TIME;
              COMMIT;
            END;’;                
   
   DBMS_SCHEDULER.CREATE_JOB(
        job_name        =>’MOVE_AUDIT_HISTORY’,
        job_type        => ‘PLSQL_BLOCK’,
        job_action      => plsql_block,   
        start_date      => SYSTIMESTAMP ,
        repeat_interval => ‘FREQ=DAILY’,
        enabled         => TRUE );
   end;
   /
   
   
   
4. Define audit on every user login:
   
   sys@DB1> AUDIT CREATE SESSION WHENEVER SUCCESSFUL ;
   
   Audit succeeded.