前往小程序,Get更优阅读体验!
立即前往
首页
学习
活动
专区
工具
TVP
发布
社区首页 >专栏 >Oracle 单实例数据库修改数据文件路径

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%

代码语言:javascript
复制
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、查询数据文件号,文件名,路径,表空间(保存数据记录)

代码语言:javascript
复制
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、查询临时文件(保存数据)

代码语言:javascript
复制
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(保存数据)

代码语言:javascript
复制
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、关闭数据库

代码语言:javascript
复制
ps -ef |grep smon      
echo $ORACLE_SID
alter system switch logfile;  --切换日志
shutdown immediate

5、将数据文件拷贝到另外的目录(/testdata)使用 oracle 执行,注意权限不变

新建目录/testdata/app/oracle/oradata/JiekeXutest

代码语言:javascript
复制
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

代码语言:javascript
复制
startup mount

7、更改数据库普通文件,临时,redo名称

代码语言:javascript
复制
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、打开数据库

代码语言:javascript
复制
SQL> alter database open;
Database altered.
SQL> 
SQL> 

9、检查新的数据文件路径

检查各个数据文件发现已经到新的路径下了。

代码语言:javascript
复制
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%,任务完成,完美收工,故此记录一下!

代码语言:javascript
复制
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 删除

本文分享自 JiekeXu之路 微信公众号,前往查看

如有侵权,请联系 cloudcommunity@tencent.com 删除。

本文参与?腾讯云自媒体同步曝光计划? ,欢迎热爱写作的你一起参与!

评论
登录后参与评论
0 条评论
热度
最新
推荐阅读
相关产品与服务
数据库
云数据库为企业提供了完善的关系型数据库、非关系型数据库、分析型数据库和数据库生态工具。您可以通过产品选择和组合搭建,轻松实现高可靠、高可用性、高性能等数据库需求。云数据库服务也可大幅减少您的运维工作量,更专注于业务发展,让企业一站式享受数据上云及分布式架构的技术红利!
领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档
http://www.vxiaotou.com