Database

Move and Rename Datafile on Oracle Database

As the Database Administrator, you might want to move datafile from one location (path/directory/file system) to other rename the datafile in order to reorganize the datafiles/files structure on your database. Basically, moving database as same as renaming the datafile itself. It’s analogically same with “mv” command in Unix OS that can be use for both moving file and renaming file.

Here’s the step if we want to move/rename datafile from ‘/mnt1/oradata/ins1/data01.dbf’ to ‘/mnt2/oradata/ins1/data01.dbf’

In the NOARCHIVELOG Database

  1. Shutdown the database
    SQL> shutdown immediate
  2. Move the datafile. You may use this command in Unix
    mv /mnt1/oradata/ins1/data01.dbf /mnt2/oradata/ins1/data01.dbf
  3. Startup mount the database
     SQL> startup mount
  4. Rename datafile in the database level
    SQL> alter database rename file '/mnt1/oradata/ins1/data01.dbf' to '/mnt2/oradata/ins1/data01.dbf';
  5. Last, open the database
    SQL> alter database open;

In the ARCHIVELOG database:

  1. Make the datafile offline
    SQL> alter database datafile '/mnt1/oradata/ins1/data01.dbf' offline;
  2. Move the datafile. You may use this command in Unix
    mv /mnt1/oradata/ins1/data01.dbf /mnt2/oradata/ins1/data01.dbf
  3. Rename datafile in the database level
    SQL> alter database rename file '/mnt1/oradata/ins1/data01.dbf' to '/mnt2/oradata/ins1/data01.dbf';
  4. Recover datafile that has been renamed
    SQL> recover datafile '/mnt2/oradata/ins1/data01.dbf';
  5. Last, make the renamed datafile online
    alter database datafile '/mnt2/oradata/ins1/data01.dbf' online;

For your notes :
In the NONARCHIVELOG database, you may not be able to access the database because the database has been shutdown (there’s a downtime)
In the ARCHIVELOG database, the database itself still can be accessed & queried expect for data that is stored in those particular datafile. For example if tablespace DATA have three datafiles (data01.dbf, data02.dbf & data03.dbf) then we can’t access the data that is stored in the data01.dbf, but we can still access/query data on datafile data02.dbf or data03.dbf.

Advertisements

Discussion

No comments yet.

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Archives

Twitter Updates

Error: Twitter did not respond. Please wait a few minutes and refresh this page.

%d bloggers like this: