You are hereBlogues / almudena's blog / Cómo provocar un bloque corrupto en BD baseadas en ASM e restaurar con RMAN a nivel de bloque

Cómo provocar un bloque corrupto en BD baseadas en ASM e restaurar con RMAN a nivel de bloque


Por: almudena - Publicado o: 12 April 2013

Detallo a continuación os pasos seguidos para provocar un bloque corrupto unha BD con ASM e levar a cabo unha restauración a nivel de bloque con RMAN. Elo é posible por estar traballando con unha BD Enterprise Edition. Mencionar tamén que se trata dun contorno de test.

           1. Creamos tablespace/usuario/táboa e procemento para insertar filas.             

 	    14:25:39 SYS@orcl1> create tablespace bad_data_test_acc;

 	    Tablespace created.


	    14:27:32 SYS@orcl1> select  file_name, file_id , tablespace_name from dba_data_files
	    14:27:42   2   where tablespace_name like '%BAD%';

         FILE_NAME					                                FILE_ID  TABLESPACE_NAME
         ----------------------------------------------------------- ---------- -------------------------------------------------------------------------
	    +DATA/orcl/datafile/bad_data_test_acc.342.812469967	        12      BAD_DATA_TEST_ACC

         create user acc identified by *****  default tablespace bad_data_test_acc; 
         create table test (username varchar2(9), password varchar2(6));
         
         DECLARE 
         u  VARCHAR2(9);
         p  VARCHAR2(6);
         
         BEGIN 
         FOR jump  IN 1 ..10000  LOOP 
         u := 'TEST'||jump; 
         p := 'P'||jump; 
         insert into test values (u,p);
         END LOOP; 
         commit;
         END; 
         / 

         2. Quedámonos cos datos que precisaremos para forzar a corrupción dun bloque.    

         select
         rowid ,
         to_number(utl_encode.base64_decode(utl_raw.cast_to_raw(lpad(substr(rowid, 1, 6), 8, 'A'))), 'XXXXXXXXXXXX') as objid,
         to_number(utl_encode.base64_decode(utl_raw.cast_to_raw(lpad(substr(rowid, 7, 3), 4, 'A'))), 'XXXXXX') as filenum,
         to_number(utl_encode.base64_decode(utl_raw.cast_to_raw(lpad(substr(rowid, 10, 6), 8, 'A'))), 'XXXXXXXXXXXX') as blocknum,
         to_number(utl_encode.base64_decode(utl_raw.cast_to_raw(lpad(substr(rowid, 16, 3), 4, 'A'))), 'XXXXXX') as rowslot
         from test where password='P7777'
         /
         ROWID OBJID FILENUM   BLOCKNUM    ROWSLOT
         ------------------ ---------- ---------- ---------- ----------
         AAARofAAMAAAACeAAr 72223       12 158     43         

          2. Lanzamos un backup RMAN da BD  que contén os últimos datos creados.   


          3. Poñemos o tablespace offline e pasámolo a fs empregando RMAN para elo.

     alter tablespace bad_data_test_acc offline;
   
     RMAN> copy datafile 12  to '/tmp/block_corruption/bad_data_01.dbf';
     
     Starting backup at 11/04/2013 14:26:27
     using target database control file instead of recovery catalog
         (...)
     channel ORA_DISK_7: SID=197 instance=orcl1 device type=DISK
     allocated channel: ORA_DISK_8
     channel ORA_DISK_8: SID=236 instance=orcl1 device type=DISK
     allocated channel: ORA_DISK_9
     channel ORA_DISK_9: SID=278 instance=orcl1 device type=DISK
     allocated channel: ORA_DISK_10
     channel ORA_DISK_10: SID=316 instance=orcl1 device type=DISK
     channel ORA_DISK_1: starting datafile copy
     input datafile file number=00012 name=+DATA/orcl/datafile/bad_data_test_acc.342.812469967
     output file name=/tmp/block_corruption/bad_data_01.dbf tag=TAG20130411T142632 RECID=1 STAMP=812471194
     channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03    

     4. Forzamos a corrupción no bloque.       

     [oracle@oracle block_corruption]$ dd if=/tmp/block_corruption/bad_data_01.dbf bs=8k count=158 of=/tmp/block_corruption/bad_data_01.dbf_new
     158+0 records in
     158+0 records out
     1294336 bytes (1.3 MB) copied, 0.00493063 s, 263 MB/s
   
     [oracle@oracle block_corruption]$ dd if=/tmp/block_corruption/bad_data_01.dbf bs=8k count=1 >> /tmp/block_corruption/bad_data_01.dbf_new
     1+0 records in
     1+0 records out
     8192 bytes (8.2 kB) copied, 0.000112149 s, 73.0 MB/s
   
     [oracle@oracle block_corruption]$ dd if=/tmp/block_corruption/bad_data_01.dbf bs=8k skip=159 >> /tmp/block_corruption/bad_data_01.dbf_new
     12642+0 records in
     12642+0 records out
     103563264 bytes (104 MB) copied, 0.374947 s, 276 MB/s
     [oracle@oracle block_corruption]$
     
     mv /tmp/block_corruption/bad_data_01.dbf_new  /tmp/block_corruption/bad_data_01.dbf

       5. Copiamos os datos do datafile de fs a ASM de novo con RMAN.

         run {
         set maxcorrupt for datafile 12 to 2;
         restore datafile 12 from tag 'TAG20130411T142632';
         recover datafile 12;
         }
         channel ORA_DISK_1: restoring datafile 00012
         input datafile copy RECID=1 STAMP=812471194 file name=/tmp/block_corruption/bad_data_01.dbf
         destination for restore of datafile 00012: +DATA/orcl/datafile/bad_data_test_acc.342.812469967
         channel ORA_DISK_1: copied datafile copy of datafile 00012
         output file name=+DATA/orcl/datafile/bad_data_test_acc.342.812469967 RECID=0 STAMP=0
         Finished restore at 11/04/2013 14:35:41
        
         Starting recover at 11/04/2013 14:35:41
         using channel ORA_DISK_1
         using channel ORA_DISK_2
         using channel ORA_DISK_3
         using channel ORA_DISK_4
         using channel ORA_DISK_5
         using channel ORA_DISK_6
         using channel ORA_DISK_7
         using channel ORA_DISK_8
         using channel ORA_DISK_9
         using channel ORA_DISK_10
        
         starting media recovery
         media recovery complete, elapsed time: 00:00:01
        
         Finished recover at 11/04/2013 14:35:42
              

       6. Poñemos tablespace online e comprobamos que temos o bloque corrupto.

        alter tablespace bad_data_test_acc online;
        14:36:39 ACC@orcl1> select * from test where password='P7777';
         select * from test where password='P7777'
                       *
         ERROR at line 1:
         ORA-01578: ORACLE data block corrupted (file # 12, block # 158)
         ORA-01110: data file 12: '+DATA/orcl/datafile/bad_data_test_acc.342.812469967'
        
         14:37:38 SYS@orcl1> select * from v$database_block_corruption;
        
              FILE#     BLOCK#   BLOCKS CORRUPTION_CHANGE# CORRUPTIO
         ---------- ---------- ---------- ------------------ ---------
         12   158        1   0 CORRUPT
        
        
         [oracle@oracle log]$ rman target /
         Recovery Manager: Release 11.2.0.3.0 - Production on Thu Apr 11 14:37:58 2013
        
         Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
        
         connected to target database: orcl (DBID=1700599633)
        
         RMAN> backup check logical validate tablespace bad_data_test_acc;
        
         Starting backup at 11/04/2013 14:38:42
         using target database control file instead of recovery catalog
         allocated channel: ORA_DISK_1
         channel ORA_DISK_1: SID=471 instance=orcl1 device type=DISK
         allocated channel: ORA_DISK_2
         (...)
         allocated channel: ORA_DISK_9
         channel ORA_DISK_9: SID=236 instance=orcl1 device type=DISK
         allocated channel: ORA_DISK_10
         channel ORA_DISK_10: SID=278 instance=orcl1 device type=DISK
         channel ORA_DISK_1: starting full datafile backup set
         channel ORA_DISK_1: specifying datafile(s) in backup set
         input datafile file number=00012 name=+DATA/orcl/datafile/bad_data_test_acc.342.812469967
         channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
         List of Datafiles
         =================
         File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
         ---- ------ -------------- ------------ --------------- ----------
         12   OK     1              12641        12800           356051175
           File Name: +DATA/orcl/datafile/bad_data_test_acc.342.812469967
           Block Type Blocks Failing Blocks Processed
           ---------- -------------- ----------------
           Data       0              27             
           Index      0              0              
           Other      0              132            
        
         Finished backup at 11/04/2013 14:38:48
        
         RMAN>      

       7. Restauramos a nivel de bloque con rman.   

         [oracle@oracle log]$ rman target /
        
         Recovery Manager: Release 11.2.0.3.0 - Production on Thu Apr 11 14:40:36 2013
        
         Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
        
         connected to target database: orcl (DBID=1700599633)
        
         RMAN> recover datafile 12 block 158;
        
         Starting recover at 11/04/2013 14:40:44
         using target database control file instead of recovery catalog
         allocated channel: ORA_DISK_1
         channel ORA_DISK_1: SID=471 instance=orcl1 device type=DISK
         allocated channel: ORA_DISK_2
         channel ORA_DISK_2: SID=626 instance=orcl1 device type=DISK
         allocated channel: ORA_DISK_3
         channel ORA_DISK_3: SID=665 instance=orcl1 device type=DISK
         allocated channel: ORA_DISK_4
         channel ORA_DISK_4: SID=939 instance=orcl1 device type=DISK
         allocated channel: ORA_DISK_5
         channel ORA_DISK_5: SID=120 instance=orcl1 device type=DISK
         allocated channel: ORA_DISK_6
         channel ORA_DISK_6: SID=159 instance=orcl1 device type=DISK
         allocated channel: ORA_DISK_7
         channel ORA_DISK_7: SID=198 instance=orcl1 device type=DISK
         allocated channel: ORA_DISK_8
         channel ORA_DISK_8: SID=236 instance=orcl1 device type=DISK
         allocated channel: ORA_DISK_9
         channel ORA_DISK_9: SID=278 instance=orcl1 device type=DISK
         allocated channel: ORA_DISK_10
         channel ORA_DISK_10: SID=316 instance=orcl1 device type=DISK
        
         channel ORA_DISK_1: restoring block(s) from datafile copy /tmp/block_corruption/bad_data_01.dbf
         ORA-19587: error occurred reading 8192 bytes at block number 0
         ORA-27069: attempt to do I/O beyond the range of the file
         Additional information: 1
         Additional information: 12801
         ORA-19600: input file is datafile copy 0 (/tmp/block_corruption/bad_data_01.dbf)
        
         failover to previous backup
        
         channel ORA_DISK_1: restoring block(s)
         channel ORA_DISK_1: specifying block(s) to restore from backup set
         restoring blocks of datafile 00012
         channel ORA_DISK_1: reading from backup piece +FRA/orcl/backupset/2013_04_11/nnndf0_tag20130411t141520_0.473.812470521
         channel ORA_DISK_1: piece handle=+FRA/orcl/backupset/2013_04_11/nnndf0_tag20130411t141520_0.473.812470521 tag=TAG20130411T141520
         channel ORA_DISK_1: restored block(s) from backup piece 1
         channel ORA_DISK_1: block restore complete, elapsed time: 00:00:01
        
         starting media recovery
         media recovery complete, elapsed time: 00:00:07
        
         Finished recover at 11/04/2013 14:40:58         

      8. Comprobamos que xa non hai bloques corruptos na BD.

         14:42:55 ACC@orcl1> select * from test where password='P7777';
        
         USERNAME  PASSWO
         --------- ------
         TEST7777  P7777
        
         14:42:00 ACC@orcl1> select * from v$database_block_corruption;
        
         no rows selected
        
         14:42:08 ACC@orcl1> exit
Share this

Hardcore Gay photo blogging service
http://mens.sexgalleries.top/?post-alan

Ребят, долго искал, где можно почитать мангу, ловите сайт [url=]myvi.gq[/url]
Пока не заблокировали в РФ!
Для всех мангатянов и анимешников!

Созерцать фото галереи орального секса бескорыстно
http://siski-krima.porndairy.in/?page-pamela

Habitually updated photo blog with hot men
http://hot.gay.teen.porndairy.in/?entry-yadiel

Assignment recto moved:
http://agnes.brief1.telrock.org

Секс-галерея порно и секс фото с зрелыми дамочками
http://analnyi.seks.sexblog.top/?post.piper

[url=https://cardswm.com/]пластиковая карта вебмани заказать[/url] - перевести деньги с карты на вебмани, вебмани карта где купить.

Молодежное серое CYLF.RU [url=http://cylf.ru/molodejnoe-seroe-palto.html]More info![/url]

[url=http://chel-week.ru/]потому[/url]

Грандис-запчасти для иномарок и всё для митцубиси

[URL=http://grandis-888.ru/>]запчасти митсубиси[/URL]
[URL=http://grandis-888.ru/>]запчасти митсубиси здесь[/URL]

[url=http://rains.servicecash.top/]бинарники[/url]

After my new project
http://chubby.feed.bdsmsex.top/?post.tracy
all free long porn clips brothel house porn porn vido teen free young teen innocent porn custom porn pinball machine

redtube ukrania women http://fondnevskij.ru/profile/deejay-girl nangi women sex

В «Наркононе» мы посвятили себя одной цели: помочь вам навсегда преодолеть зависимость. Во внимание принимается каждая деталь, чтобы дать вам стабильность и комфорт, a также помочь освободиться от зависимости и заново начать свою жизнь без наркотиков.
Телефон: +79200396703

This email I sent this program. Look
http://dimas87o.bget.ru/xrumer/

Common updated photo blog with boiling men
http://shemale.galleries.blogporn.in/?entry-oliver

Добрый день. Хочу вас познакомить с новым Онлайн кинотеатром по адресу Онлайн кинотеатр

Original programme
http://peggme.yopoint.in/?gain.eliza
erotic tattoo adult sex erotic sound free adult film ancient erotic art

Started new web throw
http://boobs.pics.erolove.top/?post.donna
julia oppai porn busty blond porn star porn pics retro dad eating out straight porn wildcat porn clips

Замечательный отель, окна во всю стену в прибрежной зоне выходят прямо на море. До пляжа буквально 10 метров. Идеальное место для семей - ибо есть, где спокойно погулять.

недорогой отдых в Крыму с детьми тут

My contemporary time
http://bigboobs.yopoint.in/?entry-paloma
barely legal cowgirl porn major boobage porn sweeden porn great free blowjob porn free gay porn video upload

perfect money advanced cash - bitcoin обменник, купить биткоин +за яндекс деньги.

Cephalexin And Cramping Liste Pays Ou Viagra Vente Libre Propecia Pastillas cialis Generic Levitra In Canada Healthy Man Generic Viagra Suppliers

заказать продвижение сайта 4 гейм логин в скайпе SEO PRO1

Сопровождение сайта

My revitalized number
http://arab.aunties.porndairy.in/?post.marie
gifts arrested lol originated starting

Girls with well-known tits blogs
http://asian.porndairy.in/?reserved-michaela
erotic wallpapers erotic pc game freepornos.com erotic animals

Aliexpress - get your star sales...

Enviar un comentario novo

O contido deste campo é privado e non se amosará publicamente.
  • As direccións das páxinas web e as direccións de correo se convirten en enlaces automáticamente.
  • Tags HTML permitidos: <a> <em> <strong> <cite> <code> <ul> <ol> <li> <dl> <dt> <dd>
  • Os saltos de liña e párrafo créanse automaticamente.

Máis información acerca das opcións de formato

Image CAPTCHA
Escriba os caracteres que se amosan na imaxe.