Re: Listener is on but db istance is down

From: <mfullerton_at_gmail.com>
Date: Tue, 19 Jun 2007 17:58:38 -0000
Message-ID: <1182275918.399801.160960@u2g2000hsc.googlegroups.com>

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

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

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> Received on Tue Jun 19 2007 - 12:58:38 CDT