Re: Listener is on but db istance is down

From: Frank van Bortel <frank.van.bortel_at_gmail.com>
Date: Tue, 19 Jun 2007 19:57:37 +0200
Message-ID: <f595eh$r3l$1@news3.zwoll1.ov.home.nl>

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Alen Cappelletti wrote:

> On Jun 19, 2:00 pm, 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)
>>> )

>
Make tnsnames.ora like:
(Note the localhost instead of your ISP internet address - that can change! Also note the addition of the more efficient IPC protocol)

ORCL =
    (DESCRIPTION =

       (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
       (ADDRESS = (PROTOCOL = IPC)(KEY=ORCL))
       (CONNECT_DATA =(SERVICE_NAME = ORCL))

    )

Loose the EXTPROC line and following, unless you program against external procedures.

The listener.ora that goes with this:
SID_LIST_LISTENER =
  (SID_LIST =

     (SID_DESC =
      (SID_NAME = ORCL)
       (ORACLE_HOME = C:\oracle\product\10.2.0\db_1)
     )

 )

LISTENER =

  (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = LOCALHOST)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY=ORCL))

    )

If you want to run *without* listener, try this as addition to your existing tnsnames.ora:

ALAN =
 (DESCRIPTION=
    (ADDRESS = (PROTOCOL = BEQ)(PROGRAM=oracle)

      (ARGV0=oracleORCL)
      (ARGS='(DESCRIPTION=(LOCAL=yes)(ADDRESS=(PROTOCOL=BEQ)))')

    )
    (CONNECT_DATA=(service_name=ORCL)
    )
  )

And connect using sqlplus scott_at_alan.
Some programs rely on TCP/IP, though, so in that case you must start your listener.
- --
Regards,
Frank van Bortel

Top-posting is one way to shut me up...
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.1 (MingW32)

iD8DBQFGeBkRLw8L4IAs830RAi06AKCanmeNSde8Cj4yi7GTrlydRtt4mgCePYvg 8Gj4vp3qA0icbSQU83g4iRA=
=O7Fx
-----END PGP SIGNATURE----- Received on Tue Jun 19 2007 - 12:57:37 CDT