Oracle’ da Audit Mekanizması

Kurumsal firmalarda uzun bir zamandır hem database hemde operating sytem seviyesinde şirket için gizli ve değerli bilgilerin bir takım kullanıcılar tarafından şirklet dışına çıkartılması veya bu bilgilerin şirket  içerisinde kötü amaçlarla kullanılmasnı önlemek için firmalar çok çeşitli yöntemler kullanmaya başlamışlardır.  Bir oracle dba olarak burada database seviyesinde kullanıcıları nasıl  izleyebiliriz, bunu yaparken nelere dikkat etmeliyiz gibi bir takım teknik konulara değineceğiz.

Oracle ‘ daki audit mekanizmasının biraz tarihçesine inersek, 1992 yılında oracle 7 sürümüyle tüm audit özelliklerini içeren ilk veritabanı olma, 1994 yılında ise bağımsız güvenlik kuruluşlarından onay alan ilk veritabanı olma özelliiğinede sahiptir. Bu açıdan düşünüldüğünde oracle veritabanı diğer ilişkisel veritabanları arasında güvenilirliğini ile ilk sırada yer almayı başarmış bir veritabanıdır.

Peki Auditing nedir ?  Özetle, seçilen bazı userların (veya hepsinin) database içerisinde yapmış olduğu aktivitelerin monitör edilmesi ve sonra kayıt altına alınarak saklanması işlemine auditing diyebiliriz. Bu monitöring ve kayıt altına alma işlemi, kullanıcı adı, işlemin yapıldığı an, hangi uygulama kullanılarak yapıldığı, hangi makine üzerinden sisteme giriş yapıldığı gibi bilgileri kapsar. Bir işlemin auditlenmesi için mutlaka başarılı olması gerekmemektedir, başarısız olan tüm işlemlerde izlenebilmektedir. (başarız olan işlemler kimi  zaman başarılı olanlardan daha fazla  önem taşırlar. Sürekli olarak birinin, sistemde yer alan bir kullanıcı adı ile sisteme giriş yapmayı denemesi şüphelenmek ve araştırmak için yeterli bir nedendir.)  

Neden  Aduting yapılmaya ihtiyaç duyulur ? Bunun nedenleri ana hatlarıyla ;

·                  Yapılan İşlemlere Ait Sorumluları Tespit Etmek İçin; 

·                  Davetsiz Misafirleri Caydırmak İçin ,

·                  Şüpheli İşlemleri Araştırmak için,

·                  Yetkisiz Kullanıcıya Ait İşlemleri Tespit Etmek ve Denetime Bildirmek İçin,

·                  Özel bir database işlemi ile ilgili istatistik toplama ve monitor etmek için,

·                  Yetki ve Erişim Kontolleri ile Problemleri Tespit Etmek İçin.

Neden auditing yapacağımıza karar verdikden sonra,  karar verilmesi gereken 2 konu karşımıza çıkmaktadır. Bunlardan birincisi, nelerin audit edileceğine ikincisi ise audit kayıtlarının nerede saklanacağına karar vermekteir.  Database’ deki her oturumu her işlemi auditlememelisiniz, sonuçta bu kayıtların tutulması için de bir kaynak kullanılması gerekmektedir ki, audit kayıtları yanlış bir konfigurasyonla çok hızlı büyüyebilir ve sizi olmadık bir anda zor  durumda bırakabilir.  Sorun sadece kaynak problemi değildir aslında, yapılan auditing işleminin sisteme getirdiği ek bir performans kaybı olmaktadır. Dolayısıyla herşeyi auditlemek  kimi  zaman  çözüm olmakdan uzak bir hal almaktadır. Bir diğer neden ne kadar çok auditin işlemi yaparsanız o kadar çok incelemeniz, denetlemeniz gereken kayıt ortaya çıkacaktır.

Audit işlemleri ile ilgili olarak nasıl yapılacağı konusu aslında ne tarz bir auditing metodu uygulayacağınızla birebir ilişkilidir.  Audit yöntemlerinden bahsedelim biraz;

1.              Genel Aktivitilerin Standart Auditing ile Auditlenmesi  ; 

Satndart auditing, SQL statamentlerın, yetkilerin, schema objelerinin ve networkdeki aktivitilerin auditlenmesidir. Standart auditing AUDIT komutu ile başlatılır, NOAUDIT komutu ile sonlandırılır.  Bu auditing işlemi AUDIT ANY yetkisi ile AUDIT SYSTEM yetkisine sahip herhangi bir kullanıcı yapabilir.

Audit kayıtları, güvenlik işinden sorumlu adminin database seviyesinde auditingi enable etmesiyle başlar. Auditing enable edilmesiyle , database çalışan SQL statementının çalışmasından sonra bir audit kaydı üretir. Üretilen bu audit kaydı kullanıcının transactionı commit etmesinden bağımsızdır. Aynı şekilde başlatılan bir rollback işlemide yine yeni bir audit kaydı oluşturur.

Burada AUDIT_TRAIL parametresi önemlidir, database tarafından tespit edilen audit kayıtlarının nerede ve nasıl saklanacağının belirlendiği parametredir. Bu parametrenin şu anki değerini görmek için ;

SQL> show parameter audit_trail;

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

audit_trail                          string      NONE

Şu an bu değer NONE olduğundan dolayı auditing özelliği bu database’ de disable anlamına gelmektedir. AUDIT_TRAIL parametresi statik bir parametre olduğundan dolayı yapılan değişikliğin geçerli olması için database’ in restart olması gerekmektedir. Değişikliği yapmak içinse;

SQL> show user

USER is "SYS"

SQL> ALTER SYSTEM SET AUDIT_TRAIL=DB SCOPE=SPFILE;

System altered.

SQL> shutdown immediate ;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup

ORACLE instance started.

Total System Global Area  419430400 bytes

Fixed Size                  1249368 bytes

Variable Size              96473000 bytes

Database Buffers          314572800 bytes

Redo Buffers                7135232 bytes

Database mounted.

Database opened.

SQL>

Komutlarından faydalanılabilir. Buarada audit_trail parametresini “db” olarak set ettik. Bunun anlamı ve diğer alternatiflerini şöyle özetleyebiliriz ;

DB ;  Audit kayıtlarının database’ de tutulması anlamına gelir. Bu kayıtlar database’ de sys.aud$ tablosunda saklanır.  (sys userına ait audit kayıtları hariç, sys userına ait audit kayıtlar OS’ ye yazılır)  Audit_trail parametresi DB olarak set edili iken database’ i read only modda açmak isterseniz, database size aşağıdaki gibi hata dönecektir. Bunun nedeni, audit_trail db olarak set edili iken database’ i read only olarak açmak istiyorsanız önce audit_trail parametresini ya OS yada NONE olarak set etmeniz gerektiğidir (read only  modda iken  audit kayıtlarını database’ e write edemeyeceğinden dolayı).  

SQL> startup mount

ORACLE instance started.

Total System Global Area  419430400 bytes

Fixed Size                  1249368 bytes

Variable Size             100667304 bytes

Database Buffers          310378496 bytes

Redo Buffers                7135232 bytes

Database mounted.

SQL> alter database open read only

  2  /

alter database open read only

*

ERROR at line 1:

ORA-16006: audit_trail destination incompatible with database open mode

SQL> select open_mode from v$database ;

OPEN_MODE

----------

READ WRITE

SQL> show parameter audit_trail;

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

audit_trail                          string      DB

SQL>

OS ; Audit kayıtları operating sistemde belirtilen bir path’ de saklanır. Buraya file bazında çıkan dosyalar .aud uzantılı  olur. Burada iki farklı parametre daha var set etmemiz gereken, AUDIT_FILE_DEST parametresi audit kayıtlarının nerede tutulacağının set edildiği parametredir. Aşağıdaki şekilde set edilebilir;

alter system set AUDIT_FILE_DEST ='d:\oracle11gR2\audit' scope=spfile;

Diğer parametre SYS userının auditlenip auditlenmemesi ile ilgili, eğer SYS userının sessionlarıda auditlenecekse  AUDIT_SYS_OPERATIONS parametresi TRUE olarak set edilmelidir.

Alter system set  AUDIT_SYS_OPERATIONS=TRUE scope=spfile;

10g’ de olmayan, 11g ile yeni gelen bir parametremiz daha var;  AUDIT_SYSLOG_LEVEL parametresi. Bu parametre sadece Unix ortamlarda kullanılmaktadır. Bu parametre manuel olarak initSID.ora dosyasına eklenilerek set edilebilir. İki farklı değer alabilir;

Facility; İşletim sistemi' nin, mesajı loglayan bölümünü ifade eder. Kabul ettiği değerler; user, local0–local7, syslog, daemon, kern, mail, auth, lpr, news, uucp ve cron.  

Local0 – local7 arasındaki değerler, syslog mesajlarını kategorize etmemize olanak sağlayan önceden tanımlanmış etiketlerdir. Bu kategoriler, syslog aracının ulaşabileceği log dosyaları veya  diğer dizinler olabilir. Bu etiket tipler ile ilgili ayrıntılı bilgi için, syslog aracının MAN sayfasına bakabilirsiniz.

Priority ; Mesajın öncelik derecesini belirler.  Kabul ettiği değerler, notice, info, debug, warning, err, crit, alert ve emerg’ dir.

Bu parametreyi database’ e set  etmek için ;

ALTER SYSTEM SET AUDIT_TRAIL=DB SCOPE=SPFILE;

Komutundan faydalanabiliriz.

DB, EXTENTED ;  Audit kayıtları sys.aud$ tablosuna kaydedilir aynı zamanda Sqlbind değerleri ile sqltext’ deki Clob bilgileride bu tabloda tutulur.

Aşağıdaki gibi 2 farklı şekilde set edilebilir.

ALTER SYSTEM SET AUDIT_TRAIL=DB, EXTENDED SCOPE=SPFILE;

ALTER SYSTEM SET AUDIT_TRAIL='DB','EXTENDED' SCOPE=SPFILE;

Aşağıdaki gibi bu iki kriteri tek tırnak içerisinde kullanılmamalıdır;

ALTER SYSTEM SET AUDIT_TRAIL='DB, EXTENDED' SCOPE=SPFILE;

XML ;  Auditing kayıtları operating system de xml dosyası olarak saklanır. XML formatındaki audit kayıtlarının default lokasyonu windows’ da dahil olmak üzere tüm platformlarda; $ORACLE_HOME/admin/$ORACLE_SID/adump  lokasyonudur.

Sys ve zorunlu audit dosyalarını operating sisteme XML fromatında yazmak için ; Audit_trail = XML or XML,ExTENDED, AUDIT_SYS_OPERATIONS=TRUE fakat AUDIT_SYSLOG_LEVEL parametresi set edilmemiş olmalıdır.

Sys ve zorunlu audit kayıtlarını syslog Audit file’ lerine, Standart Audit kayıtlarını XML Audit file’ lerine yazmak için;  Audit_trail = XML or XML,ExTENDED, AUDIT_SYS_OPERATIONS=TRUE ve AUDIT_SYSLOG_LEVEL parametreside set edilmiş olmalıdır.

Bu parametreyi database’ e set  etmek için ;

ALTER SYSTEM SET AUDIT_TRAIL=XML SCOPE=SPFILE;

Komutundan faydalanabiliriz.

XML, EXTENTED ;  Auditing kayıtları operating system de xml dosyası olarak saklanır aynı zamanda Sqlbind değerleri ile sqltext’ deki Clob bilgileride burada saklanır.

Bu parametreyi set etmek içinde aşağıdaki iki komuttan biri kullanılabilir.

ALTER SYSTEM SET AUDIT_TRAIL=XML, EXTENDED SCOPE=SPFILE;

ALTER SYSTEM SET AUDIT_TRAIL='XML','EXTENDED' SCOPE=SPFILE;

Aşağıdaki gibi çalıştırılmamalıdır;

ALTER SYSTEM SET AUDIT_TRAIL='XML, EXTENDED' SCOPE=SPFILE;

NONE ;  Auditing disable anlamına  gelir.

Audit_trail parametresini DB olarak set ettiğimizde belirlediğimiz audit kriterlerine uygun sessionlar geldikçe bu tabloda dolmaya başlayacaktır.

select sessionid, userid, userhost, terminal, action#  from sys.aud$

SESSIONID            USERID  USERHOST                          TERMINAL           ACTION#

14708                    KAMIL   WORKGROUP\KHOME   KHOME                               101

Burada action alanı çalıştırılan sql komutunun kodunu ifade etmektedir. Hangi kodun hangi komuta denk geldiğini aşağıdaki sorgu ile çekebilirsiniz.

Select * from AUDIT_ACTIONS ;

Ile sorgulayabilirsiniz.

Biraz da  AUDIT ve NOAUDIT komutlarının nasıl çalıştığından bahsedelim.

Audit veya Noaudit komutları sisteme gönderilirken komut sonlarında yer alan aşağıdaki 3 parametre önemlidir;

·         WHENEVER SUCCESSFUL cümlesi; herhangi bir audit veya noaudit komutunun sonunda bu cümle yer alıyorsa, çalıştırılan komut başarılı olmak kaydıyla auditleneceği   veya auditlenmeyeceği  anlamına gelir. Yani başarısız olan komut üzerinde işlem yapılmaz.

·         WHENEVER NOT SUCCESSFUL cümlesi; Eğer komutun sonunda bu cümle yer alıyorsa, başarısız olan komutların auditleneceği veya auditlenmeyeceği anlamına gelir. Başarılı olan komut üzerinde işlem yapılmayacak demektir.

·                  Her İkiside kullanılmazsa; Çalıştırılan Audit/Noaudit komutu içerisinde yukarıda belirtilen kısımlar kullanılmaz ise, hem başarılı hemde başarız tüm işlemlerin auditleneceği/auditlenmeyeceği anlamına gelir.

Örneğin;

AUDIT CREATE TABLE BY ACCESS WHENEVER NOT SUCCESSFUL;

Bu script ile create table  scriptini çalıştıran tüm userlar içerisinde sadece başarız olanlar  (çalıştırdığı komutu hata verenler)  auditlenecek demektir.

Oracle audit komutlarında  BY ACCSESS  cümleciğini  kullanmayı önerir.  Oracle 11gR2 ile birlikte audit komutunun sonunda by accsess cümleciği kullanılmasa bile defaultunda yer aldığı için kullanılacaktır. Bu komutu kullanmanın bir takım avantajları vardır.  Bu avantajları özetleyecek olursak ;

·                  BY ACCSESS komutu kullanıldığında, elde edilen audit kayısında daha fazla bilgi yer alacaktır. Örneğin, execution status (return code), execution zamanı vetarihi,  kullanılan privilege,  sql text’ i ve bind variable değerleri gibi . Ek olarak BY ACCSESS kullanımı ile yapılan işleme ait SCN ‘ ıda kaydettiğinden geri dönüş gibi bir aksiyon alınması gerektiğinde bu bilgi ile Flashback komutları kullanılarak çok rahat eski veriye ulaşım sağlanabilecektir.

·                  Oracle database’ i çalışan her bir sql statement’ını, kullanılan bir privilege ‘ ı ve auditlenen her nesneye erişimi kaydeder. Bu kayıtlar neticesinde bu işlemin kaç defa yapıldığını bulmanıza da yardımcı olur.

·                  BY ACCSESS audit kayıtlarında oturum açma ve kapatma kayıtlarını fine-grained zamanları ile birlikte tutar.

BY ACCSESS kullanımına örnek olarak ;

AUDIT SELECT TABLE BY ACCESS;

Spesifik bir Usera ait işlemlerin Auditlenmesi ;

Örneğin, Kamil ve Hakan userının çalıştırmış olduğu tüm selectler ile Updateleri auditlenmesini istersek;

AUDIT SELECT TABLE, UPDATE TABLE BY KAMIL,HAKAN BY ACCESS;

Başlıklar altında örnek scriptler  başlangıçda az gibi algılanabilir.

NOAUDIT Komutunun Çalıştırılması ;

Audit edilen bir nesne , system privilege, veya bir kullanıcının  audit edilmesini n kaldırılması, iptal edilmesini sağlar. NOAUDIT komutunda   WHENEVER SUCCESSFUL  ve   WHENEVER  NOT SUCCESSFUL   kullanımına dikkat edilmelidir,  burada da AUDIT’ dekine benzer bir kullanım sözkonusudur, bu cümle NOAUDIT komutunun sonunda kullanılmazsa başarılı/başarısız tüm işlemler audit kapsamı dışına çıkarılmış  olur.

Audit işlemlerleri ile ilgili olarak konu içerisinde vermiş olduğum örnekler ileride konular detaylandıkça artacağından şu  aşamada konunun anlaşılması için yeterli olacağını düşünüyorum.

SQL Cümlelerinin Auditlenmesi ;

Database de yapılan tüm selectleri ayırt etmeksizin auditlemek istersek ;

AUDIT SELECT TABLE BY ACCESS;

komutundan faydalanabiliriz.

Eğer  tüm SQL komutlarını auditlemek istiyorsanız, user connectionlarını veya var olmayan nesnelere ait referanslar için aşağıdaki adımları  izleyebilirsiniz;

·                  Userların tüm SQL Statement’ larının Auditlenmesi ; ALL STATEMENTS cümleciği ile sadece top-level SQL sorguları auditlenebilir. Bu audit seçeneği diğer audit seçeneklerinden farklıdır. Eğer sql statement’ ı pl/sql proceduru içerisinden çalıştırılıyorsa,  All Statement opsiyonu bunları auditlemeyecektir. Bu audit opsiyonu diğer set edilmiş olan audit opsiyonlarından etkilemez.

Kullanımı ile örnek ;

               

      AUDIT ALL STATEMENTS BY KAMIL BY ACCESS WHENEVER SUCCESSFUL;

·                  Userlar tarafından çalıştırılan tüm Sql Statement’ larının (Komutların Kısayolları İl e) Auditlenmesi ; 

Burada ifade edilmek istenen İndex, Procedure, Database Link vs. gibi bazı komutların kısayolları ile bu komutlar ile yapılan   tüm işlemleri auditleyebilirsiniz.  Oracle’ da çok fazla sayıda komut olduğundan dolayı komutlara ait  kısayolları buraya yazmaktansa merak edenler aşağıdaki linkden bunlara ulaşabilirler;

http://download.oracle.com/docs/cd/E11882_01/server.112/e17118/statements_4007.htm#SQLRF53735

Bu konuyla ilgili örnek olarak;

        AUDIT ALL BY KAMIL BY ACCESS;

verebiliriz.

   

·                  Kullanıcı Ayıt Etmeksizin Geçerli Oturumdaki Tüm SQL Statementlarının Auditlenmesi ; All Statement audit seçeneği için IN SESSION CURRENT cümleciği ile top-level sql statementlarını session sonlanmadığı sürece audit edebilirsiniz. Bu auditi NOAUDIT ile sonlandıramazsınız fakat user sessionı discconect olduğunda zaten auditte sonlamış olacaktır.

Örneğin, mevcut bir sessinındaki tüm işlemleri auditlemek için ;

         AUDIT ALL STATEMENTS IN SESSION CURRENT BY ACCESS WHENEVER NOT SUCCESSFUL;

komutunu  kullanabilirsiniz.

·                  Login ve logout’ ların Audilenmesi ;  İsterseni z AUDIT_SESSION komutu ile tüm login ve logooutları auditleyebilirsiniz.

Örneğin,

        AUDIT SESSION BY ACCESS;

Sadece belli bir kullanıcının login ve logoutlarını auditlemek  isterseniz,

        AUDIT SESSION BY kamil  BY ACCESS;

Komutunu kullanabiliriz.

·                  Object doesn’ t exist hatasıyla fail olan sql statementlarının Auditlenmesi ;  NOT EXIST  cümleciği ile object  doesn’ t exist hatasıyla  fail  olan sorguları auditleyebilirsiniz.

Örneğin,

AUDIT  NOT  EXIST ;

SQL Stamentları ile İlgili Audintingleri  Sonlandırmak İçin;

NOAUDIT komutu ile SQL statementları üzerindeki auditingleri sonlandırabiliriz.  Bu komutu çalıştırmadan önce mutlaka çalıştıracak olan kullanıcının AUDIT SYSTEM yetkisine sahip olmalıdır.  Audit All Statements seçeneği ile set edilmiş olan autiler, Noaudit Audit Statements  ile kaldırıldığında, bu işlemden diğer audit konfigurasyonları etkilenmeyecektir. Daha öncede belirttiğimiz üzere In Session Current cümleciği ile set edilmiş olan auditler Noaudit ile geri alınmazlar, bu auditing işlemi session sonlandığında  bitecektir.

Noaudit  ile ilgili  örnek olarak aşağıdaki komutları verebiliriz ;

NOAUDIT session;

NOAUDIT session BY  kamil;

NOAUDIT DELETE ANY TABLE;

NOAUDIT SELECT TABLE, INSERT TABLE, DELETE TABLE, EXECUTE PROCEDURE ;

NOAUDIT ALL STATEMENTS;

Privilege ile İlgili Audit Seçeneklerinin Configure Edilmesi ;

Haklar ile ilgili  audit seçenekleri,  ilgili   ayrıcalığın ismine karşılık gelen sistem hakları ile aynıdır. Örneğin,  Delete any table komutunun auditlenmesi ;

AUDIT DELETE ANY TABLE BY ACCESS WHENEVER NOT SUCCESSFUL;

AUDIT DELETE ANY TABLE BY ACCESS;

AUDIT SELECT TABLE, INSERT TABLE, DELETE TABLE, EXECUTE PROCEDURE BY ACCESS WHENEVER NOT SUCCESSFUL;

şeklindedir.

Bu auditler üzerindeki auditing işleminin kaldırılması ise, yine benzer bir mantıkla ;

NOAUDIT ALL PRIVILEGES;

NOAUDIT SELECT TABLE, INSERT TABLE, DELETE TABLE, EXECUTE PROCEDURE;

Schema Objelerinin Auditlenmesi ;

Konuyu biraz daha spesifiklendirebiliriz, yani tüm select komutlarını auditlemek istemiyde sadece x userının altındaki bir tabloyu auditlemek isteyebiliriz.  

AUDIT SELECT ON owner.tablename BY ACCESS;

AUDIT SELECT ON owner.viewname BY ACCESS;

AUDIT DELETE ON laurel.emp BY ACCESS;

AUDIT SELECT, INSERT, DELETE ON hr.dept BY ACCESS WHENEVER SUCCESSFUL;

AUDIT SELECT ON DEFAULT BY ACCESS  WHENEVER NOT SUCCESSFUL;

AUDIT EXECUTE ON procedure_name BY ACCESS;

AUDIT INSERT TABLE BY ACCESS;

Schema Objelerinin Auditlerinin Kaldırılması ;

Schema nesneleri üzerindeki auditingi kaldırmak için yine Noaudit komutundan faydalanabiliriz ;

NOAUDIT DELETE  ON emp;

NOAUDIT SELECT, INSERT, DELETE  ON hr.dept;

NOAUDIT ALL ON emp;

NOAUDIT ALL  ON  DEFAULT;

Bu rada ON DEFAULT  cümleciğinden bahsetmek gerekebilir.  Aşağıdaki  örnek üzerinden  açıklamaya çalışalım.

AUDIT ALL ON DEFAULT BY ACCESS;

Bu komut ile aşağıdaki komutların hepsi etkilenecektir.

Alter , Execute, İnsert, Select , Audit, Grant, lock, Update, Comment, Flashback, Read, Delete, Index, Rename .

On default yerine spesifik olarak neleri auditleyeceğinizi siz belirlemek isterseniz, örneğin ;

AUDIT ALTER, DELETE ON DEFAULT BY ACCESS;

İle yapabilirsiniz.

Directory’ lerin Auditlenmesi ;

Directoryler içerisindeki nesneleri auditleyebilirsiniz. Örneğin,  bir directory  içerisine  create ettiğiniz bir programı, kimin çalıştırdığını auditleyebilirsiniz.  Bunu yaparken aşağıdaki komuttan faydalanabiliriz ;

AUDIT EXECUTE ON DIRECTORY my_directory BY ACCESS;

Audit işlemini iptal etmek içinse ;

NOAUDIT EXECUTE ON DIRECTORY my_directory;

komutunu kullanabiliriz.

Tüm Function, Procedure, Package ve Trigger’ ları Audit Etmek İçin;

AUDIT EXECUTE PROCEDURE BY ACCESS;

User bazında bu işlemi yapmak istersek ;

AUDIT EXECUTE PROCEDURE BY psmith BY ACCESS;

Schema İçerisinden Procedure veya Function Execute Edildiğinde Auditlemek İçin ;

AUDIT EXECUTE ON HR.check_work BY ACCESS WHENEVER SUCCESSFUL;

Function, Procedure , Packages  ve Trigger’ lar  Üzerindeki Auditingi Kaldırmak İçin ;

Noaudit komutu ile kaldırılabilir.

NOAUDIT EXECUTE PROCEDURE;

NOAUDIT EXECUTE PROCEDURE BY kamil;

NOAUDIT EXECUTE ON sales_data.checkwork;

Networkü Auditlemek İçin ;

Network üzerinden yapılan işlemleri auditlemek istiyorsak ;

AUDIT NETWORK BY ACCESS;

Bu auditingi kaldırmak içinse ;

NOAUDIT NETWORK;

komutlarından yararlanabiliriz.

2.              Güvenlik ile İlgili SQL Komutlarının ve Yetkilerin Varsayılan Olarak Auditlenmesi ;

Çok kullanılan bir yöntem olmamakla beraber , bu konuya da açıklık  getirmekte fayda var. Dbca ile database’ i create ederken, oracle database’i  auditi çok sık kullanılan güvenlikle ilgili  sql statementlarını ve haklarını  auditleyecek şekilde set eder.

Oracle database default olarak aşağıdaki yetkileri auditler.

ALTER ANY PROCEDURE	CREATE ANY LIBRARY	DROP ANY TABLE
ALTER ANY TABLE	CREATE ANY PROCEDURE	DROP PROFILE
ALTER DATABASE	CREATE ANY TABLE	DROP USER
ALTER PROFILE	CREATE EXTERNAL JOB	EXEMPT ACCESS POLICY
ALTER SYSTEM	CREATE PUBLIC DATABASE LINK	GRANT ANY OBJECT PRIVILEGE
ALTER USER	CREATE SESSION	GRANT ANY PRIVILEGE
AUDIT SYSTEM	CREATE USER	GRANT ANY ROLE
CREATE ANY JOB	DROP ANY PROCEDURE

3.              Spesifik Olarak Fine-grained Activitilerinin Auditlenmesi ;

Fine-grained auditing i kullanmak için, istenilen durumları içeren  policiler oluşturup sonrasında bu policy’ leri auditleyebilirsiniz.  FGA  aslında audit işleminin yapılan aktivitiye göre özelleştirilmesidir şeklinde tanımlayabiliriz.  

FGA insert, update  ve delete gibi operasyonları ile ilgili sql query’ lerinin auditlenmesine de  olanak sağlar. 

FGA aduting log kayıtları sys.fga_log$  tablosuna kaydedilir.  Bu kayıtlar DBA_FGA_AUDIT_TRAIL view’ inden select edilebilir. DBA_COMMON_AUDIT_TRAIL viewi standart ve fga kayıtlarını combine eder. Ayrıca audit_trail kayıtları XML file’ lere yazılacak şekilde set edilmişse  V$XML_AUDIT_TRAIL  view’ i kullanılarak select edilebilir.

Fga  sadece bizim istemiş olduğumuz spesifik alanlar üzerinde yapılan işlemleri auditlememizi sağlar. Böylelikle bizim için daha anlamlı sonuçlar oluşturur.  Durum böyle olduğunda  gereksiz audit kayıtlarının oluşmasını n önüne geçilmiş  olunur.  FG auditingi kullanabilmek için sys’ nin şemalarından DBMS_FGA  package’ ına execute yetkisinin olması yeterlidir.

FG audit policy manage etmek için dbms_fga package’ ından faydalanabiliriz. Bu package ile tek policy ile tüm kombinasyonları (select, insert, update, delete)  komutlarının audit edilmesini enable edebiliriz.  Burada önemli bir nokta policy’ i  create  ettikden sonra modify edemiyoruz onun yerine drop – cerate ederek yenisini oluşturuyoruz. DBMS_FGA’ ın tüm değişkenleri ile birlikte kullanımı aşağıdaki gibi,

DBMS_FGA.ADD_POLICY(

   object_schema      VARCHAR2,

   object_name        VARCHAR2,

   policy_name        VARCHAR2,

   audit_condition    VARCHAR2,

   audit_column       VARCHAR2,

   handler_schema     VARCHAR2,

   handler_module     VARCHAR2,

   enable             BOOLEAN,

   statement_types    VARCHAR2,

   audit_trail        BINARY_INTEGER IN DEFAULT,

   audit_column_opts  BINARY_INTEGER IN DEFAULT);

Kullanımına geçmden önce burada sık kullanılan değişkenler üzerinde biraz bahsedebiliriz;

·                  Object_schema ; Hangi şemasnın altındaki object auditlenecekse,  bu şemanın belirlendiği parametre,

·                  Object_name ; Auditlenecek olan object’ in isminin belirlendiği parametre,

·                  Policy_name ; oluşturulan bu policy’ e istenirse ismin verildiği parametre, unigue bir alandır, kullanılan bir isim bir daha kullanılamaz.

·                  Audit_condition ;  Auditlenecek olan sql statementlarında where condition’ı içerisinde geçecek olan alanı ifade eder. Null olarak set edilirse  ve bu parametre kullanılmaz ise tüm aktivitelerin auditleneceği anlamına gelir.

·                  Audit_column ; spesifik bir kolon auditlenecek ise bunun set edildiği parametre,

·                  Enables ;  TRUE  ise policy  enable demektir, default değeride TRUE’ dur,

·                  Statements Types ; Hangi sql statementının audit edileceğini belirten parametredir  (sadece  select, insert, update, delete’ i  içerir. Bu ğer null gönderilirse default değeri SELECT olduğundan buna göre policy’ i set edilecektir. )

·                  Audit_trail ; fg audit kayıtları için destination DB veya XML set edilebilir, (default değeri db+extented ‘ dır)  

Tüm parametreleri ile package’ ı aşağıdaki şekilde kullanabiliriz ;

Begin

DBMS_FGA.ADD_POLICY (

   object_schema      =>  'scott',

   object_name        =>  'emp',

   policy_name        =>  'mypolicy1',

   audit_condition    =>  'sal < 100',

   audit_column       =>  'comm,sal',

   handler_schema     =>   NULL,

   handler_module     =>   NULL,

   enable             =>   TRUE,

   statement_types    =>  'INSERT, UPDATE',

   audit_trail        =>   DBMS_FGA.XML + DBMS_FGA.EXTENDED,

   audit_column_opts  =>   DBMS_FGA.ANY_COLUMNS);

end ;

/

Create edilmiş olan policy  dolayısıyla  auditi disable etmek için aşağıdaki sytax kullanılabilir, bunlarla ilgili örnekleri aşağıda bulabilirsiniz;

Begin

DBMS_FGA.DISABLE_POLICY(

   object_schema  VARCHAR2,

   object_name    VARCHAR2,

   policy_name    VARCHAR2 );

end;

/

Package içerisinde object_schema parametresini göndermeniz, database  otomatik olarak sisteme  connect olmuş olan userı dikkate alacaktır. 

Var olan bir policy drop etmek içinse ;

Begin

DBMS_FGA.DROP_POLICY(

   object_schema  VARCHAR2,

   object_name    VARCHAR2,

   policy_name    VARCHAR2 );

end ;

/

systax’ dan faydalanabiliriz.

Bir örnekle yaptıklarımızı açıklamaya çalışalım. Scott scheması altındaki emp tablosunda yapılan işlemleri   scott_emp_policy adı ile bir policy oluşturup auditmeleye çalışalım.

Begin

DBMS_FGA.DROP_POLICY (

object_schema   =>  'scott',

object_name     =>  'emp',

policy_name     =>  'scott_emp_policy');

end ;

/

Dikkat ederseniz statement_type parametresi kullanmadım, dolayısıyla audit policy’ imiz sadece bu tabloya yapılan selectleri auditleyecektir.

Şu anda disable olan bir policy’ i tekrar aktive etmek içinse ;

Begin

DBMS_FGA.ENABLE_POLICY(

   object_schema  VARCHAR2,

   object_name    VARCHAR2,

   policy_name    VARCHAR2,

   enable         BOOLEAN);

end ;

/

komutundan faydalanabiliriz.  Örneğin.

Begin

DBMS_FGA.ENABLE_POLICY (

object_schema    =>  'scott',

object_name      =>  'emp',

policy_name      =>  'mypolicy1',

enable           =>   TRUE);

end ;

/

Daha anlaşılması için örnekleri biraz daha detaylandıralım;

BEGIN

  DBMS_FGA.ADD_POLICY(

   object_schema      => 'HR',

   object_name        => 'EMPLOYEES',

   policy_name        => 'chk_hr_employees',

   policy_owner       => 'SEC_MGR',

   enable             =>  TRUE,

   statement_types    => 'INSERT, UPDATE, SELECT, DELETE',

   audit_trail        =>  DBMS_FGA.DB);

END;

/

Tukarıdaki şekilde policy’ i oluşturdukdan sonra aşağıdaki gibi sql’ ler auditleniyor olacaktır.

SELECT COUNT(*) FROM HR.EMPLOYEES WHERE COMMISSION_PCT = 20 AND SALARY > 4500;

SELECT SALARY FROM HR.EMPLOYEES WHERE DEPARTMENT_ID = 50;

DELETE FROM HR.EMPLOYEES WHERE SALARY > 1000000;

Spesifik bir schema altında yer alan bir tablodaki özel bir (yada birden fazla) kolunu,  istediğimiz bir kondition gerçekleştiğinde auditlemek istiyorsak  create policy package’ ına aşağıdaki kısımlarıda  aeklememiz gerekir;

audit_condition    => 'DEPARTMENT_ID = 50',

audit_column       => 'SALARY,COMMISSION_PCT,'

Fine – Grating yöntemi oluşan audit kayıtlarını UTL_MAIL package’ ını kullarak sonuçları istenilen kullanıcılara mail attırabiliriz. Bunun için oracle documentation’ dan UTL_MAIL package’ ının kullanımı ile ilgili ayrıntılı bilgi alabilirsiniz.

Bir başka örnek olarak ;

BEGIN

 DBMS_FGA.ADD_POLICY(OBJECT_SCHEMA => 'OE',

   OBJECT_NAME                     => 'ORDERS',

   POLICY_NAME                     => 'ORDERS_FGA_POL',

   AUDIT_CONDITION                 => 'SYS_CONTEXT(''USERENV'', ''CLIENT_IDENTIFIER'') = ''Robert''',

   HANDLER_SCHEMA                  => NULL,

   HANDLER_MODULE                  => NULL,

   ENABLE                          => True,

   STATEMENT_TYPES                 => 'INSERT,UPDATE,DELETE,SELECT',

   AUDIT_TRAIL                     => DBMS_FGA.DB + DBMS_FGA.EXTENDED,

   AUDIT_COLUMN_OPTS               => DBMS_FGA.ANY_COLUMNS);

END;

/

systaxı kullanılabilir.

4.              Admin  Userlarının (SYS) Auditlenmesi ;

Sys  userını veya sistemde sysdba  veya sysoper  yetkisine sahip olan tüm kullanıcıları auditleyebilirsiniz. Sys  userının auditlenmesi ile ilgili olarak bir takım faklılıklar mevcut, örneğin sys’ yi auditliyorsanız audit_trail  parametreniz ne olarak set edilmiş olursa olsun (None, Db, Xml, EXTENDE) sys userı auditlenir ve bu audit kayıtları operating sisteme file olarak yazar.  Sys userının audit kayıtlarını  operating sisteme yazması,  sys.aud$  tablosuna yazmasından çok daha güvenlidir, sys userı zaten sysdba yetkisine sahip olduğundan dolayı  tabloya müdahale edebilir yani burdaki bir takım audit  kayıtlarını  delete  edebilir, bu yöntem ile  bu tarz kötü davranışların önüne geçilmesi  planlanmıştır.   

Sys userının auditlenmesi ile ilgili özel bir parametrenin ( AUDIT_SYS_OPERATIONS) tanımlanması gerekmektedir.  Yapılacak değişiklik aşağıdaki gibidir;

ALTER SYSTEM SET AUDIT_SYS_OPERATIONS=TRUE SCOPE=SPFILE;

Audit_trail  parametresi zaten set edilmiş olmalı ;

ALTER SYSTEM SET AUDIT_TRAIL=XML, EXTENDED SCOPE=SPFILE;

Son olarak database’ i restart ediyoruz. Sonrasında sys userına ait tüm işlmler artık auditleniyor  olacaktır.

Buraya kadar 4 tane audit yöntemi üzerinde konuştuk.  Her yöntemde audit kayıtları DB olarak seçilirse, bu kayıtlar SYSTEM tablespace’ inde tutulur. Bizim için SYSTEM tablepace’ i kritik önem taşıdığından dolayı bu tarz verilerin system tablespace’ i dışında farklı bir yerde tutulmasını isteriz. Bu tarz bir işlemi gerçekleştirmek isterseniz  aşağıdaki adımları  kullanarak yapabilirsiniz ;

Öncelikle audit  kayıtlarımızın tutulduğu tablespace’ lerin hangi tablespace’ de olduğunu kontrol etmekle başlayalım ;
 

SELECT table_name, tablespace_name

FROM   dba_tables

WHERE  table_name IN ('AUD$', 'FGA_LOG$')

ORDER BY table_name;

TABLE_NAME                     TABLESPACE_NAME

------------------------------ ------------------------------

AUD$                           SYSTEM

FGA_LOG$                       SYSTEM

Gördüğünüz gibi SYSTEM tablespace’ i içerisinde yer alıyorlar.  Şimdi yeni oluşturacağımız bir tablepace ‘ e taşıyalım.

Yeni bir tablespace create edelim;

Create tablespace only_audit  datafile ‘c:\oracle11gR2\audit\’    size  10M ;

Aşağıdaki pl/sql ile taşıma işlemi yapıyoruz.

BEGIN

  DBMS_AUDIT_MGMT.set_audit_trail_location(

    audit_trail_type           => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,

    audit_trail_location_value => 'ONLY_AUDIT');

END;

/

Son durumu kontol edelim.

SELECT table_name, tablespace_name

FROM   dba_tables

WHERE  table_name IN ('AUD$', 'FGA_LOG$')

ORDER BY table_name;

TABLE_NAME                     TABLESPACE_NAME

------------------------------ ------------------------------

AUD$                           ONLY_AUDIT

FGA_LOG$                       ONLY_AUDIT

ve taşıma tamamlanmış oldu.

Oracle  da  özellikle 11gR2 ile gelen yeni değişikliklerden de bahsederek audit kavramını  açıklamaya çalıştım.  Umarım faydalı olmuştur.  

Kamil TÜRKYILMAZ 

www.kamilturkyilmaz.com

Kullanıcı Hesabını Package Yardımı İle Açma – Kapatma

Merhaba Arkadaşlar, 

Öncelikle başlığı okudukdan sonra şöyle bir soru sorulabilir, alter user XXXX account lock / unlock ile zaten bu işlemi yapıyoruz. Neden bunu bir package aracılığı ile yapmak isteyeyimki diye düşünebilirsiniz, Evet bu işlemi yapacak olan kullanıcıya Alter User yetkisini vermek için önünüzde bir engel yoksa böyle bir package’ a da ihtiyacınız yok demektir. Ancak BDDK denetimine tabi olan kurumlarda bu tarz yetkilerin kullanıcılarda olmasına pek izin verilmiyor (dba’ ler hariç tabiki) Hal böyle bu işlemi yapacak olan kullanıcıya sadece aşağıdaki package için execute yetkisi vermeniz yeterli oluyor. Package kısaca şunu yapıyor benim belirlemiş olduğum kullanıcıların accountlarının durumlarını değiştirme yetkisi veriyor. Özetle bu aslında, şimdi create edip bir örnek yapalım ;

CREATE OR REPLACE procedure kamil.UserLockUnlock(pUserName in varchar2, pLockUnlock in varchar2)

is

  icount          pls_integer;

BEGIN

    select count(*) into icount

    from dba_users

    where username = upper(pusername);

    if (icount > 0)then

                   if  upper(substr(pUserName,1,4)) = 'USER' or upper(substr(pUserName,1,6)) = 'GECICI' Then

                                   if upper(plockUnlock) = 'L' Then

                                                 execute immediate 'alter user ' || pUserName || ' account lock';

                                                 dbms_output.put_line('Kullanıcı Hesabı Kilitlendi.');

                                  elsif upper(plockUnlock) = 'U' Then

                                                 execute immediate 'alter user ' || pUserName || ' account unlock';

                                     dbms_output.put_line('Kullanıcı Hesabı Açıldı.');

                                  else

                                raise_application_error( -20001, 'Kullanıcıyı Açmak için U , Kilitlemek için L girmelisiniz!');

                                  end if;

                        Else

                raise_application_error( -20001, 'Bu kullanıcı için yetkiniz yoktur!');

                       end if;

    else

        raise_application_error( -20001, 'Kullanıcı mevcut değil!');

    end if;

END;

/

Package’ ımızın ismi kamil.UserLockUnlock ve bu package 2 tane değişkenle call ediyoruz.

Birincisi üzerinde işlem yapacağımız USERNAME bilgisi diğeri ise yapmak istediğimiz işlemin ne olduğu, accountu açacakmıyız, kapatacakmıyız.  Bizim örneğimizde kullanıcı ismi USER ve GECICI ile başlayan kullanıcı adlarında sadece işlem yapılabilinir.  Bunun dışındaki userlar için işlem yapılmak istenirse, hata dönecektir. Yapılacak olan işlemin type’ ları içinse “L” gönderilirse LOCK, “U” gönderilirse “UNLOCK” işlemi yapılacaktır.  Örneğin USER_OZSOY  kullanıcısın accountunu lock yapmak istersek ;

begin

        kamil.LockUnlockUser('USER_OZSOY','L');

end;

   

USER_OZSOY hesabını unlock etmek istersek de ;

begin

        kamil.LockUnlockUser('TEMP_OZSOY','U');

end;

komutlarını çalıştırmamız yeterli olacaktır.

Kamil TÜRKYILMAZ 

www.kamilturkyilmaz.com

İstatistikler Hakkında Birkaç Özet Bilgi

Aslında yola Automatic Worload Repository ile ilgili bişeyler yazmak için çıkmıştım. Ancak önemli bazı noktaları atlamak istemediğim için yazmadan öncesinde oracle documantation dan göz atmak istedim. Konuya ilk girişde AWR, istatistikler ile birebir ilişkili olduğundan dolayı öncelikle istatistikler hakkında nedir, çeşitleri nelerdir, ne ifade ederler şeklinde özet bir takım bilgiler vermiş. Konunun anlaşılması ve kavramların daha iyi oturması için bende bu kısma ufakdan bir giriş yapıp, özet olarak bu başlıklar hakkında bikaç cümle yazayım dedim. Sonrasında AWR ile devam edeceğiz.

Database’ de performans ile ilgili sorunların etkili bir şekilde teşhiş edilmesi için istatistiklerin mevcut olması gerekmektedir. Oracle Veritabanı sistemi, oturumlar ve tek başına SQL deyimlerinin toplu istatistikleri birçok türde  üretir ve saklar. Oracle segmentler ve serviceler üzerindeki kümülatif istatistikleri de izler.  Database tarafından toplanan kümülative istatistiki verileri dinamik performans viewlerinde tutar. (v$sesstat, v$sysstat gibi) Burada şunu unutmamak gerekir, database kapatıp açıldığında kümülative istatistikler sıfırlanır.  Sonrasında bu istatistiklerin toplanması işi düzenli olarak belli aralılarla AWR aracılığıyla devam eder.  Active kullanılan sessionlar la ilgili olarak istatistikleri ASH (active session history) toplar.

Alınan bu istatistikler, performansın kötü olduğu dönemlerde, önceki dönem istatistikleriyle karşılaştırılarak performans düşüklüğünü tespit etmeye yardımcı olur. Dba’ lerin performans ile ilgili şikayetlerin arttığı dönemlerde (şikayet gelmediği dönemlerde de sistemi gözlemlemek adına da) çok sık ziyaret ettikleri bir araçtır.

İstatistikleri 3 kısımda düşünebiliriz.

1.     Veritabanı İstatistikleri   (Database Statistics)

2.     İşletim Sistemi İstatistikleri (Operating System Statistics)

3.     Yorumlama İstatistikleri  (Interpreting Statistics)

1.    Veritabanı İstatistikleri  (Database Statistics)

Database istatistikleri, database tarafından kullanılan iç ve dış kaynakların getirdiği yoğunluk hakkında bilgi verir. Bazı önemli veritabanı istatistikleri açıklamaya çalışalım;

Wait Events

Wait event istatistikleri, özetle çalışan bir sessionı beklemek zorunda bırakan işlemlerdir.  Beklemeleri analiz edebilmek için bu işlemler sınıflandırılmıştır. Örneğin;  Administrative, Application, Cluster, Commit, Concurrency, Configuration, Idle, Network, Other, Scheduler, System I/O ve User I/O. Vs.

Aşağıdaki listede wait class’ ların yaygın olarak görülenleri ile örnekler yer almaktadır;

·         Application: lock waitlerinin nedeni, satır bazında veta tablo bazındaki lock komutlarından,

·         Commit: Commit onayının alınmasından sonra, redologlar da yazmak için oluşan beklemeler,

·         Idle: Etkin olmayan oturumlardaki beklemeler, örneğin, SQL*Net message from client

·         Network: network üzerinden kaynaklanan data beklemeleri,

·         User I/O: Okumalardan kaynaklanan beklemeler.

İnstance’ ın wait event istatistikleri, hem background hemde foreground processlerine ait istatistikleri de içerir.

Time Model Statistics

Oracle database tun  edilirken, her bir component kendi istatistiklerini set eder. Sistemin bütününe bakmak için,  bu karşılaştırmalar da ortak bir ölçek kullanılması şarttır.  Model istatistiklerden db_time çok önemlidir.  Bu istatistik  database’ deki toplam iş yükününü gösterir. Bu değer Cpu ve tüm sessionlardaki (idle olan user sessionları hariç) beklemelerin toplamıyla hesaplanır. Db_time instance start oldukdan sonraki değerlerin toplamıdır. Çünkü db_time değeri idle olmayan user sessionlarının kombinasyonundan oluşur.

Active Session History

V$ACTIVE_SESSION_HISTORY view’ i database içerisinden seçmiş olduğu sample sessionlara ait  aktivite’ ler ile ilgili istatistikleri tutar.

System and Session Statistics

Çok sayıda kümülative database istatistikleri, system ve session bazında V$SYSSTAT and V$SESSTAT views’ leri aracılığıyla kullanılabilir.

2.    İşletim Sistemi İstatistikleri (Operating System Statistics)

Operating system istatistikleri, operating systemin performansı ile sistemin ana donanım bileşenlerinin performansı hakkında bilgi verir. Performans düşüklüklerinde bu bilgi son derece önemlidir.  Operating system istatistikleri, operating systemin ve hardware ‘ in nasıl çalıştığının bir göstergesidir.  Operating system istatistikleri aşağıdaki istatistikleri içerir;

CPU Statistics

CPU kullanımı tuning sürecinde en önemli işletim sistemi istatistiki bilgisidir. Tüm sistemin veya çok işlemcili ortamlarda her bir CPU’ nun istatiskleri mutlaka alınmalıdır.

Virtual Memory Statistics

Virtual memory istatistikleri, system üzerinde çok az miktarda paging veya swapping işlemi olduğunda doğrulamak için bir onay olarak kullanılmalıdır. System paging veya swap kullanmaya başladığı zaman, performans hızla düşer.

Disk I/O Statistics

Alt systemlerin i/o performansı, disk setleri üzerindeki database’ in performansı açısından son derece önemlidir. Birçok işletim sistemi disk perfromansı hakkında ciddi istatistiki veri sağlar. En önemli disk istatistik verisi, gerçek responce time süresi ile disk queues’ sunun büyüklüğüdür. Diskin optimum seviyede çalışıp çalışmadığı veya disk üzerindeki yoğunluğu bu istatistikler göstermektedir.

Örneğin system üzerindeki normal bir i/o performansının ölçümü;  hardware kullanımına bağlı olarak tek bir bloğun okuma süresi 5 ile 20 milisaniye arasında olmalıdır. Eğer responce time süresi normal performans süresinden büyükse, bu kötü bir performans olduğunu gösterir.

Network Statistics

Eğer network veya network arabirimi aşırı yüklü ve optimum performansda çalışmıyorsa, disk istatistiklerine benzer bir şekilde network istatistikleri kullanılabilinir.  Bu nedenle, bu istatistikler önemli bir hata ayıklama aracıdır.

Operating System Data Gathering Tools

Unix için aşağıdaki componentler bazında operating bazında istatistikleri almak için bu komutlar kullanılabilirsiniz, widows için se performans monitör tool’ u kullanılabilir. 

Component	UNIX Tool
CPU	sar, vmstat, mpstat, iostat
Memory	sar, vmstat
Disk	sar, iostat
Network	Netstat

3.    Yorumlama İstatistikleri (Interpreting Statistics)

Başlangıç da performance istatistiklerine bakarak,  istatistiklerini inceleyerek potansiyel teorilerinzi formüle edebilirsiniz. İstatistikleri doğru yorumladığınızdan emin olmanın bir yoluda çapraz kontroller yapmaktan geçer. Buda istatistik veya eventın ne kadar doğru olduğunu gösterir.

AWR’ a geçmeden önce istatistikler üzerinden de özetle geçmiş olduk. Başta da dediğim gibi AWR istatistikler ile çok yakından ilgili, hatta database’ de istatistiğiniz yok ise alınmıyorsa, AWR’ ınız da çalışmayacaktır.

Bu ay biraz yoğun geçtiğinden dolayı blogla çok fazla ilgilenemedim. Çok yakında daha sık görüşüyor olacağız diye umuyorum :)

Kamil TÜRKYILMAZ 

www.kamilturkyilmaz.com

Flashback Database Komutu Kullanımı ve Yapabilirlikleri

Flashback database özelliği, database’ i zaman içerisinde sisteminizin kapasitesi elverdiği ölçüde zamanda ileri veya geri götürmektir diye tanımlayabiliriz. Bu özellik özellikle test database’ leri için ciddi kolaylıklar getirmektedir. Çoğu test ortamları için, çeşitli sebeblerden dolayı test yapıldıkdan sonra da eski haline tekrar çekmek gerekiebiliyor. Test başarısız olduğunda yeni bir test için başlangıç duruma geçmek gerekebiliyor. Şimdi bu işlemi nasıl yapabileceğimizden bahsedelim biraz;


Flashbackle ile ilgili diğer yazılarımda database’ in flashback modda olup olmadığından nasıl kontrol edeceğimizden, flashback modda değil ise nasıl flashback moda alacağımızdan ve hangi parametrelerini set edeceğimizden bahsetmiştik, dolayısıyla burda tekrar bu kısımlardan bahsetmeyeceğim.

Flashback database ile yapılacak operasyonları 3 şekilde yapabiliriz. Birincisi SCN ile, ikincisi adını bizim belirlediğimiz bir restore point noktası ile, üçüncüsü ise tarih belirterek flashback database komutunu çalıştırabiliriz.

Flashback database, RMAN üzerinden FLASHBACK DATABASE komutu ile veya SQL üzerinden FLASHBACK DATABASE komutu ile kesintisiz olarak ulaşılabilir. Hızlı bir şekilde mantıksal veri bozulması veya kullanıcı hatalarından dolayı veritabanını kurtarmak için bu komutu kullanabilirsiniz. Aşağıdaki örnekler ile, belirtilen SCN anına veya önceden alınmış olan restore noktasına veritabanını geri döndürebiliriz. Aşağıda bu konu ile ilgili kullanabileceğimiz muhtemel komutları görebilirsiniz ;

-- sistemin şu anki SCN değerini öğrenmek için;
SELECT CURRENT_SCN FROM V$DATABASE;

-- sistem SCN hangi zamanı işaret ettiğini görmek için;
SELECT SCN_TO_TIMESTAMP( 34530329701 ) FROM DUAL;

-- Bir zaman aralığını SCN olarak neye denk geldiğini görmek için;
SELECT TIMESTAMP_TO_SCN(TO_TIMESTAMP('16.10.2010 00:48:17', 'DD.MM.YYYY HH24:MI:SS') ) FROM DUAL;

-- dönebileceğiniz en eski SCN değerini öğrenmek için ;
SELECT OLDEST_FLASHBACK_SCN, OLDEST_FLASHBACK_TIME FROM V$FLASHBACK_DATABASE_LOG;

-- alınmış olan flahback loglarını görmek için;
SELECT * FROM V$FLASHBACK_DATABASE_LOGFILE;

-- flashback recovery area’ nın doluluk oranlarını takip etmek için;
SELECT name,
TO_CHAR (space_limit, '999,999,999,999') AS space_limit,
TO_CHAR (space_limit - space_used + space_reclaimable,
'999,999,999,999')
AS space_available,
ROUND ( (space_used - space_reclaimable) / space_limit * 100, 1)
AS pct_full
FROM v$recovery_file_dest;

-- restore point noktası create etmek için ;
CREATE RESTORE POINT before_upgrade;
CREATE RESTORE POINT before_upgrade GUARANTEE FLASHBACK DATABASE;

-- create edilmiş olan restore noktalarını select etmek için;
SELECT NAME, SCN, TIME, DATABASE_INCARNATION#, GUARANTEE_FLASHBACK_DATABASE,STORAGE_SIZE
FROM V$RESTORE_POINT;

-- rman üzerinden alınmış olan restore noktalarını görmek için ;
LIST RESTORE POINT restore_point_name;
LIST RESTORE POINT ALL;

-- GUARANTEE ile alınmış olan restore pointleri görmek için;
SELECT NAME, SCN, TIME, DATABASE_INCARNATION#, GUARANTEE_FLASHBACK_DATABASE, STORAGE_SIZE
FROM V$RESTORE_POINT
WHERE GUARANTEE_FLASHBACK_DATABASE='YES';

-- alınmış bir retore point noktasını drop etmek için;
DROP RESTORE POINT before_app_upgrade;

--flashback database komutu ile geriye dönmek için ;
FLASHBACK DATABASE TO TIMESTAMP SYSDATE-1;
FLASHBACK DATABASE TO RESTORE POINT 'before_upgrade';
FLASHBACK DATABASE TO SCN 202381;

-- istenilen bir tablespace flashbach dışında tutmak için ;
ALTER TABLESPACE tbs_3 FLASHBACK OFF;

-- flashbach modda olmayan tablespace’ I flashback kapsamına almak için ;
ALTER TABLESPACE tbs_3 FLASHBACK ON;

-- flashback databae’ i disable yapmak için (disable yaparken, database mount veya open modda olabilir ancak enable yaparken mutlaka mount modda olması gerekmektedir.)
ALTER DATABASE FLASHBACK OFF;

Flashback database komutları mount modda çalışır. Flashback database ile geçmişde bir ana dönüldükden sonra database “alter database open resetlogs” komutu ile açılmalıdır. Ancak flashback database komutu ile işlemimizi gerçekleştirdikden sonra doğruluğunu test etmek için database’ i “ALTER DATABASE OPEN READ ONLY” komutu ile read only modda açabiliriz. İşimiz bittikden sonra ise;

SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
ALTER DATABASE OPEN RESETLOGS;

Ile açmamız gerekir.

Guarante restore point sadece flashback logları için yeterince yer olması durumunda garanti eder.Guaranteed restore points ile alınmış olan restore noktası kullanıcı tarafından mutlaka DROP RESTORE POINT komutu ile düşürülmelidir. Guarante ile alınmış olan restore noktalarında zaman aşımı veya üzerine yazma gibi durumlar olmayacağından drop edilmediği sürece sürekli olarak yer kaplamaya devam edeceklerdir. Guarante restore point ile flash_recovery_area' nın önemli bir alanı kullanıyor olabilirsiniz. Bu nedenle, oracle Guarante restore point create işlemi çok iyi düşünüldükden sonra yapılmalıdır der.

Flashback database işlemi geleneksel point-in-time recovery işlemine benzer. Size geçmiş bir zamana veritabanınızı dönmenizi sağlar. Flashback database, point-in-time recovery işleminden çok daha hızlıdır çünkü, datafile' leri restore etmek için backup' a ve archive loglara ihtiyaç duymaz.

Flashback Database' in Limitleri;

Flashback database, komutu çalıştırdığınızda şu anda mevcut datafile' ler için undoda ki değişiklikleri çalıştırdığından, aşağıdaki sınırlamalara sahiptir:

1 - Flashback database ile sadece Oracle Database' i tarafından datafile üzerinde yapılan değişikliklerini geri alabilirsiniz. Kazara silinen datafile' leri veya disk üzerindeki problemlerden dolayı oluşan durumları flashback database ile düzeltemezsiniz.

2 - Undo datafile' i üzerinde shrink yaparsanız Flashback database' i kullanamazsınız. Ancak, offline' a alabilir, flasback database komutunu çalıştırıp sonrasında restore / recover işlemini gerçekleştirebilirsiniz.

3 - Drop edilen datafile için Flashback database komutunu kullanamazsınız. Sadece RMAN ile datafile’ I kurtarabilirsiniz.

4 – Control file dosyası backupdan restore edilir veya re-create edilirse, tüm flashback logları kullanılamaz hale gelir veya control file’ in create edildiği tarihden öncesine flashback database komutu kullanamazsınız.

5 – Flashback database komutu NOLOGGING yapılan işlemlerden etkilenir. Yani geçmişde direct-path yöntemiyle nologging yapılan bir insert işlemi öncesinde dönülmek istendiğinde blok corruption oluşabilir.

Bu kadar yazdıkdan sonra pratikde bir örneğini test ederek görelim.

Database’ imizi immediate ile kapatıp, mount modda açalım ;

SQL> shutdown immediate;
Veritabanı kapatıldı.
Veritabanı kullanıma kapatıldı.
ORACLE anı kapatıldı.
SQL> startup mount
ORACLE anı başlatıldı.
Total System Global Area 612368384 bytes
Fixed Size 1250428 bytes
Variable Size 79694724 bytes
Database Buffers 524288000 bytes
Redo Buffers 7135232 bytes
Veritabanı kullanıma açıldı.

Rman’ e connect olup flashback database komutumuzu burdan çalıştıralım ;

C:\Documents and Settings\Administrator>rman target /
Kurtarma ¦darecisi : Release 10.2.0.1.0 - Production on Paz Oca 2 23:50:16 2011
Copyright (c) 1982, 2005, Oracle. All rights reserved.
 hedef veritabanına bağlanıldı: WINDB (DBID=1657424315, açık değil)
RMAN> FLASHBACK DATABASE TO RESTORE POINT before_upgrade;
flashback başlatılıyor; saat 03/01/2011
ORA_DISK_1 kanalı kullanılıyor
ortam kurtarmayı başlatma
arşiv günlüğü thread'i 1 sıra 91 zaten D:\ARCH\ARCH_1_91_739205892.ARC dosyası o
larak diskte
arşiv günlüğü thread'i 1 sıra 92 zaten D:\ARCH\ARCH_1_92_739205892.ARC dosyası o
larak diskte
…
…
…
arşiv günlüğü thread'i 1 sıra 128 zaten D:\ARCH\ARCH_1_128_739205892.ARC dosyası
olarak diskte
arşiv günlüğü thread'i 1 sıra 129 zaten D:\ARCH\ARCH_1_129_739205892.ARC dosyası
olarak diskte
arşiv günlüğü thread'i 1 sıra 130 zaten D:\ARCH\ARCH_1_130_739205892.ARC dosyası
olarak diskte
ortam kurtarma tamamlandı, geçen süre: 00:04:10
flashback tamamlandı; saat 03/01/2011

İşlem tamamlandı, şimdi resetlog yapmadan alter database open ile açmaya çalışırsak;

RMAN> sql 'alter database open';
sql deyimi: alter database open
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============
RMAN-00571: ===========================================================
RMAN-03009: sql komutu, default kanalında 01/03/2011 16:45:02 konumunda başarısız
RMAN-11003: SQL deyiminin çözümlenmesi/çalıştırılması sırasında başarısızlık görülüyor: alter database open
ORA-01589: veritabanı açma için RESETLOGS veya NORESETLOGS seçeneği kullanılmalı

belirttiğimiz hatayı alıyoruz

Yukarıdaki hatayı alırız. Burada database’ I read only açabiliriz. Bu şekilde dönülen anın doğruluğunu test edebiliriz.

RMAN> sql 'alter database open read only';
sql deyimi: alter database open read only

Read only olarak açtıkdan sonra, kapatmadan resetlog ile açmayı denersek;

SQL> alter database open resetlogs;
alter database open resetlogs
*
1 satırında HATA:
ORA-01531: bir veritabanı zaten an tarafından açıldı

hatamızı aldık :)

Yine hata alırız. Yapılması gereken immediate ile kapatıp, mount ile start edip, sonrasında alter database open resetlogs komutunu göndermek.

SQL> shutdown immediate;
Veritabanı kapatıldı.
Veritabanı kullanıma kapatıldı.
ORACLE anı kapatıldı.
SQL> startup mount
ORACLE anı başlatıldı.
Total System Global Area 612368384 bytes
Fixed Size 1250428 bytes
Variable Size 79694724 bytes
Database Buffers 524288000 bytes
Redo Buffers 7135232 bytes
Veritabanı kullanıma açıldı.
SQL> alter database open resetlogs;
Veritabanı değiştirildi.

Flashback konusunu (genel hatlarıylada olsa) tüm konularına değinerek bitirmiş olduk. Umarım faydalı olmuştur.

Kamil TÜRKYILMAZ

www.kamilturkyilmaz.com