Re: password file question ...

steph wrote:
> Hi,
>
> I've read the f***ing manual, but still i don't get it. Flame me for
> being dumb if you please.
>
> Operating System: suse linux 9.3
> Oracle Flavour: Standard Edition 10.1.0.4.0
>
> I've created a password file to be able to manage a database remotely,
> and user sys is apparently in it as you can see here:
>
> SQL> select * from v$pwfile_users
> 2 ;
>
> USERNAME SYSDB SYSOP
> ------------------------------ ----- -----
> SYS TRUE TRUE
>
>
> Connecting remote works when the database is up and running:
>
> e.g. sqlplus sys/<pwd>@<service> as sysdba
>
> ... but this also worked before. But when the database is down i still
> only can connect locally, trying to connect from remote tells me:
>
> ERROR:
> ORA-12514: TNS:listener does not currently know of service requested in
> connect
> descriptor
>
> (which is the same i get when trying to connect with any other user and
> it makes perfect sense. )
>
> I thought having a password file enables me to remotely connect to an
> instance as sysdba even if it's idle or not mounted? How can this be
> accomplised then?
>
> Please give me a hint, even if it's only the manual page where this
> information is hidden - as i said i've consulted the manuals, even
> searched on tahiti.oracle.com - but still i miss the point.
>
> Thanks,
> Stephan

In your tnsnames.ora you have

<service>=
......
(connect_data=*service_name*=...) (This follows from the 12514 error) The service_name is the service_name parameter the database registers with the listener.
To register a service_name with a listener, the database must be *open*.
If you want to be able to reach a mounted database, try using the SID= syntax (as opposed to the service_name= syntax) in tnsnames.ora Guess this is why OEM sticks to the SID syntax. The listener must have been setup with a proper SID_LIST_<listener> section.

And no, this is not documented, it is implied. You only need to read *very carefully* and *understand* what is going on. Some people might call this a documentation bug.
Only open databases register themselves with a listener. You could have easily verified that by lsnrctl services.

Note, that if the database was really down the SID= syntax doesn't help you either.

-- 
Sybrand Bakker
Senior Oracle DBA