用重建控制文件的方法修改oracle数据文件路径

1、查看数据文件的位置：
SQL> select file_name,tablespace_name,status from dba_data_files;

FILE_NAME                                                                                                                      
-----------------------------------------------------------------------------------------------------------------------------
E:\ORACLE\ORADATA\TONES\SYSTEM01.DBF                                                                                           
E:\ORACLE\ORADATA\TONES\UNDOTBS01.DBF                                                                                          
E:\ORACLE\ORADATA\TONES\CWMLITE01.DBF                                                                                          
E:\ORACLE\ORADATA\TONES\DRSYS01.DBF                                                                                            
E:\ORACLE\ORADATA\TONES\EXAMPLE01.DBF                                                                                          
E:\ORACLE\ORADATA\TONES\INDX01.DBF                                                                                             
E:\ORACLE\ORADATA\TONES\ODM01.DBF                                                                                              
E:\ORACLE\ORADATA\TONES\TOOLS01.DBF                                                                                            
E:\ORACLE\ORADATA\TONES\USERS01.DBF                                                                                            
E:\ORACLE\ORADATA\TONES\XDB01.DBF                                                                                              
E:\ORACLE\ERP\ERP01.DBF                                                                                                        

FILE_NAME                                                                                                                      
-----------------------------------------------------------------------------------------------------------------------------
E:\ORACLE\ORADATA\TONES\BRM01.DBF                                                                                              
E:\ORACLE\ERP\RMANDB01.DBF                                                                                                     

已选择13行。

2、关闭数据库，拷贝ERP下的文件到E:\ORACLE\ORADATA\TONES\中
SQL> shutdown immediate;
数据库已经关闭。
已经卸载数据库。
ORACLE 例程已经关闭。

3、启动到mount下
SQL> startup mount
ORACLE 例程已经启动。

Total System Global Area  126950220 bytes
Fixed Size                   453452 bytes
Variable Size             109051904 bytes
Database Buffers           16777216 bytes
Redo Buffers                 667648 bytes
数据库装载完毕。

4、备份控制文件，在udump目录下产生trace文件：
SQL> alter database backup controlfile to trace;
数据库已更改。

5、关闭数据库，编辑产生的trc文件。
SQL> shutdown immediate;
ORA-01109: 数据库未打开

已经卸载数据库。
ORACLE 例程已经关闭。

6、启动数据库到nomount下，执行创建控制文件的语句
SQL> startup nomount;
ORACLE 例程已经启动。

Total System Global Area  126950220 bytes
Fixed Size                   453452 bytes
Variable Size             109051904 bytes
Database Buffers           16777216 bytes
Redo Buffers                 667648 bytes

SQL> CREATE CONTROLFILE REUSE DATABASE "TONES" NORESETLOGS FORCE LOGGING ARCHIVELOG
  2      MAXLOGFILES 50
  3      MAXLOGMEMBERS 5
  4      MAXDATAFILES 100
  5      MAXINSTANCES 1
  6      MAXLOGHISTORY 226
  7  LOGFILE
  8    GROUP 1 'E:\ORACLE\ORADATA\TONES\REDO01.LOG'  SIZE 100M,
  9    GROUP 2 'E:\ORACLE\ORADATA\TONES\REDO02.LOG'  SIZE 100M,
 10    GROUP 3 'E:\ORACLE\ORADATA\TONES\REDO03.LOG'  SIZE 100M
 11  DATAFILE
 12    'E:\ORACLE\ORADATA\TONES\SYSTEM01.DBF',
 13    'E:\ORACLE\ORADATA\TONES\UNDOTBS01.DBF',
 14    'E:\ORACLE\ORADATA\TONES\CWMLITE01.DBF',
 15    'E:\ORACLE\ORADATA\TONES\DRSYS01.DBF',
 16    'E:\ORACLE\ORADATA\TONES\EXAMPLE01.DBF',
 17    'E:\ORACLE\ORADATA\TONES\INDX01.DBF',
 18    'E:\ORACLE\ORADATA\TONES\ODM01.DBF',
 19    'E:\ORACLE\ORADATA\TONES\TOOLS01.DBF',
 20    'E:\ORACLE\ORADATA\TONES\USERS01.DBF',
 21    'E:\ORACLE\ORADATA\TONES\XDB01.DBF',
 22    'E:\ORACLE\ORADATA\TONES\ERP01.DBF',
 23    'E:\ORACLE\ORADATA\TONES\BRM01.DBF',
 24    'E:\ORACLE\ORADATA\TONES\RMANDB01.DBF'
 25  CHARACTER SET ZHS16GBK;

控制文件已创建

7、打开数据库，
SQL> alter database open;
alter database open
*
ERROR 位于第 1 行:
ORA-16014: ?? 2 ???? 15 ????????????
ORA-00312: ???? 2 ?? 1: 'E:\ORACLE\ORADATA\TONES\REDO02.LOG'

8、系统提示出错，重新关闭数据库
SQL> shutdown immediate
ORA-01109: ??????
已经卸载数据库。
ORACLE 例程已经关闭。

9、启动到mount下，清除unarchived logfile 'e:\ORACLE\ORADATA\TONES\REDO02.LOG';
SQL> conn sys/gjm001 as sysdba;
已连接到空闲例程。
SQL> startup mount
ORACLE 例程已经启动。

Total System Global Area  126950220 bytes
Fixed Size                   453452 bytes
Variable Size             109051904 bytes
Database Buffers           16777216 bytes
Redo Buffers                 667648 bytes
数据库装载完毕。

SQL> alter database clear  unarchived logfile 'e:\ORACLE\ORADATA\TONES\REDO02.LOG';
数据库已更改。

SQL> alter database open;
数据库已更改。

10、重新查看数据文件位置
SQL> select name from v$datafile;

NAME
-----------------------------------------------------------------------------------------------------------------------------
E:\ORACLE\ORADATA\TONES\SYSTEM01.DBF
E:\ORACLE\ORADATA\TONES\UNDOTBS01.DBF
E:\ORACLE\ORADATA\TONES\CWMLITE01.DBF
E:\ORACLE\ORADATA\TONES\DRSYS01.DBF
E:\ORACLE\ORADATA\TONES\EXAMPLE01.DBF
E:\ORACLE\ORADATA\TONES\INDX01.DBF
E:\ORACLE\ORADATA\TONES\ODM01.DBF
E:\ORACLE\ORADATA\TONES\TOOLS01.DBF
E:\ORACLE\ORADATA\TONES\USERS01.DBF
E:\ORACLE\ORADATA\TONES\XDB01.DBF
E:\ORACLE\ORADATA\TONES\ERP01.DBF

NAME
-----------------------------------------------------------------------------------------------------------------------------
E:\ORACLE\ORADATA\TONES\BRM01.DBF
E:\ORACLE\ORADATA\TONES\RMANDB01.DBF

已选择13行。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/16486600/viewspace-545263/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/16486600/viewspace-545263/