其他
Oracle 单实例数据库修改数据文件路径
近几日，公司一业务需求要将一些 dump 文件导入到测试库，但是却有一个头痛的事，发现数据库所在的根目录已经 100%，这样肯定是没办法导入 dump 数据文件的,而且数据库已面临巨大的宕机风险了，故申请了临时停机窗口，打算将原有的数据文件移动到其他新挂载的盘符中。
由于此测试库是单机版的且使用文件系统管理，故采用 alter database rename file 'XXX' to 'XXX'; 来实现这个。
说明：此环境为 Linux CentOS6.7 Oracle11.2.0.4
0、检查发现根目录 100%
TEST:/home/oracle$df -h df: `/root/.gvfs': Permission deniedFilesystem Size Used Avail Use% Mounted on/dev/xvda2 67G 66G 16M 100% /udev 2.1G 68K 2.1G 1% /devtmpfs 2.1G 792K 2.1G 1% /dev/shm/dev/xvdb1 197G 188M 187G 1% /testdata1、查询数据文件号，文件名，路径，表空间（保存数据记录）
set linesize 200 pagesize 200col file_name for a50col tablespace_name for a20select file_name,FILE_ID,TABLESPACE_NAME,BYTES/1024/1024/1024 from dba_Data_files order by 2;
FILE_NAME FILE_ID TABLESPACE_NAME BYTES/1024/1024/1024-------------------------------------------------- ---------- -------------------- --------------------/app/oracle/oradata/JiekeXutest/system01.dbf 1 SYSTEM 3.21289063/app/oracle/oradata/JiekeXutest/sysaux01.dbf 2 SYSAUX 1.5234375/app/oracle/oradata/JiekeXutest/undotbs01.dbf 3 UNDOTBS1 30/app/oracle/oradata/JiekeXutest/users01.dbf 4 USERS 6.6784668/app/oracle/oradata/JiekeXutest/rhzx01.dbf 5 RHZX 13.2151489/app/product/11.2.0/db/dbs/D:test.ora 6 TEST .9765625
6 rows selected.2、查询临时文件（保存数据）
set linesize 200 pagesize 200col file_name for a50col tablespace_name for a20select file_name,FILE_ID,TABLESPACE_NAME,BYTES/1024/1024/1024 from dba_temp_files order by 2;FILE_NAME FILE_ID TABLESPACE_NAME BYTES/1024/1024/1024-------------------------------------------------- ---------- -------------------- --------------------/app/oracle/oradata/JiekeXutest/temp01.dbf3、查询 redo（保存数据）
set linesize 150;set pagesize 50;column MB format a10;column STATUS format a12;column MEMBER format a60;select l.GROUP#,l.THREAD#,l.members,l.BYTES/1024/1024||'MB' MB,l.STATUS, lf.TYPE,lf.MEMBER from v$log l,v$logfile lf where l.GROUP#=lf.GROUP#; GROUP# THREAD# MEMBERS MB STATUS TYPE MEMBER---------- ---------- ---------- ---------- ------------ ------- ------------------------------------------------------------ 3 1 1 50MB CURRENT ONLINE /app/oracle/oradata/JiekeXutest/redo03.log 2 1 1 50MB INACTIVE ONLINE /app/oracle/oradata/JiekeXutest/redo02.log 1 1 1 50MB INACTIVE ONLINE /app/oracle/oradata/JiekeXutest/redo01.log
4、关闭数据库
ps -ef |grep smon echo $ORACLE_SIDalter system switch logfile; --切换日志shutdown immediate5、将数据文件拷贝到另外的目录（/testdata）使用 oracle 执行，注意权限不变
新建目录/testdata/app/oracle/oradata/JiekeXutest
TEST:/home/oracle$df -h df: `/root/.gvfs': Permission deniedFilesystem Size Used Avail Use% Mounted on/dev/xvda2 67G 66G 16M 100% /udev 2.1G 68K 2.1G 1% /devtmpfs 2.1G 792K 2.1G 1% /dev/shm/dev/xvdb1 197G 188M 187G 1% /testdataTEST:/testdata$mkdir -p /testdata/app/oracle/oradata/JiekeXutestTEST:/testdata$cd /app/oracle/oradata/JiekeXutestTEST:/app/oracle/oradata/JiekeXutest$lltotal 58787740-rw-r----- 1 oracle oinstall 9846784 Jul 19 21:00 control01.ctl-rw-r----- 1 oracle oinstall 52429312 Jul 19 20:59 redo01.log-rw-r----- 1 oracle oinstall 52429312 Jul 19 21:00 redo02.log-rw-r----- 1 oracle oinstall 52429312 Jul 19 21:00 redo03.log-rw-r----- 1 oracle oinstall 14189666304 Jul 19 21:00 rhzx01.dbf-rw-r----- 1 oracle oinstall 1635786752 Jul 19 21:00 sysaux01.dbf-rw-r----- 1 oracle oinstall 3449823232 Jul 19 21:00 system01.dbf-rw-r----- 1 oracle oinstall 1326456832 Jul 19 17:00 temp01.dbf-rw-r----- 1 oracle oinstall 32212262912 Jul 19 21:00 undotbs01.dbf-rw-r----- 1 oracle oinstall 7170957312 Jul 19 21:00 users01.dbfTEST:/app/oracle/oradata/JiekeXutest$pwd/app/oracle/oradata/JiekeXutestTEST:/app/oracle/oradata/JiekeXutest$mv *.dbf /testdata/app/oracle/oradata/JiekeXutest/6、启动数据库到mount
startup mount7、更改数据库普通文件，临时，redo名称
alter database rename file '/app/oracle/oradata/JiekeXutest/system01.dbf' to '/testdata/app/oracle/oradata/JiekeXutest/system01.dbf'; alter database rename file '/app/oracle/oradata/JiekeXutest/sysaux01.dbf' to '/testdata/app/oracle/oradata/JiekeXutest/sysaux01.dbf'; alter database rename file '/app/oracle/oradata/JiekeXutest/undotbs01.dbf' to '/testdata/app/oracle/oradata/JiekeXutest/undotbs01.dbf';alter database rename file '/app/oracle/oradata/JiekeXutest/users01.dbf' to '/testdata/app/oracle/oradata/JiekeXutest/users01.dbf'; alter database rename file '/app/oracle/oradata/JiekeXutest/rhzx01.dbf' to '/testdata/app/oracle/oradata/JiekeXutest/rhzx01.dbf';--alter database rename file '/app/product/11.2.0/db/dbs/D:test.ora' to '/testdata/app/oracle/oradata/JiekeXutest/test.dbf'; --这个D盘没法更改，路径不对，无法辨认，故暂时放弃ERROR at line 1:ORA-01511: error in renaming log/data filesORA-01141: error renaming data file 6 - new file'/testdata/app/oracle/oradata/JiekeXutest/test.dbf' not foundORA-01110: data file 6: '/app/product/11.2.0/db/dbs/D:test.ora'ORA-27037: unable to obtain file statusLinux-x86_64 Error: 2: No such file or directoryAdditional information: 3
alter database rename file '/app/oracle/oradata/JiekeXutest/temp01.dbf' to '/testdata/app/oracle/oradata/JiekeXutest/temp01.dbf'; alter database rename file '/app/oracle/oradata/JiekeXutest/redo01.log' to '/testdata/app/oracle/oradata/JiekeXutest/redo01.log';alter database rename file '/app/oracle/oradata/JiekeXutest/redo02.log' to '/testdata/app/oracle/oradata/JiekeXutest/redo02.log';alter database rename file '/app/oracle/oradata/JiekeXutest/redo03.log' to '/testdata/app/oracle/oradata/JiekeXutest/redo03.log';8、打开数据库
SQL> alter database open;Database altered.SQL> SQL> 9、检查新的数据文件路径
检查各个数据文件发现已经到新的路径下了。
SQL> set linesize 200 pagesize 200SQL> col file_name for a50SQL> col tablespace_name for a20select file_name,FILE_ID,TABLESPACE_NAME,BYTES/1024/1024/1024 from dba_Data_files order by 2;SQL>
FILE_NAME FILE_ID TABLESPACE_NAME BYTES/1024/1024/1024-------------------------------------------------- ---------- -------------------- --------------------/testdata/app/oracle/oradata/JiekeXutest/system01.dbf 1 SYSTEM 3.21289063/testdata/app/oracle/oradata/JiekeXutest/sysaux01.dbf 2 SYSAUX 1.5234375/testdata/app/oracle/oradata/JiekeXutest/undotbs01.db 3 UNDOTBS1 30f
/testdata/app/oracle/oradata/JiekeXutest/users01.dbf 4 USERS 6.6784668/testdata/app/oracle/oradata/JiekeXutest/rhzx01.dbf 5 RHZX 13.2151489/app/product/11.2.0/db/dbs/D:test.ora 6 TEST .9765625
6 rows selected.
SQL> set linesize 200 pagesize 200SQL> col file_name for a50SQL> col tablespace_name for a20select file_name,FILE_ID,TABLESPACE_NAME,BYTES/1024/1024/1024 from dba_temp_files order by 2;SQL>
FILE_NAME FILE_ID TABLESPACE_NAME BYTES/1024/1024/1024-------------------------------------------------- ---------- -------------------- --------------------/testdata/app/oracle/oradata/JiekeXutest/temp01.dbf 1 TEMP 1.23535156
SQL> set linesize 150;SQL> set pagesize 50;SQL> column MB format a10;column STATUS format a12;column MEMBER format a60;select l.GROUP#,l.THREAD#,l.members,l.BYTES/1024/1024||'MB' MB,l.STATUS, lf.TYPE,lf.MEMBER from v$log l,v$logfile lf where l.GROUP#=lf.GROUP#; SQL> SQL> SQL> GROUP# THREAD# MEMBERS MB STATUS TYPE MEMBER---------- ---------- ---------- ---------- ------------ ------- ------------------------------------------------------------ 3 1 1 50MB CURRENT ONLINE /testdata/app/oracle/oradata/JiekeXutest/redo03.log 2 1 1 50MB INACTIVE ONLINE /testdata/app/oracle/oradata/JiekeXutest/redo02.log 1 1 1 50MB INACTIVE ONLINE /testdata/app/oracle/oradata/JiekeXutest/redo01.logSQL> 10、查看文件系统大小
查看文件系统大小发现根目录也已经到 15%，任务完成，完美收工，故此记录一下！
TEST:/testdata/app/oracle/oradata/JiekeXutest$df -h df: `/root/.gvfs': Permission deniedFilesystem Size Used Avail Use% Mounted on/dev/xvda2 67G 9.9G 57G 15% /udev 2.1G 68K 2.1G 1% /devtmpfs 2.1G 792K 2.1G 1% /dev/shm/dev/xvdb1 197G 57G 131G 31% /testdataTEST:/testdata/app/oracle/oradata/JiekeXutest$推荐阅读：
Linux Oracle 11.2.0.4 单机数据库升级至最新补丁安装指北
万字详解Oracle架构、原理、进程，学会世间再无复杂架构
Oracle 11GR2 RAC 最新补丁 190416 安装指导
Oracle 11gR2 RAC 集群服务启动与关闭总结
CentOS6.7安装PostgreSQL10.9详细教程
史上最全的 OGG 基础知识整理
资源分享：
5T 技术资源大放送！包括但不限于：Linux，Python，Oracle，MySQL，Java，前端，大数据，具体获取方式可添加我微信获取~~~
（长按添加微信公众号，更多精彩内容不错过！）
码字不易，点赞、转发是一种对作者的鼓励！