Restore database to a different path - set newname for datafile script (UNIX/Linux)

This script helps you to set the new name for datafiles during restoration to a different path. Script will ask two inputs: new path and restoration SCN.


set serveroutput on;
set linesize 300

declare
  cursor df is
    select name from v$datafile
    union all
    select name from v$tempfile
    union all
    select member as name from v$logfile;
   
  cursor df2 is
    select case when instr (file_name, '/') > 0
         then
         substr (file_name, instr (file_name, '/', -1) + 1)
         end as file_name
    from (select name as file_name from v$datafile
    union all
    select name as file_name from v$tempfile
    union all
    select member as file_name from v$logfile);

  newPath varchar2(300);
  newScn number(15);
  s_df varchar2(300);
  s_df2 varchar2(300);
 
begin
--  dbms_output.put_line('Enter the set newname path (omit trailing ''/''):');
  newPath := '&PATH';
--  dbms_output.put_line('Enter the SCN:');
  newScn := &SCN;
  dbms_output.put_line('run');
  dbms_output.put_line('{');
  dbms_output.put_line('set until scn = '||newScn||';');

  open df;
  open df2;

  fetch df into s_df;
  fetch df2 into s_df2;

  while df%FOUND loop
    dbms_output.put_line('set newname for datafile '''||s_df||''' to '''||newPath||'/'||s_df2||''';');
    fetch df into s_df;
    fetch df2 into s_df2;
  end loop;

  dbms_output.put_line('restore database;');
  dbms_output.put_line('switch all;');
  dbms_output.put_line('}');

  close df;
  close df2;
end;
/

Example output:

SQL>@plsql_set_newname_for_datafile.sql
Enter value for path: /path/to/the/new/localtion
old  27:   newPath := '&PATH';
new  27:   newPath := '/path/to/the/new/localtion';
Enter value for scn: 1234123
old  29:   newScn := &SCN;
new  29:   newScn := 1234123;
run
{
set until scn = 1234123;
set newname for datafile '/current/datafile/path/system01.dbf' to '/path/to/the/new/localtion/system01.dbf';
set newname for datafile '/current/datafile/path/undotbs01.dbf' to '/path/to/the/new/localtion/undotbs01.dbf';
set newname for datafile '/current/datafile/path/sysaux01.dbf' to '/path/to/the/new/localtion/sysaux01.dbf';
set newname for datafile '/current/datafile/path/users01.dbf' to '/path/to/the/new/localtion/users01.dbf';
set newname for datafile '/current/datafile/path/temp01.dbf' to '/path/to/the/new/localtion/temp01.dbf';
set newname for datafile '/current/datafile/path/redo01_1.rdo' to '/path/to/the/new/localtion/redo01_1.rdo';
set newname for datafile '/current/datafile/path/redo02_1.rdo' to '/path/to/the/new/localtion/redo02_1.rdo';
set newname for datafile '/current/datafile/path/redo03_1.rdo' to '/path/to/the/new/localtion/redo03_1.rdo';
restore database;
switch all;
}

PL/SQL procedure successfully completed.