File creation issue in standby database and resolution

File creation issue in standby database and resolution:



ORA-01274: cannot add datafile '/u02/oradata/orcl/psindex_2.dbf' - file could not be created
Recovery interrupted!

Reason for this issue is may be in Primary database standby file management manual, or space in standby side mount point is fulled. these are the most frequently facing issue for file creation in standby when we add datafile in primary.

In this case standby file management is manual in primary side.
============================
SQL> show parameter standby

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
standby_archive_dest                 string      ?/dbs/arch
standby_file_management              string      MANUAL
SQL> exit

In standby:
===================================
file will be created as below with UNNAMED

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

  TS# NAME
 -----------  ----------------------------------
   94          /u01/app/oracle/product/11.2.0.4/dbhome_1/dbs/UNNAMED00127


Take the actual datafile name from standby alertlog file to create it.

Create the file as bellow.

SQL> alter system set standby_file_management=manual;

SQL> alter database create datafile '/u01/app/oracle/product/11.2.0.4/dbhome_1/dbs/UNNAMED00127' as '/u02/oradata/orcl/psindex_2.dbf';

Database altered.

If the FS is ASM then use below command.

alter database create datafile '/opt/oracle/product/12.2.0.1/dbhome_1/dbs/UNNAMED00096' as new;

SQL> alter system set standby_file_management=AUTO;

In Primary also make sure set standby_file_management as auto;

SQL> alter system set standby_file_management=AUTO;

System altered.