Oracle数据库存储更换,更改文件路径(停机和在线两种方式)
一.前言
在日常的数据库运维工作中,会出现存储更换的情况,比如一些老的存储性能跟不上数据库的需求,需要更换成新型的存储设备,需要把数据库的所有文件移动到新的存储所挂载的目录当中,本文档讲解的所有文件移动的几种方式
二.停库更改
这种是建议使用的方法,相对更为稳妥,操作也比较简单,需要申请停机窗口。
现有文件所在的路径
数据文件、控制文件、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
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】博客园社区专享云产品让利特惠,阿里云新客6.5折上折
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· 一个费力不讨好的项目,让我损失了近一半的绩效!
· 清华大学推出第四讲使用 DeepSeek + DeepResearch 让科研像聊天一样简单!
· 实操Deepseek接入个人知识库
· CSnakes vs Python.NET:高效嵌入与灵活互通的跨语言方案对比
· Plotly.NET 一个为 .NET 打造的强大开源交互式图表库