All About Logfiles
Immediately perform a complete consistent cold backup.
- Creates a new incarnation of the database, putting a new SCN in the headers of all database files
- Resets the log sequence number to 1
- 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
) 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
The previous statement removes the logfile member
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)
select * from v$log ->determine the current log sequence number
recover until cancel
- enter cancel when the archive number equals the log sequence number
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;
For Listen Software