归档日志错 ora-00257: archiver error . Connect internal only, until freed.

今天用sqlplus登录时提示 ora-00257: archiver error. Connect internal only, until freed.

很明显，归档日志满了可能

解决如下：

sql> conn zhang/weizhao
ERROR
ora-00257: archiver error. Connect internal only, until freed.
SQL>  SELECT space_limit/1024/1024/1024 AS “Quota_G”,space_used/1024/1024 AS “Used_M”,space_used/space_limit*100 “Used_%”,space_reclaimable AS reclaimable,number_of_files AS files FROM v$recovery_file_dest ;
Quota_G     Used_M     Used_% RECLAIMABLE      FILES
———- ———- ———- ———– ———-
2 2034.59375 99.3453979    20979712          6
sql>show parameter  db_recovery_file_dest_size  –显示 2G

发现2G的空间已用了99%多，现在解决有两个办法，1扩大db_recovery_file_dest_size,2删除db_recovery 区中的数据。

先说第一种

SQL> alter system set DB_RECOVERY_FILE_DEST_SIZE=20g;
系统已更改。

第二种

[oracle@orazhang ~]$ rman target /
Recovery Manager: Release 10.2.0.1.0 – Production on 星期四 12月 2 23:10:52 2010
Copyright (c) 1982, 2005, Oracle.  All rights reserved.
connected to target database: ORCL (DBID=1246063822)
RMAN> list archivelog all
2> ;
using target database control file instead of recovery catalog
List of Archived Log Copies
Key     Thrd Seq     S Low Time   Name
——- —- ——- – ———- —-
243     1    60      A 10-11月-10 /u01/app/oracle/flash_recovery_area/ORCL/archivelog/2010_11_10/o1_mf_1_60_6fob3dn2_.arc
244     1    61      A 10-11月-10 /u01/app/oracle/flash_recovery_area/ORCL/archivelog/2010_11_11/o1_mf_1_61_6fqxqlhc_.arc
245     1    62      A 11-11月-10 /u01/app/oracle/flash_recovery_area/ORCL/archivelog/2010_11_18/o1_mf_1_62_6gb1swxf_.arc
246     1    63      A 18-11月-10 /u01/app/oracle/flash_recovery_area/ORCL/archivelog/2010_11_20/o1_mf_1_63_6gfc8c4w_.arc
247     1    64      A 20-11月-10 /u01/app/oracle/flash_recovery_area/ORCL/archivelog/2010_11_22/o1_mf_1_64_6gmj5nxx_.arc
248     1    65      A 22-11月-10 /u01/app/oracle/flash_recovery_area/ORCL/archivelog/2010_11_24/o1_mf_1_65_6grrxk1d_.arc
249     1    66      A 24-11月-10 /u01/app/oracle/flash_recovery_area/ORCL/archivelog/2010_11_24/o1_mf_1_66_6gt6nnls_.arc
250     1    67      A 24-11月-10 /u01/app/oracle/flash_recovery_area/ORCL/archivelog/2010_11_26/o1_mf_1_67_6gzcv77q_.arc
251     1    68      A 26-11月-10 /u01/app/oracle/flash_recovery_area/ORCL/archivelog/2010_11_27/o1_mf_1_68_6h24kfcc_.arc
252     1    69      A 27-11月-10 /u01/app/oracle/flash_recovery_area/ORCL/archivelog/2010_11_28/o1_mf_1_69_6h4gw10f_.arc
253     1    70      A 28-11月-10 /u01/app/oracle/flash_recovery_area/ORCL/archivelog/2010_11_29/o1_mf_1_70_6h6txhtq_.arc
254     1    71      A 29-11月-10 /u01/app/oracle/flash_recovery_area/ORCL/archivelog/2010_11_30/o1_mf_1_71_6h7o375n_.arc
RMAN> delete archivelog until time ‘sysdate -3’;
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=126 devtype=DISK
List of Archived Log Copies
Key     Thrd Seq     S Low Time   Name
——- —- ——- – ———- —-
243     1    60      A 10-11月-10 /u01/app/oracle/flash_recovery_area/ORCL/archivelog/2010_11_10/o1_mf_1_60_6fob3dn2_.arc
244     1    61      A 10-11月-10 /u01/app/oracle/flash_recovery_area/ORCL/archivelog/2010_11_11/o1_mf_1_61_6fqxqlhc_.arc
245     1    62      A 11-11月-10 /u01/app/oracle/flash_recovery_area/ORCL/archivelog/2010_11_18/o1_mf_1_62_6gb1swxf_.arc
246     1    63      A 18-11月-10 /u01/app/oracle/flash_recovery_area/ORCL/archivelog/2010_11_20/o1_mf_1_63_6gfc8c4w_.arc
247     1    64      A 20-11月-10 /u01/app/oracle/flash_recovery_area/ORCL/archivelog/2010_11_22/o1_mf_1_64_6gmj5nxx_.arc
248     1    65      A 22-11月-10 /u01/app/oracle/flash_recovery_area/ORCL/archivelog/2010_11_24/o1_mf_1_65_6grrxk1d_.arc
249     1    66      A 24-11月-10 /u01/app/oracle/flash_recovery_area/ORCL/archivelog/2010_11_24/o1_mf_1_66_6gt6nnls_.arc
250     1    67      A 24-11月-10 /u01/app/oracle/flash_recovery_area/ORCL/archivelog/2010_11_26/o1_mf_1_67_6gzcv77q_.arc
251     1    68      A 26-11月-10 /u01/app/oracle/flash_recovery_area/ORCL/archivelog/2010_11_27/o1_mf_1_68_6h24kfcc_.arc
252     1    69      A 27-11月-10 /u01/app/oracle/flash_recovery_area/ORCL/archivelog/2010_11_28/o1_mf_1_69_6h4gw10f_.arc
253     1    70      A 28-11月-10 /u01/app/oracle/flash_recovery_area/ORCL/archivelog/2010_11_29/o1_mf_1_70_6h6txhtq_.arc
254     1    71      A 29-11月-10 /u01/app/oracle/flash_recovery_area/ORCL/archivelog/2010_11_30/o1_mf_1_71_6h7o375n_.arc
Do you really want to delete the above objects (enter YES or NO)? yes
deleted archive log
archive log filename=/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2010_11_10/o1_mf_1_60_6fob3dn2_.arc recid=243 stamp=734739184
deleted archive log
archive log filename=/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2010_11_11/o1_mf_1_61_6fqxqlhc_.arc recid=244 stamp=734824820
deleted archive log
archive log filename=/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2010_11_18/o1_mf_1_62_6gb1swxf_.arc recid=245 stamp=735418815
deleted archive log
archive log filename=/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2010_11_20/o1_mf_1_63_6gfc8c4w_.arc recid=246 stamp=735526796
deleted archive log
archive log filename=/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2010_11_22/o1_mf_1_64_6gmj5nxx_.arc recid=247 stamp=735728439
deleted archive log
archive log filename=/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2010_11_24/o1_mf_1_65_6grrxk1d_.arc recid=248 stamp=735901234
deleted archive log
archive log filename=/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2010_11_24/o1_mf_1_66_6gt6nnls_.arc recid=249 stamp=735948054
deleted archive log
archive log filename=/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2010_11_26/o1_mf_1_67_6gzcv77q_.arc recid=250 stamp=736117226
deleted archive log
archive log filename=/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2010_11_27/o1_mf_1_68_6h24kfcc_.arc recid=251 stamp=736208051
deleted archive log
archive log filename=/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2010_11_28/o1_mf_1_69_6h4gw10f_.arc recid=252 stamp=736284162
deleted archive log
archive log filename=/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2010_11_29/o1_mf_1_70_6h6txhtq_.arc recid=253 stamp=736362033
deleted archive log
archive log filename=/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2010_11_30/o1_mf_1_71_6h7o375n_.arc recid=254 stamp=736388840
Deleted 12 objects

删除3天前的归档日志，也可以delete archivelog all;删除全部

RMAN> crosscheck archivelog all;
released channel: ORA_DISK_1
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=126 devtype=DISK
validation succeeded for archived log
archive log filename=/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2010_12_02/o1_mf_1_72_6hhg5xvw_.arc recid=256 stamp=736730311
validation succeeded for archived log
archive log filename=/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2010_12_02/o1_mf_1_73_6hhg5y4k_.arc recid=255 stamp=736730310
validation succeeded for archived log
archive log filename=/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2010_12_02/o1_mf_1_74_6hhg685b_.arc recid=257 stamp=736730320
validation succeeded for archived log
archive log filename=/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2010_12_02/o1_mf_1_75_6hhg68dd_.arc recid=258 stamp=736730320
Crosschecked 4 objects
RMAN> list archivelog all;
List of Archived Log Copies
Key     Thrd Seq     S Low Time   Name
——- —- ——- – ———- —-
256     1    72      A 30-11月-10 /u01/app/oracle/flash_recovery_area/ORCL/archivelog/2010_12_02/o1_mf_1_72_6hhg5xvw_.arc
255     1    73      A 01-12月-10 /u01/app/oracle/flash_recovery_area/ORCL/archivelog/2010_12_02/o1_mf_1_73_6hhg5y4k_.arc
257     1    74      A 01-12月-10 /u01/app/oracle/flash_recovery_area/ORCL/archivelog/2010_12_02/o1_mf_1_74_6hhg685b_.arc
258     1    75      A 01-12月-10 /u01/app/oracle/flash_recovery_area/ORCL/archivelog/2010_12_02/o1_mf_1_75_6hhg68dd_.arc
SQL> SELECT space_limit/1024/1024/1024 AS “Quota_G”,space_used/1024/1024 AS “Used_M”,space_used/space_limit*100 “Used_%”,space_reclaimable AS
2  reclaimable,number_of_files AS files FROM v$recovery_file_dest ;
Quota_G     Used_M     Used_% RECLAIMABLE      FILES
———- ———- ———- ———– ———-
2 607.058105 29.6415091    20979712 1.8447E+19

再检测一下归档日志，发现已使用为29%，可以连接了。其实db_recovery_file_dest_size虽然有2G发现归档明显也没有那么多那为什么会满呢，原因是2G不只有归档日志还有flash_recovery_area里的其它文件如backup file,rman copy file，以下查询

SQL> run
1* select * from v$flash_recovery_area_usage
FILE_TYPE                PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES
———————— —————— ————————- —————
CONTROLFILE                               0                         0               0
ONLINELOG                             12.21                         0               4
ARCHIVELOG                            45.41                         0              16
BACKUPPIECE                            2.56                       .65               3
IMAGECOPY                              47.2                       .33               5
FLASHBACKLOG                              0                         0               0
6 rows selected.