ORA-01274: cannot add data file that was originally created as: In this paragraph i will discuss about ORA-01274 database error. When you getting ORA-01274 error follow below steps to resolve the issue.
Problem:
After adding a datafile in primary database, recovery process in standby database stopped with below error.
Primary Side:
After adding a datafile in primary database.
SQL> select file_name, tablespace_name from dba_data_files where tablespace_name='TEST' order by tablespace_name;
FILE_NAME TABLESPACE_NAME
-------------------------------------------------------------------------------- ------------------------------
+DATA/ORCL/FED16F56330B66C7E0539A2BA8C0354A/DATAFILE/test.280.1141344343 TEST
SQL>
SQL> alter tablespace test add datafile '+DATA' size 100m autoextend on;
Tablespace altered.
SQL> select file_name, tablespace_name from dba_data_files where tablespace_name='TEST' order by tablespace_name;
FILE_NAME TABLESPACE_NAME
-------------------------------------------------------------------------------- ------------------------------
+DATA/ORCL/FED16F56330B66C7E0539A2BA8C0354A/DATAFILE/test.280.1141344343 TEST
+DATA/ORCL/FED16F56330B66C7E0539A2BA8C0354A/DATAFILE/test.281.1141344627 TEST
SQL>
Standby Side:
Getting error in alert log file at Standby side.
2023-07-05T00:10:26.631166+05:30
(3):File #19 added to control file as 'UNNAMED00019' because
(3):the parameter STANDBY_FILE_MANAGEMENT is set to MANUAL
(3):The file should be manually created to continue.
PR00 (PID:14657): MRP0: Background Media Recovery terminated with error 1274
2023-07-05T00:10:26.677290+05:30
Errors in file /u01/app/oracle/diag/rdbms/orcldr/ORCL/trace/ORCL_pr00_14657.trc:
ORA-01274: cannot add data file that was originally created as '+DATA/ORCL/FED16F56330B66C7E0539A2BA8C0354A/DATAFILE/test.281.1141344627'
PR00 (PID:14657): Managed Standby Recovery not using Real Time Apply
Recovery interrupted!
IM on ADG: Start of Empty Journal
IM on ADG: End of Empty Journal
Recovered data files to a consistent state at change 3925843
stopping change tracking
2023-07-05T00:10:26.900803+05:30
Errors in file /u01/app/oracle/diag/rdbms/orcldr/ORCL/trace/ORCL_pr00_14657.trc:
ORA-01274: cannot add data file that was originally created as '+DATA/ORCL/FED16F56330B66C7E0539A2BA8C0354A/DATAFILE/test.281.1141344627'
2023-07-05T00:10:26.921904+05:30
Background Media Recovery process shutdown (ORCL)
2023-07-05T00:10:26.992064+05:30
Errors in file /u01/app/oracle/diag/rdbms/orcldr/ORCL/trace/ORCL_mz00_15059.trc:
ORA-01110: data file 19: '/u01/app/oracle/product/19c/dbhome_1/dbs/UNNAMED00019'
ORA-01565: error in identifying file '/u01/app/oracle/product/19c/dbhome_1/dbs/UNNAMED00019'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 7
Checker run found 1 new persistent data failures
Solution:
This issue occours, 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
Check the standby_file_management parameter value in standby database.
SQL> show parameter standby_file_management
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
standby_file_management string MANUAL
SQL>
Cancel the recovery process:
SQL> recover managed standby database disconnect from session;
Media recovery complete.
SQL>Check the files, which caused the issue in standby database:
SQL> select file#, error, name from v$datafile_header where ERROR='FILE MISSING';
FILE# ERROR NAME
---------- ----------------------------------------------------------------- ------------------------------------------------------------------------------------------
19 FILE MISSING
SQL> select name from v$datafile where file#=19;
NAME
------------------------------------------------------------------------------------------
/u01/app/oracle/product/19c/dbhome_1/dbs/UNNAMED00019
SQL>Recreate the missing datafiles in standby database:
If you have OMF files, then follow below steps. In my case I have OMF.
SQL> alter database create datafile '/u01/app/oracle/product/19c/dbhome_1/dbs/UNNAMED00019' as new;
Database altered.
SQL> select name from v$datafile where file#=19;
NAME
------------------------------------------------------------------------------------------
+DATA/ORCLDR/FED16F56330B66C7E0539A2BA8C0354A/DATAFILE/test.280.1141345837
SQL>If you dont have OMF files, then get the exact datafile name from primary and recreate.
----- Primary Side -----
SQL> select name from v$datafile where file#=19;
NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/ORCLDR/datafile/test_01.dbf
----- Recreate datafile in standby -----
SQL> alter database create datafile '/u01/app/oracle/product/19c/dbhome_1/dbs/UNNAMED00019' as '/u01/app/oracle/oradata/ORCLDR/datafile/test_01.dbf';
Database altered.
SQL> select name from v$datafile where file#=19;
NAME
------------------------------------------------------------------------------------------
/u01/app/oracle/oradata/ORCLDR/datafile/test_01.dbf
Set standby_file_management parameter value:
Set standby_file_management from MANUAL to AUTO, to avoid similar issue in future:
SQL> alter system set standby_file_management='AUTO' scope=both sid='*';
System altered.
SQL>
SQL> show parameter standby_file_management
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
standby_file_management string AUTO
SQL>
Start the Recovery Process:
SQL> alter database recover managed standby database disconnect from session;
Database altered.
SQL> select process,status,sequence# from v$managed_standby where PROCESS='MRP0';
PROCESS STATUS SEQUENCE#
--------- ------------ ----------
MRP0 APPLYING_LOG 111
SQL> !ps -ef | grep mrp
oracle 17529 1 0 00:49 ? 00:00:00 ora_mrp0_ORCL
oracle 17780 13812 0 00:53 pts/0 00:00:00 /bin/bash -c ps -ef | grep mrp
oracle 17782 17780 0 00:53 pts/0 00:00:00 grep mrp
SQL>
Check the archive sync status:
SQL> set lines 400 pages 400
SQL> SELECT ARCH.THREAD# "Thread", ARCH.SEQUENCE# "Last Sequence Received", APPL.SEQUENCE# "Last Sequence Applied", (ARCH.SEQUENCE# - APPL.SEQUENCE#) "Difference"
FROM
(SELECT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)) ARCH,
(SELECT THREAD# ,SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$LOG_HISTORY GROUP BY THREAD#)) APPL
WHERE
ARCH.THREAD# = APPL.THREAD#
ORDER BY 1;
Thread Last Sequence Received Last Sequence Applied Difference
---------- ---------------------- --------------------- ----------
1 114 114 0
SQL>
oracle 19c 2 node rac to single instance standby database setup