powershell远程检查多个oracle数据库表空间使用率

作为一个大中企业的DBA，要面对众多大中小多个数据库，除了及时发现异常问题，还要及时了解数据库表空间占用率，掌握空间增长速度过快情况，防止数据库空间不足。

本程序用powershell语言编写，通过配置文件所列清单，对数据库实例进行远程访问，获得除undo和临时表空间外其它表空间使用率，并按使用率进行由大到小的排序。

本程序将先检测IP地址和端口侦听是不是正常，如果数据库实例无法远程访问，见及时继续下一个数据库实例，而不会无限等待。

最主要的是将检查结果显示为html页面的一个表格内，使得检查结果直观，方便结果检查。

以下为程序源代码：

Add-PSSnapin VMware.VimAutomation.Core

function filestring-search($inputFile,$matchstring,$matchcount){

  $tmpcontent = Get-Content $inputFile

  for($i=0;$i -le $tmpcontent.length;i++)

  {

    if($tmpcontent[$i] -like '*异常*'){

    $matchcount++

    wite-host matchcount: $matchcount  -background red

    }

  }

  return

}

Function ConvertTo-AdvHTML

{   <#

    .SYNOPSIS

        Advanced replacement of ConvertTo-HTML cmdlet

    .DESCRIPTION

        This function allows for vastly greater control over cells and rows

        in a HTML table.  It takes ConvertTo-HTML to a whole new level!  You

        can now specify what color a cell or row is (either dirctly or through 

        the use of CSS).  You can add links, pictures and pictures AS links.

        You can also specify a cell to be a bar graph where you control the 

        colors of the graph and text that can be included in the graph.

        All color functions are through the use of imbedded text tags inside the

        properties of the object you pass to this function.  It is important to note 

        that this function does not do any processing for you, you must make sure all 

        control tags are already present in the object before passing it to the 

        function.

        Here are the different tags available:

        Syntax                          Comment

        ===================================================================================

        [cell:<color>]<optional text>   Designate the color of the cell.  Must be 

                                        at the beginning of the string.

                                        Example:

                                            [cell:red]System Down

        [row:<color>]                   Designate the color of the row.  This control

                                        can be anywhere, in any property of the object.

                                        Example:

                                            [row:orchid]

        [cellclass:<class>]<optional text>  

                                        Designate the color, and other properties, of the

                                        cell based on a class in your CSS.  You must 

                                        have the class in your CSS (use the -CSS parameter).

                                        Must be at the beginning of the string.

                                        Example:

                                            [cellclass:highlight]10mb

        [rowclass:<class>]              Designate the color, and other properties, of the

                                        row based on a class in your CSS.  You must 

                                        have the class in your CSS (use the -CSS parameter).

                                        This control can be anywhere, in any property of the 

                                        object.

                                        Example:

                                            [rowclass:greyishbold]

        [p_w_picpath:<height;width;url>]<alternate text>

                                        Include an p_w_picpath in your cell.  Put size of picture

                                        in pixels and url seperated by semi-colons.  Format

                                        must be height;width;url.  You can also include other

                                        text in the cell, but the [p_w_picpath] tag must be at the

                                        end of the tag (so the alternate text is last).

                                        Example:

                                            [p_w_picpath:100;200;/file/upload/202210/18/tclayuuj4ys.jpg]Alt Text For Image

        [link:<url>]<link text>         Include a link in your cell.  Other text is allowed in

                                        the string, but the [link] tag must be at the end of the 

                                        string.

                                        Example:

                                            blah blah blah [link:www.thesurlyadmin.com]Cool PowerShell Link

        [linkpic:<height;width;url to pic>]<url for link>

                                        This tag uses a picture which you can click on and go to the

                                        specified link.  You must specify the size of the picture and 

                                        url where it is located, this information is seperated by semi-

                                        colons.  Other text is allowed in the string, but the [link] tag 

                                        must be at the end of the string.

                                        Example:

                                            [linkpic:100;200;/file/upload/202210/18/tclayuuj4ys.jpg]www.thesurlyadmin.com

        [bar:<percent;bar color;remainder color>]<optional text>

                                        Bar graph makes a simple colored bar graph within the cell.  The

                                        length of the bar is controlled using <percent>.  You can 

                                        designate the color of the bar, and the color of the remainder

                                        section.  Due to the mysteries of HTML, you must designate a 

                                        width for the column with the [bar] tag using the HeadWidth parameter.

                                        So if you had a percentage of 95, say 95% used disk you

                                        would want to highlight the remainder for your report:

                                        Example:

                                            [bar:95;dark green;red]5% free

                                        What if you were at 30% of a sales goal with only 2 weeks left in

                                        the quarter, you would want to highlight that you have a problem.

                                        Example:

                                            [bar:30;darkred;red]30% of goal

    .PARAMETER InputObject

        The object you want converted to an HTML table

    .PARAMETER HeadWidth

        You can specify the width of a cell.  Cell widths are in pixels

        and are passed to the parameter in array format.  Each element

        in the array corresponds to the column in your table, any element

        that is set to 0 will designate the column with be dynamic.  If you had

        four elements in your InputObject and wanted to make the 4th a fixed

        width--this is required for using the [bar] tag--of 600 pixels:

        -HeadWidth 0,0,0,600

    .PARAMETER CSS

        Designate custom CSS for your HTML

    .PARAMETER Title

        Specifies a title for the HTML file, that is, the text that appears between the <TITLE> tags.

    .PARAMETER PreContent

        Specifies text to add before the opening <TABLE> tag. By default, there is no text in that position.

    .PARAMETER PostContent

        Specifies text to add after the closing </TABLE> tag. By default, there is no text in that position.

    .PARAMETER Body

        Specifies the text to add after the opening <BODY> tag. By default, there is no text in that position.

    .PARAMETER Fragment

        Generates only an HTML table. The HTML, HEAD, TITLE, and BODY tags are omitted.

    .INPUTS

        System.Management.Automation.PSObject

        You can pipe any .NET object to ConvertTo-AdvHtml.

    .OUTPUTS

        System.String

        ConvertTo-AdvHtml returns series of strings that comprise valid HTML.

    .EXAMPLE

        $Data = @"

Server,Description,Status,Disk

[row:orchid]Server1,Hello1,[cellclass:up]Up,"[bar:45;Purple;Orchid]55% Free"

Server2,Hello2,[cell:green]Up,"[bar:65;DarkGreen;Green]65% Used"

Server3,Goodbye3,[cell:red]Down,"[bar:95;DarkGreen;DarkRed]5% Free"

server4,This is quite a cool test,[cell:green]Up,"[p_w_picpath:150;650;/file/upload/202210/18/iy4f5pgxyv3.jpg]Test Images"

server5,SurlyAdmin,[cell:red]Down,"[link:http://thesurlyadmin.com]The Surly Admin"

server6,MoreSurlyAdmin,[cell:purple]Updating,"[linkpic:150;650;/file/upload/202210/18/iy4f5pgxyv3.jpg]http://thesurlyadmin.com"

"@

        $Data = $Data | ConvertFrom-Csv

        $HTML = $Data | ConvertTo-AdvHTML -HeadWidth 0,0,0,600 -PreContent "<p><h2>This might be the best report EVER</h2></p><br>" -PostContent "<br>Done! $(Get-Date)" -Title "Cool Test!"

        This is some sample code where I try to put every possibile tag and use into a single set

        of data.  $Data is the PSObject 4 columns.  Default CSS is used, so the [cellclass:up] tag

        will not work but I left it there so you can see how to use it.

    .NOTES

        Author:             Martin Pugh

        Twitter:            @thesurlyadm1n

        Spiceworks:         Martin9700

        Blog:               www.thesurlyadmin.com

        Changelog:

            1.0             Initial Release

    .LINK

        http://thesurlyadmin.com/convertto-advhtml-help/

    .LINK

        http://community.spiceworks.com/scripts/show/2448-create-advanced-html-tables-in-powershell-convertto-advhtml

    #>

    #requires -Version 2.0

    [CmdletBinding()]

    Param (

        [Parameter(Mandatory=$true,

            ValueFromPipeline=$true)]

        [Object[]]$InputObject,

        [string[]]$HeadWidth,

        [string]$CSS = @"

<style>

TABLE {border-width: 1px;border-style: solid;border-color: black;border-collapse: collapse;}

TH {border-width: 1px;padding: 3px;border-style: solid;border-color: black;background-color: #6495ED;font-size:120%;}

TD {border-width: 1px;padding: 3px;border-style: solid;border-color: black;}

</style>

"@,

        [string]$Title,

        [string]$PreContent,

        [string]$PostContent,

        [string]$Body,

        [switch]$Fragment

    )

    Begin {

        If ($Title)

        {   $CSS += "`n<title>$Title</title>`n"

        }

        $Params = @{

            Head = $CSS

        }

        If ($PreContent)

        {   $Params.Add("PreContent",$PreContent)

        }

        If ($PostContent)

        {   $Params.Add("PostContent",$PostContent)

        }

        If ($Body)

        {   $Params.Add("Body",$Body)

        }

        If ($Fragment)

        {   $Params.Add("Fragment",$true)

        }

        $Data = @()

    }

    Process {

        ForEach ($Line in $InputObject)

        {   $Data += $Line

        }

    }

    End {

        $Html = $Data | ConvertTo-Html @Params

        $NewHTML = @()

        ForEach ($Line in $Html)

        {   If ($Line -like "*<th>*")

            {   If ($Headwidth)

                {   $Index = 0

                    $Reg = $Line | Select-String -AllMatches -Pattern "<th>(.*?)<\/th>"

                    ForEach ($th in $Reg.Matches)

                    {   If ($Index -le ($HeadWidth.Count - 1))

                        {   If ($HeadWidth[$Index] -and $HeadWidth[$Index] -gt 0)

                            {   $Line = $Line.Replace($th.Value,"<th width:$($HeadWidth[$Index])px"">$($th.Groups[1])</th>")

                            }

                        }

                        $Index ++

                    }

                }

            }

            Do {

                Switch -regex ($Line)

                {   "<td>\[cell:(.*?)\].*?<\/td>"

                    {   $Line = $Line.Replace("<td>[cell:$($Matches[1])]","<td background-color:$($Matches[1])"">")

                        Break

                    }

                    "\[cellclass:(.*?)\]"

                    {   $Line = $Line.Replace("<td>[cellclass:$($Matches[1])]","<td class=""$($Matches[1])"">")

                        Break

                    }

                    "\[row:(.*?)\]"

                    {   $Line = $Line.Replace("<tr>","<tr background-color:$($Matches[1])"">")

                        $Line = $Line.Replace("[row:$($Matches[1])]","")

                        Break

                    }

                    "\[rowclass:(.*?)\]"

                    {   $Line = $Line.Replace("<tr>","<tr class=""$($Matches[1])"">")

                        $Line = $Line.Replace("[rowclass:$($Matches[1])]","")

                        Break

                    }

                    "<td>\[bar:(.*?)\](.*?)<\/td>"

                    {   $Bar = $Matches[1].Split(";")

                        $Width = 100 - [int]$Bar[0]

                        If (-not $Matches[2])

                        {   $Text = "&nbsp;"

                        }

                        Else

                        {   $Text = $Matches[2]

                        }

                        $Line = $Line.Replace($Matches[0],"<td><div background-color:$($Bar[1]);float:left;width:$($Bar[0])%"">$Text</div><div background-color:$($Bar[2]);float:left;width:$width%"">&nbsp;</div></td>")

                        Break

                    }

                    "\[p_w_picpath:(.*?)\](.*?)<\/td>"

                    {   $Image = $Matches[1].Split(";")

                        $Line = $Line.Replace($Matches[0],"<img class="lazy" data-src=""$($Image[2])"" alt=""$($Matches[2])"" height=""$($Image[0])"" width=""$($Image[1])""></td>")

                    }

                    "\[link:(.*?)\](.*?)<\/td>"

                    {   $Line = $Line.Replace($Matches[0],"<a href=""$($Matches[1])"">$($Matches[2])</a></td>")

                    }

                    "\[linkpic:(.*?)\](.*?)<\/td>"

                    {   $Images = $Matches[1].Split(";")

                        $Line = $Line.Replace($Matches[0],"<a href=""$($Matches[2])""><img class="lazy" data-src=""$($Image[2])"" height=""$($Image[0])"" width=""$($Image[1])""></a></td>")

                    }

                    Default

                    {   Break

                    }

                }

            } Until ($Line -notmatch "\[.*?\]")

            $NewHTML += $Line

        }

        Return $NewHTML

    }

}

function DownloadFile($Username,$Password,$RemoteFile,$LocalFile){

try

{

         $ErrorActionPreference="Stop";

         if($RemoteFile -eq $null){ 

             REM "RemoteFile is null"

             return 

         }

         if($LocalFile -eq $null){ 

             REM "LocalFile is null"

             return 

         }

         $FTPRequest = [System.Net.FtpWebRequest]::Create($RemoteFile)

         $FTPRequest.Credentials = New-Object System.Net.NetworkCredential($Username,$Password) 

         $FTPRequest.Method = [System.Net.WebRequestMethods+Ftp]::DownloadFile 

         $FTPRequest.UseBinary = $true

         $FTPRequest.KeepAlive = $false

         # Send the ftp request

         $FTPResponse = $FTPRequest.GetResponse() 

         # Get a download stream from the server response 

         $ResponseStream = $FTPResponse.GetResponseStream() 

         # Create the target file on the local system and the download buffer 

         $LocalFileFile = New-Object IO.FileStream ($LocalFile,[IO.FileMode]::Create) 

         [byte[]]$ReadBuffer = New-Object byte[] 1024 

         if($ResponseStream -eq $null){

            REM "$RemoteFile Download ERR"

             return

         }

         # Loop through the download 

         do { 

             $ReadLength = $ResponseStream.Read($ReadBuffer,0,1024) 

             $LocalFileFile.Write($ReadBuffer,0,$ReadLength) 

         } 

         while ($ReadLength -ne 0)

         $LocalFileFile.close()

         $ResponseStream.close()

         $ResponseStream.dispose

         REM "$RemoteFile Download OK"

}

catch

{

         REM("Exception Msg: $_")

}

}

function REM($Msg){

    $now= Get-Date

    write-host "$now : $Msg" -foregroundcolor Yellow

    Add-Content $LogFilePath "$now : $Msg"

}

#   AIX_51host_B_all_2016070119_result.txt Linux_BJAPTC10_all_2016070119_result.txt

#   $b = ls C:\DayCheck\

#   "OS","APP","IP","NAME"

#Test-Port -ComputerName TestServer  list_port

function Test-Port 

{

    Param([string]$ComputerName,$ListPort ,$timeout = 1000)

    try

    {

        $tcpclient = New-Object -TypeName system.Net.Sockets.TcpClient

        $iar = $tcpclient.BeginConnect($ComputerName,$ListPort,$null,$null)

        $wait = $iar.AsyncWaitHandle.WaitOne($timeout,$false)

        if(!$wait)

        {

            $tcpclient.Close()

            return $false

        }

        else

        {

            # Close the connection and report the error if there is one

            $null = $tcpclient.EndConnect($iar)

            $tcpclient.Close()

            return $true

        }

    }

    catch

    {

        $false

    }

}

# ####################################################################################v

$today = Get-Date -UFormat "%Y%m%d"

$LogFilePath = "C:\DayCheck\ftpLog_$today.txt"

$today = Get-Date -UFormat "%Y%m%d"

$TargetFileTxt=  "C:\DayCheck\OracleCheckResultALL.txt"

$TargetFileHtml=  "C:\DayCheck\OracleCheckResultALL.html"

if( Test-Path $TargetFileTxt ){

                        write-host  "$TargetFileTxt exist remove" 

                        remove-item $TargetFileTxt -Force

}else {

                        write-host "create $TargetFileTxt "

                        New-Item -Path $TargetFileTxt -Type file   

}

remove-item C:\DayCheck\ResultOut.txt -Force 

remove-item C:\DayCheck\OracleCheckResult* -Force 

New-Item -Path  C:\DayCheck\*_result.txt  -Type file

New-Item -Path  C:\DayCheck\OracleCheckResultSummaryALL.txt  -Type file

New-Item -Path  C:\DayCheck\ResultOut.txt  -Type file

New-Item -Path  C:\DayCheck\OracleCheckResultALL.txt  -Type file

$UserName = "daycheck"

$Password = "daycheck"

"IP,INSTANCE,TABESPALCE_NAME,SIZE,Pct_Used"    |  Out-File  -Encoding utf8   C:\DayCheck\OracleCheckResultSummaryALL_TOTAL.csv

Import-Csv 'C:\DayCheck\OracleList.csv'  |ForEach-Object{

          $IP = $_.IP

          $STRING = $_.STRING

          $PORT = $_.PORT

          $INSTANCE = $_.INSTANCE

         $ConnectStatus =Test-Port -ComputerName $IP  -ListPort $PORT

         if ( $ConnectStatus -eq "True" ){

          $SqlplusCommand = "sqlplus -s " + $STRING + "@"+ $IP + ":"+ $PORT + "/"+ $INSTANCE + " as sysdba "+ "@C:\DayCheck\oracheck_space.sql" 

          $SqlplusCommand

     "TABESPALCE_NAME,SIZE,Pct_Used"|  Out-File  -Encoding utf8  C:\DayCheck\OracleCheckResult.csv

          cmd /c $SqlplusCommand | Out-File   -Encoding utf8   -append  C:\DayCheck\OracleCheckResult.csv

         Get-Content C:\DayCheck\OracleCheckResult.csv  | where { !([string]::IsNullOrWhiteSpace($_))} |Out-File   -Encoding utf8   C:\DayCheck\OracleCheckResult_ttt.csv 

         Import-Csv C:\DayCheck\OracleCheckResult_ttt.csv  |ForEach-Object{

          $TABESPALCE_NAME = $_.TABESPALCE_NAME

          $SIZE = $_.SIZE

          $Pct_Used = $_.Pct_Used

          "$IP,$INSTANCE,$TABESPALCE_NAME,$SIZE,$Pct_Used"

          "$IP,$INSTANCE,$TABESPALCE_NAME,$SIZE,$Pct_Used"|Out-File  -Encoding utf8 -append  C:\DayCheck\OracleCheckResultSummaryALL_TOTAL.csv

         }

             remove-item C:\DayCheck\OracleCheckResult.csv -Force 

      }else {

"$IP,$INSTANCE,$PORT,NotConnect,NotConnect"|Out-File  -Encoding utf8 -append  C:\DayCheck\OracleCheckResultSummaryALL_TOTAL.csv

 }

}

import-csv C:\DayCheck\OracleCheckResultSummaryALL_TOTAL.csv | ConvertTo-advhtml |Set-Content  "C:\DayCheck\OracleCheckResultSummaryALL_TOTAL.html"

invoke-item "C:\DayCheck\OracleCheckResultSummaryALL_TOTAL.html"

====================file oraclelist.csv====================================

"IP","STRING","PORT","INSTANCE","APPNAME"

"192.168.0.90","sys/oracle","1521","wcmdb","wcmdb"

"192.168.0.102","sys/oracle","1521","racdb","racdb"

======================file  oracheck_space.sql====================================

SET ECHO        OFF

SET FEEDBACK OFF

SET HEADING     OFF

SET LINESIZE    180

SET PAGESIZE    50000

SET TERMOUT     ON

SET TIMING      OFF

SET TRIMOUT     ON

SET TRIMSPOOL   ON

SET VERIFY      OFF

SELECT Upper(F.TABLESPACE_NAME)||','||D.TOT_GROOTTE_MB||','||To_char(Round(( D.TOT_GROOTTE_MB - F.TOTAL_BYTES ) / D.TOT_GROOTTE_MB * 100, 2), '990.99')  FROM   (SELECT TABLESPACE_NAME,

               Round(Sum(BYTES) / ( 1024 * 1024 ), 2) TOTAL_BYTES,

               Round(Max(BYTES) / ( 1024 * 1024 ), 2) MAX_BYTES

        FROM   SYS.DBA_FREE_SPACE where  Upper(TABLESPACE_NAME) not like '%UNDO%'

        GROUP  BY TABLESPACE_NAME) F,

       (SELECT DD.TABLESPACE_NAME,

               Round(Sum(DD.BYTES) / ( 1024 * 1024 ), 2) TOT_GROOTTE_MB

        FROM   SYS.DBA_DATA_FILES DD  where (TABLESPACE_NAME not like '%UNDO%' )or  (TABLESPACE_NAME not like '%undo%')

        GROUP  BY DD.TABLESPACE_NAME) D

WHERE  D.TABLESPACE_NAME = F.TABLESPACE_NAME

ORDER  BY  To_char(Round(( D.TOT_GROOTTE_MB - F.TOTAL_BYTES ) / D.TOT_GROOTTE_MB * 100, 2), '990.99') desc

/

exit