Oracle Database can be operates in two modes.
1. Archive log mode (The archive logs are generated and online backup is possible)
2. No archive log mode (No archive log generated and cold backup is only possible)
The online redolog file is the physical file which is updated with redo information (changed information in the database) from redolog buffer by LGWR background process. The online redo log files can be of different states.
CURRENT: - The online redolog file to which LGWR is currently writing the redo information.
ACTIVE: - The online redolog file to which the LGWR is about to write the redo information once completed with the current online redolog file.
INACTIVE: - The online redolog file to which LGWR has finished its writing.
UNUSED: - The online redolog file which is not used by LGWR to write the redo information.
Now we are going to discuss how to recover the database when online redolog file is corrupted due to some reason. We would have various scenarios, I have divided the scenarios in terms of archive log mode and no archivelog mode. I would try to cover most of the scenario.
a) Online redolog file in INACTIVE and UNUSED state:-
This would not require crash recovery. Clear the logfile group and recreate the group manually by deleting the log file.
SQL> ALTER DATABASE CLEAR LOGFILE GROUP
SQL>ALTER DATABASE DROP LOGFILE GROUP
Delete the redolog file from the physical location.
SQL>ALTER DATABASE ADD LOGFILE GROUP
('/oracle/dbs/log1c.rdo', '/oracle/dbs/log2c.rdo') SIZE 10M;
b) Online redolog file in ACTIVE state:-
This would require crash recovery. Issue a checkpoint.
SQL>ALTER SYSTEM CHECKPOINT;
If the check point failed, then please do a complete recovery of the database from the last full backup. The steps to perform complete recovery are explained in the complete recovery section.
c) Online redolog file in CURRENT state:-
The LGWR will be terminated in this case and you have to perform complete recovery from cold backup.
v Archivelog mode:-
a) Online redolog file in UNUSED state:-
The steps would be same as that of no archivelog mode.
b) Online redolog file in INACTIVE state:-
Check your redolog file is archive or not.
SQL>Select group#, archived from v$log;
Yes:-follow the same step as noarchive log mode.
No:- Issue the below command.
SQL>ALTER DATABASE CLEAR UNARCHIVED LOGFILE GROUP
After this command there would be a gap in the archive log. So do complete recovery from backup.
c) Online redolog file in ACTIVE state:-
Perform a check point and if it fails then perform a complete recovery.
SQL>ALTER SYSTEM CHECKPOINT;
d) Online redolog file in CURRENT state:-
The LGWR will be terminated in this case. We would have to perform incomplete recovery.
Steps for complete recovery:-
1. Using RMAN
2. Using multiplex copy of redolog file
a) Using RMAN:-
Connect to the target database and perform a recovery.
D:\>rman target sys/
RMAN> RESTORE DATABASE;
RMAN>ALTER DATABASE OPEN;
b) Using Backup of redolog file:-
Apply the multiplex copy of redolog file and start the database.
SQL> alter database open;
Steps for Incomplete recovery:-
1. First find out the first change for the redolog file. This would be the SCN number.
SQL>select first_change, group# from v$log where group#=
2. Connect to RMAN and perform the incomplete recovery until SCN number -1;
C:\>rman target sys
RMAN> RESTORE DATABASE UNTIL SCN
RMAN> restore database until scn 123455;
RMAN> recover database until scn 123455;
RMAN> alter database open resetlogs;