Re: Control File Backup

There's strictly speaking no need for the 'noresetlogs' keyword.

And yes, it would be a good idea for different instances to have different dump file destinations -although, as you've found out by now, in fact the Instance Name and process id are included in the trace file names, so there is practically no likelihood of things being over-written.

If you issue the command ...

alter database backup controlfile to 'trace'

...then what should actually be produced is a binary copy of the control file, and the copy filename would be "trace". Perhaps the error arose because the command didn't include a path into which to write such a file. I would imagine (but haven't bothered to test) that Oracle would then attempt to write the binary file into the directory where you launched server manager from... and if the right to write is not set on that directory, naturally the command would fail.

Regards
HJR "VA Access" <vaaccess_at_my-deja.com> wrote in message news:95sjir$b3j$1_at_nnrp1.deja.com...
> I think I figured out the problem. I did some more reading and found
> the following command listed in the manual that I have:
> ALTER DATABASE BACKUP CONTROLFILE TO TRACE NORESETLOGS
>
> I had mistakenly put the word "trace" within apostrophes, which
> obviously caused the problem...
>
>
> In article <3a81a22c_at_news.iprimus.com.au>,
> "Howard J. Rogers" <howardjr_at_www.com> wrote:
> >
> > "VA Access" <vaaccess_at_my-deja.com> wrote in message
> > news:95rpp3$h4n$1_at_nnrp1.deja.com...
> > > Last night the control file didn't backup correctly. Shock, eh? At
> > > what point during the backup do I execute the alter database backup
> > > controlfile 'trace' command?
> >
> > Either before or after starting the backups of your datafiles. It
 makes no
> > difference which. You could even do it in the middle of the process,
 but
> > why add further complications to your life??!
> >
> > >It didn't do anything, that I can tell.
> > > I looked in the %Oracle%/trace directory, which is where the dump
 dest
> > > is, but nothing was there.
> >
> > Which dump dest? This is a User Process that has requested the write
 of a
> > trace file, and hence it willbe in whatever you've got USER_DUMP_DEST
> > pointed at.
> >
> > >I ran it after the tablespaces were put
> > > into backup mode. Should I do that trace before the backup runs or
> > > after? Should it have worked even if the system was in backup mode?
> > > I'm running V. 7.3.4, BTW, and in that case, does it change the way
> > > I'm doing this???
> > >
> >
> > Not that I'm aware of, but I will check this arvo.
> >
> > Regards
> > HJR
> >
> > > Thanks,
> > > Mike
> > >
> > > In article <3a80596a_at_news.iprimus.com.au>,
> > > "Howard J. Rogers" <howardjr_at_www.com> wrote:
> > > > Lots of comments.... (sorry!)
> > > >
> > > > "VA Access" <vaaccess_at_my-deja.com> wrote in message
> > > > news:95pia1$kbt$1_at_nnrp1.deja.com...
> > > > > I guess I should have stated that the system is in backup mode
 at
 the
> > > > > time of the command execution that I listed below. All of the
> > > > > tablespaces are put into backup mode by executing the following
 command
> > > > > for each tablespace:
> > > > >
> > > > > alter tablespace SYSTEM begin backup;
> > > > >
> > > > > Then, the ocopy73 commands are executed to copy the datafiles
 and
 the
> > > > > control file is copied as well.
> > > >
> > > > That's your first mistake! I presume you are doing hot backups,
 because you
> > > > want to allow Users to still access data, and perform
 transactions,
 whilst
> > > > the backup is taking place. Be aware that whilst a tablespace is
 in
 hot
> > > > backup mode, every transaction, even of a single field in a single
 row,
> > > > results in the entire *BLOCK* in which that row is found being
 written into
> > > > the Log Buffer and hence into your Redo Logs and archives.
> > > >
> > > > Accordingly, the amount of Redo generated whilst in hot backup
 mode is
> > > > potentially enormous, and is likely to cripple your redo sub-
 system.
> > > >
> > > > For that reason, the very firm recommendation from Oracle is that
 you
 place
> > > > a SINGLE tablespace into hot backup mode, copy the relevant files,
 then take
> > > > it out of hot backup mode -and then move onto the next tablespace.
 Most of
> > > > your database is therefore NOT in hot backup mode at any one
 moment,
 and
> > > > hence the extra redo generation is minimised as far as possible.
 You're
> > > > also supposed to make the time between a 'begin backup' command
 and
 an 'end
> > > > backup' command as short as possible, for the same reason.
> > > >
> > > > >What is happening is that the control
> > > > > file wasn't copying when the AT commands are executed, but would
 copy
> > > > > when I ran the commands by double-clicking on the .cmd file that
> > > > > started the process.
> > > > > I changed the commands so it will copy the control files with a
 dos
> > > > > copy command instead of the Oracle ocopy command. We'll see if
 that
> > > > > works tonight.
> > > > >
> > > >
> > > > OK. But (as you go on to say) so long as you understand that the
 resulting
> > > > file will be useless. But fair enough that you want to just make
 sure the
> > > > thing works.
> > > >
> > > > > However, based on what you're saying the consultant that set all
 this
> > > > > up didn't do it correctly. By using the "alter database backup
> > > > > controlfile" command that you mentioned, how would I get the
 control
> > > > > files and all the datafiles to be in sync?
> > > >
> > > > First off, the chances of you ever having to use the binary
 backup of
 your
> > > > control file are small. You'd only ever be facing that problem if
 you lost
> > > > *all* exisitng copies of your control file -and I'm hoping that
 you
 have
> > > > done the sensible thing and mirrored your Control Files within
 Oracle
 so
> > > > that there are multiple copies. (I always recommend 3-way
 mirroring).
> > > >
> > > > So in normal recovery situations (where your control files are not
 lost, but
> > > > one of your datafiles is, say), it's dead easy: Control File is at
 time
> > > > 21000, 9 data files are at time 21000, 1 datafile is at time 16000
 (restored
> > > > from backup). At startup, SMON reads the timestamp in the
 Controlfile,
> > > > spots one of the data files is out of synch, and prompts for
 recovery
 of
> > > > that datafile. When you then say "recover database" or "recover
 datafile
> > > > X", Redo is applied to the recalcitrant datafile, and it is rolled
 forward
> > > > to time 21000.
> > > >
> > > > If you ever restored a binary copy of your Control File (and one
 that
 was
> > > > useable because it had been taken with the 'alter database backup
> > > > controlfile command), then you'd potentially have this problem: 10
 datafiles
> > > > all beautifully consistent at time 21000, and a Control File at
 time
 16000.
> > > >
> > > > Now, you can't apply redo to Control Files. So how do you force
 the
 Control
> > > > File to synchronise with the data files? Short answer is, you
 tell
 SMON not
> > > > to panic about the timestamp on the Control File, and to check the
 data
> > > > files: "recover database USING BACKUP CONTROLFILE". That still
 will
 leave
> > > > the Control File out of synch with everything else, so now you
 force
 the
> > > > issue: "alter database open RESETLOGS".
> > > >
> > > > The resetlogs commands effectively forces synchronisation back to
 time 1.
> > > >
> > > > In short, any time you use a binary backup of your control file to
 open a
> > > > database, you MUST issue a resetlogs.
> > > >
> > > > Now think about that: if the timestamps on everything have just
 been
 reset
> > > > back to time 1, you've got a problem on your hands. Your old
 backups
 were
> > > > all stamped at 16000. 16000 can not be rolled forward to 1.
 Hence
 all
> > > > prior backups are useless. Guess what? Your archives are all at
 time
> > > > 16000, 17000, 18000 and so on too... hence all prior archives are
 useless.
> > > > See the problem? Anytime you open a database with a resetlogs, it
> > > > effectively has no backups, and no archives. You are in a hugely
 vulnerable
> > > > position, and hence the standard advice is always to immediately
 shutdown
> > > > the database and take a new, cold, base line backup.
> > > >
> > > > Short story is, using binary versions of your Control File is a
 very
> > > > expensive exercise, and for that reason it's usually much better
 to
 take
> > > > trace file backups: "alter database backup controlfile to trace".
 The trace
> > > > script doesn't really back the control file up -it's just a set
 of SQL
> > > > statements which will reconstruct a control file from scratch,
 and it
 will
> > > > do so in a sneaky fashion whereby the timestamps on the datafiles
 are
> > > > written into the header of the controlfile -and hence, the new
 control files
> > > > will be set at time 21000 in our previous example. No expensive
 resetlogs
> > > > option is therefore required, and no loss of effective backups and
 archives
> > > > takes place.
> > > >
> > > > In practice, I'd do both -a binary, consistent backup AND the
 trace
 file
> > > > option (there *is* a use for a binary copy, but it's a bit
 obscure.
 Still,
> > > > if you need that flexibility, you need it bad!).
> > > >
> > > > >Could the "alter database
> > > > > backup controlfile" command be run during the backup of the
 datafiles?
> > > > >
> > > >
> > > > Yes, theoretically. It doesn't matter if you do it before or
 after
 your
> > > > datafiles (or in the middle of it, if you were doing the proper
 technique of
> > > > one tablespace backed up at a time).... as I've explained, when it
 comes
> > > > time to use the thing, its timestamp is irrelevant. You'll be
 forcing the
> > > > issue with a resetlogs option anyway.
> > > >
> > > > > What is the best way to do a backup, then? Right now I could
 take
 the
> > > > > database offline and copy the files and bring it back up
 nightly.
 That
> > > > > wouldn't be a big problem...
> > > > >
> > > >
> > > > Cold backups are a doddle to perform, and if you have that luxury
 of
 being
> > > > able to shutdown and copy *.* to somewhere, I'd thoroughly
 recommend
 it.
> > > > The presence of archives means you have the luxury of choosing
 whether to do
> > > > hot or cold, and cold is always simpler to perform.
> > > >
> > > > But you really ought to get your head around hot backups, since
 even
 if you
> > > > don't use them in your current
> > > > position, there's a good chance they'll be used in your next one!
> > > >
> > > > Check out

 http://www.geocities.com/hjroz2000/backup_and_recovery.htm
> > > > There's a 70-odd page pdf document there I wrote a while back.
 Explains all
> > > > this stuff in some detail, and has apparently been of help to a
 number of
> > > > posters here. I'll happily take this offline with you if you
 contact
 me,
> > > > too.
> > > >
> > > > Regards
> > > > HJR
> > >
> > >
> > > Sent via Deja.com
> > > http://www.deja.com/
> >
> >
>
>
> Sent via Deja.com
> http://www.deja.com/
Received on Thu Feb 08 2001 - 01:42:42 CST