| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: I/O activity during HOT backups- CORRECTED??
To find out what is available as an undocumented entry in the init.ora =
file you could use the following query:
1 select KSPPINM,
2 nvl(KSPPSTVL,'NULL'),
3 KSPPDESC
4 from x$ksppi x, x$ksppcv y
5 where x.INDX =3D y.INDX
6 and translate(KSPPINM,'_','#') like '#%'
7* order by KSPPINM
SQL> /
Just a reminder and to re-enforce Rachel . DO NOT USE WITHOUT DIRECTION OF =
OWWS.
Ron
>>> carmichr_at_hotmail.com 06/21/00 10:05AM >>>
Maybe because, depending on the version you are running, that parameter=20
might not be there? Oracle Support and Oracle University keep telling =
us=20
that the "underscore" parameters are not to be used without direction.=20
That's why they are UNDOCUMENTED.
Okay, I'm better now, off the soapbox.
Rachel
>From: "kgopalakrishnan" <kgopalakrishnan_at_mantraonline.com>
>Reply-To: ORACLE-L_at_fatcity.com=20
>To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
>Subject: RE: I/O activity during HOT backups- CORRECTED??
>Date: Tue, 20 Jun 2000 23:15:04 -0800
>
>
>Hi All !
>
>
>Why are we missing the behind the scene parameter
>'_log_block_during_backups=3DTRUE'?
>
>Is it just because undoc??
>
>
>K Gopalakrishnan
>Bangalore, INDIA
>
>
>
>
>At 11:59 AM 6/20/00 -0800, Gaja Krishna Vaidyanatha wrote:
> >Steve,
> >
> >Yes, I have been involved in an EMC TimeFinder implementation.
> >And you are absolutely right on, what you have outlined is
> >exactly what occurs, i.e. put the tablespace in hot backup mode,
> >break mirror, end hotbackup mode for tablespace, copy mirror,
> >then resilver broken mirror with other 2 members.
> >
> >The amount of time it takes to re-silver is obviously a function
> >of the amount of change that is going on in the database. Given
> >that backups are usually done in relatively "lean periods", the
> >resilvering should occur very quickly. I have seen resilvering
> >time ranging from 45 secs. - 15 minutes, based on write volume
> >during the backup.
> >
> >If at all possible, you might want to consider a backup solution
> >that deploys triple mirroring and uses RMAN backups. That then
> >makes the resilvering time of the mirrors a moot point - at
> >least wrt to hot backups.
> >
> >The 3rd mirror would provide the additional security for a true
> >highly available environment, if one of the mirrors fail, and
> >can also be used to support a "read-only reporting database" if
> >there is a need for that. This provides segregation of
> >"reports" from online transactions, if needed.
> >
> >Obviously, if you have a "read-only database" supported by a 3rd
> >mirror and you resilver on a nightly basis, the time to resilver
> >may take longer. But that is not a big deal, as the mirror
> >which was just resilvered, would be broken immediately after the
> >resilvering, to prepare for that night's batch report run.
> >
> >You are also right in your comment that the "split-block"
> >problem goes away with RMAN. This is because RMAN reads and
> >writes in "db_block_size" blocking-factor, and thus the
> >split-block problem never occurs. It also performs block-level
> >checksumming and verification to ensure that the image of the
> >block it read, is the image that it is actually writing. If the
> >block has changed since it read it, it will re-read it and this
> >process occurs upto 'n' times for each block read. It is my
> >understanding that the value of n is 16 (trying to remember from
> >something I saw 3 years ago). At any rate, that is enough
> >number of iterations to ensure read-and-write-integrity for the
> >backups.
> >
> >Plus, the fact that tablespaces are not put into hot backup mode
> >while backing them up using RMAN, eliminates the extra redo
> >generation which is now relevant only to hot backups.
> >
> >Best Regards,
> >
> >Gaja.
> >
> >--- Steve Orr <sorr_at_arzoo.com> wrote:
> >> > That's why it is recommended to keep the file in the
> >> hotbackup mode for
> >> the > shortest possible time.That is one tablespace at a time.
> >>
> >> Or you can avoid split blocks altogether by using RMAN. It's
> >> an on-line
> >> backup vs. the hot backup.
> >>
> >> Regarding reducing the window for tablespaces in backup mode,
> >> has anyone
> >> used EMC's TimeFinder? It's a triple mirror where you toggle
> >> all the
> >> tablespaces into backup mode, break the mirror, and retoggle
> >> the tablespaces
> >> out of backup mode. Supposedly this only takes a few seconds.
> >> Then you
> >> backup the broken mirror and resilver it. Any ideas as to how
> >> long the
> >> resilvering takes? I'm thinking about using this stuff.
> >>
> >>
> >> Steve Orr
> >>
> >>
> >>
> >> -----Original Message-----
> >> Sanjeev
> >> Sent: Tuesday, June 20, 2000 9:36 AM
> >> To: Multiple recipients of list ORACLE-L
> >>
> >>
> >> I fully agree with this explanation expect that the redo
> >> increases by 100%.
> >> Redo increase depend on the changes made to the block.When a
> >> block is
> >> copied to the backup and somebody else make a change to a part
> >> of that block
> >> oracle has to write the complete block again because the block
> >> is fractured
> >> and
> >> oracle cannot construct the entire block in the advent of
> >> failure, so
> >> whenever a
> >> block gets changed during the hot backup oracle has to write
> >> the entire
> >> block to
> >> keep the consistency of the block. So the redo can be
> >> increased by 100% or
> >> can be
> >> by 1000% depending on the type of activity goinig on. That's
> >> why it is
> >> recommended
> >> to keep the file in the hotbackup mode for the shortest
> >> possible time.That
> >> is one
> >> tablespace at a time.
> >>
> >> Cheers
> >>
> >> -----Original Message-----
> >> Sent: Tuesday, June 20, 2000 11:25 AM
> >> To: Multiple recipients of list ORACLE-L
> >>
> >>
> >> Well, I will disagree with all of you to a point.
> >>
> >> When a tablespace is put into hot backup mode with the "begin
> >> backup"
> >> command
> >> the file headers of all datafiles comprising the tablespace
> >> have their SCN's
> >> locked (if you want to confirm that, take a very quite DB,
> >> alter one
> >> tablespace
> >> and take a look at the datafile date/time stamps. They will
> >> be different
> >> from
> >> all other datafiles in the database, very current, and equal
> >> to that of the
> >> control file.). If you remember the SCN is Oracle's internal
> >> clock used for
> >> insuring a consistent state within the database. This SCN
> >> lock gets
> >> released &
> >> the SCN updated to the current one by the "end backup"
> >> command. Now during
> >> the
> >> intervening time period, dbwr continues to write to the file
> >> as normal, but
> >> the
> >> use of redo does increase by 100% since both before and after
> >> images of the
> >> data
> >> blocks are being saved. Now, depending on what redo files
> >> your saving it
> >> may
> >> well be a good idea to switch logfiles before starting a
> >> backup. In my case
> >> we
> >> save all of them for one week longer than we save the datafile
> >> backups as a
> >> safety net.
> >>
> >> Now, if you need to restore from this hot backup the locked
> >> SCN's will
> >> guarantee
> >> you an inconsistent database, especially if you follow the
> >> Oracle book and
> >> backup the control file LAST (it's SCN never gets locked).
> >> This is why you
> >> always have to roll a hot backup forward, at least until the
> >> datafile SCN's
> >> match that of the control file.
> >>
> >> Does the IO load on the computer increase, you bet. But if
> >> you've wisely
> >> chosen
> >> the time of your backup or the size of your backplane/io ports
> >> it should not
> >> be
> >> noticeable. Anyway, what's the bother. IO loading is a SA
> >> problem (or so
> >> my SA
> >> tells me).
> >>
> >> Dick Goulet
> >>
> >> ____________________Reply Separator____________________
> >> Author: "William Beilstein" <BeilstWH_at_obg.com>
> >> Date: 6/20/00 5:57 AM
> >>
> >> The following is a quote from my favorite Oracle reference
> >> boot titled
> >> "Oracle
> >> Unleashed, Edition 2" by SAMS Publishing.
> >>
> >> When you place a tablespace in backup mode, the Oracle
> >> instance notes that a
> >> backup is being performed and internally compensates for it.
> >> As you know, it
> >> is
> >> impossible to make an authentic copy of a database file that
> >> is being
> >> written
> >> to. Upon receipt of the command to begin the backup, however,
> >> Oracle ceases
> >> to
> >> make direct changes to the database file. It uses a complex
> >> combination of
> >> rollback segments, buffers, redo logs, and archive logs to
> >> store the data
> >> until
> >> the end backup command is received and the database files are
> >> brought back
> >> to
> >> sync. .... What you should understand is that the trade-off
> >> for taking a hot
> >> backup is increased use of rollback segments, redo logs,
> >> archive logs, and
> >> internal buffers within the SGA.
> >>
> >> You must be running the database in Archive mode to perform a
> >> hot backup.
> >> Archive logs assure data consistency when backing up only
> >> pieces of a
> >> database
> >> at a time.
> >>
> >> >>> "Eric Lansu" <eric.lansu_at_quicknet.nl> 06/20/00 09:11AM >>>
> >> Am I wrong, or am I wrong?
> >>
> >> But as far as I know you have to do a log-switch BEFORE
> >> starting the
> >> HOT-backup, and one AFTER the HOT-backup. The tape containing
> >> the
> >> database-files should also contain the archive-log-files
> >> created between the
> >> log-switches. This way you can roll-forward (recover) the
> >> database to the
> >> point-in-time after completion of the backup, and thus
> >> bringing it all in a
> >> consistent state. ( yes, yes, not from a book.... )
> >>
> >> Still the question remains; how can the OS make a copy of a
> >> datafile if
> >> Oracle is still writing in it? Better, how does Oracle know
> >> what to update
> >> after recovery?
> >>
> >> Eric Lansu
> >>
> >> ----- Original Message -----
> >> To: "Multiple recipients of list ORACLE-L"
> >> <ORACLE-L_at_fatcity.com>
> >> Sent: Tuesday, 20 June 2000 11:03
> >>
> >>
> >> > Wow! your explanation is wonderful.
> >> >
> >> > Thanks Rachel.
> >> >
> >> >
> >> > Bhat
> >> >
> >> > -----Original Message-----
> >> > Sent: Tuesday, June 20, 2000 4:19 AM
> >> > To: Multiple recipients of list ORACLE-L
> >> >
> >> >
> >> > Lisa,
> >> >
> >> > I'm sorry but you are wrong. Writes to the datafiles
> >> continue during hot
> >> > backup.....
> >> >
> >> > take a hypothetical situation:
> >> >
> >> > you have 3 redo logs
> >> > you have put every tablespace in your database into hot
> >> backup mode
> >> > you do a LARGE dataload (enough to cycle through all your
> >> redo logs
> >> several
> >> > times)
> >> >
> >> >
> >> > so... if Oracle does NOT write to the datafiles, then the
> >> changes you have
> >> > been making to the blocks get overwritten in the redo logs
> >> after the logs
> >> > are archived. Once you take the tablespaces out of backup
> >> mode, given your
> >> > thinking, Oracle would have to then write all the blocks to
> >> the database
> >> > files at once. But where would it get them from? The
> >> archived redo logs
> >> are
> >> > NOT re-read, nor are the redo logs.
> >> >
> >> > So...... writing continues to the database files.
> >> >
> >> > Rachel
> >> >
> >> > >From: Lisa_Koivu_at_gelco.com=20
> >> > >Reply-To: ORACLE-L_at_fatcity.com=20
> >> > >To: Multiple recipients of list ORACLE-L
> >> <ORACLE-L_at_fatcity.com>
> >> > >Subject: Re: I/O activity during HOT backups
> >> > >Date: Mon, 19 Jun 2000 11:50:37 -0800
> >> > >
> >> > >No! The command below stops all writes to the datafiles in
> >> the
> >> tablespace
> >> > >for
> >> > >the duration of the backup, to ensure consistency.
> >> > >
> >> > >The i/o overload I see during backups is the data being
> >> copied out to our
> >> > >backup
> >> > >server. And it is usually very high: like 80% of all
> >> current activity.
> >> > >
> >> > >Lisa
> >> > >
> >> > >
> >> > >
> >> > >
> >> > >
> >> > >Dan.Hubler_at_midata.com on 06/19/2000 01:18:14 PM
> >> > >
> >> > >Please respond to ORACLE-L_at_fatcity.com=20
> >> > >
> >> > >To: Multiple recipients of list ORACLE-L
> >> <ORACLE-L_at_fatcity.com>
> >> > >cc: (bcc: Lisa Koivu/GELCO)
> >> > >
> >> > >
> >> > >
> >> > >
> >> > >
> >> > >Please settle a discussion amongst our DBA team:
> >> > >
> >> > >Is there ANY I/O that takes place to the database files
> >> (*.DBF)
> >> > >during a HOT backup? (That is, ALTER TABLESPACE BEGIN
> >> BACKUP).
> >> > >
> >> > >If not, how does the process work?
> >> > >
> >> > >Thanks.
> >> > >
> >> > >
> >> > >
> >> > >
> >> > >???
> >> > >
> >> > >also send the HELP command for other information (like
> >> subscribing).
> >>
> >> --
> >> Author: Steve Orr
> >> INET: sorr_at_arzoo.com=20
> >>
> >> Fat City Network Services -- (858) 538-5051 FAX: (858)
> >> 538-5051
> >> San Diego, California -- Public Internet access /
> >> Mailing Lists
> >>
> >--------------------------------------------------------------------
> >> To REMOVE yourself from this mailing list, send an E-Mail
> >> message
> >> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru')
> >> and in
> >> the message BODY, include a line containing: UNSUB ORACLE-L
> >> (or the name of mailing list you want to be removed from).
> >> You may
> >> also send the HELP command for other information (like
> >subscribing).
> >
> >
> >=3D=3D=3D=3D=3D
> >Gaja Krishna Vaidyanatha | gajav_at_yahoo.com=20
> >Brio Technology | (972)-304-1170
> >
> >"Opinions and views expressed are my own and not of Brio Technology"
> >
> >__________________________________________________
> >Do You Yahoo!?
> >Send instant messages with Yahoo! Messenger.
> >http://im.yahoo.com/=20
> >--
> >Author: Gaja Krishna Vaidyanatha
> > INET: gajav_at_yahoo.com=20
> >
> >Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> >San Diego, California -- Public Internet access / Mailing Lists
> >--------------------------------------------------------------------
> >To REMOVE yourself from this mailing list, send an E-Mail message
> >to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> >the message BODY, include a line containing: UNSUB ORACLE-L
> >(or the name of mailing list you want to be removed from). You may
> >also send the HELP command for other information (like subscribing).
> >
>
>--
>Author: kgopalakrishnan
> INET: kgopalakrishnan_at_mantraonline.com=20
>
>Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
>San Diego, California -- Public Internet access / Mailing Lists
>--------------------------------------------------------------------
>To REMOVE yourself from this mailing list, send an E-Mail message
>to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
>the message BODY, include a line containing: UNSUB ORACLE-L
>(or the name of mailing list you want to be removed from). You may
>also send the HELP command for other information (like subscribing).
--=20
Author: Rachel Carmichael
INET: carmichr_at_hotmail.com=20
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists Received on Wed Jun 21 2000 - 11:13:14 CDT
 |
 |