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: Jared Still <jkstill_at_cybcon.com>
Date: Sat, 10 Jan 2004 10:59:25 -0800
Message-ID: <F001.005DC5E2.20040110105925@fatcity.com>


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).
Received on Sat Jan 10 2004 - 12:59:25 CST

Original text of this message

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