Shulou(Shulou.com)06/01 Report--

DBA, as a large and medium-sized enterprise, has to face many large, medium and small databases. In addition to finding abnormal problems in time, it is also necessary to timely understand the occupancy rate of database table space, grasp the situation of excessive space growth, and prevent the shortage of database space.

This program is written in powershell language. through the list listed in the configuration file, we can remotely access the database instance, obtain the utilization rate of other table spaces except undo and temporary table space, and sort from big to small according to the utilization rate.

This procedure will first check whether the IP address and port listening is normal, if the database instance can not be accessed remotely, see continue the next database instance in time, instead of waiting indefinitely.

The most important thing is to display the inspection results in a table on the html page, which makes the inspection results intuitive and convenient to check the results.

The following is the program source code:

Add-PSSnapin VMware.VimAutomation.Core

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

$tmpcontent = Get-Content $inputFile

For ($iTuno / I-le $tmpcontent.length;i++)

{

If ($tmpcontent [$I]-like'* exception *') {

$matchcount++

Wite-host matchcount: $matchcount-background red

}

}

Return

}

Function ConvertTo-AdvHTML

{

# requires-Version 2.0

[CmdletBinding ()]

Param (

[Parameter (Mandatory=$true)

ValueFromPipeline=$true)]

[Object []] $InputObject

[string []] $HeadWidth

[string] $CSS = @ "

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

TH {border-width: 1pxscape padding: 3pxposition bordercopyright style: solid;border-color: black;background-color: # 6495EDbot Fontsway sizepigment 120%;}

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

"@

[string] $Title

[string] $PreContent

[string] $PostContent

[string] $Body

[switch] $Fragment

)

Begin {

If ($Title)

{$CSS + = "`n$ 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 "* *")

{If ($Headwidth)

{$Index = 0

Reg = $Line | Select-String-AllMatches-Pattern "(. *?)"

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.Groups [1]))

}

}

$Index + +

}

}

}

Do {

Switch-regex ($Line)

{"\ [cell: (. *?)\]. *?"

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

Break

}

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

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

Break

}

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

{$Line = $Line.Replace (",")

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

Break

}

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

{$Line = $Line.Replace (",")

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

Break

}

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

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

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

If (- not $Matches [2])

{$Text = ""

}

Else

{$Text = $Matches [2]

}

$Line = $Line.Replace ($Matches [0], "$Text")

Break

}

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

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

$Line = $Line.Replace ($Matches [0], "

")

}

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

{$Line = $Line.Replace ($Matches [0], $($Matches [2]))

}

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

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

$Line = $Line.Replace ($Matches [0], "

")

}

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

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.