Moving Database files from one location to other location in a Planned way

Hi,
Whenever we get issue like some mount point is not getting sufficient in terms of disk space,we decide to move the Database files to a separate mount point.While doing this task all the end users or affected users should be informed well in advance and we have to ask for suitable downtime and duration considering the size of Data we are moving from one mount point to other and if possible we have to do such task at less peak hour of Database usage.Planning and creation of Scripts should be done well in advance,so that the actual process can be completed on time or much before time.
Step 1 and step2 can be planned and implemented well before the actual process can start.I'm doing this small test environment,Remember when we are doing such task in a very large size Database that time planning and implementing this small task on
time is very crucial.



1)Check the source location where Database files reside:
-------------------------------------------------------------------

SQL> select file_name||' '||tablespace_name||' '||bytes/1024/1024 as MB from dba_data_files;

MB
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/TESTDB/users01.dbf USERS 203.75
/u01/app/oracle/oradata/TESTDB/undotbs01.dbf UNDOTBS1 235
/u01/app/oracle/oradata/TESTDB/sysaux01.dbf SYSAUX 970
/u01/app/oracle/oradata/TESTDB/system01.dbf SYSTEM 940
/u01/app/oracle/oradata/TESTDB/example01.dbf EXAMPLE 102.5
/u01/app/oracle/oradata/TESTDB/apex01.dbf APEX 100

6 rows selected.

SQL> select file_name||' '||tablespace_name||' '||bytes/1024/1024 as MB from dba_temp_files;

MB
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/TESTDB/temp01.dbf TEMP 52

[oracle@host1 ~]$ df -h
Filesystem Size Used Avail Use% Mounted on
/dev/mapper/VolGroup00-LogVol00
18G 14G 2.6G 85% /
/dev/sda1 99M 20M 75M 21% /boot
tmpfs 940M 0 940M 0% /dev/shm
/dev/sdb1 20G 3.0G 16G 17% /u02



Step 2:Create the directory structure in /u02 where we can move database files.
-------
$cd /u02
$mkdir -p app/oracle/oradata/TESTDB
$cd app/oracle/oradata/TESTDB/
$pwd
/u02/app/oracle/oradata/TESTDB

Step 3: Shutdown the Database
---------

SQL> select name from v$database;

NAME
---------
TESTDB

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> exit

Step 4: Using 'mv' command move all database files(.dbf) files.
-------

$ cd /u01/app/oracle/oradata/TESTDB/
$ pwd
/u01/app/oracle/oradata/TESTDB
$ ls -altr
total 3016160
-rw-r----- 1 oracle oinstall 10043392 Jun 3 2010 control03.ctl
-rw-r----- 1 oracle oinstall 10493964 Sep 23 2010 test_01.dfb__old
-rw-r----- 1 oracle oinstall 10493874 Sep 23 2010 test_01.dfb
drwxr-x--- 4 oracle oinstall 4096 Mar 17 14:19 ..
drwxr-x--- 2 oracle oinstall 4096 Jun 2 19:51 .
-rw-r----- 1 oracle oinstall 52429312 Jun 9 19:56 redo02.log
-rw-r----- 1 oracle oinstall 52429312 Jun 9 19:56 redo02a.log
-rw-r----- 1 oracle oinstall 52429312 Jun 10 08:00 redo03.log
-rw-r----- 1 oracle oinstall 52429312 Jun 10 08:00 redo03a.log
-rw-r----- 1 oracle oinstall 54534144 Jun 10 15:30 temp01.dbf
-rw-r----- 1 oracle oinstall 213655552 Jun 10 15:43 users01.dbf
-rw-r----- 1 oracle oinstall 246423552 Jun 10 15:43 undotbs01.dbf
-rw-r----- 1 oracle oinstall 985669632 Jun 10 15:43 system01.dbf
-rw-r----- 1 oracle oinstall 1017126912 Jun 10 15:43 sysaux01.dbf
-rw-r----- 1 oracle oinstall 52429312 Jun 10 15:43 redo01.log
-rw-r----- 1 oracle oinstall 52429312 Jun 10 15:43 redo01a.log
-rw-r----- 1 oracle oinstall 107487232 Jun 10 15:43 example01.dbf
-rw-r----- 1 oracle oinstall 104865792 Jun 10 15:43 apex01.dbf
-rw-r----- 1 oracle oinstall 10043392 Jun 10 15:43 control01.ctl
$ mv *.dbf /u02/app/oracle/oradata/TESTDB/


Step 5:Verify at the target location(/u02)
-------
[oracle@host1 TESTDB]$ cd /u02/app/oracle/oradata/TESTDB/
[oracle@host1 TESTDB]$ pwd
/u02/app/oracle/oradata/TESTDB
[oracle@host1 TESTDB]$ ls -altr
total 2668428
-rw-r----- 1 oracle oinstall 54534144 Jun 10 15:30 temp01.dbf
drwxr-xr-x 3 oracle oinstall 4096 Jun 10 15:31 ..
-rw-r----- 1 oracle oinstall 213655552 Jun 10 15:43 users01.dbf
-rw-r----- 1 oracle oinstall 246423552 Jun 10 15:43 undotbs01.dbf
-rw-r----- 1 oracle oinstall 985669632 Jun 10 15:43 system01.dbf
-rw-r----- 1 oracle oinstall 1017126912 Jun 10 15:43 sysaux01.dbf
-rw-r----- 1 oracle oinstall 107487232 Jun 10 15:43 example01.dbf
-rw-r----- 1 oracle oinstall 104865792 Jun 10 15:43 apex01.dbf

Step 6:Use 'Alter database' commmand to rename or change the location in mount state
--------

$ sqlplus

SQL*Plus: Release 11.2.0.1.0 Production on Fri Jun 10 15:54:05 2011

Copyright (c) 1982, 2009, Oracle. All rights reserved.

Enter user-name: /as sysdba
Connected to an idle instance.

SQL> startup mount
ORACLE instance started.

Total System Global Area 764157952 bytes
Fixed Size 2217184 bytes
Variable Size 335547168 bytes
Database Buffers 423624704 bytes
Redo Buffers 2768896 bytes
Database mounted.

-- For single file The below command can be used.
SQL>alter database rename file '/u01/app/oracle/oradata/TESTDB/users01.dbf' to '/u02/app/oracle/oradata/TESTDB/users01.dbf';

Rename_datafile_TESTDB.sql:
---------------------------

-- Renaming Database files for TESTDB Database
alter database rename file '/u01/app/oracle/oradata/TESTDB/system01.dbf' to '/u02/app/oracle/oradata/TESTDB/system01.dbf';
alter database rename file '/u01/app/oracle/oradata/TESTDB/sysaux01.dbf' to '/u02/app/oracle/oradata/TESTDB/sysaux01.dbf';
alter database rename file '/u01/app/oracle/oradata/TESTDB/undotbs01.dbf' to '/u02/app/oracle/oradata/TESTDB/undotbs01.dbf';
alter database rename file '/u01/app/oracle/oradata/TESTDB/users01.dbf' to '/u02/app/oracle/oradata/TESTDB/users01.dbf';
alter database rename file '/u01/app/oracle/oradata/TESTDB/example01.dbf' to '/u02/app/oracle/oradata/TESTDB/example01.dbf';
alter database rename file '/u01/app/oracle/oradata/TESTDB/apex01.dbf' to '/u02/app/oracle/oradata/TESTDB/apex01.dbf';
alter database rename file '/u01/app/oracle/oradata/TESTDB/temp01.dbf' to '/u02/app/oracle/oradata/TESTDB/temp01.dbf';

[oracle@host1 SCRIPTS]$ chmod -R 775 /u02/SCRIPTS/

Make sure Rename_datafile_TESTDB.sql is having the execute privileges.

[oracle@host1 SCRIPTS]$ sqlplus "/as sysdba"

SQL*Plus: Release 11.2.0.1.0 Production on Fri Jun 10 16:08:21 2011

Copyright (c) 1982, 2009, Oracle. All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select name from v$database;

NAME
---------
TESTDB

SQL> @Rename_datafile_TESTDB.sql

Database altered.


Database altered.


Database altered.


Database altered.


Database altered.


Database altered.


Database altered.

Step 7:Open the Database and verify the new location for your Database files
-------
SQL> alter database open;

Database altered.

SQL> select tablespace_name||' '||file_name||' '||bytes/1024/1024 as MB from dba_data_files;

MB
--------------------------------------------------------------------------------
USERS /u02/app/oracle/oradata/TESTDB/users01.dbf 203.75
UNDOTBS1 /u02/app/oracle/oradata/TESTDB/undotbs01.dbf 235
SYSAUX /u02/app/oracle/oradata/TESTDB/sysaux01.dbf 970
SYSTEM /u02/app/oracle/oradata/TESTDB/system01.dbf 940
EXAMPLE /u02/app/oracle/oradata/TESTDB/example01.dbf 102.5
APEX /u02/app/oracle/oradata/TESTDB/apex01.dbf 100

6 rows selected.

SQL> select tablespace_name||' '||file_name||' '||bytes/1024/1024 as MB from dba_temp_files;

MB
--------------------------------------------------------------------------------
TEMP /u02/app/oracle/oradata/TESTDB/temp01.dbf 52

[oracle@host1 TESTDB]$ df -h
Filesystem Size Used Avail Use% Mounted on
/dev/mapper/VolGroup00-LogVol00
18G 12G 5.2G 69% /
/dev/sda1 99M 20M 75M 21% /boot
tmpfs 940M 0 940M 0% /dev/shm
/dev/sdb1 20G 5.6G 14G 30% /u02


Note: Moving or renaming a datafile while the database is in the 'OPEN' stage is also possible,But we have to make tablespace offline,When no downtime is accepted than we can go for the below steps.

The steps involved are:
-------------------------

1)Take the tablespace OFFLINE.
2)Use operating system commands to move or rename the files.
3)Use the ALTER TABLESPACE command to rename the file within the database.
4)Bring the tablespace back ONLINE.

sqlplus "/ as sysdba"

SQL> alter tablespace TEST_TB offline;

SQL> !mv /u05/app/oradata/TESTDB/user_data_01.dbf /u06/app/oradata/TESTDB/user_data_01.dbf

SQL> alter tablespace TEST_TB
rename datafile '/u05/app/oradata/TESTDB/user_data_01.dbf' to '/u06/app/oradata/TESTDB/user_data_01.dbf';

Bring the tablespace back online.

SQL> alter tablespace TEST_TB online;

SQL> exit

Hope it helps.


Best regards,

Rafi.