Simpler Navigation coming for Servers and Operating Systems
Coming soon: a much simpler Servers and Operating Systems section of the Community. We will combine many of the older boards, and you won't have to click through so many levels to get at the information you need. If you are looking for an older board and do not find it, check the consolidated boards, as the posts are still there.
General
cancel
Showing results for 
Search instead for 
Did you mean: 

Need oracle script(s) to generate a database

Diemanh
Occasional Visitor

Need oracle script(s) to generate a database

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
11 REPLIES
Craig Rants
Honored Contributor

Re: Need oracle script(s) to generate a database

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

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

GL,
C
"In theory, there is no difference between theory and practice. But, in practice, there is. " Jan L.A. van de Snepscheut
H.Merijn Brand (procura
Honored Contributor

Re: Need oracle script(s) to generate a database

This is a perl script we use for 7.4.3.1
After some modifications it /might/ help you.
Enjoy, Have FUN! H.Merijn
Andreas D. Skjervold
Honored Contributor

Re: Need oracle script(s) to generate a database

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
Only by ignoring what everyone think is important, can you be aware of what everyone ignores!
Deanna Tran
Frequent Advisor

Re: Need oracle script(s) to generate a database

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
Honored Contributor

Re: Need oracle script(s) to generate a database

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
Only by ignoring what everyone think is important, can you be aware of what everyone ignores!
Deanna Tran
Frequent Advisor

Re: Need oracle script(s) to generate a database

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
Honored Contributor

Re: Need oracle script(s) to generate a database

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
Only by ignoring what everyone think is important, can you be aware of what everyone ignores!
Deanna Tran
Frequent Advisor

Re: Need oracle script(s) to generate a database

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
Frequent Advisor

Re: Need oracle script(s) to generate a database

Hi andreas,
Do all of those filesystem need to have the
init.ora in the directory?
Andreas D. Skjervold
Honored Contributor

Re: Need oracle script(s) to generate a database

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
Only by ignoring what everyone think is important, can you be aware of what everyone ignores!
Andreas D. Skjervold
Honored Contributor

Re: Need oracle script(s) to generate a database

Ooops

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

Deanna, lets continue in your own posting.

Andreas
Only by ignoring what everyone think is important, can you be aware of what everyone ignores!