Tuesday, February 24, 2015

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



No comments: