ASM data File Migration (Asm–>os)

--View current situation

Sql> Select COUNT (*) from HR.A;

COUNT (*)

----------

1580

Sql> select name from V$datafile;

NAME

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

+data/tasm/system01.dbf

+data/tasm/undotbs01.dbf

+data/tasm/sysaux01.dbf

+data/tasm/users01.dbf

+data/tasm/example01.dbf

+dg2/tasm/datafile/xff.256.754902279

6 rows selected.

--Create a table of contents

Sql> Create directory Asmsrc as ' +dg2/tasm/datafile ';

Directory created.

Sql> Create directory Osdesc as '/u01/oradata ';

Directory created.

--Table space off-line

sql> alter tablespace XFF offline;

Tablespace altered.

--Data File migration

Sql> begin

2 dbms_file_transfer.copy_file (' Asmsrc ',

More Wonderful content: http://www.bianceng.cn/database/Oracle/

3 ' xff.256.754902279 ',

4 ' Osdesc ',

5 ' xff_new_2.dbf ');

6 end;

7/

Pl/sql procedure successfully completed.

--Modify the datafile path in the database

sql> ALTER DATABASE Rename file

2 ' +dg2/tasm/datafile/xff.256.754902279 '

3 to '/u01/oradata/xff_new_2.dbf ';

Database altered.

--Table Space online

sql> alter tablespace XFF online;

Tablespace altered.

--Test migration results

Sql> select name from V$datafile;

NAME

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

+data/tasm/system01.dbf

+data/tasm/undotbs01.dbf

+data/tasm/sysaux01.dbf

+data/tasm/users01.dbf

+data/tasm/example01.dbf

/u01/oradata/xff_new_2.dbf

6 rows selected.

Sql> Select COUNT (*) from HR.A;

COUNT (*)

----------

1580

--Removing files in ASM

asmcmd> RM xff.256.754902279

Ora-15032:not all alterations performed

Ora-15028:asm file ' +dg2/tasm/datafile/xff.256.754902279 ' not dropped;

Currently being accessed (DBD error:ocistmtexecute)

--Cannot delete directly, can be deleted after closing the database (should be a bug)