Generate scp datafiles script with regular expression

  • Published on May 24 2011
  • # 262
  • oracle

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;

declare
 function replace_sid ( piv_string in varchar2)
  return varchar2
 as
  db_name_cur varchar2(64);
  db_name_new varchar2(64);
 begin
  -- 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);
 end;

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

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’ > cpdb.sh

    //JP

  2. remi says:

    @JP: lol – je weet zelluuufff.

    http://youtu.be/Fk5bVqDvwqk

Leave a Reply

Helpful? - leave your note below so I can brag

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>