ORA-00257

来自官方文档的解释

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>
总结:此错误由于归档日志超过闪回空间所致,定期清除归档日志即可避免次错误发生。

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值