Database Reference
In-Depth Information
Feb 21 14:46:53 dbserver Oracle Audit[29170]: [ID 853627 auth.info]
ACTION : 'drop user appuser'
Feb 21 14:46:53 dbserver DATABASE USER: '/'
Feb 21 14:46:53 dbserver PRIVILEGE : SYSDBA
Feb 21 14:46:53 dbserver CLIENT USER: oracle
Feb 21 14:46:53 dbserver CLIENT TERMINAL: pts/3
Feb 21 14:46:53 dbserver STATUS: 0
Lessons Learned
CONNECT
,
STARTUP
, and
SHUTDOWN
with
SYSDBA
or
SYSOPER
privileges are logged to
*.aud
files by
default in spite of an
AUDIT_TRAIL=NONE
setting. If
AUDIT_SYSLOG_LEVEL
is set, the SQL*Plus
STARTUP
command is logged to a
*.aud
file in
$ORACLE_HOME/rdbms/audit
, whereas
ALTER
DATABASE
MOUNT
and subsequent commands as well as
SHUTDOWN
are logged via syslog, since a running
instance is required for using the syslog facility and the instance is not yet running when
STARTUP
is issued.
Setting
AUDIT_SYSLOG_LEVEL
and
AUDIT_SYS_OPERATIONS=TRUE
produces additional auditing
trail records covering all actions performed with
SYSDBA
or
SYSOPER
privileges in the configured
syslog
log file irrespective of the setting of
AUDIT_TRAIL
. Intruders who have not managed to
break into the account of the UNIX user root, will not be able to remove these audit trail records.
Of course, an intruder who is aware of these features might remove the
AUDIT_SYSLOG_
LEVEL
setting, but at least the parameter change would be logged if an
SPFILE
is used, and the
change would not be in effect immediately since it is a static parameter. You may wish to set
AUDIT_SYS_OPERATIONS=FALSE
during maintenance operations such as an upgrade (which have
to be run as user
SYS
) to avoid generating large syslog log files.
PGA_AGGREGATE_TARGET
The initialization parameter
PGA_AGGREGATE_TARGET
is documented in
Oracle9i Database
Performance Tuning Guide and Reference Release 2
and in
Oracle Database Performance
Tuning Guide 10g Release 2
. The aforementioned Oracle9
i
manual states that the parameters
SORT_AREA_SIZE
and
HASH_AREA_SIZE
for manual PGA memory management should not be
used, except in Shared Server environments, since Oracle9
i
Shared Server cannot leverage
automatic PGA memory management (pages 1-57 and 14-50). The algorithm that governs
individual work area sizing for serial and parallel execution is undocumented.
Knowing the undocumented restrictions imposed on work area sizing allows DBAs to set
the most appropriate value for
PGA_AGGREGATE_TARGET
, thus avoiding expensive spilling of work
areas to disk and allowing operations to run entirely in memory, realizing significant perfor-
mance gains. Under rare circumstances it may be desirable to override automatic settings of
hidden parameters affected by
PGA_AGGREGATE_TARGET
.
Introduction to Automatic PGA Memory Management
The program global area (PGA) is a private memory region where server processes allocate
memory for operations such as sorts, hash joins, and bitmap merges. Consequently, the PGA
memory region is separate from the SGA (system global area). There is even a third memory