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
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!
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.
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.
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;
/
Pingback: Lessons from RMAN restore and recovery | Bobby Durrett's DBA Blog
I guess I don’t understand your question. Can you clarify what it is you are asking?
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.
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.
Pingback: Renaming Files for RMAN Restore – Gokhan Atil