Rename or Move Datafiles and Logfiles

 To rename or relocate datafiles in the SYSTEM tablespace one must use option II, 'Renaming or Moving a Datafile with the Database Shut Down', because you cannot take the SYSTEM tablespace offline.

I. RENAME OR MOVE DATAFILE(S) WITH THE DATABASE OPEN

To do this you must follow these steps:

1. Determine how many datafiles are associated with the tablespace.

       > SELECT FILE_NAME, STATUS FROM DBA_DATA_FILES

                 WHERE TABLESPACE_NAME = '';

2. Keep tablespace OFFLINE before moving in OS LEVEL

      > ALTER TABLESPACE  SCOTT_TABLESPACE OFFLINE;

   * At this point the tablespace is not accessible to users.

3. Go to OS box and run move command

MV /u02/oradata/scott_data.dbf /u03/oradata/datafile/scott_data.dbf

4. Once moving done on OS level, run followings:

* This updates the entry for the datafile(s) in the  controlfile.

       > ALTER DATABASE RENAME FILE

                 '/u02/oradata/scott_data.dbf ' TO '/u03/oradata/datafile/scott_data.dbf’;

     

5. Once the alter database statement has been processed for the datafile(s) you can bring the tablespace online.

       > ALTER TABLESPACE YOUR_TABLESPACE_NAME ONLINE;

 6. You can also verify that the controlfile has been updated by doing the

    Following:

        > ALTER DATABASE BACKUP CONTROLFILE TO TRACE;

    This will produce a readable copy of the contents of your controlfile

    which will be placed in your user_dump_dest directory.

    Optionally, you can query V$DATAFILE, which gets information from the

    controlfile as well.

II. RENAME AND OR MOVE DATAFILE(S) WITH THE DATABASE SHUT DOWN

1. If the database is up, shut it down.

2. Copy the datafile to the new name/location at operating system level.

3. Mount the database.

        > STARTUP MOUNT                                                          

         

   This command will read the control file but will not mount the datafiles.   

4. Rename the file inside Oracle.

        > ALTER DATABASE RENAME FILE

                 '/FULL_PATH_OF_OLD_LOCATION/AND_DATAFILE_NAME.DBF'

                 TO

                 '/FULL_PATH_OF_NEW_LOCATION/AND_DATAFILE_NAME.DBF';

   Do this for all the datafiles that were renamed or moved at the operating

   system level.

5. Open the database.

        > ALTER DATABASE OPEN;

6. Query v$dbfile to confirm that the changes made were correct.

        > SELECT * FROM V$DBFILE;

7. Remove the datafile(s) from the old location at the operating system level.

 

III. RENAME AND OR MOVE A LOGFILE

1. Shutdown the database. 

2. Copy the logfile to the new name/location at operating system level.

3. Mount the database.

        > STARTUP MOUNT

4. Rename the file.

       > ALTER DATABASE RENAME FILE

                 '/FULL_PATH_OF_OLD_LOCATION/AND_REDO_LOG_NAME.LOG'

                 TO

                 '/FULL_PATH_OF_NEW_LOCATION/AND_REDO_LOG_NAME.LOG';

5. Open the database.

        > ALTER DATABASE OPEN;

6. Remove the logfile(s) from the old location at the operating system level.