Move datafile:From ASM to File System

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

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

[oracle] sqlplus '/as sysdba'

SQL>  shutdown immediate

SQL> startup mount;

2、确保文件系统有足够的空间

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 +DGDATA/dg1/datafile/users.260.815367243                                                500
         5 /home/oracle/test.dbf                                                                   500
这里举例移动undo表空间文件

[oracle@dg1:/tmp/oracle/backup]#df -k
Filesystem           1K-blocks      Used Available Use% Mounted on
/dev/mapper/VolGroup00-LogVol00
                      28313732  15067440  11784968  57% /
/dev/sda1               101086     12332     83535  13% /boot
tmpfs                   517348    153436    363912  30% /dev/shm

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 '/tmp/oracle/backup/undo01.dbf';

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=+DGDATA/dg1/datafile/undotbs1.258.815367141
  output file name=/tmp/oracle/backup/undo01.dbf tag=TAG20130820T032739 RECID=3 STAMP=823922979
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:02:08
Finished backup at 20-AUG-13

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

[oracle] sqlplus '/as sysdba'

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

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 /tmp/oracle/backup/undo01.dbf
         4 +DGDATA/dg1/datafile/users.260.815367243
         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 /tmp/oracle/backup/undo01.dbf
USERS                                             4 +DGDATA/dg1/datafile/users.260.815367243
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 +DGDATA/dg1/datafile/users.260.815367243                                                500
         5 /home/oracle/test.dbf                                                                   500
这里举例移动users表空间文件

[oracle@dg1:/tmp/oracle/backup]#df -k
Filesystem           1K-blocks      Used Available Use% Mounted on
/dev/mapper/VolGroup00-LogVol00
                      28313732  15067440  11784968  57% /
/dev/sda1               101086     12332     83535  13% /boot
tmpfs                   517348    153436    363912  30% /dev/shm

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 '/tmp/oracle/backup/users01.dbf';

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=24 device type=DISK
channel ORA_DISK_1: starting datafile copy
input datafile file number=00004 name=+DGDATA/dg1/datafile/users.260.815367243
 output file name=/tmp/oracle/backup/users01.dbf tag=TAG20130820T034557 RECID=4 STAMP=823924020
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:01:13
Finished backup at 20-AUG-13

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

[oracle] sqlplus '/as sysdba'

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

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 /tmp/oracle/backup/undo01.dbf
         4 /tmp/oracle/backup/users01.dbf
         5 /home/oracle/test.dbf

6、online表空间

SQL> alter tablespace USERS online;

Tablespace altered.