Oracle Managed Files (OMF) have many advantages, but the fact that such files could coexist in the same database with manually added (and named) ones, could sometimes lead to confusion. Situation is made worse by the fact, that there is no straightforward way (at least of which I'm aware of...or rather was - please check the comment of Mikhail Velikikh) to say if the file is Oracle managed or not. Oracle documentation seems to confirm this:
The database identifies an Oracle managed file based on its name.
To say the least, it is not very convenient way to detect such files. First of all it is error-prone, especially that naming convention is different for filesystem and for ASM. Moreover, it could change between Oracle versions, causing your regular expression to not properly detect all or part of the files, which actually happened in our environment. So when I saw following errors during one of our automatic recoveries (for more details about them please check this post):
channel c1: ORA-19870: error while restoring backup piece DB_20140527_h2p9b2pt_6_1_lvl0A ORA-19504: failed to create file "/ORA/dbs03/DB/datafile/DB_PVSS_EVENT_00000055.DBF" ORA-27040: file create error, unable to create file Linux-x86_64 Error: 2: No such file or directory Additional information: 1
I was pretty sure that they are related with the fact, that our recovery system has not properly detected if listed file was Oracle managed or not. At that time, for every non-OMF file, recovery system was supposed to put following commands into generated restore script:
set newname for datafile nnn to new;
Fortunately, since Oracle 11.2 it is not needed anymore, because to align placement of restored database files to directory structure of recovery server, it is enough to add following command without even checking if there are any non-OMF files (one has to remember about proper setting of db_create_file_dest initialization parameter):
set newname for database to new;
But going back to our problem, after quick check I realized that this case was different as I discovered interesting behavior - different output of report schema command, with and without recovery catalog connection:
-- not connected to catalog 683 0 DB_PVSS_EVENT_00000066 *** /ORA/dbs03/DB_RAC50/datafile/o1_mf_db_pvs__1396647588093112_.dbf -- connected to catalog 683 10240 DB_PVSS_EVENT_00000066 NO /ORA/dbs03/DB/datafile/DB_PVSS_EVENT_00000066.DBF
The output obtained without catalog connection was the right one. As you see it was OMF name, also correctly recognized by recovery system, so no specific set newname... has been put into the script - everything as expected, apart from the second results. If you read my post about backups in Data Guard environment, you know that we are using them where possible, also for the database we were trying to restore here. At that time, we were in the middle of the migration period, where some old standbys were still located on old hardware, with some files Oracle managed and others not. Currently our environment is much more standardized, with common storage platform, naming conventions and so on, but in this particular case, there was a difference, with datafile 683 being OMF on primary and non-OMF on standby. Oracle differentiates source of backups by site_key, which is derived from db_unique_name initialization parameter, so it is crucial to have database unique names different for every database in each primary - standby(s) configuration. In our example, initialization file template used by recovery system had only db_name parameter specified, so after connecting to the catalog, even though controlfile has been restored from primary, RMAN was looking for database records of the standby, on which updated naming conventions have not been applied yet, as it was supposed to disappear after moving to new hardware. That's why we were getting ORA-19504 error during restore - it tried to restore the file to the same location as on standby, which was different than the one used on recovery server. It wasn't automatically renamed, because recovery system was checking for type of the file using control file (found OMF one), but the real restore was using recovery catalog connection (found non-OMF) - as it is required with RMAN operations if standby is involved. Uff, I hope you got it :).
Complexity of IT systems and Oracle database in particular is ever-growing. Very useful functionalities like possibility to take backups from standby or Oracle Managed Files are providing many advantages, but one should never forget about potential disadvantages. Before taking any architectural decisions regarding your environment, you should do the homework to learn as much as possible about all pros, cons and hopefully any side effects that might be introduced by enabling new features. As another example of such side effect when using OMF and RMAN, I could mention difficulties in restarting failed restore jobs - please check the details in following MOS note: RMAN Restore Optimization and Oracle Managed Files (OMF) (Doc ID 1621319.1). Deep understanding is also necessary to efficiently troubleshoot, which becomes more difficult, while complexity is increasing. And please don't get me wrong - even though switching off OMF is possible using hidden _omf initialization parameter, I certainly don't encourage you to do so :), rather to dig and test as much as possible, to avoid or at least be prepared to troubleshoot complex problems.