Page 80
Each log group can consist of multiple members. Each member of a log group is an exact mirror of the others, and redo log entries are written to each member in parallel. If LGWR cannot write to a member of the group, it does not fail. Rather, it writes an entry into the alert.log file. By using multiple members per group, you can safeguard against database failure resulting from lost redo logs. As long as one member of the group is accessible, the database will continue to function.
NOTE |
The same functionality can be obtained by mirroring your redo log groups using RAID 1 (Mirrored) or RAID 5 volumes. This alleviates the overhead caused when LGWR has to update multiple log group members for each database transaction. |
As shown in Figure 6.2, the redo logs are written to in a circular fashion. LGWR writes to one group until it is full. It then moves on to the next group in the sequence. Meanwhile, ARCH will copy the just-filled online redo log to the operation system as an archived redo log. This process will continue until all of the logs have been filled, and then LGWR will begin writing back at the first online redo log. At no point will LGWR write to an online redo log that ARCH is still copying. If this happens, all database activity will seem to halt until ARCH has finished its copy. This LGWR-ARCH contention can be avoided by having online redo log groups of ample size and number available in the database.
Page 81
The V$LOG and V$LOGFILE views hold information on the online redo log files. The following query checks the status of the current logs:
SELECT member, bytes, members, a.status FROM v$log a, v$logfile b WHERE a.group# = b.group# ORDER BY member; SELECT b.member, b.bytes, b.members, a.status FROM v$log a, v$logfile b WHERE a.group# = b.group# ORDER BY b.member;
The Trace File(s)All Oracle databases have at least one file where system messages, errors, and major events are logged. This file, named sidALRT.log (where sid is the system identifier for the database), is stored at the location specified by the init.ora parameter BACKGROUND_DUMP_DEST. It is the first place you should look when investigating database problems. Critical failures are always logged here, as well as database startup and shutdown messages, log switch messages, and other events.
Background and user processes also create their own trace files where problems and failures are logged. Background process trace files are stored in the BACKGROUND_DUMP_DEST location, while user trace files are stored in the directory pointed to by the USER_DUMP_DEST parameter setting. Setting the USER_DUMP_DEST directory differently than that pointed to by BACKGROUND_DUMP_DEST will allow you to keep track of the different classes of trace files. Background process trace files are named sidPROC.trc, where sid is the system identifier for the database and PROC is the name of the background process (DBWR, LGWR, SMON, PMON, and so on).
User session trace files are given an ora prefix, followed by a sequence of unique numbers, with a .trc file extension. User session trace files are generated when a user session causes an unrecoverable problem (such as a deadlock or a crashed server process) or when the user session is explicitly told to, such as when SQL tracing is enabled or an ALTER DATABASE BACKUP CONTROLFILE TO TRACE command is issued. To enable SQL tracing, issue ALTER SESSION
Page 82
SET SQL_TRACE=TRUE from the SQL*Plus prompt, or set the SQL_TRACE init.ora parameter to true. Be cautious, however, because setting SQL_TRACE to true in init.ora will cause all SQL statements that occur against the database to be written to trace files; this will generate an enormous amount of trace information.
The current settings for the BACKGROUND_DUMP_DEST and USER_DUMP_DEST parameters can be queried from the V$PARAMETER view.
The ROWIDFor the Oracle database to retrieve information, it must be able to uniquely identify each row in the database. The internal structure the Oracle RDBMS uses for this task is called the ROWID, a two-byte value that stores the physical location for a row in the database. The format of the ROWID is as follows:
BBBBBBBB.RRRR.FFFF where BBBBBBBB is the block number (in hex) where the row resides in the datafile, RRRR is the row number (in hex) in the block where the data row exists, and FFFF is the file where the block exists.
For example, a row in a table might have a OWID as follows:
0000068C.0000.0001
This ROWID is in the first datafile (0001), the 68C (hex) block in that datafile, and the first row in that block (0000).
NOTE |
You can match the file number from the preceding ROWID to the filename by querying the DBA_DATA_FILES view. In Oracle7, these file numbers are fixed, while in Oracle8 they are determined at database startup. |
A row is assigned a ROWID when it is first created. The ROWID remains the same until the row is deleted or the segment the row is in is reorganized (through import/export, third party reorganization tools, and so on). Using the ROWID is the fastest method of finding a row in the database. Every table in the database contains a pseudocolumn named ROWID, which can be queried to show the ROWID of each row in the table.
Because of the unique nature of the ROWID, it can be used to creatively solve many different problems. For example, one of the most common usages of the ROWID is to identify columns with duplicate values in the database. The following SQL script shows how this can be done:
delete from duplicate_table where ROWID not in (select MIN (ROWID) from duplicate_table group by a1, a2);
You can also use the value of the actual ROWIDs in queries. The following SQL script will display the number of database files a table has rows in:
SELECT COUNT(DISTINCT(SUBSTR(ROWID, 15, 14)))) "Files" FROM test_table;