ORA-00257 archive log 故障解决过程

提示归档错误，通过查找ORACLE错误代码，解释为硬盘空间不足，需要删除归档日志增加空间，但是磁盘明显还有3G的空闲空间。

第一次的处理是这样的：

startup mount
recover database until change 2025611
alter database open resetlogs

--后来才知道这个是非常愚蠢的方法，虽然可以是数据库正常启动，并运行。但这根本就是牛头不对马嘴。运行不久又产生了相同的问题。

后来发现oracle10g有FLASH_RECOVERY_AREA的参数，当前值为2G，也就是说只能产生2G的archive log文件，通过

SQL> alter system set DB_RECOVERY_FILE_DEST_SIZE=20g;
系统已更改。
SQL> select * from v$recovery_file_dest;

查询使用recovery_file

这样就可以解决问题。

网上看来提到有3种解决方法：

方法一：增大闪回恢复区

SQL> show parameter db_recovery_file_dest_size

NAME                                  TYPE         VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest_size            big integer 2G

SQL> alter SYSTEM SET db_recovery_file_dest_size=5g；

System altered.

方法二：删除闪回恢复区里的归档日志

SQL> show parameter db_recovery_file_dest_size;

NAME                                  TYPE         VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest_size            big integer 10G
SQL> select * from v$flash_recovery_area_usage;

FILE_TYPE     PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES
------------ ------------------ ------------------------- ---------------
CONTROLFILE                  .07                          0                1
ONLINELOG                   1.46                          0                3
ARCHIVELOG                 49.68                          0              126
BACKUPPIECE                    0                          0                0
IMAGECOPY                      0                          0                0
FLASHBACKLOG                   0                          0                0

6 rows selected.

登陆rman:
[oracle@localhost LMAIL]$ rman target / nocatalog
RMAN>list archivelog all;
RMAN>crosscheck archivelog all;
RMAN>change archivelog until logseq=130 delete;

--这个方法值得商榷，因为它把archive log file 下的文件都删除而来。

use crosscheck to sync, when it finished, the status of those files would be "EXPIRED", then you can use the following commands to clean it.
"DELETE NOPROMPT EXPIRED BACKUP;
DELETE NOPROMPT EXPIRED COPY;"

应该是值得推荐的，它把不存在的文件从统计信息里删除了。详细见下篇的例子

SQL> select * from v$flash_recovery_area_usage;

FILE_TYPE     PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES
------------ ------------------ ------------------------- ---------------
CONTROLFILE                  .07                          0                1
ONLINELOG                   1.46                          0                3
ARCHIVELOG                  1.26                          0                5
BACKUPPIECE                    0                          0                0
IMAGECOPY                      0                          0                0
FLASHBACKLOG                   0                          0                0

6 rows selected.

其实解决方法有3种：
1.将归档设置到其他目录，修改alter system set log_archive_dest = 其他路径
2.转移或者删除闪回恢复区里的归档日志。
3.增大闪回恢复区。ALTER SYSTEM SET db_recovery_file_dest_size=3g