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

Home -> Community -> Mailing Lists -> Oracle-L -> Re: Backups in a DW Environment

Re: Backups in a DW Environment

From: Ryan <ryan.gaffuri_at_cox.net>
Date: Sat, 10 Jan 2004 11:09:25 -0800
Message-ID: <F001.005DC5E3.20040110110925@fatcity.com>


its read only data in production. we monitor for chained rows on our staging environment and do table reorgs as necessary. Our staging server only ingests data over night, so we have all day for reorgs. Or we can just do them on weekends. We may do a handful every few months. We just run a script to check on it and get an email if chained rows is over 5%.

No big deal. Archive log mode when you ingest tons of data is problematic. It just kills I/O. We do alot of full refreshes on tables every night. Its easier just to do a tablespace transport for backup and run dbverify on them. Faster to restore too. You keep two backup copies for each day. Then you just do a 'move' on one them to restore it. Very fast. ----- Original Message -----
To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com> Sent: Saturday, January 10, 2004 1:59 PM

> Would it be incorrect to assume that you never do inserts
> into newly loaded partitions, or updates that could increase
> the length of rows?
>
> 1 pctfree could be problematic in that case.
>
> Jared
>
> On Sat, 2004-01-10 at 05:04, Ryan wrote:
> > I can understand the concern about ingesting large amount of data. We
ingest
> > about 200 GB a night. To get around the archiving problem we make a
> > noarchivelog 'staging' instance, to run our loads. Then we use
transportable
> > tablespaces to move the data to production. Its alot quicker and easier
to
> > restore a backup copy transportable tablespace than it is to roll
forward
> > plus we dont have to generate massive amounts of redo.
> >
> > If you can do your loads in the middle of the night and very few
production
> > users are on then, you can put your staging instance right on your
> > production server. We don't do this, but we have them all on the same
> > netapp.
> >
> > If you do this, I recommend using 99 percent free and 1 percent used in
> > order to 'compact' your tablespace. This keeps the tablespace as small
as
> > possible and decreases how long it takes to copy. This speeds up the
load
> > process(getting data to production), backups, and recovery. We were able
to
> > knock a 28 GB tablespace down to 12 GBs.
> >
> > ----- Original Message -----
> > To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com>
> > Sent: Friday, January 09, 2004 10:04 PM
> >
> >
> > > Mohammed,
> > >
> > > Comments inline...
> > >
> > > on 1/9/04 2:24 PM, mkb at mkb125_at_yahoo.com wrote:
> > >
> > > > Have a question on backups in a DW environment.
> > > >
> > > > Our DW is somewhat small at the moment but projected
> > > > to grow. I seem to be having a hard time trying to
> > > > convince the sys admin that I don't want archive
> > > > logging turned on. To me, it does'nt make much sense.
> > >
> > > On the contrary, not using archivelog mode is what makes less sense,
thus
> > > justifying more careful consideration and justification.
Archivelogging
> > is
> > > the "industry standard" and makes complete sense in all but a few
extreme
> > > cases.
> > >
> > > Have you considered what archive logging actually provides for you,
and
> > what
> > > is necessary to engineer the same effects on your own? Think it
> > through...
> > >
> > > >
> > > > He's proposed using EMC BCV's which I've agreed to
> > > > (and also sounds like a good idea) but also wants to
> > > > turn on archiving. My thinking is why turn on
> > > > archiving if I can restore my DB from last night's
> > > > BCV's and then bring it up to date by re-loading any
> > > > data that was loaded after the BCV split.
> > >
> > > The rebuild-then-reload method seems to make sense on paper, but it is
the
> > > cause of extreme difficultly in actual practice. If you have not yet
> > > already implemented a very mature change-management procedure, to
record
> > all
> > > changes in the database, complete with all of the security to prevent
it
> > > being bypassed, then you are in for a rough time.
> > >
> > > Robust change-management and ironclad security always makes sense, but
the
> > > extra insurance of being able to recover every change using
archivelogging
> > > makes sense also.
> > >
> > > Also, on the topic of "BCV splits", one of the problems of using "BCV
> > > splits" (or file-system snapshots or similar snapshot schemes) is
that,
> > > while it makes backups very easy, it does not make recovery any
easier.
> > > This type of backup-centric thinking is very seductive.
> > >
> > > What is the purpose of the whole exercise? Taking backups? Or being
> > > recoverable?
> > >
> > > Oracle Recovery Manager (RMAN) is not named "Backup Manager" for a
reason.
> > > RMAN is "recovery-centric". It seems more complex on the backup end
of
> > > things (it isn't), but it is undeniably easier on the recovery side of
> > > things. Try to work RMAN into your strategy at all times. It is
worth
> > the
> > > extra consideration.
> > >
> > > >
> > > > Our system is not 24x7 so we can shutdown before the
> > > > BCV split. Also, it's not directly accessed by users
> > > > for ad-hoc queries. Automated processes access the
> > > > database and build cubes using Cognos tools. Users
> > > > access these and not the DB directly.
> > >
> > > Any data warehouse that is shutdown, even for a few minutes, just to
take
> > a
> > > backup, has been engineered to fail. People keep data warehouses busy
on
> > a
> > > 24x7 basis just like any other system.
> > >
> > > >
> > > > So, again I don't see the need for archive logging.
> > > >
> > > > Any thoughts?
> > > >
> > > > mohammed
> > >
> > > Hope this helps...
> > >
> > > -Tim
> > >
> > > --
> > > Please see the official ORACLE-L FAQ: http://www.orafaq.net
> > > --
> > > Author: Tim Gorman
> > > INET: tim_at_sagelogix.com
> > >
>
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: Jared Still
> INET: jkstill_at_cybcon.com
>
> Fat City Network Services -- 858-538-5051 http://www.fatcity.com
> San Diego, California -- Mailing list and web hosting services
> ---------------------------------------------------------------------
> 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).

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Ryan
  INET: ryan.gaffuri_at_cox.net

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
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).
Received on Sat Jan 10 2004 - 13:09:25 CST

Original text of this message

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