RE: Recovery scenario

  • From: "Mark W. Farnham" <mwf@xxxxxxxx>
  • To: "'Mark W. Farnham'" <mwf@xxxxxxxx>, <veeeraman@xxxxxxxxx>, "'Oracle List'" <oracle-l@xxxxxxxxxxxxx>
  • Date: Thu, 31 Jan 2008 14:19:45 -0500

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



From: Mark W. Farnham [mailto:mwf@xxxxxxxx] 
Sent: Thursday, January 31, 2008 1:20 PM
To: 'veeeraman@xxxxxxxxx'; '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.)







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




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;

---------------- ------------
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
needed for this recovery

16:50:41 Specify log: {<RET>=suggested | filename | AUTO | 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'


Other related posts: