如何在Oracle中释放flash_recovery_area. W! l$ j; R; r( I4 }& Z3 W
2011-11-25 13:56
: N/ [+ c+ B* S; ~1 M/ G如何在Oracle中释放flash_recovery_area5 y' S7 \+ v3 z. J+ W
2011-05-09 www.hongtunet.cn1 j7 Y# I( F' O. A+ d$ t8 x
案例：Oracle数据库10g中释放flash_recovery_area解决ORA-19815错误。
$ U+ Q, R% T# X: [错误现象：备份Oracle数据库10g时出现下面的错误：$ A5 _; m! E' q) _7 T9 i
ORA-19815: WARNING: db_recovery_file_dest_size of 2147483648 bytes is 100.00% used, and has 0 remaining bytes available.
1 g& T( P: u$ y1 P* c*************************************************************$ K7 X7 u9 r( N# Q
You have the following choices to free up space from# T* @; R: B4 r
flash recovery area:( B& y+ j- D6 J! E
1. Consider changing your RMAN retention policy.
1 Z' r( B. b7 ]If you are using dataguard, then consider changing your% g! r' ]! N# g6 ?) U' |, m
RMAN archivelog deletion policy.
( @( t5 H/ U8 E( _6 Q8 p& n2. Backup files to tertiary device such as tape using the, f- R/ }$ c" t( H2 p' I
RMAN command BACKUP RECOVERY AREA.
7 G9 F% W6 q) _. \3. Add disk space and increase the db_recovery_file_dest_size
8 Z v$ B" J0 o( cparameter to reflect the new space.) G) q4 k* o, A* }
4. Delete unncessary files using the RMAN DELETE command.& `/ ` S5 U0 {9 u R* V* Z
If an OS command was used to delete files, then use6 u* | ~9 s' f
RMAN CROSSCHECK and DELETE EXPIRED commands.: `' ^* }! N! y6 K$ U k- u* B
*************************************************************
W% V4 N- J$ R3 q, Y1 e此时flash_recovery_area已经手工释放空间，即使切换到一个全新的磁盘也无法解决此问题。
, j; b% U& P. l3 L/ m继续连接数据库的查询：0 C% E {, S* c- z5 a1 ?+ k8 R
$ sqlplus "/ as sysdba"* ~0 v, A# ~* m ^. @& _
SQL*Plus: Release 10.1.0.2.0 - Production on Mon Mar 28 11:45:30 2005
4 f% r! T; c. m7 ~7 X z5 O" ACopyright (c) 1982, 2004, Oracle. All rights reserved.
( @- v4 [% p9 p" K& l+ H" J; E3 T0 Q$ RConnected to:1 Z. }$ J: U- w+ n5 ?. A
Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - 64bit Production
" s H- O- l. z# o& MWith the Partitioning, OLAP and Data Mining options: e0 P0 N& u: O0 |5 h9 v
SYS AS SYSDBA on 28-MAR-05 >set liesize 120
5 w! |8 V& l) Y+ a4 c% U9 k' ISP2-0158: unknown SET option "liesize"
0 D, b- u2 M7 M B; ]0 Y" iSYS AS SYSDBA on 28-MAR-05 >set linesize 1209 L: b1 f% l6 p' n* P$ k
SYS AS SYSDBA on 28-MAR-05 >SELECT substr(name, 1, 30) name, space_limit AS quota,+ Q( a4 J( O& l
2 space_used AS used,
# Q& G! L! \3 o+ U. L% w; j1 D6 v3 space_reclaimable AS reclaimable,& g- O( Q* U4 i# C) ]
4 number_of_files AS files4 m2 V7 ^. t) R/ P' j; Y
5 FROM v$recovery_file_dest ;3 R- D' R9 X' ?- o: ?
NAME QUOTA USED RECLAIMABLE FILES7 j5 ? d# S7 E; g1 `# X8 h
---------------------------- ---------- ---------- ----------- ----------
7 R6 M C- O0 P/data5/flash_recovery_area 2147483648 2144863232 0 227
1 s* F# G' V; S7 _2 l此时发现仍然记录了227个文件，USED空间仍然未被释放。
: z0 A! ]: [. g5 h) E继续使用rman登录数据库来进行crosscheck：
# c1 |, ^: g5 s6 Q: {) g, c) Z$ rman target /
0 t8 H3 l% x% D3 cRecovery Manager: Release 10.1.0.2.0 - 64bit Production
2 v1 z O9 k. b1 W9 s# s- pCopyright (c) 1995, 2004, Oracle. All rights reserved.8 k# ~. x; a1 ~1 ?& D* I# Q F- `
connected to target database: EYGLE (DBID=1337390772)
5 }4 N4 b4 Z6 x3 ~) u; ~7 M. O8 U" ?RMAN> crosscheck archivelog all;: m! Y3 d0 e8 _7 N2 x1 S
using target database controlfile instead of recovery catalog
1 F5 W G) U& ]allocated channel: ORA_DISK_18 _! \( y7 z; C$ S5 y
channel ORA_DISK_1: sid=144 devtype=DISK
4 c1 C7 |$ V3 T( ]6 @validation failed for archived log# r; v# \8 W3 B7 _5 }+ _% F5 _7 e* H
archive log filename=/opt/oracle/flash_recovery_area/EYGLE/' m' m8 j H j8 v b: `
archivelog/2004_05_17/o1_mf_1_790_0bjq36ps_.arc recid=1 stamp=526401126# }5 z9 b" d, H2 }: _) p1 P) x( y
validation failed for archived log
. o: |$ [8 O+ ?1 z# I! s' A2 Darchive log filename=/opt/oracle/flash_recovery_area/EYGLE/
# D: K3 _- ^: T& m1 j: \: k: Z! j! O! x5 }archivelog/2004_05_17/o1_mf_1_791_0bkbcy7x_.arc recid=2 stamp=526420862
/ m$ u5 a. u. I( evalidation failed for archived log
8 o v0 P& T+ C' c& k; G0 Sarchive log filename=/opt/oracle/flash_recovery_area/EYGLE/, l8 A3 P) |3 s0 F
archivelog/2004_05_17/o1_mf_1_792_0bkkds4d_.arc recid=3 stamp=526428057( N7 r2 J# U% j7 r
.......
, W% \4 U l" k6 i7 p$ }- `) xarchive log filename=/opt/oracle/flash_recovery_area/EYGLE/
3 i$ s r+ w2 K9 P0 rarchivelog/2004_07_16/o1_mf_1_1014_0hh3zsrp_.arc recid=225 stamp=531678074! {: @5 M& V3 \! N9 C5 m- G6 a
validation failed for archived log' R$ K8 C& i2 q; W! ~ c
archive log filename=/opt/oracle/flash_recovery_area/EYGLE/( f& D5 s6 q) [
archivelog/2004_07_16/o1_mf_1_1015_0hh40qyp_.arc recid=226 stamp=531678104& H* } e" W1 A) ~$ S3 t* ]+ \
validation failed for archived log7 U: Y( A: t$ t5 \! i1 g. G
archive log filename=/opt/oracle/flash_recovery_area/EYGLE/* G- h+ Q9 j9 b$ b. u s
archivelog/2004_07_16/o1_mf_1_1016_0hh41jqq_.arc recid=227 stamp=531678129, x( B% A! u. S8 ^, K& K3 j& P
Crosschecked 227 objects& f( K- X" _ ?! M, p0 @* _5 y
RMAN> delete expired archivelog all;4 p: w7 N7 g9 T3 Q+ d( S
released channel: ORA_DISK_1
, W$ [. H! ^6 G5 c: Q& w! Tallocated channel: ORA_DISK_1$ x9 g, V Z% y5 f4 w b
channel ORA_DISK_1: sid=144 devtype=DISK
: G0 _8 m2 W7 ?6 b! R# t+ y: xList of Archived Log Copies, o" i& N& \" @( h) `
Key Thrd Seq S Low Time Name
p2 P3 F8 H3 S0 ^: Z$ y/ H7 `) p- ~& W------- ---- ------- - --------- ----
+ ]& W9 S4 @, Y/ P7 L, J' h1 1 790 X 17-MAY-04 /opt/oracle/flash_recovery_area/EYGLE/ w, v1 K( [) m2 X& ]
archivelog/2004_05_17/o1_mf_1_790_0bjq36ps_.arc# V: Y1 k8 r+ t: R. |
2 1 791 X 17-MAY-04 /opt/oracle/flash_recovery_area/EYGLE/
! i* l$ Y4 w' p3 P- `* ~archivelog/2004_05_17/o1_mf_1_791_0bkbcy7x_.arc3 \- x/ B0 g+ g' H
3 1 792 X 17-MAY-04 /opt/oracle/flash_recovery_area/EYGLE/1 j6 a3 B7 x/ h7 _
archivelog/2004_05_17/o1_mf_1_792_0bkkds4d_.arc' s/ O# y+ V$ `* m' l5 v5 ^
.......
) H5 ?, _9 V3 S2 l' F7 e225 1 1014 X 16-JUL-04 /opt/oracle/flash_recovery_area/EYGLE/; z3 q7 P }, f$ }% A1 E b
archivelog/2004_07_16/o1_mf_1_1014_0hh3zsrp_.arc
, L2 v T" H' L: \% b226 1 1015 X 16-JUL-04 /opt/oracle/flash_recovery_area/EYGLE/ |
发表于 2012-8-4 13:54:49
楼主