来自官方文档的解释
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.
现象:切换用户时,出现错误
SYS@ orcl>conn scott/tiger
ERROR:
ORA-00257: archiver error. Connect internal only, until freed.
Warning: You are no longer connected to ORACLE.
解决方法:缩减归档日志
1.使用sysdba用户登录查看archive log 存放位置:
@ >conn / as sysdba
Connected.
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_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
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 28
Next log sequence to archive 28
Current log sequence 30
3.检查flash recovery area的使用情况
SYS@ orcl>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 63.97 0 27
BACKUPPIECE 34.23 .07 8
IMAGECOPY 0 0 0
FLASHBACKLOG 0 0 0
6 rows selected.
4.计算flash recovery area已经占用的空间:达到2.946GB
SYS@ orcl>select sum(percent_space_used)*3/100 from v$flash_recovery_area_usage;
SUM(PERCENT_SPACE_USED)*3/100
-----------------------------
2.946
5.查询闪回空间,发现只有2G,超过了规定值。
SYS@ orcl>show parameter recover
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string /u01/app/oracle/flash_recovery
_area
db_recovery_file_dest_size big integer 2G
recovery_parallelism integer 0
6.登陆RMAN,校验归档日志
[oracle@oracle ~]$ rman target /
Recovery Manager: Release 10.2.0.1.0 - Production on Sun Aug 10 10:25:49 2014
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database: ORCL (DBID=1365770982)
RMAN> crosscheck archivelog all;
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=214 devtype=DISK
validation succeeded for archived log
archive log filename=/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2014_07_05/o1_mf_1_1_9vgn0lkz_.arc recid=25 stamp=852109774
validation succeeded for archived log
archive log filename=/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2014_07_12/o1_mf_1_2_9w2ksx8y_.arc recid=26 stamp=852762829
validation succeeded for archived log
archive log filename=/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2014_07_12/o1_mf_1_3_9w2o7pfw_.arc recid=28 stamp=852766376
validation succeeded for archived log
archive log filename=/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2014_07_12/o1_mf_1_4_9w2o8ggy_.arc recid=27 stamp=852766373
validation succeeded for archived log
archive log filename=/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2014_07_12/o1_mf_1_5_9w2ocq0d_.arc recid=29 stamp=852766464
validation succeeded for archived log
archive log filename=/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2014_07_12/o1_mf_1_6_9w2om4rs_.arc recid=30 stamp=852766695
validation succeeded for archived log
archive log filename=/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2014_07_13/o1_mf_1_7_9w46xq2m_.arc recid=31 stamp=852817213
validation succeeded for archived log
archive log filename=/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2014_07_13/o1_mf_1_8_9w46y8cs_.arc recid=32 stamp=852817231
validation succeeded for archived log
archive log filename=/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2014_07_13/o1_mf_1_9_9w46ytcp_.arc recid=33 stamp=852817247
validation succeeded for archived log
archive log filename=/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2014_07_13/o1_mf_1_10_9w46z760_.arc recid=34 stamp=852817260
validation succeeded for archived log
archive log filename=/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2014_07_13/o1_mf_1_11_9w46zm03_.arc recid=35 stamp=852817269
validation succeeded for archived log
archive log filename=/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2014_07_13/o1_mf_1_12_9w46zw3l_.arc recid=36 stamp=852817279
validation succeeded for archived log
archive log filename=/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2014_07_13/o1_mf_1_13_9w4706pg_.arc recid=37 stamp=852817291
validation succeeded for archived log
archive log filename=/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2014_07_13/o1_mf_1_14_9w472ng0_.arc recid=38 stamp=852817369
validation succeeded for archived log
archive log filename=/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2014_07_13/o1_mf_1_15_9w472yfm_.arc recid=39 stamp=852817379
validation succeeded for archived log
archive log filename=/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2014_07_13/o1_mf_1_16_9w473b2h_.arc recid=40 stamp=852817389
validation succeeded for archived log
archive log filename=/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2014_07_13/o1_mf_1_17_9w473pl7_.arc recid=41 stamp=852817400
validation succeeded for archived log
archive log filename=/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2014_07_13/o1_mf_1_18_9w4746d0_.arc recid=42 stamp=852817417
validation succeeded for archived log
archive log filename=/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2014_07_13/o1_mf_1_19_9w474n5v_.arc recid=43 stamp=852817430
validation succeeded for archived log
archive log filename=/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2014_07_13/o1_mf_1_20_9w4756ol_.arc recid=44 stamp=852817449
validation succeeded for archived log
archive log filename=/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2014_07_13/o1_mf_1_21_9w475mlt_.arc recid=45 stamp=852817463
validation succeeded for archived log
archive log filename=/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2014_07_13/o1_mf_1_22_9w4760bb_.arc recid=46 stamp=852817474
validation succeeded for archived log
archive log filename=/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2014_07_13/o1_mf_1_23_9w476dgr_.arc recid=47 stamp=852817487
validation succeeded for archived log
archive log filename=/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2014_07_13/o1_mf_1_24_9w476w0y_.arc recid=48 stamp=852817503
validation succeeded for archived log
archive log filename=/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2014_07_13/o1_mf_1_25_9w477dwb_.arc recid=49 stamp=852817521
validation succeeded for archived log
archive log filename=/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2014_07_13/o1_mf_1_26_9w477r7x_.arc recid=50 stamp=852817530
validation succeeded for archived log
archive log filename=/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2014_07_13/o1_mf_1_27_9w4784v2_.arc recid=51 stamp=852817543
Crosschecked 27 objects
7.删除过期的日志,因为是测试环境,这里采取删除前一天的归档日志。
RMAN> delete archivelog until time 'sysdate-1';
released channel: ORA_DISK_1
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=214 devtype=DISK
List of Archived Log Copies
Key Thrd Seq S Low Time Name
------- ---- ------- - --------- ----
25 1 1 A 29-JUN-14 /u01/app/oracle/flash_recovery_area/ORCL/archivelog/2014_07_05/o1_mf_1_1_9vgn0lkz_.arc
26 1 2 A 05-JUL-14 /u01/app/oracle/flash_recovery_area/ORCL/archivelog/2014_07_12/o1_mf_1_2_9w2ksx8y_.arc
28 1 3 A 12-JUL-14 /u01/app/oracle/flash_recovery_area/ORCL/archivelog/2014_07_12/o1_mf_1_3_9w2o7pfw_.arc
27 1 4 A 12-JUL-14 /u01/app/oracle/flash_recovery_area/ORCL/archivelog/2014_07_12/o1_mf_1_4_9w2o8ggy_.arc
29 1 5 A 12-JUL-14 /u01/app/oracle/flash_recovery_area/ORCL/archivelog/2014_07_12/o1_mf_1_5_9w2ocq0d_.arc
30 1 6 A 12-JUL-14 /u01/app/oracle/flash_recovery_area/ORCL/archivelog/2014_07_12/o1_mf_1_6_9w2om4rs_.arc
31 1 7 A 12-JUL-14 /u01/app/oracle/flash_recovery_area/ORCL/archivelog/2014_07_13/o1_mf_1_7_9w46xq2m_.arc
32 1 8 A 13-JUL-14 /u01/app/oracle/flash_recovery_area/ORCL/archivelog/2014_07_13/o1_mf_1_8_9w46y8cs_.arc
33 1 9 A 13-JUL-14 /u01/app/oracle/flash_recovery_area/ORCL/archivelog/2014_07_13/o1_mf_1_9_9w46ytcp_.arc
34 1 10 A 13-JUL-14 /u01/app/oracle/flash_recovery_area/ORCL/archivelog/2014_07_13/o1_mf_1_10_9w46z760_.arc
35 1 11 A 13-JUL-14 /u01/app/oracle/flash_recovery_area/ORCL/archivelog/2014_07_13/o1_mf_1_11_9w46zm03_.arc
36 1 12 A 13-JUL-14 /u01/app/oracle/flash_recovery_area/ORCL/archivelog/2014_07_13/o1_mf_1_12_9w46zw3l_.arc
37 1 13 A 13-JUL-14 /u01/app/oracle/flash_recovery_area/ORCL/archivelog/2014_07_13/o1_mf_1_13_9w4706pg_.arc
38 1 14 A 13-JUL-14 /u01/app/oracle/flash_recovery_area/ORCL/archivelog/2014_07_13/o1_mf_1_14_9w472ng0_.arc
39 1 15 A 13-JUL-14 /u01/app/oracle/flash_recovery_area/ORCL/archivelog/2014_07_13/o1_mf_1_15_9w472yfm_.arc
40 1 16 A 13-JUL-14 /u01/app/oracle/flash_recovery_area/ORCL/archivelog/2014_07_13/o1_mf_1_16_9w473b2h_.arc
41 1 17 A 13-JUL-14 /u01/app/oracle/flash_recovery_area/ORCL/archivelog/2014_07_13/o1_mf_1_17_9w473pl7_.arc
42 1 18 A 13-JUL-14 /u01/app/oracle/flash_recovery_area/ORCL/archivelog/2014_07_13/o1_mf_1_18_9w4746d0_.arc
43 1 19 A 13-JUL-14 /u01/app/oracle/flash_recovery_area/ORCL/archivelog/2014_07_13/o1_mf_1_19_9w474n5v_.arc
44 1 20 A 13-JUL-14 /u01/app/oracle/flash_recovery_area/ORCL/archivelog/2014_07_13/o1_mf_1_20_9w4756ol_.arc
45 1 21 A 13-JUL-14 /u01/app/oracle/flash_recovery_area/ORCL/archivelog/2014_07_13/o1_mf_1_21_9w475mlt_.arc
46 1 22 A 13-JUL-14 /u01/app/oracle/flash_recovery_area/ORCL/archivelog/2014_07_13/o1_mf_1_22_9w4760bb_.arc
47 1 23 A 13-JUL-14 /u01/app/oracle/flash_recovery_area/ORCL/archivelog/2014_07_13/o1_mf_1_23_9w476dgr_.arc
48 1 24 A 13-JUL-14 /u01/app/oracle/flash_recovery_area/ORCL/archivelog/2014_07_13/o1_mf_1_24_9w476w0y_.arc
49 1 25 A 13-JUL-14 /u01/app/oracle/flash_recovery_area/ORCL/archivelog/2014_07_13/o1_mf_1_25_9w477dwb_.arc
50 1 26 A 13-JUL-14 /u01/app/oracle/flash_recovery_area/ORCL/archivelog/2014_07_13/o1_mf_1_26_9w477r7x_.arc
51 1 27 A 13-JUL-14 /u01/app/oracle/flash_recovery_area/ORCL/archivelog/2014_07_13/o1_mf_1_27_9w4784v2_.arc
Do you really want to delete the above objects (enter YES or NO)? y
deleted archive log
archive log filename=/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2014_07_05/o1_mf_1_1_9vgn0lkz_.arc recid=25 stamp=852109774
deleted archive log
archive log filename=/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2014_07_12/o1_mf_1_2_9w2ksx8y_.arc recid=26 stamp=852762829
deleted archive log
archive log filename=/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2014_07_12/o1_mf_1_3_9w2o7pfw_.arc recid=28 stamp=852766376
deleted archive log
archive log filename=/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2014_07_12/o1_mf_1_4_9w2o8ggy_.arc recid=27 stamp=852766373
deleted archive log
archive log filename=/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2014_07_12/o1_mf_1_5_9w2ocq0d_.arc recid=29 stamp=852766464
deleted archive log
archive log filename=/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2014_07_12/o1_mf_1_6_9w2om4rs_.arc recid=30 stamp=852766695
deleted archive log
archive log filename=/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2014_07_13/o1_mf_1_7_9w46xq2m_.arc recid=31 stamp=852817213
deleted archive log
archive log filename=/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2014_07_13/o1_mf_1_8_9w46y8cs_.arc recid=32 stamp=852817231
deleted archive log
archive log filename=/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2014_07_13/o1_mf_1_9_9w46ytcp_.arc recid=33 stamp=852817247
deleted archive log
archive log filename=/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2014_07_13/o1_mf_1_10_9w46z760_.arc recid=34 stamp=852817260
deleted archive log
archive log filename=/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2014_07_13/o1_mf_1_11_9w46zm03_.arc recid=35 stamp=852817269
deleted archive log
archive log filename=/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2014_07_13/o1_mf_1_12_9w46zw3l_.arc recid=36 stamp=852817279
deleted archive log
archive log filename=/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2014_07_13/o1_mf_1_13_9w4706pg_.arc recid=37 stamp=852817291
deleted archive log
archive log filename=/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2014_07_13/o1_mf_1_14_9w472ng0_.arc recid=38 stamp=852817369
deleted archive log
archive log filename=/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2014_07_13/o1_mf_1_15_9w472yfm_.arc recid=39 stamp=852817379
deleted archive log
archive log filename=/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2014_07_13/o1_mf_1_16_9w473b2h_.arc recid=40 stamp=852817389
deleted archive log
archive log filename=/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2014_07_13/o1_mf_1_17_9w473pl7_.arc recid=41 stamp=852817400
deleted archive log
archive log filename=/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2014_07_13/o1_mf_1_18_9w4746d0_.arc recid=42 stamp=852817417
deleted archive log
archive log filename=/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2014_07_13/o1_mf_1_19_9w474n5v_.arc recid=43 stamp=852817430
deleted archive log
archive log filename=/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2014_07_13/o1_mf_1_20_9w4756ol_.arc recid=44 stamp=852817449
deleted archive log
archive log filename=/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2014_07_13/o1_mf_1_21_9w475mlt_.arc recid=45 stamp=852817463
deleted archive log
archive log filename=/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2014_07_13/o1_mf_1_22_9w4760bb_.arc recid=46 stamp=852817474
deleted archive log
archive log filename=/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2014_07_13/o1_mf_1_23_9w476dgr_.arc recid=47 stamp=852817487
deleted archive log
archive log filename=/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2014_07_13/o1_mf_1_24_9w476w0y_.arc recid=48 stamp=852817503
deleted archive log
archive log filename=/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2014_07_13/o1_mf_1_25_9w477dwb_.arc recid=49 stamp=852817521
deleted archive log
archive log filename=/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2014_07_13/o1_mf_1_26_9w477r7x_.arc recid=50 stamp=852817530
deleted archive log
archive log filename=/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2014_07_13/o1_mf_1_27_9w4784v2_.arc recid=51 stamp=852817543
Deleted 27 objects
8.再次查看闪回空间的情况
SYS@ orcl>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 7.1 0 3
BACKUPPIECE 34.23 1.4 8
IMAGECOPY 0 0 0
FLASHBACKLOG 0 0 0
6 rows selected.
9.切换用户后,问题已经解决
SYS@ orcl>conn scott/tiger
Connected.
SCOTT@ orcl>
总结:此错误由于归档日志超过闪回空间所致,定期清除归档日志即可避免次错误发生。