[Oracle] MRP0: Background Media Recovery terminated with error 1111

1. 문제 발생 상황

• 상황: Primary DB에서 테스트를 위해 신규 테이블스페이스를 생성함.
• 증상: Standby DB에서 로그 적용(Redo Apply)이 중단됨.
• 에러 코드:
  • ORA-01111: 데이터 파일 이름이 알 수 없음(unknown)으로 표시됨.
  • ORA-01274: 데이터 파일 생성 시 오류로 인해 복구 모드가 종료됨.

DGMGRL> show database orcl_stby

Database - orcl_stby
  Role:               PHYSICAL STANDBY
  Intended State:     APPLY-ON
  Transport Lag:      0 seconds (computed 0 seconds ago)
  Apply Lag:          2 days 14 hours 7 minutes 57 seconds (computed 0 seconds ago)
  Average Apply Rate: 4.00 KByte/s
  Real Time Query:    OFF
  Instance(s):
    orcl

  Database Error(s):
    ORA-16766: Redo Apply is stopped

  Database Warning(s):
    ORA-16853: apply lag has exceeded specified threshold

Database Status:
ERROR

DGMGRL> enable database orcl_stby

Error: ORA-16688: command cannot be issued on a disabled member

SQL> SELECT timestamp, message 
		FROM v$dataguard_status 
		WHERE severity IN ('Error', 'Fatal') 
		ORDER BY timestamp DESC;
        
TIMESTAM MESSAGE
-------- ------------------------------------------------------------
26/02/17 MRP0: Background Media Recovery terminated with error 1111
26/02/17 MRP0: Background Media Recovery terminated with error 1111
26/02/17 MRP0: Background Media Recovery terminated with error 1111
26/02/17 MRP0: Background Media Recovery terminated with error 1111
26/02/17 MRP0: Background Media Recovery terminated with error 1111
26/02/17 MRP0: Background Media Recovery terminated with error 1111
26/02/17 MRP0: Background Media Recovery terminated with error 1111
26/02/17 MRP0: Background Media Recovery terminated with error 1111
26/02/17 MRP0: Background Media Recovery terminated with error 1111
26/01/23 MRP0: Background Media Recovery terminated with error 1274

 

 

2. 원인 분석

Primary에서 파일이 생성될 때, Standby 서버는 다음 두 가지 중 하나가 충족되지 않으면 파일을 어디에 만들지 몰라 방황

1. db_file_name_convert 파라미터가 정확히 설정되어 있는가?
2. standby_file_management가 AUTO로 설정되어 있는가?

내 사례: Primary에서는 /u01/app/oracle/oradata/orcl/tbs_test01.dbf 경로에 파일을 만들었지만, Standby에서는 경로를 찾지 못해 $ORACLE_HOME/dbs/UNNAMED00013이라는 임시 이름으로 멈춰버린 상태 (14번 파일은 목록에 없는 것으로 보아 아직 13번 복구 단계에 걸려있는 것 같음)

-- Primary
SQL> show parameter standby_file_management

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
standby_file_management              string      MANUAL

-- Standby
SQL> show parameter standby_file

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
standby_file_management              string      AUTO

-- Primary
SQL> select file#, name from v$datafile;
FILE#		NAME
---------- --------------------------------------------------
         1 /u01/app/oracle/oradata/orcl/system01.dbf
         3 /u01/app/oracle/oradata/orcl/sysaux01.dbf
         4 /u01/app/oracle/oradata/orcl/undotbs01.dbf
         5 /u01/app/oracle/oradata/orcl/pdbseed/system01.dbf
         6 /u01/app/oracle/oradata/orcl/pdbseed/sysaux01.dbf
         7 /u01/app/oracle/oradata/orcl/users01.dbf
         8 /u01/app/oracle/oradata/orcl/pdbseed/undotbs01.dbf
         9 /u01/app/oracle/oradata/orcl/pdb/system01.dbf
        10 /u01/app/oracle/oradata/orcl/pdb/sysaux01.dbf
        11 /u01/app/oracle/oradata/orcl/pdb/undotbs01.dbf
        12 /u01/app/oracle/oradata/orcl/pdb/users01.dbf
        13 /u01/app/oracle/oradata/orcl/tbs_test01.dbf
        14 /u01/app/oracle/oradata/orcl/pdb/test_tbs01.dbf

13 rows selected.


-- Standby
SQL> select file#, name from v$datafile;
FILE#		NAME
---------- --------------------------------------------------
         1 /u01/app/oracle/oradata/orcl/system01.dbf
         3 /u01/app/oracle/oradata/orcl/sysaux01.dbf
         4 /u01/app/oracle/oradata/orcl/undotbs01.dbf
         5 /u01/app/oracle/oradata/orcl/pdbseed/system01.dbf
         6 /u01/app/oracle/oradata/orcl/pdbseed/sysaux01.dbf
         7 /u01/app/oracle/oradata/orcl/users01.dbf
         8 /u01/app/oracle/oradata/orcl/pdbseed/undotbs01.dbf
         9 /u01/app/oracle/oradata/orcl/pdb/system01.dbf
        10 /u01/app/oracle/oradata/orcl/pdb/sysaux01.dbf
        11 /u01/app/oracle/oradata/orcl/pdb/undotbs01.dbf
        12 /u01/app/oracle/oradata/orcl/pdb/users01.dbf
        13 /u01/app/oracle/product/19c/dbhome_1/dbs/UNNAMED00

12 rows selected.

 

 

3. 해결 방법

 현재 잘못된 UNNAMED 파일을 정식 경로로 생성

SQL> alter database create datafile
	'/u01/app/oracle/product/19c/dbhome_1/dbs/UNNAMED00013'
	as '/u01/app/oracle/oradata/orcl/tbs_test01.dbf';

 

파일 자동 관리 기능 활성화

SQL> alter system set standby_file_management=AUTO;

 

MRP 재기동

SQL> alter database managed standby database disconnect from session;