Oracle allows for the movement of datafiles from one storage device to another when you exhaust space on the original device without having to recreate your database. In this procedure we will move the datafiles located on LUN data4 to a new LUN on data1.
1. Find all datafiles located on mount point /data4 and view size in MB.
SQL> select tablespace_name, bytes/1024/1024, file_name from dba_data_files where
file_name like ‘%data3%’;
SYSTEM /data4/oradata/system01.dbf
DRSYS /data4/oradata/drsys01.dbf
TOOLS /data4/oradata/tools01.dbf
OEM /data4/oradata/oem01.dbf
USERDATA /data4/oradata/userdata_01.dbf
USERDATA /data4/oradata/userdata_02.dbf
USERDATA /data4/oradata/userdata_03.dbf
USERDATA /data4/oradata/userdata_04.dbf
SQL> select SUM(bytes)/1024/1024 from dba_data_files
13:15:46 2 where file_name like ‘%data4%’;
SUM(BYTES)/1024/1024
——————–
32546
2. The data files in tablespace USERDATA amount to 32GB of space,
we will move these data files to the new mount point /data1.
3. Shutdown the oracle database with the command ‘shutdown immediate’.
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
4. Startup the oracle database in restricted mode, this will prevent non-DBA users from accessing the database while you moving the data files. Use the command ‘startup restrict’.
SQL> startup restrict
ORACLE instance started.
Total System Global Area 612368384 bytes
Fixed Size 1250428 bytes
Variable Size 155192196 bytes
Database Buffers 448790528 bytes
Redo Buffers 7135232 bytes
Database mounted.
Database opened.
SQL>
5. Take the tablespace USERDATA offline.
SQL> alter tablespace USERDATA offline;
Tablespace altered.
SQL>
6. View the status of the tablespace USERDATA to ensure that it is in an offline state.
SQL> select tablespace_name, status from dba_tablespaces where tablespace_name =
‘USERDATA’;
TABLESPACE_NAME STATUS
—————————— ———
USERDATA OFFLINE
SQL>
7. Shutdown the database with the command ‘shutdown immediate’.
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
8. Move the data files of tablespace USERDATA to their new locations with OS command ‘cp’. One could use the command mv, which is normally faster, however if anything goes wrong you do not have a backup of your datafile.
cp /data4/oradata/userdata_01.dbf /data1/oradata/userdata_01.dbf
cp /data4/oradata/userdata_02.dbf /data1/oradata/userdata_02.dbf
cp /data4/oradata/userdata_03.dbf /data1/oradata/userdata_03.dbf
cp /data4/oradata/userdata_04.dbf /data1/oradata/userdata_04.dbf
9. Open the database in a nomount state with ‘startup nomount’.
SQL> startup nomount;
ORACLE instance started.
Total System Global Area 612368384 bytes
Fixed Size 1250428 bytes
Variable Size 155192196 bytes
Database Buffers 448790528 bytes
Redo Buffers 7135232 bytes
SQL>
10. Change the location of the data files moved in the control file with the command ‘alter database rename datafile’.
SQL> alter database rename datafile ‘/data4/oradata/userdata_01.dbf’ to
‘/data1/oradata/userdata_01.dbf’;
Database alerted.
SQL> alter database rename datafile ‘/data4/oradata/userdata_02.dbf’ to
‘/data1/oradata/userdata_02.dbf’;
Database alerted.
SQL> alter database rename datafile ‘/data4/oradata/userdata_03.dbf’ to
‘/data1/oradata/userdata_03.dbf’;
Database alerted.
SQL> alter database rename datafile ‘/data4/oradata/userdata_04.dbf’ to
‘/data1/oradata/userdata_04.dbf’;
Database alerted.
11. Mount the database with the command ‘alter database mount’.
SQL> alter database mount;
Database altered.
SQL>
12. Open the database with command ‘alter database open’.
SQL> alter database open;
Database altered.
SQL>
13. Bring the tablespaces back online.
SQL> alter tablespace USERDATA online;
Tablespace altered.
SQL>
14. Validate that the tablespace is online with the following SQL.
SQL> select tablespace_name, status from dba_tablespaces where tablespace_name
2 ‘USERDATA’;
TABLESPACE_NAME STATUS
—————————— ———
USERDATA ONLINE
SQL>
Completes movement of Oracle data files.
Larry J Catt, OCP 9i, 10g
oracle@allcompute.com
www.allcompute.com