Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
![]() |
![]() |
Home -> Community -> Usenet -> c.d.o.server -> Re: HotBackup Dilema
"Chuckster" <chuckycarson_at_networkcloud.com> wrote in message
news:3DFFBCA7.7020003_at_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:
>
> 1) Archive current online redo logs
> SVRMGRL> alter system archive log current
No need to do this. It causes a log switch, and thus a checkpoint. But if you didn't do it, there would be some current redo which is not yet archived and thus not yet backed up. But who cares? Tomorrow, you'll be doing a new backup... and the first archive to be backed up then will be the same log you're currently forcing to be created prematurely.
Avoid checkpoints unless absolutely critical to do so. This command causes one.
This command is also in the wrong place. See below.
> 2) I then copy the archive logs to a backup destination
Fair enough.
> 3) Connect to oracle and ontain list of tablespaces
> 4) Place each tablespace in backup mode, one at a time, and copy to
> backup destination, and then remove from backup mode
I'm glad you said 'one at a time'!
> 5) Back up control file (which I will be changeing to trace)
Well, do both. Occasionally, juniior DBAs do daft things like 'drop tablespace BLAH including contents'. Which means that your current controlfile is no longer describing the database that you want. Replacing it with a binary version is just the ticket for a restore. So do both the binary and the trace version backups. Just teach yourself to use the trace file backup 99% of the time, and have the binary version handy just in case.
> 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:
>
> 1) Recreate the oracle installation and directory structure as it was
before
Yup.
> 2) Add the init<sid>.ora file into the correct location
Perfect.
> 3) restore datafiles to correct locations (which will be the same path's
> as before)
Doesn't need to be, but it's good that you can do this, because it makes the rest of the process very easy, with minimal editing and faffing around.
Don't forget that you need to restore the archivelogs as well, unless they can be seen from the new machine as easily as they can be from the original.
> Now the next step is to restore the control files, correct? This is
> where I need the help.
Locate trace file. Edit it the way I described. You need the RESETLOGS version (incidentally, the subtle hint I gave about not seeing a version mentioned anywhere was a cue for you to supply one). Startup nomount, run trace script. Controlfiles are recreated, recovery performed, online redo logs re-created. Database opened.
> One other question. If I restored this hotbackup, would I lose any
> transactions that occured after step 1, where I copied the archive logs?
I was going to mention that. It is usual to backup the datafiles first, and then backup the archives. Whilst you are busy backing up the datafiles, people are (presumbly) doing new transactions on the database. Given enough time, it is distinctly possible that they could get archived. If you've already backed up the archives, then that backup won't contain the necessary redo for those transactions.
Now, ordinarily, I wouldn't sweat about it, because you'll back up *those* archives tomorrow night. But for peace of mind, and to minimise the chances of data loss, it would be as well to backup the datafiles first and then do the archives. That way, your backed up archives contain all bar the current log's transactions as they were at the end of the datafile backup.
As to a specific answer to your question, then the answer is: you can only ever lose the transactions in the current online redo log. And only then if you're daft enough to not have multiplexed them across multiple hard disks (and then mirrored them using hardware mirroring (RAID 1) techniques).
It's that possible loss that causes people to do the 'alter system archive log current' command. Usually, again, at the end of backing up the datafiles (otherwise there's not much point).
Personally, I'm always prepared to lose the current log, and size them accordingly (taking into account the desire not to cause too much checkpointing by switching online logs at an incredible rate of knots). Soon enough, the current log won't be current and then I can catch it the next time I backup the archives.
Regards
HJR
> 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:26:35 CST
![]() |
![]() |