某日,登录oracle数据库,得到如下错误提示:
使用sys 用户登录
查看arciv log位置
1: $ sqlplus /"as sysdba"
2:
3: SQL*Plus: Release 10.2.0.4.0 - Production on Mon Jun 20 16:47:51 2011
4:
5: Copyright (c) 1982, 2007, Oracle. All Rights Reserved.
6:
7:
8: Connected to:
9: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
10: With the Partitioning, OLAP, Data Mining and Real Application Testing options
11:
12: SQL> show parameter log_archive_dest;
13:
14: NAME TYPE VALUE
15: ------------------------------------ ----------- ------------------------------
16: log_archive_dest string
17: log_archive_dest_1 string
18: log_archive_dest_10 string
19: log_archive_dest_2 string
20: log_archive_dest_3 string
21: log_archive_dest_4 string
22: log_archive_dest_5 string
23: log_archive_dest_6 string
24: log_archive_dest_7 string
25: log_archive_dest_8 string
26: log_archive_dest_9 string
27:
28: NAME TYPE VALUE
29: ------------------------------------ ----------- ------------------------------
30: log_archive_dest_state_1 string enable
31: log_archive_dest_state_10 string enable
32: log_archive_dest_state_2 string enable
33: log_archive_dest_state_3 string enable
34: log_archive_dest_state_4 string enable
35: log_archive_dest_state_5 string enable
36: log_archive_dest_state_6 string enable
37: log_archive_dest_state_7 string enable
38: log_archive_dest_state_8 string enable
39: log_archive_dest_state_9 string enable
40: SQL>
一般VALUE为空时，可以用archive log list;检查一下归档目录和log sequence
1: SQL> archive log list;
2: Database log mode Archive Mode
3: Automatic archival Enabled
4: Archive destination USE_DB_RECOVERY_FILE_DEST
5: Oldest online log sequence 125
6: Next log sequence to archive 125
7: Current log sequence
检查flash recovery area的使用情况
1: SQL> select * from V$FLASH_RECOVERY_AREA_USAGE;
2:
3: FILE_TYPE PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES
4: ------------ ------------------ ------------------------- ---------------
5: CONTROLFILE 0 0 0
6: ONLINELOG 0 0 0
7: ARCHIVELOG 99.72 0 123
8: BACKUPPIECE 0 0 0
9: IMAGECOPY 0 0 0
10: FLASHBACKLOG 0 0 0
11:
12: 6 rows selected.
可以看到archivelog已经占用到99.72%了.
计算flash recovery area已经占用的空间
1:
2: SQL> select sum(percent_space_used)*3/100 from v$flash_recovery_area_usage;
3:
4: SUM(PERCENT_SPACE_USED)*3/100
5: -----------------------------
6: 2.9916
找到recovery目录, show parameter recover
1:
2: SQL> show parameter recover;
3:
4: NAME TYPE VALUE
5: ------------------------------------ ----------- ------------------------------
6: db_recovery_file_dest string /oracle/flash_recovery_area
7: db_recovery_file_dest_size big integer 5G
8: recovery_parallelism integer 0
上述结果告诉我们，归档位置用的是默认值，放在flash_recovery_area下(db_recovery_file_dest目录=/oracle/flash_recovery_area)
1: cd /oracle/flash_recovery_area/
删除一些旧的arc文件.
1: $ pwd
2: /oracle/flash_recovery_area/JCT/archivelog
3: $find . -mtime +30|xargs rm -rf
删除归档日志后,还需要使用RMAN维护控制文件.
1: $ rman target sys/sys
2:
3: Recovery Manager: Release 10.2.0.4.0 - Production on Tue Jun 21 16:14:17 2011
4:
5: Copyright (c) 1982, 2007, Oracle. All rights reserved.
6:
7: connected to target database: JCT (DBID=786899877)
8: -------检查无用归档文件---------
9: RMAN> crosscheck archivelog all;
10: ------删除过期的归档------------
11: RMAN> delete expired archivelog all;
再看使用率 已经下来了.
1: sql>select * from V$FLASH_RECOVERY_AREA_USAGE;
2: FILE_TYPE PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES
3: ------------ ------------------ ------------------------- ---------------
4: CONTROLFILE 0 0 0
5: ONLINELOG 0 0 0
6: ARCHIVELOG 38.94 0 48
7: BACKUPPIECE 0 0 0
8: IMAGECOPY 0 0 0
9: FLASHBACKLOG 0 0 0
10:
11: 6 rows selected.
再次登录,就可以登录了.
多吃东西多喝水，
少玩游戏少睡觉！ -------
望月飞鱼(珏石头)
