Introduction
The STANDBY_FILE_MANAGEMENT parameter controls whether datafile additions and deletions on the primary are automatically replicated to the standby. It accepts two values:
- MANUAL: disables automatic standby file management. Datafile changes on the primary are not automatically reflected on the standby.
- AUTO: enables automatic standby file management. This is the recommended setting for almost all Data Guard configurations.
Scenario
On the standby database, STANDBY_FILE_MANAGEMENT was set to MANUAL, either by mistake or for some other reason. After that, new datafiles were added on the primary, and the primary and standby fell out of sync as a result. This guide walks through diagnosing and fixing exactly this situation.
Prerequisites
- Oracle Data Guard should already be configured.
- To reproduce this scenario for testing, set
STANDBY_FILE_MANAGEMENTto MANUAL on the standby and add datafiles on the primary.
Environment Used in This Guide
| Server | Primary | Standby |
|---|---|---|
| Hostname | Source | Target |
| IP | 192.168.80.51 | 192.168.80.111 |
| OS | OEL 9 | OEL 9 |
| SID | ORCLDC | ORCLDR |
| Service Name | ORCLDC | ORCLDR |
Step 1: Check Sync Status and Compare Schemas
Start by confirming the apply gap and comparing the datafile list on both sides. This is what actually reveals the problem, the primary will show datafiles that don't exist on the standby.
At Primary:
SQL> select name,db_unique_name,database_role,open_mode from v$database;
NAME DB_UNIQUE_NAME DATABASE_ROLE OPEN_MODE
--------- ------------------------------ ---------------- --------------------
ORCL ORCLDC PRIMARY READ WRITE
SQL> set lines 200 pages 300
SQL> alter session set nls_date_format= 'DD-MON-YYYY HH24:MI:SS';
Session altered.
SQL> select d.db_unique_name,d.database_role, a.thread#, b.last_seq, a.applied_seq, a.last_app_timestamp, b.last_seq - a.applied_seq ARC_DIFF
FROM
(select thread#, MAX(sequence#) applied_seq, MAX(next_time) last_app_timestamp from gv$archived_log where applied='YES' group by thread#) a,
(select thread#, MAX(sequence#) last_seq from gv$archived_log group by thread#) b,
(select db_unique_name,database_role from v$database) d where a.thread#=b.thread#;
DB_UNIQUE_NAME DATABASE_ROLE THREAD# LAST_SEQ APPLIED_SEQ LAST_APP_TIMESTAMP ARC_DIFF
------------------------------ ---------------- ---------- ---------- ----------- -------------------- ----------
ORCLDC PRIMARY 1 241 236 11-APR-2025 12:19:56 5
SQL>
[oracle@source ~]$ rman target /
Recovery Manager: Release 19.0.0.0.0 - Production on Fri Apr 11 13:52:05 2025
Version 19.24.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORCL (DBID=1718803653)
RMAN> report schema;
using target database control file instead of recovery catalog
Report of database schema for database with db_unique_name ORCLDC
List of Permanent Datafiles
===========================
File Size(MB) Tablespace RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1 1100 SYSTEM YES /u01/app/oracle/oradata/ORCL/system01.dbf
3 920 SYSAUX NO /u01/app/oracle/oradata/ORCL/sysaux01.dbf
4 710 UNDOTBS1 YES /u01/app/oracle/oradata/ORCL/undotbs01.dbf
5 450 PDB$SEED:SYSTEM NO /u01/app/oracle/oradata/ORCL/pdbseed/system01.dbf
6 430 PDB$SEED:SYSAUX NO /u01/app/oracle/oradata/ORCL/pdbseed/sysaux01.dbf
7 5 USERS NO /u01/app/oracle/oradata/ORCL/users01.dbf
8 230 PDB$SEED:UNDOTBS1 NO /u01/app/oracle/oradata/ORCL/pdbseed/undotbs01.dbf
9 460 ORCLPDB:SYSTEM YES /u01/app/oracle/oradata/ORCL/orclpdb/system01.dbf
10 490 ORCLPDB:SYSAUX NO /u01/app/oracle/oradata/ORCL/orclpdb/sysaux01.dbf
11 230 ORCLPDB:UNDOTBS1 YES /u01/app/oracle/oradata/ORCL/orclpdb/undotbs01.dbf
12 5 ORCLPDB:USERS NO /u01/app/oracle/oradata/ORCL/orclpdb/users01.dbf
13 1024 ORCLPDB:TEST NO /u01/app/oracle/oradata/ORCL/orclpdb/test01.dbf
14 500 ORCLPDB:TEST NO /u01/app/oracle/oradata/ORCL/orclpdb/test02.dbf
15 500 ORCLPDB:USERS NO /u01/app/oracle/oradata/ORCL/orclpdb/users02.dbf
16 500 ORCLPDB:TEST NO /u01/app/oracle/oradata/ORCL/orclpdb/test03.dbf
List of Temporary Files
=======================
File Size(MB) Tablespace Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1 181 TEMP 32767 /u01/app/oracle/oradata/ORCL/temp01.dbf
2 174 PDB$SEED:TEMP 32767 /u01/app/oracle/oradata/ORCL/pdbseed/temp012025-01-22_00-17-43-687-AM.dbf
3 174 ORCLPDB:TEMP 32767 /u01/app/oracle/oradata/ORCL/orclpdb/temp01.dbf
RMAN>
At Standby:
[oracle@target ~]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Fri Apr 11 13:48:53 2025
Version 19.24.0.0.0
Copyright (c) 1982, 2024, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.24.0.0.0
SQL> select name,db_unique_name,database_role,open_mode from v$database;
NAME DB_UNIQUE_NAME DATABASE_ROLE OPEN_MODE
--------- ------------------------------ ---------------- --------------------
ORCL ORCLDR PHYSICAL STANDBY MOUNTED
SQL> set lines 200 pages 300
SQL> alter session set nls_date_format= 'DD-MON-YYYY HH24:MI:SS';
Session altered.
SQL> select d.db_unique_name,d.database_role, a.thread#, b.last_seq, a.applied_seq, b.last_seq - a.applied_seq ARC_DIFF, a.last_app_timestamp,
round((sysdate - a.last_app_timestamp)*24*60,2) Gap_in_Mins, round((sysdate - a.last_app_timestamp)*24*60*60,2) Gap_in_Seconds
FROM
(select thread#, MAX(sequence#) applied_seq, MAX(next_time) last_app_timestamp from gv$archived_log where REGISTRAR='RFS' and applied='YES' group by thread#) a,
(select thread#, MAX(sequence#) last_seq from gv$archived_log group by thread#) b,
(select db_unique_name,database_role from v$database) d where a.thread#=b.thread#;
DB_UNIQUE_NAME DATABASE_ROLE THREAD# LAST_SEQ APPLIED_SEQ ARC_DIFF LAST_APP_TIMESTAMP GAP_IN_MINS GAP_IN_SECONDS
------------------------------ ---------------- ---------- ---------- ----------- ---------- -------------------- ----------- --------------
ORCLDR PHYSICAL STANDBY 1 241 236 5 11-APR-2025 12:19:56 89.25 5355
SQL>
[oracle@target ~]$ rman target /
Recovery Manager: Release 19.0.0.0.0 - Production on Fri Apr 11 13:52:16 2025
Version 19.24.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORCL (DBID=1718803653, not open)
RMAN> report schema;
using target database control file instead of recovery catalog
RMAN-06139: warning: control file is not current for REPORT SCHEMA
Report of database schema for database with db_unique_name ORCLDR
List of Permanent Datafiles
===========================
File Size(MB) Tablespace RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1 1100 SYSTEM *** /u01/app/oracle/oradata/ORCLDR/system01.dbf
3 920 SYSAUX *** /u01/app/oracle/oradata/ORCLDR/sysaux01.dbf
4 710 UNDOTBS1 *** /u01/app/oracle/oradata/ORCLDR/undotbs01.dbf
5 450 PDB$SEED:SYSTEM *** /u01/app/oracle/oradata/ORCLDR/pdbseed/system01.dbf
6 430 PDB$SEED:SYSAUX *** /u01/app/oracle/oradata/ORCLDR/pdbseed/sysaux01.dbf
7 5 USERS *** /u01/app/oracle/oradata/ORCLDR/users01.dbf
8 230 PDB$SEED:UNDOTBS1 *** /u01/app/oracle/oradata/ORCLDR/pdbseed/undotbs01.dbf
9 460 ORCLPDB:SYSTEM *** /u01/app/oracle/oradata/ORCLDR/orclpdb/system01.dbf
10 490 ORCLPDB:SYSAUX *** /u01/app/oracle/oradata/ORCLDR/orclpdb/sysaux01.dbf
11 230 ORCLPDB:UNDOTBS1 *** /u01/app/oracle/oradata/ORCLDR/orclpdb/undotbs01.dbf
12 5 ORCLPDB:USERS *** /u01/app/oracle/oradata/ORCLDR/orclpdb/users01.dbf
13 1024 ORCLPDB:TEST *** /u01/app/oracle/oradata/ORCLDR/orclpdb/test01.dbf
14 0 ORCLPDB:TEST *** /u01/app/oracle/product/19.0.0/dbhome_1/dbs/UNNAMED00014
List of Temporary Files
=======================
File Size(MB) Tablespace Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1 32 TEMP 32767 /u01/app/oracle/oradata/ORCLDR/temp01.dbf
2 36 PDB$SEED:TEMP 32767 /u01/app/oracle/oradata/ORCLDR/pdbseed/temp012025-01-22_00-17-43-687-AM.dbf
3 174 ORCLPDB:TEMP 32767 /u01/app/oracle/oradata/ORCLDR/orclpdb/temp01.dbf
RMAN>
Comparing the two schema reports, datafiles 14, 15, and 16 exist on the primary but the standby only shows a placeholder entry, UNNAMED00014, with size 0. This confirms the standby couldn't automatically create the corresponding files.
Step 2: Check MRP and the Standby Alert Log
Managed recovery isn't running at all (no rows selected), which is the direct symptom of the problem. The alert log explains exactly why.
SQL> select process, status, thread#, sequence#, block# from v$managed_standby where process like '%MRP%';
no rows selected
SQL>
DR Alert log:
2025-04-11T12:23:02.622396+05:30
ORCLPDB(3):File #14 added to control file as 'UNNAMED00014' because
ORCLPDB(3):the parameter STANDBY_FILE_MANAGEMENT is set to MANUAL
ORCLPDB(3):The file should be manually created to continue.
PR00 (PID:11496): MRP0: Background Media Recovery terminated with error 1274
2025-04-11T12:23:02.696283+05:30
Errors in file /u01/app/oracle/diag/rdbms/orcldr/orcldr/trace/orcldr_pr00_11496.trc:
ORA-01274: cannot add data file that was originally created as '/u01/app/oracle/oradata/ORCL/orclpdb/test02.dbf'
PR00 (PID:11496): Managed Standby Recovery not using Real Time Apply
Recovery interrupted!
Recovery stopped due to failure in applying recovery marker (opcode 17.30).
Datafiles are recovered to a consistent state at change 4262537 but controlfile could be ahead of datafiles.
Stopping change tracking
2025-04-11T12:23:03.327811+05:30
Errors in file /u01/app/oracle/diag/rdbms/orcldr/orcldr/trace/orcldr_pr00_11496.trc:
ORA-01274: cannot add data file that was originally created as '/u01/app/oracle/oradata/ORCL/orclpdb/test02.dbf'
2025-04-11T12:23:03.478947+05:30
Background Media Recovery process shutdown (orcldr)
2025-04-11T12:34:56.947874+05:30
The alert log makes the root cause explicit: because STANDBY_FILE_MANAGEMENT is MANUAL, the redo apply process registered the new file in the control file as a placeholder but couldn't physically create it, and MRP stopped entirely at that point rather than skipping the file and continuing.
Step 3: Manually Create the Missing Datafile
Since automatic creation is disabled, we create the datafile by hand using the placeholder name reported in the error, pointing it at the correct real file path.
For a normal file system:
alter database create datafile '/u01/app/oracle/product/19.0.0/dbhome_1/dbs/UNNAMED00014' as 'file_name_with_location';
For ASM or OMF:
alter database create datafile '/u01/app/oracle/product/19.0.0/dbhome_1/dbs/UNNAMED00014' as '+DATA';
Note: if the missing datafile belongs to a PDB, switch to that PDB's container before running the command, running it against the wrong container won't resolve the placeholder.
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED MOUNTED
3 ORCLPDB MOUNTED
SQL> alter session set container=ORCLPDB;
Session altered.
SQL> alter database create datafile '/u01/app/oracle/product/19.0.0/dbhome_1/dbs/UNNAMED00014' as '/u01/app/oracle/oradata/ORCLDR/orclpdb/test02.dbf';
Database altered.
Note this resolves one file at a time. If multiple datafiles were added on the primary while the parameter was MANUAL, you'd need to repeat this for each corresponding UNNAMED placeholder before recovery can proceed past all of them.
Step 4: Set Standby File Management to AUTO and Restart MRP
With the missing file created, fix the root cause by switching the parameter to AUTO so this doesn't happen again, then restart managed recovery.
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED MOUNTED
3 ORCLPDB MOUNTED
SQL> show parameter standby_file_management
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
standby_file_management string MANUAL
SQL> alter system set standby_file_management=AUTO;
System altered.
SQL> show parameter standby_file_management
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
standby_file_management string AUTO
SQL> alter database recover managed standby database disconnect from session;
Database altered.
SQL> select process,status,thread#,sequence#,block# from v$managed_standby where process like '%MRP%';
PROCESS STATUS THREAD# SEQUENCE# BLOCK#
--------- ------------ ---------- ---------- ----------
MRP0 APPLYING_LOG 1 237 452
SQL> /
PROCESS STATUS THREAD# SEQUENCE# BLOCK#
--------- ------------ ---------- ---------- ----------
MRP0 APPLYING_LOG 1 243 922
MRP is now running and actively advancing through sequences, confirming recovery resumed successfully.
Step 5: Confirm Sync on Both Sides
Switch a couple of logs on the primary and confirm the standby catches up completely, using the same verification queries from Step 1.
At Primary:
SQL> set lines 200 pages 300
SQL> alter session set nls_date_format= 'DD-MON-YYYY HH24:MI:SS';
Session altered.
SQL> select d.db_unique_name, d.database_role, a.thread#, b.last_seq, a.applied_seq, a.last_app_timestamp, b.last_seq - a.applied_seq ARC_DIFF
FROM
(select thread#, MAX(sequence#) applied_seq, MAX(next_time) last_app_timestamp from gv$archived_log where applied='YES' group by thread#) a,
(select thread#, MAX(sequence#) last_seq from gv$archived_log group by thread#) b,
(select db_unique_name, database_role from v$database) d
where a.thread# = b.thread#;
DB_UNIQUE_NAME DATABASE_ROLE THREAD# LAST_SEQ APPLIED_SEQ LAST_APP_TIMESTAMP ARC_DIFF
------------------------------ ---------------- ------- -------- ----------- -------------------- ----------
ORCLDC PRIMARY 1 243 243 11-APR-2025 14:04:57 0
SQL>
At Standby:
SQL> set lines 200 pages 300
SQL> alter session set nls_date_format= 'DD-MON-YYYY HH24:MI:SS';
Session altered.
SQL> select d.db_unique_name, d.database_role, a.thread#, b.last_seq, a.applied_seq,
b.last_seq - a.applied_seq ARC_DIFF, a.last_app_timestamp,
round((sysdate - a.last_app_timestamp)*24*60,2) Gap_in_Mins,
round((sysdate - a.last_app_timestamp)*24*60*60,2) Gap_in_Seconds
FROM
(select thread#, MAX(sequence#) applied_seq, MAX(next_time) last_app_timestamp
from gv$archived_log
where REGISTRAR='RFS' and applied='YES'
group by thread#) a,
(select thread#, MAX(sequence#) last_seq
from gv$archived_log
group by thread#) b,
(select db_unique_name, database_role from v$database) d
where a.thread# = b.thread#;
DB_UNIQUE_NAME DATABASE_ROLE THREAD# LAST_SEQ APPLIED_SEQ ARC_DIFF LAST_APP_TIMESTAMP GAP_IN_MINS GAP_IN_SECONDS
------------------------ ------------------- -------- ---------- ----------- ---------- --------------------- ------------- ---------------
ORCLDR PHYSICAL STANDBY 1 243 243 0 11-APR-2025 14:04:57 13.63 818
SQL>
ARC_DIFF is back to 0 on both sides, confirming the standby is fully caught up. With STANDBY_FILE_MANAGEMENT now set to AUTO, any future datafile additions on the primary will replicate automatically without needing this manual intervention again.
Thank you for reading!
I hope this content has been helpful to you. Your feedback and suggestions are always welcome. Feel free to leave a comment or reach out with any queries.
Abhishek Shrivastava
Oracle DBA with hands-on experience managing production Data Guard, RAC, GoldenGate, and APEX environments. I write practical, tested installation and troubleshooting guides based on real deployment work.
Comments
Post a Comment