Forum Discussion

kazeem_yusuf1's avatar
kazeem_yusuf1
Icon for Nimbostratus rankNimbostratus
Oct 10, 2017

TNS LISTENER ERROR ON ORACLE RAC VIRTUAL SERVER IRULE

What steps should be listed in the Oracle RAC irule, when load balancing oracle db servers. I get the following error on my VIP (Green) (10.1.232.89:1521). Connection test failed. Listener refused the connection with the following error: ORA-12516, TNS:listener could not find available handler with matching protocol stack The Connection descriptor used by the client was: (description= (address=(protocol=tcp)(host=10.1.232.89)(port=1521))(connect_data=(SERVICE_NAME=tpp_n4))) oracle.jdbc.driver.SQLStateMapping.newSQLException(SQLStateMapping.java:70) oracle.jdbc.driver.DatabaseError.newSQLException(DatabaseError.java:112) oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:173) oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:460) oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:411) oracle.jdbc.driver.PhysicalConnection.(PhysicalConnection.java:490) oracle.jdbc.driver.T4CConnection.(T4CConnection.java:202) oracle.jdbc.driver.T4CDriverExtension.getConnection(T4CDriverExtension.java:33) oracle.jdbc.driver.OracleDriver.connect(OracleDriver.java:474) com.bea.console.utils.jdbc.JDBCUtils.testConnection(JDBCUtils.java:505) com.bea.console.actions.jdbc.datasources.createjdbcdatasource.CreateJDBCDataSource.testConnectionConfiguration(CreateJDBCDataSource.java:369) sun.reflect.GeneratedMethodAccessor797.invoke(Unknown Source) sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25) java.lang.reflect.Method.invoke(Method.java:597) org.apache.beehive.netui.pageflow.FlowController.invokeActionMethod(FlowController.java:870) org.apache.beehive.netui.pageflow.FlowController.getActionMethodForward(FlowController.java:809) org.apache.beehive.netui.pageflow.FlowController.internalExecute(FlowController.java:478) org.apache.beehive.netui.pageflow.PageFlowController.internalExecute(PageFlowController.java:306) org.apache.beehive.netui.pageflow.FlowController.execute(FlowController.java:336) org.apache.beehive.netui.pageflow.internal.FlowControllerAction.execute(FlowControllerAction.java:52) ...

 

However,all the three DB servers connection test from Oracle Server are fine, 10.1.232.191,10.1.232.192,10.1.232.193.

 

The service name is tpp_n4, and the irule is in the comments

 

  • This is the irule in use for oracle virtual server.

     

    ---------------------------------------------------------------------------- The Initial Developer of the Original Code is F5 Networks, Inc. Seattle, WA, USA. Portions created by F5 are Copyright (C) 1996-2011 F5 Networks, Inc. All Rights Reserved. ---------------------------------------------------------------------------- Name: ora11gr2_service_switching_irule.tcl Author: Ryan Corder Description: Load-balance/redirect Oracle Net traffic to different pools depending on the SERVICE_NAME specified in the connect string with the optional ability to strip out the client-specified INSTANCE_NAME Resources: http://www.f5.com/pdf/deployment-guides/oracle-rac-database-dg.pdf http://devcentral.f5.com/wiki/default.aspx/iRules.TCP

    when CLIENT_ACCEPTED { set last_service_name "tpp_n4"

     

     

     

     Change to a non-zero number if your clients are specifying an
     INSTANCE_NAME in their connect stings and you wish to remove it.  This
     allows you to have clients connect through the BIG-IP without the need
     to worry which instance of your database the connection gets load-balanced
     to.  As a result, the individual nodes will not reject a connection
     because of a Instance Name mismatch.
    set remove_instance_name 0
    
     Map service names to the pool on which they run.
     Use lower case instance names since arrays are case-sensitive and we are
     converting everything to lower case when we do comparisons later on.
    array set switch_map {
        "tpp_n4"   "ORACLE_11G_PRIMARY"
    
    }
    TCP::collect

     

     

    }

     

    when CLIENT_DATA { if { [TCP::payload] contains "(DESCRIPTION=(CONNECT_DATA=" } { if { [TCP::payload] contains "(SERVICE_NAME=" } { Find and save the service name the user/client has provided. set service_name [string tolower [findstr [TCP::payload] "(SERVICE_NAME=" 14 ")"]]

     

     

     

             User supplied service name must exist in our map.
            if { [array names switch_map $service_name] ne "" } {
                 Make no node changes if we are seeing things like RESEND, etc.
                if { $service_name eq $last_service_name } {
                    log local0.debug "Saw same service name as last time: $service_name"
                }
                else {
                    pool $switch_map($service_name)
                    log local0.debug "Sending traffic to pool $switch_map($service_name) based on service name $service_name"
                    set last_service_name $service_name
                }
    
                if { [TCP::payload] contains "(INSTANCE_NAME=" } {
                     Change to a zero value in the RULE_INIT event in order to
                     NOT remove the Instance Name
                    if { $remove_instance_name != 0 } {
                        set instance_name [findstr [TCP::payload] "(INSTANCE_NAME=" 15 ")"]
                        set instance_match [regexp -all -inline -indices "\\(INSTANCE_NAME=$instance_name\\)" [TCP::payload]]
                        foreach instance $instance_match {
                            set instance_start [lindex $instance 0]
                            set instance_end [lindex $instance 1]
                            TCP::payload replace $instance_start [expr {$instance_end - $instance_start + 1}] ""
                            log local0.debug "Removing '(INSTANCE_NAME=$instance_name)' at byte $instance_start"
    
                            TCP::payload replace 0 2 [binary format S1 [TCP::payload length]]
                            log local0.debug "Rewriting TCP packet length ([TCP::payload length])"
    
                            set connect_data_length [string length [findstr [TCP::payload] "(DESCRIPTION=(CONNECT_DATA=" 0]]
                            TCP::payload replace 24 2 [binary format S1 $connect_data_length]
                            log local0.debug "Rewriting Oracle Net connect data length ($connect_data_length)"
                        }
                    }
                }
            }
            else {
                log local0.warn "Client specified SERVICE_NAME ($service_name) does not exist in switch map."
                 In the future, we could enforce which service names are and
                 are not allowed through the proxy.  In such a future, we would
                 need to respond here with a proper Oracle Net error packet.
                 For now, we are just going to log the fact that the user
                 specified instance name doesn't exist in our map and close
                 the connection
                TCP::close
                TCP::respond $oraclenet_error_packet
            }
        }
        else {
            log local0.debug "No SERVICE_NAME seen"
        }
    
        TCP::release
         If you don't want to process further packets, comment the
         TCP::collect statement out.  Remember though, if there is another
         connect event, for the duration of this connection, you wouldn't be
         able to act upon it.
        TCP::collect
    }
    else {
        log local0.debug "No CONNECT_DATA seen"
    
        TCP::release
         If you don't want to process further packets, comment the
         TCP::collect statement out.  Remember though, if there is another
         connect event, for the duration of this connection, you wouldn't be
         able to act upon it.
        TCP::collect
    }

     

     

    }

     

    when CLIENT_CLOSED { set last_service_name "" }