Topic: SQL*Plus and PL/SQL >> Auditing a Procedure/Function
|
Disclaimer: The purpose of all dbapool forums including OCP and Other Oracle Certification forums is to help each other with specific issues but not to share dump and copyrighted exam content, materials or intellectual property.
You may review the entire Oracle Certification Program Candidate Agreement online Here.
|
|
|
|
| Title: Auditing a Procedure/Function |
| Vijay |
Posted: Apr 24, 2008 02:12:51 AM |
Total Post: 12
Joined: Jul, 2007
|
Dear All,
I have created a procedure in my development server.
I want to ensure and see what are the changes made between specific date and the user who had made the changes.
I want to make use of the auditing feature for this.
If any developer/dba can throw some light on this.
Thanks in advance.
Regards,
Vijay Mahawar.
OCA. |
|
|
 GOPU |
| Posted: Apr 24, 2008 05:32:06 AM |  |
|
Total Post: 281
Joined: Apr, 2008
|
Hi
Here is the process by which we can audit the data and recover previous data.
Start Mining
Let's return to the issue of Ms. Smith's account. John can start looking for how and when the account manager was changed by using Flashback Query to select prior values of the record in this table. Flashback Query is an Oracle feature that lets a user see the value of a column at some point in the past, before that value may have been changed and committed.
However, the Flashback Query approach works only if a change occurred within the period specified by the UNDO_RETENTION parameter, which is often as short as 30 minutes for an OLTP database. In this case, there's a good chance that Flashback Query will not find the change.
John can find the answer to the question at hand by looking directly at the online redo log files, using LogMiner. Here are the steps for setting up and using LogMiner:
1. Install the DBMS_LOGMNR package. John installs the package by logging on to SQL*Plus as SYS and executing the $ORACLE_HOME/rdbms/admin/dbmslm.sql file.
2. Grant the role. John then grants the user who will do the mining—in this case, himself—the appropriate role to execute this package:
3. GRANT EXECUTE_CATALOG_ROLE TO JOHN;
4. Create the synonym. John creates a public synonym:
5. CREATE PUBLIC SYNONYM DBMS_LOGMNR FOR SYS.DBMS_LOGMNR;
These first three steps are required just once.
6. Specify the scope of the mining. Because the error may have happened just recently, it's possible that the change is still within the online redo log files. So, for the first pass, John decides to mine only the online redo log files. He identifies the files by running the following query:
7. SELECT distinct member LOGFILENAME FROM V$LOGFILE;
8.
9. LOGFILENAME
10. ________________________
11. /dev/vgredo01/rlog1a
12. /dev/vgredo01/rlog1b
These are the two redo log files for the database. John limits the scope of mining to only these files, by executing the following:
BEGIN
DBMS_LOGMNR.ADD_LOGFILE
('/dev/vgredo01/rlog1a');
DBMS_LOGMNR.ADD_LOGFILE
('/dev/vgredo01/rlog1b');
END;
13. Start the LogMiner session and specify a dictionary. John executes the following SQL to start the LogMiner session:
14. BEGIN
15. DBMS_LOGMNR.START_LOGMNR
16. (options =>
17. dbms_logmnr.dict_from_online_catalog);
18. END;
Using the OPTIONS parameter, he also specifies that Oracle Database read the dictionary information to convert the object names from the online catalog while starting LogMiner.
As previously mentioned, redo log entries are not written in clear text. LogMiner provides human-readable reporting from the redo log files, but some reported values may not look like the original objects. For example, the owner, table, and column names are not recorded in the online redo log in the same format users input; they are instead converted to hexadecimal values. For instance, the ACCOUNTS table may be recorded as OBJ#45C1. To make the redo log data more readable, John can ask LogMiner to convert these values to more recognizable ones by translating them from a dictionary.
19. Check contents. Starting the LogMiner session populates a view named V$LOGMNR_CONTENTS, which John can then query to find out what happened to Ellen Smith's account. The query looks for the users and times of any updates against the ACCOUNTS table. The query against V$LOGMNR_CONTENTS and the output are shown in Listing 1. Note that John must query this view from the same session in which he started the LogMiner session. Because he is looking only for updates, he uses the predicate in Listing 1, line 6.
|
|
|
|
|
| Murtuja |
| Posted: Apr 26, 2008 10:04:42 AM | |
|
Total Post: 856
Joined: Jan, 2006
|
Hi Gopu,
Please don't copy/paste content from any sites. It’s can create copyright issues and effect this forum.
Instead of copy/paste just point to that site.
Original content is from this site:
http://www.oracle.com/technology/oramag/oracle/05-jul/o45dba.html
|
|
|
|
|
| Vijay |
| Posted: Apr 30, 2008 01:31:26 AM | |
|
Total Post: 12
Joined: Jul, 2007
|
Thanks Gopu and Murtuja.
It would have been great if you could have replied me in simplified manner.
Thanks once again.
Regards,
Vijay Mahawar
OCA. (Pursuing OCP).
|
|
|
|
|
| Time Zone: EDT |
Send this thread to your friend |
|
|
|
|
Forum Rules & Description
Who Can Read The Forum? Any registered user or guest
Who Can Post New Topics? Any registered user
Who Can Post Replies? Any registered user
|
| |
Get FREE Magazines
|
Top 10 Forum User
|
| Murtuja Khokhar | 856 |
| Mohammed Taj | 746 |
| positive fanatic | 483 |
| Jayanta Sur | 479 |
| Vigyan Kaushik | 386 |
| Gitesh Trivedi | 322 |
| Gopu Gopi | 281 |
| neeraj sharma | 254 |
| Ramesh Jois | 245 |
| Bishal Khetan | 207 |
|
|
