oracle 常见问题

• ora-00257 archiver error. connect as sysdba only until resolved

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 that the destination device is out of space to store the redo log file. Another possible cause is that a destination marked as MANDATORY has failed.

resolve:

该错误是由于归档日志满了，造成的。
查看了下V$FLASH_RECOVERY_AREA_USAGE，看看归档目录使用的情况。果然是归档满了。

SQL> SELECT * FROM  V$FLASH_RECOVERY_AREA_USAGE;
FILE_TYPE            PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES
———— —————— ————————- —————
CONTROLFILE                   0                         0               0
ONLINELOG                     0                         0               0
ARCHIVELOG                  99.9                        0               255
BACKUPPIECE                   0                         0               0
IMAGECOPY                     0                         0               0
FLASHBACKLOG                  0                         0               0

注：可以看出，ARCHIVELOG日志已经达到99.9%了。造成归档满的原因是因为有一个用户在做大量更新操作，由于更新操作产生大量重做日志，归档日志切换频繁。解决方法是要把大量归档日志清除掉!有两种方式可以解决该问题。一使用RMAN清除归档日志。二修改闪回恢复区的大小DB_RECOVERY_FILE_DEST_SIZE。
第一种使用RMAN清除归档日志:

$rman target /
RMAN> crosscheck archivelog all;
RMAN> delete noprompt expired archivelog all;

第二种方法就是增大闪回恢复区的大小：

SQL>alter system set DB_RECOVERY_FILE_DEST_SIZE=8G;

在归档数据中要制定备份策略。以免造成该问题。

configure retention policy to recovery window of 15 days;

或者在备份时删除日志信息。

DELETE ARCHIVELOG ALL COMPLETED BEFORE 'SYSDATE-7'; //删除七天前的归档   
DELETE ARCHIVELOG FROM TIME 'SYSDATE-7'; //删除七天到现在的归档