/*set serveroutput on*/
/*2019-3-8 version 1.0 author-chenguangrui*/
declare
v_i number;
v_log varchar2(2000) default '&log';
v_txt varchar2(200);
v_parallel number := ∥
p_txt varchar2(2000);
v_device varchar2(10) := '&device';
v_path varchar2(200) := '&datafile_path';
clear_text varchar2(200);
begin
select 'rman target / log=' || v_log || '<<EOF' || chr(10) || 'run' ||
chr(10) || '{'
into v_txt
from dual;
dbms_output.put_line(v_txt);
for p in 1 .. v_parallel loop
select 'allocate channel ch' || p || ' device type ' || v_device || ';'
into p_txt
from dual;
dbms_output.put_line(p_txt);
end loop;
for i in (select 'set newname for datafile ' || file# || ' to ' || ' "' ||
v_path || '/' ||
substr(name,
instr(name, '/', -1) + 1,
instr(name, '.') - instr(name, '/', -1) - 1) || ts# ||
rownum || '.dbf";' txt
from v$datafile) loop
dbms_output.put_line(i.txt);
end loop;
for i in (select 'set newname for tempfile ' || file# || ' to ' || ' "' ||
v_path || '/' ||
substr(name,
instr(name, '/', -1) + 1,
instr(name, '.') - instr(name, '/', -1) - 1) ||
'.dbf";' txt
from v$tempfile) loop
dbms_output.put_line(i.txt);
end loop;
dbms_output.put_line('restore database;');
dbms_output.put_line('switch datafile all;');
dbms_output.put_line('switch tempfile all;');
dbms_output.put_line('recover database;');
for p in 1 .. v_parallel loop
select 'release channel ch' || p || ';' into p_txt from dual;
dbms_output.put_line(p_txt);
end loop;
for l in (select distinct group# id from v$log) loop
for log in (select 'sql "alter database rename file ' || '''''' ||
member || ''''' to ''''' || v_path || '/redo' ||
group# || rownum || '.log''''";' log
from v$logfile
where group# = l.id) loop
dbms_output.put_line(log.log);
end loop;
end loop;
for l in (select distinct group# id from v$log) loop
select 'sql ''alter database clear logfile group ' || l.id || ''';'
into clear_text
from dual;
dbms_output.put_line(clear_text);
end loop;
dbms_output.put_line('}' || chr(10) || 'exit;' || chr(10) || 'EOF');
end;