ORA-19809: limit exceeded for recovery files

OS환경 : Oracle Linux 7.6 (64bit)

 

DB 환경 : Oracle Database 19.10.0.0 Active Data Guard

 

에러 : ORA-19809: limit exceeded for recovery files

standby db 의 alert log 에 발생하는 에러 메세지

alert log 확인(standby db)

$ vi alert.log
2023-06-16T08:10:16.138090+02:00
Errors in file /oraLog/diag/rdbms/teststb/teststb/trace/teststb_tt00_69528.trc:
ORA-19809: limit exceeded for recovery files
ORA-19804: cannot reclaim 21475885056 bytes disk space from 21474836480 bytes limit
2023-06-16T08:10:17.246351+02:00
Errors in file /oraLog/diag/rdbms/teststb/teststb/trace/teststb_mz00_106898.trc:
ORA-01110: data file 922: '/oracle/19c/dbs/UNNAMED00922'
ORA-01565: error in identifying file '/oracle/19c/dbs/UNNAMED00922'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 7

 

 

adg gap 확인

SQL> select name, value from v$dataguard_stats
 
NAME                             VALUE
-------------------------------- ----------------------------------------------------------------
transport lag                    +00 00:00:00
apply lag                        +00 04:47:59
apply finish time                +00 01:28:14.186
estimated startup time           20

 

 

해결 방법 : primary db와 standby db에서 차이나는 datafile 확인 후 수동 생성

primary db와 standby db의 현재 datafile 확인

SQL> 
select file_id, file_name
from dba_data_files
order by 1 desc;
 
primary db
FILE_ID  FILE_NAME
--------- ---------------------------------------------
922    +DATA/TEST/DATAFILE/imsi_tbl_idx01.691.1142503627
921    +DATA/TEST/DATAFILE/imsi_tbl_data01.690.1138110979
 
standby db
FILE_ID  FILE_NAME
--------- ---------------------------------------------
921    +DATA/teststb/DATAFILE/imsi_tbl_data01.12787.1139396553
920    +DATA/teststb/DATAFILE/ees_spc_hist_data01.12786.1139396553

 

 

차이나는 데이터파일 크기 확인(primary db)

SQL> 
select file_id, round(bytes/1024/1024) mb 
from dba_data_files 
where file_name = '+DATA/TEST/DATAFILE/imsi_tbl_idx01.691.1142503627';
 
FILE_ID  MB
-----  ------
922    10500

 

 

standby_file_management 파라미터 manual 로 변경(standby db)

SQL> alter system set standby_file_management = 'MANUAL';
 
System altered.

 

 

수동 데이터파일 추가(standby db)

SQL> alter database create datafile 922 as '+DATA' size 10500m;
 
Database altered.

 

 

standby_file_management 파라미터 auto 로 변경(standby db)

SQL> alter system set standby_file_management = 'AUTO';
 
System altered.

 

 

mrp 프로세스 내려가있는 경우 mrp 기동(standby db)

SQL> alter database recover managed standby database using current logfile disconnect from session;
 
또는
 
SQL> alter database recover managed standby database disconnect from session;
 
Database altered.

 

 

adg gap 확인

SQL> select name, value from v$dataguard_stats
 
NAME                             VALUE
-------------------------------- ----------------------------------------------------------------
transport lag                    +00 00:00:00
apply lag                        +00 04:10:59
apply finish time                +00 01:27:34.247
estimated startup time           20

gap이 4시간 있지만 빠르게 줄어듬

 

 

원인 : primary db와 standby db에서 차이나는 datafile 갯수 문제

primary db와 standby db에서 각각 가지고 있는 datafile의 갯수가 달라서 발생한 문제

기본적으로는 primary db에서 특정 tbs에 datafile을 추가할 경우 자동으로 datafile이 standby db에도 생성되지만

가끔 fra영역이 가득차는 등의 이유로 datafile이 자동으로 추가되지 못하는 경우가 발생함

이때 본문과 같이 조치해주어야함

 

 

참조 : 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