diskgroup "DATA" space exhausted

Cyrill

飞总 早上好。

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
Sector  Block       AU  Total_MB  Free_MB  Req_mir_free_MB  Usable_file_MB  Offline_disks  Voting_files  Name
   512   4096  1048576    307195   152157                0          152157              0             N  DATA/
   512   4096  1048576      1019      623                0             207              2             Y  OCR/


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

xifenfei

lsdg 结果贴出来，我看下

Cyrill

ASMCMD> lsdg
State    Type    Rebal  Sector  Block       AU  Total_MB  Free_MB  Req_mir_free_MB  Usable_file_MB  Offline_disks  Voting_files  Name
MOUNTED  EXTERN  N         512   4096  1048576    307195   152157                0          152157              0             N  DATA/
MOUNTED  HIGH      N         512   4096  1048576      1019      623                0             207              2             Y  OCR/

xifenfei

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

xifenfei

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

Cyrill

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

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

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

xifenfei

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

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

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

Cyrill

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

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

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

travel.liu

把完整的脚本贴出来看看！用下面的脚本查下

1. select /* EXTERNAL REDUNDANCY */
   
2. g.name,
   
3. sum(d.TOTAL_MB) * min(d.FREE_MB / d.total_mb) /
   
4. decode(g.type, 'EXTERN', 1, 'NORMAL', 2, 'HIGH', 3, 1) "USABLE_FREE_MB"
   
5. from v$asm_disk d, v$asm_diskgroup g
   
6. where d.group_number = g.group_number
   
7. and g.type = 'EXTERN'
   
8. group by g.name, g.type
   
9. union
   
10. select /* NON EXTERNAL REDUNDANCY WITH SYMMETRIC FG */
    
11. g.name,
    
12. sum(d.TOTAL_MB) * min(d.FREE_MB / d.total_mb) /
    
13. decode(g.type, 'EXTERN', 1, 'NORMAL', 2, 'HIGH', 3, 1) "USABLE_FREE_MB"
    
14. from v$asm_disk d, v$asm_diskgroup g
    
15. where d.group_number = g.group_number
    
16. and g.group_number not in /* KEEP SYMMETRIC*/
    
17. (select distinct (group_number)
    
18. from (select group_number,
    
19. failgroup,
    
20. TOTAL_MB,
    
21. count_dsk,
    
22. greatest(lag(count_dsk, 1, 0)
    
23. over(partition by TOTAL_MB,
    
24. group_number order by TOTAL_MB,
    
25. FAILGROUP),
    
26. lead(count_dsk, 1, 0)
    
27. over(partition by TOTAL_MB,
    
28. group_number order by TOTAL_MB,
    
29. FAILGROUP)) as max_lag_lead,
    
30. count(distinct(failgroup)) over(partition by group_number, TOTAL_MB) as nb_fg_per_size,
    
31. count_fg
    
32. from (select group_number,
    
33. failgroup,
    
34. TOTAL_MB,
    
35. count(*) over(partition by group_number, failgroup, TOTAL_MB) as count_dsk,
    
36. count(distinct(failgroup)) over(partition by group_number) as count_fg
    
37. from v$asm_disk))
    
38. where count_dsk <> max_lag_lead
    
39. or nb_fg_per_size <> count_fg)
    
40. and g.type <> 'EXTERNAL'
    
41. group by g.name, g.type
    
42. union
    
43. select /* NON EXTERNAL REDUNDANCY WITH NON SYMMETRIC FG
    
44. AND DOES EXIST AT LEAST ONE DISK WITH PARTNERS OF DIFFERENT SIZE*/
    
45. name,
    
46. min(free) / decode(type, 'EXTERN', 1, 'NORMAL', 2, 'HIGH', 3, 1) "USABLE_FREE_MB"
    
47. from (select name,
    
48. disk_number,
    
49. free_mb / (factor / sum(factor) over(partition by name)) as free,
    
50. type
    
51. from (select name,
    
52. disk_number,
    
53. avg(free_mb) as free_mb,
    
54. avg(total_mb) as total_mb,
    
55. sum(factor_disk + factor_partner) as factor,
    
56. type
    
57. from (SELECT g.name,
    
58. g.type,
    
59. d.group_number as group_number,
    
60. d.disk_number disk_number,
    
61. d.total_mb as total_mb,
    
62. d.free_mb as free_mb,
    
63. p.number_kfdpartner "Partner disk#",
    
64. f.factor as factor_disk,
    
65. fp.factor as factor_partner
    
66. FROM x$kfdpartner p,
    
67. v$asm_disk d,
    
68. v$asm_diskgroup g,
    
69. (select disk_number,
    
70. group_number,
    
71. TOTAL_MB / min(total_mb) over(partition by group_number) as factor
    
72. from v$asm_disk
    
73. where state = 'NORMAL'
    
74. and mount_status = 'CACHED') f,
    
75. (select disk_number,
    
76. group_number,
    
77. TOTAL_MB / min(total_mb) over(partition by group_number) as factor
    
78. from v$asm_disk
    
79. where state = 'NORMAL'
    
80. and mount_status = 'CACHED') fp
    
81. WHERE p.disk = d.disk_number
    
82. and p.grp = d.group_number
    
83. and f.disk_number = d.disk_number
    
84. and f.group_number = d.group_number
    
85. and fp.disk_number = p.number_kfdpartner
    
86. and fp.group_number = p.grp
    
87. and d.group_number = g.group_number
    
88. and g.type <> 'EXTERN'
    
89. and g.group_number in /* KEEP NON SYMMETRIC */
    
90. (select distinct (group_number)
    
91. from (select group_number,
    
92. failgroup,
    
93. TOTAL_MB,
    
94. count_dsk,
    
95. greatest(lag(count_dsk, 1, 0)
    
96. over(partition by
    
97. TOTAL_MB,
    
98. group_number order by
    
99. TOTAL_MB,
    
100. FAILGROUP),
     
101. lead(count_dsk, 1, 0)
     
102. over(partition by
     
103. TOTAL_MB,
     
104. group_number order by
     
105. TOTAL_MB,
     
106. FAILGROUP)) as max_lag_lead,
     
107. count(distinct(failgroup)) over(partition by group_number, TOTAL_MB) as nb_fg_per_size,
     
108. count_fg
     
109. from (select group_number,
     
110. failgroup,
     
111. TOTAL_MB,
     
112. count(*) over(partition by group_number, failgroup, TOTAL_MB) as count_dsk,
     
113. count(distinct(failgroup)) over(partition by group_number) as count_fg
     
114. from v$asm_disk))
     
115. where count_dsk <> max_lag_lead
     
116. or nb_fg_per_size <> count_fg)
     
117. and d.group_number not in /* KEEP DG THAT DOES NOT CONTAIN AT LEAST ONE DISK HAVING PARTNERS OF DIFFERENT SIZE*/
     
118. (select distinct (group_number)
     
119. from (select d.group_number as group_number,
     
120. d.disk_number disk_number,
     
121. p.number_kfdpartner "Partner disk#",
     
122. f.factor as factor_disk,
     
123. fp.factor as factor_partner,
     
124. greatest(lag(fp.factor, 1, 0)
     
125. over(partition by
     
126. d.group_number,
     
127. d.disk_number order by
     
128. d.group_number,
     
129. d.disk_number),
     
130. lead(fp.factor, 1, 0)
     
131. over(partition by
     
132. d.group_number,
     
133. d.disk_number order by
     
134. d.group_number,
     
135. d.disk_number)) as max_lag_lead,
     
136. count(p.number_kfdpartner) over(partition by d.group_number, d.disk_number) as nb_partner
     
137. FROM x$kfdpartner p,
     
138. v$asm_disk d,
     
139. v$asm_diskgroup g,
     
140. (select disk_number,
     
141. group_number,
     
142. TOTAL_MB / min(total_mb) over(partition by group_number) as factor
     
143. from v$asm_disk
     
144. where state = 'NORMAL'
     
145. and mount_status = 'CACHED') f,
     
146. (select disk_number,
     
147. group_number,
     
148. TOTAL_MB / min(total_mb) over(partition by group_number) as factor
     
149. from v$asm_disk
     
150. where state = 'NORMAL'
     
151. and mount_status = 'CACHED') fp
     
152. WHERE p.disk = d.disk_number
     
153. and p.grp = d.group_number
     
154. and f.disk_number = d.disk_number
     
155. and f.group_number = d.group_number
     
156. and fp.disk_number =
     
157. p.number_kfdpartner
     
158. and fp.group_number = p.grp
     
159. and d.group_number = g.group_number
     
160. and g.type <> 'EXTERN')
     
161. where factor_partner <> max_lag_lead
     
162. and nb_partner > 1))
     
163. group by name, disk_number, type))
     
164. group by name, type;

复制代码

Cyrill

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

SQL> @ /tmp/check.sql

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