I need to admit my lack of working on UNIX flavors.Few months back only i started working on databases on UNIX flavors, before this it was all windows family.Reason for my so much working on windows family needs to asked to my MANAGER 😉
I was informed about the space crunch issue by the support guys on the ORACLE_HOME mount point.As part of free up process all the tracefiles form adump,bdump,udump and cdump were removed.Interestingly, i saw “ora_xxxxx.aud” files in “/data2/oracle/admin/db1/adump/” which is supposed to be the location for adump.One of the file’s content was :-
-bash-3.00$ cat ora_8238.aud Audit file /data2/oracle/admin/db1/adump/ora_8238.aud Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production With the Partitioning, Real Application Clusters, OLAP, Data Mining and Real Application Testing options ORACLE_HOME = /data2/oracle/product/10.2.0/db_1 System name: SunOS Node name: isjfooj1 Release: 5.10 Version: Generic_137112-08 Machine: i86pc Instance name: prd2 Redo thread mounted by this instance: 2 Oracle process number: 22 Unix process pid: 8238, image: [email protected](TNS V1-V3) Wed Mar 24 12:40:25 2010 LENGTH : '132' ACTION : 'CONNECT' DATABASE USER: '/' PRIVILEGE : 'SYSDBA' CLIENT USER: 'oracle' CLIENT TERMINAL: '' STATUS: '0' Wed Mar 24 12:41:34 2010 LENGTH : '133' ACTION : 'SHUTDOWN' DATABASE USER: '/' PRIVILEGE : 'SYSDBA' CLIENT USER: 'oracle' CLIENT TERMINAL: '' STATUS: '0'
I immediately logged into the database and check for the values of parameter related to audit
SQL> show parameter audit NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ audit_file_dest string /data2/oracle/admin/db1/adu mp audit_sys_operations boolean FALSE audit_syslog_level string audit_trail string NONE
The value for parameter audit_sys_opertaion is set FALSE.I had the misconception that as this parameter is set to FALSE, why are then the files getting generated in adump folder??? Sys operations are not enabled to be audited.
With the same parameter values, no files are present in adump folders of db on windows environment,so is it some UNIX property that is audits ” connect as sysdba”?? I asked my friend and he said that the he has seen this in UNIX, but couldn’t give me any solid proof or answer.
So, i searched metalink, and i found Metalink DOC Id 103964.1
On Unix Systems, the administrative user connections are logged to special log files
created by Oracle and stored in $ORACLE_HOME/rdbms/audit directory.
At a minimum, a new file is created for each startup and shutdown event.
Administrative user connections are not written to database tables as these
connections are needed in order to start and stop the database. The files where
the administrative user connections are written to are stored externally from
the database so they can be accessed when the database is down. For example,
the administrative user connect to startup the database cannot be audited
(written) to a database table so auditing must be done externally to the
database, as opposed to other audited activity (as example: successfull/
unsuccessfull executions of specified SQL statements, auditing privileges or
The administrative user connections are always audited regardless of the init.ora
On Windows Systems, you can monitor audited connects to Oracle as an administrative
user (former connect INTERNAL as of 8.1.7 connections ‘ as sysdba ‘) in the
The administrative user connections are written to the operating system audit
trail as opposed to the database audit trail (sys.aud$ table).
-> Windows NT/2000/2003/XP
Go for: Start -> Settings -> Control Panel -> Services (Windows NT)
Start -> Settings -> Control Panel ->
Administrative Tools -> Services -> Event viewer->
Application log (Windows 2000/2003/XP)