Moving Datafile from File System to ASM

If datafile is created on file system instead of ASM, following procedure can be applied to move datafile to ASM


SQL > select NAME,STATUS, ENABLED from v$datafile where TS#=nn

NAME                                                                       STATUS         ENABLED

+DATA/DBNAME/datafile/TS_NAME.nnn.nnn         ONLINE         READ WRITE

+DATA/DBNAME/datafile/TS_NAME .nnn.nnn         ONLINE         READ WRITE

SQL > ALTER TABLESPACE TS_NAME ADD DATAFILE 'DATA' SIZE 1M AUTOEXTEND ON NEXT 1M MAXSIZE 10M;

NAME                                                                              STATUS         ENABLED

+DATA/DBNAME/datafile/TS_NAME.nnn.nnn               ONLINE         READ WRITE

+DATA/DBNAME/datafile/TS_NAME.nnn.nnn               ONLINE         READ WRITE

/u01/app/.../DBNAME/dbs/DATA                             ONLINE         READ WRITE  <- asm="" at="" created="" file="" font="" instead="" of="" system=""> 

Take the datafile to offline:

SQL> alter database datafile '/u01/app/.../DBNAME/dbs/DATA' offline drop;

Database altered.

SQL > select NAME, STATUS, ENABLED from v$datafile where TS#=nn

NAME                                                                        STATUS            ENABLED

+DATA/DBNAME/datafile/TS_NAME.nnn.nnn          ONLINE         READ WRITE

+DATA/DBNAME/datafile/TS_NAME.nnn.nnn          ONLINE         READ WRITE

/u01/app/.../DBNAME/dbs/DATA                        RECOVER       READ WRITE

Copy datafile to ASM using RMAN. If you use asmcmd for file copy then file will be created as non-oracle managed file (with alias)

RMAN> COPY DATAFILE ' /u01/app/.../DBNAME/dbs/DATA' TO '+DATA';

Starting backup at ....

allocated channel...

...

channel ORA_DISK_1: starting datafile copy

input datafile file number=nnnnn name= /u01/app/.../DBNAME/dbs/DATA

output file name=+DATA/DBNAME/datafile/TS_NAME.nnn.nnn  tag=...

channel ORA_DISK_1: datafile copy complete, elapsed time: ...

Finished backup ...

Update file location

SQL> alter tablespace TS_NAME  rename  datafile '/u01/app/.../DBNAME/dbs/DATA' to '+DATA/DBNAME/datafile/TS_NAME.nnn.nnn';

Tablespace altered.

Recover datafile and bring it online

SQL> recover datafile '+DATA/DBNAME/datafile/TS_NAME.nnn.nnn';

Media recovery complete.

SQL > alter database datafile '+DATA/DBNAME/datafile/TS_NAME.nnn.nnn' online;

Database altered.

SQL > select NAME,CREATION_TIME, STATUS, ENABLED from v$datafile where TS#=nn

NAME                                                                        STATUS            ENABLED

+DATA/DBNAME/datafile/TS_NAME.nnn.nnn          ONLINE         READ WRITE

+DATA/DBNAME/datafile/TS_NAME.nnn.nnn          ONLINE         READ WRITE

+DATA/DBNAME/datafile/TS_NAME.nnn.nnn          ONLINE         READ WRITE