Re: ORA-27101 hell!!!

Chris Clarke wrote:

> Hi,
>
> Once I've started the database and the listener, I do ps -ef | grep
> oracle and can see that the O817DB instance is indeed started (see
> following)
>
> oracle 3615 1 0 13:42 ? 00:00:00 ora_pmon_0817DB
> oracle 3617 1 0 13:42 ? 00:00:00 ora_dbw0_0817DB
> oracle 3619 1 0 13:42 ? 00:00:00 ora_lgwr_0817DB
> oracle 3621 1 0 13:42 ? 00:00:00 ora_ckpt_0817DB
> oracle 3623 1 0 13:42 ? 00:00:00 ora_smon_0817DB
> oracle 3625 1 0 13:42 ? 00:00:00 ora_reco_0817DB
> oracle 3627 1 0 13:42 ? 00:00:00 ora_snp0_0817DB
> oracle 3629 1 0 13:42 ? 00:00:00 ora_snp1_0817DB
> oracle 3631 1 0 13:42 ? 00:00:00 ora_snp2_0817DB
> oracle 3633 1 0 13:42 ? 00:00:00 ora_snp3_0817DB
> oracle 3635 1 0 13:42 ? 00:00:00 ora_s000_0817DB
> oracle 3637 1 0 13:42 ? 00:00:00 ora_d000_0817DB
> oracle 3639 1 0 13:42 ? 00:00:00 ora_d001_0817DB
> oracle 3650 1 0 13:44 ? 00:00:00

Well, that's useful to know. The instance is definitely running. But you mentioned the Listener only reported one service handler before? That's the one that's arising because of your SID_LIST part of the listener.ora, but it means automatic instance registration isn't happening, because if it were, you'd have 2, one with a status of READY and one with a status of UNKNOWN (I mentioned that before, I think).

>
> So I would think there is a TNS problem... I've checked the resolution
> of "bbserver" by doing ping bbserver - this indeed resolves it to
> 127.0.0.1.

Good.

>
> I can't see a problem with my existing tnsnames.ora and listener.ora
> configurations. Please find below my listener.ora (and below that the
> tnsnames.ora listed again)
>
> # LISTENER.ORA Network Configuration File:
> /u01/app/oracle/product/8.1.7/network/admin/listener.ora
> # Generated by Oracle configuration tools.
>
> LISTENER =
> (DESCRIPTION_LIST =
> (DESCRIPTION =
> (ADDRESS_LIST =
> (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))
> )
> (ADDRESS_LIST =
> (ADDRESS = (PROTOCOL = TCP)(HOST = bbserver)(PORT = 1521))
> )
> )
> (DESCRIPTION =
> (PROTOCOL_STACK =
> (PRESENTATION = GIOP)
> (SESSION = RAW)
> )
> (ADDRESS = (PROTOCOL = TCP)(HOST = bbserver)(PORT = 2481))
> )
> )

Well it looks OK.

> SID_LIST_LISTENER =
> (SID_LIST =
> (SID_DESC =
> (SID_NAME = PLSExtProc)
> (ORACLE_HOME = /u01/app/oracle/product/8.1.7)
> (PROGRAM = extproc)
> )
> (SID_DESC =
> (GLOBAL_DBNAME = oracle.localhost)
> (ORACLE_HOME = /u01/app/oracle/product/8.1.7)
> (SID_NAME = O817DB)
> )

And that looks OK, too.

> (SID_DESC =
> (GLOBAL_DBNAME = bbdata.localhost)
> (ORACLE_HOME = /u01/app/oracle/product/8.1.7)
> (SID_NAME = bbdata)
> )
> )
>
> TRACE_LEVEL_LISTENER = SUPPORT
>
> tnsnames.ora follows:
>
> # TNSNAMES.ORA Network Configuration File:
> /u01/app/oracle/product/8.1.7/network/admin/tnsnames.ora
> # Generated by Oracle configuration tools.
>
> O817DB =
> (DESCRIPTION =
> (ADDRESS_LIST =
> (ADDRESS = (PROTOCOL = TCP)(HOST = bbserver)(PORT = 1521))
> )
> (CONNECT_DATA =
> (SID = O817DB)
> (ORACLE_HOME = /u01/app/oracle/product/8.1.7)
> )
> )
>

And that looks fine too. You've got a referene to an alias of ORACLE with a service name with the same name as the SID_DESC declared in the listener.ora, and I presume that was just experimentation on your part, and I'd get rid of it.

So I'm left asking a couple of other questions. Are you absolutely certain that wherever and whenever you've mentioned O817DB you haven't ever typed 0817DB (ie, with a leading zero instead of an Oh)? That's one reason I would never name instance with leading numbers (1 is often typed as L or I... I've done it myself), or with letters which are subject to doubt (and Oh is about the worst one I can think of) but since the damage is done, lets just check.

And I hate to ask for yet another file, but does your init.ora contain a parameter local_listener, and if so, what's it set to?

Otherwise, I can't see much else wrong with this lot, and it'll take another pair of eyes to spot the problem.

Regards
HJR Received on Fri Oct 10 2003 - 08:17:53 CDT