topic Re: how to create a database in Operating System - HP-UX

how to create a database

Giada Bonfà — Thu, 24 Apr 2003 13:55:04 GMT

Hi,
I have to create a database and I don't know how to set SGA dimension, how many rollback segments, redo log I have to do. Furthermore I don't know which script (e.g. catalog.sql) I have to run. After I will have to do a full import.

Could someone give to me some advice about standard setting?

thank you

Re: how to create a database

Zafar A. Mohammed_1 — Thu, 24 Apr 2003 13:59:56 GMT

If this is Oracle 8i, then just install Oracle DBA Studio on your PC and all its GUI.

Thanks
Zafar

Re: how to create a database

Giada Bonfà — Thu, 24 Apr 2003 14:10:43 GMT

Hi zafar,
I have already done it. How can I use it in my case?

Re: how to create a database

Zafar A. Mohammed_1 — Thu, 24 Apr 2003 14:16:53 GMT

If you want to create a new instance, if you have Xterm then run on your unix server this command
$dbassist
(hope i am correct)

just create with minimal requirement and later you can increase the parameter, but make sure don't choose all the options which you don't require now, like it might ask you spatial data and J/SQL other.

In this case you don't have to manually run all those database scripts, I mean catproc.sql, catalog.sql and other 3-4 scripts.

Thanks
Zafar

Re: how to create a database

Giada Bonfà — Thu, 24 Apr 2003 14:32:22 GMT

hi Zafar,
I know dbassist but I don't know for example how big I have to do shared pool and so on.
I need to know reasonable value for the parameter.

Re: how to create a database

Zafar A. Mohammed_1 — Thu, 24 Apr 2003 14:53:06 GMT

If you know how many concurrent users and how much the database size and how much available memory on the server, then you can tune these parameters. I hope you already read this document, if not

http://download-west.oracle.com/docs/cd/A87860_01/doc/server.817/a76961/toc.htm

Thanks
Zafar

Re: how to create a database

Brian Crabtree — Thu, 24 Apr 2003 17:01:53 GMT

The shared pool should be sized according to how large you will need.

Good basic values are:

shared_pool_size = 50m
db_block_buffers = 1000
log_buffer = 32768
processes = 50

Rollback segments can be set however you like. I would recommend starting with a 1 or 2m extent size, and tuning it after the application is running. Logs are the same way, using 500k to 1m to start, and make sure that you aren't switching more than once every 30 seconds.

The scripts that you will need to run are:
catalog.sql
catproc.sql

If you use any extra procedures (replication, advanced query, parallel query, etc) you will need to run the scripts associated with those products as well to make sure that the tables/indexes/views are loaded correctly.

Hope this helps,

Brian

Re: how to create a database

twang — Fri, 25 Apr 2003 00:41:09 GMT

Re: how to create a database

malay boy — Fri, 25 Apr 2003 00:52:06 GMT

Hi,
The most information things to decide during database creation are db block size as this parameter cannot be change after database is create.But the SGA size,rollback,redo log all can be altered/changed after the database creation.
In the init.ora file there are already size mention for SGA and may be you can use the default size and changed later if cause some performace issue.

regards
mB

Re: how to create a database

Tim Sanko — Fri, 25 Apr 2003 17:49:47 GMT

Excellent technical work on creation of DB above.

I find a couple of logical additions could really help.

There are two keys to your long term happiness here!

Storage in the temp and system
tablespaces. Don't install them as defaults. Make system tablespace large for a production database. Non-indexed queries use temp tablespace at alarming rate.

The system tablespace holds views,triggers,database information, and other sundries to keep the Database healthy.

Most tableaspaces, and datafiles can be created/modified to grow by themselves.

This is an evil practice. (I do not recommend autoextend) make sure you have the storage you need immediately.

Plan the indexes to be on a separate drive from the datafiles,

Place the archive logs on a separate spindle. the system can go no faster than the log writer process.

Watch space evaporate. Oracle is almost a license to buy storage...

Tim