首页 » ORACLE 9i-23c » 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显示不一致。
微信扫一扫，打赏作者吧～
对不起，这篇文章暂时关闭评论。