注：本文参考了《 》
一：问题背景：
今天在 做外部表的时候，出现了下图的问题；
二：具体操作步骤
1： 看看archiv log所在位置
[oracle@localhost ~]$ rlwrap sqlplus / sysdba;*Plus: Release 11.2.0.3.0 Production Sat Jul 14 09:43:00 2018Copyright (c) 1982, 2011, Oracle. rights reserved.Connected :Oracle 11g Enterprise Edition Release 11.2.0.3.0 - Production the Partitioning, OLAP, Data Mining Application Testing optionsSYS@orcl> show parameter log_archive_dest;NAME TYPE ------------------------------------ ----------- ------------------------------log_archive_dest stringlog_archive_dest_1 stringlog_archive_dest_10 stringlog_archive_dest_11 stringlog_archive_dest_12 stringlog_archive_dest_13 stringlog_archive_dest_14 stringlog_archive_dest_15 stringlog_archive_dest_16 stringlog_archive_dest_17 stringlog_archive_dest_18 stringNAME TYPE ------------------------------------ ----------- ------------------------------log_archive_dest_19 stringlog_archive_dest_2 stringlog_archive_dest_20 stringlog_archive_dest_21 stringlog_archive_dest_22 stringlog_archive_dest_23 stringlog_archive_dest_24 stringlog_archive_dest_25 stringlog_archive_dest_26 stringlog_archive_dest_27 stringlog_archive_dest_28 stringNAME TYPE ------------------------------------ ----------- ------------------------------log_archive_dest_29 stringlog_archive_dest_3 stringlog_archive_dest_30 stringlog_archive_dest_31 stringlog_archive_dest_4 stringlog_archive_dest_5 stringlog_archive_dest_6 stringlog_archive_dest_7 stringlog_archive_dest_8 stringlog_archive_dest_9 stringlog_archive_dest_state_1 string enableNAME TYPE ------------------------------------ ----------- ------------------------------log_archive_dest_state_10 string enablelog_archive_dest_state_11 string enablelog_archive_dest_state_12 string enablelog_archive_dest_state_13 string enablelog_archive_dest_state_14 string enablelog_archive_dest_state_15 string enablelog_archive_dest_state_16 string enablelog_archive_dest_state_17 string enablelog_archive_dest_state_18 string enablelog_archive_dest_state_19 string enablelog_archive_dest_state_2 string ENABLENAME TYPE ------------------------------------ ----------- ------------------------------log_archive_dest_state_20 string enablelog_archive_dest_state_21 string enablelog_archive_dest_state_22 string enablelog_archive_dest_state_23 string enablelog_archive_dest_state_24 string enablelog_archive_dest_state_25 string enablelog_archive_dest_state_26 string enablelog_archive_dest_state_27 string enablelog_archive_dest_state_28 string enablelog_archive_dest_state_29 string enablelog_archive_dest_state_3 string enableNAME TYPE ------------------------------------ ----------- ------------------------------log_archive_dest_state_30 string enablelog_archive_dest_state_31 string enablelog_archive_dest_state_4 string enablelog_archive_dest_state_5 string enablelog_archive_dest_state_6 string enablelog_archive_dest_state_7 string enablelog_archive_dest_state_8 string enablelog_archive_dest_state_9 string enable
2：一般VALUE为空时，可以用archive log list;检查一下归档目录和log sequence
SYS@orcl> archive log list; log mode Archive ModeAutomatic archival EnabledArchive destination USE_DB_RECOVERY_FILE_DESTOldest online log sequence 357 log sequence archive 357 log sequence 360
3： 检查flash recovery area的使用情况,可以看见archivelog已经很大了，达到99.1
SYS@orcl> * V$FLASH_RECOVERY_AREA_USAGE;FILE_TYPE PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES-------------------- ------------------ ------------------------- ---------------CONTROL 0 0 0REDO LOG 0 0 0ARCHIVED LOG 99.1 0 PIECE 0 0 0 COPY 0 0 0FLASHBACK LOG 0 0 0 ARCHIVED LOG 0 0 07 selected.
4：计算flash recovery area已经占用的空间
SYS@orcl> (percent_space_used)*3/100 v$flash_recovery_area_usage;(PERCENT_SPACE_USED)*3/100----------------------------- 2.973
5： 找到recovery目录, show parameter recover
SYS@orcl> show parameter recover;NAME TYPE ------------------------------------ ----------- ------------------------------db_recovery_file_dest string /u01/app/oracle/fast_recovery_area/ORCL/db_recovery_file_destdb_recovery_file_dest_size big 3Gdb_unrecoverable_scn_tracking boolean recovery_parallelism 0SYS@orcl> ho ls -al /u01/app/oracle/fast_recovery_area/ORCLtotal 20drwxr-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 archivelogdrwxr-xr-x 3 oracle oinstall 4096 May 21 13:00 db_recovery_file_destdrwxr-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]$ lsarchivelog db_recovery_file_dest onlinelog[oracle@localhost ORCL]$ cd db_recovery_file_dest/[oracle@localhost db_recovery_file_dest]$ lsORCL[oracle@localhost db_recovery_file_dest]$ cd ORCL/[oracle@localhost ORCL]$ lsarchivelog[oracle@localhost ORCL]$ cd archivelog/[oracle@localhost archivelog]$ ls2018_05_21 2018_05_23 2018_05_25 2018_05_27 2018_05_29 2018_05_31 2018_06_07 2018_07_142018_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 directoryrm: cannot remove `2018_05_22': Is a directoryrm: cannot remove `2018_05_23': Is a directoryrm: cannot remove `2018_05_24': Is a directoryrm: cannot remove `2018_05_25': Is a directoryrm: cannot remove `2018_05_26': Is a directoryrm: cannot remove `2018_05_27': Is a directoryrm: cannot remove `2018_05_28': Is a directoryrm: cannot remove `2018_05_29': Is a directoryrm: cannot remove `2018_05_30': Is a directoryrm: 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]$ ls2018_07_14[oracle@localhost archivelog]$---------------------------------------------------------------------------------------注意:在删除归档日志后，必须用RMAN维护控制文件，否则空间显示仍然不释放。---------------------------------------------------------------------------------------
7：rman target sys/pass
[oracle@localhost ~]$Recovery Manager: Release 11.2.0.3.0 - Production on Sat Jul 14 09:47:17 2018Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.connected to target database: ORCL (DBID=1457025880)RMAN> using target database control file instead of recovery catalogallocated channel: ORA_DISK_1channel ORA_DISK_1: SID=133 device type=DISKvalidation succeeded for archived logarchived log file name=/u01/app/oracle/fast_recovery_area/ORCL/archivelog/2016_11_30/o1_mf_1_6_d3vpslgx_.arc RECID=1 STAMP=929245620validation succeeded for archived logarchived log file name=/u01/app/oracle/fast_recovery_area/ORCL/archivelog/2016_12_01/o1_mf_1_7_d409htxg_.arc RECID=2 STAMP=929482240RMAN> released channel: ORA_DISK_1allocated channel: ORA_DISK_1channel ORA_DISK_1: SID=133 device type=DISKspecification does not match any archived log in the repositoryRMAN> released channel: ORA_DISK_1allocated channel: ORA_DISK_1channel ORA_DISK_1: SID=133 device type=DISKList of Archived Log Copies for database with db_unique_name ORCL=====================================================================Key Thrd Seq S Low ------- ---- ------- - ---------1 1 6 A 30-NOV-16 Name: /u01/app/oracle/fast_recovery_area/ORCL/archivelog/2016_11_30/o1_mf_1_6_d3vpslgx_.arc2 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 2018Copyright (c) 1982, 2011, Oracle. All rights reserved.Connected to:Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - ProductionWith the Partitioning, OLAP, Mining and Real Application Testing optionsSYS@orcl> select * from V$FLASH_RECOVERY_AREA_USAGE;FILE_TYPE PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE-------------------- ------------------ -------------------------NUMBER_OF_FILES---------------CONTROL FILE 0 0 0REDO LOG 0 0 0ARCHIVED LOG 8.67 0 6FILE_TYPE PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE-------------------- ------------------ -------------------------NUMBER_OF_FILES---------------BACKUP PIECE 0 0 0IMAGE COPY 0 0 0FLASHBACK LOG 0 0 0FILE_TYPE PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE-------------------- ------------------ -------------------------NUMBER_OF_FILES---------------FOREIGN ARCHIVED LOG 0 0 07 rows selected.SYS@orcl>