Thursday, January 05, 2012

Rename datafile on-the-flying

Imaging you accidentally append a space to the datafile...
Luckily, you have the database in archivelog mode.

Examples
1. Resize datafile if possible (for backup before rename: save space and faster copy)
SQL> ALTER DATABASE DATAFILE 'F:\oradata\live\Mydb02.ora ' RESIZE 50m; 
 
2. Take datafile offline:
SQL> ALTER DATABASE DATAFILE 'F:\oradata\live\Mydb02.ora ' OFFLINE;
Database altered.


3. at OS level, copy (preferable) or rename the file
 
cd  F:\oradata\live\
copy 'Mydb02.ora ' 'Mydb02.ora'
#cp -p 'Mydb02.ora ' 'Mydb02.ora'  (for unix like platform)
or move 'Mydb02.ora ' 'Mydb02.ora'
 
4. Rename the datafile in database to update control file 
 
ALTER DATABASE RENAME FILE 'F:\oradata\live\Mydb02.ora ' TO 'F:\oradata\live\Mydb02.ora
';
 
5. Take datafile online, when doing this, file recovery is needed to update the timestamp in the offline datafile header.:

SQL> ALTER DATABASE DATAFILE 'F:\oradata\live\Mydb02.ora' ONLINE;

ALTER DATABASE DATAFILE 'F:\oradata\live\Mydb02.ora' ONLINE
* ERROR at line 1: ORA-01113: file 16 needs media recovery
ORA-01110: data file 16: 'F:\ORADATA\LIVE\Mydb02.ORA'

 
SQL> RECOVER DATAFILE 'F:\oradata\live\Mydb02.ora';
 Media recovery complete.
 
SQL> ALTER DATABASE DATAFILE 'F:\oradata\live\Mydb02.ora' ONLINE;
Database altered
 
 
 
Ref:
1. ALTER DATABASE
2. Renaming or Moving Oracle Files
3. Move/rename datafiles in Oracle