0447-0449

Previous Table of Contents Next

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.

Trace and Alert Files

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

Database Modes

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

NOARCHIVELOG Mode

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.

Archive Log Mode

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.

Changing the Mode of the Database

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:

  1. Shut down and restart the instance in STARTUP MOUNT mode.
  2. Run the ALTER DATABASE ARCHIVELOG statement to start archiving.

Page 449

  1. Ensure that the ARCHIVELOG_START INIT.ORA parameter is set to TRUE to enable automatic archiving and that ARCHIVELOG_DEST is set to the destination where you want the offline redo log files copied.
  2. Shut down and restart the instance normally. Check that the offline redo log files are being produced by looking at which files are produced in the archive destination or by checking the alert file.

Useful Data Dictionary Views

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.
Previous Table of Contents Next


Oracle Unleashed
Oracle Development Unleashed (3rd Edition)
ISBN: 0672315750
EAN: 2147483647
Year: 1997
Pages: 391

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net