ORACLE SOS-常规手段恢复-备份与恢复-diskgroup "DATA" space exhausted

Cyrill 发表于 2014-3-28 10:02:30

diskgroup "DATA" space exhausted

飞总早上好。

rman restore报错如下
ORA-19504: failed to create file "+DATA/gzpb/datafile/epointbid_pb2011_02.dbf"
ORA-17502: ksfdcre:4 Failed to create file +DATA/gzpb/datafile/epointbid_pb2011_02.dbf
ORA-15041: diskgroup "DATA" space exhausted

应该是磁盘组的空间不够了。要恢复的数据有124G，我查看了磁盘组空间，剩下152G。
ASMCMD> ls -s
SectorBlock    AUTotal_MBFree_MBReq_mir_free_MBUsable_file_MBOffline_disksVoting_filesName
512 40961048576 307195 152157             0       152157          0          NDATA/
512 40961048576    1019    623             0          207          2          YOCR/

为什么还无法恢复？
能不能把+DATA\DATAFILE 下的文件删掉，腾出空间用来恢复？

xifenfei 发表于 2014-3-28 10:10:07

lsdg 结果贴出来，我看下

Cyrill 发表于 2014-3-28 10:14:05

ASMCMD> lsdg
State Type RebalSectorBlock    AUTotal_MBFree_MBReq_mir_free_MBUsable_file_MBOffline_disksVoting_filesName
MOUNTEDEXTERNN       512 40961048576 307195 152157             0       152157          0          NDATA/
MOUNTEDHIGH    N       512 40961048576    1019    623             0          207          2          YOCR/

xifenfei 发表于 2014-3-28 10:20:24

1. 可以通过rm删除asm dg里面的文件
2. 从这里看，还有150G以上的空间，你的数据文件是bigfile？

xifenfei 发表于 2014-3-28 10:24:34

还有一种可能性，就是你同时多个进程或者多个文件在还原，刚好到了该文件的时候报错了

Cyrill 发表于 2014-3-28 10:27:43

xifenfei 发表于 2014-3-28 10:20
1. 可以通过rm删除asm dg里面的文件
2. 从这里看，还有150G以上的空间，你的数据文件是bigfile？ ...

rmdatafile下的文件对数据库恢复没影响吧？

不是bigfile，只是普通备份文件而已

xifenfei 发表于 2014-3-28 10:29:21

Cyrill 发表于 2014-3-28 10:27
rmdatafile下的文件对数据库恢复没影响吧？

不是bigfile，只是普通备份文件而已 ...

确定文件不要了，删除掉没有问题，和你在文件系统里面删除东西一样

Cyrill 发表于 2014-3-28 10:40:34

Cyrill 发表于 2014-3-28 10:27
rmdatafile下的文件对数据库恢复没影响吧？

不是bigfile，只是普通备份文件而已 ...

腾出了170G的空间，但是restore的时候还是报了空间满了。想不明白~
bigfile怎么看？

travel.liu 发表于 2014-3-28 12:49:01

把完整的脚本贴出来看看！用下面的脚本查下select /* EXTERNAL REDUNDANCY */
g.name,
sum(d.TOTAL_MB) * min(d.FREE_MB / d.total_mb) /
decode(g.type, 'EXTERN', 1, 'NORMAL', 2, 'HIGH', 3, 1) "USABLE_FREE_MB"
from v$asm_disk d, v$asm_diskgroup g
where d.group_number = g.group_number
and g.type = 'EXTERN'
group by g.name, g.type
union
select /* NON EXTERNAL REDUNDANCY WITH SYMMETRIC FG */
g.name,
sum(d.TOTAL_MB) * min(d.FREE_MB / d.total_mb) /
decode(g.type, 'EXTERN', 1, 'NORMAL', 2, 'HIGH', 3, 1) "USABLE_FREE_MB"
from v$asm_disk d, v$asm_diskgroup g
where d.group_number = g.group_number
and g.group_number not in /* KEEP SYMMETRIC*/
(select distinct (group_number)
from (select group_number,
failgroup,
TOTAL_MB,
count_dsk,
greatest(lag(count_dsk, 1, 0)
over(partition by TOTAL_MB,
group_number order by TOTAL_MB,
FAILGROUP),
lead(count_dsk, 1, 0)
over(partition by TOTAL_MB,
group_number order by TOTAL_MB,
FAILGROUP)) as max_lag_lead,
count(distinct(failgroup)) over(partition by group_number, TOTAL_MB) as nb_fg_per_size,
count_fg
from (select group_number,
failgroup,
TOTAL_MB,
count(*) over(partition by group_number, failgroup, TOTAL_MB) as count_dsk,
count(distinct(failgroup)) over(partition by group_number) as count_fg
from v$asm_disk))
where count_dsk <> max_lag_lead
or nb_fg_per_size <> count_fg)
and g.type <> 'EXTERNAL'
group by g.name, g.type
union
select /* NON EXTERNAL REDUNDANCY WITH NON SYMMETRIC FG
AND DOES EXIST AT LEAST ONE DISK WITH PARTNERS OF DIFFERENT SIZE*/
name,
min(free) / decode(type, 'EXTERN', 1, 'NORMAL', 2, 'HIGH', 3, 1) "USABLE_FREE_MB"
from (select name,
disk_number,
free_mb / (factor / sum(factor) over(partition by name)) as free,
type
from (select name,
disk_number,
avg(free_mb) as free_mb,
avg(total_mb) as total_mb,
sum(factor_disk + factor_partner) as factor,
type
from (SELECT g.name,
g.type,
d.group_number as group_number,
d.disk_number disk_number,
d.total_mb as total_mb,
d.free_mb as free_mb,
p.number_kfdpartner "Partner disk#",
f.factor as factor_disk,
fp.factor as factor_partner
FROM x$kfdpartner p,
v$asm_disk d,
v$asm_diskgroup g,
(select disk_number,
group_number,
TOTAL_MB / min(total_mb) over(partition by group_number) as factor
from v$asm_disk
where state = 'NORMAL'
and mount_status = 'CACHED') f,
(select disk_number,
group_number,
TOTAL_MB / min(total_mb) over(partition by group_number) as factor
from v$asm_disk
where state = 'NORMAL'
and mount_status = 'CACHED') fp
WHERE p.disk = d.disk_number
and p.grp = d.group_number
and f.disk_number = d.disk_number
and f.group_number = d.group_number
and fp.disk_number = p.number_kfdpartner
and fp.group_number = p.grp
and d.group_number = g.group_number
and g.type <> 'EXTERN'
and g.group_number in /* KEEP NON SYMMETRIC */
(select distinct (group_number)
from (select group_number,
failgroup,
TOTAL_MB,
count_dsk,
greatest(lag(count_dsk, 1, 0)
over(partition by
TOTAL_MB,
group_number order by
TOTAL_MB,
FAILGROUP),
lead(count_dsk, 1, 0)
over(partition by
TOTAL_MB,
group_number order by
TOTAL_MB,
FAILGROUP)) as max_lag_lead,
count(distinct(failgroup)) over(partition by group_number, TOTAL_MB) as nb_fg_per_size,
count_fg
from (select group_number,
failgroup,
TOTAL_MB,
count(*) over(partition by group_number, failgroup, TOTAL_MB) as count_dsk,
count(distinct(failgroup)) over(partition by group_number) as count_fg
from v$asm_disk))
where count_dsk <> max_lag_lead
or nb_fg_per_size <> count_fg)
and d.group_number not in /* KEEP DG THAT DOES NOT CONTAIN AT LEAST ONE DISK HAVING PARTNERS OF DIFFERENT SIZE*/
(select distinct (group_number)
from (select d.group_number as group_number,
d.disk_number disk_number,
p.number_kfdpartner "Partner disk#",
f.factor as factor_disk,
fp.factor as factor_partner,
greatest(lag(fp.factor, 1, 0)
over(partition by
d.group_number,
d.disk_number order by
d.group_number,
d.disk_number),
lead(fp.factor, 1, 0)
over(partition by
d.group_number,
d.disk_number order by
d.group_number,
d.disk_number)) as max_lag_lead,
count(p.number_kfdpartner) over(partition by d.group_number, d.disk_number) as nb_partner
FROM x$kfdpartner p,
v$asm_disk d,
v$asm_diskgroup g,
(select disk_number,
group_number,
TOTAL_MB / min(total_mb) over(partition by group_number) as factor
from v$asm_disk
where state = 'NORMAL'
and mount_status = 'CACHED') f,
(select disk_number,
group_number,
TOTAL_MB / min(total_mb) over(partition by group_number) as factor
from v$asm_disk
where state = 'NORMAL'
and mount_status = 'CACHED') fp
WHERE p.disk = d.disk_number
and p.grp = d.group_number
and f.disk_number = d.disk_number
and f.group_number = d.group_number
and fp.disk_number =
p.number_kfdpartner
and fp.group_number = p.grp
and d.group_number = g.group_number
and g.type <> 'EXTERN')
where factor_partner <> max_lag_lead
and nb_partner > 1))
group by name, disk_number, type))
group by name, type;

Cyrill 发表于 2014-3-28 14:03:50

travel.liu 发表于 2014-3-28 12:49
把完整的脚本贴出来看看！用下面的脚本查下

SQL> @ /tmp/check.sql

NAME                         USABLE_FREE_MB
------------------------------ --------------
DATA                            208382.661
OCR                                     623

页: [1] 2

ORACLE SOS's Archiver

diskgroup "DATA" space exhausted