Delphi и Oracle: разрабатываем утилиту для администрирования ORACLE

Одним из самых полезных инструментов в арсенале хорошего администратора является утилита, позволяющая автоматизировать рутинные действия и представить различную информацию о базе в наглядном виде (графики, диаграммы).

Благодаря нашему сегодняшнему практикуму вы сможете освоить основные понятия ORACLE с позиции программиста и более детально разобраться в этой популярнейшей СУБД.

Наши «конкуренты»

Одной из самых популярных утилит для мониторинга и администрирования СУБД ORACLE является Quest Spoltight. Она позволяет диагностировать и решать проблемы, возникающие с сервером. Официальный сайт http://wm.quest.com, а по адресу www.quest.com/spotlight_on_windows можно скачать бесплатную trial-версию для Windows, рассчитанную на 365 дней.

Quest Spotlight

Для разработки нам потребуется набор компонентов AnyDAC (его можно скачать с официального сайта www.da-soft.com или взять на нашем диске), с помощью которого мы будем подключаться к ORACLE. Чем обусловлен этот выбор? Как всегда: бесплатностью, удобством и простотой работы.

Установка пакета AnyDAC после setup.exe становится весьма тривиальной, а в результате работы установщика в палитре компонентов Delphi появится несколько новых закладок:

AnyDAC
AnyDAC UI
AnyDAC Links
AnyDAC Devs

Создадим новый проект, разместим на нем TPageControl со следующими закладками:

общая информация;
табличные пространства;
сессии;
блокировки;
пользователи;
проверка паролей.

Далее приведем форму к виду.

Создадим DataModule (назовем его DM), на котором будет расположен TADOConnection, щелкнем на нем правой кнопкой мыши и выберем пункт Connection Editor. Появится диалоговое окно AnyDAC Connection Editor, в его выпадающем списке Driver ID нужно выбрать пункт ORA. Затем настроим поля Database (имя базы данных), User_Name (имя пользователя), Password (Пароль).

Настройка параметров доступа к базе

Нажмем кнопку Test для проверки соединения – появится окно AnyDAC Login, в котором необходимо выбрать тип учетной записи: Normal, SysDBA, SysOper (рис. 4), а затем щелкнуть кнопку ОК.

Проверка соединения с базой

Через несколько секунд вы увидите диалоговое окно с надписью «Connection established successfully», если этого не произошло, то еще раз проверьте введенные параметры доступа. Теперь установим свойство LoginPromt компонента TADOConnection в False.

Закладка «Общая информация»

Разместим на модуле данных TADQuery (будьте внимательны, это не TADOQuery), назовем его QGlobalName и в свойстве SQL введем следующий запрос:

select * from global_name

По этому запросу мы получим глобальное имя нашей базы. Обратите внимание, после запроса не нужно ставить точку с запятой иначе при его выполнении возникнет ошибка.

Точка с запятой, размещенная в конце SQL запроса, вызывает ошибку ORA-00911: неверный символ

Компонент TDataSource расположим также на DataModule и назовем его DSGlobalName, связав с набором QGlobalName и с TDBEdit, расположенным напротив метки «Имя базы», которую можно найти на форме. Точно таким же образом поместим на модуль данных еще один TADQuery, назовем его QVersion, с помощью которого мы получим версию СУБД ORACLE, установленной на сервере:

select * from v$version

Вообще запрос возвращает несколько строк, но нас интересует только первая, поэтому добавляем ее в проект TDataSource, называем его DSVersion, затем связываем его с набором QVersion и c TDBEdit , расположенным на форме напротив метки «Версия».

Представления с префиксом V$

В ORACLE существует специальный тип представлений – системные. Как правило, они начинаются с префикса V$. С их помощью DBA может получать исчерпывающую информацию об экземпляре базы данных, осуществлять мониторинг за действиями пользователей, контролировать процессы, происходящие в базе.

Последний TADQuery (name=QSGAInfo) будет возвращать информацию о специальной области памяти ORACLE под названием SGA:

select pool,sum(BYTES) from v$sgastat where pool='shared pool' group by pool union select POOL,sum(BYTES) from v$sgastat where POOL='java pool' group by pool union select POOL,sum(BYTES) from v$sgastat where POOL='large pool' group by pool union select NAME as POOL,BYTES from v$sgastat where NAME='buffer_cache'

Результат будет аналогичен тому, как если бы вы открыли Oracle Enterprise Manager Console и, выбрав ветку Configuration, перешли на закладку Memory.

Информация об области SGA, полученная с помощью Oracle Enterprise Manager Console

Обратимся к теории. SGA – область памяти, которая обслуживает конкретный экземпляр базы данных. В ней хранятся данные и контрольная информация для экземпляра. К ней обращаются системные процессы ORACLE, такие как DBWR, чтобы осуществлять запись изменений в базу, ARCH, чтобы архивировать оперативный журнал транзакций и др. Она состоит из:

Shared Pool (разделяемый пул) – содержит разобранные запросы, процедуры и другие программные компоненты. Не секрет, что когда вы отправляете SQL-запрос на сервер, он анализируется для достижения наибольшей производительности. Таким образом, перед тем как выполнить очередной запрос, ORACLE обязательно просмотрит разделяемый пул на наличие такового там, и если он есть, то будет мгновенно выполнен, минуя операцию разбора;
Buffer cache (кэш буфера данных) – запрашиваемые пользователем данные, прежде чем попасть к нему, сначала считываются в буферный кэш, а поскольку обращение к памяти быстрее, чем к диску, то в случае, когда эти данные потребуются еще кому-то, они будут выданы максимально быстро. Конечно, кэш не резиновый, поэтому данные, к которым давно не было обращения, затираются новой информацией;
Large Pool – используется в режиме MTS, а также в операциях, которые выполняются параллельно несколькими серверами;
Java Pool – область памяти, выделенная виртуальной машине Java, работающей в составе сервера;
Redo log buffer (буфер журнала транзакций) – предназначен для кэширования изменений, перед тем как записать их на диск.

Итак, практически все готово к проверке нашей, пока еще тестовой программы. Но прежде, чем нажать заветную F9, добавьте в проект TDataSource (DSSGAInfo) и свяжите его с TDBGrid, расположенной под текстом «Данные по SGA»:

Если вы хотите более детально разобраться в архитектуре ORACLE, то советую посетить вот эту ссылку: www.citforum.ru/database/oracle/kyte/02.shtml.

Последний шаг, который необходимо совершить для закладки «Общая информация», – это разместить компонент TChart. Щелкнем на нем дважды и добавим круговую диаграмму. Затем перейдем на закладку Legend и снимем флаг Resize Chart, чтобы масштабирование работало нормально. Переходим к программированию – в событии OnCreate модуля данных вставим следующий код:

Инициирующие действия и заполнение TChart данными

procedure TDM.DataModuleCreate(Sender: TObject); var zn:integer;//значение txt:string;//метка color:integer;//цвет begin //Открываем сессию к БД и наборы ADConnection1.Open; QGlobalName.Open; QVersion.Open; QSGAInfo.Open;

color:=100000;//начальный цвет //Заполняем диаграмму while not(QSGAInfo.Eof) do begin zn:=QSGAInfo.Fields[1].Value; txt:=QSGAInfo.Fields[0].Value ; //Переводим значение диаграммы в МБ, для наглядности MainForm.Chart1.Series[0].Add(zn div 1024 div 1024,txt,color); color:=color+200000; //меняем цвет для следующего значения QSGAInfo.Next; end; end;

После запуска приложения появится сообщение об ошибке, в котором говорится о необходимости размещения компонента TADGUIxWaitCursor, отвечающего за контроль над курсором мыши во время долгих операций. Также добавим и компонент TADPhysOraclDriverLink (драйвер доступа к ORACLE), иначе повторный запуск нашей программы опять закончится неудачей.

ЗАКЛАДКА «Табличные пространства»

База данных ORACLE, упрощенно говоря, – это набор табличных пространств, каждое из которых представляет собой логическую структуру для хранения однотипных данных. Рассмотрим пример. Предположим, у нас в базе хранится таблица TABLE1, которую мы размещаем в табличном пространстве DATA, а индексы для данного объекта – в табличном пространстве INDEX, причем оба табличных пространства физически находятся на разных винчестерах. Таким образом, правильно задавая логическую структуру базы, мы можем прямо влиять на ее производительность.

Полностью интерфейс закладки «Табличные пространства» вы можете найти в исходниках программы, а здесь мы лишь обратим внимание на извлечение из базы необходимой информации.

Нам потребуется 4 компонента TADQuery (для каждого – по TDataSource), описание их настройки представлено ниже:

1) выборка всех табличных пространств и их статуса (QTablespaceInfo - DSTablespaceInfo):

SELECT TABLESPACE_NAME,STATUS from dba_tablespaces ORDER BY TABLESPACE_NAME

Результат выводится в TDBGrid, расположенный под текстом «Список табличных пространств»;

2) выборка файлов данных принадлежащих табличному пространству (QSostav – DSSostav)

SELECT FILE_NAME, BYTES/1024/1024 as BYTES FROM DBA_DATA_FILES WHERE TABLESPACE_NAME=:par

Результат выводится в TDBGrid, расположенные под текстом «Файлы табличного пространства»;

3) определение общего размера табличного пространства

SELECT SUM(BYTES/1024/1024) FROM DBA_DATA_FILES WHERE TABLESPACE_NAME=:par

Результат выводится в TDBEdit, расположенные рядом с текстом «Общий размер (МБ):»;

4) определение свободного размера табличного пространства

SELECT sum(bytes/1024/1024) from dba_free_space where tablespace_name=:PAR

Результат выводится в TDBEdit, расположенные рядом с текстом «Свободно (МБ):».

Так как сведения о табличных пространствах могут меняться с течением времени, то мы будем периодически с помощью таймера обновлять информацию. Разместим на модуле данных вышеописанный компонент, назовем его TimerStructureBD. Свойство Enabled установим в False, а свойство Interval сделаем равным 5000. Напишем ему следующий обработчик:

Обработчик для TimerStructureBD

procedure TDM.TimerStructureBDTimer(Sender: TObject); begin QTablespaceInfo.Close; QTablespaceInfo.Open; MainForm.DBGridStructureCellClick(nil); end;

Теперь перейдем к форме и для TDBGrid, отражающей результат первого запроса, назначим следующий код:

Получение дополнительной информации для выбранного пользователем табличного пространства

procedure TMainForm.DBGridStructureCellClick(Column: TColumn); begin DM.QItogo.Close; DM.QItogo.Params.ParamByName('par').Value:=DM.QTablespaceInfo.Fields[0].Value; DM.QItogo.Open;

DM.QFree.Close; DM.QFree.Params.ParamByName('par').Value:=DM.QTablespaceInfo.Fields[0].Value; DM.QFree.Open;

DM.QSostav.Close; DM.QSostav.Params.ParamByName('par').Value:=DM.QTablespaceInfo.Fields[0].Value; DM.QSostav.Open; end;

Как видите, здесь мы просто передаем в качестве параметра имя табличного пространства, для которого нужно отобразить более детальную информацию, и открываем набор.

Однако есть тут и одно «но» в виде табличного пространства TEMP, которое используется для операций сортировки, а также содержит временные таблицы, индексы, созданные при выполнении SQL-запросов. Для определения его общего размера и количества свободного места в нем нужно будет использовать другие запросы:

select file_name,bytes/1024/1024 from dba_temp_files select (bytes_cached)/1024/1024 as FreeSpace from v$temp_extent_pool

Я оставил реализацию этого действия на ваше самостоятельное исполнение. Теперь нам необходимо создать обработчик OnChange для компонента TPageControl, в котором будет проверяться выбранная пользователем закладка, и если это «Табличные пространства», то будет производиться включение таймера TimerStructureBD:

Проверка активной закладки

procedure TMainForm.PageControl1Change(Sender: TObject); begin //Если выбрана закладка СТУКТУРА БД, то включаем соответствующий таймер if PageControl1.ActivePage.Caption='Структура БД' then begin //Сначала вызываем DM.TimerStructureBDTimer(self); //Потом включаем таймер (чтобы пользователь не ждал данные лишние 5 секунд) DM.TimerStructureBD.Enabled:=true end else DM.TimerStructureBD.Enabled:=false; end;

Закладка «Сессии»

Откройте пример программы, расположенной на компакт-диске, чтобы привести интерфейс закладки к нужному виду. Для получения данных нам понадобиться 6 компонентов TADQuery и для каждого из них по TDataSource; информацию о том, как все это настроить вы найдете ниже:

1) выборка всех сессий (QSession - DSTablespaceInfo):

select username,sid,osuser,machine,program,terminal,logon_time from v$session where username is not null order by username

Результат выводится в TDBGrid, расположенный под текстом «Список сессий»;

2) получение запроса выполняемого сессией (QSQL – DSSQL)

select * from v$sqlarea,v$session where v$session.sql_address = v$sqlarea.address and v$session.username=:par

Результат выводится в TDBMemo, расположенные под текстом «SQL-запрос, выполняемый в сессии»;

3) общее количество сессий (QCountSession – DSCountSession)

select * from v$sqlarea,v$session where v$session.sql_address = v$sqlarea.address and v$session.username=:par

Результат выводится в TDBText, расположенный рядом с меткой «Всего открыто сессий»;

4) общее количество активных сессий (QActiveSession – DSActiveSession)

select count(*) from v$session where username is not null and status = 'ACTIVE'

Результат выводится в TDBText, расположенный рядом с меткой «Из них активных»;

5) размер PGA выделенной для обслуживания сессии пользователя (PGA – это участок памяти, называемый глобальная область процесса).

SELECT VALUE FROM V$SESSION sess, V$SESSTAT stat, V$STATNAME name WHERE sess.SID=stat.SID AND stat.STATISTIC#=name.STATISTIC# AND name.NAME='session pga memory' and sess.sid:=par

Результат выводится в TDBText, расположенный рядом с меткой «PGA для сессии»;

6) размер PGA выделенной сессии (данная область памяти хранит информацию о состоянии сеанса или сессии)

SELECT VALUE FROM V$SESSION sess, V$SESSTAT stat, V$STATNAME name WHERE sess.SID=stat.SID AND stat.STATISTIC#=name.STATISTIC# AND name.NAME='session uga memory' and sess.sid=:par

Результат выводится в TDBText, расположенный рядом с меткой «UGA для сессии».

Остался последний штрих – добавление графики. Мы будем выводить количество активных сессий в виде диаграммы, для чего воспользуемся очень удобным и (что немаловажно) абсолютно бесплатным компонентом Trend, который можно скачать с torry.net или взять на диске.

После того как данный компонент будет инсталлирован в палитру Delphi, разместим его в удобном месте, далее в свойстве Style установим значение 3D. Нам понадобится TTimer на модуле данных (назовем его TimerUser), свойство Enabled установим равным false, а Interval равным 5000 и напишем для TTimer необходимый код.

Создадим обработчик для TDBGrid, расположенный под текстом «Список сессий», этот компонент называется у меня DBGridUsers. Листинг вы найдете в исходниках к программе. Также потребуется модифицировать обработчик OnChage компонента TPageControl, чтобы при активизации закладки «Сессии» включался таймер TimerUser (этот код вы также найдете на компакт-диске).

Закладка «Сессии» в действии

Закладки «Блокировки», «Пользователи», «Проверка паролей»

В ORACLE можно углубляться бесконечно долго, но поскольку сделать это в масштабах одной статьи не представляется возможным, кратко рассмотрим реализацию остальных закладок (точнее, запросы, которые для них понадобятся). Необходимый код и законченное приложение вы найдете на компакт-диске.
Если один из пользователей установил блокировку на таблицу, то другие пользователи не смогут вносить туда данные и, возможно, даже просматривать их (все зависит от типа блокировки), для таких случаев на помощь придет запрос:

select s.OBJECT_ID,SESSION_ID,ORACLE_USERNAME,OWNER,OBJECT_NAME,OBJECT_TYPE from v$locked_object s,dba_objects k where s.object_id=k.object_id;

Привилегии пользователей регулируются следующим механизмом:

1) объектные роли (например, на таблицы)

select OWNER,TABLE_NAME,PRIVILEGE,GRANTABLE from dba_tab_privs where grantee=:par

2) роли, созданные DBA

SELECT GRANTED_ROLE,ADMIN_OPTION,DEFAULT_ROLE from DBA_ROLE_PRIVS where grantee=:par order by GRANTED_ROLE

3) системные роли (присутствующие в ORACLE и разрешающие выполнять различные рода операции)

SELECT PRIVILEGE,ADMIN_OPTION from DBA_SYS_PRIVS WHERE GRANTEE=:par

Логины всех пользователей, зарегистрированных в базе, можно получить по следующему запросу:

SELECT USERNAME, ACCOUNT_STATUS from DBA_USERS order by USERNAME;

В ORACLE, как и в любой другой СУБД, есть встроенные учетные записи, на которые идут стандартные пароли, и очень важно их поменять либо вовсе заблокировать.

Конечно, в жизни часто встречаются случаи, когда эту, на первый взгляд, элементарную операцию просто забывают сделать. Чтобы быть уверенным в завтрашнем дне и не оставлять лишние лазейки злоумышленникам, просто выполните следующий запрос:

select username,account_status from dba_users where password in ( 'E066D214D5421CCC', -- dbsnmp '24ABAB8B06281B4C', -- ctxsys '72979A94BAD2AF80', -- mdsys 'C252E8FA117AF049', -- odm 'A7A32CD03D3CE8D5', -- odm_mtr '88A2B2C183431F00', -- ordplugins '7EFA02EC7EA6B86F', -- ordsys '4A3BA55E08595C81', -- outln 'F894844C34402B67', -- scott '3F9FBD883D787341', -- wk_proxy '79DF7A1BD138CF11', -- wk_sys '7C9BA362F8314299', -- wmsys '88D8364765FCE6AF', -- xdb 'F9DA8977092B7B81', -- tracesvr '9300C0977D7DC75E', -- oas_public 'A97282CE3D94E29E', -- websys 'AC9700FD3F1410EB', -- lbacsys 'E7B5D92911C831E1', -- rman 'AC98877DE1297365', -- perfstat 'D4C5016086B2DC6A', -- sys 'D4DF7931AB130E37' -- system ) order by username

В помощь DBA

Любому администратору ORACLE или разработчику знакома ситуация, когда с виду правильно составленный SQL-запрос не выполняется, а вызывает ошибку с префиксом ORA. Как правило, из этой скромной информации вы не всегда можете понять, что не так, и тут на помощь приходит специальная утилита от Thamerlan, под названием Oracle Error Messages Viewer, содержащая подробное описание причины ошибки и действий необходимых для ее устранения. Официальный сайт http://tham.eclub.lv, программа абсолютно бесплатная, и вы можете ее найти на компакт-диске, в каталоге с исходником программы.

Послесловие

Мир ORACLE поистине безграничен, и даже самый изощренный специалист всегда может найти в нем что-то новое для себя. Что касается администрирования, то, на мой взгляд, ни какие утилиты не заменят работу настоящего DBA, так как в критической ситуации решение все равно придется принимать человеку, а чтобы быть готовым к непредвиденным ситуациям, нужно знать и понимать с чем вы работаете, и чем глубже и полней ваша осведомленность, тем более уверенней вы будете чувствовать себя в завтрашнем дне, а следовательно, тем надежней будет обеспечена работоспособность ваших серверов.

Полезные ресурсы

www.databasejournal.com/features/article.php/3593471 – электронный журнал, посвященный ORACLE.
www.dsvolk.ru/oracle/dba/ – полезная информация для ORACLE DBA.
www.orafaq.org – название ресурса говорит само за себя.
www.oracle.com/ru/oramag/ – электронная версия популярного журнала Oracle Magazine RE.
www.oracle.com/technology/documentation/ – документация по Oracle.
www.psoug.org/library.html – полезные решения на SQL и PL/SQL для Oracle.
http://garshin.ru/it/oracle.html – электронная книга Константина Гаршина, посвященная Oracle.
www.oradba.com.ru/index.shtml – полезная информация для администратора и программиста баз данных Oracle.
http://mab.h1.ru/ADMIN/ – руководство администратора Oracle.
http://surgutnet.ru/list.php?c=oracle – набор статей и полезных советов для Oracle.
www.oraclub.ru/ – клуб любителей Oracle.
www.red-database-security.com/software/checkpwd.html – переборщик паролей для ORACLE.

Обсуждение статьи

Guest guest@gameland.ru Отправлено: 13.05.2010 4:46:13

RE: Delphi и Oracle: разрабатываем утилиту для администрирования ORACLE
I like this site very much Nice design site replica watches Our prestigious fake franck muller watches The Best Service

Guest guest@gameland.ru Отправлено: 11.10.2010 4:47:21

RE: Delphi и Oracle: разрабатываем утилиту для администрирования ORACLE
The printing machinery network (www.yja.com.cn), founded in 2001, is now the largest domestic printing machinery industry vertical B2B e-commerce websites. Value-added telecommunication business license Numbers for: hubei B2-20050125. For printing and china printing machine related enterprise products trade and information exchange interaction bridge erection. It has comprehensive application foreground and spread value for label printing machine,flexo printing machine.And it is the biggest online printing machinery display platform. The printing machinery network make full use of the advantages of the Internet, distinctive "Network + publications" , facing the whole country and the world, to provide professional enterprise online, offline various forms of professional trade service.

Страницы: