11 Şubat 2012 Cumartesi

Data Recovery Advisor Nedir ve Nasıl Çalışır?


Data Recovery Advisor
Oracle 11g ile gelen yeni özelliklerden birisi de DRA’ dır. DRA’lar sistemde bulunan data corruption’ ları yada data kayıplarına olası çözümler  bularak tavsiye verirler.

Bu özellik sadece Oracle 11g ‘de Standalone databaselerde kullanılabilir. Yani Real Application Cluster databaseleri bu özelliği şuan desteklememektedir.
Peki genel olarak data hataları nelerdir?

  • data dosyalarına ulaşılamama(silinmiş,kayıp)
  • Tablespace silinmiş olabilir yada offline durumunda olabilir.
  • fiziksel corruption(bozulmalar)
  • Mantksal Corruption' lar
  • I/O hataları
  • Controlfiledeki bilgi ile datafile bilgileri arasındaki tutarsızlıklar
data hataları kabul edilebilir.
Data Recover Advisor (Veri kurtarma tavsiyecisi) hatayı bulup, sorunun çözülmesi için önermelerde bulunup
, çözümleme işlemini hızlı bir şekilde yapan bir teknolojidir.

Biz bunlardan  LIST FAILURE,
ADVISE FAILURE,
REPAIR FAILURE,
CHANGE FAILURE,
Ve VALIDATE komutlarını örnek bir senaryo ile inceleyeceğiz.
RMAN ' bağlanalım.
Not: Koyu yazılar komut satırlarıdır.

[oracle@mhm dbhome_1]$ rman  target=/

Recovery Manager: Release 11.2.0.1.0 - Production on Sat Feb 4 23:48:08 2012
Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
connected to target database: XDB (DBID=3401130493)
RMAN> list failure;
using target database control file instead of recovery catalog
no failures found that match specification
SQL> create table t (x number) tablespace users ;
Table created.
SQL> insert into t(x) values (to_char(sysdate,'DD'));
1 row created.
SQL> select * from t;
         X
----------
         4
[oracle@mhm dbhome_1]$ cd /
[oracle@mhm /]$ cd $ORACLE_BASE
[oracle@mhm oracle]$ ls
admin  cfgtoollogs  checkpoints  diag  FRA  oradata  oradiag_oracle  product
[oracle@mhm oracle]$ cd oradata
[oracle@mhm oradata]$ ls
catdb  XDB
[oracle@mhm oradata]$ cd XDB
[oracle@mhm XDB]$ ls
controlfile  datafile  onlinelog
[oracle@mhm XDB]$ cd datafile
[oracle@mhm datafile]$ ls
sysaux01.dbf  system01.dbf  undotbs01.dbf  users01.dbf
[oracle@mhm datafile]$ rm -rf users01.dbf
[oracle@mhm datafile]$ ls
sysaux01.dbf  system01.dbf  undotbs01.dbf
[oracle@mhm datafile]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Sat Feb 4 23:58:38 2012
Copyright (c) 1982, 2009, Oracle.  All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
SQL> select * from t;
         X
----------
         4
  Yukarıdaki sorgu, users01.dbf data dosyası olmadan da  cevap verebiliyor. Nasıl oluyor da dosya yok iken çalışıyor?
Veritabanı çalıtığında Belleğimizde database buffer cache isimli bir SGA bileşeni vardır. Çalışacak sorgular, data dosyalarından bu alana getirilip çalıştırılıyor. Dolayısıyla şuan ki çalıştırdığımız sorgu Buffer cache de çalışmaktadır. Şimdi dilerseniz bir buffer cache Flush edelim. İçerisini boşaltalım. 
SQL> alter system flush buffer_cache;
System altered.
Ve sorguyu tekrar çalıştıralım.
SQL> select * from t;
select * from t
*
ERROR at line 1:
ORA-01116: 4 veritaban? dosyas?n? acmada hata
ORA-01110: veri dosyas? 4: '/u01/app/oracle/oradata/XDB/datafile/users01.dbf'
ORA-27041: dosya acılamıyor
Linux Error: 2: No such file or directory
Additional information: 3
Şimdi tekrar RMAN’ a bağlanalım.
[oracle@mhm datafile]$ rman  target=/
Recovery Manager: Release 11.2.0.1.0 - Production on Sun Feb 5 00:01:10 2012
Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
connected to target database: XDB (DBID=3401130493)
RMAN> list failure;
using target database control file instead of recovery catalog
List of Database Failures
=========================
Failure ID Priority Status    Time Detected Summary
---------- -------- --------- ------------- -------
2074       HIGH     OPEN      04-FEB-12     Bir veya daha fazla sistem dısı veri dosyası eksik
RMAN> advise failure;
List of Database Failures
=========================

Failure ID Priority Status    Time Detected Summary
---------- -------- --------- ------------- -------
2074       HIGH     OPEN      04-FEB-12     Bir veya daha fazla sistem dısı veri dosyası eksik

analyzing automatic repair options; this may take some time
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=18 device type=DISK
analyzing automatic repair options complete
Mandatory Manual Actions
========================
no manual actions available

Optional Manual Actions
=======================
1. /u01/app/oracle/oradata/XDB/datafile/users01.dbf dosyas? yanl?sl?kla yeniden adland?r?lm?s veya tas?nm?ssa, bunu geri yukleyin

Automated Repair Options
========================
Option Repair Description
------ ------------------
1      4 veri dosyasını geri yukleyin ve kurtarın 
  Strategy: Onarım, veri kaybı olmadan tam bir ortam kurtarmayı icerir.
(Yukarıdaki tavsiye 4 nolu datafile restore etmeyi ve daha sonra recover işlemini içermektedir.) 
Repair script: /u01/app/oracle/diag/rdbms/xdb/XDB/hm/reco_2957351084.hm



RMAN> repair failure preview;
Strategy: Onarım, veri kaybı olmadan tam bir ortam kurtarmayı icerir
Repair script: /u01/app/oracle/diag/rdbms/xdb/XDB/hm/reco_2957351084.hm
contents of repair script:
   # restore and recover datafile
   sql 'alter database datafile 4 offline';
   restore datafile 4;
   recover datafile 4;
   sql 'alter database datafile 4 online';

Çözüm için yukarıdaki script’ i önermektedir. Şayet bizce de uygunsa  o zaman aşağıdaki kodu rahatça çalıştırabiliriz.
RMAN> repair failure;

Strategy: Onar?m, veri kayb? olmadan tam bir ortam kurtarmay? icerir
Repair script: /u01/app/oracle/diag/rdbms/xdb/XDB/hm/reco_2957351084.hm

contents of repair script:
   # restore and recover datafile
   sql 'alter database datafile 4 offline';
   restore datafile 4;
   recover datafile 4;
   sql 'alter database datafile 4 online';

Do you really want to execute the above repair (enter YES or NO)? Y
(Burada  yukarıdaki tavsiye scripti çalıştırmak istediğinden eminsen Y ile yanıt verelim.)
executing repair script

sql statement: alter database datafile 4 offline
Starting restore at 05-FEB-12
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00004 to /u01/app/oracle/oradata/XDB/datafile/users01.dbf
channel ORA_DISK_1: reading from backup piece +XFRA/xdb/backupset/2012_02_04/nnndn0_tag20120204t232950_0.275.774401393
channel ORA_DISK_1: piece handle=+XFRA/xdb/backupset/2012_02_04/nnndn0_tag20120204t232950_0.275.774401393 tag=TAG20120204T232950
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:04
Finished restore at 05-FEB-12
Starting recover at 05-FEB-12
using channel ORA_DISK_1
starting media recovery
rchived log for thread 1 with sequence 1 is already on disk as file +XFRA/xdb/archivelog/2012_02_04/thread_1_seq_1.277.774402255
archived log for thread 1 with sequence 2 is already on disk as file +XFRA/xdb/archivelog/2012_02_04/thread_1_seq_2.278.774402273
archived log for thread 1 with sequence 3 is already on disk as file +XFRA/xdb/archivelog/2012_02_04/thread_1_seq_3.279.774402285
archived log file name=+XFRA/xdb/archivelog/2012_02_04/thread_1_seq_1.277.774402255 thread=1 sequence=1
media recovery complete, elapsed time: 00:00:00
Finished recover at 05-FEB-12
sql statement: alter database datafile 4 online
repair failure complete
Şuan sistemde herhangi bir failure varmı yokmu diye bir kontrol edebiliriz.
RMAN> list failure;
no failures found that match specification
[oracle@mhm datafile]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Sun Feb 5 00:04:51 2012
Copyright (c) 1982, 2009, Oracle.  All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options

Test edelim tekrardan…
SQL> select * from t;
         X
         4
Sonuç pozitif.

Not: Önce list Failure sonra Advise failure ve en son repair failure komutları kullanılır. Bu sıra önemlidir. Ve sanırsam da  bununla ilgili OCP sınavında bir soru vardı.
Örnek hata:
RMAN> list failure;

List of Database Failures
=========================

Failure ID Priority Status    Time Detected Summary
---------- -------- --------- ------------- -------
2074       HIGH     OPEN      11-FEB-12     Bir veya daha fazla sistem d?s? veri dosyas? eksik
RMAN> repair failure;
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of repair command at 02/11/2012 17:58:31
RMAN-06954: REPAIR command must be preceded by ADVISE command in same session

RMAN> list failure;
using target database control file instead of recovery catalog
List of Database Failures
=========================
Failure ID Priority Status    Time Detected Summary
---------- -------- --------- ------------- -------
2074       HIGH     OPEN      11-FEB-12     Bir veya daha fazla sistem d?s? veri dosyas? eksik

CHANGE FAILURE ile de hatanın LOW, HIGH yada CRITICAL  seçeneklerini kullanarak hatayı derecelendirebiliriz. Sistem için  önemli olan HIGH hatalarını system kaldığı yerden devam etmesi için LOW duruma getirilir. Sistem için etkisi olmayan bir HIGH hatasını LOW duruma getirebiliriz.Level olarak CRITICAL olan bir hatayı  HIGH yada LOW durumuna getiremeyiz. CRITICAL bir hata hemen REPAIR edilmesi gerekir. Edilemiyorsa alternatif çözümlerin bulunulması gerekir.

RMAN> change failure 2074 priority high;
List of Database Failures
=========================
Failure ID Priority Status    Time Detected Summary
---------- -------- --------- ------------- -------
2074       LOW      OPEN      11-FEB-12     Bir veya daha fazla sistem d?s? veri dosyas? eksik
Do you really want to change the above failures (enter YES or NO)? y
changed 1 failures to HIGH priority
RMAN> list failure;
List of Database Failures
=========================    
Failure ID Priority Status    Time Detected Summary
---------- -------- --------- ------------- -------
2074       HIGH     OPEN      11-FEB-12     Bir veya daha fazla sistem d?s? veri dosyas? eksik
VALIDATE komutu ise  fiziksel ve mantıksal bozulabilecek tüm bütünleşik  olarak check eder.
Örnek olarak mevcut backupsetlerin check edilmesi, tablespace yada datafile check edilmesi, controlfile yada log dosylarının chec edilmesi  archive logların yada tüm database ‘in check edilmesi gibi bir çok örnek verilebilir.
Rman’ a bağlanalım.
RMAN> validate database;
Starting validate at 11-FEB-12
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=1 device type=DISK
channel ORA_DISK_1: starting validation of datafile
channel ORA_DISK_1: specifying datafile(s) for validation
input datafile file number=00001 name=/u01/app/oracle/oradata/XDB/datafile/system01.dbf
input datafile file number=00002 name=/u01/app/oracle/oradata/XDB/datafile/sysaux01.dbf
input datafile file number=00003 name=/u01/app/oracle/oradata/XDB/datafile/undotbs01.dbf
input datafile file number=00004 name=/u01/app/oracle/oradata/XDB/datafile/users01.dbf
channel ORA_DISK_1: validation complete, elapsed time: 00:01:02
List of Datafiles
=================
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
1    OK     0              13430        87040           987716   
  File Name: /u01/app/oracle/oradata/XDB/datafile/system01.dbf
  Block Type Blocks Failing Blocks Processed
  ---------- -------------- ----------------
  Data       0              58759          
  Index      0              12274          
  Other      0              2577           

File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
2    OK     0              17900        64009           987714   
  File Name: /u01/app/oracle/oradata/XDB/datafile/sysaux01.dbf
  Block Type Blocks Failing Blocks Processed
  ---------- -------------- ----------------
  Data       0              12363          
  Index      0              8878           
  Other      0              24859          

File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
3    OK     0              129          6400            987716   
  File Name: /u01/app/oracle/oradata/XDB/datafile/undotbs01.dbf
  Block Type Blocks Failing Blocks Processed
  ---------- -------------- ----------------
  Data       0              0              
  Index      0              0              
  Other      0              6271           

File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
4    OK     0              473          640             955682   
  File Name: /u01/app/oracle/oradata/XDB/datafile/users01.dbf
  Block Type Blocks Failing Blocks Processed
  ---------- -------------- ----------------
  Data       0              20             
  Index      0              2              
  Other      0              145            

channel ORA_DISK_1: starting validation of datafile
channel ORA_DISK_1: specifying datafile(s) for validation
including current control file for validation
including current SPFILE in backup set
channel ORA_DISK_1: validation complete, elapsed time: 00:00:01
List of Control File and SPFILE
===============================
File Type    Status Blocks Failing Blocks Examined
------------ ------ -------------- ---------------
SPFILE       OK     0              2              
Control File OK     0              594            
Finished validate at 11-FEB-12

Backup Validate;
Backup alınırken tüm database ve archive log dosyalarının logical olarak bozulmalar varmı diye bir check işlemiş yapılır. Sorun yoksa backup alınır. Aşağıdaki komutları yazdığımızda tüm datafile, parametre dosyası, controlfile  ve archive log’ lar check edilmiş olacaktır. Herhangi bir  logical bozulma mevcut olursa alert loglara da yazaktır.
                                                                                                                                                                                  
RMAN> backup validate  check logical database archivelog all;
Starting backup at 11-FEB-12
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=/u01/app/oracle/oradata/XDB/datafile/system01.dbf
input datafile file number=00002 name=/u01/app/oracle/oradata/XDB/datafile/sysaux01.dbf
input datafile file number=00003 name=/u01/app/oracle/oradata/XDB/datafile/undotbs01.dbf
input datafile file number=00004 name=/u01/app/oracle/oradata/XDB/datafile/users01.dbf
channel ORA_DISK_1: backup set complete, elapsed time: 00:01:15
List of Datafiles
=================
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
1    OK     0              13421        87040           989274   
  File Name: /u01/app/oracle/oradata/XDB/datafile/system01.dbf
  Block Type Blocks Failing Blocks Processed
  ---------- -------------- ----------------
  Data       0              58765          
  Index      0              12277          
  Other      0              2577           

File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
2    OK     0              17721        64009           989272   
  File Name: /u01/app/oracle/oradata/XDB/datafile/sysaux01.dbf
  Block Type Blocks Failing Blocks Processed
  ---------- -------------- ----------------
  Data       0              12363          
  Index      0              8938           
  Other      0              24978          

File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
3    OK     0              129          6400            989274   
  File Name: /u01/app/oracle/oradata/XDB/datafile/undotbs01.dbf
  Block Type Blocks Failing Blocks Processed
  ---------- -------------- ----------------
  Data       0              0              
  Index      0              0              
  Other      0              6271           

File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
4    OK     0              473          640             955682   
  File Name: /u01/app/oracle/oradata/XDB/datafile/users01.dbf
  Block Type Blocks Failing Blocks Processed
  ---------- -------------- ----------------
  Data       0              20             
  Index      0              2              
  Other      0              145            

channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=13 RECID=12 STAMP=774008535
input archived log thread=1 sequence=14 RECID=13 STAMP=774008914
input archived log thread=1 sequence=15 RECID=14 STAMP=774008917
input archived log thread=1 sequence=16 RECID=15 STAMP=774008919
input archived log thread=1 sequence=17 RECID=16 STAMP=774008935
input archived log thread=1 sequence=18 RECID=17 STAMP=774008936
input archived log thread=1 sequence=19 RECID=23 STAMP=774385020
input archived log thread=1 sequence=20 RECID=24 STAMP=774385020
input archived log thread=1 sequence=21 RECID=25 STAMP=774385026
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:07
List of Archived Logs
=====================
Thrd Seq     Status Blocks Failing Blocks Examined Name
---- ------- ------ -------------- --------------- ---------------
1    13      OK     0              21              +XFRA/xdb/archivelog/2012_01_31/thread_1_seq_13.266.774008535
1    14      OK     0              96              +XFRA/xdb/archivelog/2012_01_31/thread_1_seq_14.264.774008915
1    15      OK     0              1               +XFRA/xdb/archivelog/2012_01_31/thread_1_seq_15.263.774008917
1    16      OK     0              5               +XFRA/xdb/archivelog/2012_01_31/thread_1_seq_16.257.774008919
1    17      OK     0              9               +XFRA/xdb/archivelog/2012_01_31/thread_1_seq_17.256.774008935
1    18      OK     0              1               +XFRA/xdb/archivelog/2012_01_31/thread_1_seq_18.262.774008937
1    19      OK     0              3               +XFRA/xdb/archivelog/2012_02_04/thread_1_seq_19.267.774385021
1    20      OK     0              231             +XFRA/xdb/archivelog/2012_02_04/thread_1_seq_20.268.774385021
1    21      OK     0              58656           +XFRA/xdb/archivelog/2012_02_04/thread_1_seq_21.269.774385021
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=1 RECID=26 STAMP=774402257
input archived log thread=1 sequence=2 RECID=27 STAMP=774402273
input archived log thread=1 sequence=3 RECID=28 STAMP=774402285
input archived log thread=1 sequence=4 RECID=29 STAMP=774485129
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
List of Archived Logs
=====================
Thrd Seq     Status Blocks Failing Blocks Examined Name
---- ------- ------ -------------- --------------- ---------------
1    1       OK     0              50184           +XFRA/xdb/archivelog/2012_02_04/thread_1_seq_1.277.774402255
1    2       OK     0              1               +XFRA/xdb/archivelog/2012_02_04/thread_1_seq_2.278.774402273
1    3       OK     0              51              +XFRA/xdb/archivelog/2012_02_04/thread_1_seq_3.279.774402285
1    4       OK     0              53536           +XFRA/xdb/archivelog/2012_02_05/thread_1_seq_4.281.774485121
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current control file in backup set
including current SPFILE in backup set
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02
List of Control File and SPFILE
===============================
File Type    Status Blocks Failing Blocks Examined
------------ ------ -------------- ---------------
SPFILE       OK     0              2              
Control File OK     0              594            
Finished backup at 11-FEB-12


Dynamic performans view ler ile;
LIST FAILURE görevini yerine getirecek V$IR_FAILURE;
ADVISE FAILURE görevini yerinegetirecek V$IR_MANUAL_CHECKLIST;
REPAIR FAILURE görevini yerine getirecek V$_IR_REPAIR;


viewlerinden de  yararlanabiliriz.
Farklı bir makale ile tekrar görüşmek ümidi ile…


Gönderen zaman: 18:03

0 yorum:

Yorum Gönder

Kaydol: Kayıt Yorumları (Atom)