Viewing SYSDBA and SYSOPER operations through Grid Control (OEM)

Auditors need to see that I track operations performed as SYSOPER or SYSDBA. Here is how I do it. First, in the database, log in as SYSDBA and issue:

SQL> alter system set audit_trail=xml scope=spfile
SQL> alter system set audit_sys_operations=true
SQL> shutdown immediate;
SQL> startup;

Ok, now be sure to create an OS-level space monitor to do something with those .xml files that will be rapidly clouding your $ORACLE_BASE/admin/(db_name)/adump directory. I will explain why you need to use XML in a moment, instead of audit_trail=DB. Now, go into Grid Control and click on the home page for the instance you just restarted. You must be sure to log into the database as SYSDBA and be sure to choose "Save as Preferred Credential" for this instance . Next, go to the reports tab, and go to Reports -> Security -> Database Targets -> SYS User Operations. On the next page, click the flashlight and choose the instance you just restarted and click continue.

If you receive an error like "Failed to render element. Error: This report only applies to database with version 10.2 and up." it means that you did not properly log into the database instance as SYSDBA and set the preferred credentials. Try going back to the instance home page, logging out, then logging back in being sure to click "Save as Preferred Credential". I have seen cases where immediately after checking the checkbox, the page refreshes, and un-checks it. This can be easily overlooked while you are going to click the "Login" button. Now retry the steps above on the Reports tab. If you experience a problem, please post a comment on this page so someone can help you!

If all went well, you should now have a listing of your audit trail. I like to choose "Show All xxx" and then click the "Time" heading twice to sort by most recent activity. Viola! Now you know what is going on in your systems. The next step is to put alerts on the things that you care about, like "Page me when someone logs into my instance as SYSDBA or SYSOPER."

Ok, so why do you need to set audit_trail=XML? I believe it is a requirement by OEM. I don't recall the exact message but setting audit_trail=DB did not display any records and gave a message indicating that you must set audit_trail to DB or XML. Since I was already set to DB, I decided to try XML, and it worked. CG version 10.2.0.4. Will 10.2.0.5 show audit records with audit_trail=DB? I dunno.