作為一個大中企業(yè)的DBA，要面對眾多大中小多個數(shù)據(jù)庫，除了及時發(fā)現(xiàn)異常問題，還要及時了解數(shù)據(jù)庫表空間占用率，掌握空間增長速度過快情況，防止數(shù)據(jù)庫空間不足。
創(chuàng)新互聯(lián)長期為上1000+客戶提供的網(wǎng)站建設(shè)服務(wù)，團(tuán)隊從業(yè)經(jīng)驗10年，關(guān)注不同地域、不同群體，并針對不同對象提供差異化的產(chǎn)品和服務(wù)；打造開放共贏平臺，與合作伙伴共同營造健康的互聯(lián)網(wǎng)生態(tài)環(huán)境。為福安企業(yè)提供專業(yè)的成都網(wǎng)站設(shè)計、成都網(wǎng)站建設(shè)，福安網(wǎng)站改版等技術(shù)服務(wù)。擁有十年豐富建站經(jīng)驗和眾多成功案例,為您定制開發(fā)。
本程序用powershell語言編寫，通過配置文件所列清單，對數(shù)據(jù)庫實例進(jìn)行遠(yuǎn)程訪問，獲得除undo和臨時表空間外其它表空間使用率，并按使用率進(jìn)行由大到小的排序。
本程序?qū)⑾葯z測IP地址和端口偵聽是不是正常，如果數(shù)據(jù)庫實例無法遠(yuǎn)程訪問，見及時繼續(xù)下一個數(shù)據(jù)庫實例，而不會無限等待。
最主要的是將檢查結(jié)果顯示為html頁面的一個表格內(nèi)，使得檢查結(jié)果直觀，方便結(jié)果檢查。
以下為程序源代碼：
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:
at the beginning of the string.
Example:
[cell:red]System Down
[row:
can be anywhere, in any property of the object.
Example:
[row:orchid]
[cellclass:
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:
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:
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;http://www.sampleurl.com/samplep_w_picpath.jpg]Alt Text For Image
[link:
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:
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;http://www.sampleurl.com/samplep_w_picpath.jpg]www.thesurlyadmin.com
[bar:
Bar graph makes a simple colored bar graph within the cell. The
length of the bar is controlled using
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
}
$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,"
}
}
$Index ++
}
}
}
Do {
Switch -regex ($Line)
{ "
{ $Line = $Line.Replace("
Break
}
"\[cellclass:(.*?)\]"
{ $Line = $Line.Replace("
Break
}
"\[row:(.*?)\]"
{ $Line = $Line.Replace("
$Line = $Line.Replace("[row:$($Matches[1])]","")
Break
}
"\[rowclass:(.*?)\]"
{ $Line = $Line.Replace("
$Line = $Line.Replace("[rowclass:$($Matches[1])]","")
Break
}
"
{ $Bar = $Matches[1].Split(";")
$Width = 100 - [int]$Bar[0]
If (-not $Matches[2])
{ $Text = " "
}
Else
{ $Text = $Matches[2]
}
$Line = $Line.Replace($Matches[0],"
Break
}
"\[p_w_picpath:(.*?)\](.*?)<\/td>"
{ $Image = $Matches[1].Split(";")
$Line = $Line.Replace($Matches[0],"")
}
"\[link:(.*?)\](.*?)<\/td>"
{ $Line = $Line.Replace($Matches[0],"$($Matches[2])")
}
"\[linkpic:(.*?)\](.*?)<\/td>"
{ $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
重慶分公司
重慶分公司
