Здравствуйте, дорогие хабрачитатели! Естественно желание каждого нанимателя оценить эффективность и качество исполняемой работы сотрудников, умножить прибыль и сократить издержки. Поддержка же ИТ-инфраструктуры всегда является «черным ящиком». За что заплачены деньги, ведь ничего еще не сломалось? Так как вникать в проблематику, скорее всего, ни один управленец не захочет, с высокой вероятностью вскоре возникнет необходимость в отчете о проделанной работе, и желательно — ежедневном. Рассматривая красивые циферки и стройные графики, заказчик неизменно «входит во вкус». Постепенно появляются отчеты мониторинга инфраструктуры, состояния бэкапов, упущенных инцидентов, работы DLP-систем. И чем дальше, тем страшнее. ИТ-поддержка начинает проигрывать в эффективности, меняется график работы команды, ведь отчет нужен уже утром. Подобная практика невероятно стимулирует умственную активность в направлении оперативного предоставления данных, к которым имеешь почти что эксклюзивный доступ. Мой способ решить данную проблему постараюсь описать далее.

Отчет отчетом погоняет

Заказчик — очень крупная производственная компания с огромным количеством магазинов и складов. Обожает Oracle в Windows-среде (что вообще редкость). Заводы мы не рассматриваем, наша цель – склады и магазины, и все СУБД, что там крутятся.

Известно, что новые инстансы СУБД создаются на регулярной основе разработчиками или тестировщиками – они запросто могут и не поставить никого в известность о существовании своей тестовой среды, но запаникуют, когда ее случайно удалят в связи со списанием сервера или очередной виртуализацией. Есть и богом забытые сервера с базами 10-летней давности. С ними до сих пор работает какой-нибудь магазин или склад. Где же расположены базы (хотя бы географически) – никто не знает, в том числе заказчик и система мониторинга за 10К$. Эти СУБД никогда туда не вносились. Как уже утверждалось, специфика такова, что большая часть СУБД Oracle развернута в Windows-среде. Win-инстансов уже порядка 200 и собрать подобную информацию о них весьма трудно. Также есть и Oracle под Linux. Таких баз всего-то ничего – штук 40. Есть еще один серьезный плюс – сервера имеют Name convention по локации: найдем имя сервера – найдем и его расположение.

Для разработки отчета будем использовать PowerShell. Почему? Ибо:

• Работа ведется с терминальной машины Windows Server 2008. Извне доступа к другим серверам нет.
• Там есть Excel! PowerShell расчудесно с ним работает как с com-обьектом. Не нужно искать модули, как, например, с Python, так как все уже вшито в NET.
• Большая часть серверов у нас все-таки Windows.
• Опыта работы с PowerShell у меня побольше.

Для доступа и получения информации с Linux-хостов все-таки поставим Cygwin. Все скрипты и отчеты тогда будут в одном месте, и это хорошо. Задача отчета: происходит долгосрочный аудит безопасности баз данных Oracle в связи с миграцией СУБД в виртуальную среду.

Необходимо определить:

• сколько у нас баз,
• в каком они состоянии на текущий момент,
• на каких серверах расположены, запущены ли они вообще,
• как потребляют ресурсы, какая развернута версия Oracle.

Начнем с Linux

Местные разработчики их почему-то боятся, так что все СУБД в production. Сервера известны, их мало. Cканируем список Linux-хостов и получаем итоговый файл в своем каталоге. Для поиска инстансов Oracle ищем запущенный процесс Pmon простым однострочным bash-скриптом.

Скрипт 1:

for line in $(cat file.txt)
 do ssh oracle@$line  '$(ps -e -o cmd | grep ora_pmon |grep -v grep|cut -c 10-19  > /tmp/result.txt) ; while read i ; do my_var=$(echo $i ); echo $(hostname -s)";"${my_var##*_}";;;"; done < /tmp/result.txt ; rm /tmp/result.txt' >>script_files/FileOra2.csv
done

Windows наше все

Тут мы Pmon не найдем, весь Oracle реализован как один многопоточный процесс. Windows-хосты будем обходить с помощью Windows Management Interface. Инстанс Oracle же будет найден в службах Windows. Используем PowerShell:

Скрипт 2

$MLpath=  'c:\scripts\DBA\script_files\ML.txt'
$MLdir= [System.IO.Path]::GetDirectoryName($MLPath)
$outfile=$($MLdir +'\'+'FileOra.csv')
$Dbfile= $($MLdir +'\'+'DBList.csv')
$hosts=get-content $MLpath -Force 
$a= foreach ($pc in $hosts){
write-host "test $pc"
try{
<#TO display
gwmi -Class win32_service -computername "$pc"|where { $_.name -like "OracleService*" } -ErrorAction SilentlyContinue|format-table "$pc", name, state, pathname, StartMode -autosize|out-host#>
$colItems = gwmi -Class win32_service -computername "$pc"|where { $_.name -like "OracleService*" } -ErrorAction SilentlyContinue
foreach ($objItem in $colItems) {$($pc +";" +($objItem.name).trimstart("OracleService") +";" +$objitem.state +";" +$ObjITem.pathname +";" +$ObjITem.startmode) >> $outfile
}
}
catch {
					Write-Output $("$pc" + $_.Exception.Message)
				} 			
}

Что же дальше

Собрав список хостов и баз данных, сделан первый шаг к актуализации информации. Первым делом я завел единого пользователя в каждой СУБД, от которого выполнял дальнейшие действия. Настало время сбора информации. Можно было бы использовать SQL*Plus, но раз уж мы работаем с com-обьектами, лучше использовать OLEDB для Oracle. Для этого доустановим на наш терминал OLEDB-провайдер и выполним интересующий нас запрос в каждой СУБД. Скачать его можно, например, с официального сайта Oracle. В системных требованиях к OLEDB видим примерно следующее:

— Access to an Oracle Database (Oracle 9.2 or later)
— Oracle Client release 11.1 or later and Oracle Net Services (included with Oracle Provider for OLE DB installation).

Теперь можно абстрагироваться от операционной системы на серверах. Cоздаем коннектор, выполняем запрос в каждой отдельной базе и сохраняем результаты в файлик. Скрипт 3 я, впрочем, использую отдельно, выполняя любые произвольные запросы к списку СУБД, такие как количество свободного места, параметры SGA, PGA, списки пользователей и криптостойкость их паролей (HASH для Oracle паролей можно без труда найти в Интернете). Некоторые символы запросов потребуют экранирования в PowerShell – в этом случае удобно воспользоваться Oracle-функцией CHR, возвращающей символ из таблицы кодировки ASCII. Также на выходе получим отдельный список хостов, к которым не удалось подключиться с указанием кодов ошибок для дальнейшего анализа.

Скрипт 3

function Get-OLEDBData ($connectstring, $sql) {            
   $OLEDBConn = New-Object System.Data.OleDb.OleDbConnection($connectstring)            
   $OLEDBConn.open()            
   $readcmd = New-Object system.Data.OleDb.OleDbCommand($sql,$OLEDBConn)            
   $readcmd.CommandTimeout = '10'            
   $da = New-Object system.Data.OleDb.OleDbDataAdapter($readcmd)            
   $dt = New-Object system.Data.datatable            
   [void]$da.fill($dt)            
   $OLEDBConn.close()            
   return $dt           
}
$date=(get-date).toshortdatestring().replace("/",".")
$log = "$("$date" +"_"+ 'error')"
$db = "$("$date" +"_"+ 'DBlist')"
$qry= 'select INSTANCE_NAME,HOST_NAME,VERSION from V$INSTANCE'
gc c:\_tir\fileORA.csv| % {
$row = $_.split(";") 
$hostname = $row[0] 
$service = $row[1]
$connString = "password=xxxxXXXxxx;User ID=ORAUSER;Data Source=$hostname/$service;Provider=OraOLEDB.Oracle"     
try { Get-OLEDBData $connString $qry}
catch {Write-Output $("$Compname" +';'+ $_.Exception.Message)  >> C:\_tir\$log.log
}
}|Export-Csv c:\_tir\$db.csv -delim ';'

Наводим красоту

Текстовые файлы – это некрасиво. Соединяем все полученные результаты в каталоге в ежедневный отчет Excel. Работаем с листом Excel как с обычным объектом. Скрываем лист, чтобы операция шла быстрее. Отчет отправляем к себе на почту. Наконец, обновим наш TNSNAMES-файл для удобства дальнейшего подключения к базам через SQL*Plus. Используем правильный синтаксис файла (никогда не мог его запомнить).

Скрипт 4

$date=(get-date).toshortdatestring().replace("/",".")
$MLpath=  'c:\scripts\DBA\script_files\ML.txt'
$MLdir= [System.IO.Path]::GetDirectoryName($MLPath)
$outfile=$($MLdir +'\'+'FileOra.csv')
$Dbfile= $($MLdir +'\'+'DBList.csv')
$Dbfilexls= $($MLdir +'\'+'DBList'+ $date +'.xlsx')
#$logFile= [System.IO.Path]::Combine($MLdir,$("{0}.log" -f $sourceFileName ))
gc $outfile|Sort-Object -Unique|out-file $Dbfile -Force
<#creating excel doc#>
$excel = new-object -comobject excel.application
$excel.visible = $false
$workbook = $excel.workbooks.add()
$workbook.workSheets.item(3).delete()
$workbook.WorkSheets.item(2).delete()
$workbook.WorkSheets.item(1).Name = "Databases"
$sheet = $workbook.WorkSheets.Item("Databases")
$x = 2
$colorIndex = "microsoft.office.interop.excel.xlColorIndex" -as [type]
$borderWeight = "microsoft.office.interop.excel.xlBorderWeight" -as [type]
$chartType = "microsoft.office.interop.excel.xlChartType" -as [type]
For($b = 1 ; $b -le 5 ; $b++)
{
 $sheet.cells.item(1,$b).font.bold = $true
  $sheet.cells.item(1,$b).borders.ColorIndex = $colorIndex::xlColorIndexAutomatic
 $sheet.cells.item(1,$b).borders.weight = $borderWeight::xlMedium
}
$sheet.cells.item(1,1) = "Hostname"
$sheet.cells.item(1,2) = "Instance"
$sheet.cells.item(1,3) = "state"
$sheet.cells.item(1,4) = "path"
$sheet.cells.item(1,5) = "autorun"
Foreach ($row in $data=Import-Csv $Dbfile -Delimiter ';' -Header name, value, path, state, start)
{ 
 $sheet.cells.item($x,1) = $row.name
 $sheet.cells.item($x,2) = $row.value
 $sheet.cells.item($x,3) = $row.path
 $sheet.cells.item($x,4) = $row.state
 $sheet.cells.item($x,5) = $row.start
 $x++
} 
$range = $sheet.usedRange
 $range.EntireColumn.AutoFit() | Out-Null
  $Excel.ActiveWorkbook.SaveAs($Dbfilexls)
if($workbook -ne $null)
 {
     $sheet = $null
     $range = $null
  
     $workbook.Close($false)
 }
 if($excel -ne $null)
 {
     $excel.Quit()
     $excel = $null
     [GC]::Collect()
     [GC]::WaitForPendingFinalizers()
 }
 IF(Test-Path $MLdir\tnsnames.ora )
  {
   
   remove-item $MLdir\tnsnames.ora -Force 
    
  }
ELSE
  {
   Write-Host "new tnsora"  
  }
  
<# Update TNSORA file#>
gc $Dbfile| % { 
$row = $_.split(";") 
$hostname = $row[0] 
$service = $row[1] 
$name=$service+'_'+$hostname
"$name =
 (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = $hostname)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = $service)
    )
  )">> $MLdir\tnsnames.ora 
  }
    <#Mail report to #>
  $filename= $Dbfilexls= $($MLdir +'\'+'DBList'+ $date +'.xlsx')
  $smtpServer = “server_name”
 $smtp = new-object Net.Mail.SmtpClient($smtpServer)
$credentials=new-object system.net.networkcredential("server_name\mail","Dfgtnb451")
$smtp.credentials=$credentials.getcredential($smtpserver,”25”,”basic”)
$msg= New-Object net.Mail.MailMessage
$att = new-object Net.Mail.Attachment($filename)
$msg.from = “user@yourdomain.com”
 $msg.to.add(“timur@rrrr.xxx, valentin@rrrr.xxx”)
 $msg.subject = “Database_Report”
 $msg.body = "DAtabase report sample body"
 $msg.isbodyhtml= "false"
 $msg.Attachments.Add($att)
$smtp.Send($msg)

Осталось внести наши скрипты в Windows-планировщик. Сначала собираем информацию об инстансах Oracle на Linux и Windows-хостах соответственно (1 и 2 скрипты). Далее подключаемся к каждой СУБД и собираем информацию (скрипт 3). После чего строим агрегированный Excel-отчет и отправляем его почтой (скрипт 4).

Выводы: за что боролись – на то и напоролись

• Навели порядок. Нашлись древние базы с Oracle 9 и ничейные инсталляции Oracle 12. Наконец от них избавились.
• Настроил автоматическую пересылку письма со своего Exchange-ящика заинтересованным лицам. Всегда в курсе изменений в инфраструктуре.
• Знаю, когда и где база упала, сравнив два отчета.
• Части этого решения я постепенно растащил по другим областям применения. Получил всегда актуальный список Tnsnames
• В ходе создания скрипта отчета обнаружились дыры по входу со стандартными паролями Oracle, вроде scott/tiger с завышенными привилегиями доступа. Поэтому после создания отчета пришлось провести отдельный аудит безопасности с перебором стандартных паролей и создать отчет уже по списку паролей. Таким образом, решение пригодилось дважды.

Удачной автоматизации!