03
Aug
06

To the developer turned DBA – Plan a little bit

So many companies have a project involving a big database. For some reasons they turn to Oracle but they didn’t want to have a DBA helping them…

Because “We already have IT people to do the job”. When a boss is not in the IT business, you can be sure he doesn’t make a big difference between a developer and a DBA.

And there you are! Standing alone in front of this cold server waiting to have Oracle installed on it… And you don’t have a clue!

Or you think it’s easy which is juste the same.

This post is intended to the Developers, system administrators and anybody else who needs to install Oracle on a server.

First of all: Dig a little bit of information.

I’m not going to discuss about the server configuration, it has been told here already.

But anyway, here it is. Use RAID 1 + 0 when you are in doubt.

Usually what will happen is:

Because the IT guy doesn’t have a clue how to install oracle, he will use the wizard.

He will end up with 5 tablespaces and he will quicky return to developing his application.

All the tables will be stored in the same tablespace usr and possibly on the same set of hard disks.

Indulge yourself with a bit of standard.

1) Think about the different module in your application and group your tables accordingly. Put them in different tablespaces.

2) Create a tablespace for your data and a tablespace for your indexes. Do that for each group of tables

3) Separate the indexes and the data on different hard disks

4) Separate the data modules evenly on the different disks.

Let’s consider the following:

I have an application which will have a Financial module, a provider module and a Customer module.

I will create the Tablespaces:

TD_FINANCIAL with a datafile named TD_FIN_[servicename]_01.dbf

TI_FINANCIAL with a datafile named TI_FIN_[servicename]_01.dbf

TD_PROVIDER with a datafile named TD_PROV_[servicename]_01.dbf

TI_PROVIDER with a datafile named TI_PROV_[servicename]_01.dbf

TD_CUSTOMER with a datafile named TD_CUST_[servicename]_01.dbf
TI_CUSTOMER with a datafile named TI_CUST_[servicename]_01.dbf

If you have 3 sets of disks D E and F

Then I would put the datafiles as follow:

D:\oradata\[service_name]\dat\

TD_FIN_[servicename]_01.dbf

E:\oradata\[service_name]\dat\

TI_FIN_[servicename]_01.dbf

E:\oradata\[service_name]\dat\

TD_PROV_[servicename]_01.dbf

F:\oradata\[service_name]\dat\

TI_PROV_[servicename]_01.dbf

F:\oradata\[service_name]\dat\

TD_CUST_[servicename]_01.dbf

D:\oradata\[service_name]\dat\

TI_CUST_[servicename]_01.dbf

Indexes and data are separated on different disks so that the access to the data and the indexes can be done simultaneously thus making better performance.

Also, the different modules are separated on different disks so that 2 persons can work together on different modules without reducing the performances for each of them.

Of course with a more complexe system it gets tricky to have such a perfect model. This is really school demonstration but the idea is to mix up so that the chances of accessing data simultaneously are best.
Finally, notice how the file names are standardized. The idea is to know where your tables are. With this system, it is even possible to shutdown a part of the database, say the customer module, without affecting the others (if it is not too much related, we are still in a perfect world here…)

It is still ok to create the database with the wizard, but discard all the tablespaces, except the system tablespace (that would destroy your DB) and create your own following the standards. It’s very easy to manage that with Toad or DBA Studio.

Now on Oracle 10g, it’s so simple to manage the tablespaces and the datafile, it would be a crime not doing so.

Advertisements

1 Response to “To the developer turned DBA – Plan a little bit”


  1. June 18, 2014 at 1:45 pm

    Greetings! Very helpful advice within this post! It’s the little changes which will make the largest changes.
    Thanks a lot for sharing!


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s


%d bloggers like this: