Forum Discussion

Kris__109062's avatar
Kris__109062
Icon for Nimbostratus rankNimbostratus
Sep 09, 2013

Replace SID to SERVICE_NAME in Oracle connnection string

Hi,

I am trying to replace SID with SERVICE_NAME in an Oracle connection string using an irule on an F5 Oracle VS. I have used as a starting point the irule documented here - https://devcentral.f5.com/articles/oracle-rac-connection-string-rewrite - I am basically doing the reverse. ie SID to SERVICE_NAME not SERVICE_NAME to SID

My setup..

A VS with a pool configured, lets call it "default_pool" - this pool should be used if no SID replacement needs to occur. Then, I have another pool not configured to any VS, lets call it "other_pool" - this pool should be used if the SID is replaced with SERVICE_NAME.

The problem I am facing is that when replacing the SID with SERVICE_NAME in the TCP::payload it overwrites any trailing parts of the Oracle Connection string.

eg.

Original connecting string from application to the VS

(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.11.110.10)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SID=MYSID)(CID=(PROGRAM=sqlplus)(HOST=xxxxxxx)(USER=xxxxxx))))

Replacement connection string after irule manipulation

(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.11.110.10)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=MYSERVICENAME)(HOST=xxxxxxx)(USER=xxxxxx))))

As you can see from above the irule below has replaced the SID but has not preserved the trailing data entirely, ie. (CID=(PROGRAM=sqlplus) has been overwritten.

My question is how can I replace SID=MYSID, with SERVICE_NAME=MYSERVICENAME whilst preserving the trailing connection data and then send the connection onto "other_pool" given the application connect string mentioned above and the irule below?

when CLIENT_ACCEPTED {
    TCP::collect
}

when CLIENT_DATA {    
    if { [TCP::payload] contains "(CONNECT_DATA=" } {
set sid_match ""
log local0. "Have access to TCP::Payload"    
set sid_match [regexp -all -inline -indices "\(SID=MYSID\)" [TCP::payload]]    
log local0. "Found a sid_match = $sid_match"  
set service_name "SERVICE_NAME=MYSERVICENAME"

set tmp [lindex $sid_match 1]    
set newservice [list $tmp]   

foreach instance $newservice {   
log local0. "Iterating through connect strings in the payload.  Raw:  $instance"   
set sid_start [lindex $instance 0]   

set original_tcp_length [TCP::payload length]   
TCP::payload replace $sid_start 34 $service_name   
log local0. "Inserted Servicename at $sid_start offset."   

TCP::payload replace 0 2 [binary format S1 [TCP::payload length]]   
log local0. "Updated packet with new length: [TCP::payload length] - original $original_tcp_length"   

set looking_for_connect [findstr [TCP::payload] "(DESCRIPTION" 0]   
log local0. "Looking for connect:  $looking_for_connect"   
set connect_data_length [string length [findstr [TCP::payload] "(DESCRIPTION" 0]]   
TCP::payload replace 24 2 [binary format S1 $connect_data_length]   
log local0. "New Oracle data length is $connect_data_length"   

}   
}   
if { [TCP::payload] contains "(CONNECT_DATA=" } {   
set looking_for_connect [findstr [TCP::payload] "(DESCRIPTION" 0]   
log local0. "2. Looking for connect:  $looking_for_connect"   
}   

TCP::release   
TCP::collect   
if { $sid_match != "" } {
pool other_pool
} else {
do nothing further - found no SID match - use the VS default_pool
log local0 "No match - use the VS default_pool"
}
}
  • Can you try regsub instead of regexp?

     http://www.tcl.tk/man/tcl8.4/TclCmd/regsub.htm
    regsub -all {(SID=MYSID)} [TCP::payload] SERVICE_NAME=MYSERVICENAME $new_payload
    TCP::payload replace 0 [TCP::payload length] $new_payload
    

    Aaron

  • I didn't test this against an Oracle environment, but the following should get you closer:

    when CLIENT_ACCEPTED {
        TCP::collect
    }
    when CLIENT_DATA {
        set sid_match 0
        if { [TCP::payload] contains "CONNECT_DATA=" } {
            set sid_match 1
            log local0. "original payload = [TCP::payload]"
            set service_name "SERVICE_NAME=MYSERVICENAME"
            if { [regsub -all -nocase "SID=MYSID" [TCP::payload] "$service_name" newdata] } {
                TCP::payload replace 0 [TCP::payload length] ""
                TCP::payload replace 0 0 $newdata
                log local0. "replaced payload = $newdata"
            }
        }
        TCP::release   
        TCP::collect
    }
    

    You'll need to re-add your pool logic, but you shouldn't have to do any of the payload length manipulation. Output from test using an HTTP POST:

    original payload = (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.11.110.10)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SID=MYSID)(CID=(PROGRAM=sqlplus)(HOST=xxxxxxx)(USER=xxxxxx))))
    replaced payload = (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.11.110.10)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=MYSERVICENAME)(CID=(PROGRAM=sqlplus)(HOST=xxxxxxx)(USER=xxxxxx))))
    
  • I'm getting closer in that the payload is succesfully updated and the correct pool is chosen and when I run a tcpdump on the pool member from "other_pool" I can see the updated payload is being received and it replies but for some reason I'm getting a connection closed error from the client (sqlplus) when testing. I can also see from a tcpdump on the client that replies are coming in correctly so it's not a route/SNAT issue.

    ERROR: ORA-12537: TNS:connection closed

    when CLIENT_ACCEPTED {
        log local0. "in client_accepted event"
        TCP::collect
    }
    when CLIENT_DATA {
        log local0. "in client_data event"
        set sid_match 0
        log local0. "orig payload length = [TCP::payload length]"
        if { [TCP::payload] contains "CONNECT_DATA=" } {
            log local0. "original payload = [TCP::payload]"
            set service_name "SERVICE_NAME=MY_SERVICE_NAME"
            if { [regsub -all -nocase "SID=MYSID" [TCP::payload] "$service_name" newdata] } {
                log local0. "found a SID match in tcp payload - replacing with $service_name"
                TCP::payload replace 0 [TCP::payload length] ""
                TCP::payload replace 0 0 $newdata
                log local0. "replaced payload = $newdata"
       set sid_match 1
            }
            log local0. "new payload length = [TCP::payload length]"
            if { $sid_match != 0 } {
                 log local0. "sending to pool other_pool"
                 LB::detach
        pool other_pool
                 TCP::release 
            } else {
        do nothing further - found no SID match - use the VS default_pool
        log local0. "No SID match - use the VS default_pool"
                 TCP::release 
            }
        }
    
        TCP::release
        TCP::collect
    }
    
    when LB_SELECTED {
        log local0. "selected pool member [LB::server addr]"
    }
    
  • Can you open up the tcpdump to look at the complete transaction? When I tested with a cURL client and a POST request, the first packet contained the headers so they didn't match the criteria and went to the default pool. I assumed, because of the way you had written the previous version, that the sqlplus client didn't have that problem. So please look to see if ALL of the data from the client is being directed to the same pool (and not just the payload).