Tuesday 4 December 2012

ORA-01511: error in renaming log/data files ORA-01121: cannot rename data file – file is in use or recovery ORA-01110: data file : ‘datafile.dbf’



ORA-01511: error in renaming log/data files
ORA-01121: cannot rename 
data file – file is in use or recovery
ORA-01110: data file <string>: ‘datafile.dbf’


To Move the datafile or rename the datafile, follow this Steps And also related with the above mentioned Errors
1.    Login to SQLPlus.
2.    Connect as SYS DBA with 
CONNECT / AS SYSDBA 
3.    Shutdown database with
 SHUTDOWN 
4.    Rename or/and move the datafiles at operating system level.
5.    Start Oracle database in mount state with 
STARTUP MOUNT 
6.    Modify the name or location of datafiles in Oracle dictionary using following command syntax:
ALTER DATABASE RENAME FILE ‘<fully qualified path to original data file name>’ TO ‘<new or original fully qualified path to new or original data file name>’;
7.    Open Oracle database instance completely with 
ALTER DATABASE OPEN 
If the datafiles that need to be changed or moved do not belong to SYSTEM tablespaces, and do not contain active rollback segments or temporary segments, there is another workaround that does not require database instance to be shutdown. Instead, only the particular tablespace that contains the date files is taken offline.
1.    Login to SQLPlus.
2.    Connect as SYS DBA with 
CONNECT / AS SYSDBA .
3.    Make offline the affected tablespace with 
ALTER TABLESPACE <tablespace name> OFFLINE;
4.    Modify the name or location of datafiles in Oracle data dictionary using following  syntax:
ALTER TABLESPACE <tablespace name> RENAME DATAFILE ‘<fully qualified path to original data file name>’ TO ‘<new or original fully qualified path to new or original data file name>’;
5.    Bring the tablespace online again with
 ALTER TABLESPACE alter tablespace <tablespace name> ONLINE; 

No comments:

Post a Comment