PROBLEM:

After adding a datafile in primary database, recovery process in standby stopped with below error.

— Error in alert log of standby database

Fri Jan 15 09:56:46 2021
Errors in file /mnt/ERPPROD/erpprod/erpproddb/121/admin/ERPPROD_ebsdbprd01/diag/rdbms/erpprod_stby/ERPPROD_STBY/trace/ERPPROD_STBY_pr00_8013.trc:
ORA-01111: name for data file 720 is unknown – rename to correct file
ORA-01110: data file 720: ‘/mnt/ERPPROD/erpprod/erpproddb/12102/dbs/UNNAMED00720’
ORA-01157: cannot identify/lock data file 720 – see DBWR trace file
ORA-01111: name for data file 720 is unknown – rename to correct file
ORA-01110: data file 720: ‘/mnt/ERPPROD/erpprod/erpproddb/12102/dbs/UNNAMED00720’
Managed Standby Recovery not using Real Time Apply
Fri Jan 15 09:56:46 2021
Recovery Slave PR00 previously exited with exception 1111
Fri Jan 15 09:56:46 2021
Errors in file /mnt/ERPPROD/erpprod/erpproddb/121/admin/ERPPROD_ebsdbprd01/diag/rdbms/erpprod_stby/ERPPROD_STBY/trace/ERPPROD_STBY_mrp0_8011.trc:
ORA-01111: name for data file 720 is unknown – rename to correct file
ORA-01110: data file 720: ‘/mnt/ERPPROD/erpprod/erpproddb/12102/dbs/UNNAMED00720’
ORA-01157: cannot identify/lock data file 720 – see DBWR trace file
ORA-01111: name for data file 720 is unknown – rename to correct file
ORA-01110: data file 720: ‘/mnt/ERPPROD/erpprod/erpproddb/12102/dbs/UNNAMED00720′
Fri Jan 15 09:56:46 2021
MRP0: Background Media Recovery process shutdown (ERPPROD_STBY)

SOLUTION:

This issue happens, when the standby_file_management parameter is set to MANUAL in standby database. So when a datafile is added in primary database, standby database is unable to process it. To fix it follow the below process

1. Check the standby_file_management parameter in the standby database.

SQL> show parameter standby

NAME TYPE VALUE
———————————— ———– ——————————
standby_archive_dest string ?/dbs/arch

standby_file_management string MANUAL

2. Cancel the recovery:

SQL> recover managed standby database disconnect from session;

Media recovery complete.

3. Check the file, which caused the issue:[ STANDBY]

SQL> select file#, error, name from v$datafile_header where ERROR=’FILE MISSING’;

FILE# ERROR
———- —————————————————————–
NAME
——————————————————————————–
720 FILE MISSING

SQL> select name from v$datafile where file#=5;

NAME
——————————————————————————–
/mnt/ERPPROD/erpprod/erpproddb/12102/dbs/UNNAMED00720

Check the datafile name using file# value from Primary:

NAME
——————————————————————————–
/mnt/ERPPROD/erpprod/erpproddata/apex01.dbf

4. Recreate the datafile as below [ STANDBY ]

SQL> alter database create datafile ‘/mnt/ERPPROD/erpprod/erpproddb/12102/dbs/UNNAMED00720’ as ‘/mnt/ERPPROD/erpprod/erpproddata/apex01.dbf’ ;

database altered.

5. Set standby_file_management to AUTO from standby database, to avoid similar issue in future

alter system set standby_file_management=AUTO scope=both;

6. Start recovery in standby database

alter database recover managed standby database disconnect from session;

Now MRP process will do apply the archivelog.

Recent Posts

Start typing and press Enter to search