Powershell – Oracle startup and shutdown with Powershell

oraclepowershell

I'm trying to automate the startup and shutdown of some Oracle instances with powershell.

The best I've come up with so far is below

param(
[String]$Instance = $(Throw 'Instance required' ),
[String]$Password = $(Throw 'Password required'),
[String]$ShutdownMode = 'IMMEDIATE'
)

$validShutdownModes = ('IMMEDIATE', 'NORMAL', 'ABORT')

if($validShutdownModes -notcontains $ShutdownMode)
{
    Throw 'Invalid ShutdownMode: [IMMEDIATE | NORMAL | ABORT]'
}

#Prepare the connection statement based on the Password and Instance name
$sqlConnect = 'connect sys/{0}@{1} as sysdba;' -f ($Password, $Instance)

#Prepare the shutdown statement based on the ShutdownMode
$sqlShutdown = 'shutdown {0};' -f ($ShutdownMode)

#Prepare the exit statement
$sqlExit = 'exit;'

#Get a temporary file for storing the SQLPLUS commands
$tmpFile = [System.IO.Path]::GetTempFileName()

#Write the commands to the file
Set-Content -path $tmpFile -value $sqlConnect
Add-Content -path $tmpFile -value $sqlShutdown
Add-Content -path $tmpFile -value $sqlExit

#Execute the commands
$output = &'sqlplus.exe' '/NOLOG' '@' $tmpFile

#Remove the temporary file
Remove-Item -path $tmpFile

#Dump the ouput of SQLPLUS to the console
$output

This works, but its not able to deal with anything unexpected happening and writing passwords into temporary files is far from ideal.

Is there any programmatic interface I can use to shutdown Oracle instances, or a better way of using SQLPLUS for this kind of task?

Any general criticisms of the powershell in general are also appreciated.

Thanks

Best Answer

I have no experience with PowerShell.

However, how about using ORADIM, the Oracle built-in Windows-specific command-line to start/stop databases? It can stop/start database instances and ASM instance.

Using it is much cleaner than your workaround :)

See Oracle Documentation for it here. It has startup and shutdown examples.