当前位置： 首页 > article >正文

Oracle数据库存储更换，更改文件路径（停机和在线两种方式）

article 2024/10/22 15:10:51

一.前言

在日常的数据库运维工作中，会出现存储更换的情况，比如一些老的存储性能跟不上数据库的需求，需要更换成新型的存储设备，需要把数据库的所有文件移动到新的存储所挂载的目录当中，本文档讲解的所有文件移动的几种方式

二.停库更改

这种是建议使用的方法，相对更为稳妥，操作也比较简单，需要申请停机窗口。
现有文件所在的路径
数据文件、控制文件、redo日志文件：/oradata/orcl
归档日志文件：/oradata/arch
新路径
数据文件、控制文件、redo日志文件：/neworadata/orcl
归档日志文件：/neworadata/arch

1. 生成迁移所用到的语句

执行以下脚本 生成rman中的copy语句 此步骤生成数据文件移动目录的语句

set linesize 400;
set pagesize 2000;
SELECT    'copy datafile '
         ||''||''''||name||''''||''
         || ' to '||''''|| '/neworadata/orcl/'
         || SUBSTR (name, INSTR (name, '/', -1) + 1)
         || ''';'
    FROM v$dbfile
ORDER BY FILE#;

copy datafile '/oradata/orcl/system01.dbf' to '/neworadata/orcl/system01.dbf';
copy datafile '/oradata/orcl/sysaux01.dbf' to '/neworadata/orcl/sysaux01.dbf';
copy datafile '/oradata/orcl/users01.dbf' to '/neworadata/orcl/users01.dbf';
copy datafile '/oradata/orcl/example01.dbf' to '/neworadata/orcl/example01.dbf';
copy datafile '/oradata/orcl/test01.dbf' to '/neworadata/orcl/test01.dbf';
copy datafile '/oradata/orcl/test02.dbf' to '/neworadata/orcl/test02.dbf';
copy datafile '/oradata/orcl/test03.dbf' to '/neworadata/orcl/test03.dbf';
copy datafile '/oradata/orcl/test_02.dbf' to '/neworadata/orcl/test_02.dbf';
copy datafile '/oradata/orcl/test_03.dbf' to '/neworadata/orcl/test_03.dbf';
copy datafile '/oradata/orcl/undotbs02.dbf' to '/neworadata/orcl/undotbs02.dbf';

执行以下脚本  生成rename语句   
set linesize 400;
set pagesize 2000;
SELECT    'alter database rename file '
         ||''||''''||name||''''||''
         || ' to '||''''|| '/neworadata/orcl/'
         || SUBSTR (name, INSTR (name, '/', -1) + 1)
         || ''';'
    FROM v$dbfile
ORDER BY FILE#;

alter database rename file '/oradata/orcl/system01.dbf' to '/neworadata/orcl/system01.dbf';
alter database rename file '/oradata/orcl/sysaux01.dbf' to '/neworadata/orcl/sysaux01.dbf';
alter database rename file '/oradata/orcl/users01.dbf' to '/neworadata/orcl/users01.dbf';
alter database rename file '/oradata/orcl/example01.dbf' to '/neworadata/orcl/example01.dbf';
alter database rename file '/oradata/orcl/test01.dbf' to '/neworadata/orcl/test01.dbf';
alter database rename file '/oradata/orcl/test02.dbf' to '/neworadata/orcl/test02.dbf';
alter database rename file '/oradata/orcl/test03.dbf' to '/neworadata/orcl/test03.dbf';
alter database rename file '/oradata/orcl/test_02.dbf' to '/neworadata/orcl/test_02.dbf';
alter database rename file '/oradata/orcl/test_03.dbf' to '/neworadata/orcl/test_03.dbf';
alter database rename file '/oradata/orcl/undotbs02.dbf' to '/neworadata/orcl/undotbs02.dbf';


执行以下脚本  生成rename语句
set linesize 400;
set pagesize 2000;
SELECT    'alter database rename file '
         ||''||''''||member||''''||''
         || ' to '||''''|| '/oradata/lnorcl/'
         || SUBSTR (member, INSTR (member, '/', -1) + 1)
         || ''';'
    FROM v$logfile
ORDER BY GROUP#;

alter database rename file '/oradata/orcl/redo01.log' to '/neworadata/orcl/redo01.log';
alter database rename file '/oradata/orcl/redo02.log' to '/neworadata/orcl/redo02.log';
alter database rename file '/oradata/orcl/redo03.log' to '/neworadata/orcl/redo03.log';
alter database rename file '/oradata/orcl/redo04.log' to '/neworadata/orcl/redo04.log';
alter database rename file '/oradata/orcl/redo05.log' to '/neworadata/orcl/redo05.log';
2.开始停库迁移数据文件、redo日志和控制文件

创建pfile 停库

create pfile from spfile;
shutdown immediate

修改pfile中的控制文件存放路径

*.control_files='/neworadata/orcl/control01.ctl','/neworadata/orcl/control02.ctl'

拷贝两份控制文件到新的目录下

cp /oradata/orcl/control01.ctl /oradata/orcl/control02.ctl /neworadata/orcl/

通过pfile生成spfile 并启动数据库到mount

create spfile from pfile;
startup mount;

在rman中 执行上面生成的copy语句

rman target /
copy datafile '/oradata/orcl/system01.dbf' to '/neworadata/orcl/system01.dbf';
copy datafile '/oradata/orcl/sysaux01.dbf' to '/neworadata/orcl/sysaux01.dbf';
copy datafile '/oradata/orcl/users01.dbf' to '/neworadata/orcl/users01.dbf';
copy datafile '/oradata/orcl/example01.dbf' to '/neworadata/orcl/example01.dbf';
copy datafile '/oradata/orcl/test01.dbf' to '/neworadata/orcl/test01.dbf';
copy datafile '/oradata/orcl/test02.dbf' to '/neworadata/orcl/test02.dbf';
copy datafile '/oradata/orcl/test03.dbf' to '/neworadata/orcl/test03.dbf';
copy datafile '/oradata/orcl/test_02.dbf' to '/neworadata/orcl/test_02.dbf';
copy datafile '/oradata/orcl/test_03.dbf' to '/neworadata/orcl/test_03.dbf';
copy datafile '/oradata/orcl/undotbs02.dbf' to '/neworadata/orcl/undotbs02.dbf';

文件系统中拷贝redo日志到新目录

cp /oradata/orcl/redo* /neworadata/orcl/

在sqlplus中 执行上面生成的rename语句

sqlplus / as sysdba
alter database rename file '/oradata/orcl/system01.dbf' to '/neworadata/orcl/system01.dbf';
alter database rename file '/oradata/orcl/sysaux01.dbf' to '/neworadata/orcl/sysaux01.dbf';
alter database rename file '/oradata/orcl/users01.dbf' to '/neworadata/orcl/users01.dbf';
alter database rename file '/oradata/orcl/example01.dbf' to '/neworadata/orcl/example01.dbf';
alter database rename file '/oradata/orcl/test01.dbf' to '/neworadata/orcl/test01.dbf';
alter database rename file '/oradata/orcl/test02.dbf' to '/neworadata/orcl/test02.dbf';
alter database rename file '/oradata/orcl/test03.dbf' to '/neworadata/orcl/test03.dbf';
alter database rename file '/oradata/orcl/test_02.dbf' to '/neworadata/orcl/test_02.dbf';
alter database rename file '/oradata/orcl/test_03.dbf' to '/neworadata/orcl/test_03.dbf';
alter database rename file '/oradata/orcl/undotbs02.dbf' to '/neworadata/orcl/undotbs02.dbf';

alter database rename file '/oradata/orcl/redo01.log' to '/neworadata/orcl/redo01.log';
alter database rename file '/oradata/orcl/redo02.log' to '/neworadata/orcl/redo02.log';
alter database rename file '/oradata/orcl/redo03.log' to '/neworadata/orcl/redo03.log';
alter database rename file '/oradata/orcl/redo04.log' to '/neworadata/orcl/redo04.log';
alter database rename file '/oradata/orcl/redo05.log' to '/neworadata/orcl/redo05.log';

打开数据库

alter database open;
3.更改归档日志存放路径和重建临时表空间

修改归档日志存放路径

alter system set log_archive_dest_1='location=/neworadata/arch' scope = both;

重建临时文件 查看临时文件

select * from dba_temp_files;

先在新路径添加 然后再删除旧的

alter tablespace temp add tempfile '/oradata/orcl/temp01.dbf' size 512M autoextend on maxsize 32704M;
alter database tempfile '/neworadata/orcl/temp01.dbf' drop;

三.在线更改

表空间数据文件的在线移动功能仅在12c及之后版本中可用，如果数据库为11g，只能使用停机方式。
12c及之后的版本控制文件的迁移也只能停机

1.修改归档日志存放路径
alter system set  log_archive_dest_1='location=/neworadata/arch' scope = both;
2.在线移动表空间数据文件

编写修改数据文件路径脚本

set linesize 400;
set pagesize 2000;
SELECT    'ALTER DATABASE MOVE DATAFILE '
         ||''||''''||name||''''||''
         || ' to '||''''|| '/neworadata/orcl/'
         || SUBSTR (name, INSTR (name, '/', -1) + 1)
         || ''';'
    FROM v$dbfile
ORDER BY FILE#;

sqlplus直接执行根据脚本得出的语句

ALTER DATABASE MOVE DATAFILE '/oradata/ORCL/system01.dbf' to '/neworadata/orcl/system01.dbf';
ALTER DATABASE MOVE DATAFILE '/oradata/ORCL/sysaux01.dbf' to '/neworadata/orcl/sysaux01.dbf';
ALTER DATABASE MOVE DATAFILE '/oradata/ORCL/undotbs01.dbf' to '/neworadata/orcl/undotbs01.dbf';
ALTER DATABASE MOVE DATAFILE '/oradata/ORCL/users01.dbf' to '/neworadata/orcl/users01.dbf';
3.重建redo日志

查看redo日志组、大小、组内日志个数、是否归档、状态

select group#,bytes /1024/1024,members,archived,status from v$log;

查看redo日志的文件路径

col member for a60
set line 300
set pages 2000
select group#,status,member from v$logfile;

之前的日志是三组 每组一个成员 每个成员200m 在新的路径下创建三组日志

alter database add logfile group 4 '/neworadata/orcl/redo04.log' size 200m;
alter database add logfile group 5 '/neworadata/orcl/redo05.log' size 200m;
alter database add logfile group 6 '/neworadata/orcl/redo06.log' size 200m;

查看日志组状态 删除旧的INACTIVE状态的日志组 切换日志组到新加的日志组上 等到旧的日志组状态为INIACTIVE时 全部删除

alter database drop logfile group 1;
alter database drop logfile group 2;
alter database drop logfile group 3;

如果有standby日志组 也是删除后在新的路径下重建
查看standby日志组

select group#,status,used from v$standby_log;

alter database drop standby logfile group 7;
alter database drop standby logfile group 8;
alter database drop standby logfile group 9;
alter database drop standby logfile group 10;

alter database add standby logfile group 7 '/neworadata/orcl/std_redo07.log' size 200M;
alter database add standby logfile group 8 '/neworadata/orcl/std_redo08.log' size 200M;
alter database add standby logfile group 9 '/neworadata/orcl/std_redo09.log' size 200M;
alter database add standby logfile group 10 '/neworadata/orcl/std_redo10.log' size 200M;
4.重建临时文件

查看所有临时表空间的文件

select * from dba_temp_files;

先在新路径添加 然后再删除旧的

alter tablespace temp add tempfile '/neworadata/orcl/temp01.dbf' size 512M autoextend on maxsize 32704M;
alter database tempfile '/oradata/ORCL/temp01.dbf' drop;
5.更改控制文件路径(需重启)

修改控制文件存放路径

alter system set control_files='/oradata/orcl/control01.ctl','/oradata/orcl/control02.ctl' scope=spfile;

停库

shutdown immediate;

拷贝控制文件到对应目录

cp control01.ctl control02.ctl /oradata/orcl

启动数据库

startup

http://www.kler.cn/news/360300.html

相关文章：

  • 2024年10月21日第三部分
  • 2024第四届”认证杯“数学中国全国大学生数学竞赛参赛通知
  • 彻底解决IDEA SpringBoot项目yml文件没有小树叶，读取配置文件失败问题
  • 【Dv2Admin】软删除的修改与恢复
  • 香港服务器哪种硬盘的运行速度最快？
  • Java后端面试题：MySQL篇
  • 搭子陪玩伴游线上游戏陪玩付费语音陪聊系统源码线下家政源码一体式部署（h5+小程序+app）
  • libtool版本更新
  • 6、ES6
  • 【算法日记】力扣239 滑动窗口最大值
  • 【MM2024】阿里云 PAI 团队图像编辑算法论文入选 MM2024
  • golang自封框架
  • go中阶乘实现时递归及迭代方式的比较
  • element-ui table 前端分页
  • Oracle 使用位图索引 Cost降低200倍! 探讨位图索引的利与弊
  • 002_基于django国内运动男装小红书文章数据可视化分析系统的设计与实现2024_qo6cy3i4
  • Linux虚拟化软件与操作系统与SSH客户端
  • 使用 Docker 升级 MySQL 的优化步骤与说明
  • 【CXL协议-性能注意事项（13）】
  • 零售行业的数字化营销转型之路