Renaming files for RMAN restore

Here is a script I hacked together Tuesday night to figure out how to fit all my production data files and temp files on a development server for a RMAN restore of a production backup onto a new server:

DECLARE 
    CURSOR DF_CURSOR IS 
        SELECT
            *
        FROM dba_data_files
        order by FILE_ID;

    DF_REC DF_CURSOR%ROWTYPE;

    CURSOR TF_CURSOR IS 
        SELECT
            *
        FROM dba_temp_files
        order by FILE_ID;

    TF_REC TF_CURSOR%ROWTYPE;

    db01 number;
    db02 number;
    db03 number;
    db04 number;
    db05 number;
    db06 number;
    db07 number;
    db08 number;
    db09 number;

BEGIN
db08 := 16561550;
db07 := 19548242;  
db06 := 91252087;  
db05 := 29913520;  
db04 := 18507885;  
db03 := 6206062;   
db02 := 64145394;  
db01 := 265206680; 
db09 := 323990034;

    OPEN DF_CURSOR;
LOOP
    FETCH DF_CURSOR INTO DF_REC;
    EXIT WHEN DF_CURSOR%NOTFOUND;

    IF (DB01 > ((DF_REC.BYTES/1024)+1024)) THEN

       dbms_output.put_line('set newname for datafile '||DF_REC.FILE_ID||' to  '''||
         substr(DF_REC.FILE_NAME,1,16)||'db01/NEWDBXX'||substr(DF_REC.FILE_NAME,29)||
         ''';');
       DB01 := DB01 - (DF_REC.BYTES/1024);

    ELSIF (DB09 > ((DF_REC.BYTES/1024)+1024)) THEN

       dbms_output.put_line('set newname for datafile '||DF_REC.FILE_ID||' to  '''||
         substr(DF_REC.FILE_NAME,1,16)||'db09/NEWDBXX'||substr(DF_REC.FILE_NAME,29)||
         ''';');
       DB09 := DB09 - (DF_REC.BYTES/1024);

    ELSIF (DB06 > ((DF_REC.BYTES/1024)+1024)) THEN

       dbms_output.put_line('set newname for datafile '||DF_REC.FILE_ID||' to  '''||
         substr(DF_REC.FILE_NAME,1,16)||'db06/NEWDBXX'||substr(DF_REC.FILE_NAME,29)||
         ''';');
       DB06 := DB06 - (DF_REC.BYTES/1024);

    end if;

END LOOP;
CLOSE DF_CURSOR;

    OPEN TF_CURSOR;
LOOP
    FETCH TF_CURSOR INTO TF_REC;
    EXIT WHEN TF_CURSOR%NOTFOUND;    
   IF (DB06 > ((TF_REC.BYTES/1024)+1024)) THEN

       dbms_output.put_line('set newname for tempfile '||TF_REC.FILE_ID||' to  '''||
         substr(TF_REC.FILE_NAME,1,16)||'db06/NEWDBXX'||substr(TF_REC.FILE_NAME,29)||
         ''';');
       DB06 := DB06 - (TF_REC.BYTES/1024);

    end if;

END LOOP;
CLOSE TF_CURSOR;
END;
/

The filesystems and paths to the datafiles and temp files looked like this:

/var/opt/oracle/db01/OLDDBXX/dbf/system01.dbf

/var/opt/oracle/db06/NEWDBXX/dbf/system01.dbf

Lots of stuff is hard coded such as the space available in each filesystem like this:

db08 := 16561550;

The units are kilobytes which is the output of bdf in HP-UX.

Might not be useful since it isn’t that parametrized and automatic but if you need some code for fitting a bunch of datafiles in a new set of filesystems it might help you find a place to start.  Also, I tried the three filesystems with the most free space first and didn’t really need any others so that is why the if statements only have db01, db09, and db06 but you could add more.

Output is RMAN commands like these:

set newname for datafile 1 to  ‘/var/opt/oracle/db01/NEWDBXX/dbf/system01.dbf’;

set newname for tempfile 1 to  ‘/var/opt/oracle/db06/NEWDBXX/dbf/temp01.dbf’;

– Bobby

About Bobby

I live in Chandler, Arizona with my wife and three daughters. I work for US Foods, the second largest food distribution company in the United States. I have worked in the Information Technology field since 1989. I have a passion for Oracle database performance tuning because I enjoy challenging technical problems that require an understanding of computer science. I enjoy communicating with people about my work.
This entry was posted in Uncategorized. Bookmark the permalink.

10 Responses to Renaming files for RMAN restore

  1. Gokhan Atil says:

    Bobby, it’s a great idea to write a script for it. We usually need to duplicate our DBs to the servers with different disk layouts. As I see you didn’t put if-then conditions for all disks (although you defined the space of them)… Anyway thanks for sharing!

  2. Bobby says:

    Gokhan,

    Thanks for your comment. I was in a hurry so I didn’t put all the filesystems in the if-then-else statements but that would be the more complete way to do it. Once I figured out that everything would fit on the three filesystems I just left it and moved on. So, not perfect but possibly useful to someone.

    – Bobby

    • Hi Bobby,

      Can you please share the complete script. This is very useful and it resolved my problem.

      • Bobby says:

        I uploaded an edited version of the full restore script to https://www.bobbydurrettdba.com/uploads/restore08272013.zip. This is a zip of the rman restore script including the rename commands generated by the script in the blog post. I’ve edited the database names and host names to not be our real ones for security.

        • sunilbhola says:

          I tried to complete your script which you to figure out how to fit all my production data files. Can u pls verify:-

          REM Lots of stuff is hard coded such as the space available in each filesystem like this:
          REM db08 := 16561550;
          REM The units are kilobytes which is the output of bdf in HP-UX.

          Here is a script I hacked together Tuesday night to figure out how to fit all my production data files and temp files on a development server for a RMAN restore of a production backup onto a new server:

          DECLARE
          CURSOR DF_CURSOR IS SELECT * FROM dba_data_files order by FILE_ID;
          DF_REC DF_CURSOR%ROWTYPE;
          CURSOR TF_CURSOR IS SELECT * FROM dba_temp_files order by FILE_ID;
          TF_REC TF_CURSOR%ROWTYPE;

          db01 number;
          db02 number;
          db03 number;
          db04 number;
          db05 number;
          db06 number;
          db07 number;
          db08 number;
          db09 number;

          BEGIN
          db01 := 265206680;
          db02 := 64145394;
          db03 := 6206062;
          db04 := 18507885;
          db05 := 29913520;
          db06 := 91252087;
          db07 := 19548242;
          db08 := 16561550;
          db09 := 323990034;

          OPEN DF_CURSOR;
          LOOP
          FETCH DF_CURSOR INTO DF_REC;
          EXIT WHEN DF_CURSOR%NOTFOUND;

          IF (DB01 > ((DF_REC.BYTES/1024)+1024)) THEN
          dbms_output.put_line(‘set newname for datafile ‘||DF_REC.FILE_ID||’ to ”’||substr(DF_REC.FILE_NAME,1,16)||’db01/NEWDBXX’||substr(DF_REC.FILE_NAME,29)||”’;’);
          DB01 := DB01 – (DF_REC.BYTES/1024);

          ELSIF (DB02 > ((DF_REC.BYTES/1024)+1024)) THEN
          dbms_output.put_line(‘set newname for datafile ‘||DF_REC.FILE_ID||’ to ”’||substr(DF_REC.FILE_NAME,1,16)||’db02/NEWDBXX’||substr(DF_REC.FILE_NAME,29)||”’;’);
          DB02 := DB02 – (DF_REC.BYTES/1024);

          ELSIF (DB03 > ((DF_REC.BYTES/1024)+1024)) THEN
          dbms_output.put_line(‘set newname for datafile ‘||DF_REC.FILE_ID||’ to ”’||substr(DF_REC.FILE_NAME,1,16)||’db03/NEWDBXX’||substr(DF_REC.FILE_NAME,29)||”’;’);
          DB03 := DB03 – (DF_REC.BYTES/1024);

          ELSIF (DB04 > ((DF_REC.BYTES/1024)+1024)) THEN
          dbms_output.put_line(‘set newname for datafile ‘||DF_REC.FILE_ID||’ to ”’||substr(DF_REC.FILE_NAME,1,16)||’db04/NEWDBXX’||substr(DF_REC.FILE_NAME,29)||”’;’);
          DB04 := DB04 – (DF_REC.BYTES/1024);

          ELSIF (DB05 > ((DF_REC.BYTES/1024)+1024)) THEN
          dbms_output.put_line(‘set newname for datafile ‘||DF_REC.FILE_ID||’ to ”’||substr(DF_REC.FILE_NAME,1,16)||’db05/NEWDBXX’||substr(DF_REC.FILE_NAME,29)||”’;’);
          DB05 := DB05 – (DF_REC.BYTES/1024);

          ELSIF (DB06 > ((DF_REC.BYTES/1024)+1024)) THEN
          dbms_output.put_line(‘set newname for datafile ‘||DF_REC.FILE_ID||’ to ”’|| substr(DF_REC.FILE_NAME,1,16)||’db06/NEWDBXX’||substr(DF_REC.FILE_NAME,29)||”’;’);
          DB06 := DB06 – (DF_REC.BYTES/1024);

          ELSIF (DB07 > ((DF_REC.BYTES/1024)+1024)) THEN
          dbms_output.put_line(‘set newname for datafile ‘||DF_REC.FILE_ID||’ to ”’||substr(DF_REC.FILE_NAME,1,16)||’db07/NEWDBXX’||substr(DF_REC.FILE_NAME,29)||”’;’);
          DB07 := DB07 – (DF_REC.BYTES/1024);

          ELSIF (DB08 > ((DF_REC.BYTES/1024)+1024)) THEN
          dbms_output.put_line(‘set newname for datafile ‘||DF_REC.FILE_ID||’ to ”’||substr(DF_REC.FILE_NAME,1,16)||’db08/NEWDBXX’||substr(DF_REC.FILE_NAME,29)||”’;’);
          DB08 := DB08 – (DF_REC.BYTES/1024);

          ELSIF (DB09 > ((DF_REC.BYTES/1024)+1024)) THEN
          dbms_output.put_line(‘set newname for datafile ‘||DF_REC.FILE_ID||’ to ”’|| substr(DF_REC.FILE_NAME,1,16)||’db09/NEWDBXX’||substr(DF_REC.FILE_NAME,29)||”’;’);
          DB09 := DB09 – (DF_REC.BYTES/1024);

          end if;

          END LOOP;
          CLOSE DF_CURSOR;

          OPEN TF_CURSOR;

          LOOP
          FETCH TF_CURSOR INTO TF_REC;
          EXIT WHEN TF_CURSOR%NOTFOUND;
          IF (DB06 > ((TF_REC.BYTES/1024)+1024)) THEN

          dbms_output.put_line(‘set newname for tempfile ‘||TF_REC.FILE_ID||’ to ”’|| substr(TF_REC.FILE_NAME,1,16)||’db06/NEWDBXX’||substr(TF_REC.FILE_NAME,29)||”’;’);
          DB06 := DB06 – (TF_REC.BYTES/1024);

          end if;

          END LOOP;
          CLOSE TF_CURSOR;
          END;
          /

  3. Pingback: Lessons from RMAN restore and recovery | Bobby Durrett's DBA Blog

  4. Bobby says:

    I guess I don’t understand your question. Can you clarify what it is you are asking?

    • sunilbhola says:

      Hi Bobby,

      You have created this article with information like – “Script to figure out how to fit all my production data files and temp files on a development server for a RMAN restore of a production backup onto a new server”

      but as you told that we have not used all mount points so this script is little bit uncompleted. i tried to complete the same.

      • Bobby says:

        Sorry. I was slow to understand. It looks good. I was under the gun when I wrote the code originally so that was as far as I got.

  5. Pingback: Renaming Files for RMAN Restore – Gokhan Atil

Leave a Reply

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

This site uses Akismet to reduce spam. Learn how your comment data is processed.