oracle 数据文件 属组,oracle 11g移动数据文件到其他磁盘组

目标：将USERS表空间数据文件从RAC_DATA磁盘组移动到MING磁盘组。

这里不用alter database move datafile那种方式，直接用rman copy，然后switch一下就可以了。

首先修改数据库为归档模式

SQL> alter system set log_archive_dest_1='LOCATION=/arch1' scope=spfile sid='ractest1';

System altered.

SQL> alter system set log_archive_dest_1='LOCATION=/arch2' scope=spfile sid='ractest2';

System altered.

重启数据库

srvctl stop database -d ractest

srvctl start database -d ractest -o mount

sqlplus / as sysdba

alter database archivelog;

alter database open;  --两个节点

查看要被移动的数据文件

rman target /

RMAN> report schema;

Report of database schema for database with db_unique_name RACTEST

List of Permanent Datafiles

===========================

File Size(MB) Tablespace           RB segs Datafile Name

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

1    690      SYSTEM               ***     +RAC_DATA/ractest/system01.dbf

2    510      SYSAUX               ***     +RAC_DATA/ractest/sysaux01.dbf

3    110      UNDOTBS1             ***     +RAC_DATA/ractest/undotbs01.dbf

4    15       USERS                ***     +RAC_DATA/ractest/users01.dbf

5    100      EXAMPLE              ***     +RAC_DATA/ractest/example01.dbf

6    25       UNDOTBS2             ***     +RAC_DATA/ractest/undotbs02.dbf

7    100      TBS_MING             ***     +MING/ractest/datafile/tbs_ming.256.982218743

List of Temporary Files

=======================

File Size(MB) Tablespace           Maxsize(MB) Tempfile Name

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

1    30       TEMP                 32767       +RAC_DATA/ractest/temp01.dbf

拷贝数据文件

RMAN> BACKUP AS COPY

2> DATAFILE "+RAC_DATA/ractest/users01.dbf"

3> FORMAT   "+MING";

Starting backup at 11-SEP-18

using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile copy

input datafile file number=00004 name=+RAC_DATA/ractest/users01.dbf

output file name=+MING/ractest/datafile/users.257.986536253 tag=TAG20180911T055052 RECID=2 STAMP=986536257

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

Finished backup at 11-SEP-18

asmcmd中查看已经成功复制

ASMCMD> ls

TBS_MING.256.982218743

USERS.257.986536253

离线数据文件

RMAN> SQL "ALTER DATABASE DATAFILE

2>        ''+RAC_DATA/ractest/users01.dbf'' OFFLINE";

sql statement: ALTER DATABASE DATAFILE        ''+RAC_DATA/ractest/users01.dbf'' OFFLINE

查看数据文件状态

SQL> col name for a30

SQL> select name,status from v$datafile where name like '%user%';

NAME                           STATUS

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

+RAC_DATA/ractest/users01.dbf  RECOVER

更新控制文件中的信息

RMAN> SWITCH DATAFILE "+RAC_DATA/ractest/users01.dbf" TO COPY;

datafile 4 switched to datafile copy "+MING/ractest/datafile/users.257.986536253"

recover数据文件

RMAN> SWITCH DATAFILE "+RAC_DATA/ractest/users01.dbf" TO COPY;

datafile 4 switched to datafile copy "+MING/ractest/datafile/users.257.986536253"

RMAN> RECOVER DATAFILE "+MING/ractest/datafile/users.257.986536253";

Starting recover at 11-SEP-18

using channel ORA_DISK_1

starting media recovery

media recovery complete, elapsed time: 00:00:07

Finished recover at 11-SEP-18

online数据文件

RMAN> SQL "ALTER DATABASE DATAFILE

2>       ''+MING/ractest/datafile/users.257.986536253'' ONLINE";

sql statement: ALTER DATABASE DATAFILE      ''+MING/ractest/datafile/users.257.986536253'' ONLINE

查看数据文件状态

NAME                                               STATUS

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

+MING/ractest/datafile/users.257.986536253         ONLINE

删除旧的磁盘组中的复制文件

RMAN> DELETE DATAFILECOPY "+RAC_DATA/ractest/users01.dbf";

released channel: ORA_DISK_1

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=33 instance=ractest1 device type=DISK

List of Datafile Copies

=======================

Key     File S Completion Time Ckp SCN    Ckp Time

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

3       4    A 11-SEP-18       1178882    11-SEP-18

Name: +RAC_DATA/ractest/users01.dbf

Do you really want to delete the above objects (enter YES or NO)? YES

deleted datafile copy

datafile copy file name=+RAC_DATA/ractest/users01.dbf RECID=3 STAMP=986536580

Deleted 1 objects

RMAN> REPORT SCHEMA;

Report of database schema for database with db_unique_name RACTEST

List of Permanent Datafiles

===========================

File Size(MB) Tablespace           RB segs Datafile Name

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

1    690      SYSTEM               ***     +RAC_DATA/ractest/system01.dbf

2    510      SYSAUX               ***     +RAC_DATA/ractest/sysaux01.dbf

3    110      UNDOTBS1             ***     +RAC_DATA/ractest/undotbs01.dbf

4    15       USERS                ***     +MING/ractest/datafile/users.257.986536253

5    100      EXAMPLE              ***     +RAC_DATA/ractest/example01.dbf

6    25       UNDOTBS2             ***     +RAC_DATA/ractest/undotbs02.dbf

7    100      TBS_MING             ***     +MING/ractest/datafile/tbs_ming.256.982218743

List of Temporary Files

=======================

File Size(MB) Tablespace           Maxsize(MB) Tempfile Name

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

1    30       TEMP                 32767       +RAC_DATA/ractest/temp01.dbf