ORACLE11G测试数据文件的迁移到其他磁盘组

 [oracle@IDM ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Thu Aug 22 08:19:24 2013

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

With the Partitioning, Automatic Storage Management, OLAP, Data Mining

and Real Application Testing options

SQL>

SQL> set lines 200

SQL> col FILE_NAME for a70

SQL> select tablespace_name, file_name from dba_data_files;


TABLESPACE_NAME               FILE_NAME

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

USERS                  +DATA1/alice/datafile/users.259.824111081

UNDOTBS1              +DATA1/alice/datafile/undotbs1.258.824111081

SYSAUX                    +DATA1/alice/datafile/sysaux.257.824111081

SYSTEM                 +DATA1/alice/datafile/system.256.824111081

EXAMPLE                +DATA1/alice/datafile/example.265.824111171

启用数据库归档，

SQL> archive log list;

Database log mode             Archive Mode

Automatic archival            Enabled

Archive destination           /u03/archive/alice

Oldest online log sequence     4

Next log sequence to archive   6

Current log sequence          6

我们要把users表空间的数据文件迁移到DATA2磁盘组，所有使users表空间的数据文件离线，

SQL> alter database datafile  '+DATA1/alice/datafile/users.259.824111081' offline;

Database altered.

SQL>


8.用rman迁移user表空间的数据文件到DATA2磁盘组，并更新控制文件

[oracle@IDM alice]$ rlwrap rman target /

Recovery Manager: Release 11.2.0.3.0 - Production on Thu Aug 22 08:28:11 2013

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

connected to target database: ALICE (DBID=1806834031)

RMAN> copy datafile '+DATA1/alice/datafile/users.259.824111081' to '+DATA2';

Starting backup at 22-AUG-13

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=192 device type=DISK

channel ORA_DISK_1: starting datafile copy

input datafile file number=00004 name=+DATA1/alice/datafile/users.259.824111081

output file name=+DATA2/alice/datafile/users.256.824113711 tag=TAG20130822T082831 RECID=2 STAMP=824113712

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01

Finished backup at 22-AUG-13


RMAN>

RMAN> run{

2> set  newname for datafile '+DATA1/alice/datafile/users.259.824111081' to '+DATA2/alice/datafile/USERS.256.824113711';

3> switch datafile all;

4> }


executing command: SET NEWNAME


datafile 4 switched to datafile copy

input datafile copy RECID=2 STAMP=824113712 file name=+DATA2/alice/datafile/users.256.824113711

RMAN>


9.登录Oracle instance， online数据文件

[root@rh2 rules.d]# /sbin/udevcontrol reload_rules

SQL> SELECT FILE_NAME FROM DBA_DATA_FILES;


FILE_NAME

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

+DATA2/alice/datafile/users.256.824113711

+DATA1/alice/datafile/undotbs1.258.824111081

+DATA1/alice/datafile/sysaux.257.824111081

+DATA1/alice/datafile/system.256.824111081

+DATA1/alice/datafile/example.265.824111171


SQL> recover datafile '+DATA2/alice/datafile/users.256.824113711';

Media recovery complete.

SQL> alter database datafile'+DATA2/alice/datafile/users.256.824113711' online

  2  ;

Database altered

SQL>