一台老的测试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 thread1:
'/opt/oracle/software/oradata/orcl/redo03.log'
报这个错误是因为闪回区满了：
?
1
2
3
4
5
6
7
8
-- 扩大闪回区空间
SQL> alter system set db_recovery_file_dest_size=3gscope=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 modeArchiveMode
Automatic archivalEnabled
Archive destination/nfsdisk/oraarc
Oldest online log sequence161
Next log sequence to archive161
Current log sequence163
设完成后，测试一下:
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 formata20
SQL> col dest_name formata20
SQL> select dest_id,dest_name,STATUS,DESTINATIONfrom v$archive_dest;
DEST_ID DEST_NAMESTATUSDESTINATION
---------- -------------------------------------- --------------------
1 LOG_ARCHIVE_DEST_1ERROR/nfsdisk/oraarc
2 LOG_ARCHIVE_DEST_2INACTIVE
3 LOG_ARCHIVE_DEST_3INACTIVE
4 LOG_ARCHIVE_DEST_4INACTIVE
5 LOG_ARCHIVE_DEST_5INACTIVE
6 LOG_ARCHIVE_DEST_6INACTIVE
7 LOG_ARCHIVE_DEST_7INACTIVE
8 LOG_ARCHIVE_DEST_8INACTIVE
9 LOG_ARCHIVE_DEST_9INACTIVE
10 LOG_ARCHIVE_DEST_10INACTIVE
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
