Solved

Creating new database and a parameter file

Posted on 2012-12-24
24
525 Views
Last Modified: 2013-01-07
I am new to Oracle and I am trying to create a database on the UNIX server that has Oracle software installed but no databases created yet. I need to create a databas usign SQL script (not dbca).
I am following these steps:

http://www.csee.umbc.edu/portal/help/oracle8/server.815/a67772/create.htm

My first step is to create a parameter file. I need to create it from scratch and need some help creating it and setting the parameters.

Can someone help me and also provide me with a sample pfile
0
Comment
Question by:YZlat
  • 13
  • 8
  • 2
  • +1
24 Comments
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 38718777
really oracle 8?

the parameters (for oracle 8) are shown in step 3 of that page,
the file is a plain ascii file, each line have 1 parameter like:
paramter_name = paramater_value

anyhow, here a sample file shown:
http://psoug.org/reference/init_dot_ora.html
0
 
LVL 12

Expert Comment

by:praveencpk
ID: 38718844
if your are looking steps to create a 8.1.7 database here is the document.
http://www.akadia.com/services/ora_linux_install.html

if it is 10g and above here is the doc for step by step procedure.

http://kamranagayev.com/2009/05/31/create-database-manually-step-by-step-instruction/
0
 
LVL 35

Author Comment

by:YZlat
ID: 38718927
I didn't even notice it was Oracle 8:)
0
 
LVL 35

Author Comment

by:YZlat
ID: 38718936
Actually I am using 11g. I have just installed ORacle 11g software without a database and now I am attempting to create my first database. There is no listener file on the server. Do I need to create that one as well? And tnsname.ora too?
0
 
LVL 12

Expert Comment

by:praveencpk
ID: 38718956
yes you have to create a listener and tnsnames.ora file as well, you can go with my second doc which as given step by step procedure.
0
 
LVL 35

Author Comment

by:YZlat
ID: 38718973
looks good, I am gonna try it
0
 
LVL 12

Expert Comment

by:praveencpk
ID: 38719026
here some more doc on step by step process for creation oracle 11g database manually

https://sites.google.com/site/catchdba/11g-manual-database-creation

http://ack2904.wordpress.com/2009/09/18/database-creating-11gr2/
0
 
LVL 35

Author Comment

by:YZlat
ID: 38721361
praqveen, I am trying to follow the steps but when I get to running

CREATE SPFILE FROM PFILE=’/home/oracle/product/11.2.0.3/initTEST.ora’;

SQLPlus gives me an error:

ORA-12547: TNS:lost contact
0
 
LVL 12

Accepted Solution

by:
praveencpk earned 500 total points
ID: 38723373
before executing the above command you have to login to DB i.e

#sqlplus / as sysdba
SQL>startup nomount pfile=/home/oracle/product/11.2.0.3/initTEST.ora;
ORACLE instance started.

Total System Global Area 1071333376 bytes
Fixed Size 1341312 bytes
Variable Size 620759168 bytes
Database Buffers 444596224 bytes
Redo Buffers 4636672 bytes

SQL>CREATE SPFILE FROM PFILE=’/home/oracle/product/11.2.0.3/initTEST.ora’;

I think these paticular step is missing you can go through the doc in my post 38719026
0
 
LVL 35

Author Comment

by:YZlat
ID: 38723644
actually as soon as I run

sqlplus / as sysdba

I get the error

ORA-12547: TNS:lost contact
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 38723653
I would say the instance is not up.
so you need to check the instance's alertlog file to see "why" this is so
0
 
LVL 35

Author Comment

by:YZlat
ID: 38724785
how do I check this log? I am very new to all this

There isn't a database created yet. It's just a server with only software installed
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 12

Assisted Solution

by:praveencpk
praveencpk earned 500 total points
ID: 38726567
The alertlog will be present only when your database is created, so first create the database.

try execueting sqplus /nolog once you get SQL prompt then execute below stepts top create the database.

SQL>startup nomount pfile=/home/oracle/product/11.2.0.3/initTEST.ora;
ORACLE instance started.

Total System Global Area 1071333376 bytes
Fixed Size 1341312 bytes
Variable Size 620759168 bytes
Database Buffers 444596224 bytes
Redo Buffers 4636672 bytes

SQL>CREATE SPFILE FROM PFILE=’/home/oracle/product/11.2.0.3/initTEST.ora’;

if still you are getting an error, can you post the error meassage and the listener.ora details.
0
 
LVL 35

Author Comment

by:YZlat
ID: 38726783
I started sqlplus with

sqlplus /nolog

and as soon as I ran

startup nomount pfile=/home/oracle/product/11.2.0.3/dbs/initTEST.ora;

I got

ORA-12547: TNS:lost contact
0
 
LVL 12

Expert Comment

by:praveencpk
ID: 38726792
i think it as to do with some permission issues, check this doc

http://www.dbvisit.com/forums/showthread.php?p=3058
0
 
LVL 12

Expert Comment

by:praveencpk
ID: 38726800
also can you post the listener.ora file details which is present in the directory
<ORACLE_HOME>/network/admin/listener.ora

and get this details as well
#lsnrctl status
0
 
LVL 35

Author Comment

by:YZlat
ID: 38726861
I did and listener is started successfully but it also says "The listener supports no services". What does that mean?
0
 
LVL 35

Author Comment

by:YZlat
ID: 38726883
praveen, the permissions for the oracle file are -rwsr-s--x  which looks about right
0
 
LVL 35

Author Comment

by:YZlat
ID: 38726912
Looks like "listener supports no services" means that there is no database started, which is about right since there is no database there to begin with
0
 

Expert Comment

by:Gon75
ID: 38727514
Be sure that your are connected with oracle user and all enviroment variables are setting properly.
0
 
LVL 12

Expert Comment

by:praveencpk
ID: 38727727
you are goining step by step right as mentioned in previous post links

exporting the ORACLE_HOME, ORACLE_SID, LD_LIBIRARY_PATH, ORACLE_BASE and then the below steps

1) sqlplus /nolog
2)SQL> connect / as sysdba
connected to ideal instance
3)SQL> startup nomount pfile=/home/oracle/product/11.2.0.3/dbs/initTEST.ora;
0
 
LVL 35

Author Comment

by:YZlat
ID: 38727842
@Gon75, already did
0
 
LVL 35

Author Comment

by:YZlat
ID: 38727845
praveen, here is what happened:

> sqlplus /nolog

SQL*Plus: Release 11.2.0.3.0 Production on Fri Dec 28 13:44:48 2012

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

SQL> connect / as sysdba
ERROR:
ORA-12547: TNS:lost contact

Open in new window

0
 
LVL 35

Author Comment

by:YZlat
ID: 38727878
can someone tell me if my env variables look correct?

ORACLE_BASE=/u10/app/oracle
ORACLE_HOME=/u10/app/oracle/product/11.2.0.3
ORACLE_SID=TESTDB
ORAENV_ASK=YES
LD_LIBRARY_PATH=/u10/app/oracle/product/11.2.0.3/lib:/u10/app/oracle/product/11.2.0.3/lib
PATH=/home/oracle:/usr/local/bin:/usr/bin:/etc:/usr/sbin:/usr/ucb:/home/oracle/bin:/usr/bin/X11:/sbin:.:/u10/app/oracle/product/11.2.0.3/bin
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines

920 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now