Page 447
To ensure that more than one copy of the control file is being maintained , you can query the v$parameter table to see the names of the control files currently being updated. This table often truncates the names of the control files, however. Another way is to put LIST on a line by itself at the start of the INIT.ORA file. When the instance starts, this parameter lists all the parameters used by the instance. The control files parameter shows the full names and locations of the control files used.
If a failure occurs with a control file (for example, if it is deleted accidentally ), remove the name of the missing control file from the CONTROL_FILES parameter of the INIT.ORA file. Then shut down and restart the instance.
In every Oracle instance, the background processes produce trace files and an alert file. The alert file records significant events in the life of the instance, such as instance startups and shutdowns and redo log file switches. The alert file provides information on errors that occur on the Oracle database.
The BACKGROUND_DUMP_DEST INIT.ORA parameter specifies the location of the trace files and the alert file. You safely can delete the trace and alert files, because they are re-created when the background processes need them.
In addition to the trace files produced by the background processes, user processes produce trace files, which generally are used for debugging and performance optimization. These trace files are produced in different ways in the Oracle tools. For example,
alter session set SQL_TRACE true
is used to start tracing in a SQL*Plus or an Oracle Reports module. Similarly, setting the Statistics option in Oracle Forms shows the number of cursors used during the runform session and produces a trace file.
The name of the trace file is platform-dependent, and the location is given by the USER_DUMP_DEST INIT.ORA parameter. The raw trace files produced are not easy to handle. Use the TKPROF Oracle utility to format the trace files so that they are easier to read. You can see information such as the number of logical and physical block reads, parses, executions, and fetches for the different SQL and PL/SQL statements that occurred while the trace was running. To sort the trace output, use the command-line parameters of TKPROF. Show the slowest statements at the top of the trace output.
Information about the CPU and elapsed time taken by the statements appears only if the TIMED_STATISTICS INIT.ORA parameter is set to TRUE.
You also can set tracing on for the whole Oracle instance. To do this, set the SQL_TRACE INIT.ORA parameter to TRUE. The usual effect is drastically reduced instance performance, however, because all the user-generated and system-generated statements must be recorded in the trace files.
Page 448
This section discusses two modes in which the database can operate : NOARCHIVELOG, the default (which means that redo logs are not archived when filled), and Archive Log mode (which essentially means that copies of the redo log files are made when they fill).
The default mode of a database is NOARCHIVELOG. When one of the online redo log files fills, a checkpoint is made and further old and new values are written to the next online redo log file. The redo log files are not archived to the archive destination. Recovery is limited to instance recovery. That is, you can protect against the instance crashing. If you lose the database or other files, however, you must restore the files to the latest backup, and all changes made to the database since the last backup cannot be reapplied. This provides the simplest setup. This setup could be applicable for a development database, in which losing the database is not such a big deal and the resources to manage archiving are not available. This setup also is simpler to maintain, because the problem of not having enough space to archive the redo logs does not occur.
If the database runs in Archive Log mode, the redo logs are copied over to the archive destination whenever one of the online redo log files fills. The background process, ARCH, should be set up to copy the online redo log file to the archive destination automatically. Otherwise, the DBA must manually copy the files when they fill. If the online redo log files cannot be archived (either automatically by the ARCH background process or manually by the DBA), the whole database waits until the archiving is done. Setting the database in Archive Log mode obviously involves more work for the DBA, but it provides an extra level of protection. If the database, control, or redo log files are lost, they can be restored from the most recent backup, and all the changes made since the last backup can be reapplied automatically from a combination of the archived and online redo logs.
To change the mode of the database from the default mode, NOARCHIVELOG, to Archive Log so that redo log files are archived, perform the following steps in the Instance Manager tool:
Page 449
Table 19.2 describes the main data dictionary objects. There are others, but the major ones are shown here. The objects are classified by purpose:
UDIT | Views related to the Oracle audit facility |
DB STRUCT | Information about the structure of the database as a whole |
DUP | Information shown in other views |
LOOK | Lookup tables that hold static data or data not likely to change often for the database or instance |
MISC | Miscellaneous views |
MON | Views useful in monitoring current activity in the instance |
OBJ STRUCT | Information about the structure of objects within the database |
PREV | Views for Oracle5 and Oracle6 |
SEC | Security information, including users, roles, and privileges that have been granted or received |
TUN | Views useful in tuning |
NOTE |
If you are not familiar with the dictionary tables and views, ignore the AUDIT, DUP, and PREV objects. Oracle auditing rarely is used. DUP views show information that is available elsewhere. PREV views are provided for compatibility with previous versions of Oracle. |