Hi,
Following are steps to enable auditing in oracle database
1) In the initialization parameter file add an entry
audit_trail=db
2) Shutdown the database.
3) startup the database using the init file.
4) After logging into the database at the SQL prompt run the following script
SQL>@
With the help of this script various views are created which will help you to monitor the auditing information:-
a) STMT_AUDIT_OPTION_MAP:-Contains information about auditing option type codes. Created by the SQL.BSQ script at CREATE DATABASE time.
b) AUDIT_ACTIONS:- Contains descriptions for audit trail action type codes
c) ALL_DEF_AUDIT_OPTS:- Contains default object-auditing options that will be applied when objects are created
d) DBA_STMT_AUDIT_OPTS:-Describes current system auditing options across the system and by user
e) DBA_PRIV_AUDIT_OPTS:-Describes current system privileges being audited across the system and by user
f) USER_OBJ_AUDIT_OPTS & DBA_AUDIT_TRAIL 
escribes auditing options on all objects. USER view describes auditing options on all objects owned by the current user.
g) USER_AUDIT_TRAIL & USER_AUDIT_TRAIL:- Lists all audit trail entries. USER view shows audit trail entries relating to current user.
h) DBA_AUDIT_OBJECT & USER_AUDIT_OBJECT:-Contains audit trail records for all objects in the system. USER view lists audit trail records for statements concerning objects that are accessible to the current user.
i) DBA_AUDIT_SESSION & USER_AUDIT_SESSION:-Lists all audit trail records concerning CONNECT and DISCONNECT. USER view lists all audit trail records concerning connections and disconnections for the current user.
j) DBA_AUDIT_STATEMENT & USER_AUDIT_STATEMENT :- Lists audit trail records concerning GRANT, REVOKE, AUDIT, NOAUDIT, and ALTER SYSTEM statements throughout the database, or for the USER view, issued by the user
k) DBA_AUDIT_EXISTS:-Lists audit trail entries produced BY AUDIT NOT EXISTS
5) After completion of the script SQL prompt will return.
6)Run the following commands to enable the various auditing
a)SQL>audit create session;
b)SQL>audit create user;
c)SQL>audit drop user;
d)SQL>audit create user;
e)SQL>audit drop tablespace;
f)SQL>audit grant any role;
g)SQL>audit grant any privelege;
h)SQL>audit alter system;
i)SQL>audit alter session;
j)SQL>audit delete on AUD$ by access;
k)SQL>audit insert on AUD$ by access;
l)SQL>audit update on AUD$ by access;
CAUTION:-
As the table AUD$ which contains all the auditing data is created in the system tablespace,
so as the auditing information grows the size of the system tablespace also increases,
so it is advisable to move this particular table AUD$ to some another tablespace.
THE COMMAND TO MOVE THE TABLE IS:-
SQL>create table AUDX tablespace
SQL>rename AUD$ to AUD$$;
SQL>rename AUDX to AUD$;
TO check whether AUD$ table has shifted to the new tablespace write the following query in the SQL prompt
SQL>select table_name,tablespace_name from dba_tables where table_name=’AUD$’;
Hi,
For e.g If we have 2 databases on a solaris Intel x86 system each with sga 2gb and 1 gb respectively.
Then if we use prstat -a command to determine most CPU Or Memory consuming process we get the following output.
NPROC USERNAME SIZE RSS MEMORY TIME CPU
7 ctsmrc1 983M 200M 0.3% 3:34:32 4.0%
32 ora10g 64G 61G 99% 0:00:05 0.1%
4 daemon 10M 6048K 0.0% 0:13:07 0.1%
28 ctsdb 1225M 202M 0.3% 0:02:15 0.0%
14 mqm 335M 59M 0.1% 0:00:19 0.0%
7 ctsap1 456M 40M 0.1% 0:00:32 0.0%
1 smmsp 6352K 1420K 0.0% 0:00:00 0.0%
1 nobody 4132K 2764K 0.0% 0:00:00 0.0%
1 lp 2644K 1064K 0.0% 0:00:00 0.0%
21 ctsshr 743M 107M 0.2% 0:01:27 0.0%
46 root 177M 107M 0.2% 0:01:38 0.0%
From the above output, It seems that prstat -a does not give true picture of memory usage.
This is because prstat -a sorts the memory processes on the basis of users.
So if we have ora10g user under which there are 2 database each of sga 2gb and 1gb then all the background processes which are already part of sga are counted as the seperate processes of user ora10g and hence each of the background process or processes associated with sga and comes under user ora10g is counted as consuming 2gb or 1gb respectively giving the false impression that user ora10g is consuming 64G of memory which is not true and not possible.
Hence in order to use prstat to determine top CPU or Memory consuming processes use the following commands
to sort the processes based on the CPU consummed use the following command
#prstat -s cpu -n 10 (to determine top 10 cpu consuming processes)
to sort the processes based on the memory size use the following command
#prstat -s size -n 10 (to determine top 10 memory consuming processes)
Also, to determine the resource consumption in Solaris preferably use vmstat and top command which gives true indication of resource which is described in my previous articles.
