How to move a datafile from ASM to the file system [ID 390416.1]

Applies to:

Oracle Server - Enterprise Edition - Version: 10.1.0.2 to 10.2.0.3
Information in this document applies to any platform.

Goal

This note will guide a DBA in moving a datafile currently located in a ASM diskgroup to a file system.

Solution

How to move a datafile from ASM to a file system.

 

Moving a datafile from ASM to the file system can be performed in two ways:

i. While the database is shutdown (in mount stage). This is the only option if datafiles to be
 moved are from system or undo tablespaces.

ii. While the database is running (with the selected tablespace offline).

-------------------------------------------------------------------------------------------------------------------------------

i. While the database is shutdown (in mount stage).


Moving oracle datafile while the database is in mount stage is performed in the following way:

1. Shutdown and mount the database.

[oracle@linux] sqlplus '/as sysdba'

SQL> shutdown immediate;

SQL> startup mount;

2. Ensure you have enough space on the file system to copy the datafile.

First identify the size of the datafile you wish to move.

SQL> select file#, name, (bytes/1048576) File_Size_MB from v$datafile;

FILE#  NAME                                           FILE_SIZE_MB
-----  ---------------------------------------------  ------------
...
4      +DGROUP2/PROD/datafile/users01.258.600351265   2500
...

* In this example we will be moving the users01 datafile.


[oracle@linux] df -k

Filesystem                  1K-blocks      Used      Available   Use% Mounted on
/dev/vg01/root              10321208       3716884   6080036     38%  /
/dev/sda1                   147766         15521     124616      12%  /boot
none                        1022976        0         1022976     0%   /dev/shm
/dev/vg01/tmp               2064208        58380     1900972     3%   /tmp
/dev/vg01/u01               20158332       18557600  576732      97%  /u01
/dev/vg01/backups           6092608        5266016   517140      92%  /backups
/dev/vg01/oradata           15487600       11876492  2824676     81%  /oradata
/dev/vg01/recovery_area     15487600       11465932  3235236     78%  /recovery_area

3. Connect to RMAN and copy the datafile from the ASM diskgroup to the filesystem.

[oracle@linux] rman target=/

RMAN> copy datafile 4 to '/oradata/PROD/users01.dbf';

Starting backup at 2006/09/06 15:46:13
using target database controlfile instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=156 devtype=DISK
channel ORA_DISK_1: starting datafile copy
input datafile fno=00004 name=+DGROUP3/rman/datafile/users.258.600450259
output filename=/oradata/PROD/users01.dbf tag=TAG20060906T154614 recid=22 stamp=600450375
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 2006/09/06 15:46:15

4. Update the controlfile with the new location of the datafile.

[oracle@linux] sqlplus '/as sysdba'

SQL> alter database rename file '+DGROUP3/PROD/datafile/users.258.600450259' to '/oradata/PROD/users01.dbf';

Database altered.

5. The file is now if the new location.

SQL> select file#, name from v$datafile;

FILE#    NAME
------   ------------------------------------------------------------------
..
4        /oradata/PROD/users01.dbf
..

6. The database may now be opened.


While the database is running (with the select tablespace offline).


In order to move a datafile on a running active database the tablespace where the datafile resides must be placed offline.

1. Identify the tablespace which contains the datafile and offline the tablespace.

SQL> select tablespace_name, file_name from dba_data_files where file_id=4;

TABLESPACE_NAME    FILE_NAME
------------------ ------------------------------------------
USERS              +DGROUP3/PROD/datafile/users.258.600450259


SQL> alter tablespace USERS offline;

 

* * * * * Continue with Steps 2 - 5 above. * * * * *

6. After you have successfully completed the above steps (2 -5) place the tablespace online;

SQL> alter tablespace USERS online;

The datafile has now been successfully moved to the ASM diskgroup.