set serveroutput on
execute dbms_output.enable(20000);
declare
cursor c1 is
select distinct tablespace_name
from dba_data_files
order by tablespace_name;
cursor c4 is
select member from v$logfile
order by member;
staging_copy_area varchar2(100) := ‘10.201.43.51:/uocdb1/oradata/UOC’;
begin
for cur in c1 loop
dbms_output.put_line (‘alter tablespace ‘ || cur.tablespace_name || ‘ begin backup;’);
declare
cursor c2 is
select file_name
from dba_data_files
where tablespace_name = cur.tablespace_name
order by file_name;
begin
for cur1 in c2 loop
dbms_output.put_line (‘! scp ‘ || cur1.file_name || ‘ ‘ || staging_copy_area );
end loop;
end;
dbms_output.put_line (‘alter tablespace ‘ || cur.tablespace_name || ‘ end backup;’);
end loop;
/********* log file backup **************/
for cur4 in c4 loop
dbms_output.put_line (‘! scp ‘ || cur4.member || ‘ ‘ || staging_copy_area );
end loop;
end;
/