Converting from 9.1D to 10.02B

Is there any reason why you are only going as far as 10.2B? Why not 11.x whilst you're upgrading anyway?

As for the structure of your new storage areas, an up to date DBANALYS output from your database would be really helpful for deciding what structure your new database should take.

I don't see why you would want a 1 GB fixed extent for the schema area.  It shouldn't need anywhere near that much space if it only contains system tables.  Mine are typically 4-10 MB with over 1000 application objects in the schema.

I would keep the code separate from the database.  You mention source and object code so I assume compiles are happening on this machine.  If you have to recompile the application that will be a lot of I/O on your database volume.

Version 10.2B is over five years old and will never receive another service pack.  It isn't retired yet, but that could change at any time.  I recommend against moving to such an old release.

Customer dictates to on;ly go to v10 at this point. Probably do v11 in 6 - 12 months.

Is there a way to attach my dbanalys output? As opposed to pasting it below?

With a 3 Gb DB you can just create two type II storage areas with variable lengths extents for everything (AI, BI, D). There is no need at all for fixed length extents unless your DB is growing quickly. Just "Data Area" with 128 RPB and 64 BPC and "Index Area" with 64 RPB and 64 BPC is *probably* sufficient. The final DB will be bigger than the original but 3 or 4 or 5 Gb is the same. If you probkup with -com the empty blocks won't be backed up.

I also doubt that there is a need to separate AI, BI and D files in your case. Why do you want to segregate? In your suggested setup, AI, BI, temp files, reports, OS stuff, etc would all write to ONE 15K rpm drive that can do about 150 IOPS. The D files, which should get less write disk I/O then AI/BI/TMP get to share two 15K rpm drives (write) and 4 disks for reads. Personally, when I see a box with very few drives I like to put them all in one big RAID 10 so that everyone can benefit from all the available disk I/O.

On RHEL use a blocksize of 4Kb (the default). And your AI and BI blocksizes should be 16Kb.

It is highly unlikely that you need more than one APW. Two is overkill unless you tell me you're updating every record in the 3 Gb DB every hour.

Good points Rob. I can see that Schema area is ridiculous overkill.

Full application compiles are pretty rare and usually only when I have the system in single-user mode. And it only takes about 15 minutes. They can tolerate anything for 15 minutes.   :-)

One more point: if you go to 10.2B make sure to apply SP8.

Here is the dbanalys output.

Thanks again -- Jim

[quote user="Paul Koufalis"]

With a 3 Gb DB you can just create two type II storage areas with variable lengths extents for everything (AI, BI, D). There is no need at all for fixed length extents unless your DB is growing quickly. Just "Data Area" with 128 RPB and 64 BPC and "Index Area" with 64 RPB and 64 BPC is *probably* sufficient. The final DB will be bigger than the original but 3 or 4 or 5 Gb is the same. If you probkup with -com the empty blocks won't be backed up.

I also doubt that there is a need to separate AI, BI and D files in your case. Why do you want to segregate? In your suggested setup, AI, BI, temp files, reports, OS stuff, etc would all write to ONE 15K rpm drive that can do about 150 IOPS. The D files, which should get less write disk I/O then AI/BI/TMP get to share two 15K rpm drives (write) and 4 disks for reads. Personally, when I see a box with very few drives I like to put them all in one big RAID 10 so that everyone can benefit from all the available disk I/O.

On RHEL use a blocksize of 4Kb (the default). And your AI and BI blocksizes should be 16Kb.

It is highly unlikely that you need more than one APW. Two is overkill unless you tell me you're updating every record in the 3 Gb DB every hour.

[/quote]

Thanks Paul. I appreciate the feedback.

My thoughts were: that I thought there was some advantage to not forcing the extents to grow every time there was a write, hence the fixed extents. And also that fixing the extents at create helped avoid disk scatter/fragmentation.

My thoughts on segregating the bi from the d files was to share spread the workload around. I also thought it was advised to segregate the ai for recoverability in case the d's array had a meltdown.

Is it better to use the OS default for blocksize (4k in my case) or is "bigger always better"? Or is that up for debate?

May I ask why you recommend 16k for ai & bi -- just so i can understand, when someone else says something different?

The second APW was just based on something I read somewhere....

Thanks again --

Jim Shepherd

Level 7

I thought there was some advantage to not forcing the extents to grow every time there was a write 

Do the math: if you grow the DB 1 Gb per month (certainly NOT your case), that's 50 Mg day (20 weekdays) over 10h = 5 Mg per hour. Assuming 512 blocks per cluster and a 4 Kb blocksize, the opsys will grow an extent 2-3 times per hour. Fixed length extents would save you less than a second an hour.

segregating the bi from the d files was to share spread the workload around

You would be doing the opposite. You would be making two drives work hard on write while the other four sat idle.

thought it was advised to segregate the ai for recoverability

It is. But in practice it is rarely true. For example, can you load the hot-swap disk in the DR box? Have you/will you test it? How long will it take? If the DR box is in another city management will not want to wait to recover the last 5 minutes of AI notes.

Is it better to use the OS default for blocksize

Probably. You avoid the mythical torn page. 

why you recommend 16k for ai & bi...when someone else says something different

I don't think any of the senior DBAs reading this would recommend anything else. On a system with a low rate of AI/BI note generation it probably doesn't matter as -Mf will kick in and write the partially full AI/BI buffer to disk after 3 seconds (the default value for -Mf). On a busy system you want to do less frequent IO to disk: fill 16Kb, write 16Kb, fill 16K, write 16K...

The second APW was just based on something I read somewhere 

There is a lot of garbage floating out there. For a while the recommendation was one APW per physical disk + 1. Same for -spin: it used to be 10K * #CPU. In reality it's π (pi) * your year of birth (hat tip to Dan Foreman). Since I was born in 1970, my default value for -spin is 6189. No I'm not joking!  For most environments, a -spin of 5-10K is sufficient.