Oracle 单实例数据库修改数据文件路径

JiekeXu之路

发布于 2019-07-30 10:53:33

9220

发布于 2019-07-30 10:53:33

文章被收录于专栏：JiekeXu之路

近几日，公司一业务需求要将一些 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$

本文参与腾讯云自媒体同步曝光计划，分享自微信公众号。

原始发表：2019-07-29，如有侵权请联系 cloudcommunity@tencent.com 删除

数据库