使用PLSQL Developer工具登录一个测试的数据库时报错提示无法分配区间
然后登录到Linux操作系统上使用df -h
[root@test149 ~]# df -h
Filesystem Size Used Avail Use% Mounted on
/dev/mapper/vg_test149-lv_oracle_data
42G 39G 15M 100% /
tmpfs 3.9G 2.0G 2.0G 50% /dev/shm
/dev/sda1 240M 73M 155M 32% /boot
查看数据库日志
/u01/app/oracle/diag/rdbms/test/TEST/trace/alert_TEST.log
找到如下错误信息，前面还有相关报错
Sat Dec 03 06:00:33 2016
Thread 1 advanced to log sequence 126 (LGWR switch)
Current log# 3 seq# 126 mem# 0: /u02/app/oracle/oradata/TEST/onlinelog/o1_mf_3_d29s7qhd_.log
Current log# 3 seq# 126 mem# 1: /u01/app/oracle/fast_recovery_area/TEST/onlinelog/o1_mf_3_d29s7qn6_.log
Sat Dec 03 06:00:33 2016
Errors in file /u01/app/oracle/diag/rdbms/test/TEST/trace/TEST_arc3_7431.trc:
ORA-19815: WARNING: db_recovery_file_dest_size of 4781506560 bytes is 100.00% used, and has 0 remaining bytes available.
Sat Dec 03 06:00:33 2016
************************************************************************
You have following choices to free up space from recovery area:
1. Consider changing RMAN RETENTION POLICY. If you are using Data Guard,
then consider changing RMAN ARCHIVELOG DELETION POLICY.
2. Back up files to tertiary device such as tape using RMAN
BACKUP RECOVERY AREA command.
3. Add disk space and increase db_recovery_file_dest_size parameter to
reflect the new space.
4. Delete unnecessary files using RMAN DELETE command. If an operating
system command was used to delete files, then use RMAN CROSSCHECK and
DELETE EXPIRED commands.
************************************************************************
Sat Dec 03 06:00:33 2016
Errors in file /u01/app/oracle/diag/rdbms/test/TEST/trace/TEST_arc3_7431.trc:
ORA-19809: limit exceeded for recovery files
ORA-19804: cannot reclaim 36886016 bytes disk space from 4781506560 limit
ARC3: Error 19809 Creating archive log file to '/u01/app/oracle/fast_recovery_area/TEST/archivelog/2016_12_03/o1_mf_1_125_%u_.arc'
Sat Dec 03 06:00:33 2016
Errors in file /u01/app/oracle/diag/rdbms/test/TEST/trace/TEST_arc0_7425.trc:
ORA-19815: WARNING: db_recovery_file_dest_size of 4781506560 bytes is 100.00% used, and has 0 remaining bytes available.
根据报错信息是闪回恢复区空间不够db_recovery_file_dest_size
而整个磁盘也没有空间分配，磁盘没有可以清除的文件
使用rman检查记录的归档日志信息
RMAN> list archivelog all;
归档日志信息显示有很多已经物理位置删除，即在闪回恢复区内只使用200M，
但由于未及时清除记录的归档日志信息导致闪回恢复区被认为空间不够，
而且物理磁盘也过小，闪回恢复区配置4G,但未分配到指定大小4G，
所以先从闪回恢复区内物理删除（也可以拷贝走），然后清除归档日志信息，方法如下：
RMAN> crosscheck archivelog all;
--检查失效的归档信息
RMAN> delete expired archivelog all;
--删除失效的归档信息
除了删除（拷贝走）以外还可以增大闪回恢复区内（要有空间可以增大）。
使用到的命令
[oracle@test149 ~]$ df -h
Filesystem Size Used Avail Use% Mounted on
/dev/mapper/vg_test149-lv_oracle_data
42G 38G 1.2G 98% /
tmpfs 3.9G 2.0G 1.9G 52% /dev/shm
/dev/sda1 240M 73M 155M 32% /boot
[oracle@test149 ~]$ du -sh ./*
4.0K ./Desktop
4.0K ./Documents
4.0K ./Downloads
4.0K ./Music
4.0K ./Pictures
4.0K ./Public
4.0K ./Templates
4.0K ./Videos
使用pfile启动数据库
startup pfile='/u01/app/oracle/product/12.1.0.2/db_1/dbs/initTEST.ora’