Generate scp datafiles script with regular expression

Need to transfer a bunch of databases with pattern {a…9} to {t…8}.

Please copy databases below;

adummy19 to tdummy18 – server070 (*)
adummy29 to tdummy28 – server526
adummy39 to tdummy38 – server526
adummy49 to tdummy48 – server526
adummy59 to tdummy58 – server526
adummy69 to tdummy68 – server526
adummy79 to tdummy78 – server526
adummy89 to tdummy88 – server526

Created PL/SQL script below to get me the scp commands;

 function replace_sid ( piv_string in varchar2)
  return varchar2
  db_name_cur varchar2(64);
  db_name_new varchar2(64);
  -- current db_name
  select value into db_name_cur from v$parameter where name = 'db_name';
  -- new db_name 
  db_name_new := regexp_replace( db_name_cur, '(^a)', 't');
  db_name_new := regexp_replace( db_name_new, '(9$)', '8');
  -- return replacement
  return replace( piv_string, db_name_cur, db_name_new);

 for i in ( select file_name from dba_data_files ) 
  dbms_output.put_line ( 'scp ' || i.file_name || ' ahc526:' || replace_sid( i.file_name));
 end loop;

2 thoughts on “Generate scp datafiles script with regular expression

  1. Hi Remi! Love your work ! ;)
    Try this:
    find /m*/oradata/${SIDOLD}/*dbf -type f | awk ‘{print “scp ” $(NF-0) ” ${HOST}:” $(NF-0)}’ | sed ‘s/${SIDOLD}/${SIDNEW}/2’ >


Leave a Reply

Your email address will not be published. Required fields are marked *