Аудит баз Oracle



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

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

    Comments 13

      +1
      >Win-инстансов уже порядка 200
      >Где же расположены базы (хотя бы географически) – никто не знает, в том числе заказчик и система мониторинга за 10К$. Эти СУБД никогда туда не вносились.
      >Мой способ решить данную проблему
      >Excel-отчет и отправляем его почтой.

      Т.е. поставить серверы на мониторинг в Oracle Enterprice Manager показалось вам слишком банальным способом следить за состоянием серверов, инстансов и БД?
      Если же цель сделать всё максимально олдскульно, то не хватает VBS и макросов для Excel.
        0
        Согласен, все просто — дополнительные функции Enterprise manager стоят денег, а лицензий на него просто нет.
        К тому же его установка требует дополнительных серверных мощностей.
        Серьезные вмешательства в инфраструктуру тоже нужно согласовывать — это было долго и мало кому кроме меня интересно.
        • UFO just landed and posted this here
            0
            Вы описываете идеальный случай. Внутренние разработки и вмешательства со стороны внешних организаций -принципиально разные вещи.
          0
          Уважаемые, все же просто. Oracle Enterprise manager для нескольких инстансов — это уже Grid Control.
          Стоимость у него уже совсем не маленькая, тем более его дополнительных возможностей. Сама установка Grid control, закупка железа, согласование… тянет на полноценное внедрение.
          • UFO just landed and posted this here
            0
            Даже если у компании проблемы с IT бюджетом(хотя судя по кол-ву инстансов это не так) можно же было использовать уже купленную «система мониторинга за 10К$».
            Серьезные вмешательства в инфраструктуру тоже нужно согласовывать — это было долго и мало кому кроме меня интересно.
            А местных DBA это не интересовало?

            upd: кстати, Grid Control кажется недавно переименовали в cloud control.
              0
              денег они пожалели…
              В жизни не поверю что для тех over 9000 инстансов, которые к тому-же создаются постоянно простыми разрабами, они платят за лицензию. Тем более, что Cloud control сам по себе бесплатен.
              0
              Действительно Oracle 11g -grid control, Oracle 12c — cloud control
              Enterprise Manager если не ошибаюсь перестает быть бесплатным как только мониторит более одного инсанса.
              Более того, для развертывания Grid понадобится еще один отдельный инстанс Oracle под репозитарий и установить Oracle application server, а это совсем отдельное кунг фу.
              Да, для настройки мониторинга понадобится еще раскидать агенты по серверам.

              • UFO just landed and posted this here
                0
                Да. Такого изврата я еще не видел.
                Все администраторы СУБД Oracle знают: если у тебя больше чем 2 инстанса СУБД, то развертывай Oracle Enterprise Manager или как он по новому называется Oracle Cloud Manager и управляй всеми продуктами Oracle включая СУБД и сервера приложений. Возможностей управления будет гораздо больше.
                  0
                  Какой-то извратный способ, но каждое решение имеет право на жизнь.
                    0
                    Поправочка, Weblogic — привык к старым названиям.
                    В данном случае нужны были систематические наглядные результаты для менеджмента, а не вторая система мониторинга, которую нужно покупать внедрять и поддерживать. Что касается первой системы мониторинга, то по странной причине в качестве СУБД для нее использоалась старая SQLExpress, которая регулярно переполнялась и без моих СУБД. В долгосрочном плане вы безусловно правы — чем писать скрипты, лучше настроить агентов SNMP и Weblogic.

                    Only users with full accounts can post comments. Log in, please.