Translate this Page

 

There are 2 methods to move the data files of the tablespace to other location.

1 . When Database is Up (for some tablespace or datafile)

To move or rename the Data File  when database is up we should take the related tablespaces (eg. TESTD) offline, at this status we can’t do DML or SELECT to all objects that stored in that tablespace.

ATTENTION: You can use this way if the datafile that need to be changed or moved is not belong to SYSTEM tablespaces, and not contain active rollback segments or temporary segments.

  • Take the Tablespace Offline
    • [oracle@levicorp ~] sqlplus ‘/as sysdba’
    • SQL > ALTER TABLEPSPACE testd OFFLINE;

For check the status tablespace and datafile :

SQL> select tablespace_name, status from dba_tablespaces where tablespace_name = ‘TESTD’;

TABLESPACE_NAME               STATUS
——————————         ———
TESTD                                             OFFLINE

SQL> select tablespace_name, file_name, status from dba_data_files where tablespace_name = ‘TESTD’;

TABLESPACE_NAME                FILE_NAME                                   ONLINE_STATUS
——————————          ———————————–  ———————-
TESTD                                              /oracle/data01/test01.dbf      OFFLINE

If we try to access the objects that stored in tablespace TESTD, we will get error :

ORA-00376: file 520 cannot be read at this time
ORA-01110: data file 520: ‘/oracle/data01/test01.dbf’

  • Move the datafile from OS
    • [oracle@levicorp ~]mv /oracle/data01/test01.dbf /oracle/data02/test01.dbf

If you forget this step, on the next step you will get error :

ORA-01525: error in renaming data files
ORA-01141: error renaming data file 520 – new file ‘/oracle/data02/test01.dbf’ not found
ORA-01110: data file 520: ‘/oracle/data01/test01.dbf’
ORA-27037: unable to obtain file status
HP-UX Error: 2: No such file or directory
Additional information: 3

  • Rename datafile in Database
    • SQL> ALTER TABLESPACE testd RENAME DATAFILE ‘/oracle/data01/test01.dbf’  TO ‘/oracle/data02/test01.dbf’

  • Bring Back the Tablespace online
    • SQL> ALTER TABLESPACE testd ONLINE;

2. When the Database is down (for many tablespaces or datafiles)

  • Shutdown the database (don’t use startup abort!!!)
    • SQL> shutdown
  • Move the datafile from OS
    • [oracle@levicorp ~]mv /oracle/data01/test01.dbf /oracle/data02/test01.dbf
  • Startup mount the database
    • SQL> startup mount
  • Rename Datafile in Database
    • SQL> ALTER DATABASE RENAME FILE ‘/oracle/data01/test01.dbf’  TO ‘/oracle/data02/test01.dbf’

Watchout !!!

When database up, and you only take the datafile offline but the tablespace still online, we still can rename the datafile (not recommended) but the database should in “ARCHIEVELOG Mode On” if not you will get error :

SQL> ALTER DATABASE DATAFILE ‘/oracle/data01/test01.dbf’ OFFLINE;

ORA-01145: offline immediate disallowed unless media recovery enabled

If the database already in ARCHIEVELOG, after you rename the datafile and want to bring the datafile online again, you will get error :

ORA-1113: file /oracle/data01/test01.dbf needs media recovery

in Alert_SID.log :

ORA-1113 signalled during: alter database datafile ‘/oracle/data01/test01.dbf’ online

To solve this problem then you need to recover the tablespace :

SQL> recover tablespace testd;

For this reason why the database should be in ARCHIEVELOG Mode On… :-)

  3 Responses to “Move or Rename the Datafile in Oracle”

  1. Hello
    i have a question about last point when i put only the datafile offline and after the rename i have to do the recovery of datafile.
    what happens if in the meanwhile there is a switch of logfile ? i think that the recovery looks for the redo log online….and if it switches and go in offline redolog.how it brings the information for the recovery? is it a wrong question? let me know
    thanks
    Nick

  2. Hi,

    Very interesting. Nice article and discussion.
    Thanks a lot for same.

    Regards,
    Gitesh Trivedi
    http://www.dbametrix.com

  3. Hi Nick,
    Sorry for late reply. Thanks for your question. Remember, before you do datafile offline, your database should be in Archievelog mode. You can recover that datafile even the redolog switch many times.

 Leave a Reply

(required)

(required)

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

   
© 2012 LevICORP's webnote Suffusion theme by Sayontan Sinha