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@xxxxxxxxx]
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@xxxxxxxxxxxxxxxxxx<mailto:RStorey@xxxxxxxxxxxxxxxxxx>> 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.'
