NEED HELP ON SETTING UP TABLESPACES FOR ORACLE9i

[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