Rename or Move Datafiles and LogFiles In many situations

Managing Tablespaces and DataFiles

In many situations, a datafile or logfile must be renamed inside Oracle. Whereas the contents of the file remain valid, you need to define a new physical name or location for it. For example:

- You want to move a database file to a different disk for performance or maintenance reasons.
- You have restored a datafile from backup, but the disk where it should normally be placed has crashed and you need to relocate it to a different disk.
- You have moved or renamed a datafile at operating system level but forgot to rename it within Oracle. At startup, you get ORA-01157 and ORA-01110. If the database is up and you try to shut it down normal or immediate, you get ORA-01116 ad ORA-01110.
- You have multiple databases on the same machine and you need to rename certain database files to prevent collision and confusion.

RENAME OR MOVE A DATAFILE FOR A SINGLE TABLESPACE
1. Make the tablespace offline.
ALTER TABLESPACE <TABLESPACE_NAME> OFFLINE NORMAL;

2. Copy the datafile(s) to the new location using the operating system copy command. Once the datafile(s) have been copied to the new location compare the sizes of the datafiles. Make sure that the sizes match.

3. Rename the datafile to the new location. This updates the entry for the datafile(s) in the controlfile.
ALTER TABLESPACE <TABLESPACE_NAME> RENAME DATAFILE '/FULL_PATH_OF_OLD_LOCATION/AND_DATAFILE_NAME.DBF' TO '/FULL_PATH_OF_NEW_LOCATION/AND_DATAFILE_NAME.DBF';

4. Once the alter database statement has been processed for the datafile(s) you can bring the tablespace online.
ALTER TABLESPACE <TABLESPACE_NAME> ONLINE;

Another method is to have the database in mount state:
1. SHUTDOWN IMMEDIATE
2. STARTUP MOUNT
3. Copy the datafile(s) to the new location using the operating system copy command
4. ALTER DATABASE RENAME FILE '/FULL_PATH_OF_OLD_LOCATION/AND_DATAFILE_NAME.DBF' TO
'/FULL_PATH_OF_NEW_LOCATION/AND_DATAFILE_NAME.DBF';
5. ALTER DATABASE OPEN;

RENAME OR MOVE DATAFILE(S) WITH THE DATABASE OPEN
Datafiles can be renamed or moved while the database is open. However, the tablespace must be made READ-ONLY. This will allow users to select from the tablespace, but prevents them from doing inserts, updates, and deletes. The amount of time the tablespace is required to be read only will depend on how large the datafile(s) are and how long it takes to copy the datafile(s) to the new location. Making the tablespace read only freezes the file header, preventing updates from being made to the file header. Since this datafile is then at a read only state, it is possible to copy the file while the database is 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 = '<YOUR_TABLESPACE_NAME>';

2. Make sure that all datafiles returned have the status AVAILABLE.

3. Make the tablespace is read only.
ALTER TABLESPACE <TABLESPACE_NAME> READ ONLY;

4. Make sure that the tablespace is defined as read only in the datadictionary.
SELECT TABLESPACE_NAME, STATUS FROM DBA_TABLESPACES
WHERE TABLESPACE_NAME = '<TABLESPACE_NAME>';

TABLESPACE_NAME STATUS
------------------------------ ---------
<YOUR_TABLESPACE_NAME> READ ONLY

5. Copy the datafile(s) to the new location using the operating system copy command. Once the datafile(s) have been copied to the new location compare the sizes of the datafiles. Make sure that the sizes match.

6. Once the datafiles have been copied to the new location alter the tablespace offline.
ALTER TABLESPACE <TABLESPACE_NAME> OFFLINE;

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

7. Once the tablespace is offline you will need to rename the datafile(s) to the new location. This updates the entry for the datafile(s) in the controlfile.
ALTER DATABASE RENAME FILE '/FULL_PATH_OF_OLD_LOCATION/AND_DATAFILE_NAME.DBF' TO
'/FULL_PATH_OF_NEW_LOCATION/AND_DATAFILE_NAME.DBF';

* You will need to do this for all datafiles associated with this tablespace.

8. Once the alter database statement has been processed for the datafile(s) you can bring the tablespace online.
ALTER TABLESPACE <TABLESPACE_NAME> ONLINE;

9. After you bring the tablespace back online you can make the tablespace read/write again.
ALTER TABLESPACE <TABLESPACE_NAME> READ WRITE;

10. You can check the status of the tablespace to make sure it is read/write. 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.

11. Remove the datafile(s) from the old location at the O/S level.

RENAME OR MOVE DATAFILE(S) OR LOGFILES WITH THE DATABASE SHUT DOWN
This includes the rollback segment datafile.
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.

ADD A DATAFILE
Adding a File is very simple:
ALTER TABLESPACE <TABLESPACE_NAME> ADD DATAFILE '<file specific> SIZE xxxM;

DROP A TABLESPACE
Dropping a Tablespace is very simple:
DROP TABLESPACE <TABLESPACE_NAME> INCLUDING CONTENTS AND DATAFILES CASCADE;

You can use just DROP TABLESPACE <tablespace name>, but the data files won’t be dropped and the tablespace must be empty (no objects exist in the tablespace); otherwise, Oracle will return an error. Further, the DROP TABLESPACE <tablespace name> INCLUDING CONTENTS command drops all the objects, if they exist in the tablespace. Next, by including the CASCADE option you can drop foreign key references from objects in the other tablespaces to the one you’re dropping. The DATAFILES option, which is available in Oracle9i, will physically delete the related data files as well.

CREATE A TABLESPACE
Create tablespace <TABLESPACE_NAME>
DataFile '/opt/oracle/oradata/FGUARD/fg_data01.dbf' Size 800M AutoExtend On Next 100M Maxsize 2000M,
'/opt/oracle/oradata/FGUARD/fg_data02.dbf' Size 800M AutoExtend On Next 100M Maxsize 2000M
EXTENT MANAGEMENT LOCAL
SEGMENT SPACE MANAGEMENT AUTO;