| Success event log [message #606471] |
Fri, 24 January 2014 06:41  |
juhani
Messages: 7
Registered: May 2007
|
Junior Member |
|
|
Hi,
I need to do success event logging on my db, ex. when insert statement is
executed succesfull, should timestamp and text 'SUCCESS' inserted on LOG_TBL.
How can I identify those events?
BR Juhani
|
|
|
|
| Re: Success event log [message #606473 is a reply to message #606471] |
Fri, 24 January 2014 07:00  |
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
|
Senior Member |
|
|
1. Capture the count of inserts using %rowcount
2. After this insert statement, write another insert statement to insert a new record into LOG_TBL with values, JOB_NAME, DT_TM_STAMP, INSERTS COUNT, STATUS_SUCCESS etc.
3. If you wish to handle the errors too int he same log table, then repeat step 2 in the exception block, however, the status would be FAIL with the respective failed record count.
With whatever you asked in plain english, these steps would suffice. If you want more clarification, please post what exactly you are doing. Be a bit specific, and not too generic.
|
|
|
|
|
|
| Re: Success event log [message #606520 is a reply to message #606485] |
Sat, 25 January 2014 10:42 |
 |
Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA
|
Senior Member |
|
|
So as you see, there are at least four ways to do logging of successful events. Three have already been mentioned.
1. add logging to your application
2. use Oracle auditing
3. create a trigger and do manual auditing
4. use a log miner tool of some sort (examples are Oracle log miner, Oracle GoldenGate, Quest SharePlex)
Logging is kind of a cool topic if only because it is a good example of what IT is all about (tradeoffs). None of these methods is ideal, meaning they each have advantages and detractors. If you want to log changes, then you will have to accept the tradeoff that goes with the method you use. I expect you will be reading about these methods before testing one or more of them right? Here is a quick summary of thoughts.
1. add logging to your application means making logging an application level piece of logic. This works as long as only one application is allowed to change data. Suppose however three years down the road say, after your application has been logging changes to various tables for this time, a new team creates a new application that modifies some of the same tables you are logging. Will this new team know that logging is being done on these tables? Will they know to write the necessary logging steps into their application? Maybe not, in which case some changes to tables will be logged and some not. OOPS!
2. use Oracle auditing means to exploit the built in auditing features of the database and means to make logging part of database logic. This is good, because it means it does not matter how changes are made, they will be logged and thus this avoids the problem noted in #1. The reality though is that most people don't use this so you will be one of the few that do. It is a sophisticated method and you will be required to learn it, not just write some quick code and be done with it.
3. create a trigger and do manual auditing means to make logging part of database logic. This is good, because it means it does not matter how changes are made, they will be logged and thus this avoids the problem noted in #1. But triggers are an advanced feature and as is common with advanced features in Oracle, they do not play well with other advanced features. For example, a table with triggers cannot participate in parallel DML, and there have at least in the past been issues with performance as triggers are not free and bulk operations in general being designed to affect many rows can suffer a significant cost though this can be mitigated with COMPOUND TRIGGERS (oooooh deeper and deeper).
4. use a log miner tool of some sort (examples are Oracle log miner, Oracle GoldenGate, Quest SharePlex) means the logic for logging has nothing to do with the transaction; it is neither application based nor database based. The advantage here is that there is no overhead for the logging operation if your database is already logging because the necessary work is already being done, and the logging extraction costs are offloaded to a separate time and place. Additionally the problem noted in #1 is again avoided because applications are not aware of data being logged. The disadvantages are many:
. you must be in logging or forced logging mode
. some forms of changes are not logged anyway
. log files must be available to be mined and there is a limit to how far back you can go so you must mine and save or risk losing your log data
. log miner is not easy to use and replication tools that do log mining also have a learning curve
. no log info can be queried until after the "replication" or "mining" operation is done and moves the log data to a table
. there will be a delay between the time the data is changed and when the log info is available which depending upon method can be from a few seconds to days or weeks
As you can see there is plenty to consider when looking at logging. Good luck. Kevin
[Updated on: Sat, 25 January 2014 10:46] Report message to a moderator |
|
|
|
|
|
|
|
|
|
| Re: Success event log [message #606634 is a reply to message #606628] |
Mon, 27 January 2014 11:39 |
|
Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA
|
Senior Member |
|
|
yes, most all ask for triggers when it comes to data change logging.
My system DBAs also audit connections and other NON-DATA related auditable events using Oracle auditing. But auditing goes way beyond this. One only has to read the Oracle Database Security Guide to see how audting is not simple. Audting requires planning. Most people don't want to plan, they just want to type in a few commands and then move on figuring all is OK.
The complexity of security is one reason why there are specialized groups in most organizations who handle it. I would ask the OP if they have an understanding of their corporate security standards and can explain how what they plan to do fits into it. Additionall I would ask OP if they know who handles security in their organization and if these people have provide any guidance in how to implement what is needed.
My assumption was that OP was interested in the full picture. I will clarify that my databases do in fact use Oracle auditing to monitor access as you have suggested.
Kevin
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
