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:

Make sure that you have added each parameter shown in the diagrams above 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.

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.
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.