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;

	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”

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

@JP: lol – je weet zelluuufff.

http://youtu.be/Fk5bVqDvwqk

2 thoughts on “Generate scp datafiles script with regular expression”

Leave a Reply Cancel reply