Tuesday, January 15, 2013

Moving datafile to a different location

Moving datafile involves 4 steps:
  1. Taking datafile offline
  2. Moving the file to a new location
  3. Recovering the datafile
  4. Bringing datafile online
In order to perform the steps above, the database has to be in ARCHIVELOG mode. In order to check, run the following:
The output will be something like that:
Database log mode              No Archive Mode
Automatic archival             Disabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     56
Current log sequence           58
Enable ARCHIVELOG mode and peform the following:
ALTER DATABASE DATAFILE '/u01/oradata/tbs01.dbf' OFFLINE;
ALTER DATABASE RENAME FILE '/u01/oradata/tbs01.dbf' TO '/u40/oradata/Northwind/tbs01.dbf';
RECOVER DATAFILE '/u01/oradata/Northwind/tbs01.dbf';
ALTER DATABASE DATAFILE '/u01/oradata/Northwind/tbs01.dbf' ONLINE;

before you rename the file, make sure you run the following command on the UNIX side to ensure the file on the OS has been moved:
cp /u01/oradata/tbs01.dbf /u01/oradata/Northwind/tbs01.dbf
If you do not move OS file first, you will get an error in SQL Plus while attempting to run ALTER DATABASE RENAME statement
ORA-01511: error in renaming log/data files 
ORA-01516: nonexistent log file, datafile, or tempfile "/u01/oradata/Northwind/tbs01.dbf"
When done, run the statement below to ensure that all your datafiles are in the correct place:


SELECT * FROM V$DBFILE; (for older versions)

No comments:

Post a Comment