TNS LISTENER ERROR ON ORACLE RAC VIRTUAL SERVER IRULE

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