将oracle数据存在新磁盘上,案例:OracleRAC将OS文件系统中的数据文件迁移至ASM磁盘组中...

天萃荷净

Oracle RAC 将OS文件系统中的数据文件迁移至asm磁盘组中

--添加测试表空间

SQL> create tablespace xff datafile '/u01/oradata/xifenfei.dbf' size 10m autoextend on maxsize 100m;

Tablespace created.

--查看数据文件位置

SQL> select name from v$datafile;

NAME

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

+DATA/tasm/system01.dbf

+DATA/tasm/undotbs01.dbf

+DATA/tasm/sysaux01.dbf

+DATA/tasm/users01.dbf

+DATA/tasm/example01.dbf

/u01/oradata/xifenfei.dbf

6 rows selected.

--创建测试表

SQL> create table hr.a tablespace xff

2 as

3 select * from dba_tables;

Table created.

SQL> select count(*) from hr.a;

COUNT(*)

----------

1580

--转移数据文件位置

[oracle@localhost oradata]$ rman target /

Recovery Manager: Release 10.2.0.3.0 - Production on Mon Jun 27 04:30:22 2011

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

connected to target database: TASM (DBID=3032096031)

RMAN> sql 'alter tablespace xff offline';

using target database control file instead of recovery catalog

sql statement: alter tablespace xff offline

RMAN> backup as copy tablespace xff format '+DATA';

Starting backup at 27-JUN-11

allocated channel: ORA_DISK_1

channel ORA_DISK_1: sid=132 devtype=DISK

channel ORA_DISK_1: starting datafile copy

input datafile fno=00006 name=/u01/oradata/xifenfei.dbf

output filename=+DATA/tasm/datafile/xff.269.754893121 tag=TAG20110627T043200 recid=2 stamp=754893123

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

Finished backup at 27-JUN-11

RMAN> switch tablespace xff to copy;

datafile 6 switched to datafile copy "+DATA/tasm/datafile/xff.269.754893121"

RMAN> sql 'alter tablespace xff online';

sql statement: alter tablespace xff online

--查看转移后的数据文件位置

SQL> select name from v$datafile;

NAME

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

+DATA/tasm/system01.dbf

+DATA/tasm/undotbs01.dbf

+DATA/tasm/sysaux01.dbf

+DATA/tasm/users01.dbf

+DATA/tasm/example01.dbf

+DATA/tasm/datafile/xff.269.754893121

6 rows selected.

--测试其中数据是否存在

SQL> select count(*) from hr.a;

COUNT(*)

----------

1580

--创建asm中文件别名

ASMCMD> mkalias +DATA/tasm/datafile/xff.269.754893121 +DATA/tasm/xff01.dbf

--文件重命名

SQL> alter tablespace xff offline;

Tablespace altered.

SQL> alter database rename file '+DATA/tasm/datafile/xff.269.754893121' to '+DATA/tasm/xff01.dbf';

Database altered.

SQL> alter tablespace xff online;

Tablespace altered.

SQL> select name from v$datafile;

NAME

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

+DATA/tasm/system01.dbf

+DATA/tasm/undotbs01.dbf

+DATA/tasm/sysaux01.dbf

+DATA/tasm/users01.dbf

+DATA/tasm/example01.dbf

+DATA/tasm/xff01.dbf

6 rows selected.

--手工删除原来数据

[oracle@localhost oradata]$ rm xifenfei.dbf

说明：可以在rman移植数据文件位置的时候，同时处理好别名，然后对表空间重命名，实现只需要表空间离线一次

-----------------温馨提示--------------------

操作有风险，动手需谨慎

Oracle研究中心

http://www.oracleplus.net

本文由大师惜分飞原创分享，转载请尽量保留本站网址

--------------------------------------ORACLE-DBA----------------------------------------

最权威、专业的Oracle案例资源汇总之案例:OracleRAC将OS文件系统中的数据文件迁移至ASM磁盘组中