This discussion is archived
1 2 3 6 Previous Next 79 Replies Latest reply: Mar 13, 2013 8:36 AM by EdStevens RSS

Cant connect my database using net service_name

972355 Newbie
Currently Being Moderated
Good Morning to all;

I trying to connect my database using net service_name , but i am getting error.
I have tnsnames.ora entry and listener.ora entry.
Please help me to crack this error.



SQL> select name from v$database;

NAME

ORCLTEST


SQL> grant connect ,resource to u1 identified by u1;
Grant succeeded.

SQL> grant sysdba to u1;
Grant succeeded.

SQL> show parameter pass;

NAME TYPE VALUE
remote_login_passwordfile string EXCLUSIVE

SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.


From DBS Directory :

[oracle@linuxserver dbs]$ orapwd file=orapworcltest password=asdf1234 entries=3


export ORACLE_SID=orcltest
[oracle@linuxserver ~]$ sqlplus /nolog

SQL*Plus: Release 10.2.0.1.0 - Production on Sat Mar 9 19:24:22 2013

Copyright (c) 1982, 2005, Oracle. All rights reserved.

ERROR when trying to connect

SQL> conn u1/u1@ORCLMYTEST
ERROR:
ORA-01034: ORACLE not available
ORA-27101: shared memory realm does not exist
Linux Error: 2: No such file or directory


SQL> conn u1/asdf1234@ORCLMYTEST
ERROR:
ORA-01034: ORACLE not available
ORA-27101: shared memory realm does not exist
Linux Error: 2: No such file or directory


Thanks in advance ..
  • 1. Re: Cant connect my database using net service_name
    972355 Newbie
    Currently Being Moderated
    Some additional information

    TNSNAMES.ora FILE Entry

    # tnsnames.ora Network Configuration File: /u01/app/oracle/product/10.2.0/db_1/network/admin/tnsnames.ora
    # Generated by Oracle configuration tools.

    ORCLMYTEST =
    (DESCRIPTION =
    (ADDRESS_LIST =
    (ADDRESS = (PROTOCOL = TCP)(HOST = LINUXSERVER)(PORT = 1521))
    )
    (CONNECT_DATA =
    (SERVER = DEDICATED)
    (SERVICE_NAME = MYDB.TEST.ORCLTEST.COM)
    )
    )

    ASDF =
    (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = linuxserver)(PORT = 1521))
    (CONNECT_DATA =
    (SERVER = DEDICATED)
    (SERVICE_NAME = asdf)
    )
    )

    LISTENER.ORA FILE Entry

    LISTENER_ORCL =
    (DESCRIPTION_LIST =
    (DESCRIPTION =
    (ADDRESS = (PROTOCOL =TCP)(HOST = linuxserver)(PORT = 1521))
    )
    )
    SID_LIST_LISTENER_ORCL =
    (SID_LIST =
    (SID_DESC =
    (SID_NAME = orcltest)
    (ORACLE_HOME = /u01/app/oracle/product/10.2.0/db_1)
    (GLOBAL_DBNAME = MYDB.TEST.ORCLTEST.COM)
    )
    (SID_DESC =
    (SID_NAME = orclprod)
    (ORACLE_HOME = /u01/app/oracle/product/10.2.0/db_1)
    (GLOBAL_DBNAME = MYDB.PROD.ORCLPROD.COM)
    )
    )
  • 2. Re: Cant connect my database using net service_name
    Mark D Powell Guru
    Currently Being Moderated
    Are you on the database server? Is so, lose the @ORCLMYTEST. "connect / as sysdba".

    HTH -- Mark D Powell --
  • 3. Re: Cant connect my database using net service_name
    972355 Newbie
    Currently Being Moderated
    "connect / as sysdba".

    - this works well.

    Could you please help me ?

    I want to connect my database using net servvice_name.

    Thanks ..
  • 4. Re: Cant connect my database using net service_name
    972355 Newbie
    Currently Being Moderated
    Are you on the database server? Is so, lose the @ORCLMYTEST.

    Yes. This is cllient/database is same host.

    Can i connect  my database using username/password@netservice_name ?
  • 5. Re: Cant connect my database using net service_name
    TSharma-Oracle Guru
    Currently Being Moderated
    Can i connect my database using username/password@netservice_name ?
    Yes, you can. This seems to be problem in tnsnames.ora entry. LOgin '/ as sysdba' and post the result of

    show parameter service_names
  • 6. Re: Cant connect my database using net service_name
    Paul M. Oracle ACE
    Currently Being Moderated
    Can i connect  my database using username/password@netservice_name ?
    Yes, you should. Please post the result of "lsnrctl stat LISTENER_ORCL".
  • 7. Re: Cant connect my database using net service_name
    Mark D Powell Guru
    Currently Being Moderated
    If you want to connect using a net service name you would use sqlplus username@tns_alias which would connect you as the normal username. If you want to connect as a sysdba then you do not need a service name when local. Instead of slash you can use your sysdba authorized username in which case the password can be garbage as Oracle ignores it since you are local and authorized via your OS group membership.

    Since you have a password file you have authorized remote sysdba connections. I always mess up the syntax for that so since I do not have an Oracle instance available to me I will leave posting a valid remote sysdba connect string to someone else.

    HTH -- Mark D Powell --
  • 8. Re: Cant connect my database using net service_name
    972355 Newbie
    Currently Being Moderated
    Can i connect my database using username/password@netservice_name ?

    Yes, you can. This seems to be problem in tnsnames.ora entry. LOgin '/ as sysdba' and post the result of

    show parameter service_names

    NAME TYPE VALUE

    service_names string orcltest
  • 9. Re: Cant connect my database using net service_name
    972355 Newbie
    Currently Being Moderated
    Can i connect my database using username/password@netservice_name ?

    Yes, you should. Please post the result of "lsnrctl stat LISTENER_ORCL".

    *[oracle@linuxserver admin]$ lsnrctl stat LISTENER_ORCL*

    LSNRCTL for Linux: Version 10.2.0.1.0 - Production on 09-MAR-2013 19:58:18

    Copyright (c) 1991, 2005, Oracle. All rights reserved.

    Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=linuxserver)(PORT=1521)))

    STATUS of the LISTENER

    Alias LISTener_ORcl
    Version TNSLSNR for Linux: Version 10.2.0.1.0 - Production
    Start Date 09-MAR-2013 11:10:14
    Uptime 0 days 8 hr. 48 min. 3 sec
    Trace Level off
    Security ON: Local OS Authentication
    SNMP OFF
    Listener Parameter File /u01/app/oracle/product/10.2.0/db_1/network/admin/listener.ora
    Listener Log File /u01/app/oracle/product/10.2.0/db_1/network/log/listener_orcl.log
    Listening Endpoints Summary...
    (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=linuxserver)(PORT=1521)))
    Services Summary...
    Service "MYDB.PROD.ORCLPROD.COM" has 1 instance(s).
    Instance "orclprod", status UNKNOWN, has 1 handler(s) for this service...
    Service "MYDB.TEST.ORCLTEST.COM" has 1 instance(s).
    Instance "orcltest", status UNKNOWN, has 1 handler(s) for this service...
    Service "asdf" has 1 instance(s).
    Instance "asdf", status READY, has 1 handler(s) for this service...
    Service "asdfXDB" has 1 instance(s).
    Instance "asdf", status READY, has 1 handler(s) for this service...
    Service "asdf_XPT" has 1 instance(s).
    Instance "asdf", status READY, has 1 handler(s) for this service...
    Service "orclprod" has 1 instance(s).
    Instance "orclprod", status READY, has 1 handler(s) for this service...
    Service "orclprodXDB" has 1 instance(s).
    Instance "orclprod", status READY, has 1 handler(s) for this service...
    Service "orclprod_XPT" has 1 instance(s).
    Instance "orclprod", status READY, has 1 handler(s) for this service...
    Service "orcltest" has 1 instance(s).
    Instance "orcltest", status READY, has 1 handler(s) for this service...
    Service "orcltestXDB" has 1 instance(s).
    Instance "orcltest", status READY, has 1 handler(s) for this service...
    Service "orcltest_XPT" has 1 instance(s).
    Instance "orcltest", status READY, has 1 handler(s) for this service...
    The command completed successfully
  • 10. Re: Cant connect my database using net service_name
    TSharma-Oracle Guru
    Currently Being Moderated
    Change your service_name to orcltest in

    ORCLMYTEST =
    (DESCRIPTION =
    (ADDRESS_LIST =
    (ADDRESS = (PROTOCOL = TCP)(HOST = LINUXSERVER)(PORT = 1521))
    )
    (CONNECT_DATA =
    (SERVER = DEDICATED)
    (SERVICE_NAME = MYDB.TEST.ORCLTEST.COM)
    )
    )

    Change MYDB.TEST.ORCLTEST.COM to orcltest
    then
    reload lsnrctl
    You should be good after that.
  • 11. Re: Cant connect my database using net service_name
    972355 Newbie
    Currently Being Moderated
    SQL> conn u1/u1@ORCLMYTEST
    ERROR:
    ORA-12514: TNS:listener does not currently know of service requested in connect
    descriptor

    Warning: You are no longer connected to ORACLE.
    SQL> conn u1/u1@ORCLMYTEST
    ERROR:
    ORA-12514: TNS:listener does not currently know of service requested in connect
    descriptor

    I changed entries in tnsnames.ora and listener.ora file. but i am getting error.
  • 12. Re: Cant connect my database using net service_name
    972355 Newbie
    Currently Being Moderated
    LISTENER.ORA

    LISTENER_ORCL =
    (DESCRIPTION_LIST =
    (DESCRIPTION =
    (ADDRESS = (PROTOCOL =TCP)(HOST = linuxserver)(PORT = 1521))
    )
    )
    SID_LIST_LISTENER_ORCL =
    (SID_LIST =
    (SID_DESC =
    (SID_NAME = orcltest)
    (ORACLE_HOME = /u01/app/oracle/product/10.2.0/db_1)
    (GLOBAL_DBNAME = orcltest)
    )
    (SID_DESC =
    (SID_NAME = orclprod)
    (ORACLE_HOME = /u01/app/oracle/product/10.2.0/db_1)
    (GLOBAL_DBNAME = MYDB.PROD.ORCLPROD.COM)
    )

    TNSNAMES.ORA

    ORCLMYTEST =
    (DESCRIPTION =
    (ADDRESS_LIST =
    (ADDRESS = (PROTOCOL = TCP)(HOST = LINUXSERVER)(PORT = 1521))
    )
    (CONNECT_DATA =
    (SERVER = DEDICATED)
    (SERVICE_NAME = orcltest)
    )
    )

    ASDF =
    (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = linuxserver)(PORT = 1521))
    (CONNECT_DATA =
    (SERVER = DEDICATED)
    (SERVICE_NAME = asdf)
    )
    )

    Thanks  T.sharma ..
  • 13. Re: Cant connect my database using net service_name
    TSharma-Oracle Guru
    Currently Being Moderated
    Check the the global name of your orcltest instance and put the right global name in listener.ora. Also post lsnrctl services output again please.

    select * from global_name;

    Edited by: TSharma on Mar 9, 2013 9:57 AM
  • 14. Re: Cant connect my database using net service_name
    972355 Newbie
    Currently Being Moderated
    I am getting error.

    SQL> conn u1/u1@ORCLMYTEST
    ERROR:
    ORA-12514: TNS:listener does not currently know of service requested in connect descriptor


    *$ lsnrctl reload*

    LSNRCTL for Linux: Version 10.2.0.1.0 - Production on 09-MAR-2013 20:23:32

    Copyright (c) 1991, 2005, Oracle. All rights reserved.

    Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
    The command completed successfully
1 2 3 6 Previous Next

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points