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