Friday, May 5, 2017

File #... added to control file as 'UNNAMED000...'

On your standby database if you see a datafile with the name similar to ‘UNNAMED000’, it means that you have STANDBY_FILE_MANAGEMENT parameter set to auto and managed recovery process tried to create a new datafile (after you already added this file on primary database), but it could not create file because of some reason. I faced this error because my ASM diskgroup was full. Following were the entries in my alert log file of my standby database after adding a datafile in my primary database using command ‘ALTER TABLESPACE my_tablespace ADD DATAFILE ‘+DATA/datafile06.dbf’ SIZE 20g’
WARNING: File being created with same name as in Primary
Existing file may be overwritten
Errors in file /u01/app/oracle/diag/rdbms/mydb/mydb/trace/mydb_mrp0_12030.trc:
ORA-01119: error in creating database file '+DATA/datafile06.dbf'
ORA-17502: ksfdcre:4 Failed to create file +DATA/datafile06.dbf'
ORA-15041: diskgroup "DATA" space exhausted
File #19 added to control file as 'UNNAMED00019'.
Originally created as:
'+DATA/datafile06.dbf'
Recovery was unable to create the file as:
'+DATA/datafile06.dbf'
Errors with log /arch/mydb/archive/MYDB0001_0000004740_0874013687.ARC
MRP0: Background Media Recovery terminated with error 1274
Errors in file /u01/app/oracle/diag/rdbms/mydb/mydb/trace/mydb_mrp0_12030.trc:
ORA-01274: cannot add datafile '+DATA/datafile01.dbf' - file could not be created

MRP (media recovery process) also stopped after this error as recovery could not continue after this error. Trying to start media recovery would return following error
Errors in file /u01/app/oracle/diag/rdbms/mydb/mydb/trace/mydb_dbw0_11195.trc:
ORA-01186: file 19 failed verification tests
ORA-01157: cannot identify/lock data file 19 - see DBWR trace file
ORA-01111: name for data file 19 is unknown - rename to correct file
ORA-01110: data file 19: '/u01/app/oracle/product/11203/db1/dbs/UNNAMED00019'
File 19 not verified due to error ORA-01157
Media Recovery failed with error 1111

V$datafile view in standby database showed file name as follows
SQL> select name from v$datafile';

NAME
--------------------------------------------------------------------------------



/u01/app/oracle/product/11203/db1/dbs/UNNAMED00019

19 rows selected.


There may be different reasons for file not being created in standby database and to solve this issue, you would need to find out what caused this datafile creation operation failure and resolve the issue first. As explained above that in my scenario my ASM diskgroup was left with less than 20G space that caused this failure. To resolve this issue, I added space in my ASM diskgroup so that I have enough space for adding the new datafile, and then executed following steps.

--Set standby_file_management to manual
SQL> alter system set standby_file_management=MANUAL scope=memory;

System altered

-- Re-Create affected datafile as follows
SQL> alter database create datafile '/u01/app/oracle/product/11203/db1/dbs/UNNAMED00019' as '+DATA/datafile06.dbf';

Database altered.

--Set standby_file_management back to auto
SQL> alter system set standby_file_management=auto scope=memory;

System altered

-- Now we can see our datafile listed under v$datafile
SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------



+DATA/datafile06.dbf

19 rows selected.

SQL>

-- Start Managed Recovery
SQL> alter database recover managed standby database using current logfile disconnect;
 



No comments: