rman create set newname scriptOracle rename datafile script for all the datafile ..
SET NEWNAME in oracle 11g release 2.%b Specifies the file name stripped of directory paths. For example, if a datafile is namedset linesize 200 col file_name format a100 select 'set newname for datafile ' ||FILE#|| ' to '||'''/oradata/dbase/'|| substr(name,instr(name,'/',-1)+1, instr(substr(name,instr(name,'/',-1)+1),'.')-1 ) ||''' ;' file_name from v$datafile; FILE_NAME ---------------------------------------------------------------------------------------------------- set newname for datafile 1 to '/oradata/dbase/system01' ; set newname for datafile 3 to '/oradata/dbase/sysaux01' ; set newname for datafile 4 to '/oradata/dbase/undotbs01' ; set newname for datafile 5 to '/oradata/dbase/undotbs02' ; set newname for datafile 6 to '/oradata/dbase/users01' ; http://anuj-singh.blogspot.com/2021/10/rman-restore-database.html Change this script based on your reqiurement run this script after mount the database set linesize 200 heading off feedback off col file_name format a100 select 'run{' from dual union all select 'set newname for datafile ' ||FILE#|| ' to '||'''/xxxxxxx/xxxxxxx/'|| substr(name,instr(name,'/',-1)+1, instr(substr(name,instr(name,'/',-1)+1),'.')-1 ) ||''' ;' file_name from v$datafile union all select 'set until scn xxxxx;' from dual union all select 'restore database;' from dual union all select 'switch datafile all;' from dual union all select 'recover database;' from dual union all select '} ' from dual ;
/prod/financial.dbf, then %b results in financial.dbf.
%f Specifies the absolute file number of the datafile for which the new name is generated. For
example, if datafile 2 is duplicated, then %f generates the value 2.
%I Specifies the DBID.
%N Specifies the tablespace name.
%U Specifies the following format: data-D-%d_id-%I_TS-%N_FNO-%f.
Output ...... run{ set newname for datafile 1 to '/xxxxxxx/xxxxxxx/system01' ; set newname for datafile 3 to '/xxxxxxx/xxxxxxx/sysaux01' ; set newname for datafile 4 to '/xxxxxxx/xxxxxxx/undotbs01' ; set newname for datafile 5 to '/xxxxxxx/xxxxxxx/undotbs02' ; set newname for datafile 6 to '/xxxxxxx/xxxxxxx/users01' ; set until scn xxxxx; restore database; switch datafile all; recover database; } Open the Database resetlogs option. RMAN> alter database open resetlogs;
=====
for PDB
set linesize 200 col NAME for a15 col FILE_NAME1 for a100 col file_name format a100 select 'set newname for datafile ' ||FILE#|| ' to '||'''/oradata/dbase/'||p.name||nvl2(p.name,'/','')|| substr(d.name,instr(d.name,'/',-1)+1, instr(substr(d.name,instr(d.name,'/',-1)+1),'.')-1 ) ||''' ;' file_name1 from v$datafile d,v$pdbs p where 1=1 and d.CON_ID=p.CON_ID(+) -- order by p.name order by FILE#; FILE_NAME1 ---------------------------------------------------------------------------------------------------- set newname for datafile 1 to '/oradata/dbase/system01' ; set newname for datafile 3 to '/oradata/dbase/sysaux01' ; set newname for datafile 4 to '/oradata/dbase/undotbs01' ; set newname for datafile 5 to '/oradata/dbase/PDB9/system01' ; set newname for datafile 6 to '/oradata/dbase/PDB9/sysaux01' ; set newname for datafile 7 to '/oradata/dbase/users01' ; set newname for datafile 8 to '/oradata/dbase/PDB9/undotbs01' ; set newname for datafile 9 to '/oradata/dbase/PDB9/users01' ; set newname for datafile 10 to '/oradata/dbase/PDB9/TB_CUST' ; set newname for datafile 11 to '/oradata/dbase/PDB9/TTS_OBJECTS' ; set newname for datafile 13 to '/oradata/dbase/undotbs2' ; set newname for datafile 14 to '/oradata/dbase/PDB$SEED/system01' ; set newname for datafile 15 to '/oradata/dbase/PDB$SEED/sysaux01' ; set newname for datafile 16 to '/oradata/dbase/PDB$SEED/undotbs01' ; set newname for datafile 17 to '/oradata/dbase/PDB$SEED/users01' ; set newname for datafile 18 to '/oradata/dbase/PDB$SEED/TB_CUST' ; set newname for datafile 19 to '/oradata/dbase/PDB$SEED/TTS_OBJECTS' ; set newname for datafile 20 to '/oradata/dbase/PDB$SEED/pdb9_undotbs' ; set newname for datafile 21 to '/oradata/dbase/PDB9/pdb9_undotbs1' ; 19 rows selected.
====
-- with output set linesize 200 col file_name format a100 select 'set newname for datafile ' ||FILE#|| ' to '||'''/oradata/dbase/'|| substr(name,instr(name,'/',-1)+1, instr(substr(name,instr(name,'/',-1)+1),'.')-1 )|| FILE# ||'.dbf'' ;' file_name from v$datafile; set newname for datafile 1 to '/oradata/dbase/system011.dbf' ; set newname for datafile 2 to '/oradata/dbase/apex_data012.dbf' ; set newname for datafile 3 to '/oradata/dbase/sysaux013.dbf' ; set newname for datafile 4 to '/oradata/dbase/undotbs014.dbf' ; set newname for datafile 5 to '/oradata/dbase/undotbs025.dbf' ; set newname for datafile 7 to '/oradata/dbase/users017.dbf' ; set newname for datafile 8 to '/oradata/dbase/test_data018.dbf' ; set newname for datafile 9 to '/oradata/dbase/test_data_ind_019.dbf' ; set newname for datafile 10 to '/oradata/dbase/lobtest10.dbf' ; set newname for datafile 11 to '/oradata/dbase/test_uniform11.dbf' ; set newname for datafile 12 to '/oradata/dbase/datapipe12.dbf' ; set newname for datafile 13 to '/oradata/dbase/rmanbackup13.dbf' ; set newname for datafile 14 to '/oradata/dbase/crmi_v1_medium_table14.dbf' ; set newname for datafile 15 to '/oradata/dbase/test_data15.dbf' ; set newname for datafile 16 to '/oradata/dbase/audit_tbl_0116.dbf' ; set newname for datafile 17 to '/oradata/dbase/undo_small17.dbf' ; set newname for datafile 18 to '/oradata/dbase/test_data18.dbf' ; set newname for datafile 19 to '/oradata/dbase/test_data19.dbf' ; set newname for datafile 20 to '/oradata/dbase/test_data20.dbf' ; set newname for datafile 21 to '/oradata/dbase/test_lob21.dbf' ; set newname for datafile 22 to '/oradata/dbase/test_data22.dbf' ; set newname for datafile 23 to '/oradata/dbase/test_data23.dbf' ; set newname for datafile 24 to '/oradata/dbase/test_data24.dbf' ; set newname for datafile 25 to '/oradata/dbase/rec_catalog25.dbf' ; 24 rows selected.
========================================
datafile no at the end ...
set linesize 200
heading off feedback off
col file_name format a150
col "Query For RMAN Restoration" for a100
select 'run {' "Query For RMAN Restoration" from dual
union all
select 'set newname for datafile ' ||FILE#|| ' to '||'''/oradata/dbase/'|| substr(name,instr(name,'/',-1)+1, instr(substr(name,instr(name,'/',-1)+1),'.')-1 )|| FILE# ||'.dbf'' ;' file_name from v$datafile
union all
select 'restore database;' from dual
union all
select 'switch datafile all;' from dual
union all
select 'recover database;' from dual
union all
select '} ' from dual ;
---
for pdbs file
set linesize 200
heading off feedback off
col file_name format a150
col "Query For RMAN Restoration" for a100
select 'run {' "Query For RMAN Restoration" from dual
union all
-- select 'set newname for datafile ' ||FILE#|| ' to '||'''/oradata/dbase/'|| substr(name,instr(name,'/',-1)+1, instr(substr(name,instr(name,'/',-1)+1),'.')-1 )|| FILE# ||'.dbf'' ;' file_name from v$datafile
select 'set newname for datafile ' ||FILE#|| ' to '||'''/oradata/dbase/'||p.name||nvl2(p.name,'/','')|| substr(d.name,instr(d.name,'/',-1)+1, instr(substr(d.name,instr(d.name,'/',-1)+1),'.')-1 ) || FILE# ||'.dbf'' ;' file_name
from v$datafile d,v$pdbs p
where 1=1
and d.CON_ID=p.CON_ID(+)
union all
select 'restore database;' from dual
union all
select 'switch datafile all;' from dual
union all
select 'recover database;' from dual
union all
select '} ' from dual
--order by 1
;
datafile no at the end ...
Query For RMAN Restoration
----------------------------------------------------------------------------------------------------
run {
set newname for datafile 7 to '/oradata/dbase/users7.dbf' ;
set newname for datafile 1 to '/oradata/dbase/system1.dbf' ;
set newname for datafile 3 to '/oradata/dbase/sysaux3.dbf' ;
====
simple with file no
set pagesize 100
define new_loc='/dumps/'
select 'set newname for datafile ' || x.FILE# || ' to ' ||
new_file_Location || substr(x.NAME,instr(x.name,'/',-1)+1 ,length(x.name)-instr(x.name,'/',-1))||'_'||x.FILE# ||'.dbf'
from v$datafile x,(select '&new_loc' new_file_Location from dual);
