ORACLE Log Minner

Log Minner redo logların kullanılması ile analiz yapılabilmesi, okuma yapılabilmesi hatta recover işlemlerin yapılmasını bize sağlar. Az bilinse de zaman zaman hayat kurtaracak operasyonları bu sayede çözebiliriz.

Log Minner database seviyesinde yapılan bazı dml hatalarının geri alınmasına da olanak sağlar. Bunun yanında database üzerinde yapılan işlemlerin  audit’lenmesine de olanak sağlar. Kullanıcıların yaptığı bir işlemin izinide sürebilirsiniz. 

Şimdi nasıl konfigüre ediyoruz, içeriği nedir ve yaptığımız küçük bir örnek ile detaylandıralım. 

Log Minner kurulum ile birlikte gelen ve bir package create edilmesi ile kullanılan bir opsiyondur. Bu package $ORACLE_HOME/rdbms/admin dizininde  dbmslm.sql dir.

Log minnerın konfigüre edilmesi sırasında create edilen package içeriğinin özetine fikrimiz olması adına bir bakalım.

[oracle@s0134devdb0 admin]$ cat dbmslm.sql

create or replace PACKAGE dbms_logmnr IS

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

  -- OVERVIEW

  --   This package contains the procedures used by LogMiner ad-hoc query

  --   interface that allows for redo log stream analysis.

  --   There are three procedures and two functions available to the user:

  --   dbms_logmnr.add_logfile()    : to register logfiles to be analyzed

  --   dbms_logmnr.remove_logfile() : to remove logfiles from being analyzed

  --   dbms_logmnr.start_logmnr()   : to provide window of analysis and

  --                                  meta-data information

  --   dbms_logmnr.end_logmnr()     : to end the analysis session

  --   dbms_logmnr.column_present() : whether a particular column value

  --                                  is presnet in a redo record

  --   dbms_logmnr.mine_value()     : extract data value from a redo record

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

  --  PROCEDURE INFORMATION:

  --  #1 dbms_logmnr.add_logfile():

  --     DESCRIPTION:

  --       Registers a redo log file with LogMiner. Multiple redo logs can be

  --       registered by calling the procedure repeatedly. The redo logs

  --       do not need to be registered in any particular order.

  --       Both archived and online redo logs can be mined.  If a successful

  --       call to the procedure is made a call to start_logmnr() must be

  --       made before selecting from v$logmnr_contents.

......

......

......

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

-- PROCEDURES

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

-- Initialize LOGMINER

-- Supplies LOGMINER with the list of filenames and SCNs required

-- to initialize the tool.  Once this procedure completes, the server is ready

-- to process selects against the v$logmnr_contents fixed view.

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

PROCEDURE start_logmnr(

     startScn           IN  NUMBER default 0 ,

     endScn             IN  NUMBER default 0,

     startTime          IN  DATE default '',

     endTime            IN  DATE default '',

     DictFileName       IN  VARCHAR2 default '',

     Options            IN  BINARY_INTEGER default 0 );

PROCEDURE add_logfile(

     LogFileName        IN  VARCHAR2,

     Options            IN  BINARY_INTEGER default ADDFILE );

PROCEDURE end_logmnr;

FUNCTION column_present(

     sql_redo_undo      IN  NUMBER default 0,

     column_name        IN  VARCHAR2 default '') RETURN BINARY_INTEGER;

FUNCTION mine_value(

     sql_redo_undo      IN  NUMBER default 0,

     column_name        IN  VARCHAR2 default '') RETURN VARCHAR2;

PROCEDURE remove_logfile(

     LogFileName        IN  VARCHAR2);

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

pragma TIMESTAMP('1998-05-05:11:25:00');

END;

/

grant execute on dbms_logmnr to execute_catalog_role;

create or replace public synonym dbms_logmnr for sys.dbms_logmnr;

İçeriğinde bazı procedure, function ve neler yaptığını görebiliyoruz. Sonunda bazı yetkiler veriyor ve kolay erişim için bir public synonym yaratıyor.

Bunun öncesinde Log minner’ın çalışması için supplemental loggingin açık olması gerekiyor. supplementalloggingin ne olduğunu neye yarayıp hangi seviyelerde olduğunu diğer yazımda belirtmiştim. Şimdi kontrol edip eğer enable değilse minimal seviyede loggingi açalım

SQL> select SUPPLEMENTAL_LOG_DATA_MIN from v$database;

SUPPLEMENTAL_LOG_DATA_MIN

NO

SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
Database altered.

Şimdi yukarıda içeriğine baktığımı dbms_logmnr paketi yaratalım

[oracle@ s0134devdb0 admin]$ pwd

/u01/app/oracle/product/11.2.0/dbhome_2/rdbms/admin

[oracle@ s0134devdb0 admin]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Wed Apr 6 11:27:21 2016

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

With the Partitioning and Automatic Storage Management options

SQL> @/u01/app/oracle/product/11.2.0/dbhome_2/rdbms/admin/dbmslm.sql

Package created.

Grant succeeded.

Synonym created.

SQL>

İlgili kullanıcıyı yetkilendirelim.

SQL> GRANT EXECUTE_CATALOG_ROLE TO ilker;

Grant succeeded.

SQL>

Log minner redologları okuyan bir paket. Bu yüzden log minnera mevcut loğları tanıtalım.

Öncesinde log dosyalarının bilgilerine bakıyoruz

SQL> SELECT distinct member LOGFILENAME FROM V$LOGFILE;

LOGFILENAME

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

+ORADATA/ocdev03/onlinelog/group_3.259.815671587

+ORADATA/ocdev03/onlinelog/group_1.257.815671585

+ORADATA/ocdev03/onlinelog/group_2.258.815671587

+ORAFRA/ocdev03/onlinelog/group_3.259.815671589

+ORAFRA/ocdev03/onlinelog/group_4.260.815671589

+ORADATA/ocdev03/onlinelog/group_4.260.815671589

+ORAFRA/ocdev03/onlinelog/group_1.257.815671585

+ORAFRA/ocdev03/onlinelog/group_2.258.815671587

8 rows selected.

SQL>

Görüldüğü gibi +ASM yapısında yedekli redo larım var.

Şimdi bunu package ekleyelim

11:38:39  SQL> BEGIN

DBMS_LOGMNR.ADD_LOGFILE('+ORADATA/ocdev03/onlinelog/group_1.257.815671585');

DBMS_LOGMNR.ADD_LOGFILE('+ORADATA/ocdev03/onlinelog/group_2.258.815671587');

DBMS_LOGMNR.ADD_LOGFILE('+ORADATA/ocdev03/onlinelog/group_3.259.815671587');

DBMS_LOGMNR.ADD_LOGFILE('+ORADATA/ocdev03/onlinelog/group_4.260.815671589');

END;

/

PL/SQL procedure successfully completed

Executed in 0.453 seconds

11:38:42 SQL>

Böylelikle Log_Minner konfigürasyonu nu tamamlamış olduk. 

Şimdi Start edelim. (LogMiner'ı başlatmak için birden fazla opsiyon bulunmaktadır. Belirli bir SCN numarası, zaman aralığı verilerek ya da bütün logları kullanarak  analiz yaptırabiliriz.)

SQL>  BEGIN

DBMS_LOGMNR.START_LOGMNR(options => dbms_logmnr.dict_from_online_catalog);

END;

/

                           

PL/SQL procedure successfully completed

Executed in 1.359 seconds

SQL>

Sonuçlar için v$logmnr_contents viewini kullanırız.

Log_Minner kapatılması.

SQL>  BEGIN

DBMS_LOGMNR.end_logmnr;

END;

/

PL/SQL procedure successfully completed

Executed in 1.359 seconds

SQL>

Bunun üzerinden şöyle bir senaryo ile örnek verebiliriz.

İstemeden silinen bir datanız oldu. Yada kimin sildiğini bulmanız gerekiyor. Bunun için database bir yere restore edip buradan recover etmenize gerek yok J Zaman aralığınız yaklaşık belli ise faydalı. Eğer yoksa dert değil schema ve obje ismi (Tablo) varsa işimizi yine görecektir.

•          Hemen yeni bir session açıp loglarımızı listeliyoruz.
•          ilgili logları log_minnera aktarıyoruz.
•          Istersek opsiyona göre istersek tüm logları kullanarak log_minnerı start ediyoruz.
•          Evet şimdi Analiz edip iz sürebiliriz. Elimizde ki bilgiler ışığında v$logmnr_contents den schema ve tablo ismini varsa tarih aralığını, geçen dml cümleciğini filtreleyip hedefimize ulaşabiliriz

Ben şöyle bir sql ile örnek verdim:

SELECT   username,

         TO_CHAR (timestamp, 'mm/dd/yy hh24:mi:ss') timestamp,

         seg_type_name,seg_name,

         table_space,

         session# SID,

         serial#,

         operation,

         sql_redo,

         sql_undo,

         session_info

  FROM   v$logmnr_contents

  where seg_name = ''

    and username = ''

böylelikle ilgili kayıtlara ulaşabiliriz. 

İyi Çalışmalar diliyorum.

Usta

Oracle Redo Log yönetimi

Önceki Yazımızda Redo Log mimarisi ve işleyişi hakkında konuşmuştuk. Şimdi Redo log eklenmesi silinmesi kısaca yönetimi hakkında konuşalım.

Redo loglar aynı diskte durduğu gibi farklı disklerde de durabilir. Bir Instance en az 2 log file istediğini daha önce söylemiştik. Aynı member(elemanların) oluşturduğu kümeye redo log grup denir. Ayrıntılı anlatırsak Bir problem çıkma ihtimâline karşı redo log dosyalarını çoklamak (multiplexing) mümkündür. Birinci redo log dosyası demek yerine, birinci redo log grubu denerek, bu gruba birden fazla redo log dosyası eleman/üye (member) olarak atanır. Grup içindeki redo log dosyalarının hepsi de aynıdır.

SQL> select GROUP#,THREAD#,SEQUENCE#,BYTES,MEMBERS,STATUS from v$log;

    GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS STATUS

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

         1          1       1351   52428800          1 INACTIVE

         2          1       1352   52428800          1 CURRENT

         3          1       1350   52428800          1 INACTIVE

SQL>

Redo Log dosyalarını oluşabilecek olağan dışı durumlara karşı çoklu (multiplexing) hale getirirsek o kadar güvenli olmuş oluruz. Öncekiyazımızda Redo Log mimarisinde işleyişi anlatmıştık. LGWR, bir sonraki grup elemanları arşivlenmediği için erişemiyorsa, Grup elemanları arşive çıkılana ve kullanım için uygun hâle gelene kadar, işlem durdurulur.

Yine başka bir senaryo da Sıradaki grubun bütün elemanlarına donanım kaynaklı bir problemden erişilemiyorsa, Oracle hata döndürür ve veritabanı kapatılır. Bu durumda, bir online redo log’un olmayışı nedeniyle recover işlemi yapmanız gerekebilir.

Bu sebeple Multiplexing yapılan elemanların farklı disklere yazılması veri önemlidir.

Şimdi Redo Log dosyaları ile neler yapabileceğimize bakalım

Redo Log’lara Grup/Eleman Ekleme/Çıkartma Boyut değiştirme / Yeniden adlandırma vs...

Öncelikle durumuna bakalım

SELECT F.MEMBER, L.* FROM V$LOGFILE F, V$LOG L WHERE F.GROUP# = L.GROUP#

Yeni Redo Log eklenmes: (ADD NEW ONLINE REDO LOG);

Şimdi  basit ilerleyelim ve hazırda bulunan 3. Online Redo Log file group için 4. File ekleyelim.

SQL> ALTER DATABASE ADD LOGFILE GROUP 4 ('/u01/app/oracle/oradata/denizgyotst/redo04.log ') SIZE 50M;

Database altered.

Ayni anda iki elemanli yeni bir grup eklemek:

SQL> ALTER DATABASE ADD LOGFILE GROUP 5 ('/u01/app/oracle/oradata/denizgyotst/redo51.log', '/u01/app/oracle/oradata/denizgyotst/redo52.log' ) SIZE 50M;

Kontrol edelim

Drop edip, Boyut değiştirme :

Redo log gruplarını mümkün olduğunca birbiriyle aynı şekilde tutmak önemlidir.  Bazı durumlarda mevcut redolog fillerın size ni eşitlemek isteriz. Şimdi bu duruma yönelik bir çalışma yapalım

 Tabi önce durumuna bir bakalım.

Şayet işlem yapacağımız Redo Log aktif ise switch etmemiz gerekecek. (2. Redo group için)

SQL> alter system switch logfile;

System altered.

SQL>

Şimdi tekrar durumlarına bakalım

SELECT F.MEMBER, L.* FROM V$LOGFILE F, V$LOG L WHERE F.GROUP# = L.GROUP#

Gördüğünüz gibi 2.Log file Active, 3.Log file current duruma geçti. Yani LGWR daha önce 2.Log’a yazarken şimdi 3.Log’a yazmaya başladı. LGWR bunun yanında DBWR ‘a 2.Log için mesaj verdi. 2.Log sistem check point atana kadar active statusünde kalacak.  (Bu konuyu önceki konumuzda detaylı behsetmiştik )

Eğer sistemin check pointi ni beklemek istemiyorsak manuel checkpoint atarız.

SQL> alter system checkpoint;

System altered.

Şimdi yeniden bakalım.

Evet artık Inactive grup silinebilir yeniden boytlandırılabilir işlem yapılabilir.

SQL> alter database drop logfile group 2;

Database altered.

SQL>

Şimdi yeniden ekleyelim!

SQL> alter database add logfile group 2 size 50M;

Database altered.

SQL>

Kontrol ettiğimizde görüyoruz ki UNUSED durumda hemen switch ediyorum..

SQL> alter system switch logfile;

System altered.

SQL>

Ve Current duruma geçtiğini görüyorum..

Bu işlemi boyutunu değiştirmek istediğimiz redo log grupları için yapabiliriz. Yeniden eklediğim 2.Grup içinde aynı boyutu verdim.

Drop log member:

Redo log gruplarını mümkün olduğunca birbiriyle aynı şekilde tutmak önemlidir demiştik. Yani bir

grubu 2 elemanlı yaratırken, diğer grubun 4 elemanlı olması tasarım açısından güzel

gözükmeyecektir.

SQL> ALTER DATABASE DROP LOGFILE MEMBER '/u01/app/oracle/oradata/denizgyotst/redo52.log';

Database altered.

Bu arada DROP edilen redo log nesneleri aslında silinmezler. Ancak bir daha kullanılamazlar.

İyi çalışmalar diliyorum

Usta.. 

ORACLE Redo log mimarisi

En kısa anlatım ile, redo loglar oracle'de yapilan islemlerin datafilelere yazilmadan önce tutuldugu dosyalardir diyebiliriz. 

Böylelikle eğer veri dosyalarına yapılan degişiklikler yazılmadan sistem de bir hata oluşursa bu durumda redo log dosyalarına yazılan veriler kullanılarak, yapılan degişikliklerin kaybolması önlenmiş olur. Aynı zamanda Bunun yanında redo log System Change Number (SCN) bilgisini de tutar.

Instance ler en az iki tane redo log grubu isterler. Bunun sebebi ise bir döngü mimarisi ile bir tanesi işlemlerin detayını tutarken diğerinin de yapılan işlemleri data dosyalarına yazmaya çalıştığı içindir. 

Detayına inersek, LGWR, bir sonraki redo log dosyasına geçtiğinde, bir önceki redo log dosyasının durumunu controlfile içinde CURRENT’ten ACTIVE’e çevirir. Akabinde DBWR (Database Writer) işlemini durumdan haberdar ederek, bir önceki redo log dosyasında checkpoint işlemi yapmasını gerektiğini belirtir. DBWR tarafından checkpoint işlemi tamamlanıp, redo log’daki değişiklikler veritabanı dosyalarına yazıldığında CKPT process’i çağrılır. CKPT işlemi veritabanı dosyalarının header bilgilerini ve check point bilgisini (sadece) controlfile içerisinde günceller. (CKPT tarafından yapılan güncelleme bilgisi v$datafile_header tablosundaki checkpoint_change# ve checkpoint_time bilgileriyle ilgilidir.) Burada dikkat edilmesi gereken, CKPT’in redo log bilgisine dair bir güncelleme yapmamasıdır. isminden de belli olacağı gibi sadece checkpoint işlemiyle ilgilenir. CKPT işlemi tamamlandığında, LGWR işlemi çağrılarak controlfile içerisinde redo log bilgisini ACTIVE’den INACTIVE’e çeker. (Bu güncelleme, v$log status bilgisini sağlar.) Bu değişiklik düşük önceliklidir, çünkü bu konuyla tek ilgilenen process LGWR’in kendisidir. Buradaki status bilgisine göre, LGWR redo log dosyasının tekrar kullanılıp kullanılmayacağına karar verir. (Eğer redo log dosyası ACTIVE durumdaysa, checkpoint işleminin sonuçlanması bekleniyor demektir.)

Burada hemen yeri gelmişken önemli bir noktaya değinelim. Active durumdaki redologun datafilelere aktarılması işlemi bitmeden current durumdaki redolog dosyasının dolması durumunda oracle “Thread 1 cannot allocate new log, sequence “ hatasını döndürür.  Bu durumdan kurtulmak için redolog dosyalarımızın sayısını ve boyutunu iyi belirlemeliyiz.

Yukarıda altını çizerek anlatıığımız LGWR’nin redo log dosyaklarının statüsünü güncellemesinden yetersiz bulup değiştirmek istiyorsak

SQL> ALTER SYSTEM CHECKPOINT;
ile checkpoint atarız

Yada

LOG_CHECKPOINT_TIMEOUT Parametresinin timeout süresini sistemimize uygun olarak set ederiz.

SQL> SHOW PARAMETER LOG_CHECKPOINT_TIMEOUT;

SQL> ALTER SYSTEM SET LOG_CHECKPOINT_TIMEOUT=1800 SCOPE=BOTH;

Active durumdaki redologların yarım saatte bir check point edilerik datafilelere yazılması gerektiğini söylüyoruz.

Eğer redo log için yapılan checkpoint işlemini takip edip Alert log dosyasına yazdırmak istiyorsak

SQL> SHOW PARAMETER LOG_CHECKPOINTS_TO_ALERT;

SQL> ALTER SYSTEM SET LOG_CHECKPOINTS_TO_ALERT=TRUE SCOPE=BOTH; System switch log altered.

Redo Log Dosyalarının Durumları

a. CURRENT : Redo log dosyasının kullanımda olduğunu gösterir.

b. ACTIVE :  Current redo log dosyası değişmiştir. Ancak daha önce kullanımda olan redo log dosyasının içeriği henüz veritabanına aktarılmamıştır. Yazma işlemi devam etmektedir. LGWR tarafından active’lik durumu bir süre sonra, INACTIVE’e çekilir. Checkpoint komutuyla, yazma işleminin yapılmasını tetiklemek mümkündür.

c. INACTIVE : Kullanımda olmayan redo log dosyalarını ifade eder.

d. UNUSED : ilgili redo log dosyasının henüz hiç kullanmadığını gösterir. Yeni eklenen redo log dosyalarını, unused olarak görürsünüz.

e. INVALID : Dosyanın erişilemez (ya da bozuk) olduğunu işaret eder.

f. STALE : Dosya tamamlanmamıştır. ABORT ile ya da beklenmeyen ani veritabanı kapanmalarından kaynaklanmaktadır. 

Aşağıdaki viewlar ile online redo log dosyalarına ait bilgiler görebiliriz.

select * from gv$log order by inst_id, group#;

select * from gv$logfile;

select * from gv$log_history order by recid desc;

Redo log fillerın değiştirilmesi, gruplanması silinmesi gibi yapılan operasyonları ayrı bir konu ile anlatıyor olacağız.