Steps To Enable Auditing on RAC Database

Auditing has two types, there are

• standard - auditing based on statement, privilege and object-level
• fine-grained - auditing, which is based on content

Standard auditng has 3 levels.

statement - audit all actions on any type of object

privilege - audit actions that stem from system privileges

object-level - specific audit actions like select, update, insert or delete

Auditing depends on which value we pass on audit_trail

the values are..

• none - disables auditing
• os - records the information to a o/s file (uses parameter audit_file_dest for the file location)
• db - records the information in the database, use the view dba_audit_trail ( view accesses table sys.aud$ ) to display audit information.
• db, extended - as per the db value but also populates the sqlbind and sqltext clob columns
• xml - audits to the o/s file but in xml format (uses parameter audit_file_dest for the file location)
• xml,extended - as per xml option but also populates the sqlbind and sqltext clob columnstandard

For all 3 levels of auditing you can choose to audit by access (audit every time you access) or by session (audit only once per access during the session).

Steps to enable auditing ...   Ex  11gR2 2 Node RAC setup.

We had to drop old audit (aud$) table, for that we need to keep our database in restricted mode.

Steps:

srvctl stop listener -n nodename1

srvctl stop listener -n nodename2

sqlplus>

alter system set cluster_database=false scope=spfile

alter system set audit_trail=db,extended scope=spfile

$srvctl stop database -d dbname -o immediate

>startup restrict

>truncate table aud$

>alter system set cluster_database=true scope=spfile

>shu immediate;

$srvctl start database -d dbname

$srvctl start listener -n nodename1

$srvctl start listener -n nodename2

create users

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

AUDIT ALL BY username BY ACCESS;

AUDIT SELECT TABLE, UPDATE TABLE, INSERT TABLE, DELETE TABLE BY username BY ACCESS;

AUDIT EXECUTE PROCEDURE BY username BY ACCESS;

 

opidcl aborting process unknown ospid (27181) as a result of ORA-2396

If you face the above error,

You can safely ignore.

It was written in alertlog because of timed out sessions. If you dont want this in alertlog you may have to reset your idle time to unlimited which is not possible in huge environment. So it is better to ignore the above error message.

Thanks.

Experts comments welcome.

Error 0 in kwqmnpartition-update_aqs_instance, aborting txn

If you face above error in your alert log file,

                It would be because of some sys invalid objects. It would end up with error when end users access something through application.

                To rectify this first we have to run utlrp.sql script which is located in ORACLE_HOME/rdbms/admin path.

If that doesn't work, we have to take downtime and disable all the triggers then run catproc.sql script which is also in the same location.

Thanks.