一台老的測試AIX服務器,沒人理過,最近一看Oracle閃回滿了。清理了下。
Version: Oracle 10gR2 for AIX
現象:
SQL> alter database open; alter database open * ERROR at line 1: ORA-16014: log 3 sequence# 157 not archived, no available destinations ORA-00312: online log 3 thread 1: '/opt/oracle/software/oradata/orcl/redo03.log'報這個錯誤是因為閃回區滿了:
-- 擴大閃回區空間 SQL> alter system set db_recovery_file_dest_size=3g scope=both; System altered. SQL> alter database open; Database altered.閃回區相關信息查詢:
--查看歸檔模式 SQL> archive log list; --歸檔空間使用率 SQL> select * from v$flash_recovery_area_usage; --閃回區所占用的空間 SQL> select sum(percent_space_used)*3/100 from v$flash_recovery_area_usage;釋放空間:
--刪除備份 crosscheck backupset; delete noprompt expired backupset; delete obsolete; delete noprompt expired backup; delete noprompt expired copy; --清掉歸檔 --清掉過期的歸檔 crosscheck archivelog all; delete noprompt expired archivelog all; --要還不夠,強制刪除掉指定天數前的歸檔 delete noprompt archivelog all completed before 'sysdate-7';
SQL> alter system set log_archive_dest_1='location=/nfsdisk/oraarc' scope=both ; System altered. SQL> archive log list; Database log mode Archive Mode Automatic archival Enabled Archive destination /nfsdisk/oraarc Oldest online log sequence 161 Next log sequence to archive 161 Current log sequence 163設完成後,測試一下:
SQL> col DESTINATION format a20 SQL> col dest_name format a20 SQL> select dest_id,dest_name,STATUS,DESTINATION from v$archive_dest; DEST_ID DEST_NAME STATUS DESTINATION ---------- -------------------- ------------------ -------------------- 1 LOG_ARCHIVE_DEST_1 ERROR /nfsdisk/oraarc 2 LOG_ARCHIVE_DEST_2 INACTIVE 3 LOG_ARCHIVE_DEST_3 INACTIVE 4 LOG_ARCHIVE_DEST_4 INACTIVE 5 LOG_ARCHIVE_DEST_5 INACTIVE 6 LOG_ARCHIVE_DEST_6 INACTIVE 7 LOG_ARCHIVE_DEST_7 INACTIVE 8 LOG_ARCHIVE_DEST_8 INACTIVE 9 LOG_ARCHIVE_DEST_9 INACTIVE 10 LOG_ARCHIVE_DEST_10 INACTIVE 10 rows selected.重新授權一下目錄所屬用戶和權限:
# chown oracle:oinstall /nfsdisk/oraarc # chmod -R 777 /nfsdisk/oraarc此時就可以歸檔成功了。
MAIL: [email protected]
BLOG: http://blog.csdn.net/xcl168