Return-Path: Delivered-To: 2-oracle-l@orafaq.com Received: (qmail 5745 invoked from network); 19 Sep 2007 06:15:36 -0500 Received: from freelists-180.iquest.net (HELO turing.freelists.org) (206.53.239.180) by 69.64.49.119 with SMTP; 19 Sep 2007 06:15:36 -0500 Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 3D5CA75D200; Wed, 19 Sep 2007 07:15:37 -0400 (EDT) Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing.freelists.org [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 08449-03; Wed, 19 Sep 2007 07:15:37 -0400 (EDT) Received: from turing (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id A6C9175B50C; Wed, 19 Sep 2007 07:15:36 -0400 (EDT) Received: with ECARTIS (v1.0.0; list oracle-l); Wed, 19 Sep 2007 06:30:06 -0400 (EDT) Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 3D0EA75D2F3 for ; Wed, 19 Sep 2007 06:30:06 -0400 (EDT) Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing.freelists.org [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 01378-04 for ; Wed, 19 Sep 2007 06:30:05 -0400 (EDT) Received: from nf-out-0910.google.com (nf-out-0910.google.com [64.233.182.188]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 3EF4C75D319 for ; Wed, 19 Sep 2007 06:30:04 -0400 (EDT) Received: by nf-out-0910.google.com with SMTP id 4so112062nfv for ; Wed, 19 Sep 2007 03:29:59 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=beta; h=domainkey-signature:received:received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:references; bh=yPVjMRt1sQWw633zMt9yhPEe9kIxhfPnJWdMVejDeko=; b=EV55whcBYsEYCFlVpHDf7FTSTfVUsm8bRA418g66EJu5HLCi6pMhPZoemg50fFMMUlDniayGO0stAW+OlLq57AtSd+AHrB5lIqVzgsM6BXCDQtc9cT9kAlwpcYiEYom3BVKWla3tNs2mcbUKf2gcSjkaqas+tL6MOxLiOFsDNRU= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=beta; h=received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:references; b=Eubl1MjWvv/ifybTiHZ3A1EJi7FSpJ00PSS8FT52FwMvQ2Oi6XG/goLufSzbZkm+GT5FeY4zvEcUwmAZxYmTTzl5G6ZfDjwv+u3svoromVEl6nz4F+EIPS+cl9PR51x4VEcLSjoaT+sZpjzr34dL71JpINlAhE6aDKtL1PO8nVU= Received: by 10.78.123.5 with SMTP id v5mr189196huc.1190197798935; Wed, 19 Sep 2007 03:29:58 -0700 (PDT) Received: by 10.78.181.10 with HTTP; Wed, 19 Sep 2007 03:29:58 -0700 (PDT) Message-ID: <2ba656800709190329t6ade0c92gbeb093681eebd2be@mail.gmail.com> Date: Wed, 19 Sep 2007 06:29:58 -0400 From: "Rajeev Prabhakar" To: mason@blisses.org Subject: Re: Oracle backup questions... Cc: oracle-l@freelists.org In-Reply-To: MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_21097_21008361.1190197798915" References: <20070918190424.GE13547@blisses.org> X-archive-position: 1686 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-to: oracle-l-bounce@freelists.org X-original-sender: rprabha01@gmail.com Precedence: normal Reply-to: rprabha01@gmail.com List-help: List-unsubscribe: List-software: Ecartis version 1.0.0 List-Id: oracle-l X-List-ID: oracle-l List-subscribe: List-owner: List-post: List-archive: X-list: oracle-l X-Virus-Scanned: Debian amavisd-new at localhost.localdomain ------=_Part_21097_21008361.1190197798915 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline Hello Mason It appears that Mark has already answered your questions. I just wanted to mention that to be as close to the latest archive log to be included in the backup set. you should include the command "ALTER SYSTEM ARCHIVE LOG CURRENT" twice (one in the beginning, other at the very end) in you procedure/script. That should take care of question 5 (i.e. archive logs as close as possible to the most current redo log ione). So, here is the sequence (as far as addressing question 5 is concerned) : 1) alter system archive log current ; 2) select sequence# from v$log where status='CURRENT'; 3) run your backup commands ...here.. 4) alter system archive log current ; 5) backup all archive logs from seq# found at step 2 to the (sequence#-1) found at step 4 above. Rajeev On 9/18/07, Bobak, Mark wrote: > > Wow, lots of questions! > > Here we go..... > > 1.) Are you mixing up archive log and on-line redo, here? Online redo is > written continuously, until full. Then Oracle switches to the next redo > log, and the previous one (that's just now full) will be archived. The > archiver process will copy the entire redo log to the archive log area. So, > the "on-disk state" of archivelogs is that the archive logs are complete and > usable, with the possible exception of the last archive log, which may not > have completed writing when the instance crashed. That's ok, cause it's > contents are still in the on-line redo. Oracle guarantees that the on-line > redo will not be overwritten until the redo is completely written to the > archive log area. > > 2.) Yes, there is. When you do 'recover database', Oracle will read the > SCNs the controlfile to "drive" the recovery. It will use the data in the > controlfiles to determine when recovery is complete. In the case of > 'recover database using backup controlfile', you're telling Oracle, this is > a backup controlfile that I've restored, you can't rely on the SCNs it > contains to be accurate. Don't use them to drive the recovery. In that > event, Oracle will use the SCNs in the data file headers, and continue to > roll forward till (at a minimum) they are self-consistent across all > datafiles. You'll need to give Oracle a point in time to recover to, or > cancel the recovery, and open w/ resetlogs. Note that if you have all the > archive logs and on-line redo available, you can still do a complete > recovery, but you'll need to manually apply the online redo once the last > archive log is applied. > > 3.) The crosscheck command, whether run on a backup or an archivelog, > checks the RMAN repository against what's actually available on > disk/tape. Anything that no longer has media available, is updated in the > RMAN repository to reflect that. > > 4.) 'delete expired' will remove from the RMAN repository, stuff that a > previous crosscheck marked as expired cause media is no longer > available. 'delete obsolete', on the other hand, deletes stuff that's not > needed based on the retention policy you've set. That's regardless of > whether the media is still available. > > 5.) Well, you can't control the rate of redo generation, which is a > function of how busy your database is. Archive log generation, then, will > be a function of that rate of redo generation and the size of the archive > log file. What I like to do, is to look at the peak redo generation rate > that my database sees, use that to size the online redo log (which > implicitly sets the max size of an archive log), so that a log switch > happens around every 20-30 minutes. So, if you're generating 1GB of redo > per hour, at peak, then size the on-line redo logs at around 333MB to > 500MB. That should give you, at peak, an archive log switch every 20-30 > mintes. Now, what about non-peak? Well, suppose you have non-peak times > where you'll only generate 100MB per hour. In that case, you'd be waiting > 10 hours between log switches. So, you can set a parameter called > "ARCHIVE_LAG_TARGET", and that will force a which every x minutes. You > could set it to 30 minutes, and it will faithfully do a > log switch every 30 minutes, even if the resulting archive log is only a > few MB in size. > > Hope that helps, > > And I hope I got it all right..... > > -Mark > -- > Mark J. Bobak > Senior Database Administrator, System & Product Technologies > ProQuest > 789 E. Eisenhower, Parkway, P.O. Box 1346 > Ann Arbor MI 48106-1346 > +1.734.997.4059 or +1.800.521.0600 x 4059 > mark.bobak@il.proquest.com > www.proquest.com > www.csa.com > > > -----Original Message----- > From: oracle-l-bounce@freelists.org [mailto:oracle-l-bounce@freelists.org] > On Behalf Of Mason Loring Bliss > Sent: Tuesday, September 18, 2007 3:04 PM > To: oracle-l@freelists.org > Subject: Oracle backup questions... > > I'm working on Oracle back-up and recovery, and I've run across a few > random > questions from looking at what other folks do, and from experimenting on > my > own. > > 1. What is the on-disk state of archive logs at arbitrary times? Does > Oracle > only write them out when they are complete and usable, or does it slowly > fill > them in over time? Recovering the final archive log here seems to > consistently fail, until the next archive log is in place, at which time > the > one that was previously last is imported without issue. > > 2. Is there any meaningful difference between "recover database" and > "recover > database using backup controlfile"? > > 3. What is "crosscheck archivelog all" as opposed to "crosscheck backup"? > > 4. How does "delete obsolete" differ from "delete expired backup"? > > 5. How do we control the frequency of archive log generation? Do I simply > say > "alter system switch logfile" and wait a bit, or does that only affect > redo > logs? Should I "alter system archive log all" instead? In short, what > should > I do to try to bring the archive logs as close to current as is possible? > > Thanks very much for your help! > > -- > Mason Loring Bliss mason@blisses.org Oderint dum metuant! > http://blisses.org/ awake ? sleep : random() & 2 ? dream : sleep; > -- > ------=_Part_21097_21008361.1190197798915 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline
Hello Mason
 
It appears that Mark has already answered your questions. I just wante= d to mention
that to be as close to the latest archive log to be included in the ba= ckup set.
you should include the command "ALTER SYSTEM ARCHIVE LOG CURRENT&= quot;
twice (one in the beginning, other at the very end) in = you procedure/script. That
should take care of question 5 (i.e. archive logs as close as pos= sible to the most
current redo log ione).
 
So, here is the sequence (as far as addressing question 5 is concerned= ) :
 
1) alter system archive log current ;
2) select sequence# from v$log where status=3D'CURRENT';<= /div>
3) run your backup commands ...here..
4) alter system archive log current ;
5) backup all archive logs from seq# found at step 2 to the
   (sequence#-1) found at step 4 above.

Rajeev

 
On 9/18/07, = Bobak, Mark <Mark.Boba= k@il.proquest.com> wrote:
Wow, lots of questions!

H= ere we go.....

1.)  Are you mixing up archive log and on-l= ine redo, here?  Online redo is written continuously, until full.=   Then Oracle switches to the next redo log, and the previous one= (that's just now full) will be archived.  The archiver proce= ss will copy the entire redo log to the archive log area.  So, th= e "on-disk state" of archivelogs is that the archive logs are com= plete and usable, with the possible exception of the last archive log, whic= h may not have completed writing when the instance crashed.  That= 's ok, cause it's contents are still in the on-line redo. &nbs= p;Oracle guarantees that the on-line redo will not be overwritten until the= redo is completely written to the archive log area.

2.)  Yes, there is.  When you do 'recover d= atabase', Oracle will read the SCNs the controlfile to "drive"= ; the recovery.  It will use the data in the controlfiles to dete= rmine when recovery is complete.  In the case of 'recover dat= abase using backup controlfile', you're telling Oracle, this is a b= ackup controlfile that I've restored, you can't rely on the SCNs it= contains to be accurate.  Don't use them to drive the recove= ry.  In that event, Oracle will use the SCNs in the data file hea= ders, and continue to roll forward till (at a minimum) they are self-consis= tent across all datafiles.  You'll need to give Oracle a poin= t in time to recover to, or cancel the recovery, and open w/ resetlogs.&nbs= p; Note that if you have all the archive logs and on-line redo availab= le, you can still do a complete recovery, but you'll need to manually a= pply the online redo once the last archive log is applied.

3.)  The crosscheck command, whether run on a backup or a= n archivelog, checks the RMAN repository against what's actually availa= ble on disk/tape.  Anything that no longer has media available, i= s updated in the RMAN repository to reflect that.

4.)  'delete expired' will remove from the RMAN r= epository, stuff that a previous crosscheck marked as expired cause media i= s no longer available.  'delete obsolete', on the other h= and, deletes stuff that's not needed based on the retention policy you&= #39;ve set.  That's regardless of whether the media is still = available.

5.)  Well, you can't control the rate of redo generat= ion, which is a function of how busy your database is.  Archive l= og generation, then, will be a function of that rate of redo generation and= the size of the archive log file.  What I like to do, is to look= at the peak redo generation rate that my database sees, use that to size t= he online redo log (which implicitly sets the max size of an archive log), = so that a log switch happens around every 20-30 minutes.  So, if = you're generating 1GB of redo per hour, at peak, then size the on-line = redo logs at around 333MB to 500MB.  That should give you, at pea= k, an archive log switch every 20-30 mintes.  Now, what about non= -peak?  Well, suppose you have non-peak times where you'll on= ly generate 100MB per hour.  In that case, you'd be waiting 1= 0 hours between log switches.  So, you can set a parameter called= "ARCHIVE_LAG_TARGET", and that will force a which every x minute= s.  You could set it to 30 minutes, and it will faithfully do a
log switch every 30 minutes, even if the resulting archive log is only = a few MB in size.

Hope that helps,

And I hope I got it all ri= ght.....

-Mark
--
Mark J. Bobak
Senior Database Administrat= or, System & Product Technologies
ProQuest
789 E. Eisenhower, Parkway, P.O. Box 1346
Ann Arbor MI 4= 8106-1346
+1.734.997.4059 or +1.800.521.0600 x 4059
mark.bobak@il.proquest.com
www.proquest.com
www.csa.com
<= br>
-----Original Message-----
From: oracle-l-bounce@freelists.org [mailto: oracle-l-bounce@freelists.org] On Behalf Of Mason Loring Bliss
Sent:= Tuesday, September 18, 2007 3:04 PM
To: oracle-l@freelists.org
Subject: Oracle backup questions..= .

I'm working on Oracle back-up and recovery, and I've run ac= ross a few random
questions from looking at what other folks do, and fro= m experimenting on my
own.

1. What is the on-disk state of archiv= e logs at arbitrary times? Does Oracle
only write them out when they are complete and usable, or does it slowl= y fill
them in over time? Recovering the final archive log here seems to=
consistently fail, until the next archive log is in place, at which tim= e the
one that was previously last is imported without issue.

2. Is th= ere any meaningful difference between "recover database" and &quo= t;recover
database using backup controlfile"?

3. What is &qu= ot;crosscheck archivelog all" as opposed to "crosscheck backup&qu= ot;?

4. How does "delete obsolete" differ from "delete ex= pired backup"?

5. How do we control the frequency of archive lo= g generation? Do I simply say
"alter system switch logfile" an= d wait a bit, or does that only affect redo
logs? Should I "alter system archive log all" instead? In sho= rt, what should
I do to try to bring the archive logs as close to curren= t as is possible?

Thanks very much for your help!

--
Mason= Loring Bliss       mason@blisses.org   = ;     Oderint dum metuant!
http://blisses.org/     awake ? sleep := random() & 2 ? dream : sleep;
--
------=_Part_21097_21008361.1190197798915-- -- http://www.freelists.org/webpage/oracle-l