ORACLE单机_数据文件更改路径

1.变更目的由于存储切换需要更换数据库文件路径。适用范围本变更适用于小机平台和X86平台Oracle数据库单机由于存储切换需要更换数据库文件路径的情况适用于Oracle数据库版本为11g以及11g以上。包含两类场景1.无归档需要更换数据库文件路径2.有归档需要更换数据库文件路径2.变更准备查看当前文件路径情况set lin 200 pages 999 col name for a90 select controlfile type,0 file#,name from v$controlfile union all select datafile,file#,name from v$datafile union all select tempfile,file#,name from v$tempfile union all select logfile,group#,member from v$logfile; TYPE FILE# NAME ----------- ---------------------------------------------------------- controlfile 0 /oradata/cesdb/control01.ctl controlfile 0 /oradata/cesdb/control02.ctl datafile 1 /oradata/cesdb/system01.dbf datafile 2 /oradata/cesdb/sysaux01.dbf datafile 3 /oradata/cesdb/undotbs01.dbf datafile 4 /oradata/cesdb/users01.dbf datafile 5 /oradata/copy/tbs01_01.dbf datafile 6 /oradata/scott_mv.dbf tempfile 1 /oradata/cesdb/temp01.dbf logfile 3 /oradata/cesdb/redo03.log logfile 2 /oradata/cesdb/redo02.log logfile 1 /oradata/cesdb/redo01.log备份当前控制文件和spfile有条件的提前做全库备份SQL ALTER DATABASE BACKUP CONTROLFILE TO /home/oracle/controlfile_20260508.bkp; --二进制 create pfile/home/oracle/pfile_20260508.ora from spfile; --pfile检查scnSQL -- 数据库scn select current_scn from v$database; -- 查看控制文件记录的数据库检查点 SCN SELECT checkpoint_change# FROM v$database; -- 查看数据文件头中记录的检查点 SCN SELECT file#, name, checkpoint_change#, fuzzy FROM v$datafile_header; -- 数据库scn select current_scn from v$database; CURRENT_SCN -------------------- 2310959 Elapsed: 00:00:00.00 18:08:57 SYSprimary -- 查看控制文件记录的数据库检查点 SCN SELECT checkpoint_change# FROM v$database; CHECKPOINT_CHANGE# -------------------- 2310445 Elapsed: 00:00:00.00 18:08:57 SYSprimary -- 查看数据文件头中记录的检查点 SCN 18:08:57 SYSprimary SELECT file#, name, checkpoint_change#, fuzzy FROM v$datafile_header; FILE# NAME CHECKPOINT_CHANGE# FUZ ------- -------------------------------------------------- --- 1 /oradata/cesdb/system01.dbf 2310445 YES 2 /oradata/cesdb/sysaux01.dbf 2310445 YES 3 /oradata/cesdb/undotbs01.dbf 2310445 YES 4 /oradata/cesdb/users01.dbf 2310445 YES 5 /oradata/copy/tbs01_01.dbf 2310445 YES 6 /oradata/scott_mv.dbf 2310445 YES 6 rows selected.生成替换命令SQL -- datafile select alter database rename file ||name|| to ||replace(name,/oradata/cesdb/,/oradata/cesdb_mv/)||; gen_sql from v$datafile; -- tempfile select alter database rename file ||name|| to ||replace(name,/oradata/cesdb/,/oradata/cesdb_mv/)||; gen_sql from v$tempfile; -- logfile select alter database rename file ||member|| to ||replace(member,/oradata/cesdb/,/oradata/cesdb_mv/)||; gen_sql from v$logfile; GEN_SQL --------------------------------------- alter database rename file /oradata/cesdb/system01.dbf to /oradata/cesdb_mv/system01.dbf; alter database rename file /oradata/cesdb/sysaux01.dbf to /oradata/cesdb_mv/sysaux01.dbf; alter database rename file /oradata/cesdb/undotbs01.dbf to /oradata/cesdb_mv/undotbs01.dbf; alter database rename file /oradata/cesdb/users01.dbf to /oradata/cesdb_mv/users01.dbf; alter database rename file /oradata/copy/tbs01_01.dbf to /oradata/copy/tbs01_01.dbf; alter database rename file /oradata/scott_mv.dbf to /oradata/scott_mv.dbf; GEN_SQL alter database rename file /oradata/cesdb/temp01.dbf to /oradata/cesdb_mv/temp01.dbf; GEN_SQL ------------------------------------------ alter database rename file /oradata/cesdb/redo03.log to /oradata/cesdb_mv/redo03.log; alter database rename file /oradata/cesdb/redo02.log to /oradata/cesdb_mv/redo02.log; alter database rename file /oradata/cesdb/redo01.log to /oradata/cesdb_mv/redo01.log;3.变更实施3.1无归档需要更换数据库文件路径关闭数据库拷贝对应文件到对应目录SQL Shut immediate; 18:27:39 SYSprimary Shut immediate; Database closed. Database dismounted. ORACLE instance shut down. $(ORACLE) cp /oradata/cesdb/* /oradata/cesdb_mv/.修改pfile替换控制文件路径$(ORACLE) vi /home/oracle/pfile_20260508.ora *.control_files/oradata/cesdb/control01.ctl,/oradata/cesdb/control02.ctl 修改为 *.control_files/oradata/cesdb_mv/control01.ctl,/oradata/cesdb_mv/control02.ctl SQL create spfile from pfile/home/oracle/pfile_20260508.ora; Startup mount; 18:46:12 SYSprimary create spfile from pfile/home/oracle/pfile_20260508.ora; File created. Elapsed: 00:00:00.04 18:46:14 SYSprimary Startup mount; ORACLE instance started. Total System Global Area 1252663296 bytes Fixed Size 2252824 bytes Variable Size 402657256 bytes Database Buffers 838860800 bytes Redo Buffers 8892416 bytes Database mounted.修改其他文件路径SQL 使用提前生成的SQL替换文件路径参考变更准备章节 alter database rename file /oradata/copy/tbs01_01.dbf to /oradata/copy/tbs01_01.dbf * ERROR at line 1: ORA-01511: error in renaming log/data files ORA-01523: cannot rename data file to /oradata/copy/tbs01_01.dbf - file already part of database 如果没有替换路径会报错 Elapsed: 00:00:00.00 18:53:11 SYSprimary alter database rename file /oradata/scott_mv.dbf to /oradata/scott_mv.dbf; alter database rename file /oradata/scott_mv.dbf to /oradata/scott_mv.dbf * ERROR at line 1: ORA-01511: error in renaming log/data files ORA-01523: cannot rename data file to /oradata/scott_mv.dbf - file already part of database Elapsed: 00:00:00.00 18:53:11 SYSprimary alter database rename file /oradata/cesdb/temp01.dbf to /oradata/cesdb_mv/temp01.dbf; Database altered. Elapsed: 00:00:00.00 alter database rename file /oradata/cesdb/redo03.log to /oradata/cesdb_mv/redo03.log; Database altered.检查文件路径和scn不一致不要开库SQL 参考变更准备章节 TYPE FILE# NAME ----------- -------------------- controlfile 0 /oradata/cesdb_mv/control01.ctl controlfile 0 /oradata/cesdb_mv/control02.ctl datafile 1 /oradata/cesdb_mv/system01.dbf datafile 2 /oradata/cesdb_mv/sysaux01.dbf datafile 3 /oradata/cesdb_mv/undotbs01.dbf datafile 4 /oradata/cesdb_mv/users01.dbf datafile 5 /oradata/copy/tbs01_01.dbf datafile 6 /oradata/scott_mv.dbf tempfile 1 /oradata/cesdb_mv/temp01.dbf logfile 3 /oradata/cesdb_mv/redo03.log logfile 2 /oradata/cesdb_mv/redo02.log logfile 1 /oradata/cesdb_mv/redo01.log -- 数据库scn select current_scn from v$database; CURRENT_SCN -------------------- 0 Elapsed: 00:00:00.00 19:01:03 SYSprimary -- 查看控制文件记录的数据库检查点 SCN SELECT checkpoint_change# FROM v$database; CHECKPOINT_CHANGE# -------------------- 2312837 Elapsed: 00:00:00.00 19:01:03 SYSprimary -- 查看数据文件头中记录的检查点 SCN 19:01:04 SYSprimary SELECT file#, name, checkpoint_change#, fuzzy FROM v$datafile_header; FILE# NAME CHECKPOINT_CHANGE# FUZ ------ ---------------------------------- -------------------- --- 1 /oradata/cesdb_mv/system01.dbf 2312837 NO 2 /oradata/cesdb_mv/sysaux01.dbf 2312837 NO 3 /oradata/cesdb_mv/undotbs01.dbf 2312837 NO 4 /oradata/cesdb_mv/users01.dbf 2312837 NO 5 /oradata/copy/tbs01_01.dbf 2312837 NO 6 /oradata/scott_mv.dbf 2312837 NO打开数据库SQL 先尝试read only然后重启数据库到open alter database open read only; select open_mode from v$database; Database altered. Elapsed: 00:00:00.22 20:48:44 SYSprimary OPEN_MODE -------------------- READ ONLY3.2有归档需要更换数据库文件路径需offline数据文件mv路径后online数据文件依次进行需要执行recover命令。Offline数据文件SQL alter database datafile /oradata/copy/tbs01_01.dbf offline; Database altered. $(ORACLE) mv /oradata/copy/tbs01_01.dbf /oradata/cesdb_mv/. SQL alter database rename file /oradata/copy/tbs01_01.dbf to /oradata/cesdb_mv/tbs01_01.dbf; Database altered.检查路径和scnSQL -- 数据库scn select current_scn from v$database; -- 查看控制文件记录的数据库检查点 SCN SELECT checkpoint_change# FROM v$database; -- 查看数据文件头中记录的检查点 SCN SELECT file#, name, checkpoint_change#, fuzzy FROM v$datafile_header; -- 数据库scn select current_scn from v$database; CURRENT_SCN -------------------- 2316469 Elapsed: 00:00:00.00 22:48:37 SYSprimary -- 查看控制文件记录的数据库检查点 SCN SELECT checkpoint_change# FROM v$database; CHECKPOINT_CHANGE# -------------------- 2313198 Elapsed: 00:00:00.00 22:48:37 SYSprimary -- 查看数据文件头中记录的检查点 SCN 22:48:37 SYSprimary SELECT file#, name, checkpoint_change#, fuzzy FROM v$datafile_header; FILE# NAME CHECKPOINT_CHANGE# FUZ ----- ---------------------------------- -------------------- --- 1 /oradata/cesdb_mv/system01.dbf 2313198 YES 2 /oradata/cesdb_mv/sysaux01.dbf 2313198 YES 3 /oradata/cesdb_mv/undotbs01.dbf 2313198 YES 4 /oradata/cesdb_mv/users01.dbf 2313198 YES 5 /oradata/cesdb_mv/tbs01_01.dbf 2312841 YES 6 /oradata/scott_mv.dbf 2313198 YES 6 rows selected.Online数据文件SQL alter database datafile /oradata/cesdb_mv/tbs01_01.dbf online; ERROR at line 1: ORA-01113: file 5 needs media recovery ORA-01110: data file 5: /oradata/cesdb_mv/tbs01_01.dbf SQL recover datafile /oradata/cesdb_mv/tbs01_01.dbf; Media recovery complete. SQL alter database datafile /oradata/cesdb_mv/tbs01_01.dbf online; Database altered.执行checkpointSQL alter system checkpoint; Database altered.4.变更验证4.1检查路径和scn查看当前文件路径情况SQL set lin 200 pages 999 col name for a90 select controlfile type,0 file#,name from v$controlfile union all select datafile,file#,name from v$datafile union all select tempfile,file#,name from v$tempfile union all select logfile,group#,member from v$logfile; TYPE FILE# NAME ----------- -------------------- --------------------------------- controlfile 0 /oradata/cesdb_mv/control01.ctl controlfile 0 /oradata/cesdb_mv/control02.ctl datafile 1 /oradata/cesdb_mv/system01.dbf datafile 2 /oradata/cesdb_mv/sysaux01.dbf datafile 3 /oradata/cesdb_mv/undotbs01.dbf datafile 4 /oradata/cesdb_mv/users01.dbf datafile 5 /oradata/cesdb_mv/tbs01_01.dbf datafile 6 /oradata/scott_mv.dbf tempfile 1 /oradata/cesdb_mv/temp01.dbf logfile 3 /oradata/cesdb_mv/redo03.log logfile 2 /oradata/cesdb_mv/redo02.log logfile 1 /oradata/cesdb_mv/redo01.log检查scnSQL -- 数据库scn select current_scn from v$database; -- 查看控制文件记录的数据库检查点 SCN SELECT checkpoint_change# FROM v$database; -- 查看数据文件头中记录的检查点 SCN SELECT file#, name, checkpoint_change#, fuzzy FROM v$datafile_header; -- 数据库scn select current_scn from v$database; CURRENT_SCN -------------------- 2318654 Elapsed: 00:00:00.00 23:14:45 SYSprimary -- 查看控制文件记录的数据库检查点 SCN SELECT checkpoint_change# FROM v$database; CHECKPOINT_CHANGE# -------------------- 2318448 Elapsed: 00:00:00.01 23:14:45 SYSprimary -- 查看数据文件头中记录的检查点 SCN 23:14:45 SYSprimary SELECT file#, name, checkpoint_change#, fuzzy FROM v$datafile_header; FILE# NAME CHECKPOINT_CHANGE# FUZ ------ ------------------------------------------------------ --- 1 /oradata/cesdb_mv/system01.dbf 2318448 YES 2 /oradata/cesdb_mv/sysaux01.dbf 2318448 YES 3 /oradata/cesdb_mv/undotbs01.dbf 2318448 YES 4 /oradata/cesdb_mv/users01.dbf 2318448 YES 5 /oradata/cesdb_mv/tbs01_01.dbf 2318448 YES 6 /oradata/scott_mv.dbf 2318448 YES 6 rows selected.4.2检查文件时间检查新路径文件时间是否比老路径文件时间新$(ORACLE) ls -l /oradata/cesdb_mv/ total 3734868 -rw-r----- 1 oracle oinstall 9748480 May 19 23:17 control01.ctl -rw-r----- 1 oracle oinstall 9748480 May 19 23:17 control02.ctl -rw-r----- 1 oracle oinstall 104858112 May 19 23:17 redo01.log -rw-r----- 1 oracle oinstall 104858112 May 19 22:41 redo02.log -rw-r----- 1 oracle oinstall 104858112 May 19 22:41 redo03.log -rw-r----- 1 oracle oinstall 576724992 May 19 23:11 sysaux01.dbf -rw-r----- 1 oracle oinstall 828383232 May 19 23:11 system01.dbf -rw-r----- 1 oracle oinstall 943726592 May 19 23:11 tbs01_01.dbf -rw-r----- 1 oracle oinstall 56631296 May 19 22:41 temp01.dbf -rw-r----- 1 oracle oinstall 503324672 May 19 23:11 undotbs01.dbf -rw-r----- 1 oracle oinstall 5251072 May 19 23:11 users01.dbf ls -l /oradata/cesdb/ total 3703972 -rw-r----- 1 oracle oinstall 104858112 May 18 18:00 redo01.log -rw-r----- 1 oracle oinstall 104858112 May 18 18:29 redo02.log -rw-r----- 1 oracle oinstall 104858112 May 18 18:00 redo03.log -rw-r----- 1 oracle oinstall 566239232 May 18 18:29 sysaux01.dbf -rw-r----- 1 oracle oinstall 828383232 May 18 18:29 system01.dbf -rw-r----- 1 oracle oinstall 943726592 May 8 16:47 tbs01_01.dbf -rw-r----- 1 oracle oinstall 56631296 May 18 18:00 temp01.dbf -rw-r----- 1 oracle oinstall 503324672 May 18 18:29 undotbs01.dbf -rw-r----- 1 oracle oinstall 5251072 May 18 18:29 users01.dbf5.变更回退如果发现迁移后的数据文件路径有误需要重新迁移或回退可再次按照对应步骤进行如果是虚拟机可以停库后打快照以便变更回退快照如果有全量备份可以恢复全量备份6.变更应急若数据库文件迁移过程中磁盘空间不足则需紧急扩容保证数据库文件迁移正常。检查Oracle单机文件所在磁盘空间是否充足$(ORACLE)df -h