Automate Kill SNIPED SESSION Oracle Database

If you have configured IDLE_TIME inr your user profile.

IDLE_TIME 

Specify the permitted periods of continuous inactive time during a session, expressed in minutes. Long-running queries and other operations are not subject to this limit

Lets say a session has been idle for 10 Minutes. Session will continue to show as idle even after the idle_time for that user,as specified in that user's profile, has expired. When the user attempts to run a transaction against the database after the idle_time has expired, the database will disconnect the user by terminating the session. After this, the
session will no longer show in v$session. So, even if the session appears to be idle for a duration slightly more then your 10 minutes -- it is already "dead", it just doesn't show as dead yet. PMON will eventually snipe the session, marking it dead in v$session.

Reference this oracle Document for more information.

Once the oracle session is changed to SNIPED status, we can kill that session without any problem. How ever this be done manually, but watching for these SNIPED sessions every time can be irritating, we can automate the job of killing SNIPED session as below.


-- This is an optional table. Create this only if you want to Audit the killed session.

CREATE
  TABLE "AUDIT_KILL_SNIPED_SESSIONS"
  (
    "SID"        NUMBER,
    "SERIAL#"    NUMBER,
    "TIME_STAMP" TIMESTAMP (6) DEFAULT SYSTIMESTAMP,
    "USERNAME"   VARCHAR2(50 BYTE)
  )TABLESPACE "USERS" ;


Note :     Inorder to create below procedure you will need DBA role & "ALTER SYSTEM" grant. If you are creating this procedure in non DBA user, you will need grant select on V$SESSION & "ALTER SYSTEM" grant


-- Procedure to kill sessions that are in sniped state

create or replace Procedure KILL_SNIPED_SESSIONS as
cursor SEL_SID is select SID,SERIAL#,USERNAME from v$session where status='SNIPED';
SEL_REC SEL_SID%ROWTYPE;
V_SQL varchar2(100);
V_SQL1 varchar2(100);

Begin
OPEN SEL_SID;
LOOP
FETCH SEL_SID INTO SEL_REC;
EXIT WHEN SEL_SID%NOTFOUND;

V_SQL :='ALTER SYSTEM KILL SESSION '''||SEL_REC.SID||','||SEL_REC.SERIAL#||''' IMMEDIATE';
-- DBMS_OUTPUT.PUT_LINE(V_SQL);
execute immediate V_SQL;
--- LETS AUDIT THE KILLED SESSIONS DATA HERE
-- COMMENT LINES BELOW THIS TO REMOVE AUDITING

V_SQL1:= 'insert INTO AUDIT_KILL_SNIPED_SESSIONS (SID,SERIAL#,USERNAME) VALUES ('''||SEL_REC.SID||''','''||SEL_REC.SERIAL#||''','''||SEL_REC.USERNAME||''')';
-- DBMS_OUTPUT.PUT_LINE(V_SQL1);
COMMIT;
execute immediate V_SQL1;

-- COMMENT UNTILL THIS LINE TO REMOVE AUDITING

END LOOP;
CLOSE SEL_SID;

END;


-- YOU CAN ALSO SETUP A JOB TO RUN EVERY 30 MINS OR HOUR TO EXECUTE THIS PROCEDURE

BEGIN
    DBMS_SCHEDULER.CREATE_JOB (
            job_name => '"EXEC_KILL_SNIPED_SESSIONS"',
            job_type => 'STORED_PROCEDURE',
            job_action => 'KILL_SNIPED_SESSIONS',
            number_of_arguments => 0,
            start_date => TO_TIMESTAMP_TZ('2016-09-29 13:07:38.837143000 AMERICA/CHICAGO','YYYY-MM-DD HH24:MI:SS.FF TZR'),
            repeat_interval => 'FREQ=HOURLY',
            end_date => NULL,
            enabled => FALSE,
            auto_drop => FALSE,
            comments => 'Job to run KILL_SNIPED_SESSIONS procedure that kills SNIPED SESSIONOS ');

       
    DBMS_SCHEDULER.SET_ATTRIBUTE(
             name => '"EXEC_KILL_SNIPED_SESSIONS"',
             attribute => 'logging_level', value => DBMS_SCHEDULER.LOGGING_OFF);
     
 
   
    DBMS_SCHEDULER.enable(
             name => '"EXEC_KILL_SNIPED_SESSIONS"');
END;


References :

http://arvindasdba.blogspot.com/2016/09/alter-system-kill-session.html

Enable Database Auditing in oracle

ENABLE AUDITING IN ORACLE DATABASE

Enabling Auditing in Oracle Database

Auditing in Oracle Database helps track and monitor database activities, which is essential for security and compliance. By default, auditing is disabled, but you can enable it and configure its settings based on your requirements.

1. Understanding Auditing Parameters

To begin with, check the current auditing configuration using SQL*Plus:

sql

    SQL> SHOW PARAMETER AUDIT

Expected Output:

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
audit_file_dest                      string      C:\ORACLE\PRODUCT\10.2.0\ADMIN
                                                 \DB10G\ADUMP
audit_sys_operations                 boolean     FALSE
audit_trail                          string      NONE

2. Auditing Parameters and Their Values

The AUDIT_TRAIL parameter controls the type of auditing and can be set to one of the following values:

• NONE: Auditing is disabled.
• db: Auditing is enabled, with records stored in the database audit trail (SYS.AUD$).
• db,extended: As db, but includes SQL_BIND and SQL_TEXT columns in the audit records.
• xml: Auditing is enabled, with records stored as XML files in the operating system.
• xml,extended: As xml, but includes SQL_BIND and SQL_TEXT columns.
• OS: Auditing is enabled, with records directed to the operating system’s audit trail.

3. Enabling Auditing in the Database

To enable auditing and store audit records in the database audit trail, follow these steps:

1. Set the AUDIT_TRAIL Parameter:

   Execute the following command to set the parameter:

   sql

   This command updates the AUDIT_TRAIL parameter in the server parameter file (SPFILE).

            SQL> ALTER SYSTEM SET audit_trail=db SCOPE=SPFILE;

1. Restart the Database:

   For the changes to take effect, restart the database:

   sql:
   SQL> SHUTDOWN
   Database closed.
   Database dismounted.
   ORACLE instance shut down.
   
   SQL> STARTUP
   ORACLE instance started.
   
   Total System Global Area  289406976 bytes
   Fixed Size                  1248600 bytes
   Variable Size              71303848 bytes
   Database Buffers          213909504 bytes
   Redo Buffers                2945024 bytes
   Database mounted.
   Database opened.
   

   Note: The auditing changes will only take effect after restarting the database.

4. Verifying Auditing Configuration

After the restart, confirm that the auditing settings are applied:

sql

    SQL> SHOW PARAMETER AUDIT

Expected Output:

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
audit_file_dest                      string      C:\ORACLE\PRODUCT\10.2.0\ADMIN
                                                 \DB10G\ADUMP
audit_sys_operations                 boolean     FALSE
audit_trail                          string      DB

5. Additional Considerations

• Auditing Specific Actions: To audit specific actions (e.g., DDL commands, logins), use the AUDIT command in SQL*Plus.

• Review Audit Records: Query the SYS.AUD$ table to review audit records.

  sql:

    SELECT * FROM SYS.AUD$;

• Managing Audit Data: Regularly manage and archive audit data to prevent excessive growth of audit tables.

By enabling and configuring auditing, you can enhance security and ensure that your database activities are properly monitored and recorded.

Note: Enabling and Disabling the auditing in the database will only take effect after a db restart.

Inserting Data into table with DML Error Logging (catching errors whiles inserting data into table)

Inserting Data with DML Error Logging:

When you load a table using an INSERT statement with subquery, if an error occurs, the statement is terminated and rolled back in its entirety. This can be wasteful of time and system resources. For such INSERT statements, you can avoid this situation by using the DML error logging feature.

To use DML error logging, you add a statement clause that specifies the name of an error logging table into which the database records errors encountered during DML operations. When you add this error logging clause to the INSERT statement, certain types of errors no longer terminate and roll back the statement. Instead, each error is logged and the statement continues. You then take corrective action on the erroneous rows at a later time.

DML error logging works with INSERT, UPDATE, MERGE, and DELETE statements. This section focuses on INSERT statements.

--------------------------------------------------------
--  DDL for Table ATEST1
--------------------------------------------------------

  CREATE TABLE "ATOORPU"."ATEST1"
   (    "ID" NUMBER constraint ATEST1_PK PRIMARY KEY,
    "TDATE" DATE,
    "AMOUNT" VARCHAR2(20 BYTE),
    "ORD_NO" NUMBER
   ) ;

--------------------------------------------------------
INSERT SOME VALUES
--------------------------------------------------------

Insert into ATEST1 (ID,TDATE,AMOUNT,ORD_NO) values (1,to_date('04-APR-16','DD-MON-RR'),null,300);
Insert into ATEST1 (ID,TDATE,AMOUNT,ORD_NO) values (2,to_date('04-APR-16','DD-MON-RR'),null,300);
Insert into ATEST1 (ID,TDATE,AMOUNT,ORD_NO) values (3,to_date('01-MAR-16','DD-MON-RR'),null,100);
Insert into ATEST1 (ID,TDATE,AMOUNT,ORD_NO) values (4,to_date('01-MAR-16','DD-MON-RR'),'100',200);

--------------------------------------------------------
CREATE ERROR LOG TABLE USING THE DBMS PACKAGE :
--------------------------------------------------------

EXECUTE DBMS_ERRLOG.CREATE_ERROR_LOG('ATEST1', 'ERR_ATEST1');   -- ATEST1 source table and ERR_ATEST1 error log table


Error Logging Restrictions and Caveats

• Oracle Database logs the following errors during DML operations:
• Column values that are too large
• Constraint violations (NOT NULL, unique, referential, and check constraints)
• Errors raised during trigger execution
• Errors resulting from type conversion between a column in a subquery and the corresponding column of the table
• Partition mapping errors

Certain MERGE operation errors (ORA-30926: Unable to get a stable set of rows for MERGE operation.)

--------------------------------------------------------
-- This will generate some insert errors
--------------------------------------------------------

INSERT INTO ATEST1
  SELECT ID+3,TDATE,AMOUNT,ORD_NO
  FROM ATEST1
  WHERE id > 1
  LOG ERRORS INTO ERR_ATEST1 ('daily_load') REJECT LIMIT 9;

--- daily_load is TAG, REJECT LIMT will set the max errs before terminating insert statement.


Note:

If the statement exceeds the reject limit and rolls back, the error logging table retains the log entries recorded so far.

--------------------------------------------------------
-- This will generate some update errors
--------------------------------------------------------

update ATEST1 set ID=3 where ID>5 LOG ERRORS INTO ERR_ATEST1 ('daily_load') REJECT LIMIT 9;

--- daily_load is TAG, REJECT LIMT will set the max errs before terminating insert statement.


--------------------------------------------------------
LETS CHECK THE ERROR MESSAGES RECORDED:
--------------------------------------------------------

select * from ERR_ATEST1;

AUDIT DDLS in database with trigger

-- Simple trigger to audit to audit basic schema changes :

--- CREATE TABLE TO STORE AUDIT DATA

CREATE TABLE DDL_AUDIT_LOG
(
  STAMP DATE
, USERNAME VARCHAR2(30 BYTE)
, OSUSER VARCHAR2(30 BYTE)
, MACHINE VARCHAR2(30 BYTE)
, TERMINAL VARCHAR2(30 BYTE)
, OPERATION VARCHAR2(30 BYTE)
, OBJTYPE VARCHAR2(30 BYTE)
, OBJNAME VARCHAR2(30 BYTE)
, OBJ_OWNER VARCHAR2(30 BYTE)
) TABLESPACE USERS ;

-- NOW CREATE TRIGGER TO AUDIT CHANGES

ALTER TRIGGER AUDIT_DDL_CHANGES DISABLECREATE TRIGGER AUDIT_DDL_CHANGES
   AFTER create OR drop OR alter
      ON ATOORPU.SCHEMA  -- Change SCOTT to your schema name!!!
      -- ON DATABASE
BEGIN
  INSERT INTO ddl_audit_log VALUES
        (SYSDATE,
         SYS_CONTEXT('USERENV', 'SESSION_USER'),
         SYS_CONTEXT('USERENV', 'OS_USER'),
         SYS_CONTEXT('USERENV', 'HOST'),
         SYS_CONTEXT('USERENV', 'TERMINAL'),
         ORA_SYSEVENT,
         ORA_DICT_OBJ_TYPE,
         ORA_DICT_OBJ_NAME,
         ORA_DICT_OBJ_OWNER
        );
END;

Sample output :

Sample Audit Table Output

PLSQL code to audit all (similar) tables in schema - oracle

I was playing around with some plsql code today.Just posting this sample plsql code as I thought this can help people.

This is a sample code that will search for all tables with name  ABC,ABC1,ACB2 in all schema's in database and execute a NOAUDIT against these tables. This sql can be modified to accommodate any changes where you want to run a query against all tables & schema's.


sample code:

declare
    result sys_refcursor;
    V_OWNER Varchar2(100):='SCHEMA_NAME'; --- update this with schema you want to audit.
    strTableOwner Varchar2(100);
strTableName Varchar2(100);
    strQuery varchar2(300);
begin

open result for
    select owner,table_name from user_tables where
    table_name in ('ABC','ABC1',ABC2')  and owner = 'V_OWNER' order by table_name;

loop
    fetch result into strTableOwner,strTableName;
    exit when result%notfound;
     
    DBMS_OUTPUT.PUT('NOAUDIT DELETE,UPDATE on '||strTableOwner||'.'||strTableName1||';');
   
strQuery := 'NOAUDIT DELETE,UPDATE on '||strTableOwner||'.'||strTableName1||' ';
    execute immediate strQuery;

 DBMS_OUTPUT.PUT_LINE(' ');
      DBMS_OUTPUT.PUT_LINE('SUCCESFULL');
end loop;

close result;

end;
/


Note : you can edit this part to what ever you want. >>>> NOAUDIT DELETE,UPDATE on

Trigger to disable create objects in database starting with TMP or BAK

---- DISABLE TABLE NAMES STARTING WITH TMP OR BAK in Database ----

Intially :

create table tmp_test (fname varchar2(20));

After enabling below trigger, no more tables can be created in database starting with TMP or BAK:

create or replace TRIGGER NO_TMP_TABS_TRIG
BEFORE CREATE
ON DATABASE

DECLARE
 x user_tables.table_name%TYPE;
BEGIN
  SELECT ora_dict_obj_name
  INTO x
  FROM DUAL;

  IF SUBSTR(x, 0, 4) = 'TMP_' or SUBSTR(x, 0, 4) = 'BAK_' THEN
    RAISE_APPLICATION_ERROR(-20099, 'TABLE NAMES CAN NOT START WITH THE WITH TMP% OR BAK%');
  END IF;
END NO_TMP_TABS_TRIG;


Lets test it :

create table tmp_test (fname varchar2(20));


Error :

Error starting at line : 15 in command -
create table tmp_test (fname varchar2(20))
Error at Command Line : 15 Column : 1
Error report -
SQL Error: ORA-00604: error occurred at recursive SQL level 1
ORA-20099: TABLE NAMES CAN NOT START WITH THE WITH TMP% OR BAK%.

Oracle Table Monitoring - DML ACTIVITY

Monitoring tracks the approximate number of deletes, updates & inserts operations for the table since the last time statistics were gathered. Information about how many rows are affected is maintained in the SGA, until periodically (about every three hours) SMON incorporates the data into the data dictionary. 

Enabling table monitoring :

create table TEST as select OBJECT_name from all_objects;
table TEST created.

select count(*) from test;

  COUNT(*)
----------
    120118


select table_name,inserts,updates,deletes,truncated,timestamp
   from sys.dba_tab_modifications
  where table_owner='ATOORPU' and table_name='TEST';

  --- no rows selected

  Enable FLUSH_DATABASE
  exec DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO;
  anonymous block completed

  ENABLE MONITORING ON TABLE:

  alter table TEST monitoring;

  select table_name,inserts,updates,deletes,truncated,timestamp
   from sys.dba_tab_modifications
  where table_owner='ATOORPU' and table_name='TEST';

  --- no rows selected

  insert into ABCC (select * from ABCC);

  delete from TEST where rownum<1000;

    select count(*) from test;

    COUNT(*)
----------
    119119


select table_name,inserts,updates,deletes,truncated,timestamp
   from sys.dba_tab_modifications
  where table_owner='ATOORPU' and table_name='TEST';

  --- no rows selected

exec DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO;

  anonymous block completed

  select table_name,inserts,updates,deletes,truncated,timestamp
   from sys.dba_tab_modifications
  where table_owner='ATOORPU' and table_name='TEST';

TABLE_NAME                        INSERTS    UPDATES    DELETES TRUNCATED TIMESTAMP
------------------------------ ---------- ---------- ---------- --------- ---------
TEST                                    0          0        999 NO        18-JUN-1



Note :

Starting with Oracle Database 11g, the MONITORING and NOMONITORING keywords have been deprecated and statistics are collected automatically. If you do specify these keywords, they are ignored.

LAST DDL change time on a table in ORACLE

A general question among most of developers is  " HOW can I know when LAST DDL was done??" The best option will be to go and look at the table details. 

While you can also query the same details from user_objects/dba_objects.

Lets create a table test1

sql > create table test1 (id number);

table TEST1 created.


sql >  select object_name,object_type,to_char(created,'DD-MM-YY HH24:MI:SS')created,to_char(last_ddl_time,'DD-MM-YY HH24:MI:SS')last_ddl from user_objects where object_name='TEST1';

OBJECT_NAME            OBJECT_TYPE         CREATED           LAST_DDL        
-------------------------------------------------    ---------------------     --------------
TEST1                              TABLE                     05-06-15 15:54:37        05-06-15 15:54:37 


Now lets try adding a column to table.

sql > alter table test1 add (id1 number);

table TEST1 altered.

sql > 
select object_name,object_type,to_char(created,'DD-MM-YY HH24:MI:SS')created,to_char(last_ddl_time,'DD-MM-YY HH24:MI:SS')last_ddl from user_objects where object_name='TEST1';

OBJECT_NAME      OBJECT_TYPE         CREATED                  LAST_DDL        
---------------------   ----------------------  ---------------------        -------------------
TEST1                       TABLE                         05-06-15 15:54:37          05-06-15 15:55:14 

We can see that Oracle now keeps track of latest DDL changes. It can give you an accurate time_stamp with above query.

find the LAST_DDL_TIME change time of an Oracle object

SQL to find the LAST_DDL_TIME change time of an Oracle object in the database.

-- Get the name, type, date of change of the DDL of a user object.

select OBJECT_NAME, OBJECT_TYPE, LAST_DDL_TIME from dba_objects where
owner not in ('SYS','SYSTEM');

Trigger to backup the data before delete or update on a table - Oracle

Lets create a table :

 CREATE TABLE "ABC" 
   ( "EMPNO" NUMBER(4,0), 
"ENAME" VARCHAR2(10 BYTE), 
"JOB" VARCHAR2(9 BYTE), 
"MGR" NUMBER(4,0), 
"HIREDATE" DATE, 
"SAL" NUMBER(7,2), 
"COMM" NUMBER(7,2), 
"DEPTNO" NUMBER(2,0)
   ) TABLESPACE "USERS" ;

Now we will insert some data into it :

Insert into ABC (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7369,'SMITH','CLERK',7902,to_date('17-DEC-80','DD-MON-RR'),800,null,20);

Insert into ABC (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7499,'ALLEN','SALESMAN',7698,to_date('20-FEB-81','DD-MON-RR'),1600,300,30);

Insert into ABC (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7521,'WARD','SALESMAN',7698,to_date('22-FEB-81','DD-MON-RR'),1250,500,30);

Insert into ABC (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7566,'JONES','MANAGER',7839,to_date('02-APR-81','DD-MON-RR'),2975,null,20);

Insert into ABC (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7654,'MARTIN','SALESMAN',7698,to_date('28-SEP-81','DD-MON-RR'),1250,1400,30);

select count(*) from abc;
-- 5 rows

Lets create a backup table to store data. we are only storing part of the original table data, we can edit as per requirement :

  CREATE TABLE "SCOTT"."ABC_BAK" 
   ( "EMPNO" NUMBER(4,0), 
"ENAME" VARCHAR2(10 BYTE), 
"JOB" VARCHAR2(9 BYTE), 
"MGR" VARCHAR2(20 BYTE), 
"T_STAMP" TIMESTAMP (6) DEFAULT SYSTIMESTAMP
   ) TABLESPACE "USERS" ;


   select count(*) from abc_bak;
   
   0 rows
   
   
Now lets create a trigger, that will triger all the data from the table and store it in backup table before delete or update:

CREATE OR REPLACE TRIGGER ABC_BAK1 
BEFORE UPDATE OR DELETE 
  ON ABC FOR EACH ROW
  BEGIN
      INSERT INTO ABC_BAK
 ( EMPNO,
   ENAME,
   JOB,MGR
    )
VALUES
 ( :old.EMPNO,
   :old.ENAME,
   :old.JOB,
   :old.MGR); 
END;


===========================================================

NOTE : You also add username and Host machine by adding below to trigger
You need to declare the variable first and then assgn values to it

DECLARE
   v_username varchar2(10);
      
  BEGIN
  -- Find username of person performing the DELETE on the table

   SELECT user INTO v_username FROM dual;

Add these to values ----

V_USERNAME,
   sys_context('userenv','host')   --- You can also add host machine here

===========================================================

Lets test our trigger is working fine.

delete from abc where ename=SCOTT;
delete from abc where ename=ALENN;
update abc set ename=ADAM where ename=ADAMS;
.
.
.
.
do some activity and test then validate the bak table.


Select * from abc_bak;

    EMPNO ENAME      JOB       MGR                  T_STAMP                       

---------- ---------- --------- -------------------- -------------------------------

      7788 SCOTT                                     09-FEB-15 06.35.28.862219000 PM 

      7499 ALLEN                                     09-FEB-15 06.35.28.862219000 PM 

      7521 WARD                                      09-FEB-15 06.35.28.862219000 PM 

      7876 ADAMS      CLERK                          09-FEB-15 06.35.53.457126000 PM 

      7900 JAMES      CLERK                          09-FEB-15 06.35.53.462433000 PM 

      7902 FORD       ANALYST                        09-FEB-15 06.35.53.466738000 PM 

      7934 MILLER     CLERK                          09-FEB-15 06.35.53.472108000 PM 

      7788 SCOTT      ANALYST   7566                 09-FEB-15 06.37.30.307425000 PM 

 8 rows selected 

DELETE OS AUDIT FILES IN ORACLE

[atoorpu@ORACLE1 adump]$ pwd
/u01/app/oracle/admin/ORCL/adump
[atoorpu@ORACLE1 adump]$ ls -1 /u01/app/oracle/admin/ORCL/adump | wc -l
22273
[atoorpu@ORACLE1 adump]$ ls -lrt *aud | wc -l
11363
[atoorpu@ORACLE1 adump]$ sqlplus /"As sysdba"

SQL*Plus: Release 11.2.0.4.0 Production on Mon Feb 9 10:40:58 2015

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

If the audit files are in the database (sys.aud$). They can be cleaned up using:

SQL> DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL (
   audit_trail_type => SYS.DBMS_AUDIT_MGMT.AUDIT_TRAIL_OS,
   use_last_arch_timestamp => TRUE);

If the audit files are in the OS. They can be cleaned up using:
SQL> BEGIN
DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL (
   audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_OS,
   use_last_arch_timestamp => TRUE);
   END;
/  

PL/SQL procedure successfully completed.

The CLEAN_AUDIT_TRAIL procedure is the basic mechanism for manually purging the audit trail. It accepts two parameters.

AUDIT_TRAIL_TYPE: The audit trail whose timestamp is to be set (Constants). Only individual audit trails are valid, not the constants that specify multiples.

Types :
AUDIT_TRAIL_XML  ---- For Auditing on XML (XML files)
AUDIT_TRAIL_OS   --- For Auditing on OS (text files)
AUDIT_TRAIL_AUD_STD  --- For Standard Auditing

USE_LAST_ARCH_TIMESTAMP: Set to FALSE to purge all records/files, or TRUE to only purge records/files older than the timestamp specified for the audit trail.

 Lets see if the Last  Archive TS is  set for OS Audit files.


SQL> set pagesize 150
set linesize 150
col last_archive_ts format a40
select * from DBA_AUDIT_MGMT_LAST_ARCH_TS;


AUDIT_TRAIL          RAC_INSTANCE LAST_ARCHIVE_TS
-------------------- ------------ ----------------------------------------
STANDARD AUDIT TRAIL            0 23-DEC-14 03.34.45.000000 PM +00:00



 You can also set the Last  Archive TS  if it is not set 
(in below it will set OS_AUDIT TS to sydate-45) :

SQL> BEGIN
  DBMS_AUDIT_MGMT.set_last_archive_timestamp(
    audit_trail_type  => DBMS_AUDIT_MGMT.AUDIT_TRAIL_OS,
    last_archive_time => SYSTIMESTAMP-45);
END;
/  

PL/SQL procedure successfully completed.


 Now Lets confirm the last Archive Time stamp in DB.

SQL> COLUMN audit_trail FORMAT A20
COLUMN last_archive_ts FORMAT A40

SELECT * FROM dba_audit_mgmt_last_arch_ts;SQL> SQL> SQL>

AUDIT_TRAIL          RAC_INSTANCE LAST_ARCHIVE_TS
-------------------- ------------ ----------------------------------------
STANDARD AUDIT TRAIL            0 23-DEC-14 03.34.45.000000 PM +00:00
OS AUDIT TRAIL                  1 26-DEC-14 10.43.05.000000 AM -06:00

SQL> BEGIN
DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL (
   audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_OS,
   use_last_arch_timestamp => TRUE);
   END;
/  2    3    4    5    6

PL/SQL procedure successfully completed.

SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[atoorpu@ORACLE1 adump]$ ls -lrt *aud | wc -l
1602
[atoorpu@ORACLE1 adump]$

Audit failed logon attempts - Oracle

How to audit failed logon attempts

Oracle Audit -- failed connection
Background:
In some situation DBA team wants to audit failed logon attempts when "unlock account"  requirement becomes frequently and user cannot figure out who from where is using incorrect password to cause account get locked.

Audit concern:
Oracle auditing may add extra load and require extra operation support. For this situation DBA only need audit on failed logon attempts and do not need other audit information. Failed logon attempt is only be able to track through Oracle audit trail, logon trigger does not apply to failure logon attempts


Hint: The setting here is suggested to use in a none production system. Please evaluate all concern and load before use it in production.



Approach:
1. Turn on Oracle audit function by set init parameter:
               audit_trail=DB
Note:
database installed by manual script, the audit function may not turn on:
database installed by dbca, the default audit function may already turn on:
Check:

SQL> show parameter audit_trail
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
audit_trail                          string      NONE

Turn on Oracle audit
a. If database use spfile
SQL> alter system set audit_trail=DB scope=spfile ;
System altered.

b. if database use pfile, modify init<Sid>.ora directly.

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

SQL> startup ;
ORACLE instance started.


2. Turn off Oracle default audit
Privilege audit information stored in dba_priv_audit_opts;
Note: Oracle 11g has couple of audit turned on default when the audit_trail is set. It will just utilize more resources that you might not want.
Oracle 10g, audit options is setup by explicit command (we can audit these options back any time).

Generate a script to turn off default privilege audit which we don't need here.
SQL>  SELECT 'noaudit '|| privilege||';' from dba_priv_audit_opts where user_name is NULL;
'NOAUDIT'||PRIVILEGE||';'
-------------------------------------------------
noaudit ALTER SYSTEM;
noaudit AUDIT SYSTEM;
noaudit CREATE SESSION;
noaudit CREATE USER;
noaudit ALTER USER;
noaudit DROP USER;
noaudit CREATE ANY TABLE;
noaudit ALTER ANY TABLE;
noaudit DROP ANY TABLE;
noaudit CREATE PUBLIC DATABASE LINK;
noaudit GRANT ANY ROLE;
noaudit ALTER DATABASE;
noaudit CREATE ANY PROCEDURE;
noaudit ALTER ANY PROCEDURE;
noaudit DROP ANY PROCEDURE;
noaudit ALTER PROFILE;
noaudit DROP PROFILE;
noaudit GRANT ANY PRIVILEGE;
noaudit CREATE ANY LIBRARY;
noaudit EXEMPT ACCESS POLICY;
noaudit GRANT ANY OBJECT PRIVILEGE;
noaudit CREATE ANY JOB;
noaudit CREATE EXTERNAL JOB;
23 rows selected.

-- run above commands

3. Turn on audit on failed connection
SQL> AUDIT CONNECT WHENEVER NOT SUCCESSFUL;

Audit succeeded.

SQL> SELECT PRIVILEGE,SUCCESS,FAILURE FROM dba_priv_audit_opts;

PRIVILEGE                                SUCCESS    FAILURE
---------------------------------------- ---------- ----------
CREATE SESSION                           NOT SET    BY ACCESS

4. Retrieve information
Note: audit information is stored on sys.aud$. There multiple views Oracle provide to help you read sys.aud$. Logon failed information can be retrieve from  dba_audit_session

SQL>   select os_username,  username, userhost,  to_char(timestamp,'mm/dd/yyyy hh24:mi:ss') logon_time,  action_name, returncode from dba_audit_session;

OS_USERNAME                    USERNAME                       USERHOST                                           TIMESTAMP           ACTION_NAME                  RETURNCODE
------------------------------ ------------------------------ -------------------------------------------------- ------------------- ---------------------------- ----------
Arvind                    machine1         
HOME-Arvind                                       12/06/2014 13:40:12 LOGON                              1017
Arvind                    machine1         
HOME-lArvind                                       12/06/2014 13:40:25 LOGON                              1017
Arvind                   machine1         
HOME-Arvind                                       12/06/2014 15:31:29 LOGON                              1017
Arvind                   machine1         
HOME-Arvind                                       12/06/2014 15:31:38 LOGON                              1017
4 rows selected.

Note: RETURNCODE is the ORA error code return to user.
ORA-1017 is incorrect password
ORA-28000 is account locked
ORA-1045 is missing connect privilege

------------------------------------------------------------
Up here, we be able to audit who is the bad boy causing account locked.

Turning off the audit:
If you no longer need the audit on failed attempts, run this command to turn off
SQL> noaudit CONNECT;

Noaudit succeeded.

SQL> SELECT PRIVILEGE,SUCCESS,FAILURE FROM dba_priv_audit_opts;

no rows selected


Oracle use system tablespace for sys.aud$. For enhancement, you may consider to move sys.aud$ to separate tablespace.


6. Move sys.aud$ out of system tablespace.
Oracle 11g provide package dbms_audit_mgmt.set_audit_trail_location to relocate the aud$ table.
SQL>  SELECT table_name, tablespace_name FROM dba_tables WHERE table_name ='AUD$';
TABLE_NAME                     TABLESPACE_NAME
----------------------------- ------------------------------
AUD$                           SYSTEM

Following example shows how to move sys.aud$ from system tablespace to user_data1 tablespace.

SQL> exec DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_LOCATION(audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD, audit_trail_location_value => 'USER_DATA1');


PL/SQL procedure successfully completed.

SQL> SELECT table_name, tablespace_name FROM dba_tables WHERE table_name ='AUD$';

TABLE_NAME                     TABLESPACE_NAME
------------------------------ ------------------------------
AUD$                           USER_DATA1



7. Clean up AUD$

You can simply run delete or truncate command


delete from sys.AUD$;
truncate table sys.AUD$;

Oracle audting explained

Oracle AUDIT Explained

Enabling Auditing in Database

#1

You can specify DB,EXTENDED in either of the following ways:

ALTER SYSTEM SET AUDIT_TRAIL=DB, EXTENDED SCOPE=SPFILE;

ALTER SYSTEM SET AUDIT_TRAIL='DB','EXTENDED' SCOPE=SPFILE;

However, do not enclose DB, EXTENDED in quotes, for example:

ALTER SYSTEM SET AUDIT_TRAIL='DB, EXTENDED' SCOPE=SPFILE;

OS Directs all audit records to an operating system file.

#2

Directs audit records to the database audit trail (the SYS.AUD$ table), except for mandatory and SYS audit records, which are always written to the operating system audit trail

#3

The operating system and database audit trails both capture many of the same types of actions.

Operating System Audit Record Equivalent DBA_AUDIT_TRAIL View Column

SESSIONID                                                                          SESSIONID

ENTRYID                                                                             ENTRYID

STATEMENT                                                                      STATEMENTID

USERID                                                                                USERNAME

USERHOST                                                                          USERHOST

TERMINAL                                                                         TERMINAL

ACTION                                                                                ACTION

SYS$OPTIONS                                      Indicates what audit option was set with AUDIT Or  

                                                                 NOAUDIT, or what privilege was granted or revoked.

RETURNCODE                                                                   RETURNCODE

OBJ$CREATOR                                                                 OWNER

OBJ$NAME                                                                         OBJ_NAME

OBJ$PRIVILEGES                                                             OBJ_PRIVILEGE

AUTH$GRANTEE                                                              GRANTEE

NEW$OWNER                                                                    NEW_OWNER

NEW$NAME                                                                       NEW_NAME

SES$ACTIONS                                                                    SES_ACTIONS

LOGOFF$PREAD                                                               LOGOFF_PREAD

LOGOFF$LWRITE                                                             LOGOFF_LWRITE

COMMENT$TEXT                                                            COMMENT_TEXT

OS$USERID                                                                         OS_USERNAME

PRIV$USED                                                                         PRIV_USED

SES$LABEL                                                                        CLIENT_ID

SES$TID                                               Does not have an equivalent in the DBA_AUDIT_TRAIL 

                                                                view, but it does appear in the SYS.AUD$ table

SPARE2                                                Does not have an equivalent in the DBA_AUDIT_TRAIL 

                                                               view, but it does appear in the SYS.AUD$ table

 

#4

If you set AUDIT_SYS_OPERATIONS to TRUE and AUDIT_TRAIL to XML or XML,EXTENDED, then Oracle Database writes SYS audit records operating system files in XML format.

#5

AUDIT_SYSLOG_LEVEL, which writes SYS and standard OS audit records to the system audit log using the SYSLOG utility. This option only applies to UNIX environments.

#6

The XML AUDIT_TRAIL value does not affect syslog audit file. In other words, if you have set the AUDIT_TRAIL parameter to XML, then the syslog audit records will still be in text format, not XML file format.

#7

To write SYS and mandatory audit files to operating system files in XML format: Set AUDIT_TRAIL to XML or XML,EXTENDED, set AUDIT_SYS_OPERATIONS to TRUE, but do not set the AUDIT_SYSLOG_LEVEL parameter.

To write SYS and mandatory audit records to syslog audit files and standard audit

records to XML audit files: Set AUDIT_TRAIL to XML or XML,EXTENDED, set AUDIT_

SYS_OPERATIONS to TRUE, and set the AUDIT_SYSLOG_LEVEL parameter.

#8

If the AUDIT_TRAIL initialization parameter is set to XML (or XML, EXTENDED), then Oracle Database writes audit records to the operating system as XML files. You can use the V$XML_AUDIT_TRAIL view to make XML audit records available to database administrators through a SQL query, providing enhanced usability.

#9

The DBA_COMMON_AUDIT_TRAIL view includes the standard and fine grained audit trails written to database tables, XML-format audit trail records, and the contents of the V$XML_AUDIT_TRAIL dynamic view (standard, fine grained, SYS and mandatory).

#10

Syslog Audit Trail

Potential security vulnerability for the operating system audit trail is that a privileged user, such as a database administrator, can modify or delete database audit records. To minimize this risk, you can use a syslog audit trail. Syslog is a standard protocol on UNIX-based systems for logging information from different components of a network. Applications call the syslog() function to log information to the syslog daemon, which then determines where to log the information. You can configure syslog to log information to a file or to a dedicated host by editing the syslog.conf file. You can also configure syslog to alert a specified set of users when information is logged.

#11

Setting the Size of the Operating System Audit Trail

To control the size of the operating system audit trail, set theDBMS_AUDIT_MGMT.OS_FILE_MAX_SIZE property by using the DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_PROPERTY PL/SQL procedure. Remember that you must have the EXECUTE privilege for the DBMS_AUDIT_MGMT PL/SQL package before you can use it. When the operating system file meets the size limitation you set, Oracle Database stops adding records to the current file and then creates a new operating system file for the subsequent records. For more information about the DBMS_AUDIT_MGMT PL/SQL package.

SETTING THE SIZE OF THE OPERATING SYSTEM AUDIT TRAIL

BEGIN

DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_PROPERTY(

AUDIT_TRAIL_TYPE => DBMS_AUDIT_MGMT.AUDIT_TRAIL_OS,

AUDIT_TRAIL_PROPERTY => DBMS_AUDIT_MGMT.OS_FILE_MAX_SIZE,

AUDIT_TRAIL_PROPERTY_VALUE => 102400);

END;

/

Note: 1) AUDIT_TRAIL_PROPERTY_VALUE: Sets the maximum size to 102400 kilobytes, that is,

           10 MB. The default setting is 10,000 kilobytes (approximately 10 MB). Do not exceed 2 GB.

           2) DBMS_AUDIT_MGMT.OS_FILE_MAX_SIZE property, which sets the maximum size. To find the 

             status of the current property settings, query the PARAMETER_NAME and PARAMETER_VALUE  

             columns of the DBA_AUDIT_MGMT_CONFIG_PARAMS data dictionary view.

SETTING THE AGE OF THE OPERATING SYSTEM AUDIT TRAIL

BEGIN

DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_PROPERTY(

AUDIT_TRAIL_TYPE => DBMS_AUDIT_MGMT.AUDIT_TRAIL_OS,

AUDIT_TRAIL_PROPERTY => DBMS_AUDIT_MGMT.OS_FILE_MAX_AGE,

AUDIT_TRAIL_PROPERTY_VALUE => 10 );

END;

/

Note: 1) AUDIT_TRAIL_PROPERTY_VALUE: Sets the maximum age to 10 days. Enter a value

           between 1 and 495. The default age is 5 days.

          2) DBMS_AUDIT_MGMT.OS_FILE_MAX_AGE property to set the maximum age. To find the status 

          of the current property setting, query the DBA_AUDIT_MGMT_CONFIG_PARAMS data dictionary  

          view.

Views

DBA_AUDIT_MGMT_CLEAN_EVENTS Displays the history of purge events. Periodically, as user SYS connected with the SYSDBA privilege, you should delete the contents of this view so that it does not grow too large.

DBA_AUDIT_MGMT_CLEANUP_JOBS Displays the currently configured audit trail purge jobs

DBA_AUDIT_MGMT_CONFIG_PARAMS Displays the currently configured audit trail properties that are used by the DBMS_AUDIT_MGMT PL/SQL package

DBA_AUDIT_MGMT_LAST_ARCH_TS Displays the last archive timestamps that have set for audit trail purges.

#12

Listing Active Object Audit Options for Specific Objects

SELECT * FROM DBA_OBJ_AUDIT_OPTS WHERE OWNER = 'BHUVAN' AND OBJECT_NAME LIKE 'EMP%';

The view returns information about all the audit options for the specified object. The information in the view is interpreted as follows:

* A dash (-) indicates that the audit option is not set.

* The S character indicates that the audit option is set BY SESSION.

* The A character indicates that the audit option is set BY ACCESS.

* Each audit option has two possible settings, WHENEVER SUCCESSFUL and WHENEVER NOT SUCCESSFUL, separated by a slash (/). For example, the DELETE audit option for BHUVAN.emp is set by ACCESS for successful DELETE statements and not set at all for unsuccessful DELETE statements.

#13

Oracle AUDITING VIEWS

DBA_FGA_AUDIT_TRAIL - display the captured audit info (FGA only (F GA_LOG$))

DBA_AUDIT_EXISTS - displays audit trail entries produced by AUDIT EXISTS & AUDIT NOT EXISTS.

DBA_AUDIT_OBJECT -- displays audit trail records for all objects in the database.

DBA_AUDIT_POLICIES -- identify FGA audit policies

DBA_AUDIT_POLICY_COLUMNS - Identify FGA audit policies for columns level in FGA

DBA_AUDIT_SESSION - display session level auditing information

DBA_AUDIT_STATEMENT - display statement level auditing information

DBA_AUDIT_TRAIL - display the captured audit info (Standard auditing only ( AUD$))

DBA_COMMON_AUDIT_TRAIL -- displays the captured audit info for standard & FGA

DBA_OBJ_AUDIT_OPTS -- display any object auditing

DBA_PRIV_AUDIT_OPTS -- display any privilege auditing

DBA_STMT_AUDIT_OPTS -- display any statement auditing

DBA_AUDIT_MGMT_CLEANUP_JOBS - Displays the currently configured audit trail purge jobs

DBA_AUDIT_MGMT_CLEAN_EVENTS -- Displays the history of purge events. Periodically as user SYS connected with the SYSDBA privilege, you should delete the contents of this view so that it does not grow too large.

DBA_AUDIT_MGMT_CONFIG_PARAMS -- displays information about the currently configured audit trail properties that are used by the DBMS_AUDIT_MGMT PL/SQL package.

DBA_AUDIT_MGMT_LAST_ARCH_TS -- Displays the last archive timestamps that have set for audit trail purges.

V$XML_AUDIT_TRAIL -- If you are writing to an XML file, you can query the

#14

Operating System Audit Trail Records: Example

Audit trail:

LENGTH: "349"

SESSIONID:[5] "43464"

ENTRYID:[1] "1"

STATEMENT:[1] "1"

USERID:[6] "DBSNMP"

USERHOST:[7] "SHOBEEN"

TERMINAL:[3] "MAU"

ACTION:[3] "100"

RETURNCODE:[1] "0"

COMMENT$TEXT:[97] "Authenticated by: DATABASE; Client address:

(ADDRESS=(PROTOCOL=tcp)(HOST=192.0.2.4)(PORT=2955))"

OS$USERID:[19] "NT AUTHORITY\SYSTEM"

DBID:[10] "1212547373"

PRIV$USED:[1] "5"

In this example:

■ LENGTH refers to the total number of bytes used in this audit record. This number includes the trailing newline bytes (\n), if any, at the end of the audit record.

■ [] brackets indicate the length of each value for each audit entry. For example, the USERID entry, DBSNMP, is 6 bytes long.

■ SESSIONID indicates the audit session ID number. You can also find the session ID by querying the AUDSID column in the V$SESSION data dictionary view.

■ ENTRYID indicates the current audit entry number, assigned to each audit trail record. The audit ENTRYID sequence number is shared between fine-grained audit records and regular audit records.

■ STATEMENT is a numeric ID assigned to the statement the user runs. It appears for each statement issued during the user session, because a statement can result in multiple audit records.

■ ACTION is a numeric value representing the action the user performed. The corresponding name of the action type is in the AUDIT_ACTIONS table. For example, action 100 refers to LOGON.

■ RETURNCODE indicates if the audited action was successful. 0 indicates success. If the action fails, the return code lists the Oracle Database error number. For example, if you try to drop a non-existent table, the error number is ORA-00903 invalid table name, which in turn translates to 903 in the RETURNCODE setting.

■ COMMENT$TEXT indicates additional comments about the audit record. For example, for LOGON audit records, it can indicate the authentication method. It corresponds to the COMENT_TEXT column of the DBA_COMMON_AUDIT_TRAIL data dictionary view.

■ DBID is a database identifier calculated when the database is created. It corresponds to the DBID column of the V$DATABASE data dictionary view.

■ ECONTEXT_ID indicates the application execution context identifier.

■ PRIVS$USED refers to the privilege that was used to perform an action. To find the privilege, query the SYSTEM_PRIVILEGE_MAP table. For example, privilege 5 refers to -5 in this table, which means CREATE SESSION. PRIVS$USED corresponds to the PRIV_USED column in the DBA_COMMON_AUDIT_TRAIL, which lists the privilege by name. Other possible values are as follows:

■ SCN (for example, SCN:8934328925) indicates the System Change Number (SCN). Use this value if you want to perform a flashback query to find the value of a setting (for example, a column) at a time in the past. For example, to find the value of the ORDER_TOTAL column of the OE.ORDERS table based on the SCN number, use the following SELECT statement:

SELECT ORDER_TOTAL FROM OE.ORDERS AS OF SCN = 8934328925

WHERE ORDER_TOTAL = 86;

■ SES_ACTIONS indicates the actions that took place during the session. This field is present only if the event was audited with the BY SESSION clause. Because this field does not explain in detail the actions that occurred during the session, you should configure the audit event with the BY ACCESS clause. The SES_ACTIONS field contains 16 characters. Positions 14, 15, and 16 are reserved for future use. In the first 12 characters, each position indicates the result of an action. They are: ALTER, AUDIT, COMMENT, DELETE, GRANT, INDEX, INSERT, LOCK, RENAME, SELECT, UPDATE, and FLASHBACK. For example, if the user had successfully run the ALTER statement, the SES_ACTIONS setting is as follows: S--------------- The S, in the first position (for ALTER), indicates success. Had the ALTER statement failed, the letter F would have appeared in its place. If the action resulted in both a success and failure, then the letter is B.

■ SES$TID indicates the ID of the object affected by the audited action.

■ SPARE2 indicates whether the user modified SYS.AUD$ table. 0 means the user modified SYS.AUD$; otherwise, the value is NULL.



Some reference Sites :

http://www.oradba.ch/2011/05/database-audit-and-audit-trail-purging/
https://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_4007.htm