Oracle归档日志ORA-00257错误的分析和解决办法 作者: sysit 分类: d 发表于 2018-10-26 122人围观 ## 1. 现象 * 在一次项目中，连接scott/tiger出现如下错误 ``` SQL> conn scott/tiger ERROR: ORA-00257: archiver error. Connect internal only, until freed. Warning: You are no longer connected to ORACLE. ``` ## 2. 原因 * `archive log` 日志已满 ## 3. 排查 ### 3.1 用sys用户登录 ``` SQL> conn / as sysdba Connected. SQL> show user USER is "SYS" ``` ### 3.2 查看archive log所在的位置 ``` SQL> show parameter log_archive_dest; NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ log_archive_dest string log_archive_dest_1 string log_archive_dest_10 string ``` ### 3.3 以上values的值为空，可以用`archive log list` 检查一下归档目录和`log sequence` ``` SQL> archive log list; Database log mode Archive Mode Automatic archival Enabled Archive destination USE_DB_RECOVERY_FILE_DEST Oldest online log sequence 302 Next log sequence to archive 302 Current log sequence 304 ``` ### 3.4 检查一下flash recovery area的使用情况 ``` SQL> select * from v$flash_recovery_area_usage; FILE_TYPE PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES -------------------- ------------------ ------------------------- --------------- CONTROL FILE 0 0 0 REDO LOG 0 0 0 ARCHIVED LOG 99.08 0 103 BACKUP PIECE 0 0 0 IMAGE COPY 0 0 0 FLASHBACK LOG 0 0 0 FOREIGN ARCHIVED LOG 0 0 0 ``` * 可以看到archive log的值已经是99.08了。 ### 3.5 计算flash recovery area已经占用的空间 ``` SQL> select sum(percent_space_used)*3/100 from v$flash_recovery_area_usage; SUM(PERCENT_SPACE_USED)*3/100 ----------------------------- 2.9724 ``` ### 3.6 找到recovery目录 ``` SQL> show parameter recovery; NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_recovery_file_dest string /u01/oracle/fast_recovery_area db_recovery_file_dest_size big integer 4122M recovery_parallelism integer 0 ``` ## 4. 解决办法 ### 4.1 进入recovery目录，转移或清除对应的归档日志，删除一些不用的日期目录的文件 * 注意：必须保留最后的几个目录。 ``` [oracle@kkops archivelog]$ pwd /u01/oracle/fast_recovery_area/ORCL/archivelog ``` ### 4.2 进入rman，用rman维护控制文件，否则空间显示仍然不释放 ``` [oracle@kkops ~]$ rman target / Recovery Manager: Release 11.2.0.3.0 - Production on Mon Feb 2 18:10:33 2015 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. connected to target database: ORCL (DBID=199126637) RMAN> ``` ### 4.3 检查一些无用的`archivelog` ``` RMAN> crosscheck archivelog all; ``` ### 4.4 删除过期的归档 ``` RMAN> delete expired archivelog all; ``` ### 4.5 确认使用率正常 ``` SQL> select * from v$flash_recovery_area_usage; FILE_TYPE PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES -------------------- ------------------ ------------------------- --------------- CONTROL FILE 0 0 0 REDO LOG 0 0 0 ARCHIVED LOG 5.98 0 6 BACKUP PIECE 0 0 0 IMAGE COPY 0 0 0 FLASHBACK LOG 0 0 0 FOREIGN ARCHIVED LOG 0 0 0 ``` ## 5. 扩展：指定`archivelog`文件的位置或修改大小 ``` SQL> alter system set log_archive_dest='/u01/oracle/oradata/usagedb/arch' scope=spfile; SQL> alter system set db_recovery_file_dest_size=3G scope=both; ``` 如果觉得我的文章对您有用，请随意赞赏。您的支持将鼓励我继续创作！ 赞赏支持
