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
posted @   学无止境的小一  阅读(97)  评论(0编辑  收藏  举报  
相关博文:
阅读排行:
· 一个费力不讨好的项目,让我损失了近一半的绩效!
· 清华大学推出第四讲使用 DeepSeek + DeepResearch 让科研像聊天一样简单!
· 实操Deepseek接入个人知识库
· CSnakes vs Python.NET:高效嵌入与灵活互通的跨语言方案对比
· Plotly.NET 一个为 .NET 打造的强大开源交互式图表库
点击右上角即可分享
微信分享提示