Monday 24 December 2012

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;
     

    Monday 17 December 2012

    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.