Oracle闪回区满
一台老的测试AIX服务器,没人理过,最近一看Oracle闪回满了。清理了下。Version: Oracle 10gR2 for AIX
现象:
?
1 2 3 4 5 6 7 | 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' |
报这个错误是因为闪回区满了:
?
1 2 3 4 5 6 7 8 | -- 扩大闪回区空间 SQL> alter system set db_recovery_file_dest_size=3g scope=both; System altered. SQL> alter database open; Database altered. |
闪回区相关信息查询:
?
1 2 3 4 5 6 7 8 | --查看归档模式 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; |
释放空间:
?
1 2 3 4 5 6 7 8 9 10 11 12 13 | --删除备份 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'; |
除了在闪回区删文件,或扩空间的方式,还可以通过将归档指定到其它目录的方式来做。
?
1 2 3 4 5 6 7 8 9 10 11 | 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>alter system switch logfile;
但我测试时,因为粗心,授权时没注意,导致执行时发现被卡住了,检查v$archive_dest,
发现LOG_ARCHIVE_DEST_1状态为ERROR. 这个是目录权限问题引起的。
?
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 | 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. |
重新授权一下目录所属用户和权限:
?
1 2 | # chown oracle:oinstall /nfsdisk/oraarc # chmod -R 777 /nfsdisk/oraarc |
此时就可以归档成功了。
MAIL: xcl_168@aliyun.com
BLOG: http://blog.csdn.net/xcl168
分享到: