Move datafile:From File System to ASM

一、数据库关闭(mount状态).
适用于移动undo或system表空间文件

1、关闭数据库并启动到mount状态.

[oracle] sqlplus '/as sysdba'

SQL> shutdown immediate;

SQL> startup mount;

2、确保ASM有足够的空间

SQL> col name for a80 
SQL> select file#, name, bytes/1024/1024 Size_MB from v$datafile;

     FILE# NAME                                                                                SIZE_MB
---------- -------------------------------------------------------------------------------- ----------
         1 +DGDATA/dg1/datafile/system.256.815366947                                              2048
         2 +DGDATA/dg1/datafile/sysaux.257.815367079                                              1024
         3 /tmp/oracle/backup/undo01.dbf                                                          1024
         4 /tmp/oracle/backup/users01.dbf                                                          500
         5 /home/oracle/test.dbf                                                                   500

这里举例移动undo表空间文件

[grid@dg1:/home/grid]#asmcmd
ASMCMD> lsdg
State    Type    Rebal  Sector  Block       AU  Total_MB  Free_MB  Req_mir_free_MB  Usable_file_MB  Offline_disks  Voting_files  Name
MOUNTED  EXTERN  N         512   4096  1048576      1568     1187                0            1187              0             N  DGCRS/
MOUNTED  EXTERN  N         512   4096  1048576      8628     4989                0            4989              0             N  DGDATA/

3、利用RMAN将数据文件从asm磁盘拷贝到文件系统

[oracle@dg1:/home/oracle]#rman target /

Recovery Manager: Release 11.2.0.3.0 - Production on Tue Aug 20 03:26:58 2013

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

connected to target database: DG (DBID=1728697911, not open)

RMAN> copy datafile 3 to '+DGDATA';

Starting backup at 20-AUG-13
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=29 device type=DISK
channel ORA_DISK_1: starting datafile copy
input datafile file number=00003 name=/tmp/oracle/backup/undo01.dbf
  output file name=+DGDATA/dg1/datafile/undotbs1.260.823927355 tag=TAG20130820T044233 RECID=6 STAMP=823927487
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:02:21
Finished backup at 20-AUG-13

4、修改控制文件中新文件的路径
 

[oracle] sqlplus '/as sysdba'

SQL> alter database rename file '/tmp/oracle/backup/undo01.dbf' to '+DGDATA/dg1/datafile/undotbs1.260.823927355';

Database altered.

5、查看新文件路径

SQL> col name for a80
SQL> set line 180
SQL> select file#, name from v$datafile;

     FILE# NAME
---------- --------------------------------------------------------------------------------
         1 +DGDATA/dg1/datafile/system.256.815366947
         2 +DGDATA/dg1/datafile/sysaux.257.815367079
         3 +DGDATA/dg1/datafile/undotbs1.260.823927355
         4 /tmp/oracle/backup/users01.dbf
         5 /home/oracle/test.dbf

6、打开数据库
SQL> alter database open;

Database altered.

二、数据库运行(只要将对应的表空间offline).

1、确定要移动的数据文件，并offline

SQL> col tablespace_name for a40
SQL> col file_name for a80
SQL> select TABLESPACE_NAME,FILE_ID,FILE_NAME from dba_data_files;

TABLESPACE_NAME                             FILE_ID FILE_NAME
---------------------------------------- ---------- --------
SYSTEM                                            1 +DGDATA/dg1/datafile/system.256.815366947
SYSAUX                                            2 +DGDATA/dg1/datafile/sysaux.257.815367079
UNDOTBS1                                          3 +DGDATA/dg1/datafile/undotbs1.260.823927355
USERS                                             4 /tmp/oracle/backup/users01.dbf
TEST                                              5 /home/oracle/test.dbf

SQL> alter tablespace USERS offline;

Tablespace altered.
 
2、确保ASM有足够的空间

SQL> col name for a80 
SQL> select file#, name, bytes/1024/1024 Size_MB from v$datafile;

     FILE# NAME                                                                                SIZE_MB
---------- -------------------------------------------------------------------------------- ----------
         1 +DGDATA/dg1/datafile/system.256.815366947                                              2048
         2 +DGDATA/dg1/datafile/sysaux.257.815367079                                              1024
         3 +DGDATA/dg1/datafile/undotbs1.258.815367141                                            1024
         4 /tmp/oracle/backup/users01.dbf                                                          500
         5 /home/oracle/test.dbf                                                                   500

这里举例移动users表空间文件

[grid@dg1:/home/grid]#asmcmd
ASMCMD> lsdg
State    Type    Rebal  Sector  Block       AU  Total_MB  Free_MB  Req_mir_free_MB  Usable_file_MB  Offline_disks  Voting_files  Name
MOUNTED  EXTERN  N         512   4096  1048576      1568     1187                0            1187              0             N  DGCRS/
MOUNTED  EXTERN  N         512   4096  1048576      8628     4989                0            4989              0             N  DGDATA/

3、利用RMAN将数据文件从asm磁盘拷贝到文件系统

[oracle@dg1:/home/oracle]#rman target /

Recovery Manager: Release 11.2.0.3.0 - Production on Tue Aug 20 03:26:58 2013

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

connected to target database: DG (DBID=1728697911, not open)

RMAN> copy datafile 4 to '+DGDATA';

Starting backup at 20-AUG-13
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=23 device type=DISK
channel ORA_DISK_1: starting datafile copy
input datafile file number=00004 name=/tmp/oracle/backup/users01.dbf
  output file name=+DGDATA/dg1/datafile/users.258.823927747 tag=TAG20130820T044907 RECID=7 STAMP=823927870
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:02:07
Finished backup at 20-AUG-13

4、修改控制文件中新文件的路径
 
RMAN> switch datafile 4 to copy;

datafile 4 switched to datafile copy "+DGDATA/dg1/datafile/users.258.823927747"
或
[oracle] sqlplus '/as sysdba'

SQL> alter database rename file '/tmp/oracle/backup/users01.dbf' to '+DGDATA/dg1/datafile/users.258.823927747';

Database altered.

5、查看新文件路径

SQL> select file#, name from v$datafile;

     FILE# NAME
---------- --------------------------------------------------------------------------------
         1 +DGDATA/dg1/datafile/system.256.815366947
         2 +DGDATA/dg1/datafile/sysaux.257.815367079
         3 +DGDATA/dg1/datafile/undotbs1.260.823927355
         4 +DGDATA/dg1/datafile/users.258.823927747
         5 /home/oracle/test.dbf

6、online表空间

SQL> alter tablespace USERS online;

Tablespace altered.