分类： Oracle
2011-01-04 15:15:44
ORA - 00257 故障处理
[ oracle @ test250 ~] $ sqlplus XX_js / XX_js
SQL * Plus : Release 10.2.0.1.0 - Production on Mon Nov 8 15 : 25 : 40 2010
Copyright ( c ) 1982 , 2005 , Oracle . All rights reserved .
ERROR :
ORA - 00257 : archiver error . Connect internal only , until freed .
马上来查一下错误描述。
SQL > ! oerr ora 257
00257 , 00000 , "archiver error. Connect internal only, until freed."
// * Cause : The archiver process received an error while trying to archive
// a redo log . If the problem is not resolved soon , the database
// will stop executing transactions . The most likely cause of this
// message is the destination device is out of space to store the
// redo log file .
// * Action : Check archiver trace file for a detailed description
// of the problem . Also verify that the
// device specified in the initialization parameter
// ARCHIVE_LOG_DEST is set up properly for archiving .
看来是 archivelog 的事情啊，看看 ARCHIVE_LOG_DEST 的设置。
SQL > archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 240
Next log sequence to archive 240
Current log sequence 242
想想是不是闪回区满了啊？
[ oracle @ test250 archivelog ] $ du - sh *
4.0 K 2010 _10_26
294 M 2010 _10_27
98 M 2010 _10_28
196 M 2010 _10_29
196 M 2010 _10_30
685 M 2010 _10_31
98 M 2010 _11_01
196 M 2010 _11_02
98 M 2010 _11_03
115 M 2010 _11_04
4.0 K 2010 _11_05
4.0 K 2010 _11_06
4.0 K 2010 _11_07
4.0 K 2010 _11_08
[ oracle @ test250 archivelog ] $ cd ..
[ oracle @ test250 CWT ] $ du - sh *
2.0 G archivelog
好几天没日志了， 2 G 了，果然满了。
[ oracle @ test250 archivelog ] $ rm - rf 2010 _10_ *
先来删除一下 10 月的日志
[ root @ test250 CWT ] # du -sh *
702 M archivelog
再来登录一下
[ oracle @ test250 archivelog ] $ sqlplus XX_js / XX_js
SQL * Plus : Release 10.2.0.1.0 - Production on Mon Nov 8 15 : 30 : 53 2010
Copyright ( c ) 1982 , 2005 , Oracle . All rights reserved .
ERROR :
ORA - 00257 : archiver error . Connect internal only , until freed .
还是不行啊。。
再来看看 alert 日志
[ root @ test250 ~] # tail -f /oracle/admin/cwt/bdump/alert_cwt.log
Mon Nov 8 15 : 37 : 07 2010
Errors in file / oracle / admin / cwt / bdump / cwt_arc1_2861 . trc :
ORA - 19815 : WARNING : db_recovery_file_dest_size of 2147483648 bytes is 100.00 % used , and has 0 remaining bytes available .
Mon Nov 8 15 : 37 : 07 2010
************************************************************************
You have following choices to free up space from flash recovery area :
1. Consider changing RMAN RETENTION POLICY . If you are using Data Guard ,
then consider changing RMAN ARCHIVELOG DELETION POLICY .
2. Back up files to tertiary device such as tape using RMAN
BACKUP RECOVERY AREA command .
3. Add disk space and increase db_recovery_file_dest_size parameter to
reflect the new space .
4. Delete unnecessary files using RMAN DELETE command . If an operating
system command was used to delete files , then use RMAN CROSSCHECK and
DELETE EXPIRED commands .
************************************************************************
主要的意思是让我们用 rman 清理 archivelog 。
那我们就来删除一下吧
[ oracle @ test250 bdump ] $ rman target /
Recovery Manager : Release 10.2.0.1.0 - Production on Mon Nov 8 15 : 35 : 30 2010
Copyright ( c ) 1982 , 2005 , Oracle . All rights reserved .
connected to target database : CWT ( DBID = 2677153359 )
RMAN > crosscheck archivelog all ;
released channel : ORA_DISK_1
allocated channel : ORA_DISK_1
channel ORA_DISK_1 : sid = 299 devtype = DISK
validation failed for archived log
archive log filename =/ oracle / flash_recovery_area / CWT / archivelog / 2010 _10_27 / o1_mf_1_219_6dh57tb4_ . arc recid = 42 stamp = 733489027
validation failed for archived log
archive log filename =/ oracle / flash_recovery_area / CWT / archivelog / 2010 _10_27 / o1_mf_1_220_6dh5845p_ . arc recid = 43 stamp = 733489036
validation failed for archived log
archive log filename =/ oracle / flash_recovery_area / CWT / archivelog / 2010 _10_27 / o1_mf_1_221_6djd3qy1_ . arc recid = 44 stamp = 733528856
validation failed for archived log
archive log filename =/ oracle / flash_recovery_area / CWT / archivelog / 2010 _10_28 / o1_mf_1_222_6dksv1dl_ . arc recid = 45 stamp = 733575660
validation failed for archived log
archive log filename =/ oracle / flash_recovery_area / CWT / archivelog / 2010 _10_29 / o1_mf_1_223_6dmc1s56_ . arc recid = 46 stamp = 733626046
validation failed for archived log
archive log filename =/ oracle / flash_recovery_area / CWT / archivelog / 2010 _10_29 / o1_mf_1_224_6do4hzn8_ . arc recid = 47 stamp = 733684875
validation failed for archived log
archive log filename =/ oracle / flash_recovery_area / CWT / archivelog / 2010 _10_30 / o1_mf_1_225_6dp9y4cw_ . arc recid = 48 stamp = 733723210
validation failed for archived log
archive log filename =/ oracle / flash_recovery_area / CWT / archivelog / 2010 _10_30 / o1_mf_1_226_6dr7h5m3_ . arc recid = 49 stamp = 733786218
validation failed for archived log
archive log filename =/ oracle / flash_recovery_area / CWT / archivelog / 2010 _10_31 / o1_mf_1_227_6dt1j8r0_ . arc recid = 50 stamp = 733845648
validation failed for archived log
archive log filename =/ oracle / flash_recovery_area / CWT / archivelog / 2010 _10_31 / o1_mf_1_228_6dt5pwx4_ . arc recid = 51 stamp = 733849954
validation failed for archived log
archive log filename =/ oracle / flash_recovery_area / CWT / archivelog / 2010 _10_31 / o1_mf_1_229_6dt620kn_ . arc recid = 52 stamp = 733850309
validation failed for archived log
archive log filename =/ oracle / flash_recovery_area / CWT / archivelog / 2010 _10_31 / o1_mf_1_230_6dt6j7lq_ . arc recid = 53 stamp = 733850766
validation failed for archived log
archive log filename =/ oracle / flash_recovery_area / CWT / archivelog / 2010 _10_31 / o1_mf_1_231_6dt6t8vs_ . arc recid = 54 stamp = 733851085
validation failed for archived log
archive log filename =/ oracle / flash_recovery_area / CWT / archivelog / 2010 _10_31 / o1_mf_1_232_6dt712gb_ . arc recid = 55 stamp = 733851305
validation failed for archived log
archive log filename =/ oracle / flash_recovery_area / CWT / archivelog / 2010 _10_31 / o1_mf_1_233_6dtr0ckn_ . arc recid = 56 stamp = 733868689
validation succeeded for archived log
archive log filename =/ oracle / flash_recovery_area / CWT / archivelog / 2010 _11_01 / o1_mf_1_234_6dwwwxy5_ . arc recid = 57 stamp = 733939236
validation succeeded for archived log
archive log filename =/ oracle / flash_recovery_area / CWT / archivelog / 2010 _11_02 / o1_mf_1_235_6dy9gqol_ . arc recid = 58 stamp = 733984861
validation succeeded for archived log
archive log filename =/ oracle / flash_recovery_area / CWT / archivelog / 2010 _11_02 / o1_mf_1_236_6f06jf0f_ . arc recid = 59 stamp = 734047400
validation succeeded for archived log
archive log filename =/ oracle / flash_recovery_area / CWT / archivelog / 2010 _11_03 / o1_mf_1_237_6f25nf3l_ . arc recid = 60 stamp = 734112035
validation succeeded for archived log
archive log filename =/ oracle / flash_recovery_area / CWT / archivelog / 2010 _11_04 / o1_mf_1_238_6f3mcwff_ . arc recid = 61 stamp = 734158852
validation succeeded for archived log
archive log filename =/ oracle / flash_recovery_area / CWT / archivelog / 2010 _11_04 / o1_mf_1_239_6f45oqo3_ . arc recid = 62 stamp = 734177596
Crosschecked 21 objects
RMAN > delete expired archivelog all ;
released channel : ORA_DISK_1
allocated channel : ORA_DISK_1
channel ORA_DISK_1 : sid = 299 devtype = DISK
List of Archived Log Copies
Key Thrd Seq S Low Time Name
------- ---- ------- - --------- ----
42 1 219 X 25- OCT - 10 / oracle / flash_recovery_area / CWT / archivelog / 2010 _10_27 / o1_mf_1_219_6dh57tb4_ . arc
43 1 220 X 26- OCT - 10 / oracle / flash_recovery_area / CWT / archivelog / 2010 _10_27 / o1_mf_1_220_6dh5845p_ . arc
44 1 221 X 27- OCT - 10 / oracle / flash_recovery_area / CWT / archivelog / 2010 _10_27 / o1_mf_1_221_6djd3qy1_ . arc
45 1 222 X 27- OCT - 10 / oracle / flash_recovery_area / CWT / archivelog / 2010 _10_28 / o1_mf_1_222_6dksv1dl_ . arc
46 1 223 X 28- OCT - 10 / oracle / flash_recovery_area / CWT / archivelog / 2010 _10_29 / o1_mf_1_223_6dmc1s56_ . arc
47 1 224 X 29- OCT - 10 / oracle / flash_recovery_area / CWT / archivelog / 2010 _10_29 / o1_mf_1_224_6do4hzn8_ . arc
48 1 225 X 29- OCT - 10 / oracle / flash_recovery_area / CWT / archivelog / 2010 _10_30 / o1_mf_1_225_6dp9y4cw_ . arc
49 1 226 X 30- OCT - 10 / oracle / flash_recovery_area / CWT / archivelog / 2010 _10_30 / o1_mf_1_226_6dr7h5m3_ . arc
50 1 227 X 30- OCT - 10 / oracle / flash_recovery_area / CWT / archivelog / 2010 _10_31 / o1_mf_1_227_6dt1j8r0_ . arc
51 1 228 X 31- OCT - 10 / oracle / flash_recovery_area / CWT / archivelog / 2010 _10_31 / o1_mf_1_228_6dt5pwx4_ . arc
52 1 229 X 31- OCT - 10 / oracle / flash_recovery_area / CWT / archivelog / 2010 _10_31 / o1_mf_1_229_6dt620kn_ . arc
53 1 230 X 31- OCT - 10 / oracle / flash_recovery_area / CWT / archivelog / 2010 _10_31 / o1_mf_1_230_6dt6j7lq_ . arc
54 1 231 X 31- OCT - 10 / oracle / flash_recovery_area / CWT / archivelog / 2010 _10_31 / o1_mf_1_231_6dt6t8vs_ . arc
55 1 232 X 31- OCT - 10 / oracle / flash_recovery_area / CWT / archivelog / 2010 _10_31 / o1_mf_1_232_6dt712gb_ . arc
56 1 233 X 31- OCT - 10 / oracle / flash_recovery_area / CWT / archivelog / 2010 _10_31 / o1_mf_1_233_6dtr0ckn_ . arc
Do you really want to delete the above objects ( enter YES or NO )? yes
deleted archive log
archive log filename =/ oracle / flash_recovery_area / CWT / archivelog / 2010 _10_27 / o1_mf_1_219_6dh57tb4_ . arc recid = 42 stamp = 733489027
deleted archive log
archive log filename =/ oracle / flash_recovery_area / CWT / archivelog / 2010 _10_27 / o1_mf_1_220_6dh5845p_ . arc recid = 43 stamp = 733489036
deleted archive log
archive log filename =/ oracle / flash_recovery_area / CWT / archivelog / 2010 _10_27 / o1_mf_1_221_6djd3qy1_ . arc recid = 44 stamp = 733528856
deleted archive log
archive log filename =/ oracle / flash_recovery_area / CWT / archivelog / 2010 _10_28 / o1_mf_1_222_6dksv1dl_ . arc recid = 45 stamp = 733575660
deleted archive log
archive log filename =/ oracle / flash_recovery_area / CWT / archivelog / 2010 _10_29 / o1_mf_1_223_6dmc1s56_ . arc recid = 46 stamp = 733626046
deleted archive log
archive log filename =/ oracle / flash_recovery_area / CWT / archivelog / 2010 _10_29 / o1_mf_1_224_6do4hzn8_ . arc recid = 47 stamp = 733684875
deleted archive log
archive log filename =/ oracle / flash_recovery_area / CWT / archivelog / 2010 _10_30 / o1_mf_1_225_6dp9y4cw_ . arc recid = 48 stamp = 733723210
deleted archive log
archive log filename =/ oracle / flash_recovery_area / CWT / archivelog / 2010 _10_30 / o1_mf_1_226_6dr7h5m3_ . arc recid = 49 stamp = 733786218
deleted archive log
archive log filename =/ oracle / flash_recovery_area / CWT / archivelog / 2010 _10_31 / o1_mf_1_227_6dt1j8r0_ . arc recid = 50 stamp = 733845648
deleted archive log
archive log filename =/ oracle / flash_recovery_area / CWT / archivelog / 2010 _10_31 / o1_mf_1_228_6dt5pwx4_ . arc recid = 51 stamp = 733849954
deleted archive log
archive log filename =/ oracle / flash_recovery_area / CWT / archivelog / 2010 _10_31 / o1_mf_1_229_6dt620kn_ . arc recid = 52 stamp = 733850309
deleted archive log
archive log filename =/ oracle / flash_recovery_area / CWT / archivelog / 2010 _10_31 / o1_mf_1_230_6dt6j7lq_ . arc recid = 53 stamp = 733850766
deleted archive log
archive log filename =/ oracle / flash_recovery_area / CWT / archivelog / 2010 _10_31 / o1_mf_1_231_6dt6t8vs_ . arc recid = 54 stamp = 733851085
deleted archive log
archive log filename =/ oracle / flash_recovery_area / CWT / archivelog / 2010 _10_31 / o1_mf_1_232_6dt712gb_ . arc recid = 55 stamp = 733851305
deleted archive log
archive log filename =/ oracle / flash_recovery_area / CWT / archivelog / 2010 _10_31 / o1_mf_1_233_6dtr0ckn_ . arc recid = 56 stamp = 733868689
Deleted 15 EXPIRED objects
删除结束了来登录登录呢，怎么还是不行呢？
[ root @ test250 archivelog ] # du -sh *
98 M 2010 _11_01
196 M 2010 _11_02
98 M 2010 _11_03
115 M 2010 _11_04
4.0 K 2010 _11_05
4.0 K 2010 _11_06
4.0 K 2010 _11_07
215 M 2010 _11_08
[ root @ test250 archivelog ] # du -sh *
98 M 2010 _11_01
196 M 2010 _11_02
98 M 2010 _11_03
115 M 2010 _11_04
4.0 K 2010 _11_05
4.0 K 2010 _11_06
4.0 K 2010 _11_07
216 M 2010 _11_08
[ root @ test250 archivelog ] # du -sh *
^[[ A98M 2010 _11_01
196 M 2010 _11_02
98 M 2010 _11_03
115 M 2010 _11_04
4.0 K 2010 _11_05
4.0 K 2010 _11_06
4.0 K 2010 _11_07
216 M 2010 _11_08
[ root @ test250 archivelog ] # du -sh *
^[[ A
^[[ A98M 2010 _11_01
196 M 2010 _11_02
98 M 2010 _11_03
115 M 2010 _11_04
4.0 K 2010 _11_05
4.0 K 2010 _11_06
4.0 K 2010 _11_07
217 M 2010 _11_08
发现还在归档呢。。。
再看看 alert 日志，已经变成 38.97 %
[ root @ test250 ~] # tail -f /oracle/admin/cwt/bdump/alert_cwt.log
Mon Nov 8 15 : 37 : 49 2010
Archiver process freed from errors . No longer stopped
Mon Nov 8 15 : 37 : 49 2010
Thread 1 advanced to log sequence 243
Current log # 3 seq# 243 mem# 0: /oracle/oradata/cwt/redo03.log
Mon Nov 8 15 : 37 : 52 2010
db_recovery_file_dest_size of 2048 MB is 38.97 % used . This is a
user - specified limit on the amount of space that will be used by this
database for recovery - related files , and does not reflect the amount of
space available in the underlying filesystem or ASM diskgroup
再登录一次
[ oracle @ test250 bdump ] $ sqlplus XX_js / XX_js
SQL * Plus : Release 10.2.0.1.0 - Production on Mon Nov 8 15 : 40 : 11 2010
Copyright ( c ) 1982 , 2005 , Oracle . All rights reserved .
Connected to :
Oracle Database 10 g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning , OLAP and Data Mining options
SQL > quit
终于可以了。
总结 & 问题：这个问题的引起主要是因为闪回区满了，我手工删除了过期的归档日志后，并没有释放
后来使用 rman 进行删除后，过了几十秒就可以了。
flash_recovery_area 使用率多少，不应该由 rman 来控制和触发吧，理论上，我第一次手工删除后就应该可以了，但是我没有等和观察
如果有知道的，告诉我一下。谢谢。还有好像虽然空间释放了，但是还是无法登陆，好像归档结束就可以登录了。等我有时间在虚拟机上来再模拟一次。
