Search This Blog

Total Pageviews

Tuesday 6 September 2016

rman create set newname script


rman create set newname script 

Oracle 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 named
    /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.

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_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 ;
 

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);



Oracle DBA

anuj blog Archive