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