Home » RDBMS Server » Server Administration » Unable to connect using Net service name (Oracle 10G R3)
Unable to connect using Net service name [message #283198] Mon, 26 November 2007 06:18 Go to next message
caljedi
Messages: 6
Registered: November 2007
Junior Member
I'm running 10.2.0.3 on a RHEL 4 box. I am able to connect to Oracle locally using :

sqlplus username

however, if i try to login using:

sqlplus username@SID, i get this error message:

================================
ERROR:
ORA-01034: ORACLE not available
ORA-27101: shared memory realm does not exist
Linux Error: 2: No such file or directory
==================================

My sqlnet.ora file looks like this:
=======================
SQLNET.AUTHENTICATION_SERVICES=(NONE)
NAMES.DEFAULT_DOMAIN = world
NAMES.DIRECTORY_PATH=(TNSNAMES)
=============================

and my tnsnames.ora file looks like this:
===========================
testsid.world =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 1.2.3.4)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = testsid)
)
)
==================================
I've tried this with both SYSDBA users as well as standard users; also ran TNSPING on the box which returns a success code, so I'm not really sure what I'm missing here. Any help would be greatly appreciated.
Re: Unable to connect using Net service name [message #283203 is a reply to message #283198] Mon, 26 November 2007 06:24 Go to previous messageGo to next message
Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE
Senior Member

ORA-01034: ORACLE not available
Connect with sys as sysdba user to sqlplus command line.
and open your database.
cmd>sqlplus /nolog
SQL>conn sys as sysdba
pwd:
SQL>startup


Re: Unable to connect using Net service name [message #283205 is a reply to message #283203] Mon, 26 November 2007 06:26 Go to previous messageGo to next message
caljedi
Messages: 6
Registered: November 2007
Junior Member
Hi Mohammed,
Thanks for the quick reply. I've already verified that the database instance is up and running. So that possibility has already been ruled out.
Re: Unable to connect using Net service name [message #283209 is a reply to message #283205] Mon, 26 November 2007 06:30 Go to previous messageGo to next message
Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE
Senior Member

I've already verified that the database instance is up and running.
Show us.
Re: Unable to connect using Net service name [message #283210 is a reply to message #283209] Mon, 26 November 2007 06:33 Go to previous messageGo to next message
caljedi
Messages: 6
Registered: November 2007
Junior Member
Here's how i know that the database is up:

[oracle@testhost admin]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.3.0 - Production on Mon Nov 26 04:32:26 2007

Copyright (c) 1982, 2006, Oracle. All Rights Reserved.


Connected to:
Oracle Database 10g Release 10.2.0.3.0 - Production

SQL> select count(1) from tab;

COUNT(1)
----------
3517
Re: Unable to connect using Net service name [message #283213 is a reply to message #283210] Mon, 26 November 2007 06:35 Go to previous messageGo to next message
caljedi
Messages: 6
Registered: November 2007
Junior Member
and here's the output from TNSPING:

tnsping testsid

TNS Ping Utility for Linux: Version 10.2.0.3.0 - Production on 26-NOV-2007 04:34:32

Copyright (c) 1997, 2006, Oracle. All rights reserved.

Used parameter files:
/opt/oracle/product/10gR2/network/admin/sqlnet.ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 1.2.3.4)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = testsid)))
OK (0 msec)
Re: Unable to connect using Net service name [message #283214 is a reply to message #283210] Mon, 26 November 2007 06:36 Go to previous messageGo to next message
Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE
Senior Member

Is there only one database is running ?
please first export ORACLE_SID which one your trying to connect.

consider:
$export ORACLE_SID=<SID>
$sqlplus / as sysdba
SQL> select status from v$instance;
Post here output 


Re: Unable to connect using Net service name [message #283221 is a reply to message #283214] Mon, 26 November 2007 06:45 Go to previous messageGo to next message
caljedi
Messages: 6
Registered: November 2007
Junior Member
Yes, only 1 db running on that box.

HEre's the output from the sql you sent:

SQL> select status from v$instance;

STATUS
------------
OPEN
Re: Unable to connect using Net service name [message #283223 is a reply to message #283221] Mon, 26 November 2007 06:49 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Is your local database the same than your "@SID"?

Regards
Michel
Re: Unable to connect using Net service name [message #283225 is a reply to message #283221] Mon, 26 November 2007 06:52 Go to previous messageGo to next message
Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE
Senior Member

Your net service name is "testsid.world" please try with this.
and also post your database and instance name.
select name from v$database;
select instance_name, status from v$instance;
Re: Unable to connect using Net service name [message #283228 is a reply to message #283225] Mon, 26 November 2007 06:58 Go to previous message
caljedi
Messages: 6
Registered: November 2007
Junior Member
==========================================================
SQL> select name from v$database;

NAME
---------
testsid

SQL>
==========================================================

SQL> select instance_name, status from v$instance;

INSTANCE_NAME STATUS
---------------- ------------
testsid OPEN
Previous Topic: Shrink Tablespace
Next Topic: Script dbstart
Goto Forum:
  


Current Time: Sat Dec 10 09:21:45 CST 2016

Total time taken to generate the page: 0.09450 seconds