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

1.查看数据文件的路径：

SQL> col file_name format a55         
SQL> set line 120 pagesize 2000
SQL> select file_name,tablespace_name from dba_data_files;

FILE_NAME                                               TABLESPACE_NAME
------------------------------------------------------- ------------------------------
/opt/oracle/db02/oradata/ORCL/test01.dbf                TEST
/opt/oracle/db02/oradata/ORCL/test.dbf                  TEST
/opt/oracle/db02/oradata/ORCL/wacos10.dbf               WACOS
/opt/oracle/db02/oradata/ORCL/wacos09.dbf               WACOS
/opt/oracle/db02/oradata/ORCL/wacos11.dbf               WACOS
/opt/oracle/db02/oradata/ORCL/wacos08.dbf               WACOS
/opt/oracle/db02/oradata/ORCL/wacos07.dbf               WACOS
/opt/oracle/db02/oradata/ORCL/wacos06.dbf               WACOS
/opt/oracle/db02/oradata/ORCL/wacos05.dbf               WACOS
/opt/oracle/db02/oradata/ORCL/wacos04.dbf               WACOS
/opt/oracle/db02/oradata/ORCL/wacos03.dbf               WACOS
/opt/oracle/db02/oradata/ORCL/wacos02.dbf               WACOS
/opt/oracle/db02/oradata/ORCL/wacos01.dbf               WACOS
/opt/oracle/db02/oradata/ORCL/wacos.dbf                 WACOS
/opt/oracle/nms/oradata/ORCL/nms01.dbf                  NMS
/opt/oracle/db02/oradata/ORCL/nms.dbf                   NMS
/opt/oracle/db02/oradata/ORCL/temp01.dbf                TEMP
/opt/oracle/db02/oradata/ORCL/drsys01.dbf               DRSYS
/opt/oracle/db02/oradata/ORCL/users01.dbf               USERS
/opt/oracle/db02/oradata/ORCL/tools01.dbf               TOOLS
/opt/oracle/db02/oradata/ORCL/RBS.dbf                   RBS
/opt/oracle/db02/oradata/ORCL/indx01.dbf                INDX
/opt/oracle/db02/oradata/ORCL/system01.dbf              SYSTEM
/opt/oracle/wacos/oradata/ORCL/ts.dbf                   TS
/opt/oracle/wacos/oradata/ORCL/ipas_event_data.dbf      IPAS_EVENT_DATA
/opt/oracle/wacos/oradata/ORCL/ipas_event_idx.dbf       IPAS_EVENT_IDX
/opt/oracle/db02/oradata/ORCL/fs.dbf                    FS

27 rows selected.

2.关闭数据库修改数据文件的路径，将/opt/oracle/db02/oradata/ORCL目录下的所有数据文件修改到/opt/oracle/db02/oradata下。

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> !mv /opt/oracle/db02/oradata/ORCL/*.dbf /opt/oracle/db02/oradata/

SQL> !mv /opt/oracle/db02/oradata/ORCL/*.log /opt/oracle/db02/oradata/

3.启动数据库到mount状态下：

SQL> startup mount
ORACLE instance started.

Total System Global Area 2054195960 bytes
Fixed Size                   731896 bytes
Variable Size             570425344 bytes
Database Buffers         1474560000 bytes
Redo Buffers                8478720 bytes
Database mounted.

4.备份控制文件，在udump目录下产生trace文件：

SQL> alter database backup controlfile to trace;

Database altered.

5.编辑产生的trc文件，把对应的数据文件路径修改后，再将里面重建控制文件的语句执行一下：

SQL> shutdown immediate
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.

SQL> startup nomount
ORACLE instance started.

Total System Global Area 2054195960 bytes
Fixed Size                   731896 bytes
Variable Size             570425344 bytes
Database Buffers         1474560000 bytes
Redo Buffers                8478720 bytes

SQL> CREATE CONTROLFILE REUSE DATABASE "ORCL" NORESETLOGS  NOARCHIVELOG
  2      MAXLOGFILES 20
  3      MAXLOGMEMBERS 3
  4      MAXDATAFILES 200
  5      MAXINSTANCES 1
  6      MAXLOGHISTORY 1361
  7  LOGFILE
  8    GROUP 1 '/opt/oracle/db02/oradata/redo01.log'  SIZE 64M,
  9    GROUP 2 '/opt/oracle/db03/oradata/ORCL/redo02.log'  SIZE 64M,
 10    GROUP 3 '/opt/oracle/db04/oradata/ORCL/redo03.log'  SIZE 64M,
 11    GROUP 4 '/opt/oracle/db02/oradata/redo04.log'  SIZE 64M
 12  DATAFILE
 13  '/opt/oracle/db02/oradata/system01.dbf',
 14    '/opt/oracle/db02/oradata/indx01.dbf',
 15    '/opt/oracle/db02/oradata/RBS.dbf',
 16    '/opt/oracle/db02/oradata/tools01.dbf',
 17    '/opt/oracle/db02/oradata/users01.dbf',
 18    '/opt/oracle/db02/oradata/drsys01.dbf',
 19    '/opt/oracle/db02/oradata/temp01.dbf',
 20    '/opt/oracle/db02/oradata/nms.dbf',
 21    '/opt/oracle/nms/oradata/ORCL/nms01.dbf',
 22    '/opt/oracle/wacos/oradata/ORCL/ts.dbf',
 23    '/opt/oracle/wacos/oradata/ORCL/ipas_event_data.dbf',
 24    '/opt/oracle/wacos/oradata/ORCL/ipas_event_idx.dbf',
 25    '/opt/oracle/db02/oradata/fs.dbf',
 26    '/opt/oracle/db02/oradata/wacos.dbf',
 27  '/opt/oracle/db02/oradata/wacos01.dbf',
 28    '/opt/oracle/db02/oradata/wacos02.dbf',
 29    '/opt/oracle/db02/oradata/wacos03.dbf',
 30    '/opt/oracle/db02/oradata/wacos04.dbf',
 31    '/opt/oracle/db02/oradata/wacos05.dbf',
 32    '/opt/oracle/db02/oradata/wacos06.dbf',
 33    '/opt/oracle/db02/oradata/wacos07.dbf',
 34    '/opt/oracle/db02/oradata/wacos08.dbf',
 35    '/opt/oracle/db02/oradata/wacos11.dbf',
 36    '/opt/oracle/db02/oradata/wacos09.dbf',
 37    '/opt/oracle/db02/oradata/wacos10.dbf',
 38    '/opt/oracle/db02/oradata/test.dbf',
 39    '/opt/oracle/db02/oradata/test01.dbf'
 40  CHARACTER SET WE8ISO8859P1
 41  ;

Control file created.

SQL> alter database open;

Database altered.

6.查看修改后的数据文件的路径和状态：

SQL> select file_name,tablespace_name,status from dba_data_files;

FILE_NAME                                               TABLESPACE_NAME                STATUS
------------------------------------------------------- ------------------------------ ---------
/opt/oracle/db02/oradata/test01.dbf                     TEST                           AVAILABLE
/opt/oracle/db02/oradata/test.dbf                       TEST                           AVAILABLE
/opt/oracle/db02/oradata/wacos10.dbf                    WACOS                          AVAILABLE
/opt/oracle/db02/oradata/wacos09.dbf                    WACOS                          AVAILABLE
/opt/oracle/db02/oradata/wacos11.dbf                    WACOS                          AVAILABLE
/opt/oracle/db02/oradata/wacos08.dbf                    WACOS                          AVAILABLE
/opt/oracle/db02/oradata/wacos07.dbf                    WACOS                          AVAILABLE
/opt/oracle/db02/oradata/wacos06.dbf                    WACOS                          AVAILABLE
/opt/oracle/db02/oradata/wacos05.dbf                    WACOS                          AVAILABLE
/opt/oracle/db02/oradata/wacos04.dbf                    WACOS                          AVAILABLE
/opt/oracle/db02/oradata/wacos03.dbf                    WACOS                          AVAILABLE
/opt/oracle/db02/oradata/wacos02.dbf                    WACOS                          AVAILABLE
/opt/oracle/db02/oradata/wacos01.dbf                    WACOS                          AVAILABLE
/opt/oracle/db02/oradata/wacos.dbf                        WACOS                          AVAILABLE
/opt/oracle/nms/oradata/ORCL/nms01.dbf           NMS                                 AVAILABLE
/opt/oracle/db02/oradata/nms.dbf                           NMS                                 AVAILABLE
/opt/oracle/db02/oradata/temp01.dbf                     TEMP                               AVAILABLE
/opt/oracle/db02/oradata/drsys01.dbf                    DRSYS                            AVAILABLE
/opt/oracle/db02/oradata/users01.dbf                   USERS                            AVAILABLE
/opt/oracle/db02/oradata/tools01.dbf                    TOOLS                             AVAILABLE
/opt/oracle/db02/oradata/RBS.dbf                         RBS                                  AVAILABLE
/opt/oracle/db02/oradata/indx01.dbf                     INDX                                 AVAILABLE
/opt/oracle/db02/oradata/system01.dbf               SYSTEM                          AVAILABLE
/opt/oracle/wacos/oradata/ORCL/ts.dbf               TS                                    AVAILABLE
/opt/oracle/wacos/oradata/ORCL/ipas_event_data.dbf      IPAS_EVENT_DATA                AVAILABLE
/opt/oracle/wacos/oradata/ORCL/ipas_event_idx.dbf       IPAS_EVENT_IDX                 AVAILABLE
/opt/oracle/db02/oradata/fs.dbf                              FS                                   AVAILABLE

27 rows selected.

7.至此，/opt/oracle/db02/oradata/ORCL目录下的数据文件路径全部修改成功。