RE: Single TS vs Multiple
Date: Tue, 22 Dec 2015 18:05:33 +0000
Message-ID: <FE4C2B093843BB4B873D754E5E0BE4DB01998C26BF_at_DCSOSVMS02.dcso.org>
I don’t have the worry about accidental deletes. They can only delete from within the app and I’m the only one with read/write sql level access. No ASM here, don’t really see the benefit of it. I suspect that if I have to do a TISPIR recovery, it will be to return the entire database to a point. Yep, there is a definite benefit to index/data separation with regards to corruption.
I have no driving need….just curiosity.
From: Andrew Kerber [mailto:andrew.kerber_at_gmail.com]
Sent: Tuesday, December 22, 2015 11:50 AM
To: Storey, Robert (DCSO)
Cc: Oracle L
Subject: Re: Single TS vs Multiple
Boy, now you opened a can of worms. :) My personal preference is to have index and data tablespaces separate, and that is primarily for logical separation. Also,on occasion when I hit corruption, I have managed to avoid downtime if the corruption was on an index tablespace rather than a data tablespace,
There are backup and recovery advantages to keeping your tablespaces a manageable size (manageable being a flexible definition). if you have users that might accidentally delete data, the ability to do TSPITR is important, and the size of the tablespace will drive the time to do your point in time restore of the tablespace. Bigfile tablespaces are mostly used with ASM, if you are going with file system, the file size will probably be limited by your operating system. Also, if using bigfile tablespaces it is important to be able to multi-thread your backup, or the time involved will be prohibitive.
There are many other concerns on this subject, you could probably write a book. The answer is that it depends on your requirements.
On Tue, Dec 22, 2015 at 11:03 AM, Storey, Robert (DCSO) <RStorey_at_dcso.nashville.org<mailto:RStorey_at_dcso.nashville.org>> wrote: I’m working on a redesign of the structure for my database, which has been active since 2000. I have three main users/schemas that hold the data for the app (the only app running in the database).
User A has 341 tables spread across 9 tablespaces. The tablespaces have basically grouped like objects that support a specific area of the app. Also, each “data” tablespace has a corresponding “index” tablespace. So, 18 tablespaces to support those 341 objects
User B has two tablespaces. One TS has 44 objects, the other has 1 (an audtiting table and largest table in system). Each TS has it’s own index tablespace.
User C owns all the PL/SQL. No physical objects.
User B is my schema that has about a 100 objects. One data TS and one index TS.
Then the assorted system TS, temp, undo, tec.
All tablespaces are supported by a single datafile. Again, same design since 2000 with no driving need to change. My Data TS total just about 40gig, indexes about 27gig
Major OLTP applccation. Tons of reads, fair amount of small writes.
Going to build a 12 disk RAID10 to support all the dat, so 6 mirrored groups, then striped. Two separate disks to handle the multiplexed redo.
So, other than organization purposes, is there any benefit to having multiple TS with their own datafiles, vs, fewer TS, each which might have a couple of datafiles? Or even just one datafile using the BIGFILE option within 11g? I can’t take any one TS offline without taking down the app.
Granted, if a datafile for one of the 13 or so current TS goes bad, I only have to restore that file, instead of one really large one.
Thoughts?
--
Andrew W. Kerber
'If at first you dont succeed, dont take up skydiving.'
--
http://www.freelists.org/webpage/oracle-l
Received on Tue Dec 22 2015 - 19:05:33 CET