nologging_corrupted_block_recovery

1、创建表create table t1 (i int ,name varchar(10));

2、rman备库

3、批量插入数据

alter table t2 nologging;

declare
maxrecords constant int:=100000000;
i int :=1;
begin
   for i in 1..maxrecords loop
     insert /*+ append */ into t1 nologging select i,'jssjtest' from dual;
          commit;
   end loop;
   commit;
end;

4、关机或者shutdown abort

5、启动startup

6、删除文件datafile(可以直接恢复了)，或者是echo /dev/null > datafile1.dbf（模拟错误）

7、查看聚合函数，全表扫表，select count(1) from t1;发生坏块

8、alter tablespace t1 offline；

9、rman restore tablespace t1，recover tablespace t1

10、alter tablespace online

11、查看select count(1) from t1;

SQL> select count(1) from t1;
select count(1) from t1
*
第 1 行出现错误:
ORA-01578: ORACLE 数据块损坏 (文件号 6, 块号 642)
ORA-01110: 数据文件 6: '/u01/app/oracle/datafile/datafile1.dbf'
ORA-26040: 数据块是使用 NOLOGGING 选项加载的

12、设置忽略坏块

SQL> alter session set events '10231 trace name context forever,level 10';

会话已更改。

13、重新创建表

SQL> create table t1_1 as select * from t1;

表已创建。

14、重新命名表

SQL> rename t1 to t1_bak;

表已重命名。

SQL> rename t1_1 to t1;

表已重命名。

15、关闭事件

alter session set events '10231 trace name context off';

如果有索引或者其他的还需要重新创建

alter index indexname rebuild

最后

a、查看不可恢复状态

select t.FILE#,t.TS#,t.STATUS, t.UNRECOVERABLE_CHANGE# , t.UNRECOVERABLE_time from v$datafile t

1 1 0 SYSTEM 0
2 2 1 ONLINE 0
3 3 2 ONLINE 0
4 4 4 ONLINE 0
5 5 6 ONLINE 0
6 6 7 ONLINE 1098279 2014/12/10 12:28:03

b、查看trc

SQL> alert system dump datafile 6 block 642
SP2-0734: 未知的命令开头 "alert syst..." - 忽略了剩余的行。
SQL> conn / as sysdba
已连接。
SQL> alter system dump datafile 6 block 642;

系统已更改。

[root@ora11g trace]# cat ora_ora_4510.trc | more
Trace file /u01/app/oracle/diag/rdbms/ora/ora/trace/ora_ora_4510.trc
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
ORACLE_HOME = /u01/app/oracle/11.2.0/db_1
System name:    Linux
Node name:      ora11g.localdomain
Release:        2.6.18-194.el5
Version:        #1 SMP Fri Apr 2 14:58:14 EDT 2010
Machine:        x86_64
Instance name: ora
Redo thread mounted by this instance: 1
Oracle process number: 30
Unix process pid: 4510, image: oracle@ora11g.localdomain (TNS V1-V3)

*** 2014-12-10 14:28:55.222
*** SESSION ID:(141.49) 2014-12-10 14:28:55.222
*** CLIENT ID:() 2014-12-10 14:28:55.222
*** SERVICE NAME:(SYS$USERS) 2014-12-10 14:28:55.222
*** MODULE NAME:(sqlplus@ora11g.localdomain (TNS V1-V3)) 2014-12-10 14:28:55.222
*** ACTION NAME:() 2014-12-10 14:28:55.222

Start dump data blocks tsn: 7 file#:6 minblk 642 maxblk 642
Block dump from cache:
Dump of buffer cache at level 4 for tsn=7, rdba=25166466
BH (0x86bd96a8) file#: 6 rdba: 0x01800282 (6/642) class: 1 ba: 0x86828000
  set: 12 pool 3 bsz: 8192 bsi: 0 sflg: 2 pwc: 99,28
  dbwrid: 0 obj: 74582 objn: 74582 tsn: 7 afn: 6 hint: f
  hash: [0x9476b170,0x9476b170] lru: [0x84ffb640,0x853eac40]
  ckptq: [NULL] fileq: [NULL] objq: [0x84ffb668,0x853eac68]
  st: XCURRENT md: NULL tch: 1
  flags: only_sequential_access auto_bmr_tried
  LRBA: [0x0.0.0] LSCN: [0x0.0] HSCN: [0xffff.ffffffff] HSUB: [65535]
  cr pin refcnt: 0 sh pin refcnt: 0
Block dump from disk:
buffer tsn: 7 rdba: 0x01800282 (6/642)
scn: 0x0000.0010bece seq: 0xff flg: 0x04 tail: 0xbece00ff
frmt: 0x02 chkval: 0xa512 type: 0x00=unknown
Hex dump of block: st=0, typ_found=0
Dump of memory from 0x00002BA672ABAA00 to 0x00002BA672ABCA00
2BA672ABAA00 0000A200 01800282 0010BECE 04FF0000  [................]
2BA672ABAA10 0000A512 FFFFFFFF FFFFFFFF FFFFFFFF  [................]
2BA672ABAA20 FFFFFFFF FFFFFFFF FFFFFFFF FFFFFFFF  [................]
        Repeat 508 times
2BA672ABC9F0 FFFFFFFF FFFFFFFF FFFFFFFF BECE00FF  [................]
End dump data blocks tsn: 7 file#: 6 minblk 642 maxblk 642

可以看出block 642除了头部信息外，剩下的都是‘FFFFFFFF’，应该是一个逻辑坏块！

转出log文件

select t.MEMBERS,t.group#,t1.MEMBER,t.sequence# ,t.status,t.ARCHIVED from v$log t ,v$logfile t1 where t.GROUP#=t1.GROUP#;

1 2 3 +DATA/ora/onlinelog/group_3.263.865878993 18 INACTIVE YES
2 2 3 +FRA/ora/onlinelog/group_3.259.865878997 18 INACTIVE YES
3 2 2 +DATA/ora/onlinelog/group_2.262.865878989 17 INACTIVE YES
4 2 2 +FRA/ora/onlinelog/group_2.258.865878993 17 INACTIVE YES
5 2 1 +DATA/ora/onlinelog/group_1.261.865878985 19 CURRENT NO
6 2 1 +FRA/ora/onlinelog/group_1.257.865878987 19 CURRENT NO

[grid@ora11g ~]$ amdu
amdu_2014_12_10_15_26_11/
[grid@ora11g ~]$ ll

[grid@ora11g amdu_2014_12_10_15_15_25]$ amdu -diskstring 'ORCL:VOL*' -extract data.259
amdu_2014_12_10_15_18_36/
AMDU-00204: Disk N0001 is in currently mounted diskgroup DATA
AMDU-00201: Disk N0001: 'ORCL:VOL1'
AMDU-00204: Disk N0002 is in currently mounted diskgroup DATA
AMDU-00201: Disk N0002: 'ORCL:VOL2'
AMDU-00204: Disk N0003 is in currently mounted diskgroup DATA
AMDU-00201: Disk N0003: 'ORCL:VOL3'
[grid@ora11g amdu_2014_12_10_15_15_25]$ ll
[grid@ora11g ~]$ cat amdu_2014_12_10_15_26_11/report.txt

******************************* AMDU Settings ********************************
ORACLE_HOME = /u01/app/grid/11.2.0
System name:    Linux
Node name:      ora11g.localdomain
Release:        2.6.18-194.el5
Version:        #1 SMP Fri Apr 2 14:58:14 EDT 2010
Machine:        x86_64
amdu run:       10-DEC-14 15:26:11
Endianess:      1

--------------------------------- Operations ---------------------------------

------------------------------- Disk Selection -------------------------------
-diskstring ''

------------------------------ Reading Control -------------------------------

------------------------------- Output Control -------------------------------

********************************* DISCOVERY **********************************

----------------------------- DISK REPORT N0001 ------------------------------
                Disk Path: ORCL:VOL1
           Unique Disk ID:
               Disk Label: VOL1
     Physical Sector Size: 512 bytes
                Disk Size: 20473 megabytes
               Group Name: DATA
                Disk Name: VOL1
       Failure Group Name: VOL1
              Disk Number: 0
            Header Status: 3
       Disk Creation Time: 2014/12/09 17:25:42.400000
          Last Mount Time: 2014/12/10 10:04:38.011000
    Compatibility Version: 0x0b200000(11020000)
         Disk Sector Size: 512 bytes
         Disk size in AUs: 20473 AUs
         Group Redundancy: 3
      Metadata Block Size: 4096 bytes
                  AU Size: 1048576 bytes
                   Stride: 113792 AUs
      Group Creation Time: 2014/12/09 17:25:42.212000
  File 1 Block 1 location: AU 2
              OCR Present: NO

----------------------------- DISK REPORT N0002 ------------------------------
                Disk Path: ORCL:VOL2
           Unique Disk ID:
               Disk Label: VOL2
     Physical Sector Size: 512 bytes
                Disk Size: 20473 megabytes
               Group Name: DATA
                Disk Name: VOL2
       Failure Group Name: VOL2
              Disk Number: 1
            Header Status: 3
       Disk Creation Time: 2014/12/09 17:25:42.400000
          Last Mount Time: 2014/12/10 10:04:38.011000
    Compatibility Version: 0x0b200000(11020000)
         Disk Sector Size: 512 bytes
         Disk size in AUs: 20473 AUs
         Group Redundancy: 3
      Metadata Block Size: 4096 bytes
                  AU Size: 1048576 bytes
                   Stride: 113792 AUs
      Group Creation Time: 2014/12/09 17:25:42.212000
  File 1 Block 1 location: AU 2
              OCR Present: NO

----------------------------- DISK REPORT N0003 ------------------------------
                Disk Path: ORCL:VOL3
           Unique Disk ID:
               Disk Label: VOL3
     Physical Sector Size: 512 bytes
                Disk Size: 20473 megabytes
               Group Name: DATA
                Disk Name: VOL3
       Failure Group Name: VOL3
              Disk Number: 2
            Header Status: 3
       Disk Creation Time: 2014/12/09 17:25:42.400000
          Last Mount Time: 2014/12/10 10:04:38.011000
    Compatibility Version: 0x0b200000(11020000)
         Disk Sector Size: 512 bytes
         Disk size in AUs: 20473 AUs
         Group Redundancy: 3
      Metadata Block Size: 4096 bytes
                  AU Size: 1048576 bytes
                   Stride: 113792 AUs
      Group Creation Time: 2014/12/09 17:25:42.212000
  File 1 Block 1 location: AU 2
              OCR Present: NO

----------------------------- DISK REPORT N0004 ------------------------------
                Disk Path: ORCL:VOL4
           Unique Disk ID:
               Disk Label: VOL4
     Physical Sector Size: 512 bytes
                Disk Size: 20473 megabytes
               Group Name: FRA
                Disk Name: VOL4
       Failure Group Name: VOL4
              Disk Number: 0
            Header Status: 3
       Disk Creation Time: 2014/12/09 17:26:54.363000
          Last Mount Time: 2014/12/10 10:04:38.093000
    Compatibility Version: 0x0b200000(11020000)
         Disk Sector Size: 512 bytes
         Disk size in AUs: 20473 AUs
         Group Redundancy: 1
      Metadata Block Size: 4096 bytes
                  AU Size: 1048576 bytes
                   Stride: 113792 AUs
      Group Creation Time: 2014/12/09 17:26:54.305000
  File 1 Block 1 location: AU 2
              OCR Present: NO

******************************* END OF REPORT ********************************

SQL> conn loge/loge;
已连接。
SQL> select * from t2;

         I NAME
---------- ----------
         2 py

SQL> insert into t2 values(1,'loge');

已创建 1 行。

SQL> commit;

提交完成。

[grid@ora11g amdu_2014_12_10_15_15_25]$ amdu -diskstring 'ORCL:VOL4' -extract fra.257
amdu_2014_12_10_15_24_00/
AMDU-00204: Disk N0001 is in currently mounted diskgroup FRA
AMDU-00201: Disk N0001: 'ORCL:VOL4'
[grid@ora11g amdu_2014_12_10_15_15_25]$ strings
amdu_2014_12_10_15_24_00/ FRA_256.f                 report.txt
[grid@ora11g amdu_2014_12_10_15_15_25]$ strings amdu_2014_12_10_15_24_00/
FRA_257.f   report.txt 
[grid@ora11g amdu_2014_12_10_15_15_25]$ strings amdu_2014_12_10_15_24_00/FRA_257.f  | grep gelong
GMgelong_Q

提示转存的文件时二进制，可以直接在linux下使用

如果想dump的话可以使用如下转出文件

amdu -diskstring 'ORCL:VOL4' -dump fra.2