Here is a list of the common parameters that you will find in your initialization files for Oracle databases. The other parameters are used in special circumstances, but these are the ones that you will see in almost every initialization file:

•  db_name This is the internal name of the database. I usually make it the same as the name of the SID, just to keep things simple, but there are no restrictions on this. It does get coded into the control files, so you have to use the right name once the database is created.

•  db_files This lists the maximum number of database files permitted.

•  control_files This parameter lists the control files that are out there. These control files need to be identical to one another and all must be available at startup. If one control file is missing due to a damaged disk drive, you can remove it from this list of control files and start the database with the remaining control files.

•  compatible This sets up the internal configuration of Oracle’s data dictionary to be compliant with a certain release of the Oracle DBMS software. You could, for example, set compatibility to 7.2 while running Oracle8 software (there is no guarantee that this compatibility will function exactly as it would if you had the 7.2 software installed, but there is a good chance that it will). This enables you to have database applications that were written for older versions of the software running under newer versions of the software without being updated internally.

•  db_file_multiblock_read_count This determines the number of data blocks that are written at a time.

•  db_block_buffers This determines the number of blocks in the SGA that are allocated to database block buffers.

•  shared_pool_size This determines the amount of shared memory that is allocated to the shared pool.

•  log_checkpoint_interval This determines the period in seconds between log checkpoints.

•  processes This determines the maximum number of processes that can be associated with the Oracle instance.

•  log_buffer This determines the number of blocks allocated to the redo log buffers in the SGA.

•  audit_trail This parameter is either TRUE or FALSE, depending on whether you want to enable auditing.

•  max_dump_file_size This limits the size of a dump file when there is a problem detected within Oracle. It prevents you from overflowing the disk space that you have allocated for dumps.

•  log_archive_start This determines whether the archive log process is started.

•  background_dump_dest This specifies the file location for the alert log and dump files associated with the Oracle database processes.

•  user_dump_dest This determines the file location for dump files associated with problems with end user processes.

•  db_block_size This determines the size of a block in this Oracle database (both on disk and in memory).

•  rollback_segments This specifies which rollback segments are to be brought online at database startup.

Log and Trace Files

Recall that Oracle really wants to help you out when a problem occurs. Therefore, it takes a few CPU cycles in its utilities to write out a record of important events that occur to a set of log files. The log file that records major events in the RDBMS itself is the alert log file. As always, the exact name and location of this file is a little more complicated than just alert_log. The actual file contains the name of the instance with which the events are associated. For our near-famous blue Oracle instance, the alert log file would be called alert_blue.log. The next logical question is what exactly gets placed in the alert log:

•  Major DBA activities, such as starting or stopping the instance or issuing commands that create, modify, or drop the database, its tablespaces, or rollback segments

•  Internal Oracle errors

•  Messages related to multithreaded server problems

•  Errors related to automatic refreshes of a snapshot

The trace files are produced by the Oracle background processes when they sense that they have a major problem or when one of the processes detects that another background process is in trouble or missing. The content of the message varies depending on how much information the process writing the message can sense. It usually contains the date and time of the problem, with which background processes the problems occurred, numbered Oracle error messages that you can use to discuss the problem with Oracle, and some explanatory text. If you have a problem with Oracle, save these messages until after you have determined the exact cause of the problem and have a fix for it. Some versions of Oracle write trace files every time you start. This does not indicate errors; it’s just logging a successful start.

One common Oracle feature that does not store its logging information in the alert log file is the SQL*Net or Net8 process. Instead, you will find a log file under the appropriate SQL*Net directory related to the protocol you are using for your system (TCP/IP, for example). This makes some sense in that alert logs are tied to a specific instance, but a single SQL*Net process can service multiple instances.

Now that you are enthusiastic about the wealth of information that is available to you in the log and trace files, you probably can’t wait to learn where to find them. The answer is that the location where these log files are kept is a parameter specified in your initialization files (specifically the config.ora for the instance in question). That parameter is called BACKGROUND_DUMP_DEST (the destination for the dump files of the Oracle background processes, which is typically ORACLE_BASE/admin/instance_name/bdump).

One final topic to consider is cleaning up after yourself. The log files just keep growing and growing as more events are recorded. The trace files sit out there until you do something with them, eating up more and more disk space. Therefore, it is a good idea to implement housekeeping procedures wherein you regularly clear out the log, core, and trace files. My favorite option is to set up a script that automatically copies the alert and SQL*Net log files to files that have the date that they created as part of their name (for example, alert_blue.log.013095). Then purge all the trace and log files in the appropriate directories that are over 30 days old. The system stays clean and you never have to scan through a 1MB log file to look for data on a problem that you encounter.