ORA-19815: WARNING: db_recovery_file_dest_size of n bytes is 100.00% used

OS환경 : Oracle Linux 7.6 (64bit)

 

DB 환경 : Oracle Database 19.10.0.0 Active Data Guard

 

에러 : ORA-19815: WARNING: db_recovery_file_dest_size of n bytes is 100.00% used, ...

19c adg 환경의 alert log에 발생하는 에러

alert log 확인

$ vi alert.log
2023-06-16T02:43:00.938970+02:00
Errors in file /oraLog/diag/rdbms/teststb/teststb/trace/teststb_tt00_69528.trc:
ORA-19815: WARNING: db_recovery_file_dest_size of 21474836480 bytes is 100.00% used, and has 0 remaining bytes available.
2023-06-16T02:43:00.939022+02:00
************************************************************************
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.
************************************************************************
Reclaimable space = 0
Available space = 0
Disk space limit = 21474836480
Total space reserved for files under creation(in bytes) is 0

 

 

해결 방법 : fra 영역 증가 또는 fra 에 쌓인 아카이브 로그 정리

fra 사용량 확인

SQL>
set lines 200 pages 1000
col name for a10
select name, 
round(space_limit/1024/1024/1024, 2) "space_limit(gb)", 
round(space_used/1024/1024/1024, 2) "space_used(gb)", 
round(space_reclaimable/1024/1024/1024, 2) "space_reclaimable(gb)",
decode(nvl(space_used, 0),0, 0, ceil((space_used/space_limit) * 100)) "pct_used",
number_of_files
from v$recovery_file_dest;
 
NAME       SPACE_LIMIT(GB) SPACE_USED(GB) SPACE_RECLAIMABLE(GB)   PCT_USED NUMBER_OF_FILES
---------- --------------- -------------- --------------------- ---------- ---------------
+RECO                 19.3           19.1                   .02         58            1443

 

 

asm 여유공간 확인

SQL> 
set lines 200 pages 1000
col name for a10
select name, round(usable_file_mb/1024,1) usable_file_tb, 
round(total_mb/1024,1) total_tb, round(free_mb/1024,1) free_tb, 100-round(free_mb/total_mb*100) "usage(%)",
round(((free_mb - required_mirror_free_mb))/1024/1024,1) usable_calc_tb,
state, type, group_number
from v$asm_diskgroup;
 
NAME       USABLE_FILE_GB   TOTAL_GB    FREE_GB   usage(%) USABLE_CALC_GB STATE       TYPE   GROUP_NUMBER
---------- -------------- ---------- ---------- ---------- -------------- ----------- ------ ------------
DATA              795.5     2484.5     1605.8         35           1591 CONNECTED   NORMAL            1
RECO               69.4      209.7        140         33          138.7 CONNECTED   NORMAL            2

 

 

fra 파라미터 확인

SQL> show parameter db_reco
 
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest                string      +RECO
db_recovery_file_dest_size           big integer 20G

 

 

fra 영역 증가 또는 fra 에 쌓인 아카이브로그 정리

fra 영역 증가

SQL> alter system set db_recovery_file_dest_size=30G;
 
System altered.

이후 정상화됨

 

 

또는

fra 에 쌓인 아카이브로그 정리

$ rman target /
RMAN >
crosscheck archivelog all;
delete noprompt archivelog all completed before 'SYSDATE-1';
delete noprompt expired archivelog all;
 
테스트 환경이라 백업이 필요없는 경우
delete noprompt archivelog all;

이후 정상화됨

 

 

fra 사용량 확인

SQL>
set lines 200 pages 1000
col name for a10
select name, 
round(space_limit/1024/1024/1024, 2) "space_limit(gb)", 
round(space_used/1024/1024/1024, 2) "space_used(gb)", 
round(space_reclaimable/1024/1024/1024, 2) "space_reclaimable(gb)",
decode(nvl(space_used, 0),0, 0, ceil((space_used/space_limit) * 100)) "pct_used",
number_of_files
from v$recovery_file_dest;
 
NAME       SPACE_LIMIT(GB) SPACE_USED(GB) SPACE_RECLAIMABLE(GB)   PCT_USED NUMBER_OF_FILES
---------- --------------- -------------- --------------------- ---------- ---------------
+RECO               29.3           16.9                   .02         58            1443

 

 

원인 : fra 영역(db_recovery_file_dest) full

fra 영역이 가득차서 발생한 문제로 이 영역의 파일을 정리해주면 해결됨

이 영역이 가득차는 경우는 아카이브로그로 인해 full 차는 경우가 대부분임

 

 

참조 : https://positivemh.tistory.com/909

ORA-19809: limit exceeded for recovery files

https://forums.oracle.com/ords/apexds/post/ora-01153-an-incompatible-media-recovery-is-active-1849