Chinaunix首页 | 论坛 | 博客
  • 博客访问: 792224
  • 博文数量: 180
  • 博客积分: 4447
  • 博客等级: 上校
  • 技术积分: 1582
  • 用 户 组: 普通用户
  • 注册时间: 2006-04-03 14:51
文章分类

全部博文(180)

文章存档

2014年(6)

2013年(8)

2011年(125)

2009年(35)

2008年(1)

2007年(5)

分类: 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 来控制和触发吧,理论上,我第一次手工删除后就应该可以了,但是我没有等和观察

 

如果有知道的,告诉我一下。谢谢。还有好像虽然空间释放了,但是还是无法登陆,好像归档结束就可以登录了。等我有时间在虚拟机上来再模拟一次。
阅读(1330) | 评论(0) | 转发(0) |
给主人留下些什么吧!~~