Need oracle script(s) to generate a database

[Diemanh]

Hi,
Please help me to clarify :
1. I have completed installed oracle 9i on my system. And I have started the database and edit
the oratab. However, when I tried to use sqlplus to login it gives me this error : NLS ...error.
here is the step that i did
su - oracle
echo $ORACLE_HOME
/u01/product/9.0.1
cd $ORACLE_HOME
sqlplus /nolog
SQL> connect SYSTEM/manager
error : NLS ....
Can someone help me with this problem...I have oracle 9i not 8 ...
2. I tested the start up the script, it said it has started completed
/sbin/init.d/oracle start
ps -ef | grep ora
3. Does any1 have any generic script that would give help to generate a database and write to them?

Thank you very much for your hel

[Craig Rants]

This page should give you a ton of stuff to use.

http://www.dbaoncall.net/index_files/index_unix_scripts.html

GL,
C

[H.Merijn Brand (procura]

This is a perl script we use for 7.4.3.1
After some modifications it /might/ help you.

[Andreas D. Skjervold]

Hi

Use the script I posted in this thread:
http://forums.itrc.hp.com/cm/QuestionAnswer/1,,0x36ce94f22a31d6118fff0090279cd0f9,00.html

Modify with your preferences and run.

To enter data into this database you'll need to create a suiting table with the "create table" statament in sqlplus, and insert data with the "insert into" statement.

Andreas

[Deanna Tran]

HI Andreas,
Thank you for sending the script.
I would like to ask you several questions prior of running this script.
1. I know that I have to make logical volumes
and not the filesystem. My question is this,
I just do the same old LVM stuff? without filesystem, I can't mount that data mount point?
2. How would I run this script in sqlplus ?
Thank you very much

[Andreas D. Skjervold]

Hi

Last thing first; to runb scipt in SQL*Plus:
sqlplus sys/passwd as sysdba
SQL>@script
or
SQL>start script

second, I'm not sure what you mean by using LVM and not filesystem...
If you mean using Raw devices for the tablespaces, I'm not familiar with that.
Have even read that there isn't much to gain on using this, and by using filesystem / blockdevices instead you get much easier administration.

If using filesystem, you can set mount options to bypass the os buffering, as Oracle does its own buffering. Dont have the options here, but I'm sure someone in the forums have.

Andreas

[Deanna Tran]

Hi Andreas,
I had created the init.ora in the $ORACLE_HOME/dbs
but i haven't do any link yet? would you help me on how to do it?

If that is the case using the filesystem, then i must mount these filesystems prior running
your script? I am currently running oracle 9i.
Can you go over this procedure on making the tablespaces to make sure i am on the right track?
pvcreate -f /dev/rdsk/xxxx
vgcreate /dev/vgxx /dev/dsk/xxxx
lvcreate -L size -n tablespace1 /dev/vgxx
(If I want to have 20 tablespaces, then I must
issued this command 20 times?)
newfs -F vxfs /dev/vgxx/rlvolxx
--- the software is located on /u01
and the database is on /u02
--- how do i mount this tablespace f/system to those ?

[Andreas D. Skjervold]

Hi

Before creating the database you must have in place the different mount points for the filesystems. In my setup /u01 to /u07 where:
/u01: Data tablespaces and software
/u02: Data tablespaces
/u03: Index tablespaces
/u04: Redologs
/u05: Temporary and rollback
/u06: Redolog members
/u07: Archived redologs

(ops; my script doesn't follow this. Just edit to your preference)

Andreas

[Deanna Tran]

Hi Andreas,
I have some questions in regards to your script
what does /u01 and /u03 and /u02 and /u05?
I noticed that this database is running with noachieved logs? why?

[Deanna Tran]

Hi andreas,
Do all of those filesystem need to have the
init.ora in the directory?

[Andreas D. Skjervold]

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

[Andreas D. Skjervold]

Ooops

Seems like we've overtaken Diemanh's thread.. I wasn't awake I think...

Deanna, lets continue in your own posting.

Andreas