| Lesson 9 | Other parameter settings |
| Objective | Set additional database parameters |
Additional Database Parameters
We have just gone through the most critical database parameters one by one. This lesson will briefly cover some other parameters that you should
set when creating a new database. Look through the following series of images to see a description of each parameter:
Oracle Database Parameters
background_dump_dest = c:\oracle\admin\COIN\bdumpEvery database has an alert log file, to which it logs messages about significant events such as startup, shutdown, log switches, and so forth. Any serious errors are also logged to this file. The background_dump_dest parameter controls the location of the alert log file.
user_dump_dest = c:\oracle\admin\COIN\udumpThe user_dump_dest parameter controls the directory used for trace files created by database users for debugging purposes.
db_files = 20The db_files parameter controls the amount of memory that Oracle allocates for managing database files.
compatible = 8.1.5The compatable parameter allows you to have Oracle mimick the behavior of a previous release of the software.
processes =30The process parameter controls the number of server processes that can connect to the instance. This effectively limits the number of database users. This value includes lock processes (LCKn) and job queue processes (SNPn). So if your instance has one lock process, and one job queue process, that leaves room for 28 user processes.
optimizer_mode =chooseThis is a setting that Oracle recommends for all new development. It enables the use of the cost-based optimizer, as opposed to the rule-based optimizer. The cost-based optimizer makes decisions about how to execute a query based on its estimate of the actual work that needs to be done. All of Oracle's development efforts are going into enhancing the cost-based optimizer.
Make sure that you have added each parameter in the series of images to your initCOIN.ora file. Remember to double-check the directory paths, so that setings such as background_dump_dest point to a real location.
Configuring the Fault Diagnosability Infrastructure
For the purposes of setting up the Fault Diagnosability Infrastructure for an Oracle database, what we are concerned is the setting of the new parameter DIAGNOSTIC_DEST, which defines the root of the ADR and deprecates several other parameters, including USER_DUMP_DEST, CORE_DUMP_DEST, and BACKGROUND_DUMP_DEST. As a result, if you create a new Oracle database with the DBCA, you will not find the alert log or user trace files where you previously would have expected them.
Before we discuss configuring the ADR, you should know that the process of configuring does not change if you are running Oracle Multitenant in Oracle Database 12c. The ADR structure remains the same because it stores information at the grain of the CDB, not at the grain of the PDB. That being said, if a session connected to a PDB raises an error that the ADR records, it will still be recorded in the ADR, it simply will not be recorded in a directory that is specific to that PDB. Rather it will be created in the directories that are at the level of the CDB itself. Even trace files are listed at the CDB level, so if you enable SQL tracing in a PDB, the resulting trace file will have the name of the CDB in the filename, not the name of the PDB.
By default, the DIAGNOSTIC_DEST parameter is set to $ORACLE_BASE. If $ORACLE_BASE is not set, the parameter is set to the value of $ORACLE_HOME. The root directory of the ADR directory structure starts with a directory called diag, under which is a subdirectory that references the product type. For example, for the database, the product is called rdbms. Under rdbms is a directory for each database, followed by a directory for each individual instance.
Before we discuss configuring the ADR, you should know that the process of configuring does not change if you are running Oracle Multitenant in Oracle Database 12c. The ADR structure remains the same because it stores information at the grain of the CDB, not at the grain of the PDB. That being said, if a session connected to a PDB raises an error that the ADR records, it will still be recorded in the ADR, it simply will not be recorded in a directory that is specific to that PDB. Rather it will be created in the directories that are at the level of the CDB itself. Even trace files are listed at the CDB level, so if you enable SQL tracing in a PDB, the resulting trace file will have the name of the CDB in the filename, not the name of the PDB.
By default, the DIAGNOSTIC_DEST parameter is set to $ORACLE_BASE. If $ORACLE_BASE is not set, the parameter is set to the value of $ORACLE_HOME. The root directory of the ADR directory structure starts with a directory called diag, under which is a subdirectory that references the product type. For example, for the database, the product is called rdbms. Under rdbms is a directory for each database, followed by a directory for each individual instance.
For example, if $ORACLE_BASE is /u01/oracle, the database name is mydb, and the database instance is mydb1, then the structure of the ADR directory for that database will be /u01/oracle/diag/rdbms/mydb/mydb1. This directory structure is called the ADR home, and each instance has its own ADR home. If you are using RAC, you can either use shared storage for ADR or individual storage on each node. We would recommend shared storage in a RAC environment because you can see the aggregate diagnostic data from any node. Also, a shared ADR allows for more robust recovery options for the Data Recovery Advisor. However, the truth is that we usually see ADRs stored on local, nonshared storage most of the time. Under this directory structure will be a number of other directories. Here are some of the most common ones:
Alert This is the location of the XML-formatted alert log. cdump This is the location of the core dumps for the database. Trace This directory contains trace files generated by the system, as well as a text copy of the alert log. Incident This directory contains multiple subdirectories, one for each incident.
Alert This is the location of the XML-formatted alert log. cdump This is the location of the core dumps for the database. Trace This directory contains trace files generated by the system, as well as a text copy of the alert log. Incident This directory contains multiple subdirectories, one for each incident.
