Friday, September 19, 2008

Moving a datafile to another ASM group

# set tablespace read only
sqlplus
connect / as sysdba
alter tablespace bi_mifid_stg_lob read only;

# datafile offline
alter database datafile '+ASM_ORADATA52/p01bie/datafile/bi_mifid_stg_lob.267.663234691' offline;

# copy datafile
RMAN
connect target /
copy datafile '+ASM_ORADATA52/p01bie/datafile/bi_mifid_stg_lob.267.663234691' to '+ASM_ORADATA51';

# rename datafile
switch datafile '+ASM_ORADATA52/p01bie/datafile/bi_mifid_stg_lob.267.663234691' to copy;
# or
alter database rename datafile
'+ASM_ORADATA52/p01bie/datafile/bi_mifid_stg_lob.267.663234691'
to
'ASM_ORADATA51/p01bie/datafile/bi_mifid_stg_lob.....';

# recover datafile
recover datafile 'ASM_ORADATA51/p01bie/datafile/bi_mifid_stg_lob.....';

# datafile online
sqlplus
connect / as sysdba
alter database datafile 'ASM_ORADATA51/p01bie/datafile/bi_mifid_stg_lob.....' online;

# tablespace read write
alter tablespace bi_mifid_stg_lob read write;