1 2 3 Previous Next 79 Replies Latest reply: Mar 13, 2013 10:36 AM by EdStevens RSS

    Cant connect my database using net service_name

    972355
      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
          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
            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
              "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
                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
                  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.
                    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
                      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
                        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
                          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
                            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
                              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
                                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
                                  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
                                    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 Previous Next