Renaming Files for RMAN Restore
We usually need to duplicate our databases to test servers which have different disk layouts than production server. So we need to distribute the datafiles according to the disk space and directory structure of the new server. It becomes a time-consuming task if you’re dealing with hundreds of datafiles. Today I saw a PL/SQL script written by Bobby Durrett for renaming datafiles for RMAN restore. It produces required “set newname” commands which can be used by RMAN. That was a great idea and I decided to write my own version to satisfy my needs:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 |
DECLARE CURSOR DF_CURSOR IS SELECT 'data' ft, FILE_ID, FILE_NAME, BYTES / (1024 * 1024 * 1024) fsize FROM dba_data_files union all SELECT 'temp' ft, FILE_ID, FILE_NAME, BYTES / (1024 * 1024 * 1024) fsize FROM dba_temp_files ORDER BY ft, file_id; TYPE array_number IS TABLE OF NUMBER; TYPE array_varchar2 IS TABLE OF VARCHAR2 (200); diskno NUMBER; diskspace array_number := array_number ( 30, 20, 20, 120); -- Sizes in GB diskpath array_varchar2 := array_varchar2 ( '/u01/data', '/u02/data', '/u03/data', '/u04/data'); prefix VARCHAR2 (200) := '/SID'; -- will be added to diskpaths BEGIN FOR df IN DF_CURSOR LOOP FOR diskno IN 1 .. diskspace.COUNT LOOP IF (diskspace (diskno) > df.fsize) THEN DBMS_OUTPUT.put_line ( 'set newname for ' || df.ft || 'file ' || df.FILE_ID || ' to ''' || diskpath (diskno) || prefix || SUBSTR (df.FILE_NAME, INSTR (df.FILE_NAME, '/', -1)) || ''';'); diskspace (diskno) := diskspace (diskno) - df.fsize; EXIT; ELSE IF diskno = diskspace.COUNT then DBMS_OUTPUT.put_line ('# no space for '||df.FILE_ID||' ' ||SUBSTR (df.FILE_NAME, INSTR (df.FILE_NAME, '/', -1))); END IF; END IF; END LOOP; END LOOP; END; / |