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" <[EMAIL PROTECTED]> 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" <[EMAIL PROTECTED]> > > Sent: Friday, January 09, 2004 10:04 PM > > > > > > > Mohammed, > > > > > > Comments inline... > > > > > > on 1/9/04 2:24 PM, mkb at [EMAIL PROTECTED] 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: [EMAIL PROTECTED] > > > > > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.net > -- > Author: Jared Still > INET: [EMAIL PROTECTED] > > 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
