ORA-00257
异常症状
使用PL/SQL Developer连接Oracle数据库时，出现ORA-00257错误
ORA-00257: archiver error. Connect internal only, until freed.
Error Message
ORA-00257: 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.
ORA-00257，即存储重做日志的文件已经超出目标设备限制的大小
异常确认
登录到服务器主机，操作如下：
检查启动参数
SQL> show parameter spfile;
NAME TYPE VALUE
----------- ----------- --------------------------------------------------
spfile string /db/oracle/product/10.2.0/db_1/dbs/spfiledbtest.ora
查看dest_size参数
SQL> show parameter dest_size;
NAME TYPE VALUE
-------------------------- ----------- ----------------------------
db_recovery_file_dest_size big integer 2G
查看dest路径
SQL> show parameter log_archive_dest;
NAME TYPE VALUE
-------------------------------- ----------- ------------------------------
log_archive_dest string
log_archive_dest_1 string
log_archive_dest_10 string
log_archive_dest_2 string
log_archive_dest_3 string
log_archive_dest_4 string
log_archive_dest_5 string
log_archive_dest_6 string
log_archive_dest_7 string
log_archive_dest_8 string
log_archive_dest_9 string
NAME TYPE VALUE
------------------------------- ---------- ------------------------------
log_archive_dest_state_1 string enable
log_archive_dest_state_10 string enable
log_archive_dest_state_2 string enable
log_archive_dest_state_3 string enable
log_archive_dest_state_4 string enable
log_archive_dest_state_5 string enable
log_archive_dest_state_6 string enable
log_archive_dest_state_7 string enable
log_archive_dest_state_8 string enable
log_archive_dest_state_9 string enable
若dest为空，表明log_archive使用的是默认值，此时则可以使用archive log list查看归 档路径和序列，如下
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 88
Next log sequence to archive 88
Current log sequence 90
4）查询恢复目录参数
SQL> show parameter recover;
NAME TYPE VALUE
--------------------------------- ----------- ------------------------------
db_recovery_file_dest string /db/oracle/flash_recovery_area
db_recovery_file_dest_size big integer 2G
recovery_parallelism integer 0
5）查看恢复目录文件大小
SQL> host du -h --max-depth=1 /db/oracle/flash_recovery_area/DBTEST;
4.0K /db/oracle/flash_recovery_area/DBTEST/onlinelog
2.0G /db/oracle/flash_recovery_area/DBTEST/archivelog
2.0G /db/oracle/flash_recovery_area/DBTEST
6）查看恢复目录使用率
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 97.79 0 44
BACKUPPIECE 0 0 0
IMAGECOPY 0 0 0
FLASHBACKLOG 0 0 0
6 rows selected.
flash_recovery的使用率达到97.79%，再有新的事务时，很可能会超出2G大小，故Oracle会 提示ORA-00257的错误。
解决方案
出现ORA-00257错误，解决办法有两个，一是调整db_recovery_file_dest_size的大小；二 是移除或者转移一些归档日志，释放空间。
解决方案1
调整db_recovery_file_dest_size的大小
SQL> alter system set db_recovery_file_dest_size=3G scope=both;
System altered.
此时再尝试则可以正常连接；
解决方案2
移除或者转移归档日志，释放空间
删除过期的归档日志：
find /db/oracle/flash_recovery_area/DBTEST/archivelog -name "2012-*" -mtime +14 -exec rm {} \;
使用rman同步控制文件，释放空间
检查一些无用的archivelog
RMAN> crosscheck archivelog all;
删除过期的归档
RMAN> delete expired archivelog all;
注：在OS级别删除归档日志后，还需要使用rman检查、删除，否则即使删除了归档日志，但 Oracle数据库记录的使用空间依然没有释放。
另外，也可以直接使用rman删除归档日志
RMAN> delete noprompt archivelog all completed before 'sysdate - 14';
参数设置
Oracle数据库中归档日志对于数据库的恢复十分重要，以下仅列出跟此错误相关的几个参数， 其中LOG_ARCHIVE_DEST_N/LOG_ARCHIVE_DEST_STATE_N多涉及到重做日志的多路复用、 Data Guard等，这里暂不列出。
1.LOG_ARCHIVE_DEST
Parameter type: String
Syntax: LOG_ARCHIVE_DEST = filespec
Default value: Null
Modifiable: ALTER SYSTEM
Range of values: Any valid path or device name, except raw partitions
Basic: No
Real Application Cluster: Multiple instances can have different values.
LOG_ARCHIVE_DEST is applicable only if you are running the database in ARCHIVELOG mode or are recovering a database from archived redo logs. LOG_ARCHIVE_DEST is incompatible with the LOG_ARCHIVE_DEST_n parameters, a nd must be defined as the null string (“”) or (‘ ‘) when any LOG_ARCHIVE_DEST_n parameter has a value other than a null string. Use a text string to specify the default location and root of the disk file or tape device when archiving redo log files. (Archiving to tape is not supported on all operating systems.) The value cannot be a raw partition.
If LOG_ARCHIVE_DEST is not explicitly defined and all the LOG_ARCHIVE_DEST_n parameters have null string values, LOG_ARCHIVE_DEST is set to an operating system-specific default value on instance startup.
To override the destination that this parameter specifies, either specify a different destination for manual archiving or use the SQL*Plus statement ARCHIVE LOG START filespec for automatic archiving, where filespec is the new archive destination. To permanently change the destination, use the statement ALTER SYSTEM SET LOG_ARCHIVE_DEST = filespec, where filespec is the new archive destination.
Neither LOG_ARCHIVE_DEST nor LOG_ARCHIVE_FORMAT have to be complete file or directory specifiers themselves; they only need to form a valid file path after the variables are substituted into LOG_ARCHIVE_FORMAT and the two parameters are concatenated together.
2.DB_RECOVERY_FILE_DEST_SIZE
Parameter type: Big integer
Syntax: DB_RECOVERY_FILE_DEST_SIZE = integer [K | M | G]
Default value: There is no default value.
Modifiable: ALTER SYSTEM ... SID='*'
Basic: Yes
Real Application Clusters: You must set this parameter for every instance, and multiple instances must have the same value
DB_RECOVERY_FILE_DEST_SIZE specifies (in bytes) the hard limit on the total space to be used by target database recovery files created in the flash recovery area.
3.DB_RECOVERY_FILE_DEST
Parameter type: String
Syntax: DB_RECOVERY_FILE_DEST = directory | disk group
Default value: There is no default value.
Modifiable: ALTER SYSTEM ... SID='*'
Basic: Yes
Real Application Clusters: You must set this parameter for every instance, and multiple instances must have the same value.
DB_RECOVERY_FILE_DEST specifies the default location for the flash recovery
area.
The flash recovery area contains multiplexed copies of current control files and online redo logs, as well as archived redo logs, flashback logs, and RMAN backups.
Specifying this parameter without also specifying the DB_RECOVERY_FILE_DEST_SIZE parameter is not allowed.
参考
- Oracle Database Reference
- Oracle Database Error Messages
blog comments powered by Disqus