Thursday, February 9, 2012

Rename/Move Datafile without putting Tablespace offline.

Below is a Demo where I had to Move datafile . its always a best practice & safe to offline whole Tablespace. But sometimes we don't get that liberty. Just for experimental purpose I Move/rename single Datafile of Tablespace which has 8 more Datafiles. The method dont applied to database running on Noarchivelog mode,because Recovery will be needed.

=======================================
Offline datafile. 
=======================================
SQL> ALTER DATABASE DATAFILE 62 OFFLINE;

Database altered.

=======================================
Move datafile 
=======================================
[NEW_DB](SERVERABC)/u01/oradata03/OLD_DB-> cp /u01/oradata03/OLD_DB/grr_04_d_17.dbf /u01/oradata01/NEW_DB/

=======================================
Rename datafile 
=======================================
SQL> ALTER DATABASE RENAME FILE '/u01/oradata03/OLD_DB/grr_04_d_17.dbf' TO '/u01/oradata01/NEW_DB/grr_04_d_17.dbf';

Database altered.

SQL> ALTER DATABASE DATAFILE 62 ONLINE;
ALTER DATABASE DATAFILE 62 ONLINE
*
ERROR at line 1:
ORA-01113: file 62 needs media recovery if it was restored from backup, or END
BACKUP if it was not
ORA-01110: data file 62: '/u01/oradata01/NEW_DB/grr_04_d_17.dbf'

=======================================
Recover datafile
=======================================

SQL> recover datafile 62;

Media recovery complete.

=======================================
Online datafile
=======================================

SQL> ALTER DATABASE DATAFILE 62 online;

No comments:

Post a Comment