Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Archive logs? help?

Re: Archive logs? help?

From: Tanel Poder <tanel_at_@peldik.com>
Date: Sun, 9 Mar 2003 22:51:55 +0200
Message-ID: <3e6ba8b8$1_2@news.estpak.ee>


Hi!

The summary in this form isn't complete and optimal, I'd say.

First the first archive log current just introduces unnecessary log switch, thus checkpoint or incremental checkpoint starting from 8i. No advantages are gained, the amount of redo to backup or to recover remains the same despite of the logswitch/archival.
Also, to reduce amount of logging during hot backup, one should put one datafile at time into backup mode, for copying it over, instead of the whole tablespace.

More, Oracle only tells you which logfile it needs for recovery, if the relevant information is retained in controlfile (and seen from v$log_history). That means, if your control_file_record_keep time is too short, maxloghistory is too small, or controlfile has been created from trace file, you will only be prompted about the starting SCN needed, because there is no information for correlating SCNs with LSNs (log sequence numbers).

Also, if you are recovering because a corruption, not loss of media, it might be reasonable to rename or back up the corrupt file itself, before owerwriting it with an older version. Also, in 9i you actually can do block level recovery too (oracle term is block media recovery), this means you don't have to recover the whole datafile, you even don't have to take it offline to perform recovery of a block or blocks. I'm really glad of this option (if anyone cares ;)

Cheers,
Tanel.

"Paul Brewer" <paul_at_paul.brewers.org.uk> wrote in message news:3e6b9747_1_at_mk-nntp-1.news.uk.worldonline.com...
>
> "Norman Dunbar" <Norman.Dunbar_at_lfs.co.uk> wrote in message
> news:E2F6A70FE45242488C865C3BC1245DA70364DA5D_at_lnewton.leeds.lfs.co.uk...
> > Hi James,
> >
> > In answer to your question, you only need the backup of the file(s) that
> > are 'broken' and the appropraiet pile of archived logs since the start
> > time of the hot backup you are recovering from. There is no need to
> > recover ALL the datafiles and recover the database if there is only one
> > datafile that is broken. Saves much time and effort and gets users back
> > on-line quicker - they like that !
> >
> >
> > when doing a hot backup you start (well, I do) by 'alter system archive
> > log current' to get a fresh flush of the current logfile, which forces a
> > new one to be used. You'll need to know the name, rank & serial number
> > of this new one as it needs to stay with the hot backup files.
> >
> > Than slap each tablespace into backup mode ONE AT A TIME, copy it, take
> > it out of backup mode. Repeat until finished. DON'T put all tablespaces
> > into backup mode, copy them all etc - it just causes masses of archive
> > logs to be generated ! (Read Only tablespaces should be dumped at the
> > point they became read only, then they never need dumping again - unless
> > you make them read-write again !)
> >
> > Do another' archive log current' at the end of the hot backup.
> >
> > Once you have taken the dump, all the hot backup files and all the
> > archived logs from the start of the backup should be kept. The first one
> > of course is the one you forced to be used with the initial 'archive log
> > current' command. From this point onwards, until the next backup, you
> > will need the datafile copies and the archived logs, all of them.
> >
> > When a restore is required, simply restore the datafile(s) that are
> > actually broken, and either recover database or recover tablespace or
> > even recover datafile 'blah' at which point, Oracle will tell you which
> > log file it needs to begin the recovery.
> >
> > (God help me if I've made any errors in the above - I'll get a severe
> > and well deserved telling off from Howard !)
> >
> > Basically, to do a recovery from hot backup dump files taken at time X,
> > you need all the archived log files since the START of that hot backup
> > to be available.
> >
> > If you have a 'spare' database lying around, take a hot backup, add some
> > data to a tables or three, shut it down and delete a/the/some datafiles
> > using the OS commands applicable, then do a recovery from your hot
> > backup. Practice makes perfect (sometimes) and it is best to have tried
> > it out on a totally unimportant database before you have to do it for
> > real. Trust me, I know - junior dba, first day on job, senior dba left
> > on the Friday previous with instructions for me to 'delete file xxx from
> > the source database as the tablespace has been dropped' so I did. You
> > can imagine the panic that then ensued as I work in a software house and
> > that database is where the source code lives !!! I did, get a recovery
> > with no loss of data in the end, but it took me a long time (until 02:00
> > AM - I'd never heard of this NewsGroup then - wish I had !!)
> >
> > Cheers,
> > Norman.
> >
> >
> >
> > -----Original Message-----
> > From: James [mailto:james_at_nothing.com]
> > Posted At: Friday, March 07, 2003 2:41 PM
> > Posted To: server
> > Conversation: Archive logs? help?
> > Subject: Re: Archive logs? help?
> >
> >
> > Thanks
> > And just to confirm if I take a hot backup of the datafiles and the
> > babkup
> > files at the time and directly after the backup(that was 100%
> > successful) I
> > only have to restore the archive logs that I have just backed up and the
> > datafiles. Is this true?
> >
> > cheers
> > James
> >
> >
> >
>
> IMHO this is an excellent summary, Norman.
> Jonathan; any chance of adding this as an answer in the FAQ section?
>
> Regards,
> Paul
>
>
>
Received on Sun Mar 09 2003 - 14:51:55 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US