Wednesday, September 12, 2012

Debug Methods Oracle (Coarse Grained Auditing)



Scenario
There are many times, where there was an issue in production and you did not have any idea how it happened. The following article will give you some direction. Please note there are many other ways too. Currently, I will just focus on a method I had used during a similar situation.

This error information is from the latest logs from Production said:
ORA-00001: unique constraint (TABLE_ABCD.SYS_C006189415) violated

      Now the constraints to troubleshoot the above scenario are:
  • You have limited access/privilege in production environment.
  • The above was due to a duplicate record that came in and your process tried to insert that duplicate data.
But what was that data that caused disruption? The answer could have been found out by using FlashBack Query feature from Oracle. This however is version specific. Let’s look into some other aspects of debugging.

Observation

  • There is no clue to the data that was being inserted at the time this exception was thrown.
  • Not much information either from any of the relevant log files about the metadata.
  • The respective process tables handle massive number of records which makes it further more   cumbersome to troubleshoot or pin point the culprit.
  • No bulletin or logs from front end either.


Other Debug Options
 In this scenario we have a few options to explore and figure out the corrupt data:
  • Putting ON our Application Debug option – Heavy On Performance & Resource (Not always helpful either)
  • Get the trace log files inspected by the DBA (if at all the relevant data is set to be captured) Complex, Heavy On Performance & Resource
  • There are other DBMS packages like DBMS_ERRLOG, DBMS_MONITOR, DBMS_RLS  etc. – Little More Coding and better with live capture instead of historical
  • Triggers to capture & hold the data. – Needs another table to be created to hold the bad data
  • There are some other ways as well not mentioned for now

A Simpler Option (for database issues)

Auditing, at Statement Level.
There are other variations to this as well like Object level, Privilege level, Fine Grained Auditing to increase the granularity of data captured.

PREREQUISITES: The system parameters, AUDIT_SYS_OPERATIONS are set to TRUE & AUDIT_TRAIL is set to DB_EXTENDED (This setting required to capture the SQL_BIND data).  Check V$PARAMETER view. Since by default there will be a certain level of auditing enabled for any database, luckily these settings might pre-exist. In case it doesn’t, enhanced privileges will be required to modify them.

SWITCH ON AUDIT (FOR THE TABLE ONLY):   
AUDIT INSERT, UPDATE ON <OWNER>.<TABLE_NAME>                                                                                                                                          
BY ACCESS – Can be BY SESSION as well                                                                                                                                               
WHENEVER NOT SUCCESSFUL;

CHECK RESULTS: Query the USER_AUDIT_TRAIL table and check the fields, SQL_BIND (the data of interest), SQL_TEXT (the query which caused the failure), ACTION and RETURN CODE (0 for Success)
e.g. select * from USER_AUDIT_TRAIL where owner = 'OWNER_NAME' and OBJ_NAME= 'TABLE_NAME';
The bound data from SQL_BIND column will provide the solution:
               
SWITCH OFF AUDIT:  NOAUDIT INSERT, UPDATE
                                                                                 ON <OWNER>.<TABLE_NAME>
                                                                                  WHENEVER NOT SUCCESSFUL;

NOTE: The above is also an instance where we made use of a minimal elevated privilege.

Refer to the following links for more details:

No comments:

Post a Comment