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