修改Oracle数据文件名及数据文件存放路径
发表于：2025-02-03 作者：千家信息网编辑
千家信息网最后更新 2025年02月03日，更改Oracle数据文件名及数据文件存放路径 SQL> select * from v$dbfile;FILE# NAME---------- ----------------------------
千家信息网最后更新 2025年02月03日修改Oracle数据文件名及数据文件存放路径更改Oracle数据文件名及数据文件存放路径 SQL> select * from v$dbfile;
FILE# NAME
---------- --------------------------------------------------------------------------------
1 /db2/oracle/oradata/db2/system01.dbf
2 /db2/oracle/oradata/db2/sysaux01.dbf
3 /db2/oracle/oradata/db2/undotbs01.dbf
4 /db2/oracle/oradata/db2/users01.dbf
5 /db2/oracle/product/11.2.0/db_1/dbs/D:ORACLE11GADMINORADATAADMINgg01.dbf
6 /db2/oracle/oradata/db2/db201.dbf
一、关闭数据库进行修改
[oracle@db1 ~]$ sqlplus / as sysdba SQL*Plus: Release 11.2.0.1.0 Production on Thu Jul 26 17:43:38 2012 Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down. [oracle@db1 ~]$ mv /db2/oracle/product/11.2.0/db_1/dbs/D\:ORACLE11GADMINORADATAADMINgg01.dbf /db2/oracle/oradata/db2/gg01.dbf SQL*Plus: Release 11.2.0.1.0 Production on Thu Jul 26 17:48:31 2012 Copyright (c) 1982, 2009, Oracle. All rights reserved. Connected to an idle instance. SQL> startup mount
ORACLE instance started. Total System Global Area 521936896 bytes
Fixed Size 2214936 bytes
Variable Size 163578856 bytes
Database Buffers 352321536 bytes
Redo Buffers 3821568 bytes
Database mounted. SQL> alter database rename file '/db2/oracle/product/11.2.0/db_1/dbs/D\:ORACLE11GADMINORADATAADMINgg01.dbf' to '/db2/oracle/oradata/db2/gg01.dbf'; Database altered. SQL> select * from v$dbfile;
FILE# NAME
---------- --------------------------------------------------------------------------------
1 /db2/oracle/oradata/db2/system01.dbf
2 /db2/oracle/oradata/db2/sysaux01.dbf
3 /db2/oracle/oradata/db2/undotbs01.dbf
4 /db2/oracle/oradata/db2/users01.dbf
5 /db2/oracle/oradata/db2/gg01.dbf
6 /db2/oracle/oradata/db2/db201.dbf
6 rows selected 二、在线修改
方法1:表空间offline
SQL> select * from dba_data_files;
FILE_NAME FILE_ID TABLESPACE_NAME BYTES BLOCKS STATUS RELATIVE_FNO AUTOEXTENSIBLE MAXBYTES MAXBLOCKS INCREMENT_BY USER_BYTES USER_BLOCKS ONLINE_STATUS
-------------------------------------------------------------------------------- ---------- ------------------------------ ---------- ---------- --------- ------------ -------------- ---------- ---------- ------------ ---------- ----------- -------------
/db2/oracle/oradata/db2/system01.dbf 1 SYSTEM 734003200 89600 AVAILABLE 1 YES 3435972198 4194302 1280 732954624 89472 SYSTEM
/db2/oracle/oradata/db2/sysaux01.dbf 2 SYSAUX 629145600 76800 AVAILABLE 2 YES 3435972198 4194302 1280 628097024 76672 ONLINE
/db2/oracle/oradata/db2/undotbs01.dbf 3 UNDOTBS1 618659840 75520 AVAILABLE 3 YES 3435972198 4194302 640 617611264 75392 ONLINE
/db2/oracle/oradata/db2/users01.dbf 4 USERS 5242880 640 AVAILABLE 4 YES 3435972198 4194302 160 4194304 512 ONLINE
/db2/oracle/oradata/db2/gg01.dbf 5 GG 104857600 12800 AVAILABLE 5 NO 0 0 0 103809024 12672 ONLINE
/db2/oracle/oradata/db2/db201.dbf 6 DB2 104857600 12800 AVAILABLE 6 NO 0 0 0 103809024 12672 ONLINE
SQL> alter tablespace gg offline;
Tablespace altered SQL> host mv /db2/oracle/oradata/db2/gg01.dbf /db2/oracle/oradata/db2/admin.dbf SQL> alter database rename file '/db2/oracle/oradata/db2/gg01.dbf' to '/db2/oracle/oradata/db2/admin.dbf';
Database altered SQL> alter tablespace gg online;
Tablespace altered SQL> select * from dba_data_files;
FILE_NAME FILE_ID TABLESPACE_NAME BYTES BLOCKS STATUS RELATIVE_FNO AUTOEXTENSIBLE MAXBYTES MAXBLOCKS INCREMENT_BY USER_BYTES USER_BLOCKS ONLINE_STATUS
-------------------------------------------------------------------------------- ---------- ------------------------------ ---------- ---------- --------- ------------ -------------- ---------- ---------- ------------ ---------- ----------- -------------
/db2/oracle/oradata/db2/system01.dbf 1 SYSTEM 734003200 89600 AVAILABLE 1 YES 3435972198 4194302 1280 732954624 89472 SYSTEM
/db2/oracle/oradata/db2/sysaux01.dbf 2 SYSAUX 629145600 76800 AVAILABLE 2 YES 3435972198 4194302 1280 628097024 76672 ONLINE
/db2/oracle/oradata/db2/undotbs01.dbf 3 UNDOTBS1 618659840 75520 AVAILABLE 3 YES 3435972198 4194302 640 617611264 75392 ONLINE
/db2/oracle/oradata/db2/users01.dbf 4 USERS 5242880 640 AVAILABLE 4 YES 3435972198 4194302 160 4194304 512 ONLINE
/db2/oracle/oradata/db2/admin.dbf 5 GG 104857600 12800 AVAILABLE 5 NO 0 0 0 103809024 12672 ONLINE
/db2/oracle/oradata/db2/db201.dbf 6 DB2 104857600 12800 AVAILABLE 6 NO 0 0 0 103809024 12672 ONLINE
方法2:数据文件脱机 （方法2必须在数据库归档模式下进行)
SQL> alter database datafile 5 offline;
alter database datafile 5 offline
*
ERROR at line 1:
ORA-01145: offline immediate disallowed unless media recovery enabled SQL> archive log list;
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 69
Current log sequence 71 开归档
[oracle@db1 ~]$ mkdir /db2/oracle/archivelog
[oracle@db1 ~]$ sqlplus / as sysdba SQL*Plus: Release 11.2.0.1.0 Production on Thu Jul 26 18:15:52 2012 Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down. SQL> startup mount
ORACLE instance started. Total System Global Area 521936896 bytes
Fixed Size 2214936 bytes
Variable Size 163578856 bytes
Database Buffers 352321536 bytes
Redo Buffers 3821568 bytes
Database mounted.
SQL> alter system set log_archive_dest_1='location=/db2/oracle/archivelog/'; System altered. SQL> alter database archivelog; Database altered. SQL> alter database open; Database altered. SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /db2/oracle/archivelog/
Oldest online log sequence 69
Next log sequence to archive 71
Current log sequence 71 SQL> alter database datafile 5 offline; Database altered. SQL> host mv /db2/oracle/oradata/db2/admin.dbf /db2/oracle/oradata/db2/gg01.dbf SQL> alter database rename file '/db2/oracle/oradata/db2/admin.dbf' to '/db2/oracle/oradata/db2/gg01.dbf'; Database altered. SQL> alter database datafile 5 online;
alter database datafile 5 online
*
ERROR at line 1:
ORA-01113: file 5 needs media recovery
ORA-01110: data file 5: '/db2/oracle/oradata/db2/gg01.dbf'
SQL> recover datafile 5;
Media recovery complete. SQL> alter database datafile 5 online; Database altered. SQL> select file_id,file_name,tablespace_name,online_status from dba_data_files;
FILE_ID FILE_NAME TABLESPACE_NAME ONLINE_STATUS
---------- -------------------------------------------------------------------------------- ------------------------------ -------------
1 /db2/oracle/oradata/db2/system01.dbf SYSTEM SYSTEM
2 /db2/oracle/oradata/db2/sysaux01.dbf SYSAUX ONLINE
3 /db2/oracle/oradata/db2/undotbs01.dbf UNDOTBS1 ONLINE
4 /db2/oracle/oradata/db2/users01.dbf USERS ONLINE
5 /db2/oracle/oradata/db2/gg01.dbf GG ONLINE
6 /db2/oracle/oradata/db2/db201.dbf DB2 ONLINE
FILE# NAME
---------- --------------------------------------------------------------------------------
1 /db2/oracle/oradata/db2/system01.dbf
2 /db2/oracle/oradata/db2/sysaux01.dbf
3 /db2/oracle/oradata/db2/undotbs01.dbf
4 /db2/oracle/oradata/db2/users01.dbf
5 /db2/oracle/product/11.2.0/db_1/dbs/D:ORACLE11GADMINORADATAADMINgg01.dbf
6 /db2/oracle/oradata/db2/db201.dbf
一、关闭数据库进行修改
[oracle@db1 ~]$ sqlplus / as sysdba SQL*Plus: Release 11.2.0.1.0 Production on Thu Jul 26 17:43:38 2012 Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down. [oracle@db1 ~]$ mv /db2/oracle/product/11.2.0/db_1/dbs/D\:ORACLE11GADMINORADATAADMINgg01.dbf /db2/oracle/oradata/db2/gg01.dbf SQL*Plus: Release 11.2.0.1.0 Production on Thu Jul 26 17:48:31 2012 Copyright (c) 1982, 2009, Oracle. All rights reserved. Connected to an idle instance. SQL> startup mount
ORACLE instance started. Total System Global Area 521936896 bytes
Fixed Size 2214936 bytes
Variable Size 163578856 bytes
Database Buffers 352321536 bytes
Redo Buffers 3821568 bytes
Database mounted. SQL> alter database rename file '/db2/oracle/product/11.2.0/db_1/dbs/D\:ORACLE11GADMINORADATAADMINgg01.dbf' to '/db2/oracle/oradata/db2/gg01.dbf'; Database altered. SQL> select * from v$dbfile;
FILE# NAME
---------- --------------------------------------------------------------------------------
1 /db2/oracle/oradata/db2/system01.dbf
2 /db2/oracle/oradata/db2/sysaux01.dbf
3 /db2/oracle/oradata/db2/undotbs01.dbf
4 /db2/oracle/oradata/db2/users01.dbf
5 /db2/oracle/oradata/db2/gg01.dbf
6 /db2/oracle/oradata/db2/db201.dbf
6 rows selected 二、在线修改
方法1:表空间offline
SQL> select * from dba_data_files;
FILE_NAME FILE_ID TABLESPACE_NAME BYTES BLOCKS STATUS RELATIVE_FNO AUTOEXTENSIBLE MAXBYTES MAXBLOCKS INCREMENT_BY USER_BYTES USER_BLOCKS ONLINE_STATUS
-------------------------------------------------------------------------------- ---------- ------------------------------ ---------- ---------- --------- ------------ -------------- ---------- ---------- ------------ ---------- ----------- -------------
/db2/oracle/oradata/db2/system01.dbf 1 SYSTEM 734003200 89600 AVAILABLE 1 YES 3435972198 4194302 1280 732954624 89472 SYSTEM
/db2/oracle/oradata/db2/sysaux01.dbf 2 SYSAUX 629145600 76800 AVAILABLE 2 YES 3435972198 4194302 1280 628097024 76672 ONLINE
/db2/oracle/oradata/db2/undotbs01.dbf 3 UNDOTBS1 618659840 75520 AVAILABLE 3 YES 3435972198 4194302 640 617611264 75392 ONLINE
/db2/oracle/oradata/db2/users01.dbf 4 USERS 5242880 640 AVAILABLE 4 YES 3435972198 4194302 160 4194304 512 ONLINE
/db2/oracle/oradata/db2/gg01.dbf 5 GG 104857600 12800 AVAILABLE 5 NO 0 0 0 103809024 12672 ONLINE
/db2/oracle/oradata/db2/db201.dbf 6 DB2 104857600 12800 AVAILABLE 6 NO 0 0 0 103809024 12672 ONLINE
SQL> alter tablespace gg offline;
Tablespace altered SQL> host mv /db2/oracle/oradata/db2/gg01.dbf /db2/oracle/oradata/db2/admin.dbf SQL> alter database rename file '/db2/oracle/oradata/db2/gg01.dbf' to '/db2/oracle/oradata/db2/admin.dbf';
Database altered SQL> alter tablespace gg online;
Tablespace altered SQL> select * from dba_data_files;
FILE_NAME FILE_ID TABLESPACE_NAME BYTES BLOCKS STATUS RELATIVE_FNO AUTOEXTENSIBLE MAXBYTES MAXBLOCKS INCREMENT_BY USER_BYTES USER_BLOCKS ONLINE_STATUS
-------------------------------------------------------------------------------- ---------- ------------------------------ ---------- ---------- --------- ------------ -------------- ---------- ---------- ------------ ---------- ----------- -------------
/db2/oracle/oradata/db2/system01.dbf 1 SYSTEM 734003200 89600 AVAILABLE 1 YES 3435972198 4194302 1280 732954624 89472 SYSTEM
/db2/oracle/oradata/db2/sysaux01.dbf 2 SYSAUX 629145600 76800 AVAILABLE 2 YES 3435972198 4194302 1280 628097024 76672 ONLINE
/db2/oracle/oradata/db2/undotbs01.dbf 3 UNDOTBS1 618659840 75520 AVAILABLE 3 YES 3435972198 4194302 640 617611264 75392 ONLINE
/db2/oracle/oradata/db2/users01.dbf 4 USERS 5242880 640 AVAILABLE 4 YES 3435972198 4194302 160 4194304 512 ONLINE
/db2/oracle/oradata/db2/admin.dbf 5 GG 104857600 12800 AVAILABLE 5 NO 0 0 0 103809024 12672 ONLINE
/db2/oracle/oradata/db2/db201.dbf 6 DB2 104857600 12800 AVAILABLE 6 NO 0 0 0 103809024 12672 ONLINE
方法2:数据文件脱机 （方法2必须在数据库归档模式下进行)
SQL> alter database datafile 5 offline;
alter database datafile 5 offline
*
ERROR at line 1:
ORA-01145: offline immediate disallowed unless media recovery enabled SQL> archive log list;
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 69
Current log sequence 71 开归档
[oracle@db1 ~]$ mkdir /db2/oracle/archivelog
[oracle@db1 ~]$ sqlplus / as sysdba SQL*Plus: Release 11.2.0.1.0 Production on Thu Jul 26 18:15:52 2012 Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down. SQL> startup mount
ORACLE instance started. Total System Global Area 521936896 bytes
Fixed Size 2214936 bytes
Variable Size 163578856 bytes
Database Buffers 352321536 bytes
Redo Buffers 3821568 bytes
Database mounted.
SQL> alter system set log_archive_dest_1='location=/db2/oracle/archivelog/'; System altered. SQL> alter database archivelog; Database altered. SQL> alter database open; Database altered. SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /db2/oracle/archivelog/
Oldest online log sequence 69
Next log sequence to archive 71
Current log sequence 71 SQL> alter database datafile 5 offline; Database altered. SQL> host mv /db2/oracle/oradata/db2/admin.dbf /db2/oracle/oradata/db2/gg01.dbf SQL> alter database rename file '/db2/oracle/oradata/db2/admin.dbf' to '/db2/oracle/oradata/db2/gg01.dbf'; Database altered. SQL> alter database datafile 5 online;
alter database datafile 5 online
*
ERROR at line 1:
ORA-01113: file 5 needs media recovery
ORA-01110: data file 5: '/db2/oracle/oradata/db2/gg01.dbf'
SQL> recover datafile 5;
Media recovery complete. SQL> alter database datafile 5 online; Database altered. SQL> select file_id,file_name,tablespace_name,online_status from dba_data_files;
FILE_ID FILE_NAME TABLESPACE_NAME ONLINE_STATUS
---------- -------------------------------------------------------------------------------- ------------------------------ -------------
1 /db2/oracle/oradata/db2/system01.dbf SYSTEM SYSTEM
2 /db2/oracle/oradata/db2/sysaux01.dbf SYSAUX ONLINE
3 /db2/oracle/oradata/db2/undotbs01.dbf UNDOTBS1 ONLINE
4 /db2/oracle/oradata/db2/users01.dbf USERS ONLINE
5 /db2/oracle/oradata/db2/gg01.dbf GG ONLINE
6 /db2/oracle/oradata/db2/db201.dbf DB2 ONLINE
数据
文件
方法
数据库
文件名
路径
模式
空间
在线
脱机
数据库的安全要保护哪些东西
数据库安全各自的含义是什么
生产安全数据库录入
数据库的安全性及管理
数据库安全策略包含哪些
海淀数据库安全审计系统
建立农村房屋安全信息数据库
易用的数据库客户端支持安全管理
连接数据库失败ssl安全错误
数据库的锁怎样保障安全
上位机软件开发的背景
部署网络安全证书
监控服务器哪种好
web服务器免费教程
阿里云服务器不能关机
中卫软件开发优化价格
数据库网站起名
个人电脑服务器密码查询
软件开发薪酬报告 2020
网络安全顺口溜短句
售后服务器什么牌子好
世诺科技软件开发
照片排版软件开发
学软件开发里面包含学编程吗
广西桂林软件开发公司
金蝶软件开发支出科目代码
巴可icmp服务器指令怎么导入
网络诈骗属不属于网络安全
数据库防火墙的作用
最新网络技术知识
陕西网络安全防护企业排名
坚果投影仪服务器停止服务
软件开发问题探讨
长垣微速达互联网科技有限公司
itrim数据库
php怎么查看数据库
ios软件开发 毕业设计
如何用服务器做数据分析
互联网智能科技视频
扩展器怎么无法连接服务器
