Re: Oracle OFA, Tablespaces and Redo Logs?

"Hari Om" <hari_om_at_hotmail.com> wrote in message news:d1d5ebe4.0306301321.30fc150e_at_posting.google.com...
> Hello Gurus,
>
> I was reading Oracle DBA Handbook and read article about OFA - Optimal
> Flexible Architecutre and it showed what are the different tablespaces
> used in any db. These are: SYSTEM, UNDOTBLSP, TEMP, INDX, TOOLS,
> USERS, DATA.
>
> Q1) What should be the approximate size for each tablespace....? How
> many MIN/MAX Extents should I need to allocate....?

There's no answer to that question, other than 'it all depends'. The only tablespace for which even an approximate answer can be given is SYSTEM, which is at least 150MB in 9i, because its contents are well-known, and don't include any of your own (but even then, it is sensible to leave autoallocate on for it, which means it can grow on demand, if needed).

As for min/max extents... that's old-fashioned. If you use locally managed tablespace in 8i and above (and you should) then it's irrelevant. The entire business of worrying -or particularly caring- about how many extents a table gets is pointless with such tablespaces.

>
> Q2) What would be the difference between DATA Tablespace and USERS
> Tablepsace?

Some people put the 'real' data (the reason for the existence of the database) in a tablespace called 'DATA', some into one called 'USERS'. I prefer the 'DATA' name, but the Oracle Database Configuration Assistant (the GUI wizard used to create databases) prefers the name 'USERS'. It's just a question of a name, however.

> I was reading Oracle DBA Handbook- Kevin Loney and have some
> questions on it:
>
> In one of the section (Chapter 4 - Section - Online Redo Log Files) it
> says: 'DBA's need to make sure that the online redo log files are
> mirrored by some means" and I totally agree with that. It means (if I
> am right) if my Redo Log Group has 3 files then these files should be
> on different disks.
>
> However, later on in the same chapter (Chapter 4, Section Third
> Iteration) the book says:"The online redo logs should be placed
> together on the same disks" do'nt you think it is contrary to the
> above statement - isin't it misleading....? Kindly clarify orcorrect
> me!

Personally, I find that book utterly riddled with errors and simplistic explanations, but this happens not to be an example of one. He is talking in the first example you quote about redo log *members*. A redo log group needs to be made up of at least two, and preferably three, identical copies... but each copy needs to be on a separate disk so that hardware failure doesn't mean you lose the lot in one go. Incidentally, that's known as "multiplexing" the redo logs, not mirroring them... mirroring usually refers to the use of a hardware mirror, which is inadequate on its own, because whilst it protects you against hardware failure, it doesn't protect you against software corruption or user errors. Only making LGWR write twice can do that, and that requires Oracle to believe it has two log members to fill per write.

In the second example you quote, what he is really saying is that different log *groups* can be housed on the same disk. A database must have at least two log groups... both their first members can be on, say, disk 1. But both their second members can be on disk 2. And both their third members can be on disk 3. And so on.

So you'd end up with:

GROUP 1 : Member A : C:\logs\log1a.rdo
GROUP 1 : Member B : D:\logs\log1b.rdo

GROUP 2 : Member A : C:\logs\log2a.rdo
GROUP 2 : Member B : D:\logs\log2b.rdo

Loney is, however, wrong on that second statement of his, because if you arrange the logs as I've just shown, you will be writing to the second log group on drives C & D whilst ARCH is trying to read the first log group on drives C & D... which means there's an I/O conflict going on, and you've just successfully slowed down both ARCH and LGWR. Whilst the above arrangement will work, and is relatively 'safe' and has a degree of redundancy, it's not good for performance tuning reasons.

Better would be :

GROUP 1 : Member A : C:\logs\log1a.rdo
GROUP 1 : Member B : D:\logs\log1b.rdo

GROUP 2 : Member A : E:\logs\log2a.rdo
GROUP 2 : Member B : F:\logs\log2b.rdo

Then ARCH can be reading from drives C and D whilst LGWR is writing to E and F... no I/O conflict, still a degree of redundancy.

As I say, Loney (indeed nearly all Oracle Press books) are guilty of simplistic descriptions too frequently for comfort.

~QM

>
> Thanks!
Received on Tue Jul 01 2003 - 14:38:21 CDT