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磁盘组下。