Oracle 主备修改数据文件路径

背景

今天操作了一把备库的数据文件目录切换操作，共4TB数据量用时间，用时：5小时。经验都是记录下来的。

一、主库操作

-- 迁移表空间数据文件
-- 表空间迁移命令拼接语句
select 'alter tablespace ' || tablespace_name || '  offline;' text
  from dba_data_files
 where tablespace_name ='USERS'
 group by 'alter tablespace ' || tablespace_name || '  offline;'
union all
select '! cp ' || file_name || '  ' ||
       replace(file_name, 'backup2', 'backup') text
  from dba_data_files
 where tablespace_name ='USERS'
union all
select 'alter database rename file ' || '''' || FILE_NAME || '''' || ' to ' || '''' ||
       replace(FILE_NAME, 'backup2', 'backup') || '''' || ';' text
  FROM DBA_DATA_FILES
 where tablespace_name ='USERS'
union all
select 'alter tablespace ' || tablespace_name || '  online;' text
  from dba_data_files
 where tablespace_name ='USERS'
 group by 'alter tablespace ' || tablespace_name || '  online;';

 -- 输出操作示列：USERS
    alter tablespace USERS  offline;
    ! cp /backup2/oradata/data/users.dbf  /backup/oradata/data/users.dbf  
    alter database rename file '/backup2/oradata/data/users.dbf' to '/backup/oradata/data/users.dbf ';
    alter tablespace USERS  online;
-- 更改临时表空间文件路径
col name for a50;
select name,bytes/1024/1024 m,status from v$tempfile;
alter tablespace temp add tempfile '/backup/oradata/data/temp01.dbf' size 30G reuse;
alter database tempfile '/backup2/oradata/data/temp01.dbf' autoextend on next 500M maxsize unlimited;
alter database tempfile '/backup2/oradata/data/temp.dbf' offline;
alter database tempfile '/backup2/oradata/data/temp.dbf' drop INCLUDING DATAFILES;

二、备库操作

1、修改参数:

alter system set STANDBY_FILE_MANAGEMENT='MANUAL';
alter system set DB_FILE_NAME_CONVERT='+DATA','/backup/oradata/data' scope=both;

2、重启数据库到 mount

shutdown immediate;
startup mount;

3、迁移表空间数据文件

select '! cp ' || file_name || '  ' ||
       replace(file_name, 'backup2', 'backup') text
  from dba_data_files
 where tablespace_name ='USERS'
union all
select 'alter database rename file ' || '''' || FILE_NAME || '''' || ' to ' || '''' ||
       replace(FILE_NAME, 'backup2', 'backup') || '''' || ';' text
  FROM DBA_DATA_FILES
 where tablespace_name ='USERS';
 -- 示列：USERS
 -- 输出操作示列：USERS
SQL> ! cp /backup2/oradata/data/users.dbf  /backup/oradata/data/users.dbf  
SQL> alter database rename file '/backup2/oradata/data/users.dbf' to '/backup/oradata/data/users.dbf ';

4、临时表空间迁移（同主库迁移，略）

5、OPEN数据库，启动应用进程

alter system set STANDBY_FILE_MANAGEMENT='auto';
alter database open ;
alter database recover managed standby database using current logfile disconnect from session