Re: HotBackup Dilema

From: Chuckster <chuckycarson_at_networkcloud.com>
Date: Tue, 17 Dec 2002 16:09:11 -0800
Message-ID: <3DFFBCA7.7020003@networkcloud.com>

Hey thanks for the help, I am actually a Unix admin turned DBA, so luckily this is all done is a test environment.

I just wanna make sure my hot backup script is doing what it needs to do. Here is a rough outline of what it does:

Archive current online redo logs SVRMGRL> alter system archive log current
I then copy the archive logs to a backup destination
Connect to oracle and ontain list of tablespaces
Place each tablespace in backup mode, one at a time, and copy to backup destination, and then remove from backup mode
Back up control file (which I will be changeing to trace) SVRMGRL> alter database backup controlfile to /my/patch; I will change this to SVRMGRL> alter database backup controlfile to trace;

Now, suppose I had to completely restore from this hotbackup. I understand this much:

Recreate the oracle installation and directory structure as it was before
Add the init<sid>.ora file into the correct location
restore datafiles to correct locations (which will be the same path's as before)

Now the next step is to restore the control files, correct? This is where I need the help.

One other question. If I restored this hotbackup, would I lose any transactions that occured after step 1, where I copied the archive logs?

Thanks a bunch,
CC

Howard J. Rogers wrote:
> "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

>
> =-----
>
>

-----------== 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 - 18:09:11 CST