近几日，公司一业务需求要将一些 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 denied
Filesystem Size Used Avail Use% Mounted on
/dev/xvda2 67G 66G 16M 100% /
udev 2.1G 68K 2.1G 1% /dev
tmpfs 2.1G 792K 2.1G 1% /dev/shm
/dev/xvdb1 197G 188M 187G 1% /testdata
1、查询数据文件号，文件名，路径，表空间（保存数据记录）
set linesize 200 pagesize 200
col file_name for a50
col tablespace_name for a20
select 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 200
col file_name for a50
col tablespace_name for a20
select 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.dbf
3、查询 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_SID
alter system switch logfile; --切换日志
shutdown immediate
5、将数据文件拷贝到另外的目录（/testdata）使用 oracle 执行，注意权限不变
新建目录/testdata/app/oracle/oradata/JiekeXutest
TEST:/home/oracle$df -h
df: `/root/.gvfs': Permission denied
Filesystem Size Used Avail Use% Mounted on
/dev/xvda2 67G 66G 16M 100% /
udev 2.1G 68K 2.1G 1% /dev
tmpfs 2.1G 792K 2.1G 1% /dev/shm
/dev/xvdb1 197G 188M 187G 1% /testdata
TEST:/testdata$mkdir -p /testdata/app/oracle/oradata/JiekeXutest
TEST:/testdata$cd /app/oracle/oradata/JiekeXutest
TEST:/app/oracle/oradata/JiekeXutest$ll
total 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.dbf
TEST:/app/oracle/oradata/JiekeXutest$pwd
/app/oracle/oradata/JiekeXutest
TEST:/app/oracle/oradata/JiekeXutest$mv *.dbf /testdata/app/oracle/oradata/JiekeXutest/
6、启动数据库到mount
startup mount
7、更改数据库普通文件，临时，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 files
ORA-01141: error renaming data file 6 - new file
'/testdata/app/oracle/oradata/JiekeXutest/test.dbf' not found
ORA-01110: data file 6: '/app/product/11.2.0/db/dbs/D:test.ora'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional 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 200
SQL> col file_name for a50
SQL> col tablespace_name for a20
select 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 30
f
/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 200
SQL> col file_name for a50
SQL> col tablespace_name for a20
select 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.log
SQL>
10、查看文件系统大小
查看文件系统大小发现根目录也已经到 15%，任务完成，完美收工，故此记录一下！
TEST:/testdata/app/oracle/oradata/JiekeXutest$df -h
df: `/root/.gvfs': Permission denied
Filesystem Size Used Avail Use% Mounted on
/dev/xvda2 67G 9.9G 57G 15% /
udev 2.1G 68K 2.1G 1% /dev
tmpfs 2.1G 792K 2.1G 1% /dev/shm
/dev/xvdb1 197G 57G 131G 31% /testdata
TEST:/testdata/app/oracle/oradata/JiekeXutest$
80%
推荐阅读：
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，前端，大数据，具体获取方式可添加我微信获取~~~
（长按添加微信公众号，更多精彩内容不错过！）
码字不易，点赞、转发是一种对作者的鼓励！ 
1095
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
