34.Oracle数据库表空间建错位置或者名称不对

最新推荐文章于 2023-07-07 13:47:05 发布

原创最新推荐文章于 2023-07-07 13:47:05 发布 · 744 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#数据库 #oracle

数据库运维-Oracle-常规故障专栏收录该内容

90 篇文章

订阅专栏

该文描述了如何处理数据库表空间数据文件名错误及路径不规范的问题。首先通过SQL查询检查文件位置，然后在Mount状态下移动文件至合适目录，接着修改数据库中的文件名记录，最后打开数据库，确保文件位置和名称正确，强调了创建表空间时指定正确路径和名称的重要性。

1.场景

数据库创建的表空间数据文件名称不对且存放路径不规范。

select * from v$datafile; 查看当前数据文件位置。

/oracle/app/oracle/product/11.2.0/db_1/dbs/E:APPORACLEORADATASCMcon_data.dbf      
/oracle/app/oracle/product/11.2.0/db_1/dbs/E:APPORACLEORADATASCMcon_idx.dbf      
/oracle/app/oracle/product/11.2.0/db_1/dbs/E:APPORACLEORADATASCMraw_data.dbf        
/oracle/app/oracle/product/11.2.0/db_1/dbs/E:APPORACLEORADATASCMraw_idx.dbf       
/oracle/app/oracle/product/11.2.0/db_1/dbs/E:APPORACLEORADATASCMraw_real_data.dbf 
/oracle/app/oracle/product/11.2.0/db_1/dbs/E:APPORACLEORADATASCMraw_real_idx.dbf  
/oracle/app/oracle/product/11.2.0/db_1/dbs/E:APPORACLEORADATASCMhsdef.dbf        
/oracle/app/oracle/product/11.2.0/db_1/dbs/E:APPORACLEORADATASCMbds_data.dbf      
/oracle/app/oracle/product/11.2.0/db_1/dbs/E:APPORACLEORADATASCMwfl_data.dbf      
/oracle/app/oracle/product/11.2.0/db_1/dbs/E:APPORACLEORADATASCMwfl_idx.dbf        
/oracle/app/oracle/product/11.2.0/db_1/dbs/D:APPADMINISTRATORORADATAFERMfsk_data.dbf
/oracle/app/oracle/product/11.2.0/db_1/dbs/D:APPADMINISTRATORORADATAFERMfsk_idx.dbf
/oracle/app/oracle/product/11.2.0/db_1/dbs/D:APPADMINISTRATORORADATAFERMedw_data.dbf
/oracle/app/oracle/product/11.2.0/db_1/dbs/D:APPADMINISTRATORORADATAFERMedw_idx.dbf

2.启动数据库到Mount;

sqlplus / as sysdba
shu immediate 
startup mount

3.移动数据文件到合适的目录

cd /oracle/app/oracle/product/11.2.0/db_1/dbs
mv E:APPORACLEORADATASCMcon_data.dbf            /oracle/oradata/fermczfx/con_data.dbf    
mv E:APPORACLEORADATASCMcon_idx.dbf             /oracle/oradata/fermczfx/con_idx.dbf     
mv E:APPORACLEORADATASCMraw_data.dbf            /oracle/oradata/fermczfx/raw_data.dbf    
mv E:APPORACLEORADATASCMraw_idx.dbf             /oracle/oradata/fermczfx/raw_idx.dbf     
mv E:APPORACLEORADATASCMraw_real_data.dbf       /oracle/oradata/fermczfx/raw_real_data.dbf
mv E:APPORACLEORADATASCMraw_real_idx.dbf        /oracle/oradata/fermczfx/raw_real_idx.dbf
mv E:APPORACLEORADATASCMhsdef.dbf               /oracle/oradata/fermczfx/hsdef.dbf       
mv E:APPORACLEORADATASCMbds_data.dbf            /oracle/oradata/fermczfx/bds_data.dbf   
mv E:APPORACLEORADATASCMwfl_data.dbf            /oracle/oradata/fermczfx/wfl_data.dbf    
mv E:APPORACLEORADATASCMwfl_idx.dbf             /oracle/oradata/fermczfx/wfl_idx.dbf    
mv D:APPADMINISTRATORORADATAFERMfsk_data.dbf    /oracle/oradata/fermczfx/fsk_data.dbf    
mv D:APPADMINISTRATORORADATAFERMfsk_idx.dbf     /oracle/oradata/fermczfx/fsk_idx.dbf    
mv D:APPADMINISTRATORORADATAFERMedw_data.dbf    /oracle/oradata/fermczfx/edw_data.dbf    
mv D:APPADMINISTRATORORADATAFERMedw_idx.dbf     /oracle/oradata/fermczfx/edw_idx.dbf

4.修改数据文件名称

alter database rename file '/oracle/app/oracle/product/11.2.0/db_1/dbs/E:APPORACLEORADATASCMcon_data.dbf'         to '/oracle/oradata/fermczfx/con_data.dbf'       ;
alter database rename file '/oracle/app/oracle/product/11.2.0/db_1/dbs/E:APPORACLEORADATASCMcon_idx.dbf'          to '/oracle/oradata/fermczfx/con_idx.dbf'        ;
alter database rename file '/oracle/app/oracle/product/11.2.0/db_1/dbs/E:APPORACLEORADATASCMraw_data.dbf'         to '/oracle/oradata/fermczfx/raw_data.dbf'       ;
alter database rename file '/oracle/app/oracle/product/11.2.0/db_1/dbs/E:APPORACLEORADATASCMraw_idx.dbf'          to '/oracle/oradata/fermczfx/raw_idx.dbf'        ;
alter database rename file '/oracle/app/oracle/product/11.2.0/db_1/dbs/E:APPORACLEORADATASCMraw_real_data.dbf'    to '/oracle/oradata/fermczfx/raw_real_data.dbf'  ;
alter database rename file '/oracle/app/oracle/product/11.2.0/db_1/dbs/E:APPORACLEORADATASCMraw_real_idx.dbf'     to '/oracle/oradata/fermczfx/raw_real_idx.dbf'   ;
alter database rename file '/oracle/app/oracle/product/11.2.0/db_1/dbs/E:APPORACLEORADATASCMhsdef.dbf'            to '/oracle/oradata/fermczfx/hsdef.dbf'          ;
alter database rename file '/oracle/app/oracle/product/11.2.0/db_1/dbs/E:APPORACLEORADATASCMbds_data.dbf'         to '/oracle/oradata/fermczfx/bds_data.dbf'       ;
alter database rename file '/oracle/app/oracle/product/11.2.0/db_1/dbs/E:APPORACLEORADATASCMwfl_data.dbf'         to '/oracle/oradata/fermczfx/wfl_data.dbf'       ;
alter database rename file '/oracle/app/oracle/product/11.2.0/db_1/dbs/E:APPORACLEORADATASCMwfl_idx.dbf'          to '/oracle/oradata/fermczfx/wfl_idx.dbf'        ;
alter database rename file '/oracle/app/oracle/product/11.2.0/db_1/dbs/D:APPADMINISTRATORORADATAFERMfsk_data.dbf' to '/oracle/oradata/fermczfx/fsk_data.dbf'       ;
alter database rename file '/oracle/app/oracle/product/11.2.0/db_1/dbs/D:APPADMINISTRATORORADATAFERMfsk_idx.dbf'  to '/oracle/oradata/fermczfx/fsk_idx.dbf'        ;
alter database rename file '/oracle/app/oracle/product/11.2.0/db_1/dbs/D:APPADMINISTRATORORADATAFERMedw_data.dbf' to '/oracle/oradata/fermczfx/edw_data.dbf'       ;
alter database rename file '/oracle/app/oracle/product/11.2.0/db_1/dbs/D:APPADMINISTRATORORADATAFERMedw_idx.dbf'  to '/oracle/oradata/fermczfx/edw_idx.dbf'        ;

5.打开数据库

alter database open;

6.总结

至此数据文件已经放到了规范的位置，且名称正确。所以在创建表空间时要明确指定表空间数据文件的存放位置。且名称必须正确，如果没有指定路径则默认会创建到：$ORACLE_HOME/dbs目录下。很显然这里不是存放数据文件最佳路径。很可能导致磁盘爆满而宕机。