现象：误删除LV后察看表空间利用率失败，察看ALERT日志，发现如下错误：
Errors in file /home/oracle/admin/zhjport/bdump/zhjport1_dbw0_2871436.trc:
ORA-01186: file 81 failed verification tests
ORA-01157: cannot identify/lock data file 81 - see DBWR trace file
ORA-01110: data file 81: '/dev/rPAT_BAS_1'
1、 尝试重建LV看是否能恢复
mklv -T O -y PAT_BAS_1 oradatavg 1
mklv -T O -y PAT_BAS_2 oradatavg 1
chown oracle:dba PAT_BAS_1
chown oracle:dba PAT_BAS_2
chown oracle:dba rPAT_BAS_1
chown oracle:dba rPAT_BAS_2
发现故障依旧
2、查rPAT_BAS_1、rPAT_BAS_2数据文件属于哪个表空间
select status,TABLESPACE_NAME,CHECKPOINT_CHANGE#,name from v$datafile_header;
| STATUS
|
TABLESPACE_NAME
|
CHECKPOINT_CHANGE#
|
NAME
|
| ONLINE |
PAT_1 |
3177311020 |
/dev/rPAT_1 |
| ONLINE |
PAT_10 |
3177311020 |
/dev/rPAT_10 |
| ONLINE |
|
0 |
/dev/rPAT_BAS_1 |
| ONLINE |
|
0 |
/dev/rPAT_BAS_2 |
3、执行rPAT_BAS_1、rPAT_BAS_2数据文件下线：
ALTER DATABASE DATAFILE '/dev/rPAT_BAS_1' OFFLINE DROP;
ALTER DATABASE DATAFILE '/dev/rPAT_BAS_2' OFFLINE DROP;
| STATUS
|
TABLESPACE_NAME
|
CHECKPOINT_CHANGE#
|
NAME
|
| ONLINE |
PAT_1 |
3177311020 |
/dev/rPAT_1 |
| ONLINE |
PAT_10 |
3177311020 |
/dev/rPAT_10 |
| OFFLINE |
|
0 |
/dev/rPAT_BAS_1 |
| OFFLINE |
|
0 |
/dev/rPAT_BAS_2 |
4、此时已经可以查看表空间利用率
--表空间查询
Select a.Tablespace_Name, a.Total || 'M' Total_Space, (a.Total - b.Free) || 'M' Used_Space, To_Char((a.Total - b.Free) / a.Total * 100, '99.99') || '%' Pct_Free
From
(Select Tablespace_Name, Sum(Bytes) / 1024 / 1024 Total From Dba_Data_Files Group By tablespace_Name) a,
(Select Tablespace_Name, Sum(Bytes) / 1024 / 1024 Free From Dba_Free_Space Group By Tablespace_Name) b where a.Tablespace_Name = b.Tablespace_Name
本文转自zylhsy 51CTO博客，原文链接：http://blog.51cto.com/yunlongzheng/555504，如需转载请自行联系原作者
