Doing Incomplete Recovery and Moving Redo Logs From Corrupted Disk

Doing Incomplete Recovery and Moving Redo Logs From Corrupted Disk

**Checked for relevance on 20-July-2015***

*****************************************************************************
How to recover the database to a certain point in time (Incomplete Recovery), 
and at the same time move the Redo log files away from a corrupted drive. 
*****************************************************************************

If for some reason the disk where the redo log files crashes, and you do not 
have a backup of the redo log files (i.e. hot backup strategy does not require
redo logs to be backed up), then you need to point oracle to a new location
in which to put the new redo logs.

The process outlined below will work if the current redo log files are
lost because the file system/ drive went down with hardware problems.
In addition, there are no copies of the redo log files available.

SCOPE & APPLICATION

Trying to start a database that crashed, resulted in the following errors:

 ORA-00333: redo log read error block  count  
     Cause:  An error occurred while reading the redo log file.
             Other messages will accompany this message and will give the 
             name of the file.
    Action: Restore access to the file or get another copy of the file.

 ORA-27072: skgfdisp: I/O error additional error 
     Cause: read/write/readv/writev system call returned error, additional 
            information indicates starting block number of I/O 
    Action: check errno

 ORA-00312: online log  thread :  
     Cause:  This message reports the filename for details of another message.
    Action: Other messages will accompany this message.
            See the associated messages for the appropriate action to take.

Pre-recovery Steps:
===================

1. Open database in mount mode:

 SQL> Startup mount pfile=d:\orant\database\init[sid].ora

2. Type "Archive Log List" or "Select * from v$log" to determine the 
   Active/Current Log. Most likely, the changes contained in this log will not 
   be recovered.

3. Check the alert log for the time when the current log was opened.

For example:

Fri Nov 05 10:55:18 1999
Thread 1 advanced to log sequence 129
  Current log# 3 seq# 129 mem# 0: D:\ORANT\DATABASE\LOGITOR\LOGITOR3A.LOG
  Current log# 3 seq# 129 mem# 1: D:\ORANT\DATABASE\LOGITOR\LOGITOR3B.LOG

Any modifications or additions to the database at or beyond this time will 
not be recovered.

Conditions for incomplete recovery:

- Must be in archive log mode
- Must have recent cold or hot backups
- Necessary archive log files must exist to roll forward

Recovery Procedure:

1.) Make sure that the database is shut down.

2.) Restore from last cold or hot backup all datafiles and controlfile(s).

3.) Open SQL*Plus

4.) Open the database in mount mode:

 SQL> Startup mount pfile=d:\orant\database\init[sid].ora

5.) Recover using:

 SQL> Recover database using backup controlfile until cancel

6.) When prompted to apply existing archive logs, press [return]

ORA-00279: Change 11532 generated at 11/05/99 10:47:59 needed for thread 1
ORA-00289: Suggestion: d:\orant\database\archive\arch127.arc
ORA-00280: Change 11532 for thread 1 is in sequence #127
 Specify log: ((RET)=suggested | filename | AUTO | CANCEL)

7.) When prompted to apply archive log that does not exist (current 
    redo log), type "CANCEL" (this is current/active redo log file when 
    database crashed).

ORA-00279: Change 11548 generated at 11/05/99 10:55:17 needed for thread 1
ORA-00289: Suggestion: d:\orant\database\archive\arch129.arc
ORA-00280: Change 11532 for thread 1 is in sequence #129
ORA-00278: Logfile 'd:\orant\database\archive\arch128.arc' no longer
needed for this recovery
 Specify log: ((RET)=suggested | filename | AUTO | CANCEL)

8.) Copy the control file by typing:
 SQL> Alter database backup controlfile to trace

9.) Issue "Shutdown Immediate".
     SQL> shutdown immediate

10.) Edit/rename the trace file generated.

 - Delete all lines before and including the line "Startup Nomount"
 - Change "Noresetlogs" with "Resetlogs"
 - Change the location of all redo logs away from corrupted disk
 - Delete every line after the "Datafile" entry

Result Sample file (recotest.sql):

CREATE CONTROLFILE REUSE DATABASE "ITOR" RESETLOGS ARCHIVELOG
    MAXLOGFILES 32
    MAXLOGMEMBERS 4
    MAXDATAFILES 50
    MAXINSTANCES 16
    MAXLOGHISTORY 1600
LOGFILE
  GROUP 1 (
    'D:\ORANT\DATABASE\LOGITOR\newlogdir\LOGITOR1A.LOG',
    'D:\ORANT\DATABASE\LOGITOR\newlog\LOGITOR1B.LOG'
  ) SIZE 500K,
  GROUP 2 (
    'D:\ORANT\DATABASE\LOGITOR\newlog\LOGITOR2A.LOG',
    'D:\ORANT\DATABASE\LOGITOR\newlog\LOGITOR2B.LOG'
  ) SIZE 500K,
  GROUP 3 (
    'D:\ORANT\DATABASE\LOGITOR\newlog\LOGITOR3A.LOG',
    'D:\ORANT\DATABASE\LOGITOR\newlog\LOGITOR3B.LOG'
  ) SIZE 512K
DATAFILE
  'D:\ORANT\DATABASE\LOGITOR\SYSTEMITOR.DBF',
  'D:\ORANT\DATABASE\LOGITOR\DATA_1.TBL',
  'D:\ORANT\DATABASE\LOGITOR\RBS_ITOR.DBF'
;

11.) Start SQL*Plus

12.) Start database in nomount mode:

 SQL> Startup nomount pfile=d:\orant\database\init[sid].ora

13.) Run controlfile script:

 SQL>  @recotest.sql

14.) Open the database with redo logs in new location:

 SQL> Alter database open resetlogs;

15.) Shutdown the database and perform a full backup.

=========================================================