Redo Log Data Dictionary Views

Introduction 
============ 
 
Redo logs are essential to the recovery of your database.  They record all the  
changes that are made to the database.  They also ensure the integrity of your  
database. Archiving redo logs will help ensure a successful backup.  
  
ORACLE has provided numerous views in version 7 that allow you to monitor your  
redo logs.   
  
The following views are discussed:  
  
        v$log  
        v$log_history  
        v$logfile  
        v$loghist  
        v$database  
 
 
Oracle7 Redo Logs 
================= 
  
Redo logs are key components in the recovery of your database.  Redo logs  
contain both the redo and undo changes needed to make your database   
consistent.  When an user commits a transaction, ORACLE guarantees that  
the changes to the blocks are written to the redo logs.  Furthermore, undo  
information needed to roll back uncommitted transactions are also stored  
in the redo logs/archive files.  Thereby, during instance recovery,  
oracle will roll forward applying the redo and then roll back applying the  
undo.  The result is a consistent version of the database.  
  
In oracle 7, there are numerous views that allow the user to familiarize  
themselves with redo logs.  Here are a listing of these views as well as   
some helpful hints in interpreting the information found in these views.  
  
  
v$log:  
log file information from control file.  
 Name           Null?    Type          DESCRIPTION  
 -------------- -------- ----           
-----------------------------------------  
 GROUP#                  NUMBER        LOG GROUP NUMBER  
 THREAD#                 NUMBER        LOG THREAD NUMBER  
 SEQUENCE#               NUMBER        LOG SEQUENCE NUMBER  
 BYTES                   NUMBER        SIZE OF THE LOG IN BYTES  
 MEMBERS                 NUMBER        NUMBER OF MEMBERS IN LOG GROUP  
 ARCHIVED                VARCHAR2(3)   ARCHIVE STATUS: T/F  
 STATUS                  VARCHAR2(8)   STATUS OF THE REDO LOGS  
 FIRST_CHANGE#           NUMBER        LOWEST SCN IN THE LOG  
 FIRST_TIME              VARCHAR2(20)  TIME OF THE FIRST SCN IN THE LOG  
  
There must be at least two groups with one member each in every database.   
We recommend to have at least two members in each group on separate disk   
drives; thereby, you will be preventing the case where there is just one single  
point of redo failure.  Each instance will have an associated thread and the   
combination of the thread# and the sequence# distinguishes a log file and an   
archive file.  Each member within a group should have the same size redo logs   
but we recommend that all groups have identical sizes.  SCN (SYSTEM COMMIT   
NUMBER) is the key internal value that determines at what state or "time" the  
database is at.  Every time you do a select, you basically grab   
a consistent view of the database, a SCN.  Thereby, a SCN is like a logical  
clock.  When you recover, you are recovering to a specific time of the   
database.  
  
      ARCHIVED:  
      YES         The content of the file has been archived.  For a newly added  
                  redo log file, the field is YES, regardless the archive mode.  
      NO          otherwise.   
  
	  
      STATUS:  
      UNUSED      indicates the online redo log has not been written to.  
                  This is the state of a redo log that was just added or just  
                  after a RESETLOGS when it is not the current redo log.  
      CURRENT     indicates this is the current redo log.  This implies that  
                  the redo log is active.  The redo log could be open or  
                  closed.  
      ACTIVE      indicate the log is active but is not the current log.  It  
                  is needed for crash recovery.  It may be in use for block  
                  recovery.  It might or might not be archived.  
      INACTIVE    indicate the log is no longer needed for crash recovery.  It  
                  may be in use for media recover. It might or might not be  
                  archived.  
  
v$log_history:  
archived log names for all logs in the log history.  
 Name             Null?    Type          DESCRIPTION  
 ---------------- -------- ----          ----------------------------------  
 THREAD#                    NUMBER        THREAD NUMBER OF ARCHIVED LOG  
 SEQUENCE#                 NUMBER        SEQUENCE NUMBER  
 TIME                      VARCHAR2(20)  TIME OF THE LOWEST SCN IN THE LOG  
 LOW_CHANGE#               NUMBER        LOWEST SCN  
 HIGH_CHANGE#              NUMBER        HIGHEST SCN  
 ARCHIVE_NAME              VARCHAR2(257) ARCHIVE FILE NAME  
  
  
v$logfile:  
information about all redo logs.  
 Name             Null?    Type           DESCRIPTION  
 --------------   -----    ----           --------------------------------  
 GROUP#                    NUMBER         REDO LOG GROUP IDENTIFIER NUMBER  
 STATUS                    VARCHAR2(7)    STATUS OF THIS LOG MEMBER  
 MEMBER                    VARCHAR2(257)  REDO LOG MEMBER NAME   
  
 	STATUS:  
	INVALID -FILE IS INACCESSIBLE  
	STALE   -FILE CONTENTS ARE INCOMPLETE  
	DELETE 	-FILE IS NO LONGER USED  
	BLANK	-FILE IS IN USE.  
  
v$loghist:  
log history from the control file.  
 Name             Null?    Type          DESCRIPTION  
 -------------    -------- ----          ----------------------  
 THREAD#                   NUMBER     	 LOG THREAD NUMBER  
 SEQUENCE#                 NUMBER        LOG SEQUENCE NUMBER  
 FIRST_CHANGE#             NUMBER        LOWEST SCN IN THE LOG  
 FIRST_TIME                VARCHAR2(20)  TIME OF THE FIRST SCN IN THE LOG  
 SWITCH_CHANGE#            NUMBER        SCN AT WHICH THE LOG SWITCH OCCURRED;  
					 ONE MORE THAN THE HIGHEST SCN IN THE  
					 LOG.  
  
v$database:  
database information from the control file.  
 Name             Null?    Type          DESCRIPTION  
 ---------------- -------- ----          --------------------------  
 NAME                      VARCHAR2(9)   NAME OF THE DATABASE  
 CREATED                   VARCHAR2(20)  CREATION DATE  
 LOG_MODE                  VARCHAR2(12)  ARCHIVE LOG MODE:   
					 ARCHIVELOG/NOARCHIVELOG  
 CHECKPOINT_CHANGE#        NUMBER        LAST SCN CHECKPOINTED  
 ARCHIVE_CHANGE#           NUMBER        LAST SCN ARCHIVED.  
  
  
In sqldba, you can get archival information by typing   
ARCHIVE LOG LIST.  Information about your redo logs are also shown.  
  
Here is an example:  
  
SQLDBA> archive log list  
Database log mode              NOARCHIVELOG  
Automatic archival             DISABLED  
Archive destination            /u04/oracle/6037p/dbs/arch.dbf  
Oldest online log sequence     248  
Current log sequence           249  
  
  
  
Database log mode  
  
 	Indicates whether or not your database is in archive mode or not.  
	  
	Initially, it is set at database creation time using the CREATE  
	DATABASE statement.  Then you can change it while your database is in  
	the mounted exclusive stage with the command ALTER DATABASE 	 
	ARCHIVELOG/NOARCHIVELOG  
  
  
Automatic archival	  
  
	Indicates if you are automatically archiving (ENABLED) or if you are  
	manually archiving (DISABLED).   
  
	This is set in your init.ora with the log_archive_start parameter or  
	you can manually adjust this with the command LOG ARCHIVE START/STOP. 

.