sql | SQL‎ > ‎sql | Downloads‎ > ‎sql | Notes‎ > ‎

note.ora | Oracle log files : An introduction

 
Redo Log    minimize the loss of data in the Database in case of an uncontrolled shutdown.
LGWR        Log Writer.
 
 
view log files:
=================================================================
SELECT * FROM v$log;
 
 
view log file history:
=================================================================
SELECT
     thread#, first_change#
    ,TO_CHAR(first_time
    ,'MM-DD-YY HH12:MIPM')
    ,next_change#
FROM
    v$log_history;
 
 
view log mode:
=================================================================
SELECT log_mode FROM v$database
 
ARCHIVELOG mode:
cannot reuse or overwrite an active online log file until ARCn has archived its contents.
a filled online redo log file is available to LGWR once the changes recorded in it have been written to the datafiles and once the file has been archived.
 
NOARCHIVELOG mode:
last online redo log file fills writing continues by overwriting the first available active file.
a filled online redo log file is available once the changes recorded in it have been written to the datafiles.
 
 
Parameters limit the number of online redo Oracle log files that you can add to a database:
1. MAXLOGFILES
    Maximum number of groups of online redo Oracle log files for each database from 1 to MAXLOGFILES.
 
2. MAXLOGMEMBERS
   
 
Online redo log files required for instance recovery are called active online redo log files.
Online redo log files not required for instance recovery are called inactive.
 
 
 
Operations on Oracle log files :
=================================================================

Forcing log file switches:
    ALTER SYSTEM switch logfile;
        or
    ALTER SYSTEM checkpoint;
Clear A Log File If It Has Become Corrupt:
    ALTER DATABASE CLEAR LOGFILE GROUP group_number; 
        This statement overcomes two situations where dropping redo logs is not possible:
        If there are only two log groups and if the corrupt redo log file belongs to the current group:
    ALTER DATABASE CLEAR LOGFILE GROUP 4;
Clear A Log File If It Has Become Corrupt And Avoid Archiving:
    ALTER DATABASE CLEAR UNARCHIVED LOGFILE GROUP group_number;

Use this version of clearing a log file if the corrupt log file has not been archived:
    ALTER DATABASE CLEAR UNARCHIVED LOGFILE GROUP 3;
Privileges Related To Managing Log Files:
    ALTER DATABASE
    ALTER SYSTEM
Init File Parameters Related To Log Files:
    log_checkpoint_timeout
        set to 0
Managing Log File Members:
    ALTER DATABASE
    ADD LOGFILE MEMBER 'log_member_path_and_name' TO GROUP group_number;
Adding log file group members:
    ALTER DATABASE
    ADD LOGFILE MEMBER '/oracle/dbs/log2b.rdo' TO GROUP 2;
Droping log file group members:
    ALTER DATABASE
    DROP LOGFILE MEMBER log_member_path_and_name';
    ALTER DATABASE
    DROP LOGFILE MEMBER '/oracle/dbs/log3c.rdo';
To create a new group of online redo log files,
    use the SQL statement ALTER DATABASE with the ADD LOGFILE clause: 
    The following statement adds a new group of redo Oracle log files to the database:
    ALTER DATABASE ADD LOGFILE ('/oracle/dbs/log1c.rdo', '/oracle/dbs/log2c.rdo') SIZE 500K;
 
 
 
 
|||||||||||||||||||||||||||||||||||||||||haven't read||||||||||||||||||||||||||||||||||||||||||
 

The optimum configuration has the fewest groups possible without hampering LGWR's writing redo log information.

In some cases, a database instance may require only two groups. In other situations, a database instance may require additional groups to guarantee that a recycled group is always available to LGWR.

During testing, the easiest way to determine if the current online redo log configuration is satisfactory is to examine the contents of the LGWR trace file and the database's alert log.

If messages indicate that LGWR frequently has to wait for a group because a checkpoint has not completed or a group has not been archived, add groups.

LGWR writes to online redo log files in a circular fashion. When the current online redo log file fills, LGWR begins writing to the next available online redo log file.

When the last available online redo log file is filled, LGWR returns to the first online redo log file and writes to it, starting the cycle again. The numbers next to each line indicate the sequence in which LGWR writes to each online redo log file.

 
 
Comments