Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Usenet -> c.d.o.server -> Re: Please help: stuck with ORA-12514

Re: Please help: stuck with ORA-12514

From: Howard J. Rogers <>
Date: Tue, 15 Jun 2004 23:21:20 +1000
Message-ID: <40cef7b7$0$641$>

"Anonymous Coder" <> wrote in message
> First of all, thanks for your very detailed reply :)
> >Now to control what service *you* are asking to connect to, your
> >tnsnames.ora needs to be correct. To control what service the listener is
> >aware of, your init.ora/spfile needs to be correct. So stop mucking
> >with your listener.ora. It should read:
> >
> > (ADDRESS = (PROTOCOL = TCP)(HOST = hardy)(PORT = 1521))
> > )
> > )
> > )
> >
> >That's all. We don't need your SID_LIST bit yet, nor do we need the
> >bits and pieces. And I hope "hardy" can be pinged and gets resolved to an
> >address correctly.
> >
> >Stop your listener and re-start it.
> >>Now shutdown and re-start your instance. With that done, type at a
> >prompt:
> >
> >lsnrctl services
> This gives me a message which translates to "Connected to [...]. The
> listener does not support any services. Command completed
> successfully".

Then you don't have automatic instance registration sorted out, do you?

Did you stop and re-start your instance as I mentioned? Instances only register themselves at startup, unless you are prepared to wait 5 minutes. Or, you can log on and force registration, because you have 10g. Alter system register.

PMON will register with a listener using TCP/IP, on port 1521. Which are both attributes your listener possesses, I think. So it should be just a question of forcing registration with a forced startup or a forced register command.

> >How does the listener become aware of an instance in the first place? By
> >process called dynamic instance registration, which causes the PMON
> >background process to register itself with the listener about every five
> >minutes (or, in 9i R2 and above, when you ask it with the 'alter system
> >register' command). PMON uses whatever the INSTANCE_NAME and DB_DOMAIN
> >initialisation parameters are set to as its registration information,
> >you over-ride those two by setting SERVICE_NAMES explicitly.
> Where does this process come from,

It doesn't "come from" anywhere. It's just an integral part of an instance. So, provided you have started an instance, you have PMON. It's one of the many "background processes" without which an instance doesn't actually do any practical work.

> and where does it read the
> INSTANCE_NAME and DB_DOMAIN parameters from?

Oh dear. You do need to do just a *little* bit of background reading I think. Get yourself the concepts guide from These are init.ora parameters, which are probably for you actually spfile parameters. The init.ora is a text file which tells the instance how to behave. It's stored in ORACLE_HOME\database and is called init<SID>.ora. In 9i and above, there may be a binary version of it (instead of, or as well as, the init.ora) which is stored in the same place but called spfile<SID>.ora. Hence the binary version is known as the spfile.

Run SQL Plus from the command line, and you can check the values of parameters like so
C:\>sqlplus "/ as sysdba"

SQL> show parameter instance

NAME                                 TYPE                              VALUE
------------------------------------ --------------------------------- -----
active_instance_count                integer
cluster_database_instances           integer                           1
instance_groups                      string
instance_name                        string                            win92
instance_number                      integer                           0
open_links_per_instance              integer                           4
parallel_instance_group              string
parallel_server_instances            integer                           1


SQL> show parameter service

NAME                                 TYPE                              VALUE

------------------------------------ --------------------------------- -----
mts_service                          string                            win92
service_names                        string

And, perhaps importantly for you:

SQL> show parameter spfile

NAME                                 TYPE                              VALUE
------------------------------------ --------------------------------- -----
spfile                               string
%ORACLE_HOME%\DATABASE\SPFILE% ORACLE_SID%.ORA ...which tells me I am indeed using an spfile and not an init.ora, so if I want to edit the value of any parameters, then I must do so using alter system commands, because only the instance can actually edit the binary spfile (any old text editor can be used to edit the text-based init.ora). You may want to read all about working with the spfile in my 9i New Features e-book available for download at (see chapter 6).

>I don't see it in my task
> list, but I guess it SHOULD be running...?

Er, I have no idea what task list you are looking at. This is all done with a text editor and a command line.

> >But you may want to get yourself an installation where all the relevant
> >services start and stop correctly before bothering too much. I smell a
> >Windows installation in the offing.
> Indeed, from what you just told me, it looks like my Oracle
> installation is screwed up. The strange thing is that it was almost
> the first program I installed after a fresh install of Win2000 (with
> latest drivers, SP4, and Windows Update). Ah well. Maybe I should use
> the "standard install" and not bother with the advanced options :)
> Thanks a lot

You mentioned in your first post that you are something of an Oracle newbie. Fair enough... we all started somewhere. But do yourself a big favour and make friends with Download at least the concepts guide, and read that for a day or two. It will at least give you a flavour of the 'geography' of Oracle, so you won't feel quite so lost when someone mentions things like "PMON" or "INSTANCE_NAME". You need to know your background process names, I think, and also know when someone is quoting initialisation parameters at you. You need to know how to startup and shutdown an instance, from the command line. You should know the difference between an instance and a database. And you should know how to manage a database without the help of GUI tools. The concepts guide will help you achieve all of that, and you'll find lots of practical SQL to try out as well by reading my e-book I've already shamelessly plugged once.

Now, I realise you also mentioned in your first post that you installed Oracle because you want some coding practice. Which means, I suspect, that you are a developer, not a DBA. Fair enough: but the best developers understand the database they're developing for, at least in the basics. It's quirks, and internal mechanisms. Then they write efficient code that doesn't bring the mechanism to a grinding halt. And they understand the gibberish your average DBA sometimes talks when muttering dark thoughts about badly-coded applications. And their applications aren't usually so badly coded as a result of that understanding. So the advice to read up basic concepts really applies to you as much as it would to a would-be DBA.

HJR Received on Tue Jun 15 2004 - 08:21:20 CDT

Original text of this message