14.5. Summary

In this chapter, you learned about many of the vulnerabilities of the Oracle 10g listener and the steps that you can take to secure the listener. You learned about the role that the listener plays in the database, and that it can do more than simply handle database session requests. You learned that the listener can be controlled remotely and about the risks inherent in that capability.

You learned the importance of setting a password for the listener to prevent unauthorized usage. You saw that you can control database access by using the TCP.VALIDNODE_CHECKING, TCP.INVITED_NODES, and TCP.EXCLUDED_NODES options in your sqlnet.ora file. You saw that these options allow you to specify which hosts the listener would accept or which hosts the listener would deny by listing them in the appropriate section.

You learned to enable listener logging to ensure that a log file would be produced of all listener activity. You learned simple methods to monitor the log file for anomalies. You saw that failed password attempts produce a TNS-01169 error in the log file. Therefore, brute force password attacks could be identified by a large number of TNS-01169 messages in the log file.

Next, you learned that, by default, the listener is configured to accept external procedure requests. You saw that these external procedure requests can call C procedures from shared library and DLL files. Because many Oracle environments have no need for external procedures, you learned how to remove external procedure services from the listener.

For environments where external procedures are required, you learned that you could create a separate listener to handle only external procedure requests. You saw that this listener could be secured by executing it from an operating system account with only minimal privileges. You also discovered that you could limit the libraries accessible through external procedure requests through the EXTPROC_DLLS environment variable.

We wrapped up our look at listener security by discussing a few other standard security measures. These measures included using the ADMIN_RESTRICTIONS parameter to disable listener configuration changes through the lsnrctl program. Other measures include removing write privileges from the $ORACLE_HOME/network/admin directory, keeping up-to-date on listener-related security patches, and blocking SQL*NET traffic through your firewalls.

Next, we moved on to Oracle diagnostic sources. You learned about the alert log and how it logs significant events in the database. You learned the importance of checking the alert log on a regular basis. We discussed the options available for viewing the contents of the alert log. You also saw methods in which the alert log content could be loaded as an external table and viewed using standard SQL statements.

We also discussed alert log monitoring options through the EM Database Control application. You learned about predefined alert log metrics that are computed automatically. You also discovered that threshold settings on alert log metrics could be used to generate alerts when specific values are found.

Next, you took a quick look at Oracle trace files. We discussed the differences between background session trace files and user session trace files. You learned that background sessions will dump a trace file when an error is encountered. These trace files can be used to diagnose system problems.

You learned that user session trace files are created when the SQL_TRACE parameter is set to TRUE. These trace files can be used to view in-depth execution information for the session. SQL tracing can be enabled for the entire instance or for specific user sessions.

You learned about the parameters that control the sizing and location of trace files. You saw that the TRACEFILE_IDENTIFIER parameter could be used to append a character string to the name of user session trace files to make them easier to identify. However, the TRACEFILE_IDENTIFIER setting has no effect on background session trace files.

Then we discussed memory management in Oracle 10g. You learned about all of the major memory structures in both the SGA and PGA. You saw how manually managing memory is problematic due to the granularity of memory pools and the constant fluctuations in workload requirements.

You learned that ASMM can be used to dynamically size the major SGA memory structures as workload requirements change. You saw that ASMM identifies memory structures as automatically sized components and manually sized components. Manually sized components continue to be set using traditional Oracle memory-sizing methods. You learned that ASMM will be enabled if the initialization parameter SGA_TARGET is set to a non-zero value. You also learned that statistics collection functionality must be set to TYPICAL or ALL in order for ASMM to function.

Lastly, you learned about the PGA and how APMM is Oracle's default method of managing SQL work area sizing. You saw how APMM dynamically allocates PGA memory to meet workload demands. APMM sizing is based on the value set for the SGA_TARGET initialization parameter. If the parameter is not set, it defaults to the greater of 10MB or 20 percent of the SGA size. You also learned that APMM can be overridden through the use of the WORKAREA_SIZE_POLICY parameter, though this practice is strongly discouraged by Oracle.