Renaming Files for RMAN Restore

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.

Please share this post Share on Facebook0Share on Google+0Share on LinkedIn5Share on Reddit0Tweet about this on Twitter

Gokhan Atil is a database administrator who has hands-on experience with both RDBMS and noSQL databases (Oracle, PostgreSQL, Microsoft SQL Server, Sybase IQ, MySQL, Cassandra, MongoDB and ElasticSearch), and strong background on software development. He is certified as Oracle Certified Professional (OCP) and is awarded as Oracle ACE (in 2011) and Oracle ACE Director (in 2016) for his continuous contributions to the Oracle users community.

7 Comments

  1. Mukarram Khan

    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

    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

    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

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

  4. Alex Vin

    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 Comment

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