Oracle 6 min readMay 29, 2025

Rolling Forward Standby When a Datafile Is Added to Primary

Reference: Oracle MOS DocID 1531031.1

Problem Statement

A new tablespace or datafile is added to the primary database. The physical standby cannot receive it because:

MRP is in a wait state — standby waiting for a datafile it has never seen
STANDBY_FILE_MANAGEMENT=MANUAL was set — standby did not auto-create the file
Network link between primary and standby was down when the file was added
The new file resides on a path unavailable on the standby

💡

Check alert log on standby for errors like: ORA-01111: name for datafile N is unknown or MRP0: Background Media Recovery terminated with error 1110

Step 1 — Identify the Missing Datafiles

sql

-- On PRIMARY: find recently added datafiles by file# or creation time
SELECT file#, name, creation_time, bytes/1024/1024 size_MB
FROM v$datafile
ORDER BY creation_time DESC;

-- On STANDBY: compare — files present on primary but missing on standby
-- Run on standby and compare file# list against primary output
SELECT file#, name FROM v$datafile ORDER BY file#;

-- Check standby alert log for the exact file numbers causing issues
-- grep -i "datafile" $ORACLE_BASE/diag/rdbms/<db>/<sid>/trace/alert*.log

Step 2 — Stop MRP on Standby

sql

-- On STANDBY
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

ALTER SYSTEM SET standby_file_management=MANUAL SCOPE=BOTH SID='*';

Step 3 — Backup the Missing Datafiles on Primary

On primary, backup only the new datafiles by file number:

bash

# On PRIMARY
rman target /

sql

-- Replace 219, 220, 221 with your actual missing file numbers
BACKUP DATAFILE 219, 220, 221
  FORMAT '/oracle/backup/new_files/ForStandby_%U'
  TAG 'NEW_DATAFILE_FORSTANDBY';

Step 4 — Transfer Backup to Standby

bash

# SCP from primary to standby
scp /oracle/backup/new_files/ForStandby_*     oracle@standby-server:/oracle/backup/new_files/

Step 5 — Catalog the Backup on Standby

bash

# On STANDBY
rman target /

sql

CATALOG START WITH '/oracle/backup/new_files/' NOPROMPT;

Step 6 — Restore the Datafiles to ASM (or Filesystem)

sql

-- Restore and place directly into ASM diskgroup
RUN {
  SET NEWNAME FOR DATAFILE 219 TO '+STANDBY_DATA';
  SET NEWNAME FOR DATAFILE 220 TO '+STANDBY_DATA';
  SET NEWNAME FOR DATAFILE 221 TO '+STANDBY_DATA';
  RESTORE DATAFILE 219, 220, 221;
}

💡

Replace +STANDBY_DATA with your standby ASM diskgroup name. If not using ASM, specify the full filesystem path instead.

Step 7 — Catalog New ASM Location & Switch

sql

-- Catalog the newly restored files from ASM
CATALOG START WITH '+STANDBY_DATA/<db_unique_name>/datafile/' NOPROMPT;

-- Switch the standby control file to use the new file locations
SWITCH DATABASE TO COPY;

Step 8 — Re-enable File Management & Restart MRP

sql

-- Re-enable automatic standby file management
ALTER SYSTEM SET standby_file_management=AUTO SCOPE=BOTH SID='*';

-- Start MRP
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

Step 9 — Verify MRP Is Applying

sql

-- Confirm MRP is running and the new datafile is no longer blocking
SELECT process, status, thread#, sequence#
FROM v$managed_standby;

-- Confirm the datafile is now known to standby
SELECT file#, name, status FROM v$datafile WHERE file# IN (219, 220, 221);

-- Check for any remaining archive gap
SELECT thread#, low_sequence#, high_sequence# FROM v$archive_gap;

Prevention — Set STANDBY_FILE_MANAGEMENT=AUTO

The root cause of this issue is often STANDBY_FILE_MANAGEMENT=MANUAL or network disruption at the time of file creation. To prevent it:

sql

-- On PRIMARY and STANDBY — keep this AUTO
ALTER SYSTEM SET standby_file_management=AUTO SCOPE=BOTH SID='*';

-- Verify
SHOW PARAMETER standby_file_management;

💡

With AUTO, when a new datafile is created on primary, Oracle automatically creates a placeholder on the standby and MRP writes data to it as archived logs are applied.

Alternative — Create Placeholder Manually on Standby

If you prefer not to restore from backup for small files:

sql

-- On STANDBY: manually create the placeholder datafile
-- Oracle will write the real data as MRP applies archive logs
ALTER DATABASE CREATE DATAFILE
  '/path/on/standby/unknown_219.dbf'   -- the path showing in alert log
AS '+STANDBY_DATA';                     -- actual target location

All postsOracle · Standby · RMAN · Datafile · MOS 1531031.1