Running out of space? Want to move Oracle Datafiles?

Are you running out of space in the file system where database files (data files) are installed?

Try out this tip in order to move some of your data files from one drive to another and update the datafile location in your database.

The operating system: Oracle Enterprise Linux

The Database: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0

In our environment we have installed our database's files (data files) in /oracle/oradata/mydb location. The drive is getting up to 99% of utilization. Now we will move the system01.dbf from the above mentioned location to a new location in /oracle/hdb1/oradata/mydb location. /oracle/hdb1 is mounted from /dev/hdb1. The drive is nearly empty, that's why I chose it.

Now for the real moving part, we will perform the following steps:

  1. Login to SQL* Plus and shutdown the database

  2. Logout from SQL* Plus and move the files from the source directory to destination directory.

  3. Login to SQL* Plus as /nolog

  4. Connect as SYSDBA to the database to an idle instance.

  5. Issue startup mount command to bring up the database.

  6. Issue ALTER DATABASE RENAME command to rename the data files from the source directory to destination directory.

  7. Now finally open the database.

The above mentioned are the brief steps by which we can achieve our goal. Now to demonstrate see the following commands as well so that it serves as a live example:

Step 1

oracle@astrn10: sqlplus /nolog

SQL> conn /as sysdba

SQL> shutdown immediate;

Troubleshooting: If you are not able to get the SQL prompt, check your ORACLE_HOME, and ORACLE_SID.

Step 2

SQL> exit;

oracle@astrn10: mv /oracle/oradata/mydb/system01.dbf /oracle/hdb1/oradata/mydb/

Now check whether the file have been moved or not by issuing the following command:

oracle@astrn10: ls /oracle/hdb1/oradata/mydb/

total 429924352

-rw-r----- 1 oracle oinstall 429924352 Feb 12 11:00 system01.dbf

Now we are ready for the next step.

Step 3

oracle@astrn10: sqlplus /nolog

SQL>

Step 4

SQL> conn /as sysdba

Connected to idle instance.

Step 5

SQL> startup mount;

Database mounted.

Step 6

SQL> alter database rename file '/oracle/oradata/mydb/system01.dbf' to '/oracle/hdb1/oradata/mydb/system01.dbf';

Database altered.

Step 7

SQL> alter database open;

Database opened.

That's all. We are done with our agenda for moving data files from one drive to another. If this where Windows/any other operating system, then copy files as per your operating system commands/interface in Step 2.

In order to copy more files (in this demonstration we have moved only one file), then repeat Step #2 and Step # 6 for each file.

#End of tip


7 comments :

  1. This is a good one.. I'm Impressed

    ReplyDelete
  2. Excellent I always use this info as a quick reference guide..

    ReplyDelete
  3. Wonderful info...lifes so easy :)

    ReplyDelete
  4. Hi anantha...
    i am new to DBA.. While moving the tablespace into new location we can just do alter the tablespace into offline.. right.. why we need to shutdown the entire database .. i am asking as per my knowledge.. if we really shut the db in which scenario we have to do....

    ReplyDelete
  5. You can move datafiles when the database is open, too (not the system tablespace).

    To move the USERS tablespace from /data01/oradata/MYDB/data to /data02/oradata/MYDB/data:

    SQL>select tablespace_name, file_name from dba_data_files where tablespace_name = 'USERS';

    USERS /data01/oradata/MYDB/data/users01.dbf

    SQL>alter tablespace USERS offline normal;
    SQL>host mv /data01/oradata/MYDB/data/users01.dbf /data02/oradata/MYDB/data/users01.dbf
    SQL>alter database rename file '/data01/oradata/MYDB/data/users01.dbf' to '/data02/oradata/MYDB/data/users01.dbf';
    SQL>alter tablespace USERS online;

    ReplyDelete
  6. Is the same process to move the Control files and the redo log files?

    ReplyDelete
  7. Try this for Control & Redo Files

    Control Files
    -------------
    SQL> show parameter control_files

    SQL> ALTER SYSTEM SET control_files='c:\file1.ctl', - 'c:\file2.ctl', 'c:\file3.ctl' SCOPE=SPFILE;
    System altered.

    SQL> SHUTDOWN IMMEDIATE
    Database closed.
    Database dismounted.
    ORACLE instance shut down.
    SQL> HOST MOVE c:\file1.ctl c:\new_file1.ctl
    SQL> STARTUP
    SQL> select name from v$controlfile;

    Redo Files
    ----------

    SQL> SELECT member FROM v$logfile;
    SQL> SHUTDOWN IMMEDIATE
    Database closed.
    Database dismounted.
    ORACLE instance shut down.

    SQL> HOST MOVE c:\redolog.log c:\new_redolog1.log

    SQL> STARTUP MOUNT
    SQL> ALTER DATABASE RENAME FILE 'c:\redolog.log' TO 'c:\new_redolog1.log';
    SQL> ALTER DATABASE OPEN;
    SQL> SELECT member FROM v$logfile;

    Thanks.

    ReplyDelete