RE: Current Redo got deleted

From: Mark W. Farnham <mwf_at_rsiz.com>
Date: Tue, 24 Mar 2015 11:08:14 -0400
Message-ID: <2b0001d06644$5ab75d00$10261700$_at_rsiz.com>



The aforementioned “religious war” describes all this and more in various threads on this list from antiquity.  

The short counter to your referenced problem is that backing up the online redo logs (and the controlfile) is the first step of RECOVERY. In addition to facilitating RECOVERY retries, that provides a source for streams mining.  

I remain unconvinced that backing up online redo as part of routine backup solves any other problem than the one I described below. That is NOT a trivial problem; search the archives if you like for Jared Still’s eloquent defense of the practice.  

The analog to a religious war is I believe accurate, because no matter how eloquent the arguments for and against it comes down to a belief about which devil is to be feared more. Neither deleting the on line redo Nor restoring old copies from backup over the top of current versions can be done without Sin.  

The argument is moot if you utilize something like Delphix. Then you could, for example, create a clone comprising a trivial number of blocks of real physical storage and without touching the real physical original image at all except for reading and practice your attempts at RECOVERY until reliable success is achieved. (and no, I don’t work for Delphix. I do have friends there who I respect both personally and professionally.)  

mwf  

From: Hameed, Amir [mailto:Amir.Hameed_at_xerox.com] Sent: Tuesday, March 24, 2015 10:17 AM
To: mwf_at_rsiz.com; veeeraman_at_gmail.com; kaygopal_at_gmail.com Cc: dba.tyagisumit_at_gmail.com; 'Oracle L' Subject: RE: Current Redo got deleted  

If you do not backup your online redo log files and if you are running something like Oracle Streams then even though you can do a full database recovery but you will need to open the database with the RESETLOGS option. When that happens, your Oracle Streams will break and its recovery will become very tricky. So, depending on what you are running, you may or may not need to back up your redo log files.  

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Mark W. Farnham Sent: Tuesday, March 24, 2015 10:06 AM
To: veeeraman_at_gmail.com; kaygopal_at_gmail.com Cc: dba.tyagisumit_at_gmail.com; 'Oracle L' Subject: RE: Current Redo got deleted  

Okay, so the first step of RECOVERY is to back up the online redo logs and the controlfile. (There is a religious war about whether online redo should be in routine backup sets centering on risk having some complete archive even if someone does something stupid, like delete the online redo logs, versus the risk of stupidly restoring old online redo logs over the top of the current online redo logs [which might be the only place the latest commits survive]).  

Backing up the online redo logs and the controlfile as part of RECOVERY, however, is the only way I know of that allows you to try again from the current state if during the RECOVERY attempt you encounter something unexpected (which includes anything, but common examples are additional hardware glitches and software bugs) or a problem exists between the keyboard and the chair (PEBKAC).  

Since you’ve already lost your online redo logs this bit of the RECOVERY is reduced to your controlfile.  

I’d make two for anything important to two isolated stores, preferably at least one of which is physically removable and can be put beyond the reach of whoever started this circus.  

The next bit is a physical backup of all the datafiles. It is a good idea to have a list somewhere that is not down when your database or server is down. Just like your long term performance repository should not use production cycles, your information to facilitate RECOVERY should not be in a single location nor dependent on a single point of failure.  

Old school we would use triplex raid images (translation: when folks write “mirror” they usually mean duplex, and each “image” is plex. Triplex means you have three plexes defined and you usually have a least duplex active all the time but have the capability to detach a plex or reassociate a plex to the overall volume image (aka as “resilvering” in the mirror metaphor).  

New school you use something like EMC timefinder, and even newer school something like Delphix.  

Doing this quickly revolves around non-RMAN physical backup. (which may be built into some packaged solutions.)  

Then I’d try the step Tim Gorman mentioned. You may in fact find that everything in the last used online redo log was archived before the retries to switch through all the remaining missing logs resulted in a hang or crash. Since lgwr had your last current online redo log open, any reasonable operating system would prevent the data going “poof!” due to a deletion until it is closed.  

IF you have an expert on your storage media handy, it may still be possible to locate the file’s extent map at the operating system level and use OS level utilities to copy the correct physical blocks to construct a new image. Before they play games, read the bit about preserving the existing state above.  

OH and look in your recycle bin (or the analog to a recycle bin). Your lost online redo might be there just waiting to be retrieved. Just be careful about the recycle of the one that was currently being written – the possible options depending on OS implementation beggar the imagination.  

Good luck.      

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Ram Raman Sent: Monday, March 23, 2015 11:36 PM
To: kaygopal_at_gmail.com
Cc: dba.tyagisumit_at_gmail.com; Oracle L Subject: Re: Current Redo got deleted  

Wonder how the step #2 "MAKE A COMPLETE BACKUP of CURRENT STATE OF THE DATABASE" will work in the case of a big database where backing up the current state might take a long time. Especially when the users are screaming in the ears to get things up ASAP.  

On Sun, Mar 22, 2015 at 4:48 PM, K Gopalakrishnan <kaygopal_at_gmail.com> wrote:

Sumit--  

1..OPEN AN SR -- SEV-1 with Oracle Support

2....MAKE A COMPLETE BACKUP of CURRENT STATE OF THE DATABASE  

  1. Try to mimic an incomplete recovery -- Recover database using backup control file
  2. You can either use '_allow_resetlogs_corruption=true' and try opening the db with resetlogs
  3. alter database open reset logs;
  4. Rebuild the database --probably with datapump/ export
  5. Duplex the redologs / run the db in archive log mode.

Sent from my iPhone  

On Sun, Mar 22, 2015 at 12:31 PM, sumit Tyagi <dba.tyagisumit_at_gmail.com> wrote:

Hi Guys ,  

How to deal with this " Current redo got deleted & DB crashed " . What are the option we have as a DBA  

--

BR

Sumit Tyagi    

--
 

You can become a doctor and then websearch for solutions; You cannot websearch and become a doctor

--

http://www.freelists.org/webpage/oracle-l Received on Tue Mar 24 2015 - 16:08:14 CET

Original text of this message