Page 77
Parameter Name | Use |
log_archive_start | Enables or disables automatic archiving. When true, the ARCH process will automatically start when the instance is started. |
log_buffer | Number of bytes allocated to the redo log buffer. |
log_checkpoint_interval | The number of redo log file blocks that must be filled to trigger a checkpoint. |
max_dump_file_size | Maximum size in operating system blocks of Oracle trace files. |
processes | Maximum number of OS processes that can connect to the database, including the background processes. Important when tuning shared memory on a UNIX server. |
remote_login_passwordfile | Specifies whether a password file is used for remote internal authentication, and how many databases can use a single password file. Can be set to NONE, SHARED, and EXCLUSIVE. |
rollback_segments | List of rollback segments to automatically take online at database startup. |
sequence_cache_entries | Number of sequences that can be cached in the SGA. Should be set to the maximum number of sequences that will be used in the instance at one time. |
shared_pool_size | Size of the shared pool, in bytes. |
snapshot_refresh_processes | Number of SNP processes to start at instance startup. SNP processes are responsible for refreshing snapshots and running database jobs submitted with DBMS_JOB. |
timed_statistics | Enables or disables the collecting of timing statistics for the database. While setting this to true incurs a minimal performance overhead, it allows much greater flexibility in database tuning. |
user_dump_dest | Destination directory for user trace files,including those generated by setting sql_trace to true. |
The Control FileThe control file is the heart of the database. It contains information on what datafiles and redo log files belong to the database, what character set the data should be stored as in the database, the status and revision of each datafile in the database, and other critical information. Most of the parameters contained in the control file are set during database creation and are relatively staticthey do not change from day to day. The control file is in binary format and cannot be read or edited manually.
Page 78
The control file is created when the database is created. Most databases operate with multiplexed control files (as explained later) and are therefore usually referred to in the plural. The specific control files created are those specified in the CONTROL_FILES init.ora parameter. The database creation parameters specified in the CREATE DATABASE clause are stored in these files.
The database cannot be opened without the correct control files. If the control file is unavailable or corrupted for some reason, the database cannot be started and the data contained in the database cannot be accessed. For this reason, mirroring of the control files is internally supported by the Oracle server and is highly recommended. To mirror control files in a new database, merely specify more than one value for the CONTROL_FILES init.ora parameter before issuing the CREATE DATABASE command. To mirror control files in a preexisting database, you must shut down the database, copy the current control file to the directories where you want it to be mirrored, edit the CONTROL_FILES parameter to specify the new control file locations, and start the database.
NOTE |
A good rule of thumb is to store no fewer than three copies of the control files on three separate physical disks. |
Unfortunately, modifying control file parameters is not as easy as changing an initialization parameter and bouncing the database. To change any of the control file parameters, you must re-create the control files. Follow these steps to re-create your control file:
You can avoid ever having to re-create your control files by setting the database parameters during database creation to values higher than you'll ever need. The only thing wasted in setting these control file parameters higher than needed is a negligible amount of disk space.
Page 79
CAUTION |
It's important that you set the CHARACTERSET parameter correctly when you create the database. Changing this parameter requires re-creating the entire database. It cannot be changed by rebuilding the control file. |
The configurable control file parameters are listed in Table 6.2.
Table 6.2 Modifiable Configuration Parameters Contained in the Control File
Parameter Name | Description |
MAXLOGFILES | Maximum number of online redo log files |
MAXLOGMEMBERS | Maximum number of members per redo log file |
MAXDATAFILES | Maximum number of datafiles |
MAXINSTANCES |
Maximum number of instances that can mount this database (parallel server) |
MAXLOGHISTORY |
Maximum number of archived redo log file groups to use for instance recovery (parallel server) |
NOTE |
To change the database name, re-create the control file as described, but change the REUSE DATABASE "OLD_NAME" line in the trace file to SET DATABASE "NEW_NAME". |
The V$CONTROLFILE view lists the control files that the Oracle server is currently reading from and writing to.
Online Redo Log FilesThe log writer background process (LGWR) writes the contents of the redo log cache to the online redo log files. The redo logs store all of the change information for the database and are used by Oracle during database recovery.
As shown in Figure 6.1, the online redo log files are made up of at least two groups of redo log files and are written to in a circular nature. A redo log group is active if it is currently being written to by LGWR. A log switch occurs when the current log group fills up and LGWR stops writing to it and moves on to the next one. When a log switch occurs and archivelog mode is enabled for the database, the redo log group previously written to is locked by the archiver (ARCH) process and copied to disk or tape, depending on your configuration. If LGWR catches up with the ARCH process and needs to write to the group currently being written to by ARCH, all database activity will be suspended until ARCH finishes writing to the log. If you see errors in your alert.log file stating that a free log group could not be latched by LGWR, this behavior is occurring in your database, indicating that you need to add more redo log groups or adjust the size of the groups.