NEED HELP ON SETTING UP TABLESPACES FOR ORACLE9i

[Deanna Tran]

Hi all,
Here is my setting up situation:
During the installation, I generated a database instance name sales, and I modified
the init.ora for this also.
My question : Can someone show me on how to link this file using pfile?

In addition, I would like to know on the procedure on how to set up the tablespaces. According to oracle9i suggestion that using the raw devices, however, it has the procedure for AIX, and TRU64 but not on HP-UX. Or if there is possible, is there an other alternative ?

[Alexander M. Ermes]

Hi there.
I have attached two scripts in a file.
You will need this file for the first step :
---------------------------------------------
init.0

#
# $Header: init.ora,v 6.14 91/03/28 14:42:31 pgreenwa Osd $ init.ora Copyr (c) 1987 Oracle
#
ifile = /home/dba/oracle/admin/$ORACLE_SID/pfile/config.ora
processes = 20
----------------------------------------------
config.ora

# $Header: cnfg.orc 7001200.2 93/04/26 14:58:22 eruben Osd $ Copyr (c) 1992 Oracle
#
# cnfg.ora - instance configuration parameters

control_files = (/unnn/ORACLE/SID/cntrl/control.ctl,
/u012/ORACLE/SID/cntrl/control.ctl,
/u015/ORACLE/SID/cntrl/control.ctl)
# Below for possible future use...
#init_sql_files = (?/dbs/sql.bsq,
# ?/rdbms/admin/catalog.sql,
# ?/rdbms/admin/expvew.sql)
audit_file_dest = /home/dba/oracle/admin/SID/audit
background_dump_dest = /home/dba/oracle/admin/SID/bdump
core_dump_dest = /home/dba/oracle/admin/SID/cdump
user_dump_dest = /home/dba/oracle/admin/SID/udump
log_archive_dest = /home/dba/oracle/admin/SID/arch/arch
db_block_size = 8192

db_name = SID

----------------------------------------------

init.1st

#
# $Header: init.ora,v 6.14 91/03/28 14:42:31 pgreenwa Osd $ init.ora Copyr (c) 1987 Oracle
#
ifile = /home/dba/oracle/admin/$ORACLE_SID/pfile/config.ora
db_files=1022
db_file_multiblock_read_count = 32
db_block_buffers = 200
dml_locks = 1000
log_checkpoint_interval = 10000000
processes = 20
rollback_segments=(r0)

---------------------------------------------

for the startup at end of create step 2 you need your normal init.ora.

Hope, it helps.
Rgds
Alexander M. Ermes

[Danny Ramos]

Hi Deanna,

First make sure that this init.ora file is
located under /admin/pfile
directory and subsequently, create a link
in the /dbs directory:

$ ln -s /admin/pfile/init.ora /dbs/init.ora

With regards to creating tablespaces, you dont
actually create them on the OS level, tablespaces are created and altered inside
the database that generate a datafile reflected in the filesystem as ".dbf" file.

hope this helps

~DANNY

[Andreas D. Skjervold]

Hi

Instead of raw devices use files:
SQL>startup nomount
SQL>create database DB_NAME
maxdatafiles 200
maxinstances 1
maxlogfiles 16
character set WE8ISO8859P1
datafile
'/u02/oradata/DB_NAME/DB_NAME_system_01.dbf' size 100M
logfile
group 1 ('/u02/oradata/DB_NAME/DB_NAME_redo_01a.rdo'
,'/u06/oradata/DB_NAME/DB_NAME_redo_01b.rdo') size 10M,
group 2 ('/u02/oradata/DB_NAME/DB_NAME_redo_02a.rdo'
,'/u06/oradata/DB_NAME/DB_NAME_redo_02b.rdo') size 10M,
group 3 ('/u02/oradata/DB_NAME/DB_NAME_redo_03a.rdo'
,'/u06/oradata/DB_NAME/DB_NAME_redo_03b.rdo') size 10M;

And to create tablespaces:

SQL>create tablespace USERS datafile '/u02/oradata/DB_NAME/DB_NAME_users01.dbf' size 1M;


Andreas

[MARTINACHE]

Hi,

Instead of raw devices, you can use filesystem tablespaces with mincache=direct for mount option.

Regards,

Patrice.

[Volker Borowski]

Hi,

I do not know if this is still the same with 9i, but with earlier versions, you needed to create a public (non-system) rollback segment before you could create additional tablespaces.

create public rollback segment rbs_temp;
alter rollback segment rbs_temp online;

create tablespace ....

later on you should create a seperate tablespace for your rollback segments and then drop the temporary one again.

Hope this helps
Volker

[Andreas D. Skjervold]

Volker:
You're absolutely right.
I just left it out due to the nature of the question.

Andreas

[Deanna Tran]

Hi,
Thank you for all of your inputs. However, I 'm still a little fuzzy on some of the point.

1. as suggested in the forum, I need to create
these filesystem prior to run the script.
My question is :
1. How large is required for these filesystem?
2. Can I configure these filesystem using the storage disks or internal disk on the O/S?
3. Do they need to be mounted before running the script?
/u03 : for index table space
/u04 redologs
/u05 temporary and rollback
/u06 redologs members
/u07 Archieved redologs...
4. Afer running the script, how would I know the tablespaces has been generated and alter?
5. without giving the database where to write to and size of the database how would it know where to write to?

[Deanna Tran]

Hi,
If I didn't create any of the tablespaces file, then how does oracle run?
...if i want to have 15 tablespaces? then it would be reasonale for me to create these tables spaces using the LVM command structure on how to create filesystem? I have read that oracle suggested to use raw devices for the tablespace, but unfortunately there wasn't any procedure on to do it? so, if you can, could you help me with this problem?

[Alexander M. Ermes]

Hi there.
What you do first is starting an instance without a database in the background.
Then you create the database from this instance. When you have finished step 1, you have created a minimal database. After step 2 you can already do more. In step three you should run all the sql scripts like catexp.sql and so on for whatever you need.
Raw devices give you a good performance, if you go for real BIG databases. But the backup and restore is a bit tricky. If you want to do cpio backups or even OmniBack i would suggest filesystems.
Rgds
Alexander M. Ermes

[Andreas D. Skjervold · Accepted solution]

Hi

>My question is :
>1. How large is required for these filesystem?
The filesystem size depends on how large tablespaces your getting into place, and this depends on how many and how big tables you plan to hav within the tablespace.

>2. Can I configure these filesystem using the storage disks or internal disk on the O/S?
Preferably aviod using the root disk.

>3. Do they need to be mounted before running the script?
/u03 : for index table space
/u04 redologs
/u05 temporary and rollback
/u06 redologs members
/u07 Archieved redologs...
Yes.

>4. Afer running the script, how would I know the tablespaces has been generated and alter?
SQL>select tablespace_name from dba_tablespaces;

>5. without giving the database where to write to and size of the database how would it know where to write to?
???? When you create the database, the script generates all the necessary files (system tablepsace, controlfiles and redologs). Oracle then keeps track of everything. All you then need is to connect using SQL*Plus and create tables / enter data etc, and Oracle will store it for you in the previuosly generated database.

Andreas

[Andreas D. Skjervold]

Hi

To your quiestions in Diemanh's posting:

Hi

/u01/oradata/ and /u02/oradata/ is used when creating tablespaces that will be used for containing user data.
You also place the Oracle software here under /u01/app/oracle/product/9.0.1
The pfile init.ora goes in /u01/app/oracle/admin//pfile/init.ora with a link to /u01/app/oracle/product/9.0.1/dbs/init.ora

/u03/oradata/ is used when creating tablespaces that will be used for table indexes.
/u04 and /u06 is used for the mirrored redologs /member 1 on /u04 and member 2 in /u06
/u05 is used for creating the tablespaces that will contaion the rollback segments.

It is important to keep these parts of Oracle apart to aviod I/O botlenecks. During a DML to the database Oracle updates the rollbacksegment with read consistancy info so, then changes are made in the database buffer and written in the redolog and last the data in the table itself is changed. This means that data, rollback and redo disk aktivity must be separated.

As to you quiestion os noarchivelogs, its simply a matter of your backup policy. A development or test database do not need archiving, as you don't mind loosing data if some datafiles get lost (disk failure ..)

If you want to run with archiving its important to set the log_archive_dest to a disk different than the other Oracle volumes.

Andreas

[Deanna Tran]

hi Andreas,
Thank you so much for your help. I think I am getting a hand of this now. But there is one point in your reply that I just need a little explanation :
/u03/oradata/ is used when creating tablespaces that will be used for table indexes.
My questions:1. Is that mean I will use this directory to create all of the tablespaces that I want to in the create_db.sh?
for eg:
create tablespace sales_index1 datafile
'/u03/oradata/sales/sales-idx01.dbf' ..... and etc )
2. if that is the case then instead of just making a parent directory /u03 I have to create subdirectory such /u03/oradata/sales, and change the owner to oracle user? Is this correct?

<1. The data that you refers to is that the tablespace data?

Thanks again for your help

[Andreas D. Skjervold]

Hi

To questions 1 and 2; You've got it absolutely right.

Then what about data. Do I mean tablespace data...
What I mean is the contents in the tables that is being changed / inserted. This data, when changed, generates activity in the Rollback segments and Tablespace that contains these segments.
And futher activity in the groups of redolog files.
And at last activity in the data tablespace where the table recides.

Andreas

Good to hear that your getting it all together. Oracle can be a little tricky in the start.

[Deanna Tran]

Hi Andreas,
Thank you so much for your help but as I modified the script that you sent me i stumble on one problem:
I have gained an understand that I need the following dataspace datafile :
a. for the tablespace index
b. tablespace for roll back
c. tablespace for temp
d. table space for user
1. What is the different between tablespace index which is mounted on /u03 and database user which is mounted on /u02?
2. So for example, if I want to have 10 tablespaces, where would I put these files on directory /u02 or /u03?
3. I have attached to this email the script that I had modify to run ... so would you please take a look at it and let me know what i might need to add ??
4. This is the init.ora file that i edited...
would you please look at them and see whether or not I might need to add or delet before I run this script?
Thank you very much
Deanna
######################
# Copyright (c) 1991, 2001 by Oracle Corporation
################

###########################################
# Cache and I/O
###########################################
db_block_size=8192
db_cache_size=67108864
db_block_buffers=150
db_files=100
db_file_multiblock_read_count=10
###########################################
# Cursors and Library Cache
###########################################
open_cursors=300

###########################################
# Diagnostics and Statistics
###########################################
background_dump_dest=/u01/product/9.0.1/admin/sales/bdump
core_dump_dest=/u01/product/9.0.1/admin/sales/cdump
timed_statistics=TRUE
user_dump_dest=/u01/product/9.0.1/admin/sales/udump
#log_archive_dest=/u01/product/9.0.1/admin/sales/arch/arch
# Distributed, Replication and Snapshot
db_domain=""
remote_login_passwordfile=EXCLUSIVE
job_queue_processes = 4
job_queue_interval = 60
distributed_transactions = 10
open_links = 4

# File Configuration
###########################################oduct/9.0.1/oradata/sales/control03.c
# MTS
###########################################
er.GiopServer)", "(PROTOCOL=TCP)(PRE=oracle.aurora.server.SGiopServer)"rora.serv

###########################################
# Miscellaneous
###########################################
compatible=9.0.0
db_name=sales
###########################################
###########################################
# Network Registration
###########################################
instance_name=dia2
service_names=dia2
###########################################
# Pools
###########################################
java_pool_size=117440512
large_pool_size=1048576
shared_pool_size=117440512

###########################################
# Processes and Sessions
###########################################
processes=150

###########################################
###########################################
###########################################
fast_start_mttr_target=300
log_checkpoint_interval=10000
log_buffer=163840
max_dump_file_size=10240
###########################################
# Resource Manager
###########################################
resource_manager_plan=SYSTEM_PLAN

###########################################
# Sort, Hash Joins, Bitmap Indexes
###########################################
sort_area_size=524288

###########################################
# System Managed Undo and Rollback Segments
###########################################
undo_management=AUTO
undo_tablespace=UNDOTBS

[Andreas D. Skjervold]

Hi

>1. What is the different between tablespace index which is mounted on /u03 and database user which is mounted on /u02?
This is simply used to separate disk I/O. When you update a user table, and this table has a index put on it, this will generate activity in the index tablespace as well as in the user or data tablespace (where the table is).
If your update activity is low, you might place these on the same mount point.

The user tablespace is a general purose tablespace where user can mess around as they like.

>2. So for example, if I want to have 10 tablespaces, where would I put these files on directory /u02 or /u03?
It depends on what purpose the tablespaces will serve. Tablespaces for tables on /u02 and indexes on /u03.

>3. I have attached to this email the script that I had modify to run ... so would you please take a look at it and let me know what i might need to add ??

You'll have to place the section creating the RBS tablespace before the section creating the rollback segments in the RBS tablespace, or else the creation will fail (There is now tablespace to create in yet...)

Else it look OK. Remeber to run the datadictionary creatin scripts:
$ORACLE_HOME/rdbms/admin/catalog
and catproc
connected as Sysdba

>4. This is the init.ora file that i edited...
db_block_buffers
This should be set as high as you have memory to fit. Ideally the most used contents of your database should fit into the buffer givin you a high buffer cache hit ratio.

The java_pool_size can be set to 0 until you implement java code in the database.

And last:
remote_login_passwordfile=EXCLUSIVE
This requires that you put inplace a passwordfile in the $ORACLE_HOME/dbs directory, using the orapwd utility.
(orapwd file=orapw entries=5)
But the esiest is to set this to:
remote_login_passwordfile=NONE
unless you plane to use Enterprice Manager or similar to remotely administer the database.

Andreas