Listen Software's How To: Logfiles

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

select 
a.group# 
,a.members 
,a.status 
,a.sequence# 
,a.bytes
,b.status
from v$log a, v$logfile b
where
a.group#=b.group#

Switching a Logfile

Alter System switch logfile

Adding Online Redo Log Groups

Alter Database add logfile
('e:\log\log3a.rdo',
 'f:\log\log3b.rdo'
) SIZE 1M

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
'e:\log\log3a.rdo'

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

ALTER SYSTEM ARCHIVE LOG CURRENT;

Archiving all the Non-current Redo Logs

ALTER SYSTEM ARCHIVE LOG ALL;

David Nishimoto
For Listen Software