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

发布时间：2020-12-12 18:48:00 所属栏目：百科来源：网络整理

导读：错误提示 ORA-00257: archiver error. Connect internal only,until freed 处理方法如下用sys用户登录 sqlplus / as sysdba; 看archive log日志 SQL show parameter log_archive_dest;NAME TYPE VALUE------------------------------------ ----------- ---

错误提示

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

处理方法如下

用sys用户登录

sqlplus / as sysdba;

看archive log日志

SQL> 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_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
log_archive_dest_3		     string
log_archive_dest_30		     string
log_archive_dest_31		     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
log_archive_dest_state_1	     string	 enable

NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_state_10	     string	 enable
log_archive_dest_state_11	     string	 enable
log_archive_dest_state_12	     string	 enable
log_archive_dest_state_13	     string	 enable
log_archive_dest_state_14	     string	 enable
log_archive_dest_state_15	     string	 enable
log_archive_dest_state_16	     string	 enable
log_archive_dest_state_17	     string	 enable
log_archive_dest_state_18	     string	 enable
log_archive_dest_state_19	     string	 enable
log_archive_dest_state_2	     string	 enable

NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_state_20	     string	 enable
log_archive_dest_state_21	     string	 enable
log_archive_dest_state_22	     string	 enable
log_archive_dest_state_23	     string	 enable
log_archive_dest_state_24	     string	 enable
log_archive_dest_state_25	     string	 enable
log_archive_dest_state_26	     string	 enable
log_archive_dest_state_27	     string	 enable
log_archive_dest_state_28	     string	 enable
log_archive_dest_state_29	     string	 enable
log_archive_dest_state_3	     string	 enable

NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_state_30	     string	 enable
log_archive_dest_state_31	     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

检查log sequence

SQL> archive log list;
Database log mode	       Archive Mode
Automatic archival	       Enabled
Archive destination	       USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     380
Next log sequence to archive   382
Current log sequence	       382

检查flash recovery area的使用情况，可以看见archivelog已经很大了，达到98.2

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			   98.2 			0
	     73


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 rows selected.

计算flash recovery area已经占用的空间

SQL> select sum(percent_space_used)*3/100 from v$flash_recovery_area_usage;

查看日志所在目录及日志空间设置的最大值

SQL> show parameter db_recovery_file_dest;

NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest		     string	 /data/flash_recovery_area
db_recovery_file_dest_size	     big integer 10G

根据以上结果得知，归档位置为 /data/flash_recovery_area

用Rman转移或清除对应的归档日志，删除一些不用的日期目录的文件，注意保留最后几个文件。

注意：如果直接删除归档日志后，必须用RMAN维护控制文件，否则空间显示仍然不释放。

rman target /

[oracle@Oracle ~]$ rman target /

Recovery Manager: Release 11.2.0.1.0 - Production on Wed Feb 15 14:05:26 2017

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

connected to target database: ORCL (DBID=1463371902)

RMAN>

检查一些无用的archivelog

RMAN> crosscheck archivelog all;

删除过期的归档

RMAN> delete expired archivelog all;

RMAN> delete archivelog until time 'sysdate-1';    -- 删除截止到前一天的所有archivelog

再次查询，发现使用率正常，已经降到28.8

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			   28.8 			0
	     73


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 rows selected.

日志操作常用命令

校验日志的可用性

RMAN> crosscheck archivelog all;

列出所有失效的归档日志

RMAN> list expired archivelog all;

删除log sequence为16及16之前的所有归档日志

RMAN> delete archivelog until sequence 16;

删除系统时间7天以前的归档日志，不会删除闪回区有效的归档日志

RMAN> delete archivelog all completed before 'sysdate-7';

删除系统时间1天以前的归档日志，不会删除闪回区有效的归档日志

RMAN> delete archivelog all completed before 'sysdate - 1';

删除系统时间1天以内到现在的归档日志

RMAN> delete archivelog from time 'sysdate-1';

强制清除系统时间1天前的归档日志

RMAN> delete force archivelog all completed before 'sysdate - 1';

该命令清除所有的归档日志

RMAN> delete noprompt archivelog all completed before 'sysdate';

RMAN> delete noprompt archivelog all;

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!