Learn F5 Technologies, Get Answers & Share Community Solutions Join DevCentral

Filter by:
  • Solution
  • Technology
Clear all filters
Answers

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

0
Rate this Question
Comments on this Question
Comment made 10-Oct-2017 by kazeem yusuf 212

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 r.corder@f5.com

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 "" }

0

Answers to this Question