处理：“ORA-00257: archiver error. Connect internal only, until freed”的错误问题
注：本文参考了《 ORA-00257: archiver error. Connect internal only, until freed 错误的处理方法 》
一：问题背景：
今天在 做外部表的时候，出现了下图的问题；
二：具体操作步骤
1： 看看archiv log所在位置
[oracle@localhost ~]$ rlwrap sqlplus / as sysdba; SQL*Plus: Release 11.2.0.3.0 Production on Sat Jul 14 09:43:00 2018 Copyright (c) 1982, 2011, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SYS@orcl> 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_11 string log_archive_dest_12 string log_archive_dest_13 string log_archive_dest_14 string log_archive_dest_15 string log_archive_dest_16 string log_archive_dest_17 string log_archive_dest_18 string NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ log_archive_dest_19 string log_archive_dest_2 string log_archive_dest_20 string log_archive_dest_21 string log_archive_dest_22 string log_archive_dest_23 string log_archive_dest_24 string log_archive_dest_25 string log_archive_dest_26 string log_archive_dest_27 string log_archive_dest_28 string NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ log_archive_dest_29 string log_archive_dest_3 string log_archive_dest_30 string log_archive_dest_31 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 log_archive_dest_state_1 string enable NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ log_archive_dest_state_10 string enable log_archive_dest_state_11 string enable log_archive_dest_state_12 string enable log_archive_dest_state_13 string enable log_archive_dest_state_14 string enable log_archive_dest_state_15 string enable log_archive_dest_state_16 string enable log_archive_dest_state_17 string enable log_archive_dest_state_18 string enable log_archive_dest_state_19 string enable log_archive_dest_state_2 string ENABLE NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ log_archive_dest_state_20 string enable log_archive_dest_state_21 string enable log_archive_dest_state_22 string enable log_archive_dest_state_23 string enable log_archive_dest_state_24 string enable log_archive_dest_state_25 string enable log_archive_dest_state_26 string enable log_archive_dest_state_27 string enable log_archive_dest_state_28 string enable log_archive_dest_state_29 string enable log_archive_dest_state_3 string enable NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ log_archive_dest_state_30 string enable log_archive_dest_state_31 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
2：一般VALUE为空时，可以用archive log list;检查一下归档目录和log sequence
SYS@orcl> archive log list; Database log mode Archive Mode Automatic archival Enabled Archive destination USE_DB_RECOVERY_FILE_DEST Oldest online log sequence 357 Next log sequence to archive 357 Current log sequence 360
3： 检查flash recovery area的使用情况,可以看见archivelog已经很大了，达到99.1
SYS@orcl> 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.1 0 BACKUP PIECE 0 0 0 IMAGE COPY 0 0 0 FLASHBACK LOG 0 0 0 FOREIGN ARCHIVED LOG 0 0 0 7 rows selected.
4：计算flash recovery area已经占用的空间
SYS@orcl> select sum(percent_space_used)*3/100 from v$flash_recovery_area_usage; SUM(PERCENT_SPACE_USED)*3/100 ----------------------------- 2.973
5： 找到recovery目录, show parameter recover
SYS@orcl> show parameter recover; NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_recovery_file_dest string /u01/app/oracle/fast_recovery_area/ORCL/db_recovery_file_dest db_recovery_file_dest_size big integer 3G db_unrecoverable_scn_tracking boolean TRUE recovery_parallelism integer 0SYS@orcl> ho ls -al /u01/app/oracle/fast_recovery_area/ORCL total 20 drwxr-x--- 5 oracle oinstall 4096 May 21 00:03 . drwxr-x--- 6 oracle oinstall 4096 Jun 7 22:02 .. drwxr-x--- 19 oracle oinstall 4096 May 20 23:57 archivelog drwxr-xr-x 3 oracle oinstall 4096 May 21 13:00 db_recovery_file_dest drwxr-x--- 2 oracle oinstall 4096 Nov 30 2016 onlinelog
6：上述结果告诉我们，归档位置用的是默认值，放在flash_recovery_area下(db_recovery_file_dest目录=/u01/app/oracle/fast_recovery_area/ORCL/db_recovery_file_dest
[root@localhost ~]# su - oracle [oracle@localhost ~]$ cd /u01/app/oracle/fast_recovery_area/ORCL [oracle@localhost ORCL]$ ls archivelog db_recovery_file_dest onlinelog [oracle@localhost ORCL]$ cd db_recovery_file_dest/ [oracle@localhost db_recovery_file_dest]$ ls ORCL [oracle@localhost db_recovery_file_dest]$ cd ORCL/ [oracle@localhost ORCL]$ ls archivelog [oracle@localhost ORCL]$ cd archivelog/ [oracle@localhost archivelog]$ ls 2018_05_21 2018_05_23 2018_05_25 2018_05_27 2018_05_29 2018_05_31 2018_06_07 2018_07_14 2018_05_22 2018_05_24 2018_05_26 2018_05_28 2018_05_30 2018_06_06 2018_06_20 [oracle@localhost archivelog]$ rm 2018_05* rm: cannot remove `2018_05_21': Is a directory rm: cannot remove `2018_05_22': Is a directory rm: cannot remove `2018_05_23': Is a directory rm: cannot remove `2018_05_24': Is a directory rm: cannot remove `2018_05_25': Is a directory rm: cannot remove `2018_05_26': Is a directory rm: cannot remove `2018_05_27': Is a directory rm: cannot remove `2018_05_28': Is a directory rm: cannot remove `2018_05_29': Is a directory rm: cannot remove `2018_05_30': Is a directory rm: cannot remove `2018_05_31': Is a directory [oracle@localhost archivelog]$ rm -rf 2018_05* [oracle@localhost archivelog]$ rm -rf 2018_056 [oracle@localhost archivelog]$ rm -rf 2018_06* [oracle@localhost archivelog]$ rm -rf 2018_07_0* [oracle@localhost archivelog]$ ls 2018_07_14 [oracle@localhost archivelog]$
---------------------------------------------------------------------------------------
注意:
在删除归档日志后，必须用RMAN维护控制文件，否则空间显示仍然不释放。
---------------------------------------------------------------------------------------
7：rman target sys/pass
[oracle@localhost ~]$ rman target sys/pass Recovery Manager: Release 11.2.0.3.0 - Production on Sat Jul 14 09:47:17 2018 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. connected to target database: ORCL (DBID=1457025880) RMAN> crosscheck archivelog all; using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=133 device type=DISK validation succeeded for archived log archived log file name=/u01/app/oracle/fast_recovery_area/ORCL/archivelog/2016_11_30/o1_mf_1_6_d3vpslgx_.arc RECID=1 STAMP=929245620 validation succeeded for archived log archived log file name=/u01/app/oracle/fast_recovery_area/ORCL/archivelog/2016_12_01/o1_mf_1_7_d409htxg_.arc RECID=2 STAMP=929482240 RMAN> delete expired archivelog all; released channel: ORA_DISK_1 allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=133 device type=DISK specification does not match any archived log in the repository RMAN> delete archivelog until time 'sysdate-1' ; released channel: ORA_DISK_1 allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=133 device type=DISK List of Archived Log Copies for database with db_unique_name ORCL ===================================================================== Key Thrd Seq S Low Time ------- ---- ------- - --------- 1 1 6 A 30-NOV-16 Name: /u01/app/oracle/fast_recovery_area/ORCL/archivelog/2016_11_30/o1_mf_1_6_d3vpslgx_.arc 2 1 7 A 30-NOV-16 Name: /u01/app/oracle/fast_recovery_area/ORCL/archivelog/2016_12_01/o1_mf_1_7_d409htxg_.arc
8：再次查询，发现使用率正常，已经降到8.67
Recovery Manager complete. [oracle@localhost ~]$ rlwrap sqlplus / as sysdba; SQL*Plus: Release 11.2.0.3.0 Production on Sat Jul 14 09:55:40 2018 Copyright (c) 1982, 2011, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SYS@orcl> 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 8.67 0 6 FILE_TYPE PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE -------------------- ------------------ ------------------------- NUMBER_OF_FILES --------------- BACKUP PIECE 0 0 0 IMAGE COPY 0 0 0 FLASHBACK LOG 0 0 0 FILE_TYPE PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE -------------------- ------------------ ------------------------- NUMBER_OF_FILES --------------- FOREIGN ARCHIVED LOG 0 0 0 7 rows selected. SYS@orcl>
为人：谦逊、激情、博学、审问、慎思、明辨、 笃行
学问：纸上得来终觉浅，绝知此事要躬行
为事：工欲善其事，必先利其器。
态度：道阻且长，行则将至；行而不辍，未来可期
.....................................................................
------- 桃之夭夭，灼灼其华。之子于归，宜其室家。 ---------------
------- 桃之夭夭，有蕡其实。之子于归，宜其家室。 ---------------
------- 桃之夭夭，其叶蓁蓁。之子于归，宜其家人。 ---------------
=====================================================================
* 博客文章部分截图及内容来自于学习的书本及相应培训课程以及网络其他博客，仅做学习讨论之用，不做商业用途。
* 如有侵权，马上联系我，我立马删除对应链接。 * @author Alan -liu * @Email no008@foxmail.com
转载请标注出处！ ✧*꧁一品堂.技术学习笔记꧂*✧. ---> https://www.cnblogs.com/ios9/
学问：纸上得来终觉浅，绝知此事要躬行
为事：工欲善其事，必先利其器。
态度：道阻且长，行则将至；行而不辍，未来可期
.....................................................................
------- 桃之夭夭，灼灼其华。之子于归，宜其室家。 ---------------
------- 桃之夭夭，有蕡其实。之子于归，宜其家室。 ---------------
------- 桃之夭夭，其叶蓁蓁。之子于归，宜其家人。 ---------------
=====================================================================
* 博客文章部分截图及内容来自于学习的书本及相应培训课程以及网络其他博客，仅做学习讨论之用，不做商业用途。
* 如有侵权，马上联系我，我立马删除对应链接。 * @author Alan -liu * @Email no008@foxmail.com
转载请标注出处！ ✧*꧁一品堂.技术学习笔记꧂*✧. ---> https://www.cnblogs.com/ios9/
