Oracle database copy / clone online tool

Calculates space required, maps files to filesystems and generates RMAN, SQL and Unix scripts to rename datafiles

Oracle Knowledge Base      Skip reading, go to the Datafile rename / copy tool

This script was designed to assist Oracle DBA's in renaming datafiles during database copy, refresh or restore. It automates the boring task of calculating the space requirements for the destination filesystems and mapping datafiles to new locations. Supported script syntax is either SQL (ALTER DATABASE RENAME FILE ...), RMAN (SET NEWNAME FOR DATAFILE ...), Unix cp or rcp. All you have to do is to provide the list of datafiles with sizes in Kb (an SQL command is found in Step 1 below) in CSV format and list of destination filesystems with free available space in Kb (a shell command is found in Step 2 below) also in CSV format.

Rules and assumptions:

  • This tool doesn't attempt to balance the IO load between filesystems. It will simply fill up each filesystem at a time, in order you've listed them, up to the threshold you've specified (otherwise it will leave the default 0.1% of free space). Most people use RAID 5 for Oracle filesystems, so it should be ok. If it is important for some reason to distribute files in a specific way you would have to do it manually.

  • Neither does it try to squeeze files on the filesystems. If there is a bit of space left on a filesystem and the next file is larger than that free space - percent to keep free (if any) it will not attempt to find a smaller file to fit in there, it will move on to the next filesystem. If you are running short of space and want to utilize every kilobyte you will have to either map datafiles to filesystems manually or ask your manager to authorize purchase of new hard disks.

  • If no suffix for destination is provided the script will get suffix from the source. For example: if the source is /oracle/01/data/SOURCEDB and destination file system is /oracle/99 and no suffix is specified, the suffix will be: data/SOURCEDB and the absolute path to the new file will be: /oracle/99/data/SOURCEDB.

  • If no percent to keep free for destination filesystems is provided it will be set to 0.1%. You can also specify different threshold for each filesystem if you add it as the third parameter in filesystem list. For example: /oracle/99,15163240,20. In this case the script will keep 20% of free space on filesystem /oracle/99.

  • If no remote host is specified for rcp script, the remote server will be set to localhost

  • The script only supports Unix at this stage, if you want it for Windows you may just generate a Unix script and then use global search and replace in a text editor to convert it to DOS / Windows format.

    		•

    Step 1: Get list of datafiles and their sizes in Kb
    Use the SQL statements below to get the list of source data files.
    Run this from SQLPLUS:

    		   Step 2: Get list of filesystems and free space in Kb
    Use the command below to get the list of destination
    filesystems. Run this from Unix shell:
    set head off verify off feedback off pagesize 0
    select name || ',' || round(bytes/1024)
    from v$datafile;

    -- You don't need this for RMAN restore
    select b.member || ',' || round(a.bytes/1024)
    from v$log a, v$logfile b
    where a.group# = b.group#;

    		   #
    # You may need to adjust the command below
    # for your environment; add grep filters
    # take out or add some file systems etc.
    #
    df -k | grep oracle | \
    awk '{ printf ("%s,%s\n", $6, $4); }'
     
    Datafiles
    These should be in the form: datafile,size Kb i.e.
    /oracle/fs1/data/SOURCEDB/datafile_name.dbf,512000
    		   Destination filesystems
    These should be in the form: filesystem,freespace Kb i.e.
    /oracle/fs6,15163240
     
     
    Percent of free space to keep on the destination filesystem:  %
    Suffix for destination filesystem:
    For example: if one of the destination filesystems is /oracle/fs6 the suffix may be data/DESTDB This will form the absolute path
    to the new datafile location /oracle/fs6/data/DESTDB
     
    The output script(s) format: SQL      RMAN      cp      rcp 
    Command line options for cp: -f      -i     
    Command line options for cp or rcp: -p     
    Remote copy to / from (only for rcp):
    Remote host (only for rcp):