Unnamed data file with standby_file_management=manual

Tried to solve the following problem the other day on my standby database:

File #7 added to control file as 'UNNAMED00007' because
the parameter STANDBY_FILE_MANAGEMENT is set to MANUAL
The file should be manually created to continue.
MRP0: Background Media Recovery terminated with error 1274
Fri Apr  8 10:00:22 2005
Some recovered datafiles maybe left media fuzzy
Media recovery may continue but open resetlogs may fail

I had done this before, but - couldn't remember how to do it. The same problem can occur when you are recovering your database and a data file has been added to it which was not part of your backup set.

So, here is the solution! I came close to it while looking up the Oracle SQL reference:

On the standby, use "ALTER DATABASE CREATE DATAFILE" as shown in this example:

1. RECOVER MANAGED STANDBY DATABASE CANCEL;
2. alter system set standby_file_management = 'manual';
3. select file#,name from v$datafile where name like '%UNNAMED%';
4. ALTER DATABASE CREATE DATAFILE 'unnamed datafile' AS 'proper filename';
   An example would be:
   
   alter database create datafile
   '/u01/app/oracle/product/11.2.0.2/dbhome_1/dbs/UNNAMED00005'
   as '/u02/oradata/STDBY/data007.ora';
   
   
5. alter database set standby_file_management = 'auto';

"Unnamed data file" in this conectxt is not the fully qualified file name, only UNNAMED00007.

Done! Enable recovery again:

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE [USING CURRENT LOGFILE]
2 DISCONNECT FROM SESSION;

If your database is on ASM, then please use the following syntax instead in step 2:

SQL> alter database create datafile 'unnamed datafile' as '+TargetDiskGroupName' size xM;

It's important to specify the size, or the command will fail. An example should clarify this:

SQL> ALTER DATABASE CREATE DATAFILE
  2   '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/UNNAMED00005'
  3   AS '+DATA' size 500M;