1 2 Previous Next 25 Replies Latest reply: Jul 25, 2010 8:00 PM by sb92075 RSS

    tns alias connection weirdness

    527623
      Oracle 8.1.5 (ewww - I know)

      I've got a single database on a server called PROD1. In the tnsnames.ora I have entries such as

      mydatabase =
      (DESCRIPTION =
      (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = host)(PORT = 1521))
      )
      (CONNECT_DATA =
      (SERVICE_NAME = PROD1)
      )
      )

      PROD1 =
      (DESCRIPTION =
      (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = host)(PORT = 1521))
      )
      (CONNECT_DATA =
      (SERVICE_NAME = PROD1)
      )
      )

      Basically, they're both pointing to the same database. When I set my ORACLE_SID to PROD1 and try to connect, everything connects fine. When I try to use a connect string of username/password@mydatabase, I keep getting an ORA-1034 error - oracle not available. A client trace log file shows trying to use the alias and it appears in the log that everything works fine. A lsnrctl service command keeps incrementing the established connections. Nothing in the listener log regarding a refused connection.

      Does anyone know what might be causing this?
        • 1. Re: tns alias connection weirdness
          Eduardo Legatti
          Hi,

          What Oracle Client version are you using?

          Cheers

          Legatti
          • 2. Re: tns alias connection weirdness
            Eduardo Legatti
            Hi,

            In addition, in order to make a test, see if the format below works ...
            mydatabase =
              (DESCRIPTION =
                (ADDRESS_LIST =
                  (ADDRESS = (PROTOCOL = TCP)(HOST = host)(PORT = 1521))
                )
                (CONNECT_DATA =
            (SID = PROD1)
                )
              )
            Cheers

            Legatti
            • 3. Re: tns alias connection weirdness
              527623
              Oracle client is 8.1.5 as well. I re-created the tnsnames.ora using the netca utility, and it uses service_name for 8.1.5. I made the change you suggested, and used SID instead, but still the same problem.
              • 4. Re: tns alias connection weirdness
                400137
                What happens when you execute the following commands from command line (can you include the output)?
                tnsping mydatabase
                tnsping prod1
                echo $ORACLE_SID
                (if OS is windows then echo %ORACLE_SID%)
                echo $ORACLE_HOME
                (if OS is windows then echo %ORACLE_HOME%)
                sqlplus username/password@mydatabase
                sqlplus username/password@prod1
                sqlplus username/password
                • 5. Re: tns alias connection weirdness
                  527623
                  tnsping works as expected for both mydatabase and PROD1.

                  ORACLE_SID=PROD1
                  ORACLE_HOME=/database/app/oracle/product/8.1.5

                  sqlplus username/password - works fine
                  sqlplus username/password@mydatabase - ORA-1034 oracle not available
                  sqlplus username/password@PROD1 - ORA-1034 oracle not available

                  It seems when you try to go through the listener, this is happening. I've re-linked everything to no avail.
                  • 6. Re: tns alias connection weirdness
                    RPuttagunta
                    sqlplus username/password - works fine
                    What do you mean? Where is this connecting to?
                    • 7. Re: tns alias connection weirdness
                      527623
                      I'm using sqlplus on the server itself. I was under the impression that if you're on the server, you connect using the bequeath connection unless you specify the @ sign in the connect string - therefore going through the listener.
                      • 8. Re: tns alias connection weirdness
                        400137
                        Are there any accompanying messages with ORA-01034
                        $ oerr ora 01034
                        01034, 00000, "ORACLE not available"
                        // *Cause: Oracle was not started up. Possible causes include the fol
                        //         - The SGA requires more space than was allocated for it.
                        //         - The operating-system variable pointing to the instance i
                        //           improperly defined.
                        // *Action: Refer to accompanying messages for possible causes and co
                        //          the problem mentioned in the other messages.
                        //          If Oracle has been initialized, then on some operating sy
                        //          verify that Oracle was linked correctly. See the platform
                        //          specific Oracle documentation.
                        • 9. Re: tns alias connection weirdness
                          348187
                          what's in your listener.ora? Is the oracle home correct for the database?
                          • 10. Re: tns alias connection weirdness
                            427367
                            Just as an aside, tnsping should probably only be used to check latencies (and it could be argued if it should in fact be used at all).

                            Tnsping returns immediately after it has gotten an initial response from the listener. It does not check if the service defined in the connect descriptor is available or even exists.

                            An example follows:
                            [ora111@lelux1 ~]$ tnsping rac11g
                            
                            TNS Ping Utility for Linux: Version 11.1.0.6.0 - Production on 11-JAN-2008 05:27:39
                            
                            Copyright (c) 1997, 2007, Oracle.  All rights reserved.
                            
                            Used parameter files:
                            
                            
                            Used TNSNAMES adapter to resolve the alias
                            Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = lelux1-vip)(PORT = 1521)) (ADDRESS = (PROTOCOL = TCP)(HOST = lelux2-vip)(PORT = 1521)) (LOAD_BALANCE = yes) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = rac11g)))
                            OK (10 msec)
                            [ora111@lelux1 ~]$ tnsping fake
                            
                            TNS Ping Utility for Linux: Version 11.1.0.6.0 - Production on 11-JAN-2008 05:27:34
                            
                            Copyright (c) 1997, 2007, Oracle.  All rights reserved.
                            
                            Used parameter files:
                            
                            
                            Used TNSNAMES adapter to resolve the alias
                            Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = lelux1-vip)(PORT = 1521)) (ADDRESS = (PROTOCOL = TCP)(HOST = lelux2-vip)(PORT = 1521)) (LOAD_BALANCE = yes) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = fake)))
                            OK (10 msec)
                            [ora111@lelux1 ~]$ sqlplus antti@rac11g
                            
                            SQL*Plus: Release 11.1.0.6.0 - Production on Fri Jan 11 05:20:05 2008
                            
                            Copyright (c) 1982, 2007, Oracle.  All rights reserved.
                            
                            Enter password:
                            
                            Connected to:
                            Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
                            With the Partitioning, Real Application Clusters and Real Application Testing options
                            
                            SQL> exit
                            Disconnected from Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
                            With the Partitioning, Real Application Clusters and Real Application Testing options
                            [ora111@lelux1 ~]$ sqlplus antti@fake
                            
                            SQL*Plus: Release 11.1.0.6.0 - Production on Fri Jan 11 05:20:40 2008
                            
                            Copyright (c) 1982, 2007, Oracle.  All rights reserved.
                            
                            Enter password:
                            ERROR:
                            ORA-12514: TNS:listener does not currently know of service requested in connect
                            descriptor
                            • 11. Re: tns alias connection weirdness
                              427367
                              What's you o/s version? I remember very vaguely having seen something similar on older aixen, and even more vaguely that TWO_TASK settings had something to do with it..
                              • 12. Re: tns alias connection weirdness
                                51034
                                When I set my ORACLE_SID to PROD1 and try to connect, everything connects fine.

                                Well, the fact that you're using ORACLE_SID means that must be a local connection on the server itself. I think you admit this later on.

                                When I try to use a connect string of username/password@mydatabase, I keep getting an ORA-1034 error - oracle not available

                                The big difference at this point is that you're going through the complete tns stack, client to listener, listener to instance, client to instance.

                                If it was a problem with your listener, you'd get 'no listener' or 'listener does not know of service requested' errors, so I don't think it's that.

                                What's your ORACLE_HOME set to? It needs to be correct, or you'll definitely get this 1034 error. Make sure O_H is not set to a symbolic link: it needs the fully-qualified path to the appropriate directory. Also make sure it doesn't end with a trailing "/" or "\". And if you're on Unix or Linux, watch out for case and slash direction!
                                • 13. Re: tns alias connection weirdness
                                  Pavan Kumar
                                  Hi,

                                  As per my knowledge Never go for "SERVICE_NAME" in your tnsnames.ora
                                  go for "SID"

                                  that is the actual way..the document says and prescribes.
                                  No Check everything works fine.

                                  Thanks
                                  Pavan Kumar N
                                  • 14. Re: tns alias connection weirdness
                                    Billy~Verreynne
                                    > tnsping works as expected for both mydatabase and PROD1.

                                    That means the listener responded. It does not in fact check the parameters of the TNS alias itself. The ping simply connects to the specified host on the specified IP port and sends a "ping" command (not to be confused with ICMP).

                                    > sqlplus username/password@mydatabase - ORA-1034 oracle not available
                                    sqlplus username/password@PROD1 - ORA-1034 oracle not available

                                    This seems like a Listener config error. In this case, the parameters of the TNS alias are applicable. The listener receives these and attempt to start a dedicated server process. It does that by executing an oracle process from the $ORACLE_HOME configuration for that service/SID and then handing the client connection over to that.

                                    It would seem that the oracle process (dedicated server to be) itself is successfully started by the Listener. However, it fails to attach itself to the SGA on that server and fails... typically the SGA does not exist when the instance was not started. But it could also be other issues like using an incorrect ORACLE_SID for referencing the SGA.

                                    What does the listener log say?
                                    1 2 Previous Next