PL/SQL quickie; generate RMAN set newname script to different mountpoints

  • Published on Sep 16 2011
  • # 353
  • oracle

Script below generates RMAN newname file where database A is copied to database B AND database B has ‘conflicting’ mountpoints set up.

Mountpoints setup on database A is like /m001/, /m002/, /m003/, /m004/ but mountpoints on database B side is just /m001/ and /m002.

/m001 has only 800 G free space so I place everything in /m001 until i_num_bytes reaches 800G then all the rest goes to /m002.

declare
 i_num_bytes number := 0;
 v_mount   varchar2(100);
 v_file   varchar2(100);

begin

 for i in (select file_id, file_name, bytes from dba_data_files order by file_id) 
 loop
  i_num_bytes := i_num_bytes + i.bytes ;
  -- 800g
  if i_num_bytes < 858993459200 then 
   v_mount := '/m001/oradata/********/' ;
  else 
   v_mount := '/m002/oradata/********/' ;
  end if;

  -- get base name of the file
  v_file := SUBSTR( i.file_name, INSTR( i.file_name ,'/', -1) +1);
  -- replace oracle_sid
  v_file := REPLACE( v_file, '********', '********');
  
  dbms_output.put_line( 'set newname for datafile ' || i.file_id || 
    ' to '''|| v_mount || v_file || ''';');
 
 end loop;

end;
/

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>