Tip & Hint: DB_CREATE_FILE_DEST Behavior when using ASM

Recently I saw database admin with issue during the Restore  Database using RMAN and ASM, when database is locate in more than one Diskgroup.
Situation:
The Datafiles and REDO were created in two different diskgroups.
It was necessary to perform a restore of the database, then the dba removed all datafiles in both diskgroup and executed restore the database.
During the restore RMAN was reported restoring the datafiles in the correct diskgroup and  showing original path/file on prompt of RMAN, but at the end of restore  all datafiles were restored in only one diskgroup, all datafiles were moved/renamed to diskgroup   specified in parameter DB_CREATE_FILE_DEST.
Question: It’s a normal behavior?
Yes…When using ASM and Oracle parameters such as DB_CREATE_FILE_DEST and DB_CREATE_ONLINE_LOG_DEST_n the dba must be careful to either use all OMF files or keep track of the files not created OMF. There is an order in which files are created with these parameters.
By default, when create a Database using DBCA storing datafiles in  ASM the parameter DB_CREATE_FILE_DEST  is set during creation of database, the parameter  DB_CREATE_ONLINE_LOG_DEST_n is not set.

Datafiles Behavior

The restore behavior is the same as with file creation behavior for OMF files. When OMF files are restored, they follow an order of precedence. That order is:

  1. If “SET NEWNAME” is specified, RMAN will use that name for restore.
  2.  If the original file exists, RMAN will use the original filename for restore.
  3.  If the DB_CREATE_FILE_DEST is set and original filename does not exist, RMAN will use the diskgroup name specified
  4.  If no DB_CREATE_FILE_DEST is set and the original file does not exist, then RMAN will create another name for that file in the original disk group.

So, If you do not want surprise after or during the restore, you must unset the parameter DB_CREATE_FILE_DEST before start restore.

alter system set DB_CREATE_FILE_DEST='' scope=memory;

After Restore/Recover (i.e during open database) you can have suprise if DB_CREATE_FILE_DEST is not set.

So, I recommend you set DB_CREATE_FILE_DEST  between   after RECOVER DATABASE and before “OPEN RESETLOGS”  or set  DB_CREATE_ONLINE_LOG_DEST_n, because during “open resetlogs” Oracle will create Online Logs files if it does not exists.

Online Logs (Redo) Behavior

 When OMF files are restored, they follow an order of precedence. That order is:

  1. If the original file exists, RMAN will use the original filename for restore.
  2. If the DB_CREATE_ONLINE_LOG_DEST_n  is set and original filename does not exist, RMAN will use the diskgroup name specified in DB_CREATE_ONLINE_LOG_DEST_n
  3. If no DB_CREATE_ONLINE_LOG_DEST_n  is set and the original file does not exist  but  DB_CREATE_FILE_DEST and DB_RECOVERY_FILE_DEST  (FRA) is set, RMAN will use the diskgroup name specified in both (DB_CREATE_FILE_DEST & DB_RECOVERY_FILE_DEST)  to multiplex ONLINELOG.
  4. If no DB_CREATE_ONLINE_LOG_DEST_n and DB_RECOVERY_FILE_DEST   is set and the original file does not exist  but  DB_CREATE_FILE_DEST is set,  RMAN will use the diskgroup name specified in DB_CREATE_FILE_DEST.
  5. If no DB_CREATE_ONLINE_LOG_DEST_n and DB_CREATE_FILE_DEST   is set and the original file does not exist  but  DB_RECOVERY_FILE_DEST is set,  RMAN will use the diskgroup name specified in DB_RECOVERY_FILE_DEST.
  6. If no DB_CREATE_ONLINE_LOG_DEST_n, DB_CREATE_FILE_DEST and  DB_RECOVERY_FILE_DEST is set and the original file does not exist, Oracle will raise “ORA-19801: initialization parameter DB_RECOVERY_FILE_DEST is not set” during “alter database open resetlogs“.
Hope this help

Enjoy…

Advertisements

12 Comments on “Tip & Hint: DB_CREATE_FILE_DEST Behavior when using ASM”

  1. Levi,

    It is interesting article to read, I have mostly worked with ASM/OMF, When specified DB_CREATE_FILE_DEST & DB_CREATE_ONLINE_LOG_DEST_n, then datafiles/controlfiles will be created in “DB_CREATE_FILE_DEST” & online redo logs will be created in “DB_CREATE_ONLINE_LOG_DEST_n” mentioned value.

    If in case not mentioned any “DB_CREATE_ONLINE_LOG_DEST_n” then redo log files also will be created in “DB_CREATE_FILE_DEST”.

    As you said, if we set “DB_CREATE_FILE_DEST” as NULL value, then where will be Control/redo/Datafiles will be created, when source and destination diskgroups are different?

    Thanks.

    Nassyam Basha.

    Like

    • Levi Pereira says:

      Hi Nassyan,

      You did great questions.
      Questions about Online Logs (Redo) behavior, I answered updating the Post.

      The destination of CONTROLFILE during restore is not affected by these parameters, the controlfile is managed only by the parameter “CONTROL_FILES”.

      Regards,
      Levi Pereira

      Like

  2. Levi,

    I saw your updates, Those are really helpful, Totally agreed,
    If i mention control_files=’+DATA’ it will restore in mentioned locations without communication with DB_CREATE_FILE_DEST.

    Some more follow-up.
    I have been much worked with ASM/OMF, In case of restore database, In first attempt for example it restored 10 out of 20 datafiles, Each datafile will created naming convention as +DG//USERS.60067.765541173″

    If i terminate the RMAN restore session, If i restart restore again, I have analyzed and it will restore again all the datafiles with new incarnation, Why it wont skip already restored datafiles?

    Can you share some more information on this?

    Thanks & Regards,
    Nassyam Basha.

    Like

    • Levi Pereira says:

      Hi Nassyan,

      Far as I know:
      Oracle try use same datafile (name) with was backed, Oracle get name of datafile registered in current controlfile.
      If datafile name does not exists/match with datafile registered in controlfile, Oracle create a new incarnation of datafile after datafile is fully restored, and register it’s name in current controlfile.
      So, if you stop restore and after restart restore using same controlfile used in previous restore, Oracle skip datafile already restored in previous restore.
      But, if you stop restore and after restart restore using another controlfile (i.e restore controlfile again from previous backup), controlfile does not know datafile restored by previous restore and restore datafile again creating a new incarnation of datafile keeping previous datafiles restored in diskgroup. He don’t remove or reuse previous datafiles restored because controlfile does not know wich he exists.

      Regards,
      Levi Pereira

      Like

      • nassyambasha says:

        Levi,

        Once again,I analyzed the behavior very closely, If I terminate the restore of RMAN once again if re run the job, it will skip datafiles for sure,
        In case when we restore a new controlfile again, and start restore, those already restored datafiles it wont consider, Thats why it keep restoring the same datafiles.

        I think, even after restore new controlfile, if we register restored datafiles again like (catalog start with), then i guess it will skip those datafiles. is it?

        Thanks.

        Like

      • Levi Pereira says:

        Hi,
        Sorry for delay.
        With “Catalog start with” we can catalog only backupset and archivelogs. I gess with “set new name” we can do that.
        Regards,
        Levi Pereira

        Like

  3. So, there is now easy way to catalog already restored files. If i understand correctly, i have to use set new name for each datafile using ‘+DATA/…/tablespace_name.xxx.yyyy’. In case of 400+ datafiles it’s quite difficult to map existing ASM files with file# in set newname, isn’t it? Why not to create more clever catalog command? In my mind, the header of file in ASM should contain file# and other metadata info.

    Like

  4. Jamsher says:

    Excellent Explanation…:)

    Like

  5. Arun says:

    Please help me to understand it better,I didn’t understand the concept fully. May be missing some piece.

    I am doing cloning from prod to dev. i have OMF and non OMF file in my prod database.
    in test side in pfile i put *.db_create_file_dest=’+DATA’
    to map datafile from prod to test i given

    *.db_file_name_convert=(‘+DATA/HWPF89FP’,’+LOG1/HWPF89FD’,’+DATA2/HWPF89FP’,’+LOG2/HWPF89FD’,’+FRA1/HWPF89FP’,’+LOG2/HWPF89FD’,’+FRA2/HWPF89FP’,’+DATA/HWPF89FD’,’+LOG1/HWPF89FP’,’+FRA1/HWPF89FD’,’+LOG2/HWPF89FP’,’+DATA2/HWPF89FD’)

    I have space in other disk group but still refresh is failing saying space is not there in DATA disk group. While mapping i did calculated and put the db_file_name_convert in same way.

    ORA-15041: diskgroup “FRA1” space exhausted.

    I am using rman duplicate command to do refresh.

    i am not understanding how RMAN is working internally.

    If you can put some light on it.

    Like

  6. Wang Xianda says:

    Hi, Levi:

    I am recovering Oracle 11.2 using OMF & ASM, with datafiles in a diskgroup named +SIG1 originally. And I am using

    set newname for datafile n to ‘+SIG2’

    for all the datafiles when restore. And also, rman shows something like

    restoring datafile 00014 to +SIG2/orcl/datafile/xxxx.271.911595371

    which is expected. But actually, all these files are restored to +SIG1, which is specified in db_create_file_dest. As you said, ‘set newname’ should take precedence, right?

    Like

    • Wang Xianda says:

      I found out where the problem is. I used datafile path instead of file id in the ‘set newname’ clause. Maybe RMAN do something different with OMF file between path and id. Changing the path to id fixes the problem.

      Like


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s