JS.OracleDatabase v0.2.2.3 is out of the oven!

Hello fellow technocrats, automation lovers and PowerShell enthusiasts!

Only a few days go I uploaded to the PowerShell Gallery Site the new alpha version of my Oracle Database Client-based Module.

Here’s a summary of the functions you can find in it.

PS > Get-Command -Module JS.OracleDatabase

CommandType Name                         Version Source 
----------- ----                         ------- ------ 
Function    Add-OracleDBLink    JS.OracleDatabase
Function    Get-OracleADDMInstanceReport JS.OracleDatabase
Function    Get-OracleAWRInstanceReport JS.OracleDatabase
Function    Get-OracleAWRReport JS.OracleDatabase
Function    Get-OracleDBID      JS.OracleDatabase
Function    Get-OracleDBInfo    JS.OracleDatabase
Function    Get-OracleDBVersion JS.OracleDatabase
Function    Get-OracleHosts     JS.OracleDatabase
Function    Get-OracleInstances JS.OracleDatabase
Function    Get-OracleLongOperations JS.OracleDatabase
Function    Get-OracleLongRunQueries JS.OracleDatabase
Function    Get-OracleNames     JS.OracleDatabase
Function    Get-OracleOptions   JS.OracleDatabase
Function    Get-OraclePerfReports JS.OracleDatabase
Function    Get-OracleServices  JS.OracleDatabase
Function    Get-OracleSessions  JS.OracleDatabase
Function    Get-OracleSize      JS.OracleDatabase
Function    Get-OracleSnapshot  JS.OracleDatabase
Function    Get-OracleSnapshotTime JS.OracleDatabase
Function    Get-OracleSQLText   JS.OracleDatabase
Function    Get-OracleUsers     JS.OracleDatabase
Function    Ping-OracleDB       JS.OracleDatabase
Function    Remove-OracleDBLink JS.OracleDatabase
Function    Remove-OracleSchema JS.OracleDatabase
Function    Test-OracleDBLink   JS.OracleDatabase
Function    Test-OracleEnv      JS.OracleDatabase
Function    Test-OracleHealth   JS.OracleDatabase
Function    Use-OracleDB        JS.OracleDatabase

Most of the functions rely on Use-OracleDB function which allows you to run any query you want and outputs the result set as objects.

Let’s say you want to know the version of the database.

You could easily run the following command:

PS-Dev > Use-OracleDB -TargetDB myorcldb -SQLQuery "SELECT * FROM v`$version;"

DBName   BANNER                                                                        CON_ID ErrorMsg
------   ------                                                                       ------ --------
myorcldb Oracle Database 12c Enterprise Edition Release - 64bit Production      0 
myorcldb PL/SQL Release - Production                                            0 
myorcldb CORE Production                                                        0 
myorcldb TNS for Linux: Version - Production                                    0 
myorcldb NLSRTL Version - Production                                            0

You could then think about giving your output a more interesting format

PS-Dev > Use-OracleDB -TargetDB myorcldb -SQLQuery "SELECT banner AS `"ComponentName`" FROM v`$version;"

DBName   ComponentName                                                                ErrorMsg
------   -------------                                                                --------
myorcldb Oracle Database 12c Enterprise Edition Release - 64bit Production 
myorcldb PL/SQL Release - Production 
myorcldb CORE Production 
myorcldb TNS for Linux: Version - Production 
myorcldb NLSRTL Version - Production

You can see that the ErrorMsg attribute is present everytime, providing additional information in case there’s an error during running time.

PS-Dev > Use-OracleDB -TargetDB myorcldb -SQLQuery "SELECT banner AS `"ComponentName`" FROM v`$version_bad;"

DBName   ErrorMsg 
------   -------- 
myorcldb ORA-00942: table or view does not exist

Feel free to use it and ask me here or reach me at my LinkedIn Profile if you have any questions.

Happy scripting!


JS.OracleDatabase: I got it, now how do I use it?

Welcome my much appreciated readers!

I want to tell you that if you have made it here so far, you are on my team. The unsettled ones that always look for improvement.

I’ll be posting some scripts and samples on my PowerShell GitHub Repo. I’m quite sure you’ll find some interesting ideas there. Feel free to contact me about modifications you’d like or better ways to implement and/or extend the functions I have already in the Module.

Here’s a simple script to get the size of an Oracle cluster database.

# Get Formatted Sizing with % Used
 [Parameter(Mandatory = $true)]
 [Parameter(Mandatory = $true)]
 [Parameter(Mandatory = $true)]
foreach ($DBName in $TargetDB) {
 Get-OracleSize -TargetDB $DBName -SizeType $SizeType -Unit $Unit | Format-Table -AutoSize `
@{Name = "Database";Expression = { $_.DBName }; Alignment = "left"},
@{Name = "DiskGroup";Expression = { $_.DiskGroup }; Alignment = "left"},
@{Name = "UsedSpace";Expression = { $_.UsedGB }; Alignment = "Right"},
@{Name = "AllocatedSpace";Expression = { $_.AllocatedGB }; Alignment = "Right"},
@{Name = "MaxSpace";Expression = { "{0:N2}" -f $_.MaxGB }; Alignment = "Right"},
@{Name = "PercentUsed";Expression = { "{0:P2}" -f [float]$( $_.UsedGB / $_.MaxGB ) }; Alignment = "Right"}

Saving this and running it (after having the  JS.OracleDatabase module installed) will give you output like this:

[CUSTOMPROMPT] Dev > .\FormatDBSizing.ps1 -TargetDB myorcldb1 -SizeType Full -Unit GB

Database  DiskGroup UsedSpace AllocatedSpace MaxSpace PercentUsed
---------  --------- --------- -------------- -------- -----------
myorcldb1 DATA_DG      222.47         222.99 13439         1.66 %
myorcldb1 FRA_DG         6.55           6.68  4199.69      0.16 %

Feel free to give it a go and leave your opinions.


PowerShell + Oracle Client = Awesome Module

Hello everyone!

At first I was reluctant to try PowerShell, you know, being a Oracle on Linux DBA gets you fully used to the Linux ecosystem and going for PowerShell meant relying on Windows and .Net to do what I was comfortable doing by leveraging Linux, Korn Shell and the Oracle Client on Linux.

But then again, I don’t like to be too comfortable so I decided to take a dive in and see what I could achieve with PowerShell. As usual, with Microsoft, expectations were high according to what the makers of PowerShell claimed that could be done. And this time, they were just right. If you want to go a little deeper on PowerShell, visit my previous post about it.

So, today I’m  here to make both an announcement and a request.

The announcement is that my first PowerShell module is now published in the PowerShellGallery.com under the name JS.OracleDatabase (JS as in Jesus Sanchez).

If you want to give it a test drive, just run the following commands in PowerShell and feel free to install it and use it to your best convenience.

Find-Module JS.OracleDatabase
Install-Module JS.OracleDatabase

No Admin rights? No Problem, just add -Scope CurrentUser to the Install-Module command and you’ll be set.

The module is currently under development and I’m adding functions as they come handy for me. I can use your suggestions to further add more functionality and options to existing functions.

Remember that this module is based on the Oracle Client programs, so it’s required to have the Oracle Client installed with SQL*Plus and TNS Ping at least.

Make sure to set your environment variables and add the bin directory to the path in powershell as well.

$env:ORACLE_HOME = /path/to/your/oracle/installation
$env:PATH = "$env:PATH ; $env:ORACLE_HOME/bin"

This module can be used under Windows or, if your using PowerShell Core, on Linux.

Happy scripting!

Getting Comfortable with PowerShell

I’m starting my journey with PowerShell. It seemed a rather interesting tool as presented on the MVA courses:

So, after the jump start, I decided to go for it and give it a good try for something else I’m passionate about besides scripting and automation. The Oracle Database.

That’s how my first PowerShell project was born.

But first, get comfortable with the ISE. Yes, you may want to use the PowerShell Integrated Scripting Environment if you, like me, are giving your first steps into PowerShell scripting.

Now, of course you won’t work directly on the path of the modules that are loaded when PowerShell is initiated. Thus, is a good idea to add a persistent path to the $env:PSModulePath environment variable, to do so, choose your path and add it as follows.

$CurrentValue = [Environment]::GetEnvironmentVariable("PSModulePath", "Machine")
[Environment]::SetEnvironmentVariable("PSModulePath", $CurrentValue + ";Drive:\Path\to\where\you\will\work\on\Modules", "Machine")

You can set this configuration for your local account only if you’re on a multi-user computer. Change the “Machine” scope to “User” scope as explained on this MSDN Article.

Now let’s get a better Prompt. Mine is like this, minimalist:

function Prompt {
$CurrentLocation=$($(Get-Location).ToString() -split '\\')[-1]
 "[$env:COMPUTERNAME] $CurrentLocation > "

This function is what you can modify to change the appearance and functionality of your prompt.

Now, you can get tired of getting this to load every time you run the PowerShell ISE, so you may want to automate this. Look for the $profile variable. Create a new file with that path and paste whatever you want done during the ISE startup.

[SOMEHOSTNAME] powershell > $profile

[SOMEHOSTNAME] powershell > if (-not $profile) { New-Item -Path $profile -ItemType File }

[SOMEHOSTNAME] powershell > psEdit $profile

Then paste something like this, substituting the paths for something that makes sense on your system

# Work or Autoload directory
# One line to load them all
Get-ChildItem -Path $psScriptsDir\Prompt.ps1 | %{.$_}
#Then change your location to your work directory
Set-Location Drive:\Path\to\powershell\work

Now you’re all set.

I’ll leave you with your first 5 commands!

  1. Update-Help: make sure you’re online. This will pull the latest docs for whatever you have installed and configured on your PS installation
  2. Get-Help: This will give you the initial step on how to use the help
  3. Find-Module: This command let’s you look for “keywords” on the PS Module Gallery. Try Find-Module *git* as an example.
  4. Save-Module: This can download the module and let you examine its contents. You can manually load the module.
  5. Install-Module: This command installs the module on your PS installation and makes it persistent, so it will be available every time you start PS

Happy scripting!


New GitHub Repo: powershell

Hello my fellow DBAs and dear readers!

My personal project in this case, started as a need for my job. High security measures were forcing us to stop any scripting over Linux, where our databases are (100+ databases, over 200 instances) running. Added to those limitations, no BASH for Windows or cygwin available for installation on workstations. This pushed me to find something else. Python, maybe? Can’t install extensions. Perl? adds complexity that I couldn’t afford due to time frames. I end up saying: Let’s give PowerShell a shot.

I start looking for resources and found some nice videos about PowerShell on the Microsoft Virtual Academy a couple weeks ago. As I progress on the lessons from MVA and start applying what I learn to my own PS scripts, I get a nice taste of what this can become.

We all know for a fact that the Windows console sucks big time. It falls pale when compared to Bourne Again Shell (a.k.a. BASH) and other popular Linux shells. So, it happened that Microsoft started this PowerShell thing. something that looked similar to the console, but different.

On it early stages, it was awful. But now, this kid known as PS has grown to a solid v5.1 that has some very nice functionalities. Let’s face it. We rely on Linux for servers, but Windows, Active Directory and the general Microsoft ecosystem is pretty robust when it comes to enterprise software infrastructure.

Now, I’m an Oracle DBA, looking for a way to do what I did on Linux, but from a Windows workstation. What I found was amazing and as I advance on my PS skills building, I will be posting PowerShell scripts on my GitHub powershell repository to share it with you all.

I highly recommend automation and scripting enthusiasts to take a look at what PowerShell has to offer, especially now that PowerShell 6(Beta) is also available for Linux.

The ability to use other tools and integrate them into the PS script make it a particularly good option for interacting with files, databases and all kinds of objects and data sources.

The future is uncertain, but everything seems to lead to a wonderful mix of tools and architectures that can make our life as DBAs a lot easier.

Write-Output "See you soon"