首页 » ORACLE 9i-23ai » V$RECOVERY_FILE_DEST与V$FLASH_RECOVERY_AREA_USAGE显示不一致

V$RECOVERY_FILE_DEST与V$FLASH_RECOVERY_AREA_USAGE显示不一致

最近一个客户的数据库归档空间满(Oracle 11.2.0.4 RAC),导致数据库挂起,无法连接,sys登录时提示ORA-00257: Archiver Error, 确认数据库使用的是flash recovery area,从oracle 10g R2提供了V$FLASH_RECOVERY_AREA_USAGE 可以查看flash recovery area中每类文件使用的比例,但是加起来不足10%,且当前flash recover area size已经达2TB, 因为无法远程,临时更改数据库归档路径为具体ASM DISKGROUP不再使用FRA, 这里简单记录一下排查方法
问题现象:

分析思路
1, 检查 当前归档路径

SQL> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     66354
Next log sequence to archive   66359
Current log sequence           66359

SQL> show parameter arch

PARAMETER_NAME                                               TYPE        VALUE
------------------------------------------------------------ ----------- ----------------------------------------------------------------------------------------------------
archive_lag_target                                           integer     600
log_archive_config                                           string
log_archive_dest                                             string
log_archive_dest_1                                           string      location=use_db_recovery_file_dest


2, 检查使用比例
V$RECOVERY_FILE_DEST : To find out the current location, disk quota, space in use, space reclaimable by deleting files, and total number of files in the fast recovery area.
V$FLASH_RECOVERY_AREA_USAGE : To find out the percentage of the total disk quota used by different types of files. Also, you can determine how much space for each type of file can be reclaimed by deleting files that are obsolete, redundant, or already backed up to tape.

SQL> @st V$FLASH_RECOVERY_AREA_USAGE

FILE_TYPE               PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES          CON_ID
----------------------- ------------------ ------------------------- --------------- ---------------
CONTROL FILE                           .17                         0               1               0
REDO LOG                                 0                         0               0               0
ARCHIVED LOG                             0                         0               1               0
BACKUP PIECE                         14.04                     10.31               8               0
IMAGE COPY                              .1                        .1               1               0
FLASHBACK LOG                            0                         0               0               0
FOREIGN ARCHIVED LOG                     0                         0               0               0
AUXILIARY DATAFILE COPY                  0                         0               0               0

8 rows selected.

SQL>select * from V$RECOVERY_FILE_DEST

NAME                                         SPACE_LIMIT      SPACE_USED SPACE_RECLAIMABLE NUMBER_OF_FILES          CON_ID
---------------------------------------- --------------- --------------- ----------------- --------------- ---------------
+DATA01                                      53687091200      8500805632        5591007232             305               0

SQL>  SELECT (100 - sum(percent_space_used)) + sum(percent_space_reclaimable)FROM v$flash_recovery_area_usage;

(100-SUM(PERCENT_SPACE_USED))+SUM(PERCENT_SPACE_RECLAIMABLE)
------------------------------------------------------------
                                                        96.1

3, 为了防止归档路径满,可以再指定一个“备胎”, 当dest_1不可用时,自动切换到dest_2

log_archive_dest_1='LOCATION=use_db_recovery_file_dest NOREOPEN ALTERNATE=LOG_ARCHIVE_DEST_2'
log_archive_dest_2='LOCATION=+DATA'
log_archive_dest_state_1='enable'
log_archive_dest_state_2='alternate'
db_recovery_file_dest='+ARCH'
db_recovery_file_dest_size=2G

4,检查FRA路径与大小, 如果是备份空间不足,建议增加dest大小

SQL> show parameter recover

PARAMETER_NAME                                               TYPE        VALUE
------------------------------------------------------------ ----------- ----------------------------------------------------------------------------------------------------
db_recovery_file_dest                                        string      +DATA01
db_recovery_file_dest_size                                   big integer 50G
db_unrecoverable_scn_tracking                                boolean     TRUE
recovery_parallelism                                         integer     0
remote_recovery_file_dest                                    string

SQL> alter system set db_recovery_file_dest_size=<>G ;

5,查看ASM DG是否实际大于FRA大小

SELECT * FROM V$ASM_DISKGROUP;

6, 清理FRA空间
对于11g之前Bug 4911954. 可以手动刷新可回收空间

sql>alter session set events 'immediate trace name kra_options level 1';
sql> execute dbms_backup_restore.refreshagedfiles;

对于12c之前Bug 14227959. 可以手动刷新可回收空间

SQL> exec dbms_backup_restore.refreshagedfiles ;

有些时候是因为RMAN 没有更新

rman target /

RMAN>
RUN {
report obsolete orphan;
report obsolete;
crosscheck backup;
crosscheck copy;
crosscheck backup of controlfile;
crosscheck archivelog all;

delete noprompt expired backup;
delete noprompt expired archivelog all;
delete noprompt expired backup of controlfile;
delete force noprompt expired copy;
delete force noprompt obsolete orphan;
delete force noprompt obsolete;
}

有时FRA更换了空间,原FRA的文件依旧存在,可以从以下VIEW中查找IS_RECOVERY_DEST_FILE=’YES’字段

v$controlfile
v$log_file
v$archived_log
v$backup_piece
v$datafile_copy
v$flashback_database_logfile


当FRA空间不足时,会在db alert中告警

ORA-19809: limit exceeded for recovery files
ORA-19804: cannot reclaim nnnnn bytes disk space from mmmmm limit

-- or --
ORA-19815: WARNING: db_recovery_file_dest_size of 3221225472 bytes is 100.00% used, and has 0 remaining bytes available.

或应急改为数据所在的ASM DG或其他可用空间

alter system set log_archive_dest_1='LOCATION=+DATA' 

V$RECOVERY_FILE_DEST 定义

SELECT  rdi.location,
      rdi.slimit,
      (rdi.sused + rdi.scfile),
      rdi.srecl+client.srecl,
      rdi.fcnt, rdi.con_id
FROM x$kccrdi rdi, 
  ( SELECT  sum(recl) srecl
  FROM 
    ( SELECT  0 recl
    FROM dual union SELECT  to_number(fblogreclsiz) recl
    FROM x$krfblog
    WHERE rownum = 1 union SELECT  sum(case
      WHEN ceilasm = 1
        AND rlnam LIKE '+%'
    THEN   ceil(((rlbct*rlbsz)+1)/1048576)*1048576
      ELSE rlbct*rlbsz end) recl
    FROM x$kccrl, 
      ( SELECT  /*+ no_merge */ ceilasm
      FROM x$krasga)
      WHERE bitand(rlfl2, 64) = 64
          AND (bitand(rlfl2, 4096) = 4096
          OR bitand(rlfl2, 8192) = 8192)
          AND rlnam is NOT null)) client

Note:
主要依赖x$kccrdi。

不建议db_recovery_file_dest指向有其它文件的目录,尤其不建议是存储数据文件的目录,下面演示为什么?

SQL> show parameter recover

NAME                                 TYPE                              VALUE
------------------------------------ --------------------------------- ------------------------------
db_recovery_file_dest                string
db_recovery_file_dest_size           big integer                       0
db_unrecoverable_scn_tracking        boolean                           TRUE
recovery_parallelism                 integer                           0
remote_recovery_file_dest            string
SQL> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /u01/app/oracle/product/19.0.0/db_1/dbs/arch
Oldest online log sequence     6
Next log sequence to archive   7
Current log sequence           7

SQL> select * from x$kccrdi
no rows selected

[grid@19c1 ~]$ asmcmd lsdg
State    Type    Rebal  Sector  Logical_Sector  Block       AU  Total_MB  Free_MB  Req_mir_free_MB  Usable_file_MB  Offline_disks  Voting_files  Name
MOUNTED  EXTERN  N         512             512   4096  4194304      9760     9464                0            9464              0             N  ARCH/
MOUNTED  NORMAL  N         512             512   4096  4194304     29280    20548             9760            5394              0             N  DATA/
MOUNTED  NORMAL  N         512             512   4096  4194304     58560     7620            19520           -5950              0             Y  OCR/


SQL> alter system set db_recovery_file_dest='+DATA';
alter system set db_recovery_file_dest='+DATA'
*
ERROR at line 1:
ORA-02097: parameter cannot be modified because specified value is invalid
ORA-19802: cannot use DB_RECOVERY_FILE_DEST without DB_RECOVERY_FILE_DEST_SIZE

SQL> alter system set DB_RECOVERY_FILE_DEST_SIZE=10g;

System altered.

SQL> alter system set db_recovery_file_dest='+DATA';

System altered.

SQL> @st x$kccrdi
ADDR                   INDX    INST_ID     CON_ID LOCATION       SLIMIT      SUSED     SCFILE       FCNT      SRECL   SYSAVAIL OMRTIME                   FLAGS     ALHEAD     DCHEAD     BPHEAD
---------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- -------------------- ---------- ---------- ---------- ----------
00007F8571387450          0          1          0 +DATA      1.0737E+10          0          0          0          0          0                              25          0          0          0

  1* select * from V$RECOVERY_FILE_DEST

NAME                           SPACE_LIMIT SPACE_USED SPACE_RECLAIMABLE NUMBER_OF_FILES     CON_ID
------------------------------ ----------- ---------- ----------------- --------------- ----------
+DATA                           1.0737E+10          0                 0               0          0


SQL> @st V$RECOVERY_AREA_USAGE

FILE_TYPE                                                             PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES     CON_ID
--------------------------------------------------------------------- ------------------ ------------------------- --------------- ----------
CONTROL FILE                                                                           0                         0               0          0
REDO LOG                                                                               0                         0               0          0
ARCHIVED LOG                                                                           0                         0               0          0
BACKUP PIECE                                                                           0                         0               0          0
IMAGE COPY                                                                             0                         0               0          0
FLASHBACK LOG                                                                          0                         0               0          0
FOREIGN ARCHIVED LOG                                                                   0                         0               0          0
AUXILIARY DATAFILE COPY                                                                0                         0               0          0

-- 配置db_recovery_file_dest目录后,V$RECOVERY_AREA_USAGE和V$RECOVERY_FILE_DEST有记录,但USED基本为0, 一直没有刷新,我们重启实例. 

SQL> @st V$RECOVERY_FILE_DEST

NAME                           SPACE_LIMIT SPACE_USED SPACE_RECLAIMABLE NUMBER_OF_FILES     CON_ID
------------------------------ ----------- ---------- ----------------- --------------- ----------
+DATA                           1.0737E+10   80740352                 0               4          0


SQL>  @st V$RECOVERY_AREA_USAGE
FILE_TYPE                                                             PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES     CON_ID
--------------------------------------------------------------------- ------------------ ------------------------- --------------- ----------
CONTROL FILE                                                                         .19                         0               1          0
REDO LOG                                                                               0                         0               0          0
ARCHIVED LOG                                                                         .55                         0               3          0
BACKUP PIECE                                                                           0                         0               0          0
IMAGE COPY                                                                             0                         0               0          0
FLASHBACK LOG                                                                          0                         0               0          0
FOREIGN ARCHIVED LOG                                                                   0                         0               0          0
AUXILIARY DATAFILE COPY                                                                0                         0               0          0

SQL> select count(*) from v$archived_log where IS_RECOVERY_DEST_FILE='YES';
  COUNT(*)
----------
         3

SQL> select count(*) from v$controlfile where IS_RECOVERY_DEST_FILE='YES';
  COUNT(*)
----------
         1

Note:
V$RECOVERY_FILE_DEST刷新不是很及时,如果增加文件可能不会实时统计到, 重启实例后有刷新,如果在实例启动周期内修改过db_recovery_file_dest路径,可能V$RECOVERY_FILE_DEST显示不一致。

打赏

, ,

对不起,这篇文章暂时关闭评论。