Re: HotBackup Dilema

"Chucky" <chuck.carson_at_syrrx.com> wrote in message news:3DFFABAB.9000909_at_syrrx.com...
> When I try that I get this error:
>
> SVRMGR> alter database recover until cancel
> 2> ;
> alter database recover until cancel
> *
> ORA-00283: recovery session canceled due to errors
> ORA-01610: recovery using the BACKUP CONTROLFILE option must be done
> SVRMGR>
Never, ever, ever, ever issue an 'alter database' version of a recovery command. Ever. It's functionally no different to say 'alter database recover database' than it is to say 'recover database', but the first command causes all interaction between the system and you, the user, to be suppressed. Which means you're flying blind. And when you interrupt the recovery because you think nothing is happening, you make your database totally and utterly irrecoverable. It's a disaster. So don't use that form of the syntax, OK?

>
> The control being used was backup using 'alter database backup control
> file to /some/path'
>
> Is this correct?
>

Mmmmmm. Sort of. It produces a binary copy of the control file, which means that it is immediately out of date. Not that you can't use such a backup, but it inevitably means that when you do, you are required to recover the database using a resetlogs command, and that means long shutdown times, the trashing of all prior archives, all prior redos, and the requirement to take a new backup before your database is protected again.

Better to do an 'alter database backup controlfile to trace', which generates a text file which contains all the SQL commands needed to reconstruct a control file from scratch, without the need for a resetlogs.

> What I am doing is taking a hotbackup and putting on an entirely
> different server and testing a restore.
>

Well, in that case, you've gone about it wrong. I bet you restored the controlfile from the backup, too. In that case, I'd kiss goodbye to this particular restore attempt. Shut the lot down, blow everything away and start again.

And before you do anything else, do an alter database backup controlfile to trace, and learn how to hunt it down and knock it into a useable script.

When you start fresh, restore the backed up datafiles. Only the datafiles. You then copy across the controlfile trace script. Edit that so that all the redo log paths and data file paths are correct for the new machine. Stick a 'startup nomount' command at the beginning of the script. This command might need a "pfile=/xxx/yyyy/init.ora" entry if the init.ora is not appropriately named or housed in the correct location (which by default is ORACLE_HOME/dbs or ORACLEHOME\database, depending on whether you're Unix or Windows). Stick an appropriate connect string above *that* (such as 'connect / as sysdba').

Then, sqlplus /nolog
SQL> @name_of_trace_file_here

That will cause the instance to be built. A server process will then create a controlfile with the pointers to the datafiles etc set correctly. The database will then be mounted. Any recovery required will then be performed automatically. And then the database will be opened.

I didn't see a reference to your Oracle version anywhere, so bear in mind that if all you've restored are the datafiles (which is as it should be) then you won't have any online redo logs. That means you MUST open the database with a resetlogs (since the other thing this command does is to reconstruct missing logs). In 9i, the backup to trace command produces a trace file containing a set of commands to use WITHOUT a resetlogs and WITH a resetlogs. You want the second version, WITH the resetlogs. In earlier versions, only the NOresetlogs version was produced, so you have to manually edit the script in two places: one, on the first line, there's a command 'create controlfile blah blah blah NORESETLOGS' and two, near the end, there's the command 'alter database open'. In the first case, change NORESETLOGS to RESETLOGS. And in the second, add the command RESETLOGS so the line reads 'alter database open resetlogs'.

And then you might hunt down my paper on Backup and Recovery to get some background on all this stuff so you know what you're doing.

And since Norman is on holiday, I'll mention briefly that it's still apparently available at www.geocities.com/lydian_third. Under the 'Books' link.

I'll also make clear that that's not my site, and I have nothing to do with that material still being available. (It's a long, long story).

Regards
HJR
> Thx,
> CC
>
> Howard J. Rogers wrote:
> > "Startup" on its own means 'startup open', so the command is attempting
to
> > completely open the database.
> >
> > It would appear, however, that a previous incomplete recovery has been
> > performed, and this is still awaiting the 'alter database open
resetlogs'SVRMGR>
>
> > command to terminate it.
> >
> > Incomplete recoveries are performed either (a) after a user stuff-up
(such
> > as 'Ooops. I just deleted the entire sales table and hit the commit
button
> > instead of the rollback one') or (b) when there's a gap in your stream
of
> > redo which prevents a complete recovery succeeding.
> >
> > In the case of (a), you beat the unfortunate employee around the head a
bit,
> > until he confesses that he did the catastrophic delete at, say, 8.45am.
You
> > then restore all datafiles from your last complete backup, startup
mount,
> > and issue the command 'recover database until time
'2002-17-12:08:44:00'.
> > That rolls the datafiles forward to time 8:44am. But that still leaves
the
> > Control File at time 8:45, ahead of the datafiles. You therefore force
> > synchronisation by issuing the command 'alter database open resetlogs',
> > effectively setting the database back to time zero.
> >
> > In the case of (b), you issue the command 'recover database until
cancel',
> > meaning: 'when I type in the word cancel, I want you to permanently stop
> > applying redo to my datafiles'. So you restore from last night, roll
them
> > forward by applying redo from archives (say) 105, 106, 107, 108 and 109.
But
> > because archive 110 is missing, you can't go further, so you say
'cancel'.
> > Now your datafiles are at time 109, but again your controlfile is ahead
of
> > the game at time 115. Time to force synchronisation to time zero with an
> > 'alter database open resetlogs'.
> >
> > So someone (presumably you?) has previously done one or other of these
> > scenarios, the datafiles are consistent amongst themselves at one time,
but
> > the controlfile is out of synch at a time in the future of that... so a
> > resetlogs is needed.
> >
> > Bear in mind that a resetlogs resets the database to time zero. Prior
> > backups at time (say) 100 are thus useless for recovering that database.
All
> > prior archives are likewise equally useless. Your freshly opened
database is
> > thus completely vulnerable to another failure of some kind, and would be
> > unrecoverable unless you *immediately* start to take a new backup.
> >
> > Regards
> > HJR
> >
> >
> > "Chucky" <chuck.carson_at_syrrx.com> wrote in message
> > news:3DFF8A4F.2000302_at_syrrx.com...
> >
> >>I am trying to restore from a hotbackup and am getting this problem:
> >>
> >>SVRMGR> connect internal;
> >>Connected.
> >>SVRMGR> startup
> >>ORACLE instance started.
> >>Total System Global Area 1979523232 bytes
> >>Fixed Size 73888 bytes
> >>Variable Size 368558080 bytes
> >>Database Buffers 1610612736 bytes
> >>Redo Buffers 278528 bytes
> >>Database mounted.
> >>ORA-01589: must use RESETLOGS or NORESETLOGS option for database open
> >>
> >>
> >>This is new to me, can anyone shed some light?
> >>
> >>Thx,
> >>CC
> >>
> >>
> >>
> >>-----------== Posted via Newsfeed.Com - Uncensored Usenet News
> >
> > ==----------
> >
> >> http://www.newsfeed.com The #1 Newsgroup Service in the World!
> >>-----= Over 100,000 Newsgroups - Unlimited Fast Downloads - 19 Servers
> >
> > =-----
> >
> >
>
>
>
> -----------== Posted via Newsfeed.Com - Uncensored Usenet News
==----------
> http://www.newsfeed.com The #1 Newsgroup Service in the World!
> -----= Over 100,000 Newsgroups - Unlimited Fast Downloads - 19 Servers
=----- Received on Tue Dec 17 2002 - 17:30:29 CST