Thursday, April 10, 2008

Relocate Datafiles in Oracle Database.

Rename/relocate datafiles operation vary based on the log mode of the database. Database may be in archival mode or noarchive mode. In order to know the log mode of the database issue,
SELECT LOG_MODE FROM V$DATABASE;
If you get ARCHIVELOG the follow A).
If you get NOARCHIVELOG then follow B).
A)Archival Mode Operation
1)Determine the datafile on which you will do the operation,

SQL> select file_name,file_id from dba_data_files where tablespace_name='TEST_MOVE';

FILE_NAME FILE_ID
------------------------------------------ -----------------
/oradata/Arju/test_move.dbf 5

2)Take the tablespace of the specified datafile offline. Like, in case of Archivelog use,

SQL> alter database datafile 5 offline;
Database altered.

To use the form of the ALTER DATABASE statement, the database must be in ARCHIVELOG mode. This requirement prevents you from accidentally losing the datafile, since taking the datafile offline while in NOARCHIVELOG mode is likely to result in losing the file.

3)Move the datafile by using OS(cp/scp/copy) command or by using DBMS_FILE_TRANSFER package to you desired location.

SQL> !scp /oradata/Arju/test_move.dbf /oradata/Arju/arju/after_move.dbf

4)Rename the datafiles within the database.

SQL> alter database rename file '/oradata/Arju/test_move.dbf' to '/oradata/Arju/arju/after_move.dbf';
Database altered.

5)Recover the Datafile if needed.

SQL> alter database recover datafile 5;

Database altered.

6)Query the Data Dictionary to see the new Location.

SQL> select file_name,file_id from dba_data_files where tablespace_name='TEST_MOVE';

FILE_NAME FILE_ID
-------------------------------------------------
/oradata/Arju/arju/after_move.dbf 5

7)Backup the database.

Noarchival Mode operation
1)Shutdown the database.
SQL>Shut immediate;

2)Copy the desired datafile to your new location.
You can use cp/scp/copy/dd command or any GUI tool to copy datafile to new location.

3)Mount the database.
SQL>startup mount

4)Rename the datafile to update the controlfile information.
SQL> ALTER DATABASE RENAME FILE 'old_location' TO 'new_location';

5)Open the database for normal use.
SQL>Alter database open;

Related Documents
http://arjudba.blogspot.com/2008/08/how-to-resize-datafile.html

No comments:

Post a Comment