# 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    Get-OracleAWRInstanceReport  0.2.2.3 JS.OracleDatabase
Function    Get-OracleAWRReport          0.2.2.3 JS.OracleDatabase
Function    Get-OracleDBID               0.2.2.3 JS.OracleDatabase
Function    Get-OracleDBInfo             0.2.2.3 JS.OracleDatabase
Function    Get-OracleDBVersion          0.2.2.3 JS.OracleDatabase
Function    Get-OracleHosts              0.2.2.3 JS.OracleDatabase
Function    Get-OracleInstances          0.2.2.3 JS.OracleDatabase
Function    Get-OracleLongOperations     0.2.2.3 JS.OracleDatabase
Function    Get-OracleLongRunQueries     0.2.2.3 JS.OracleDatabase
Function    Get-OracleNames              0.2.2.3 JS.OracleDatabase
Function    Get-OracleOptions            0.2.2.3 JS.OracleDatabase
Function    Get-OraclePerfReports        0.2.2.3 JS.OracleDatabase
Function    Get-OracleServices           0.2.2.3 JS.OracleDatabase
Function    Get-OracleSessions           0.2.2.3 JS.OracleDatabase
Function    Get-OracleSize               0.2.2.3 JS.OracleDatabase
Function    Get-OracleSnapshot           0.2.2.3 JS.OracleDatabase
Function    Get-OracleSnapshotTime       0.2.2.3 JS.OracleDatabase
Function    Get-OracleSQLText            0.2.2.3 JS.OracleDatabase
Function    Get-OracleUsers              0.2.2.3 JS.OracleDatabase
Function    Ping-OracleDB                0.2.2.3 JS.OracleDatabase
Function    Remove-OracleSchema          0.2.2.3 JS.OracleDatabase
Function    Test-OracleEnv               0.2.2.3 JS.OracleDatabase
Function    Test-OracleHealth            0.2.2.3 JS.OracleDatabase
Function    Use-OracleDB                 0.2.2.3 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 12.1.0.2.0 - 64bit Production 0 myorcldb PL/SQL Release 12.1.0.2.0 - Production 0 myorcldb CORE 12.1.0.2.0 Production 0 myorcldb TNS for Linux: Version 12.1.0.2.0 - Production 0 myorcldb NLSRTL Version 12.1.0.2.0 - 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 12.1.0.2.0 - 64bit Production
myorcldb PL/SQL Release 12.1.0.2.0 - Production
myorcldb CORE 12.1.0.2.0 Production
myorcldb TNS for Linux: Version 12.1.0.2.0 - Production
myorcldb NLSRTL Version 12.1.0.2.0 - 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! Advertisements # 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 Param( [Parameter(Mandatory =$true)]
[String[]]$TargetDB, [Parameter(Mandatory =$true)]
[ValidateSet("Full","Storage","Tablespace","Table")]
[String]$SizeType, [Parameter(Mandatory =$true)]
[ValidateSet("B","KB","MB","GB","TB","PB","EB","ZB")]
[String]$Unit ) 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$psScriptsDir="Drive:\Path\to\powershell\scripts"
# One line to load them all
Get-ChildItem -Path $psScriptsDir\Prompt.ps1 | %{.$_}
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!

# How to ‘find’ with argument list too long

Today I’m going to share a very short, but (I hope) useful piece of information for some Linux admins and DBAs as well.

The find tool is very good in isolating files that match a certain pattern.

I use it, in particular, for cleaning up old logs and files that are no longer required in file system if you generate 5 to 10 logs a day. You could even do this manually, but when you generate 5 to 10 logs per minute, you need to automate this as much as you can.

So piping the following commands together do the job nicely

find . -name *xmin*.log -mtime +30 | xargs -r rm -f



Or so I thought.

Lately I have been receiving the following error:

/usr/bin/find: Argument list too long

A shame… Cleanup is not working anymore and I have to go over it manually. But manually doesn’t work either as the number of files is huge.

Turns out that the error pops up at substitution time because of the pattern *xmin*.log. It doesn’t act as a pattern at all but as a list of all the files that match that pattern.

So, if you want it to work as expected, just enclose it in single quotes as follows:

find . -name '*xmin*.log' -mtime +30 | xargs -r rm -f

And that’s it… You’re back in the automated game!