Lesson 11	Looking at redo log information
Objective	Find information about the redo logs in your database.

Looking at Oracle redo log information

In Oracle Database Management Systems, redo logs are critical components for maintaining data integrity and enabling database recovery. Obtaining accurate and timely information about redo logs is essential for effective database management. Oracle provides a rich set of data dictionary views, dynamic performance views, and utility commands to gather this information.

Data Dictionary Views

`DBA_LOG_GROUPS`

This view contains information about redo log groups in the database.

SELECT * FROM DBA_LOG_GROUPS;

`DBA_LOG_FILES`

This view provides information about redo log files, including their location, status, and size.

SELECT * FROM DBA_LOG_FILES;

Dynamic Performance Views

`V$LOG`

This view provides real-time information about the redo log groups. You can glean details about the log sequence numbers, their current status (ACTIVE, INACTIVE, CURRENT), and whether they have been archived.

SELECT * FROM V$LOG;

`V$LOGFILE`

This view furnishes information about the redo log files and their statuses. It provides the full path of each redo log file, allowing you to ascertain their physical locations.

SELECT * FROM V$LOGFILE;

`V$LOG_HISTORY`

This view provides historical information about redo logs, including log sequence numbers and the time they were archived.

SELECT * FROM V$LOG_HISTORY;

Command-Line Utilities

SQL*Plus: The SQL*Plus utility provides an interface to run SQL queries to obtain redo log information directly.
Oracle Enterprise Manager (OEM): The graphical interface of OEM provides a more user-friendly approach to view redo log statuses and configurations.
Oracle Alert Log: The database's alert log captures significant events, including redo log switches and archival processes. The alert log is typically located in the `alert` subdirectory of the Oracle Diagnostic Destination directory (`diag`), which is specified by the `DIAGNOSTIC_DEST` parameter.

Best Practices

Scheduled Monitoring: Set up automated monitoring routines to query these views at regular intervals. Trigger alerts based on critical thresholds, such as when the redo log space is close to being exhausted.
Documentation: Keep detailed records of the redo log configurations, including log groups, member files, and their corresponding sizes and locations.
Security: Ensure that only authorized personnel have the required privileges to query system views related to redo logs.

By leveraging these data dictionary views, dynamic performance views, and command-line utilities, you can acquire comprehensive insights into the state, configuration, and performance of redo logs within your Oracle database environment. This information is instrumental for operational monitoring, performance tuning, troubleshooting, and disaster recovery planning.

Listing redo log groups

Two system views, V$LOG and V$LOGFILE, give you information about the redo log files for your database. The following example shows how you can query the V$LOG view to list the redo log groups in your database:

SQL>SELECT group#, members, bytes, status

     2> FROM v$log;

GROUP#     MEMBERS    BYTES      STATUS

---------- ---------- ---------- ----------------

         1          2     204800 CURRENT

         2          2     204800 INACTIVE

         3          2     204800 INACTIVE

3 rows selected.

In this example, there are three redo log groups. The redo log file sizes are 200K, or 204,800 bytes, and there are two members in each group. The status of group #1 is CURRENT, which means that Oracle is currently writing to the log files in that group.

Finding the file names of the redo log files

You can use the V$LOGFILE view to find out the actual filenames of the log files. The following example shows how:

Oracle Database Administration

SQL> SELECT group#, member
2> FROM v$logfile
3> ORDER BY group#;
GROUP#     MEMBER

---------- --------------------------------------
1 C:\ORANT\DATABASE\LOG2ORCL.ORA
1 D:\ORANT\DATABASE\LOG2ORCL.ORA
2 C:\ORANT\DATABASE\LOG1ORCL.ORA
2 D:\ORANT\DATABASE\LOG1ORCL.ORA
3 C:\ORANT\DATABASE\LOG3ORCL.ORA
3 D:\ORANT\DATABASE\LOG3ORCL.ORA

6 rows selected.

There are six groups, with two members each, for a total of six redo log files. Notice that members of each group are on separate disks in order to provide redundancy, and protect the data in the event that one disk fails. Later in this course, when you start to create your own database, you will learn how to create these files, and specify which disks to use. Next, you will learn about archive log files.

Redo Log Info - Quiz

Click the Quiz link below to see how well you have learned the material so far.
Redo Log Info - Quiz