Oracle数据库迁移-backup as copy数据文件目录迁移

（1）迁移非system数据文件

1 使用sql语句生成迁移语句

select file#,

       name,

       'backup as copy datafile '||file#||' format '||''''||

       replace(new_file_name,

               '/npm/oracle/arch/oradata10',

               '/npm/oracle/oradata13') ||''''||';' exec_statement,status

  from (select file#,

               name,

               replace(name,

                       '/npm/oracle/arch/oradata12',

                       '/npm/oracle/oradata13') new_file_name,status

          from v$datafile

         where name like '%/arch/%')

where status <>'SYSTEM'

迁移步骤1

rman target /

switch datafile 464 to copy;

alter database datafile 464 offline;

recover datafile 464;

alter database datafile 464 online;

迁移步骤2

rm  '/npm/oracle/oradata11/hpmdbs111.dbf'

迁移步骤3

rman target /

crosscheck datafilecopy all;

list expired datafilecopy all;

delete expired datafilecopy all;

（2）迁移system数据文件

alter system set sga_max_size=95G scope=spfile;
alter system set sga_target=95G scope=spfile;
shutdown immediate;
startup mount 
backup as copy datafile 461 format '/npm/oracle/oradata13/system1a0001.dbf';
switch datafile 461 to copy;
alter database open;

rman target /

crosscheck datafilecopy all;

list expired datafilecopy all;

delete expired datafilecopy all;