ITPub博客

首页 > 数据库 > Oracle > oracle 11g移动数据文件到其他磁盘组

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

原创 Oracle 作者:水逸冰 时间:2018-09-13 10:29:47 0 删除 编辑

目标:将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




来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/31480688/viewspace-2214090/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
精通oracle,mysql和linux,热衷于研究数据库,擅长shell和Python自动化运维。VX:18302174682

注册时间:2017-08-05

  • 博文量
    104
  • 访问量
    119342