2 minutes readHow to move a datafile from a file system to ASM?

1. create datafile in local file system
Example: alter tablespace users add datafile ‘/export/home/oracle/oracle/anup.dbf’ size 100m;

2. view the datafile location:

SQL> select name from v$datafile;

+DB_DATA/indian/datafile/system.256.736423549
+DB_DATA/indian/datafile/undotbs1.258.736423551
+DB_DATA/indian/datafile/sysaux.257.736423549
+DB_DATA/indian/datafile/users.259.736423551
+DB_DATA/indian/datafile/example.265.736423675
/export/home/oracle/oracle/anup.dbf

Task:
Now we move /export/home/oracle/oracle/anup.dbf datafile to ASM Storage.

Method:
We can move datafile from the file system in two ways.

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

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

A. 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.

2. Ensure you have enough space in the ASM diskgroup to copy the datafile

SQL> select file#, name, (bytes/1048576) File_Size_MB from v$datafile;
OR
export ORACLE_SID=+ASM

SQL> select NAME, STATE, TOTAL_MB, FREE_MB from v$asm_diskgroup;

3. Connect to RMAN and copy the datafile from the filesystem to the select ASM diskgroup
rman target=/

RMAN> copy datafile ‘/export/home/oracle/oracle/anup.dbf’ to ‘+DB_DATA’;

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

RMAN> switch datafile ‘/export/home/oracle/oracle/anup.dbf’ to copy;

5. The database may now be opened.

B. 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;

2. Ensure you have enough space in the ASM diskgroup to copy the datafile
SQL> select file#, name, (bytes/1048576) File_Size_MB from v$datafile;
OR
export ORACLE_SID=+ASMSQL> select NAME, STATE, TOTAL_MB, FREE_MB from v$asm_diskgroup;

3. Connect to RMAN and copy the datafile from the filesystem to the select ASM diskgroup
rman target=/

RMAN> copy datafile ‘/export/home/oracle/oracle/anup.dbf’ to ‘+DB_DATA’;

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

RMAN> switch datafile ‘/export/home/oracle/oracle/anup.dbf’ to copy;

5. place the tablespace online;

SQL> alter tablespace USERS online;

Ref: MN 390274.1