Labels

Saturday, December 11, 2010

Recover database when online redolog file is corrupted.




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.
1 .INACTIVE
2. ACTIVE
3. CURRENT
4. UNUSED

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.
v    No Archivelog mode:-
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.
Commands:-
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.
Command:-
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.
Command:-
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.
Command:-
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.
Commands:-
D:\>rman target sys/@
RMAN> RESTORE DATABASE;
RMAN>RECOVER 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.
Command:-
SQL>select first_change, group# from v$log where group#=;
FIRST_CHANGE#
-------------

123456


2.      Connect to RMAN and perform the incomplete recovery until SCN number -1;
Command:-
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;

3 comments:

  1. Well explained and summarized!

    Slight correction to the STATE descriptions of the online redo logs.

    ACTIVE - This redo log group is needed for instance recovery in case of database crash and also it may or may not be archived. LGWR is not writing to this log group.

    INACTIVE - The redo log group is not needed for instance recovery as it contents have already been archived to an archive log and it is available to be overwritten by LGWR. (Groups are used in a round robin fashion)

    ReplyDelete
  2. Excellent post! I must thank you for this informative read. I hope you will post again soon. Thanks for sharing this post

    Tableau Guru
    http://www.sqiar.com/services/tableau-software-consultants/

    ReplyDelete