错误提示
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 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;