用重建控制文件的方法修改数据文件路径
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.
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 as '/u01/app/oracle/ctl.txt';
Database altered.
5.编辑产生的ctl.txt文件，把对应的数据文件路径修改后，再将里面重建控制文件的语句执行一下：
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
MAXLOGFILES 20
MAXLOGMEMBERS 3
MAXDATAFILES 200
MAXINSTANCES 1
MAXLOGHISTORY 1361
LOGFILE
GROUP 1 ''/opt/oracle/db02/oradata/redo01.log'' SIZE 64M,
GROUP 2 ''/opt/oracle/db03/oradata/ORCL/redo02.log'' SIZE 64M,
GROUP 3 ''/opt/oracle/db04/oradata/ORCL/redo03.log'' SIZE 64M,
GROUP 4 ''/opt/oracle/db02/oradata/redo04.log'' SIZE 64M
DATAFILE
''/opt/oracle/db02/oradata/system01.dbf'',
''/opt/oracle/db02/oradata/indx01.dbf'',
''/opt/oracle/db02/oradata/RBS.dbf'',
''/opt/oracle/db02/oradata/tools01.dbf'',
''/opt/oracle/db02/oradata/users01.dbf'',
''/opt/oracle/db02/oradata/drsys01.dbf'',
''/opt/oracle/db02/oradata/temp01.dbf'',
''/opt/oracle/db02/oradata/nms.dbf'',
''/opt/oracle/nms/oradata/ORCL/nms01.dbf'',
''/opt/oracle/wacos/oradata/ORCL/ts.dbf'',
''/opt/oracle/wacos/oradata/ORCL/ipas_event_data.dbf'',
''/opt/oracle/wacos/oradata/ORCL/ipas_event_idx.dbf'',
''/opt/oracle/db02/oradata/fs.dbf'',
''/opt/oracle/db02/oradata/wacos.dbf'',
''/opt/oracle/db02/oradata/wacos01.dbf'',
''/opt/oracle/db02/oradata/wacos02.dbf'',
''/opt/oracle/db02/oradata/wacos03.dbf'',
''/opt/oracle/db02/oradata/wacos04.dbf'',
''/opt/oracle/db02/oradata/wacos05.dbf'',
''/opt/oracle/db02/oradata/wacos06.dbf'',
''/opt/oracle/db02/oradata/wacos07.dbf'',
''/opt/oracle/db02/oradata/wacos08.dbf'',
''/opt/oracle/db02/oradata/wacos11.dbf'',
''/opt/oracle/db02/oradata/wacos09.dbf'',
''/opt/oracle/db02/oradata/wacos10.dbf'',
''/opt/oracle/db02/oradata/test.dbf'',
''/opt/oracle/db02/oradata/test01.dbf''
CHARACTER SET WE8ISO8859P1
;
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目录下的数据文件路径全部修改成功。
网友评论