用重建控制檔案的方法修改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目錄下的資料檔案路徑全部修改成功。