A recent OTN Thread inspired this posting: How can we track down initialization parameter changes that someone has done with ALTER SYSTEM? One way would be to monitor the alert.log, but I think auditing should be most appropriate here. Preparing the playing field:
SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
CORE 11.2.0.3.0 Production
TNS for Linux: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production
SQL> show parameter audit_trail
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
audit_trail string NONE
When you use DBCA to create an 11g Database, AUDIT_TRAIL will show as DB – I did the creation on the command line, therefore it is still NONE. When we want to see the particular ALTER SYSTEM command like in the demonstration, the value EXTENDED is additionally required.
SQL> alter system set audit_trail='DB','EXTENDED' scope=spfile; System altered. SQL> shutdown immediate ORACLE instance shut down. SQL> startup ORACLE instance started. Total System Global Area 313159680 bytes Fixed Size 2227944 bytes Variable Size 255852824 bytes Database Buffers 50331648 bytes Redo Buffers 4747264 bytes Database mounted. Database opened. SQL> audit alter system by access; Audit succeeded. SQL> alter system set processes=250 scope=spfile; System altered. SQL> alter system set parallel_degree_policy=limited; System altered. SQL> alter system set result_cache_mode=force scope=memory; System altered.
You may notice that I was still SYS when doing the ALTER SYSTEM commands. That is important here, because they are not audited, as we can see below. If you want auditing for SYS, this posting shows how to do that.
SQL> grant dba to adam identified by adam; Grant succeeded. SQL> connect adam/adam Connected. SQL> alter system set processes=200 scope=spfile; System altered. SQL> alter system set parallel_degree_policy=manual; System altered. SQL> alter system set result_cache_mode=manual scope=memory; System altered. SQL> select username,sql_text from dba_audit_trail where sql_text like 'alter system%'; USERNAME SQL_TEXT ---------- ---------------------------------------------------------------------- ADAM alter system set processes=200 scope=spfile ADAM alter system set parallel_degree_policy=manual ADAM alter system set result_cache_mode=manual scope=memory
Conclusion: ALTER SYSTEM commands can be audited very comfortable into the audit trail as long as you don’t do them as SYS. All actions of SYS can be audited also with a different approach. As always: Don’t believe it, test it 🙂
Pickleball spielen 
#1 von santosh varma am Februar 26, 2013 - 12:17
Good post…..
#2 von rupa am Februar 28, 2017 - 11:14
What if we need to log the statements made in a particular interval, time period this is dialog way of checking the status of alter system, automatically if we need to set up audit logs how can we achieve it ? Appreciate if you have a solution for it.