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:
Login to SQL* Plus and shutdown the database
Logout from SQL* Plus and move the files from the source directory to destination directory.
Login to SQL* Plus as /nolog
Connect as SYSDBA to the database to an idle instance.
Issue startup mount command to bring up the database.
Issue ALTER DATABASE RENAME command to rename the data files from the source directory to destination directory.
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
This is a good one.. I'm Impressed
ReplyDeleteExcellent I always use this info as a quick reference guide..
ReplyDeleteWonderful info...lifes so easy :)
ReplyDeleteHi anantha...
ReplyDeletei 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....
You can move datafiles when the database is open, too (not the system tablespace).
ReplyDeleteTo 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;
Is the same process to move the Control files and the redo log files?
ReplyDeleteTry this for Control & Redo Files
ReplyDeleteControl 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.