Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum Rss Feed

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
Free Newsletters:

News Via RSS Feed

Rss Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted Feb 25, 2002

Listen Software's How To: Logfiles

By David Nishimoto

All About Logfiles

Resetlogs option

Immediately perform a complete consistent cold backup.

  1. Creates a new incarnation of the database, putting a new SCN in the headers of all database files
  2. Resets the log sequence number to 1
  3. Reformats the online redo log files if they exists.

Unintentionally Restoring Online Redo Logs

When restoring a database, it is possible to accidentally restore the online redo logs. This forces an incomplete recovery instead of a complete recovery.

Status and Location

from v$log a, v$logfile b

Switching a Logfile

Alter System switch logfile

Adding Online Redo Log Groups

Alter Database add logfile

The Oracle Server automatic numbers the group

Log File should fill up every 30 to 90 minutes. Therefore, you should adjust the size accordingly.

Adding a Logfile Member

The following statement inserts a log member into an existing Log Group.

Alter Database Add Logfile Member
'e:\log\log4a.rdo' TO GROUP 3

Dropping Online Redo Log Groups

Alter Database Drop logfile Group 3;

The previous statement drops the redo logfile Group identified as Group 3.

Dropping Online Redo Log Members

Alter Database drop logfile member

The previous statement removes the logfile member 'e:\log\log3a.rdo' from GROUP 3.

Clearing a Corrupted Logfile

Alter Database Clear Logfile

Note: The current redo log of an open thread can never be cleared.

The current log of a close thread can be cleared by switching logs in the closed thread.

Loss of Current Redo Logs

If database is open but in a Hung State

  1. determine the current log group
    select * from v$log
  2. alter database clear unarchived logfile group #nbr
  3. perform a full database backup

If the database is closed (Incomplete recovery)

  1. select * from v$log ->determine the current log sequence number
  2. recover until cancel
  3. enter cancel when the archive number equals the log sequence number
  4. alter database open resetlogs;

Archiving Unarchived Redo Log Files

If you do not have all archived redo logs produced during backup, you cannot recover the backup because you do not have all the redo records necessary to make it consistent. Archive the current log file ensuring the redo necessary for recovery


Archiving all the Non-current Redo Logs


David Nishimoto
For Listen Software

Oracle Archives

Latest Forum Threads
Oracle Forum
Topic By Replies Updated
Oracle Data Mining: Classification jan.hasller 0 July 5th, 07:19 AM
Find duplicates - Unique IDs Lava 5 July 2nd, 08:30 AM
no matching unique or primary key rcanter 1 April 25th, 12:32 PM
Update values of one table based on condition of values in other table using Trigger Gladiator 3 February 29th, 06:01 PM