Moving database files

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;
wiki oracle move db files

You can find control files in pfile/spfile too. Example of UNIX command to examine pfile of DB ORCL:

cat $ORACLE_HOME/dbs/initORCL.orawiki oracle move db files

Move files to new location

Before you proceed with move you have to shutdown the database. Example of SQL command:

shutdown immediate;wiki oracle move db files

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
wiki oracle move db files

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.orawiki oracle move db files

Next logon to SQLPLUS and create spfile from pfile. Example of SQL command:

create spfile from pfile;wiki oracle move db files

As last step you can start the database. Example of SQL command:

startupwiki oracle move db files

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;
wiki oracle move db files

Move files to new location

Before you proceed with move you have to shutdown the database. Example of SQL command:

shutdown immediate;wiki oracle move db files

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
wiki oracle move db files

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;
wiki oracle move db files

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';
wiki oracle move db files

As last step you can open the database. Example of SQL command:

alter database open;wiki oracle move db files

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;
wiki oracle move db files

Move files to new location

Before you proceed with move you have to shutdown the database. Example of SQL command:

shutdown immediate;wiki oracle move db files

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
wiki oracle move db files

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;
wiki oracle move db files

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';
wiki oracle move db files

As last step you can open the database. Example of SQL command:

alter database open;wiki oracle move db files

Leave a Reply