Search

Join this Blog for direct reference of any post into your inbox. To join just click on "Join this Site" under "Follower" then Login with your Email.*** DECLARATION: I Maintain this Blog for Helping Myself While at Work and Welcome any body Needing Help!!!.*** CAUTION: Using any of the script from this Blog may contain at Own Risk. These scripts May or May not have been Tested.***

Sunday, 26 August 2012

How to recover and open the database if the archive log required for recovery is missing.

While working with Sadhan, I came across a situation while restoring and recovering the one of our sister’s company database first time with the old backup, our restore process goes well and we were able to recreate the controlfile during recovery process, it is asking archive logs by giving error “archive log required for recovery is missing”. I tried all the possible location to find another valid copy of archivelog such as directories defined in the LOG_ARCHIVE_DEST, another directory in the same server or another server, RMAN backup, OS backup but I did not find required archivelog backup. When I consult with our team member, I came to know they are deleting the archivelog manually not having required archivelog. Apart from that I also tried other possible solution to open the database (discuss below) but fail to do. Finally with the help of some expert I tried hidden parameter “_ALLOW_RESETLOGS_CORRUPTION=TRUE” which will force to open database even though it is not properly recovered but unfortunately instance crashed after opening the database, I found our undo tablespace get corrupted.
Caution: Please do not using any hidden parameters without consulting oracle support as it is not recommended by oracle. When I read the Metal ink it clearly states there is no 100% guarantee that setting “_ALLOW_RESETLOGS_CORRUPTION=TRUE” will open the database because it allows to open database without consisting checks. This may results sometimes in corrupted database.
After digging more to resolve undo corruption I changes undo management to “Manual” in “initial parameter file” and able to open the database successfully.
Then after created new undo tablespace and dropped corrupted one. Finally changed back undo management to “AUTO’ and undo Tablespace to Newly created Undo tablespace. Thanks to support by expert we are able to resolve this issue and database is running fine.
Caution: Please do not try this concept on production system. Try on test environment makes sure you have valid backup before performing.
Steps:
  1. Set “_ALLOW_RESETLOGS_CORRUPTION=TRUE” in pfile.
  2. Startup Mount
  3. Recover database
  4. Alter database open Resetlogs.
  5. Set Undo_Management to “MANUAL” in pfile.
  6. Startup database
  7. Create new UNDO tablespace
  8. Change back Undo_Management to “AUTO” and Undo_Tablespace to Newly Create UNDO Tablespace
  9. Restart the instance;
Note: Do not forget to the full database backup again after successfully performing the above operation

Possible Solution: archivelog required for recovery is either missing, lost or corrupted or not found any other alternative backup location
If the archivelog is not found even in any of the alternative backup location then check the SCN (System Change Number) of the datafiles as well as whether the log sequence# required for the recovery is still available in the online redologs. It is one of the approach or strategy to recover and open the database in case of missing archivelog (rare chances of presence of required log sequence but you can try). For the SCN of the datafiles, it is important to know the mode of the database when the datafiles are backed up. That is whether the database is open, mounted or shutdown (normally) when the backup is taken.
If the datafiles are restored from an online or hot backup, which means that the database is open when the backup is taken, then we must apply at least the archivelog(s) or redolog(s) whose log sequence# are generated from the beginning and until the completion of the said backup that was used to restore the datafiles.
And if the datafiles are restored from an offline or cold backup, and the database is cleanly shutdown before the backup is taken, that means that the database is either not open, is in nomount mode or mounted when the backup is taken, then the datafiles are already synchronized in terms of their SCN. In this case we can open the database without even applying archivelogs, because the datafiles are already in a consistent state (except if there is a requirement to roll the database forward to a point-in-time after the said backup is taken).
The main thing here is to ensure that all of the online datafiles are synchronized in terms of their SCN before we can normally open the database.
select status, checkpoint_change#, to_char(checkpoint_time, 'DD-MON-YYYY HH24:MI:SS') as checkpoint_time, count(*)
from v$datafile_header
group by status, checkpoint_change#, checkpoint_time
order by status, checkpoint_change#, checkpoint_time;
the above query will determine whether the datafiles are synchronized or not.
Note: Querying the V$DATAFILE_HEADER to know the SCN recorded in the header of the physical datafile and not the V$DATAFILE (which derives the information from the controlfile).
STATUS  CHECKPOINT_CHANGE# CHECKPOINT_TIME        COUNT(*)
------- ------------------ -------------------- ----------
ONLINE             2778991 26-AUG-2012 05:00:51          4
ONLINE             2778993 26-AUG-2012 05:00:55          4
ONLINE             2778996 26-AUG-2012 05:01:01          3
The results of the above query must return one and only one row for the online datafiles which means that they are already synchronized in terms of their SCN. Otherwise, if the results return more than one row for the online datafiles, then the datafiles are still not synchronized yet. In this case, we need to apply archivelog(s) or redolog(s) to synchronize all of the online datafiles.
Note: The CHECKPOINT_TIME in the V$DATAFILE_HEADER, which indicates the date and time how far the datafiles have been recovered.
The results of the query above may return some offline datafiles. So, please ensure that all of the required datafiles are online because we may not be able to recover later the offline datafile once we open the database in resetlogs ( In 10g onward we can recover the database beyond resetlogs due to the introduction of the format “%R” in the LOG_ARCHIVE_FORMAT).
select file#, name from v$datafile
where file# in (select file# from v$datafile_header
where status='OFFLINE');
If we are still having required log sequence# in the online redologs and the corresponding redolog member is still physically existing on disk, then we may apply them directly instead of the archivelog. To confirm, query:
select LF.member, L.group#, L.thread#, L.sequence#, L.status,
L.first_change#, L.first_time, DF.min_checkpoint_change#
from v$log L, v$logfile LF,
(select min(checkpoint_change#) min_checkpoint_change#
from v$datafile_header
where status='ONLINE') DF
where LF.group# = L.group#
and L.first_change# >= DF.min_checkpoint_change#;
Apply the redolog member returns from above query during recovery. Otherwise, if the above query returns no rows then try to apply each of the redolog members one by one during the recovery.
select * from v$logfile;;
If you have tried to apply all of the online redolog members instead of an archivelog during the recovery, but you always received the ORA-00310 error, as shown in the example below, then the log sequence# required for recovery is no longer available in the online redolog.
ORA-00279: change 187169545 generated at 26/08/2012 08:27:16 needed for thread 1
ORA-00289: suggestion : +ORABACK
ORA-00280: change 187169545 for thread 1 is in sequence #124
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
+ORABACK/ISSCO/group_3.458.503422112
ORA-00310: archived log contains sequence 185; sequence 124 required
ORA-00334: archived log: '+ORABACK/ISSCO/group_3.458.503422112'
After trying all of the possible solutions but you still cannot open the database due to missing archivelog or the corresponding log sequence# is no longer available in the online redolog, they are already overwritten during the redolog switches, then we cannot normally open the database, as the datafiles are in an inconsistent state then use the below option as a final step to open the database.
Option1: Restore the database from the available backup, and recover the database by applying up to the last available archivelog. In this case you will only recover the database up to the last archivelog that is applied and any data after that are lost. If no archivelogs are applied at all then you are able to recover at a restored backup point of time. However, if we restored from an online or hot backup, then we may not be able to open the database, because we still need to apply the archivelogs generated during the available backup in order to synchronize the SCN of the datafiles before we can normally open the database.
Option2: Force open the database by setting above mentioned hidden parameters in the init.ora parameter. Note that you can only do this under the guidance of Oracle Support with a service request. But there is no 100% guarantee that this will open the database.

1 comments:

  1. BlueHost is the best web-hosting company with plans for all of your hosting requirements.

    ReplyDelete