oracle报ORA-00257的解决办法

OAR-00257 aichivelog归档错误

• 登录服务器，切换oracle

  su -oracle;
  sqlplus /nolog;
  SQL> conn /as sysdba;
  

• 检查fast_recovery_area的使用情况

  SQL> select * from V$FLASH_RECOVERY_AREA_USAGE;
  

• 计算fast_recovery_area已经占用的空间

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

• 修改fast_recovery_aread的空间

  SQL> ALTER SYSTEM SET DB_RECOVERY_FILE_DEST_SIZE=8g;
  

• 查询日志目录位置

  SQL> show parameter recover;
  cd /u01/app/oracle/flash_recovery_area/USERDB/archivelog
  ##USERDB是数据库实例名
  

• 退出sql编辑模式

  exit
  

• rman操作日志

  rman
  

  RMAN> connect target sys/sys_passwd
  
  crosscheck backup; 
  
  delete obsolete; 
  
  delete expired backup; 
  
  crosscheck archivelog all; 
  
  delete noprompt archivelog all; 
  

  host;   //退出rman
  

---

归档日志定时清理

• 新建归档日志脚本

  [oracle@localhost ~]$ vim /home/oracle/del_arch.sh
  #!/bin/bash
  echo "----------------------------------------`date`---------------------------------------"
  source ~/.bash_profile
  rman target / <<EOF
  crosscheck archivelog all;
  delete noprompt expired archivelog all;
  delete noprompt archivelog all completed before 'sysdate-7';
  EOF
  echo -e "\n"
  echo "------------------------------------ FINISHED ------------------------------------"
  

• 将脚本添加到定时任务当中

  [oracle@localhost ~]$ crontab -e
  * 1 * * *  sh /home/oracle/del_arc.sh