ORA-00257

一、问题描述

使用sqlplus 登录Oracle服务器时，如下问题：

SQL*Plus: Release 11.2.0.1.0 Production on Thu May 6 09:37:31 2021

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

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

二、问题解决

2.1 使用sysdba登录

[oracle@node1 ora]$ sqlplus /nolog

SQL*Plus: Release 11.2.0.1.0 Production on Thu May 6 09:38:42 2021

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


SQL> connect as sysdba
Enter user-name: LOGMINER
Enter password: 

2.2 查看归档日志信息

SQL> show parameter log_archive_dest;

NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest		     string	 /ora/oracle/archive
log_archive_dest_1		     string
log_archive_dest_10		     string
log_archive_dest_11		     string
log_archive_dest_12		     string
log_archive_dest_13		     string
log_archive_dest_14		     string
log_archive_dest_15		     string
log_archive_dest_16		     string
log_archive_dest_17		     string
log_archive_dest_18		     string

NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_19		     string
log_archive_dest_2		     string
log_archive_dest_20		     string
log_archive_dest_21		     string
log_archive_dest_22		     string
log_archive_dest_23		     string
log_archive_dest_24		     string
log_archive_dest_25		     string
log_archive_dest_26		     string
log_archive_dest_27		     string
log_archive_dest_28		     string

NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_29		     string

2.4 查看归档日志文件所在的位置

SQL> show parameter recover

NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest		     string	 /ora/oracle/flash_recovery_are
						 a
db_recovery_file_dest_size	     big integer 3882M
recovery_parallelism		     integer	 0

2.5 检查flash recovery area的使用情况

从结果看已经使用99.77%

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			  99.77 			0
	     95


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

2.6 进入归档日志所在的位置，将时间较早的归档日志删除

cd /ora/oracle/flash_oracle_recory_area
mv ....

2.7 使用rman登录

RMAN> connect target LOGMINER/LOGMINER@orcl

connected to target database: ORCL (DBID=1596267283)

RMAN> 

2.8 检查无用的日志文件

RMAN> crosscheck archivelog all;

using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=71 device type=DISK
validation failed for archived log
archived log file name=/ora/oracle/flash_recovery_area/ORCL/archivelog/2021_03_30/o1_mf_1_7_j65d3kwn_.arc RECID=1 STAMP=1068555634
validation failed for archived log
archived log file name=/ora/oracle/flash_recovery_area/ORCL/archivelog/2021_03_30/o1_mf_1_8_j66cqjqs_.arc RECID=2 STAMP=1068588017
validation failed for archived log
archived log file name=/ora/oracle/flash_recovery_area/ORCL/archivelog/2021_03_30/o1_mf_1_9_j66dr1z0_.arc RECID=3 STAMP=1068589058
validation failed for archived log
archived log file name=/ora/oracle/flash_recovery_area/ORCL/archivelog/2021_03_31/o1_mf_1_10_j67nzsxj_.arc RECID=4 STAMP=106863026
6
validation failed for archived log
archived log file name=/ora/oracle/flash_recovery_area/ORCL/archivelog/2021_03_31/o1_mf_1_11_j6903odj_.arc RECID=5 STAMP=106867442
1
validation failed for archived log
archived log file name=/ora/oracle/flash_recovery_area/ORCL/archivelog/2021_04_01/o1_mf_1_12_j69nwj62_.arc RECID=6 STAMP=106869569
6

2.9 删除过期的日志文件

RMAN> delete expired archivelog all;

released channel: ORA_DISK_1
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=71 device type=DISK
List of Archived Log Copies for database with db_unique_name ORCL
====================================================================

2.10 查看删除后的情况

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			  10.71 			0
	     10


FILE_TYPE	     PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE
-------------------- ------------------ -------------------------
NUMBER_OF_FILES

2.11 测试连接

[oracle@node1 ora]$ sqlplus

SQL*Plus: Release 11.2.0.1.0 Production on Thu May 6 09:54:24 2021

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

Enter user-name: LOGMINER
Enter password: 

Connected to:
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>