重庆思庄Oracle、Redhat认证学习论坛

标题: ORA-19815 db_recovery_file_dest_size 使用率过高 [打印本页]

作者: denglj 时间: 2021-12-21 09:49
标题: ORA-19815 db_recovery_file_dest_size 使用率过高
启动数据库时报错ORA-19815，db_recovery_file_dest_size 使用率过高，如下：
Errors in file E:\ORACLE19C\ORACLE\diag\rdbms\orcl1\orcl1\trace\orcl1_m000_6652.trc:
ORA-19815: 警告: db_recovery_file_dest_size 字节 (共 8657043456 字节) 已使用 93.04%, 尚有 602363392 字节可用。
************************************************************************
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.

查看快速恢复区使用情况
SQL> select file_type,percent_space_used,percent_space_reclaimable,number_of_files from v$flash_recovery_area_usage;

FILE_TYPE             PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES
----------------------- ------------------ ------------------------- ---------------
CONTROL FILE                            0                      0             0
REDO LOG                               0                      0             0
ARCHIVED LOG                      95.17                      0          144
BACKUP PIECE                         .13                      0             1
IMAGE COPY                            0                      0             0
FLASHBACK LOG                         0                      0             0
FOREIGN ARCHIVED LOG                   0                      0             0
AUXILIARY DATAFILE COPY                0                      0             0

已选择 8 行。
发现快速恢复区中归档日志占用95.17%的空间.

在查看归档日志存放目录
SQL> show parameter db_recovery_file;

NAME                               TYPE       VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest             string    E:\oracle19c\oracle\fast_recov
                                             ery_area
db_recovery_file_dest_size          big integer 8256M

#确认到归档存放目录为快速恢复区

解决步骤：
1、手动删掉部分归档日志文件，再次查询使用情况
SQL> select file_type,percent_space_used,percent_space_reclaimable,number_of_files from v$flash_recovery_area_usage;

FILE_TYPE             PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES
----------------------- ------------------ ------------------------- ---------------
CONTROL FILE                            0                      0             0
REDO LOG                               0                      0             0
ARCHIVED LOG                      95.17                      0          144
BACKUP PIECE                         .13                      0             1
IMAGE COPY                            0                      0             0
FLASHBACK LOG                         0                      0             0
FOREIGN ARCHIVED LOG                   0                      0             0
AUXILIARY DATAFILE COPY                0                      0             0

已选择 8 行。
发现归档日志space_used还是95.17%，归档数仍然是144。

经查询资料确认到归档日志虽然被删除，但空间并没有得到释放。
需要通过rman释放空间，如下所示：
RMAN> crosscheck archivelog all;
RMAN> delete expired archivelog all;

再次查看
SQL> select file_type,percent_space_used,percent_space_reclaimable,number_of_files from v$flash_recovery_area_usage;

FILE_TYPE             PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES
----------------------- ------------------ ------------------------- ---------------
CONTROL FILE                            0                      0             0
REDO LOG                               0                      0             0
ARCHIVED LOG                      59.83                      0             80
BACKUP PIECE                         .13                      0             1
IMAGE COPY                            0                      0             0
FLASHBACK LOG                         0                      0             0
FOREIGN ARCHIVED LOG                   0                      0             0
AUXILIARY DATAFILE COPY                0                      0             0

已选择 8 行。

欢迎光临重庆思庄Oracle、Redhat认证学习论坛 (http://bbs.cqsztech.com/)