Move control files
Find current location
Currently used control files can be found in view v$controlfile. To check, execute following SQL command:
set pagesize 100
set lines 300
col name form a70
select name from v$controlfile;
You can find control files in pfile/spfile too. Example of UNIX command to examine pfile of DB ORCL:
cat $ORACLE_HOME/dbs/initORCL.ora
Move files to new location
Before you proceed with move you have to shutdown the database. Example of SQL command:
shutdown immediate;
Afterwards you can move files to new location. Example of UNIX commands:
mv /oracle/product/oradata/ORCL/control01.ctl /oracle/DATA/ORCL/control01.ctl
mv /oracle/product/oradata/ORCL/control02.ctl /oracle/REDOA/ORCL/control02.ctl
mv /oracle/product/oradata/ORCL/control03.ctl /oracle/REDOB/ORCL/control03.ctl
Change pfile/spfile
When controlfiles are moved you have to adjust your pfile/spfile to point to new location. Example of command for editing with VI editor:
vi $ORACLE_HOME/dbs/initORCL.ora
Next logon to SQLPLUS and create spfile from pfile. Example of SQL command:
create spfile from pfile;
As last step you can start the database. Example of SQL command:
startup
Move data files
Find current location
All used data files can be found in table dba_data_files. To check, execute following SQL command:
set pagesize 100
set lines 300
col file_name form a70
select file_name from dba_data_files;
Move files to new location
Before you proceed with move you have to shutdown the database. Example of SQL command:
shutdown immediate;
Afterwards you can move files to new location. Example of UNIX commands:
mv /oracle/product/oradata/ORCL/example01.dbf /oracle/DATA/ORCL/example01.dbf
mv /oracle/product/oradata/ORCL/sysaux01.dbf /oracle/DATA/ORCL/sysaux01.dbf
mv /oracle/product/oradata/ORCL/system01.dbf /oracle/DATA/ORCL/system01.dbf
mv /oracle/product/oradata/ORCL/users01.dbf /oracle/DATA/ORCL/users01.dbf
mv /oracle/product/oradata/ORCL/temp01.dbf /oracle/TEMP/ORCL/temp01.dbf
mv /oracle/product/oradata/ORCL/undotbs01.dbf /oracle/UNDO/ORCL/undotbs01.dbf
Alter database
When datafiles are moved you have to alter your database to point to new location. To do this, first start your database to mount state. Example of SQL command:
shutdown immediate;
startup mount;
Next rename datafiles using alter database. Example of SQL command:
alter database rename file '/oracle/product/oradata/ORCL/example01.dbf' to '/oracle/DATA/ORCL/example01.dbf';
alter database rename file '/oracle/product/oradata/ORCL/sysaux01.dbf' to '/oracle/DATA/ORCL/sysaux01.dbf';
alter database rename file '/oracle/product/oradata/ORCL/system01.dbf' to '/oracle/DATA/ORCL/system01.dbf';
alter database rename file '/oracle/product/oradata/ORCL/users01.dbf' to '/oracle/DATA/ORCL/users01.dbf';
alter database rename file '/oracle/product/oradata/ORCL/temp01.dbf' to '/oracle/TEMP/ORCL/temp01.dbf';
alter database rename file '/oracle/product/oradata/ORCL/undotbs01.dbf' to '/oracle/UNDO/ORCL/undotbs01.dbf';
As last step you can open the database. Example of SQL command:
alter database open;
Move online redologs
Find current location
All used data files can be found in table dba_data_files. To check, execute following SQL command:
set pagesize 100
set lines 300
col member form a70
select member from v$logfile;
Move files to new location
Before you proceed with move you have to shutdown the database. Example of SQL command:
shutdown immediate;
Afterwards you can move files to new location. Example of UNIX commands:
mv /oracle/product/oradata/ORCL/redo01.log /oracle/REDOA/ORCL/redo01.log
mv /oracle/product/oradata/ORCL/redo02.log /oracle/REDOB/ORCL/redo02.log
mv /oracle/product/oradata/ORCL/redo03.log /oracle/REDOA/ORCL/redo03.log
Alter database
When redologs are moved you have to alter your database to point to new location. To do this, first start your database to mount state. Example of SQL command:
shutdown immediate;
startup mount;
Next rename redologs using alter database. Example of SQL command:
alter database rename file '/oracle/product/oradata/ORCL/redo01.log' to '/oracle/REDOA/ORCL/redo01.log';
alter database rename file '/oracle/product/oradata/ORCL/redo02.log' to '/oracle/REDOB/ORCL/redo02.log';
alter database rename file '/oracle/product/oradata/ORCL/redo03.log' to '/oracle/REDOA/ORCL/redo03.log';
As last step you can open the database. Example of SQL command:
alter database open;