ORA-00257: archiver error. Connect internal only, until freed 解决方法

C:\Users\Administrator>sqlplus h2/hydeesoft@orcl

SQL*Plus: Release 11.2.0.1.0 Production on 星期二 9月 30 10:57:46 2014

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

ERROR:
ORA-00257: archiver error. Connect internal only, until freed.

--系统用户登录，查看归档已用空间大小
C:\Users\Administrator>sqlplus sys/oracle as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on 星期二 9月 30 10:58:10 2014

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

连接到:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> show parameter db_recover;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest                string      E:\app\Administrator\flash_rec
                                                 overy_area
db_recovery_file_dest_size           big integer 3912M
SQL> set linesize 200;
SQL> select * from v$flash_recovery_area_usage;

FILE_TYPE PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILE
S
-------------------- ------------------ ------------------------- --------------
-
CONTROL FILE                          0                         0
0
REDO LOG                              0                         0
0
ARCHIVED LOG                       99.1                         0              9
0
BACKUP PIECE                          0                         0
0
IMAGE COPY                            0                         0
0
FLASHBACK LOG                         0                         0
0
FOREIGN ARCHIVED LOG                  0                         0
0

已选择7行。

方法一：扩大归档日志存放空间大小

SQL> alter system set db_recovery_file_dest_size=10g;

系统已更改。

SQL> select * from v$flash_recovery_area_usage;

FILE_TYPE            PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILE
S
-------------------- ------------------ ------------------------- --------------
-
CONTROL FILE                          0                         0
0
REDO LOG                              0                         0
0
ARCHIVED LOG                      37.86                         0              9
0
BACKUP PIECE                          0                         0
0
IMAGE COPY                            0                         0
0
FLASHBACK LOG                         0                         0
0
FOREIGN ARCHIVED LOG                  0                         0
0

已选择7行。
SQL> conn h2/hydeesoft
已连接。
SQL> show parameter recover;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest                string      E:\app\Administrator\flash_rec
                                                 overy_area
db_recovery_file_dest_size           big integer 10G
recovery_parallelism                 integer     0
SQL> exit;
从 Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options 断
开

方法二：清除过期归档日志（或强制删除指定的归档日志）
C:\Users\Administrator>rman target /

恢复管理器: Release 11.2.0.1.0 - Production on 星期二 9月 30 11:38:07 2014

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

连接到目标数据库: ORCL (DBID=1383696692)

RMAN> crosscheck archivelog all;
……
对归档日志的验证成功
归档日志文件名=E:\APP\ADMINISTRATOR\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG\2014_09_
30\O1_MF_1_414_B2N9PX60_.ARC RECID=161 STAMP=859635808
对归档日志的验证成功
归档日志文件名=E:\APP\ADMINISTRATOR\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG\2014_09_
30\O1_MF_1_415_B2N9Q8WD_.ARC RECID=162 STAMP=859635820
……
RMAN> delete expired archivelog all;

释放的通道: ORA_DISK_1
分配的通道: ORA_DISK_1
通道 ORA_DISK_1: SID=138 设备类型=DISK
说明与资料档案库中的任何归档日志都不匹配

RMAN> delete archivelog until time 'sysdate’;-- 删除截止到今天的所有archivelog
……
159     1    412     A 30-9月 -14
        名称: E:\APP\ADMINISTRATOR\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG\2014_09_3
0\O1_MF_1_412_B2N9P5NV_.ARC

160     1    413     A 30-9月 -14
        名称: E:\APP\ADMINISTRATOR\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG\2014_09_3
0\O1_MF_1_413_B2N9PJRP_.ARC

161     1    414     A 30-9月 -14
        名称: E:\APP\ADMINISTRATOR\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG\2014_09_3
0\O1_MF_1_414_B2N9PX60_.ARC

162     1    415     A 30-9月 -14
        名称: E:\APP\ADMINISTRATOR\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG\2014_09_3
0\O1_MF_1_415_B2N9Q8WD_.ARC

163     1    416     A 30-9月 -14
        名称: E:\APP\ADMINISTRATOR\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG\2014_09_3
0\O1_MF_1_416_B2N9QR5N_.ARC

是否确定要删除以上对象 (输入 YES 或 NO)?yes
……
已删除的归档日志
归档日志文件名=E:\APP\ADMINISTRATOR\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG\2014_09_
30\O1_MF_1_415_B2N9Q8WD_.ARC RECID=162 STAMP=859635820
已删除的归档日志
归档日志文件名=E:\APP\ADMINISTRATOR\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG\2014_09_
30\O1_MF_1_416_B2N9QR5N_.ARC RECID=163 STAMP=859635835
163 对象已删除

SQL> 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                          0                         0
              0

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行。

SQL>

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/21251711/viewspace-1285874/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/21251711/viewspace-1285874/