ora-257 using flashback recovery with asm
database June 14th, 2009刚处理完备库的bug,客户申告主库用不了了。登陆不了,幸好是周末,且客户所在行业关系,呵呵,不然这压力可更大一些哦。
看看是啥问题呢:
SQL> conn mesusr/hearsonic
ERROR:
ORA-00257: archiver error. Connect internal only, until freed.
SQL> conn /as sysdba
Connected.
普通用户无法登陆,但是sysdba不影响。从错误信息看,应该是归档空间满或者是权限问题。归档路径选择的是use_db_recovery_file_dest,而这个空间存放在ASM中。我们先来看看切换归档可否:
SQL> alter system switch logfile;
alter system switch logfile
*
ERROR at line 1:
接下来看看是不是都在等待归档:
SQL> set line 150
SQL> select * from v$Log;GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME
———- ———- ———- ———- ———- — —————- ————- ————
3 1 4203 536870912 2 NO INACTIVE 5361530833 13-JUN-09
2 1 4202 536870912 2 NO INACTIVE 5361343485 13-JUN-09
1 1 4201 536870912 2 NO INACTIVE 5361101610 13-JUN-09
4 1 4204 536870912 2 NO CURRENT 5361566732 13-JUN-09
7 2 1906 536870912 2 NO INACTIVE 5361058403 13-JUN-09
6 2 1909 536870912 2 NO CURRENT 5361559825 13-JUN-09
5 2 1908 536870912 2 NO INACTIVE 5361530696 13-JUN-09
8 2 1907 536870912 2 NO INACTIVE 5361343015 13-JUN-09
9 3 1812 536870912 2 NO CURRENT 5361530836 13-JUN-09
10 3 1809 536870912 2 NO INACTIVE 5361057981 13-JUN-09
11 3 1810 536870912 2 NO INACTIVE 5361101473 13-JUN-09GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME
———- ———- ———- ———- ———- — —————- ————- ————
12 3 1811 536870912 2 NO INACTIVE 5361343567 13-JUN-09
13 4 1835 536870912 2 YES INACTIVE 5361633655 13-JUN-09
14 4 1836 536870912 2 YES ACTIVE 5361635245 13-JUN-09
15 4 1837 536870912 2 NO CURRENT 5361636287 13-JUN-09
16 4 1834 536870912 2 YES INACTIVE 5361630420 13-JUN-0916 rows selected.
接下来看看归档目的的的情况:
SQL> show parameter reco
NAME TYPE VALUE
———————————— ———– ——————————
control_file_record_keep_time integer 7
db_recovery_file_dest string +DG1
db_recovery_file_dest_size big integer 120000M
recovery_parallelism integer 0
SQL> col name for a20
SQL> select * from v$recovery_file_dest;NAME SPACE_LIMIT SPACE_USED SPACE_RECLAIMABLE NUMBER_OF_FILES
——————– ———– ———- —————– —————
+DG1 1.2583E+11 1.2582E+11 0 3389
看起来似乎满了,那我们进去看看:
[hndb1:oracle:/home/oracle] export ORACLE_SID=+ASM1
[hndb1:oracle:/home/oracle] asmcmd
ASMCMD> cd dg1
ASMCMD> cd hnwx
ASMCMD> ls
ARCHIVELOG/
CONTROLFILE/
DATAFILE/
ONLINELOG/
PARAMETERFILE/
TEMPFILE/
spfilehnwx.ora
ASMCMD> du -H archivelog
1 1
ASMCMD>
ASMCMD>
目录里已经被清空了。我们再来看看利用率:
SQL> select * from V_$FLASH_RECOVERY_AREA_USAGE;
FILE_TYPE PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES
———— —————— ————————- —————
CONTROLFILE .02 0 1
ONLINELOG 6.83 0 16
ARCHIVELOG 93.15 0 3399
BACKUPPIECE 0 0 0
IMAGECOPY 0 0 0
FLASHBACKLOG 0 0 0
事实上,已经没那么多归档文件在里面了。这个数据从v$archived_log读取:
SQL>select count(1) from v$archived_log;
COUNT(1)
————
3399
接下来,我们将归档目的的改到本地:
SQL> alter system set log_archive_dest_1=’location=/archive’;
System altered.
SQL> alter system switch logfile;
System altered.
SQL> conn hs_his/handsome
Connected.
SQL> alter system set log_archive_dest_2=’location=+DG1′;System altered.
SQL> alter system switch logfile;
System altered.
SQL>/
System altered
改完归档目的的后,归档成功,普通用户登陆正常。
再将归档改回原处(未对原归档地做任何更改),普通用户登陆正常。
但数小时后,系统再报ORA-257错误。
那问题在哪里呢?
这应该是oracle的一个bug了。
处理办法(办法是nico想到的):
- crosscheck archivelog
- delete expired
–EOF—

June 17th, 2009 at 19:15
这个问题遇到过,所以说我们不应该直接使用asmcmd rm archivelog 文件,这和使用文件系统删除是一样的,rman并不知道,controlfile里也不会记录。
June 18th, 2009 at 06:29
是的,其实这应该作为数据库备份的维护策略来实施,任何小的细节都是造成”系统高可用”的隐患
July 23rd, 2009 at 08:35
一看就是我们的应用系统。我们用ASM不多,应该是湘财的。呵呵
April 26th, 2011 at 14:00
I’ve gone ahead and bookmarked http://www.zhihong.org/ora_257_using_flashback_with_asm.html at Digg.com so my friends can see it too. I simply used æ•°æ®å·¥äºº » Blog Archive » ora-257 using flashback recovery with asm as the entry title in my Digg.com bookmark, as I figured if it is good enough for you to title your blog post that, then you probably would like to see it bookmarked the same way.
[WORDPRESS HASHCASH] The poster sent us ’393641327 which is not a hashcash value.
June 18th, 2011 at 01:21
Since I thought this was so good, I went ahead to bookmark http://www.zhihong.org/ora_257_using_flashback_with_asm.html on my Facebook page. I figured the title – æ•°æ®å·¥äºº » Blog Archive » ora-257 using flashback recovery with asm – was best for people to locate this info, so that is what I used.
[WORDPRESS HASHCASH] The poster sent us ’228116864 which is not a hashcash value.