Database Reference
In-Depth Information
Leveraging Events at the Instance-Level
Several scenarios mandate setting events at the instance level. These are:
Enabling or disabling bug fixes
Enabling or disabling features, such as cost-based optimizer access paths
Tracing certain code paths or features in all processes of an instance
Enabling or disabling certain checks
Writing a diagnostic dump whenever an ORACLE error (ORA-
nnnnn
) is raised in any
database session
Consider the parameter
EVENT
whenever events must be set right when a process starts
or for all processes of an instance. While it is absolutely feasible to obtain SQL trace files for
multiple processes, such as those originating from a connection pool by setting event 10046
with parameter
EVENT
, I am a strong opponent of such a procedure because more sophisticated
approaches, such as using a logon trigger or
DBMS_MONITOR
exist. Setting event 10046 at level 8
or 12 with the parameter
EVENT
is better than setting
SQL_TRACE=TRUE
at the instance level, since
wait events and binds may be included; however, both incur the unnecessary overhead of
tracing each and every process of the instance. I certainly wouldn't be willing to sift through
dozens or even hundreds of trace files to find a few relevant ones when other features allow
tracing just the processes of interest.
Case Study
Recovery Manager (RMAN) supports writing backups to file systems and to third party media
managers. Writing backups to a file system works out of the box and does not incur additional
expenses. Since writing to a local file system does not protect against the failure of the database
server hardware as a whole, writing to remote file systems or network-attached storage (NAS)
arrays via NFS is supported too. RMAN has several undocumented requirements concerning
NFS mount options. If the mount options
hard,rsize=32768,wsize=32768
are not used, RMAN
will refuse to write to an NFS file system. However, certain releases of RMAN still throw an error
when these requirements are met. Under these circumstances, Oracle Support has suggested
setting event 10298 at level 32 as a temporary workaround until the underlying issue is resolved.
This is a case for setting an event at the instance level with parameter
EVENT
. With other
methods for setting events, such as
ORADEBUG
or
DBMS_SYSTEM
, it is impossible to set the event in
time for the multiple processes that RMAN spawns. Furthermore it would be too cumbersome
to set the event after each instance startup with
ALTER
SYSTEM
SET
EVENTS
.
OS_AUTHENT_PREFIX
The initialization parameter
OS_AUTHENT_PREFIX
is documented in the
Oracle Database Reference
manual. It is undocumented that a database user name that is prefixed by the string
OPS$
allows for local authentication through the operating system and password authentication
when connecting over a network.
Since
REMOTE_OS_AUTHENT=FALSE
should be set for security reasons, it's impossible to use
externally-identified users to connect to an instance over a network, e.g., using Oracle Net and
