Powershell | SQLKitten

Recently I have been tasked with taking over the database related tasks for an Oracle database that is the backend for a third party application. Two of the monthly processes require the all the user sessions connecting to the application be killed before hand. Straight forward enough…there are just a lot of sessions.

Last time I did this I had forgotten that I had this task and on the day of I was not prepared with any type of automation. As a result, I was killing off connections by hand. Yes, there was sadness with each arrow up and the changing of the SID and SERIAL# values.

Moving on, I knew I wanted to automate this with powershell so I did some searching and came across this article on querying Oracle with PS. I walked each of the segments, modifying the code for our environment and for my needs. Everything was going great…until I decided to run the code in production. There was more sadness –

ORA-12541 - TNS: no listener

1	ORA-12541 - TNS: no listener

Once I was done cursing and swearing I started to research the error. Our pre-prod and production environments should be the same. After some digging I found some small differences in the listener.ora files and thought this might be the clue I was looking for.

Cut to Monday moring – a quick chat with our Oracle person (read: the guy on our team that has Oracle training…training that I do not have) brings me my solution – something far more simple than I had imagined. Since the production server was set up with a different port than the one Oracle defaults to all I needed to do was include that with the server reference in my connection string. Duh!

I began testing in the UAT environment and uncovered one small issue – a semi-colon in my kill statement that was not needed and causing my code to fail. Once that was removed everything looked good with the exception of getting error messages when trying to kill a session related to a system process (there weren’t that many sessions to begin with here, not to mention, it didn’t matter if I broke anything on this server).

&lt;#-------------------------------------------------------------------------------------------------#&gt;
&lt;#---kill_users_sessions.ps1 - kill specific user sessions in a Oracle database--------------------#&gt;
&lt;#-------------------------------------------------------------------------------------------------#&gt;
#update path to location of Oracle.DataAccess.dll
Import-Module &#039;&lt;path&gt;\Oracle.DataAccess.dll&#039;

$connection = New-Object Oracle.DataAccess.Client.OracleConnection(&quot;Data Source=SERVERNAME:1234/DatabaseName; User Id=USERNAME; password=PASSWORD123&quot;)

$cmd = $connection.createcommand();

$killcmd = $connection.createcommand();

#Generate the kill session statements
&lt;#-------------------------------------------------------------------------------------#&gt;
&lt;#-------------------------------------------------------------------------------------#&gt;
&lt;#-------YOU WILL NEED TO ADD YOUR OWN CRITERIA TO THE WHERE STATEMENT!!!!! -----------#&gt;
&lt;#-------------------------------------------------------------------------------------#&gt;
&lt;#-------------------------------------------------------------------------------------#&gt;
$cmd.CommandText= @&quot;
with resultset (kill) as (
	select &#039;ALTER SYSTEM KILL SESSION &#039;&#039;&#039;||to_char(s.sid)||&#039;,&#039;||to_char(s.serial#)||&#039;&#039;&#039; IMMEDIATE&#039; as kill
	from gv`$session s
	where ??????????????????&#039;
)
select kill from resultset
&quot;@

$da = New-Object Oracle.DataAccess.Client.OracleDataAdapter($cmd);

$resultSet = New-Object System.Data.DataTable

[void]$da.fill($resultSet)

$connection.open()

foreach($item in $resultSet)
{
	Write-Host $item.KILL
	#----------------execute kill session statements that are in the resultset---------------
	$killcmd.CommandText=$item.KILL
	$killcmd.ExecuteNonQuery();
}

$connection.close()
$connection.dispose()

<#-------------------------------------------------------------------------------------------------#>

<#---kill_users_sessions.ps1 - kill specific user sessions in a Oracle database--------------------#>

<#-------------------------------------------------------------------------------------------------#>

#update path to location of Oracle.DataAccess.dll

Import-Module '<path>\Oracle.DataAccess.dll'

$connection = New-Object Oracle.DataAccess.Client.OracleConnection("Data Source=SERVERNAME:1234/DatabaseName; User Id=USERNAME; password=PASSWORD123")

$cmd = $connection.createcommand();

$killcmd = $connection.createcommand();

#Generate the kill session statements

<#-------------------------------------------------------------------------------------#>

<#-------YOU WILL NEED TO ADD YOUR OWN CRITERIA TO THE WHERE STATEMENT!!!!! -----------#>

<#-------------------------------------------------------------------------------------#>

$cmd.CommandText= @"

with resultset (kill) as (

select 'ALTER SYSTEM KILL SESSION '''||to_char(s.sid)||','||to_char(s.serial#)||''' IMMEDIATE' as kill

from gv`$session s

where ??????????????????'

)

select kill from resultset

$da = New-Object Oracle.DataAccess.Client.OracleDataAdapter($cmd);

$resultSet = New-Object System.Data.DataTable

[void]$da.fill($resultSet)

$connection.open()

foreach($item in $resultSet)

{

Write-Host $item.KILL

#----------------execute kill session statements that are in the resultset---------------

$killcmd.CommandText=$item.KILL

$killcmd.ExecuteNonQuery();

}

$connection.close()

$connection.dispose()

Feedback is welcome. Hoping you find this helpful. 🙂

While I am not new to the world of coding (and not just T-SQL) I am somewhat new to Powershell. I cobbled together a monitor process that works with GoldenGate a while back and just recently made it less ugly and improved its performance. Beyond that, I am still on the green side.

Enter my current project – automating the SQL portion of our deployments using Powershell. Before this I was taking the list of items to be deployed and generating my own scripts to run using the Red Gate SQL Compare tool. Then came the day when I called one of the scripts from a developer in Powershell. There was no going back. I was hooked. I immediately saw how amazing this could be. And also how my work load would drop.

While I am doing my part in Powershell, there is also some other development going on with the end the developers use – in other words, things are in flux. I have code for the old way they do things and the new way. The new is better in that some of the things I need are built-in to certain aspects so I am able to grab pieces of information and get the things from that I need. With the old, this is not the case. Instead I am passing that information in through parameters.

I had the idea of passing in strings that would then be broken up and inserted into a multidimensional array. With that, I could loop through the array one element at a time, have all the things I needed for each deployment and just sit back and watch the magic happen.

Uhhhhh…did I mention I was green in Powershell?

I had another array already going so I thought I had this. I don’t know if it was because it was late in the day, or if I was a little too confident in my array abilities, but the darn thing was not happening the way I had envisioned. I set the code aside to revisit later with a fresh pair of eyes and a brain that wasn’t so full.

Fast forward to the following week – went back to the same code and FIGURED OUT HOW TO DO IT IN 5 MINUTES. Not only that, but it was far less code than I had previously attempted before.

Here is how I have handled this. Obviously, I would be replacing the text in the strings with information that is relevant to my deployments but the concept is the same. Once I have the array created, I have all the elements I need and it is just a matter of knowing where they are in the array and accessing them.
$strings = @("The cat in the" ,"hat ate a rat" ,"and then sat and" ,"got fat from the rat")


$array = New-Object System.Collections.ArrayList

$array.Clear();
foreach($item in $strings)

{

	$array.Add(@($item.Split(" "))) | out-null;

}

for ($i = 0;$i -lt ($array | Measure-Object).count;$i++) { Write-Host $array[$i][0] }

For the sample I am writing the first element. For my project the first element might be the server name for the deployment or some other relevant piece of information and it would be in the same place throughout the array. Since variables and their contents persist in Powershell I added the statement to clear the array before it is loaded. I am also wrapping the code up in a function to keep things tidy.

If you find this useful let me know. Enjoy. 🙂

Category Archives: Powershell

Kill Oracle Sessions With PoSH

Multidimensional Array from Array Data in Parameter

Recent Posts

TexasAmy

Recent Comments

Archives

Categories

Meta