Title: Migrating a Database to New Mount Points

Author: Sanjay Garg, an Oracle DBA consultant for the Virginia Department of Transportation
in Richmond, Virginia.

Migrating a database to a new mount point can impose a lot of technical problems if it is not done with care, especially if the old
mount point has to be revoked first. There can be many reasons for this Migration: moving from a cold spot on disk to, hot;
moving from a test to a Production environment; organizational change; and changes in standards being few of them. This case
can be better explained with an example :


        Suppose a database DEVL has the following db files:

                /u01/oradata/DEVL/control01.ctl
                /u01/oradata/DEVL/control03.ctl
                /u01/oradata/DEVL/redo01a.log
                /u01/oradata/DEVL/redo02a.log
                /u01/oradata/DEVL/redo03a.log
                /u01/oradata/DEVL/redo04a.log
                /u01/oradata/DEVL/system01.dbf
                /u01/oradata/DEVL/rbs01.dbf
                /u10/oradata/DEVL/redo01b.log
                /u10/oradata/DEVL/redo02b.log
                /u10/oradata/DEVL/redo03b.log
                /u10/oradata/DEVL/redo04b.log
                /u10/oradata/DEVL/control02.ctl
                /u10/oradata/DEVL/dbh_data01.dbf
                /u10/oradata/DEVL/arch/
                /u11/oradata/DEVL/users01.dbf
                /u11/oradata/DEVL/tools01.dbf
                /u11/oradata/DEVL/temp01.dbf
                /u11/oradata/DEVL/dbh_idx01.dbf

        Suppose the mount points /u01, /u10 and  /u11 have to be
        moved as follows:

                /u01 has to be replaced by /oralog01/sun3000
                /u10 has to be replaced by /orahot10/sun3000
                /u11 has to be replaced by /oracld11/sun3000

        The best way to go through it is:

Step 1: List all existing datafiles related to the database.
        To be safe, it's always better to get the list
        of datafiles from the database.  You can use the
        following scripts to get the datafile names:

                SET PAGESIZE 0
                SET LINESIZE 2048
                SET HEADING OFF
                SET FEEDBACK OFF

                SPOOL datafile.dat

                SELECT SUBSTR(NAME,1,LENGTH(NAME))
                FROM V$DATAFILE
                /

                SELECT SUBSTR(MEMBER,1,LENGTH(MEMBER))
                FROM V$LOGFILE
                /

                SELECT SUBSTR(NAME,1,LENGTH(NAME))
                FROM V$CONTROLFILE
                /

                SPOOL OFF

Step 2: Do a cold backup of the database including init<SID>.ora
        and config<SID>.ora

Step 3: Edit config<SID>.ora file to change the name/destination
        of control file/s.
        In the above example, a typical scenario could
        be:

        control_files = (/oralog01/sun3000/oradata/DEVL/control01.ctl,
                         /oralog01/sun3000/oradata/DEVL/control03.ctl,
                         /orahot10/sun3000/oradata/DEVL/control02.ctl)

Step 4: Copy the control files to the destination specified in the
        new config<SID>.ora

Step 5: Start up the database in mount condition, with new
        init<SID>.ora and config<SID>.ora

Step 6: Copy the existing datafiles to their respective new locations.
        You might have to copy them back, from the backup tape (as
        mentioned in Step 2), if the previous mount points are
        destroyed.

Step 7: Rename the datafiles at this point, using
             "Alter database rename file ".

        Thus for the above example it could be:

        ALTER DATABASE RENAME FILE /u11/oradata/DEVL/users01.dbf to
        /oracld11/oradata/DEVL/users01.dbf.

                Repeat this step for all the datafiles.

Step 8: Open the database (Alter database open).