6 Replies Latest reply: Aug 16, 2013 12:46 PM by sb92075 RSS

    How to monitor what net_service_name (TNS alias) is being used.

    user538573

      This may sound like a newbie question but I am trying to determine which net_service_name (TNS alias) is being used for a particular database.  I thought I could look at the listener log to see how connecitons are being made but did not help.  Does anyone have an easy way to monitor this?

      Thanks.

      Linda

        • 1. Re: How to monitor what net_service_name (TNS alias) is being used.
          EdStevens

          user538573 wrote:

           

          This may sound like a newbie question but I am trying to determine which net_service_name (TNS alias) is being used for a particular database.  I thought I could look at the listener log to see how connecitons are being made but did not help.  Does anyone have an easy way to monitor this?

          Thanks.

          Linda

          No.

          The net service name in tnsnames.ora is just an alias to be resolved (by the tns entry) into a routable host/port/service name.  Trying to find out from the other end is akin to getting a call on your telephone and trying to determine how the caller had your number listed in whatever he uses for a telephone directory.

           

          Take these two tnsnames entries:

           

          kilroy =

            (DESCRIPTION =

              (ADDRESS_LIST =

                (ADDRESS = (PROTOCOL = TCP)(HOST = vbdwprod)(PORT = 1521))

              )

              (CONNECT_DATA =

                (SERVICE_NAME = orcl)

              )

            )

           

          fubar =

            (DESCRIPTION =

              (ADDRESS_LIST =

                (ADDRESS = (PROTOCOL = TCP)(HOST = vbdwprod)(PORT = 1521))

              )

              (CONNECT_DATA =

                (SERVICE_NAME = orcl)

              )

            )

           

          Notice that kilroy and fubar both point to the same service on the same host, through the same listener port.

           

          Nothing on the server is going to know if the client used kilroy or fubar.  And it really doesn't matter.  Some other client process may have used an entirely different method not even involving tnsnames.  The server doesn't give a flying fig.

           

          So, what problem are you really trying to solve?

          • 2. Re: How to monitor what net_service_name (TNS alias) is being used.
            user538573

            We are wanting to split an instance into two separate instances - long story short - we have an application that needs to remain on 10g other need to move to 11g.  We have an alias in the TNS Names that I believe several applications are using.  I am trying to identify which applications are using the alias.

            • 3. Re: How to monitor what net_service_name (TNS alias) is being used.
              sb92075

              user538573 wrote:

               

              We are wanting to split an instance into two separate instances - long story short - we have an application that needs to remain on 10g other need to move to 11g.  We have an alias in the TNS Names that I believe several applications are using.  I am trying to identify which applications are using the alias.

               

              The inspect the application source code.

               

              Oracle DB does not know or care about where, what or how any client connects to it.

              For example the DB can not differentiate between OCI, JDBC, ODBC, or any other "flavor" of network protocol.

              • 4. Re: How to monitor what net_service_name (TNS alias) is being used.
                Billy~Verreynne

                user538573 wrote:

                 

                This may sound like a newbie question but I am trying to determine which net_service_name (TNS alias) is being used for a particular database.  I thought I could look at the listener log to see how connecitons are being made but did not help.  Does anyone have an easy way to monitor this?

                 

                There is a method to determine which applications are using a specific TNS alias. But it is a bit of a hack.

                 

                The basic concept it to

                a) support a new network service in the database

                b) change the TNSNAMES.ORA alias to use this service

                c) using v$session and v$session_connect_info views, determine which sessions are using the new service

                 

                It should work on 10g (but I'm not going to test and confirm). It does however work on 11gr2 (using some of this approach in RAC).

                 

                The steps are as follows:

                 

                Update the SERVICE_NAMES parameter of the database and add a new service to it, and register this with the listener. E.g.

                SQL> show parameter service_names

                ..

                SQL> alter system set service_names='..existing entry.., newservice.mydomain.com' scope=both;

                ..

                SQL> alter system register;

                 

                If you now do a lsnrctl status, you should see something like the following, showing the new service:

                Services Summary...
                Service "newservice.mydomain.com" has 1 instance(s).
                  Instance "myorclsid", status READY, has 1 handler(s) for this service...

                 

                Change the client tnsnames.ora file, and point the TNS alias to this new service. The alias will have a CONNECT_DATA entry. E.g.

                    (CONNECT_DATA=
                      (SID=dev)
                      (SERVER=dedicated)
                    )

                 

                This connect data entry needs to be changed to using a service (if not already configured for that), and for using the new service created. E.g.

                    (CONNECT_DATA=
                      (SERVICE_NAME=newservice.mydomain.com)
                      (SERVER=dedicated)
                    )

                 

                Test this first with another new TNS alias to ensure it works - before changing the production TNS alias.

                 

                Okay, after confirmation and changing the production TNS alias, all new connections made by applications using that alias, will be connecting to the database using service newservice.mydomain.com.

                 

                Using virtual performance views such as v$session and v$session_connect_info will now enable you to determine which database sessions (servicing your production apps) are using the new service.


                Oh yes - this assumes dedicated server connections from the clients. If shared server is used, then the dispatchers need to be configured with the new service names. And I'm not sure whether multiple services can be supported by a dispatcher.

                • 5. Re: How to monitor what net_service_name (TNS alias) is being used.
                  rp0428

                  user538573 wrote:

                   

                  We are wanting to split an instance into two separate instances - long story short - we have an application that needs to remain on 10g other need to move to 11g.  We have an alias in the TNS Names that I believe several applications are using.  I am trying to identify which applications are using the alias.

                  What does 'other need to move to 11g' mean?

                   

                  And if you are 'wanting to split an instance' why wouldn't you just use two DIFFERENT tns names entries for them.

                   

                  Create a NEW tns names entry for the new instance. That way there can be NO possible confusion about which instance is being used.

                   

                  Based only on what you have posted so far it sounds like you are considering the WRONG solution for your problem.

                  • 6. Re: How to monitor what net_service_name (TNS alias) is being used.
                    sb92075

                    user538573 wrote:

                     

                    We are wanting to split an instance into two separate instances - long story short - we have an application that needs to remain on 10g other need to move to 11g.  We have an alias in the TNS Names that I believe several applications are using.  I am trying to identify which applications are using the alias.

                     

                    is application 3-tier like shown below?

                     

                     

                     

                    EndUser<=>browser<=>WebServer<=>ApplicationServer<=>DatabaseServer