RE: Recovery scenario

From: Mark W. Farnham <mwf_at_rsiz.com>
Date: Thu, 31 Jan 2008 14:19:45 -0500
Message-ID: <018601c8643e$3e3304a0$1100a8c0@rsiz.com>


whoops - too long - I snipped nearly the entire original message, which is on the list thread several times.  


From: Mark W. Farnham [mailto:mwf_at_rsiz.com] Sent: Thursday, January 31, 2008 1:20 PM To: 'veeeraman_at_gmail.com'; 'Oracle List' Subject: RE: Recovery scenario  

You're using a backup controlfile, so Oracle doesn't know when to stop. Of course this is natural since production is still running. You've gone past the point of when you took the backup by applying additional redo logs (the first place you could have stopped would have been after the last end backup was logged, since this was a physical [non-RMAN] backup).  

The first file checked on the open says "but I'm not complete against a comparison to NULL" (since you're using a backup controlfile, which is all you can have since production is still running).  

So you have to invoke an incomplete recovery. Now if you started from the backup set AND invoked some variety of incomplete recovery AND only rolled at least past the point of the last end backup, it would not need the 5 AM stuff. So you've done a more complete recovery than you absolutely had to do, as you voluntarily kept rolling forward.  

Now if this were on production and you lost all images of a database file and wanted to do a complete recovery, you would have a current controlfile. Then you'd leave out the using backup controlfile bit and consume all the redolog you had available and open normally (without resetlogs).  

I'm a little unclear where it gets the granularity to the second of the NEXT change needed, I had thought the uncertainty from the redo log threads alone was three seconds. I'd have to do some digging I'm not going to do any time soon to figure out if that message means at that exact second or at that second or later by the maximum flush interval of the redo logs.  

Basically, unless you flip the bit protecting you from resetting your online redologs by using a recovery command that implies incomplete, they are not going to let you stop until the comparison is equal. But by using a backup controlfile it is never going to be equal. You can consume all the changes committed, in which case it will be logically a complete recovery, but you're still going to have to reset the online logs unless you start with a controlfile that can be used as a "current" controlfile.  

If you want to test that, you would reload the backup files you took, shut down production, copy the cold controlfile to the test machine as well as the production online redo logs and any intervening archived redo logs and simply recovery database. Last time I fully tested that was probably V6 or V7 and they have introduced some asynchronicity in redo log writing that might have ramifications that don't occur to me, so if you actually test, let me know how you make out. While RMAN is often the best choice, it is still good to completely understand physical recovery.  

(Oh, and as with some others on this thread I pled senility if I've botched something here - chime in if you think I've had a brain cramp or if something changed that I didn't make note of.)  

Regards,  

mwf  


From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Ram Raman
Sent: Wednesday, January 30, 2008 6:25 PM To: Oracle List
Subject: Recovery scenario  

All,  

We have an Oracle 10g prod database that is being backed up every night just past midnight using user managed backup method. I am trying to restore the files to a test server and create a new instance in the test server. The full backup started at 1240am at log sequence 11592 ended at 1:23am at sequence 11593. (See attachment of alert.log BELOW)  

I recreated the the control file with new names for database, datafiles, redo logs and arch logs, etc on the test server and mounted the database. While trying to open this is what happened:  

16:49:56 SQL> select instance_name, status 16:50:02 2 from v$instance;
more..

INSTANCE_NAME STATUS

---------------- ------------
HRDEV            MOUNTED

16:50:06 SQL> recover database using backup controlfile; ORA-00279: change 11805525061 generated at 01/30/2008 00:40:01 needed for thread 1
ORA-00289: suggestion : /thrundo/oradata/HRDEV/arch/1_11592_620315068.dbf ORA-00280: change 11805525061 for thread 1 is in sequence #11592

16:50:26 Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

ORA-00279: change 11805558903 generated at 01/30/2008 01:23:32 needed for thread 1

ORA-00289: suggestion : /thrundo/oradata/HRDEV/arch/1_11593_620315068.dbf
ORA-00280: change 11805558903 for thread 1 is in sequence #11593
ORA-00278: log file '/thrundo/oradata/HRDEV/arch/1_11592_620315068.dbf' no
longer
needed for this recovery

16:50:41 Specify log: {<RET>=suggested | filename | AUTO | CANCEL} cancel
Media recovery cancelled.
16:50:52 SQL> alter database open resetlogs; alter database open resetlogs
*
ERROR at line 1:
ORA-01113: file 1 needs media recovery
ORA-01110: data file 1: '/thrsys/oradata/HRDEV/system01.dbf'

<snip>

--
http://www.freelists.org/webpage/oracle-l
Received on Thu Jan 31 2008 - 13:19:45 CST

Original text of this message