Listen Software Solutions' "How To" Series: 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
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
Alter Database Add Logfile Member
'e:\log\log4a.rdo' TO GROUP 3
The following statement inserts a
log member into an existing Log Group.
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
* determine the current log group
select * from v$log
* alter database clear unarchived
logfile group #nbr
* 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;
