ORA-00257故障处理
[oracle@test250~]$sqlplusXX_js/XX_js
SQL*Plus:Release10.2.0.1.0-ProductiononMonNov815:25:402010
Copyright(c)1982,2005,Oracle.Allrightsreserved.
ERROR:
ORA-00257:archivererror.Connectinternalonly,untilfreed.
马上来查一下错误描述。
SQL>!oerrora257
00257,00000,"archiver error. Connect internal only, until freed."
//*Cause:Thearchiverprocessreceivedanerrorwhiletryingtoarchive
//aredolog.Iftheproblemisnotresolvedsoon,thedatabase
//willstopexecutingtransactions.Themostlikelycauseofthis
//messageisthedestinationdeviceisoutofspacetostorethe
//redologfile.
//*Action:Checkarchivertracefileforadetaileddescription
//oftheproblem.Alsoverifythatthe
//devicespecifiedintheinitializationparameter
//ARCHIVE_LOG_DESTissetupproperlyforarchiving.
看来是archivelog的事情啊,看看ARCHIVE_LOG_DEST的设置。
SQL>archiveloglist
DatabaselogmodeArchiveMode
AutomaticarchivalEnabled
ArchivedestinationUSE_DB_RECOVERY_FILE_DEST
Oldestonlinelogsequence240
Nextlogsequencetoarchive240
Currentlogsequence242
想想是不是闪回区满了啊?
[oracle@test250archivelog]$du-sh*
4.0K2010_10_26
294M2010_10_27
98M2010_10_28
196M2010_10_29
196M2010_10_30
685M2010_10_31
98M2010_11_01
196M2010_11_02
98M2010_11_03
115M2010_11_04
4.0K2010_11_05
4.0K2010_11_06
4.0K2010_11_07
4.0K2010_11_08
[oracle@test250archivelog]$cd..
[oracle@test250CWT]$du-sh*
2.0Garchivelog
好几天没日志了,2G了,果然满了。
[oracle@test250archivelog]$rm-rf2010_10_*
先来删除一下10月的日志
[root@test250CWT]# du -sh *
702Marchivelog
再来登录一下
[oracle@test250archivelog]$sqlplusXX_js/XX_js
SQL*Plus:Release10.2.0.1.0-ProductiononMonNov815:30:532010
Copyright(c)1982,2005,Oracle.Allrightsreserved.
ERROR:
ORA-00257:archivererror.Connectinternalonly,untilfreed.
还是不行啊。。
再来看看alert日志
[root@test250~]# tail -f /oracle/admin/cwt/bdump/alert_cwt.log
MonNov815:37:072010
Errorsinfile/oracle/admin/cwt/bdump/cwt_arc1_2861.trc:
ORA-19815:WARNING:db_recovery_file_dest_sizeof2147483648bytesis100.00%used,andhas0remainingbytesavailable.
MonNov815:37:072010
************************************************************************
Youhavefollowingchoicestofreeupspacefromflashrecoveryarea:
1.ConsiderchangingRMANRETENTIONPOLICY.IfyouareusingDataGuard,
thenconsiderchangingRMANARCHIVELOGDELETIONPOLICY.
2.BackupfilestotertiarydevicesuchastapeusingRMAN
BACKUPRECOVERYAREAcommand.
3.Adddiskspaceandincreasedb_recovery_file_dest_sizeparameterto
reflectthenewspace.
4.DeleteunnecessaryfilesusingRMANDELETEcommand.Ifanoperating
systemcommandwasusedtodeletefiles,thenuseRMANCROSSCHECKand
DELETEEXPIREDcommands.
************************************************************************
主要的意思是让我们用rman清理archivelog。
那我们就来删除一下吧
[oracle@test250bdump]$rmantarget/
RecoveryManager:Release10.2.0.1.0-ProductiononMonNov815:35:302010
Copyright(c)1982,2005,Oracle.Allrightsreserved.
connectedtotargetdatabase:CWT(DBID=2677153359)
RMAN>crosscheckarchivelogall;
releasedchannel:ORA_DISK_1
allocatedchannel:ORA_DISK_1
channelORA_DISK_1:sid=299devtype=DISK
validationfailedforarchivedlog
archivelogfilename=/oracle/flash_recovery_area/CWT/archivelog/2010_10_27/o1_mf_1_219_6dh57tb4_.arcrecid=42stamp=733489027
validationfailedforarchivedlog
archivelogfilename=/oracle/flash_recovery_area/CWT/archivelog/2010_10_27/o1_mf_1_220_6dh5845p_.arcrecid=43stamp=733489036
validationfailedforarchivedlog
archivelogfilename=/oracle/flash_recovery_area/CWT/archivelog/2010_10_27/o1_mf_1_221_6djd3qy1_.arcrecid=44stamp=733528856
validationfailedforarchivedlog
archivelogfilename=/oracle/flash_recovery_area/CWT/archivelog/2010_10_28/o1_mf_1_222_6dksv1dl_.arcrecid=45stamp=733575660
validationfailedforarchivedlog
archivelogfilename=/oracle/flash_recovery_area/CWT/archivelog/2010_10_29/o1_mf_1_223_6dmc1s56_.arcrecid=46stamp=733626046
validationfailedforarchivedlog
archivelogfilename=/oracle/flash_recovery_area/CWT/archivelog/2010_10_29/o1_mf_1_224_6do4hzn8_.arcrecid=47stamp=733684875
validationfailedforarchivedlog
archivelogfilename=/oracle/flash_recovery_area/CWT/archivelog/2010_10_30/o1_mf_1_225_6dp9y4cw_.arcrecid=48stamp=733723210
validationfailedforarchivedlog
archivelogfilename=/oracle/flash_recovery_area/CWT/archivelog/2010_10_30/o1_mf_1_226_6dr7h5m3_.arcrecid=49stamp=733786218
validationfailedforarchivedlog
archivelogfilename=/oracle/flash_recovery_area/CWT/archivelog/2010_10_31/o1_mf_1_227_6dt1j8r0_.arcrecid=50stamp=733845648
validationfailedforarchivedlog
archivelogfilename=/oracle/flash_recovery_area/CWT/archivelog/2010_10_31/o1_mf_1_228_6dt5pwx4_.arcrecid=51stamp=733849954
validationfailedforarchivedlog
archivelogfilename=/oracle/flash_recovery_area/CWT/archivelog/2010_10_31/o1_mf_1_229_6dt620kn_.arcrecid=52stamp=733850309
validationfailedforarchivedlog
archivelogfilename=/oracle/flash_recovery_area/CWT/archivelog/2010_10_31/o1_mf_1_230_6dt6j7lq_.arcrecid=53stamp=733850766
validationfailedforarchivedlog
archivelogfilename=/oracle/flash_recovery_area/CWT/archivelog/2010_10_31/o1_mf_1_231_6dt6t8vs_.arcrecid=54stamp=733851085
validationfailedforarchivedlog
archivelogfilename=/oracle/flash_recovery_area/CWT/archivelog/2010_10_31/o1_mf_1_232_6dt712gb_.arcrecid=55stamp=733851305
validationfailedforarchivedlog
archivelogfilename=/oracle/flash_recovery_area/CWT/archivelog/2010_10_31/o1_mf_1_233_6dtr0ckn_.arcrecid=56stamp=733868689
validationsucceededforarchivedlog
archivelogfilename=/oracle/flash_recovery_area/CWT/archivelog/2010_11_01/o1_mf_1_234_6dwwwxy5_.arcrecid=57stamp=733939236
validationsucceededforarchivedlog
archivelogfilename=/oracle/flash_recovery_area/CWT/archivelog/2010_11_02/o1_mf_1_235_6dy9gqol_.arcrecid=58stamp=733984861
validationsucceededforarchivedlog
archivelogfilename=/oracle/flash_recovery_area/CWT/archivelog/2010_11_02/o1_mf_1_236_6f06jf0f_.arcrecid=59stamp=734047400
validationsucceededforarchivedlog
archivelogfilename=/oracle/flash_recovery_area/CWT/archivelog/2010_11_03/o1_mf_1_237_6f25nf3l_.arcrecid=60stamp=734112035
validationsucceededforarchivedlog
archivelogfilename=/oracle/flash_recovery_area/CWT/archivelog/2010_11_04/o1_mf_1_238_6f3mcwff_.arcrecid=61stamp=734158852
validationsucceededforarchivedlog
archivelogfilename=/oracle/flash_recovery_area/CWT/archivelog/2010_11_04/o1_mf_1_239_6f45oqo3_.arcrecid=62stamp=734177596
Crosschecked21objects
RMAN>deleteexpiredarchivelogall;
releasedchannel:ORA_DISK_1
allocatedchannel:ORA_DISK_1
channelORA_DISK_1:sid=299devtype=DISK
ListofArchivedLogCopies
KeyThrdSeqSLowTimeName
------- ---- ------- - --------- ----
421219X25-OCT-10/oracle/flash_recovery_area/CWT/archivelog/2010_10_27/o1_mf_1_219_6dh57tb4_.arc
431220X26-OCT-10/oracle/flash_recovery_area/CWT/archivelog/2010_10_27/o1_mf_1_220_6dh5845p_.arc
441221X27-OCT-10/oracle/flash_recovery_area/CWT/archivelog/2010_10_27/o1_mf_1_221_6djd3qy1_.arc
451222X27-OCT-10/oracle/flash_recovery_area/CWT/archivelog/2010_10_28/o1_mf_1_222_6dksv1dl_.arc
461223X28-OCT-10/oracle/flash_recovery_area/CWT/archivelog/2010_10_29/o1_mf_1_223_6dmc1s56_.arc
471224X29-OCT-10/oracle/flash_recovery_area/CWT/archivelog/2010_10_29/o1_mf_1_224_6do4hzn8_.arc
481225X29-OCT-10/oracle/flash_recovery_area/CWT/archivelog/2010_10_30/o1_mf_1_225_6dp9y4cw_.arc
491226X30-OCT-10/oracle/flash_recovery_area/CWT/archivelog/2010_10_30/o1_mf_1_226_6dr7h5m3_.arc
501227X30-OCT-10/oracle/flash_recovery_area/CWT/archivelog/2010_10_31/o1_mf_1_227_6dt1j8r0_.arc
511228X31-OCT-10/oracle/flash_recovery_area/CWT/archivelog/2010_10_31/o1_mf_1_228_6dt5pwx4_.arc
521229X31-OCT-10/oracle/flash_recovery_area/CWT/archivelog/2010_10_31/o1_mf_1_229_6dt620kn_.arc
531230X31-OCT-10/oracle/flash_recovery_area/CWT/archivelog/2010_10_31/o1_mf_1_230_6dt6j7lq_.arc
541231X31-OCT-10/oracle/flash_recovery_area/CWT/archivelog/2010_10_31/o1_mf_1_231_6dt6t8vs_.arc
551232X31-OCT-10/oracle/flash_recovery_area/CWT/archivelog/2010_10_31/o1_mf_1_232_6dt712gb_.arc
561233X31-OCT-10/oracle/flash_recovery_area/CWT/archivelog/2010_10_31/o1_mf_1_233_6dtr0ckn_.arc
Doyoureallywanttodeletetheaboveobjects(enterYESorNO)?yes
deletedarchivelog
archivelogfilename=/oracle/flash_recovery_area/CWT/archivelog/2010_10_27/o1_mf_1_219_6dh57tb4_.arcrecid=42stamp=733489027
deletedarchivelog
archivelogfilename=/oracle/flash_recovery_area/CWT/archivelog/2010_10_27/o1_mf_1_220_6dh5845p_.arcrecid=43stamp=733489036
deletedarchivelog
archivelogfilename=/oracle/flash_recovery_area/CWT/archivelog/2010_10_27/o1_mf_1_221_6djd3qy1_.arcrecid=44stamp=733528856
deletedarchivelog
archivelogfilename=/oracle/flash_recovery_area/CWT/archivelog/2010_10_28/o1_mf_1_222_6dksv1dl_.arcrecid=45stamp=733575660
deletedarchivelog
archivelogfilename=/oracle/flash_recovery_area/CWT/archivelog/2010_10_29/o1_mf_1_223_6dmc1s56_.arcrecid=46stamp=733626046
deletedarchivelog
archivelogfilename=/oracle/flash_recovery_area/CWT/archivelog/2010_10_29/o1_mf_1_224_6do4hzn8_.arcrecid=47stamp=733684875
deletedarchivelog
archivelogfilename=/oracle/flash_recovery_area/CWT/archivelog/2010_10_30/o1_mf_1_225_6dp9y4cw_.arcrecid=48stamp=733723210
deletedarchivelog
archivelogfilename=/oracle/flash_recovery_area/CWT/archivelog/2010_10_30/o1_mf_1_226_6dr7h5m3_.arcrecid=49stamp=733786218
deletedarchivelog
archivelogfilename=/oracle/flash_recovery_area/CWT/archivelog/2010_10_31/o1_mf_1_227_6dt1j8r0_.arcrecid=50stamp=733845648
deletedarchivelog
archivelogfilename=/oracle/flash_recovery_area/CWT/archivelog/2010_10_31/o1_mf_1_228_6dt5pwx4_.arcrecid=51stamp=733849954
deletedarchivelog
archivelogfilename=/oracle/flash_recovery_area/CWT/archivelog/2010_10_31/o1_mf_1_229_6dt620kn_.arcrecid=52stamp=733850309
deletedarchivelog
archivelogfilename=/oracle/flash_recovery_area/CWT/archivelog/2010_10_31/o1_mf_1_230_6dt6j7lq_.arcrecid=53stamp=733850766
deletedarchivelog
archivelogfilename=/oracle/flash_recovery_area/CWT/archivelog/2010_10_31/o1_mf_1_231_6dt6t8vs_.arcrecid=54stamp=733851085
deletedarchivelog
archivelogfilename=/oracle/flash_recovery_area/CWT/archivelog/2010_10_31/o1_mf_1_232_6dt712gb_.arcrecid=55stamp=733851305
deletedarchivelog
archivelogfilename=/oracle/flash_recovery_area/CWT/archivelog/2010_10_31/o1_mf_1_233_6dtr0ckn_.arcrecid=56stamp=733868689
Deleted15EXPIREDobjects
删除结束了来登录登录呢,怎么还是不行呢?
[root@test250archivelog]# du -sh *
98M2010_11_01
196M2010_11_02
98M2010_11_03
115M2010_11_04
4.0K2010_11_05
4.0K2010_11_06
4.0K2010_11_07
215M2010_11_08
[root@test250archivelog]# du -sh *
98M2010_11_01
196M2010_11_02
98M2010_11_03
115M2010_11_04
4.0K2010_11_05
4.0K2010_11_06
4.0K2010_11_07
216M2010_11_08
[root@test250archivelog]# du -sh *
^[[A98M2010_11_01
196M2010_11_02
98M2010_11_03
115M2010_11_04
4.0K2010_11_05
4.0K2010_11_06
4.0K2010_11_07
216M2010_11_08
[root@test250archivelog]# du -sh *
^[[A
^[[A98M2010_11_01
196M2010_11_02
98M2010_11_03
115M2010_11_04
4.0K2010_11_05
4.0K2010_11_06
4.0K2010_11_07
217M2010_11_08
发现还在归档呢。。。
再看看alert日志,已经变成38.97%
[root@test250~]# tail -f /oracle/admin/cwt/bdump/alert_cwt.log
MonNov815:37:492010
Archiverprocessfreedfromerrors.Nolongerstopped
MonNov815:37:492010
Thread1advancedtologsequence243
Currentlog# 3 seq# 243 mem# 0: /oracle/oradata/cwt/redo03.log
MonNov815:37:522010
db_recovery_file_dest_sizeof2048MBis38.97%used.Thisisa
user-specifiedlimitontheamountofspacethatwillbeusedbythis
databaseforrecovery-relatedfiles,anddoesnotreflecttheamountof
spaceavailableintheunderlyingfilesystemorASMdiskgroup
再登录一次
[oracle@test250bdump]$sqlplusXX_js/XX_js
SQL*Plus:Release10.2.0.1.0-ProductiononMonNov815:40:112010
Copyright(c)1982,2005,Oracle.Allrightsreserved.
Connectedto:
OracleDatabase10gEnterpriseEditionRelease10.2.0.1.0-Production
WiththePartitioning,OLAPandDataMiningoptions
SQL>quit
终于可以了。
总结&问题:这个问题的引起主要是因为闪回区满了,我手工删除了过期的归档日志后,并没有释放
后来使用rman进行删除后,过了几十秒就可以了。
flash_recovery_area使用率多少,不应该由rman来控制和触发吧,理论上,我第一次手工删除后就应该可以了,但是我没有等和观察
如果有知道的,告诉我一下。谢谢。还有好像虽然空间释放了,但是还是无法登陆,好像归档结束就可以登录了。等我有时间在虚拟机上来再模拟一次。