在ASM中迁移表空间数据文件【企业实战数据库迁移】

BIDB表空间迁移方案

随着业务的不断增长，报表库的业务表空间占用空间很大，其所在的ASM磁盘组可用空间已非常有限，为了满足业务的扩展稳定，计划创建新的大容量ASM磁盘组，并将业务表空间数据文件迁移至新ASM磁盘组下，首先查看原数据文件存放的位置：

select a.FILE_ID ,file_name,bytes/1024/1024/1024 from dba_data_files a where tablespace_name='BIDB';
SQL> select file#,name from v$datafile where name like '%bidb%';
     FILE# NAME
---------- --------------------------------------------------------------------------------
        38 +DATA/topprod/datafile/bidb-01.dbf
        39 +DATA/topprod/datafile/bidb-02.dbf
        40 +DATA/topprod/datafile/bidb-03.dbf
        41 +DATA/topprod/datafile/bidb-04.dbf
        42 +DATA/topprod/datafile/bidb-05.dbf
        43 +DATA/topprod/datafile/bidb-06.dbf
        44 +DATA/topprod/datafile/bidb-07.dbf
        45 +DATA/topprod/datafile/bidb-08.dbf

在rman中依次将该表空间的数据文件38-45 offline：

RMAN> sql "alter database datafile 38 offline";
RMAN> sql "alter database datafile 39 offline";
RMAN> sql "alter database datafile 40 offline";
RMAN> sql "alter database datafile 41 offline";
RMAN> sql "alter database datafile 42 offline";
RMAN> sql "alter database datafile 43 offline";
RMAN> sql "alter database datafile 44 offline";
RMAN> sql "alter database datafile 45 offline";

将数据文件从+DATA磁盘组拷贝另外的ASM磁盘组中+DATA2：

RMAN> run 
{ 
copy datafile 38 to '+DATA2/topprod/datafile/bidb-01.dbf'; 
copy datafile 39 to '+DATA2/topprod/datafile/bidb-02.dbf'; 
copy datafile 40 to '+DATA2/topprod/datafile/bidb-03.dbf'; 
copy datafile 41 to '+DATA2/topprod/datafile/bidb-04.dbf'; 
copy datafile 42 to '+DATA2/topprod/datafile/bidb-05.dbf'; 
copy datafile 43 to '+DATA2/topprod/datafile/bidb-06.dbf'; 
copy datafile 44 to '+DATA2/topprod/datafile/bidb-07.dbf'; 
copy datafile 45 to '+DATA2/topprod/datafile/bidb-08.dbf'; 
}  

在asmcmd中查看新生成的数据文件名称：

1. ASMCMD> cd +DATA2/topprod/datafile
2. ASMCMD> ls
   重新更新控制文件，路径修改为：

RMAN> run        
 { 
switch datafile 38 to datafilecopy '+DATA2/topprod/datafile/bidb-01.dbf'; 
switch datafile 39 to datafilecopy '+DATA2/topprod/datafile/bidb-02.dbf';
switch datafile 40 to datafilecopy '+DATA2/topprod/datafile/bidb-03.dbf';
switch datafile 41 to datafilecopy '+DATA2/topprod/datafile/bidb-04.dbf';
switch datafile 42 to datafilecopy '+DATA2/topprod/datafile/bidb-05.dbf';
switch datafile 43 to datafilecopy '+DATA2/topprod/datafile/bidb-06.dbf';
switch datafile 44 to datafilecopy '+DATA2/topprod/datafile/bidb-07.dbf';
switch datafile 45 to datafilecopy '+DATA2/topprod/datafile/bidb-08.dbf';
 }  

恢复数据文件：

recover datafile 38;
recover datafile 39;
recover datafile 40;
recover datafile 41;
recover datafile 42;
recover datafile 43;
recover datafile 44;
recover datafile 45;

然后将数据文件online：

rman target /
sql "alter database datafile 38 online";
sql "alter database datafile 39 online";
sql "alter database datafile 40 online";
sql "alter database datafile 41 online";
sql "alter database datafile 42 online";
sql "alter database datafile 43 online";
sql "alter database datafile 44 online";
sql "alter database datafile 45 online";

再次查看数据文件位置：
SQL> select file#,name from v$datafile where name like ‘%bidb%’;

观察一段时间，将BIDB表空间旧的数据文件删除即可。

rm +DATA/topprod/datafile/bidb-01.dbf
rm +DATA/topprod/datafile/bidb-02.dbf
rm +DATA/topprod/datafile/bidb-03.dbf
rm +DATA/topprod/datafile/bidb-04.dbf
rm +DATA/topprod/datafile/bidb-05.dbf
rm +DATA/topprod/datafile/bidb-06.dbf
rm +DATA/topprod/datafile/bidb-07.dbf
rm +DATA/topprod/datafile/bidb-08.dbf

至此，整个表空间的数据文件已迁移至新的ASM磁盘组下。