Additional Database Parameters

We've just gone through the most critical database parameters one by one. This lesson briefly covers several other parameters worth setting when creating a new database. Look at the diagram below for a quick reference on each one, then read through the explanation that follows for the reasoning behind each setting.

Make sure you've added diagnostic_dest, and any of the other parameters from this lesson that apply to your situation, to your initCOIN.ora file. Double-check the directory path so diagnostic_dest actually points somewhere real on your system, an alert log that never gets written because the path doesn't exist is a frustrating way to lose your first afternoon of troubleshooting.

Setting up the Fault Diagnosability Infrastructure for an Oracle database comes down to one parameter: DIAGNOSTIC_DEST, which defines the root of the Automatic Diagnostic Repository (ADR) and supersedes several older parameters, including USER_DUMP_DEST, CORE_DUMP_DEST, and BACKGROUND_DUMP_DEST. If you create a new Oracle database with DBCA today, you won't find the alert log or user trace files where older documentation says to look for them, they've moved.
The ADR stores information at the CDB level, not per-PDB, since every Oracle database today is a multitenant container database. If a session connected to a PDB raises an error the ADR records, it still gets recorded, just not in a directory specific to that PDB, it lands in the CDB-level directories instead. Even trace files follow this rule: enabling SQL tracing in a PDB produces a trace file named after the CDB, not the PDB.
DIAGNOSTIC_DEST's default value follows a three-step fallback: first the $ORACLE_BASE environment variable, if that isn't set, the ORACLE_BASE value as configured by Oracle Universal Installer, and if neither is available, $ORACLE_HOME/rdbms/log specifically. The directory structure itself starts with diag, under which sits a subdirectory naming the product type, rdbms for the database itself, followed by a directory per database, then a directory per individual instance.
For example, if $ORACLE_BASE is /u01/app/oracle, the database name is COIN, and the instance name is COIN1, the ADR directory for that database is /u01/app/oracle/diag/rdbms/COIN/COIN1. This is called the ADR home, and every instance has its own. In a RAC environment, you can use shared storage for ADR or store it individually per node, shared storage is generally the better recommendation, since you can see aggregate diagnostic data from any node and it supports more robust Data Recovery Advisor options, though in practice local, non-shared storage is still common. Underneath the ADR home sit a handful of familiar subdirectories: alert for the XML-formatted alert log, cdump for core dumps, trace for system-generated trace files plus a text copy of the alert log, and incident, holding one subdirectory per incident.

DB_FILES sets the maximum number of database files Oracle will allow open at once, the ceiling you can grow into without restarting, not how many you have right now. The current default is 200, generous enough that most databases never touch it, but not something to treat as fixed either. Increasing it later requires shutting down and restarting every instance accessing the database, you can't raise it on the fly. So if you're building something you expect to grow, add some headroom above what you need today, so a future storage expansion doesn't force an unplanned outage.

PROCESSES caps the number of operating system processes, actual client connections plus every background process the instance runs, that can connect simultaneously. The enforced minimum is 80, which reflects how much background infrastructure a modern instance runs even before a single user connects: job queue processes, parallel execution servers, and various other workers all draw from this same pool. Rather than picking a number by hand, Oracle derives a sensible default based on the number of CPU cores reported in the alert log at startup, a better starting point than a manually chosen value in most cases. If you do override it, remember SESSIONS and TRANSACTIONS both derive their own defaults from PROCESSES, so changing it has ripple effects worth checking.

Older material frames optimizer_mode as a decision between the rule-based optimizer and the cost-based optimizer. That decision doesn't exist anymore; the rule-based optimizer is gone entirely, and every query goes through the cost-based optimizer today. The current valid values are ALL_ROWS, the default, optimizing for total throughput of the entire result set, and FIRST_ROWS_n for n of 1, 10, 100, or 1000, optimizing for how fast the first n rows come back, useful for interactive applications where a user is waiting on the first screen of results rather than the complete set. A plain FIRST_ROWS also exists for backward compatibility, though Oracle's own guidance is to use FIRST_ROWS_n instead when you want that behavior. For a small, low-concurrency database like COIN, the default ALL_ROWS is exactly right.

COMPATIBLE controls which Oracle version's on-disk data format the database is allowed to use, and by extension, whether you could downgrade to an earlier release later. The valid range is 19.0.0 through your current running release. Setting it lower than your actual release keeps disk structures compatible with that earlier version, at the cost of restricting or disabling newer features until you raise it. Historically, changing this parameter required a full restart. That's changed: starting with Oracle AI Database 26ai, Release Update 23.9, you can raise it dynamically without restarting the instance at all:

SQL> SHOW PARAMETER COMPATIBLE

NAME                      TYPE        VALUE
------------------------- ----------- -------------
compatible                string      23.4.0.0

SQL> ALTER DATABASE SET DOWNGRADE COMPATIBILITY TO '23.6.0';

Database altered.