change the path of control file

1.create a text based file for spfile

SQL> create spfile from pfile=’f:initAAA.ora’;

file created.

2.edit the flat file just created in the path of control file

3.start the database using pfile just created and recreate a new spfile
SQL> startup
Total System Global Area 612368384 bytes
Fixed Size 1304728 bytes
Variable Size 167774056 bytes
Database Buffers 436207616 bytes
Redo Buffers 7081984 bytes

SQL> show parameter spfile;

NAME TYPE
———————————— ————-
VALUE
——————————
spfile string
C:ORACLEPRODUCT10.2.0DB_1
DATABASESPFILEAAA.ORA

 

change the path of redo log file

changing the path of REDO log file is the same as datafile and temporary file

1.keep the instance shutdown
SQL> SHUTDOWN IMMEDIATE
2.host copy the datafile to new location

SQL> HOST COPY F:oradataaaaREDO01.DBF F:oradata2aaaREDO01.DBF
The system cannot find the file specified.

SQL> HOST COPY F:oradataaaaREDO01.LOG F:oradata2aaaREDO01.LOG
1 file(s) copied.

SQL> HOST COPY F:oradataaaaREDO02.LOG F:oradata2aaaREDO02.LOG
1 file(s) copied.

SQL> HOST COPY F:oradataaaaREDO03.LOG F:oradata2aaaREDO03.LOG
1 file(s) copied.

3.start the database to mount stage

SQL> STARTUP MOUNT;
Total System Global Area 612368384 bytes
Fixed Size 1304728 bytes
Variable Size 167774056 bytes
Database Buffers 436207616 bytes
Redo Buffers 7081984 bytes

3.change the path of redo log file
SQL> alter database rename file ‘F:ORADATAAAAREDO01.LOG’ to ‘F:ORADATA2AAAREDO01.LOG’;

database altered

SQL> alter database rename file ‘F:ORADATAAAAREDO02.LOG’ to ‘F:ORADATA2AAAREDO02.LOG’;

database altered

SQL> alter database rename file ‘F:ORADATAAAAREDO03.LOG’ to ‘F:ORADATA2AAAREDO03.LOG’;

database altered

SQL> ALTER DATABASE OPEN;

database altered

SQL>
SQL> select status from v$instance;

STATUS
————————
OPEN

ok,everything was done!!

change the path of datafile and temporary file

1.oracle was on the open status,and check the datafile information
SQL> col file_name for a50
SQL> select file_id,file_name from dba_data_files;

FILE_ID FILE_NAME
———- —————————————–
4 F:ORADATAAAAUSERS01.DBF
3 F:ORADATAAAASYSAUX01.DBF
2 F:ORADATAAAAUNDOTBS01.DBF
1 F:ORADATAAAASYSTEM01.DBF
SQL> shutdown immediate
database closed

2.copy the datafile to new path
SQL> host copy F:ORADATAAAAUSERS01.DBF F:ORADATA2AAAUSERS01.DBF
1 file(s) copied.

SQL> host copy F:ORADATAAAASYSAUX01.DBF F:ORADATA2AAASYSAUX01.DBF
1 file(s) copied.

SQL> host copy F:ORADATAAAAUNDOTBS01.DBF F:ORADATA2AAAUNDOTBS01.DBF
1 file(s) copied.

SQL> host copy F:ORADATAAAASYSTEM01.DBF F:ORADATA2AAASYSTEM01.DBF
1 file(s) copied.

3.start database mount and change the path of datafile

SQL> startup mount
Total System Global Area 612368384 bytes
Fixed Size 1304728 bytes
Variable Size 167774056 bytes
Database Buffers 436207616 bytes
Redo Buffers 7081984 bytes

SQL> alter database rename file ‘F:ORADATAAAAUSERS01.DBF’ to ‘F:ORADATA2AAAUSERS01.DBF’;

database altered

SQL> alter database rename file ‘F:ORADATAAAASYSAUX01.DBF’ to ‘F:ORADATA2AAASYSAUX01.DBF’;

database altered

SQL> alter database rename file ‘F:ORADATAAAAUNDOTBS01.DBF’ to ‘F:ORADATA2AAAUNDOTBS01.DBF’;

database altered

SQL> alter database rename file ‘F:ORADATAAAASYSTEM01.DBF’ to ‘F:ORADATA2AAASYSTEM01.DBF’;

database altered

SQL> alter database open;

SQL> select file_id,file_name from dba_data_files;

FILE_ID FILE_NAME
———- ————————————————–
4 F:ORADATA2AAAUSERS01.DBF
3 F:ORADATA2AAASYSAUX01.DBF
2 F:ORADATA2AAAUNDOTBS01.DBF
1 F:ORADATA2AAASYSTEM01.DBF

now,the datafile was relocated to the new path….

ps: if you want to change the path of temporary file, it’s the same process as datafile