Using Windows Powershell with ORACLE on Windows

Oracle Powershell is a free shell from Microsoft for Windows. As a scripting fan I thought I would take a look at it, when I needed a script to start and stop all Oracle services on a test instance.

PowerShell provides access to WMI objects such as WMI:win32_servies and Services "cmdlets" for querying and manipulating Windows Services, among them New-Service, Set-Service, and Get-Service.

The following script lists all information of services

$colItems = get-wmiobject -query "select * from win32_service where name like 'Oracle%'"

foreach ($objItem in $colItems) {
write-host "Display Name: " $objItem.DisplayName
write-host "Accept Pause: " $objItem.AcceptPause
write-host "Accept Stop: " $objItem.AcceptStop
write-host "Caption: " $objItem.Caption
write-host "Checkpoint: " $objItem.CheckPoint
write-host "Creation Class Name: " $objItem.CreationClassName
write-host "Description: " $objItem.Description
write-host "Desktop Interact: " $objItem.DesktopInteract
write-host "Error Control: " $objItem.ErrorControl
write-host "Exit Code: " $objItem.ExitCode
write-host "InstallationDate: " $objItem.InstallDate
write-host "Name: " $objItem.Name
write-host "Path Name: " $objItem.PathName
write-host "Process ID: " $objItem.ProcessId
write-host "Service Specific Exit Code: " $objItem.ServiceSpecificExitCode
write-host "Service Type: " $objItem.ServiceType
write-host "Started: " $objItem.Started
write-host "Start Mode: " $objItem.StartMode
write-host "Start Name: " $objItem.StartName
write-host "State: " $objItem.State
write-host "Status: " $objItem.Status
write-host "System Creation Class Name: " $objItem.SystemCreationClassName
write-host "System Name: " $objItem.SystemName
write-host "Tag ID: " $objItem.TagId
write-host "Wait Hint: " $objItem.WaitHint
write-host

}

Starting and stopping dependent services was not as easy as I initially thought as there seemes to be no access to information about dependent services. (or?)

However the following script starts all services except any DBConsoles

$colItems = Get-Service -include "Oracle*" -exclude "*DBConsole"

write-host "Starting ORACLE Services"
foreach ($objItem in $colItems) {
if( $objItem.Status -eq "Running") {
write-host "Service " $objItem.DisplayName " already started"
} else {
write-host "Starting service " $objItem.DisplayName "."
Start-Service -displayName $objItem.DisplayName
}
}

Get-Service -include "Oracle*"

The script produces the folowing output when run:

PS C:\Documents and Settings\Administrator> & 'C:\Documents and Settings\Administrator\stoporacle.ps1'
Stopping ORACLE Services
Stopping service OracleASMService+ASM .
Stop-Service : Cannot stop service 'OracleASMService+ASM (OracleASMService+ASM)' because it has dependent services. It
can only be stopped if the Force flag is set.
At C:\Documents and Settings\Administrator\stoporacle.ps1:10 char:29
+ Stop-Service <<<< -displayName $objItem.DisplayName Stopping service OracleCSService . Stop-Service : Cannot stop service 'OracleCSService (OracleCSService)' because it has dependent services. It can only b e stopped if the Force flag is set. At C:\Documents and Settings\Administrator\stoporacle.ps1:10 char:29 + Stop-Service <<<< -displayName $objItem.DisplayName Stopping service OracleDBConsoleORCL . WARNING: Waiting for service 'OracleDBConsoleORCL (OracleDBConsoleORCL)' to finish stopping... WARNING: Waiting for service 'OracleDBConsoleORCL (OracleDBConsoleORCL)' to finish stopping... WARNING: Waiting for service 'OracleDBConsoleORCL (OracleDBConsoleORCL)' to finish stopping... WARNING: Waiting for service 'OracleDBConsoleORCL (OracleDBConsoleORCL)' to finish stopping... WARNING: Waiting for service 'OracleDBConsoleORCL (OracleDBConsoleORCL)' to finish stopping... WARNING: Waiting for service 'OracleDBConsoleORCL (OracleDBConsoleORCL)' to finish stopping... WARNING: Waiting for service 'OracleDBConsoleORCL (OracleDBConsoleORCL)' to finish stopping... WARNING: Waiting for service 'OracleDBConsoleORCL (OracleDBConsoleORCL)' to finish stopping... WARNING: Waiting for service 'OracleDBConsoleORCL (OracleDBConsoleORCL)' to finish stopping... WARNING: Waiting for service 'OracleDBConsoleORCL (OracleDBConsoleORCL)' to finish stopping... WARNING: Waiting for service 'OracleDBConsoleORCL (OracleDBConsoleORCL)' to finish stopping... WARNING: Waiting for service 'OracleDBConsoleORCL (OracleDBConsoleORCL)' to finish stopping... WARNING: Waiting for service 'OracleDBConsoleORCL (OracleDBConsoleORCL)' to finish stopping... WARNING: Waiting for service 'OracleDBConsoleORCL (OracleDBConsoleORCL)' to finish stopping... WARNING: Waiting for service 'OracleDBConsoleORCL (OracleDBConsoleORCL)' to finish stopping... WARNING: Waiting for service 'OracleDBConsoleORCL (OracleDBConsoleORCL)' to finish stopping... WARNING: Waiting for service 'OracleDBConsoleORCL (OracleDBConsoleORCL)' to finish stopping... Stopping service OracleOraDb10g_home1TNSListener . Stopping service OracleServiceORCL . WARNING: Waiting for service 'OracleServiceORCL (OracleServiceORCL)' to finish stopping... WARNING: Waiting for service 'OracleServiceORCL (OracleServiceORCL)' to finish stopping... WARNING: Waiting for service 'OracleServiceORCL (OracleServiceORCL)' to finish stopping... WARNING: Waiting for service 'OracleServiceORCL (OracleServiceORCL)' to finish stopping... WARNING: Waiting for service 'OracleServiceORCL (OracleServiceORCL)' to finish stopping... WARNING: Waiting for service 'OracleServiceORCL (OracleServiceORCL)' to finish stopping... WARNING: Waiting for service 'OracleServiceORCL (OracleServiceORCL)' to finish stopping... WARNING: Waiting for service 'OracleServiceORCL (OracleServiceORCL)' to finish stopping... Stopping service OracleASMService+ASM . WARNING: Waiting for service 'OracleASMService+ASM (OracleASMService+ASM)' to finish stopping... Stopping service OracleCSService . Stopping service OracleDBConsoleORCL . Stopping service OracleOraDb10g_home1TNSListener . Stopping service OracleServiceORCL . Status Name DisplayName ------ ---- ----------- Stopped OracleASMServic... OracleASMService+ASM Stopped OracleCSService OracleCSService Stopped OracleDBConsole... OracleDBConsoleORCL Stopped OracleJobSchedu... OracleJobSchedulerORCL Stopped OracleOraDb10g_... OracleOraDb10g_home1TNSListener Stopped OracleServiceORCL OracleServiceORCL The following script stops all services that has a name starting with "Oracle"

$colItems = Get-Service -include "Oracle*"

write-host "Stopping ORACLE Services"
foreach ($objItem in $colItems) {
if( $objItem.Status -eq "Stopped") {

} else {
write-host "Stopping service " $objItem.DisplayName "."
Stop-Service -displayName $objItem.DisplayName
}
}

foreach ($objItem in $colItems) {
if( $objItem.Status -eq "Stopped") {
} else {
write-host "Stopping service " $objItem.DisplayName "."
Stop-Service -displayName $objItem.DisplayName -force
}
}


Get-Service -include "Oracle*"


PS C:\Documents and Settings\Administrator> & 'C:\Documents and Settings\Administrator\startoracle.ps1'
Starting ORACLE Services
Starting service OracleASMService+ASM .
WARNING: Waiting for service 'OracleASMService+ASM (OracleASMService+ASM)' to finish starting...
WARNING: Waiting for service 'OracleASMService+ASM (OracleASMService+ASM)' to finish starting...
WARNING: Waiting for service 'OracleASMService+ASM (OracleASMService+ASM)' to finish starting...
WARNING: Waiting for service 'OracleASMService+ASM (OracleASMService+ASM)' to finish starting...
WARNING: Waiting for service 'OracleASMService+ASM (OracleASMService+ASM)' to finish starting...
WARNING: Waiting for service 'OracleASMService+ASM (OracleASMService+ASM)' to finish starting...
WARNING: Waiting for service 'OracleASMService+ASM (OracleASMService+ASM)' to finish starting...
Starting service OracleCSService .
Starting service OracleDBConsoleORCL .
WARNING: Waiting for service 'OracleDBConsoleORCL (OracleDBConsoleORCL)' to finish starting...
WARNING: Waiting for service 'OracleDBConsoleORCL (OracleDBConsoleORCL)' to finish starting...
WARNING: Waiting for service 'OracleDBConsoleORCL (OracleDBConsoleORCL)' to finish starting...
WARNING: Waiting for service 'OracleDBConsoleORCL (OracleDBConsoleORCL)' to finish starting...
WARNING: Waiting for service 'OracleDBConsoleORCL (OracleDBConsoleORCL)' to finish starting...
WARNING: Waiting for service 'OracleDBConsoleORCL (OracleDBConsoleORCL)' to finish starting...
WARNING: Waiting for service 'OracleDBConsoleORCL (OracleDBConsoleORCL)' to finish starting...
WARNING: Waiting for service 'OracleDBConsoleORCL (OracleDBConsoleORCL)' to finish starting...
WARNING: Waiting for service 'OracleDBConsoleORCL (OracleDBConsoleORCL)' to finish starting...
WARNING: Waiting for service 'OracleDBConsoleORCL (OracleDBConsoleORCL)' to finish starting...
WARNING: Waiting for service 'OracleDBConsoleORCL (OracleDBConsoleORCL)' to finish starting...
WARNING: Waiting for service 'OracleDBConsoleORCL (OracleDBConsoleORCL)' to finish starting...
WARNING: Waiting for service 'OracleDBConsoleORCL (OracleDBConsoleORCL)' to finish starting...
WARNING: Waiting for service 'OracleDBConsoleORCL (OracleDBConsoleORCL)' to finish starting...
WARNING: Waiting for service 'OracleDBConsoleORCL (OracleDBConsoleORCL)' to finish starting...
WARNING: Waiting for service 'OracleDBConsoleORCL (OracleDBConsoleORCL)' to finish starting...
WARNING: Waiting for service 'OracleDBConsoleORCL (OracleDBConsoleORCL)' to finish starting...
Starting service OracleJobSchedulerORCL .
Start-Service : Service 'OracleJobSchedulerORCL (OracleJobSchedulerORCL)' cannot be started due to the following error:
Cannot start service OracleJobSchedulerORCL on computer '.'.
At C:\Documents and Settings\Administrator\startoracle.ps1:10 char:30
+ Start-Service <<<< -displayName $objItem.DisplayName
Starting service OracleOraDb10g_home1TNSListener .
Starting service OracleServiceORCL .
WARNING: Waiting for service 'OracleServiceORCL (OracleServiceORCL)' to finish starting...
WARNING: Waiting for service 'OracleServiceORCL (OracleServiceORCL)' to finish starting...
WARNING: Waiting for service 'OracleServiceORCL (OracleServiceORCL)' to finish starting...
WARNING: Waiting for service 'OracleServiceORCL (OracleServiceORCL)' to finish starting...
WARNING: Waiting for service 'OracleServiceORCL (OracleServiceORCL)' to finish starting...
WARNING: Waiting for service 'OracleServiceORCL (OracleServiceORCL)' to finish starting...
WARNING: Waiting for service 'OracleServiceORCL (OracleServiceORCL)' to finish starting...
WARNING: Waiting for service 'OracleServiceORCL (OracleServiceORCL)' to finish starting...
WARNING: Waiting for service 'OracleServiceORCL (OracleServiceORCL)' to finish starting...
WARNING: Waiting for service 'OracleServiceORCL (OracleServiceORCL)' to finish starting...
WARNING: Waiting for service 'OracleServiceORCL (OracleServiceORCL)' to finish starting...
WARNING: Waiting for service 'OracleServiceORCL (OracleServiceORCL)' to finish starting...
WARNING: Waiting for service 'OracleServiceORCL (OracleServiceORCL)' to finish starting...
WARNING: Waiting for service 'OracleServiceORCL (OracleServiceORCL)' to finish starting...
WARNING: Waiting for service 'OracleServiceORCL (OracleServiceORCL)' to finish starting...
WARNING: Waiting for service 'OracleServiceORCL (OracleServiceORCL)' to finish starting...
WARNING: Waiting for service 'OracleServiceORCL (OracleServiceORCL)' to finish starting...
WARNING: Waiting for service 'OracleServiceORCL (OracleServiceORCL)' to finish starting...
WARNING: Waiting for service 'OracleServiceORCL (OracleServiceORCL)' to finish starting...
WARNING: Waiting for service 'OracleServiceORCL (OracleServiceORCL)' to finish starting...

Status Name DisplayName
------ ---- -----------
Running OracleASMServic... OracleASMService+ASM
Running OracleCSService OracleCSService
Running OracleDBConsole... OracleDBConsoleORCL
Stopped OracleJobSchedu... OracleJobSchedulerORCL
Running OracleOraDb10g_... OracleOraDb10g_home1TNSListener
Running OracleServiceORCL OracleServiceORCL

Sphere: Related Content