We usually need to duplicate our databases to test servers which have different disk layouts than production server. So we need to distribute the datafiles according to the disk space and directory structure of the new server. It becomes a time-consuming task if you’re dealing with hundreds of datafiles. Today I saw a PL/SQL script written by Bobby Durrett for renaming datafiles for RMAN restore. It produces required “set newname” commands which can be used by RMAN. That was a great idea and I decided to write my own version to satisfy my needs:

It’s very easy to edit this script. You just need to enter the disk partitions and available/free disk spaces (in gigabytes) on them (line 17 and 13). While entering the disk space in GBs, round the size down. You can set “prefix” (line 29) to empty string if you don’t want to use it. Here’s a sample output:

I haven’t tested the output of the script yet and I also made some small modifications right before publishing it, so there might be some errors. I would be happy to hear your feedback.

6 Responses to “Renaming Files for RMAN Restore”

  1. Mukarram Khan says:

    Hi Gokhan,

    I have duplicated the database using RMAN duplicate command on new server with different database name and different file location. I have followed the below note ID which was very helpful. The init parameter db_file_name_convert and log_file_name_convert  will place the files to the new location and will bring up the database.

    Creating a Duplicate Database on a New Host. [ID 388431.1]

    can you please tell us how to use this script while restore with an example, it will be helpful for the users.

    Thanks,

    Mukarram Khan

  2. Kees Schouten says:

    If you use Oracle Managed Files on the auxilary database then you don’t have to worry about renaming your datafiles. Oracle will generate all the new filenames.

    Kees Schouten

     

  3. Kees Schouten says:

    My previous comment is for the situation where all the databasefiles can be placed on the same location, as defined in DB_CREATE_FILE_DEST, for the situation with multiple locations the script is a good solution.

    Kees Schouten

    • Gokhan Atil says:

      Kees, yes as you see, the script is useful for restoring databases to a server with a “different disk structure”.

  4. Alex Vin says:

    add after line 43:

    else
    if diskno = diskspace.COUNT then
    DBMS_OUTPUT.put_line (‘# not space for ‘||df.FILE_ID||’ ‘||SUBSTR (df.FILE_NAME, INSTR (df.FILE_NAME, ‘/’, -1)));
    end if;

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code class="" title="" data-url=""> <del datetime=""> <em> <i> <q cite=""> <strike> <strong> <pre class="" title="" data-url=""> <span class="" title="" data-url="">