ORA-01274: cannot add data file & ORA-01136: specified size of file

Now writing about add datafile in prod database and dataguard (standby) databases.
This scenario, I added datafile prod database but in dataguard databases ‘standby_file_management’ parameter not set ‘auto’ see this problem to dataguard database alert log.

You can see likely errors in change datafile location file system to ASM.if you have this scenario, you can this steps.

-OFFLINE DATAFILE
-DROP DATAFILE
Afterly checking you can see datafile mode recover.
-CREATE DATAFILE must ASM.
-RECOVER DATAFILE
You can see datafile mode online.

Now, starting our problem scenario.

DG databases alert log.

PR00 (PID:7149): Media Recovery Log +DATA/TSTDR/ARCHIVELOG/2022_04_25/thread_1_seq_89820.440.1102939931
File #27 added to control file as 'UNNAMED00027' because
the parameter STANDBY_FILE_MANAGEMENT is set to MANUAL
The file should be manually created to continue.
PR00 (PID:7149): MRP0: Background Media Recovery terminated with error 1274
2022-04-25T12:35:35.301940-04:00
Errors in file /oracle/diag/rdbms/tstdr/TSTDR/trace/TSTDR_pr00_7149.trc:
ORA-01274: cannot add data file that was originally created as '+DATA/TST/DATAFILE/tst.9427.1102965281'
Recovery interrupted!
Recovery stopped due to failure in applying recovery marker (opcode 17.30).
Datafiles are recovered to a consistent state at change 1052638689721 but controlfile could be ahead of datafiles.
stopping change tracking
2022-04-25T12:35:35.812307-04:00
Errors in file /oracle/diag/rdbms/tstdr/TSTDR/trace/TSTDR_pr00_7149.trc:
ORA-01274: cannot add data file that was originally created as '+DATA/TST/DATAFILE/tst.9427.1102965281'
2022-04-25T12:35:35.941830-04:00

Checking ‘standby_file_management’ parameter

SQL> show parameter standby_file;

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

Checking like ‘UNNAMED’ datafile and other datafile path

SQL> select file#,name from v$datafile where name like '%UNNAMED%';

     FILE  #NAME
---------- ----------------------------------------------------------------------
     27    /oracle/product/18.13.0.0/db_1/dbs/UNNAMED00027


SQL> select file#,name from v$datafile;

     FILE  NAME
---------- ----------------------------------------------------------------------
         1 +DATA/tstdr/datafile/system.4918.1035

         2 +DATA/tstdr/datafile/sysaux.4934.10353

         3 +DATA/tstdr/datafile/undotbs1.4874.103535


     FILE  NAME
---------- ---------------------------------------------------------------------
         4 +DATA/tstdr/datafile/users.4899.1035357
		 .
         .
         .

        27 /oracle/product/18.13.0.0/db_1/dbs/UNNAMED00027

27 rows selected

Now, fix datafile location to ASM group.

SQL> alter database create datafile '/oracle/product/18.13.0.0/db_1/dbs/UNNAMED00027' as '+DATA';
alter database create datafile '/oracle/product/18.13.0.0/db_1/dbs/UNNAMED00027' as '+DATA'
*
ERROR at line 1:
ORA-01136: specified size of file 27 (12800 blocks) is less than original size
of 131072 blocks
ORA-01110: data file 27: '+DATA'

if you have this error, you can apply this steps.

SQL> show parameter db_block_size ;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_block_size                        integer     8192

How to find orginal size ?

(131072*8192) = 1073741824 /1024/1024/1024 = 1G

Change sql command and try again run.

SQL> alter database create datafile '/oracle/product/18.13.0.0/db_1/dbs/UNNAMED00027' as '+DATA' size 1G;

Database altered.

Starting standby in dataguard database.

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

Database altered.