| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: Listener is on but db istance is down
On Jun 20, 9:47 am, Alen Cappelletti <alen.cappelle..._at_gmail.com>
wrote:
> On Jun 19, 7:58 pm, "mfuller..._at_gmail.com" <mfuller..._at_gmail.com>
> wrote:
>
>
>
>
>
> > On Jun 19, 7:00 am, sybrandb <sybra..._at_gmail.com> wrote:
>
> > > On Jun 19, 9:56 am, Alen Cappelletti <alen.cappelle..._at_gmail.com>
> > > wrote:
>
> > > > My test PC have a hostname like ITVAW2k3DEV02 with a IP
> > > > 162.162.162.2.
> > > > Into my host file in windows I have overwrite the default settings
> > > > 127.0.0.1 with
>
> > > > 162.162.162.2 ITVAW2k3DEV02
>
> > > > tnsping orcl return successful but when I try a test like
>
> > > > >sqlplus connect system_at_orcl
> > > > > insert password
>
> > > > I got an error ora-12505...I see on web browser admin listner is ON
> > > > but db is down.
> > > > I try to rebuild listerner and tnsnames files...many times and one
> > > > time this operation restart my connection and I worked for 5 minutes.
> > > > After this time I restart for security test my PC and all stop
> > > > another
> > > > time. I thik there is sono connection problems...
>
> > > > I'm on oracle 10 g
>
> > > > ------- my listener --------
> > > > SID_LIST_LISTENER =
> > > > (SID_LIST =
> > > > (SID_DESC =
> > > > (SID_NAME = PLSExtProc)
> > > > (ORACLE_HOME = C:\oracle\product\10.2.0\db_1)
> > > > (PROGRAM = extproc)
> > > > )
> > > > )
>
> > > > LISTENER =
> > > > (DESCRIPTION_LIST =
> > > > (DESCRIPTION =
> > > > (ADDRESS = (PROTOCOL = TCP)(HOST = ITVAW2K3DEV02)(PORT = 1521))
> > > > )
> > > > )
>
> > > > ------- tnsnames ----------
> > > > ORCL =
> > > > (DESCRIPTION =
> > > > (ADDRESS_LIST =
> > > > (ADDRESS = (PROTOCOL = TCP)(HOST = 162.162.162.2)(PORT = 1521))
> > > > )
> > > > (CONNECT_DATA =
> > > > (SERVICE_NAME = ORCL)
> > > > )
> > > > )
>
> > > > EXTPROC_CONNECTION_DATA =
> > > > (DESCRIPTION =
> > > > (ADDRESS_LIST =
> > > > (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
> > > > )
> > > > (CONNECT_DATA =
> > > > (SID = PLSExtProc)
> > > > (PRESENTATION = RO)
> > > > )
>
> > > > ------- sqlnet.ora ---------
>
> > > > SQLNET.AUTHENTICATION_SERVICES= (NTS)
> > > > NAMES.DIRECTORY_PATH= (TNSNAMES)
>
> > > > )
>
> > > > I hope someone can help me
>
> > > > Alen Italy
>
> > > I'm not sure why you can't be bothered to look up ora-12505.
> > > The error is quite explicit:
> > > you are connecting using a connect with SID=... and you didn't
> > > configure the SID in listener.ora.
>
> > > Consequently the tnsnames.ora in your post is NOT the tnsnames.ora you
> > > are using to connect.
>
> > > Your assertion 'db is down' is probably incorrect too as you clearly
> > > didn't verify the state of the OracleService<SID>.
> > > So what should anyone do? Take their crystal ball?
>
> > > --
> > > Sybrand Bakker
> > > Senior Oracle DBA- Hide quoted text -
>
> > > - Show quoted text -
>
> > Sybrand,
>
> > He has sid=for the external proc, not for ORCL, which is setup to use
> > service names.
>
> > Alen -
>
> > Here is a stupid question. IS the database down? You didn't say if you
> > checked in your original post. If you want to see what the listener
> > has registered, issue a lsnrctl stat command in a command prompt
> > window. Here is an example of mine with the DB down. Notice that the
> > only service listed is "PLSExtProc".
>
> > C:\Documents and Settings\mfullerton>lsnrctl stat
>
> > LSNRCTL for 32-bit Windows: Version 10.2.0.1.0 - Production on 19-
> > JUN-2007 12:54:55
>
> > Copyright (c) 1991, 2005, Oracle. All rights reserved.
>
> > Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1)))
> > STATUS of the LISTENER
> > ------------------------
> > Alias LISTENER
> > Version TNSLSNR for 32-bit Windows: Version
> > 10.2.0.1.0 - Production
> > Start Date 10-JUN-2007 22:55:12
> > Uptime 8 days 13 hr. 59 min. 43 sec
> > Trace Level off
> > Security ON: Local OS Authentication
> > SNMP OFF
> > Listener Parameter File C:\oracle\product\10.2.0\db_1\network\admin
> > \listener.ora
> > Listener Log File C:\oracle\product\10.2.0\db_1\network\log
> > \listener.log
> > Listening Endpoints Summary...
> > (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe
> > \EXTPROC1ipc)))
> > (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=LH91B9Z)(PORT=1521)))
> > Services Summary...
> > Service "PLSExtProc" has 1 instance(s).
> > Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this
> > service...
> > The command completed successfully
>
> > C:\Documents and Settings\mfullerton>
>
> > ----Now, I have started the DB and here is the new lsnrctl stat
> > output. Notice the addition of the service TEST:
> > C:\Documents and Settings\mfullerton>lsnrctl stat
>
> > LSNRCTL for 32-bit Windows: Version 10.2.0.1.0 - Production on 19-
> > JUN-2007 12:58:14
>
> > Copyright (c) 1991, 2005, Oracle. All rights reserved.
>
> > Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1)))
> > STATUS of the LISTENER
> > ------------------------
> > Alias LISTENER
> > Version TNSLSNR for 32-bit Windows: Version
> > 10.2.0.1.0 - Production
> > Start Date 10-JUN-2007 22:55:12
> > Uptime 8 days 14 hr. 3 min. 2 sec
> > Trace Level off
> > Security ON: Local OS Authentication
> > SNMP OFF
> > Listener Parameter File C:\oracle\product\10.2.0\db_1\network\admin
> > \listener.ora
> > Listener Log File C:\oracle\product\10.2.0\db_1\network\log
> > \listener.log
> > Listening Endpoints Summary...
> > (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe
> > \EXTPROC1ipc)))
> > (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=LH91B9Z )(PORT=1521)))
> > Services Summary...
> > Service "PLSExtProc" has 1 instance(s).
> > Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this
> > service...
> > Service "test" has 1 instance(s).
> > Instance "test", status READY, has 1 handler(s) for this service...
> > Service "testXDB" has 1 instance(s).
> > Instance "test", status READY, has 1 handler(s) for this service...
> > Service "test_XPT" has 1 instance(s).
> > Instance "test", status READY, has 1 handler(s) for this service...
> > The command completed successfully
>
> > C:\Documents and Settings\mfullerton>- Hide quoted text -
>
> > - Show quoted text -
>
> I try with lsnrctl stat and always see
> Services Summary...
> Service "PLSExtProc" has 1 instance(s).
> Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this
> service...
>
> When I start my ServiceOracleORCL always see the same massage above.
> After 2hours of testing one time all go fine.
> But when I restart all have always same problem:
> ORA-12514, TNS:listener does not currently know of service requested
> in connect
>
> ------- listener --------
> SID_LIST_LISTENER =
> (SID_LIST =
> (SID_DESC =
> (SID_NAME = PLSExtProc)
> (ORACLE_HOME = C:\oracle\product\10.2.0\db_1)
> (PROGRAM = extproc)
> )
> )
>
> LISTENER =
> (DESCRIPTION_LIST =
> (DESCRIPTION =
> (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
> )
> )
>
> ------- tnsnames --------
> ORCL =
> (DESCRIPTION =
> (ADDRESS_LIST =
> (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
> )
> (CONNECT_DATA =
> (SERVICE_NAME = ORCL)
> )
> )
>
> EXTPROC_CONNECTION_DATA =
> (DESCRIPTION =
> (ADDRESS_LIST =
> (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
> )
> (CONNECT_DATA =
> (SID = PLSExtProc)
> (PRESENTATION = RO)
> )
>
> ------------------------------------------
> Noe I try a new total installation....I'm going mad!- Hide quoted text -
>
> - Show quoted text -
I made a new total installation of of Oracle 10g and without change a "comma" my DB work perfectly...no restart at the end of process...
On prompt I lanch -lsnrctl stat- and see my istance e my services
ready.
When I restart my PC I got the same problem...my instance and my
service UNKNOW.
At this point I don't have understood what can I resolve it. I put also a SID in the LISTENER:
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = C:\oracle\product\10.2.0\db_1)
(PROGRAM = extproc)
(SID_DESC =
(SID_NAME = ORACLE10)
(ORACLE_HOME = C:\oracle\product\10.2.0\db_1)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = ITVAW2k3dev02)(PORT = 1521))
)
Thanks Alen Received on Thu Jun 21 2007 - 02:42:28 CDT
 |
 |