rman copy 方式迁移数据文件

1、确定要迁移的表空间 & 数据文件

col file_name format a80
select file_id, tablespace_name, file_name from dba_data_files order by 1;

备注：数据库需要开启归档

2、使用rman copy方式进行数据文件复制

select 'backup as copy datafile ' || file_id || ' format ' || '''+SSDDG2'';'
from dba_data_files where tablespace_name IN('SYSTEM','SYSAUX')
order by tablespace_name, file_name;

backup as copy datafile 1 format '+SSDDG2';

select 'list copy of datafile ' || file_id || ';'
from dba_data_files where tablespace_name IN('SYSTEM','SYSAUX')
order by tablespace_name, file_name;

list copy of datafile 1;

3、移动的数据文件offline

select 'sql ''alter database datafile '  ||   file_id ||   ' offline'';' 
from dba_data_files where tablespace_name IN('SYSTEM')
order by tablespace_name, file_name;

sql 'alter database datafile 1 offline;'

4、switch datafile，使控制文件指向移动后的数据文件

select 'switch datafile ' || file_id || ' to copy;' 
from dba_data_files where tablespace_name IN('SYSTEM')
order by tablespace_name, file_name;

switch datafile 1 to copy;

5、使用recover datafile方式进行恢复

select file_id, tablespace_name, status, file_name from dba_data_files order by 1;

col name format a80

select name, status from v$datafile;

select 'recover datafile ' || file_id || ';' 
from dba_data_files where tablespace_name IN('SYSTEM')
order by tablespace_name, file_name;

recover datafile 1;

注：应该是应用归档的，记录不见了。。。

6、将数据文件online

select 'sql ''alter database datafile '  ||   file_id ||   '  online'';' 
from dba_data_files where tablespace_name IN('SYSTEM')
order by tablespace_name, file_name;

sql 'alter database datafile 1 online';

7、delet数据文件

select 'delete copy of datafile ' || file_id || ';' 
from dba_data_files where tablespace_name IN('SYSTEM')
order by tablespace_name, file_name;

delete copy of datafile 1;

8、启动数据库