File

How to Change Oracle Database File Locations

In this post, we don’t change parameter file location, because we are seldom able to change $ORACLE_HOME. Oracle always search for pfile or spfile in several specific location in $ORACLE_HOME to startup the instance.

If you really want to change the location of parameter file because of $ORACLE_HOME has been changed, please modify /etc/oratab as well.

So we don’t change parameter file location here. But we can still change all other related files, such as control files, data files, log files and temp files. Please make sure the files are all duplicated or moved to the new location before we make the changes. I will assume that you have done that.

Let’s see our case:
  • Old location: /u01/app/oracle/oradata/DB11GR2
  • New location: /u01/app/oracle/oradata/DB11G_ORCL

Control files

First of all, we need to change control files location under nomount state.
[oracle@db11204 ~]$ sqlplus / as sysdba
...
SQL> startup nomount
...

Now, let’s see current setting of control_files.
SQL> show parameter control_files

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
control_files                        string      /u01/app/oracle/oradata/DB11GR
                                                 2/control01.ctl, /u01/app/orac
                                                 le/recovery_area/DB11GR2/contr
                                                 ol02.ctl

Change the setting within scope=spfile.
SQL> alter system set control_files='/u01/app/oracle/oradata/DB11G_ORCL/control01.ctl','/u01/app/oracle/recovery_area/DB11G_ORCL/control02.ctl' scope=spfile;

System altered.

You need to restart the instance to take effect.
SQL> shutdown immediate
ORA-01507: database not mounted


ORACLE instance shut down.

All other files (data files, redo files and temp files)

Now, we can startup to mount state.
SQL> startup mount
...
Database mounted.

Get all current (old) location of files.
SQL> select name from v$datafile union select member from v$logfile union select name from v$tempfile;

NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/DB11GR2/example01.dbf
/u01/app/oracle/oradata/DB11GR2/redo01.log
/u01/app/oracle/oradata/DB11GR2/redo02.log
/u01/app/oracle/oradata/DB11GR2/redo03.log
/u01/app/oracle/oradata/DB11GR2/sysaux01.dbf
/u01/app/oracle/oradata/DB11GR2/system01.dbf
/u01/app/oracle/oradata/DB11GR2/temp01.dbf
/u01/app/oracle/oradata/DB11GR2/undotbs01.dbf
/u01/app/oracle/oradata/DB11GR2/users01.dbf

9 rows selected.

Please note that, we retrieve three types of files all together by union operator in the above statement. Use above result to compose a renaming statement which can be executed in one long line:
SQL> alter database rename file '/u01/app/oracle/oradata/DB11GR2/example01.dbf','/u01/app/oracle/oradata/DB11GR2/redo01.log','/u01/app/oracle/oradata/DB11GR2/redo02.log','/u01/app/oracle/oradata/DB11GR2/redo03.log','/u01/app/oracle/oradata/DB11GR2/sysaux01.dbf','/u01/app/oracle/oradata/DB11GR2/system01.dbf','/u01/app/oracle/oradata/DB11GR2/temp01.dbf','/u01/app/oracle/oradata/DB11GR2/undotbs01.dbf','/u01/app/oracle/oradata/DB11GR2/users01.dbf' to '/u01/app/oracle/oradata/DB11G_ORCL/example01.dbf','/u01/app/oracle/oradata/DB11G_ORCL/redo01.log','/u01/app/oracle/oradata/DB11G_ORCL/redo02.log','/u01/app/oracle/oradata/DB11G_ORCL/redo03.log','/u01/app/oracle/oradata/DB11G_ORCL/sysaux01.dbf','/u01/app/oracle/oradata/DB11G_ORCL/system01.dbf','/u01/app/oracle/oradata/DB11G_ORCL/temp01.dbf','/u01/app/oracle/oradata/DB11G_ORCL/undotbs01.dbf','/u01/app/oracle/oradata/DB11G_ORCL/users01.dbf';

Database altered.

You can now open the database to read write.
SQL> alter database open;

Database altered.

If you’re doing upgrading, you may open it as upgrade to proceed further steps.
SQL> alter database open upgrade;

Database altered.

Check the new locations of all files after switching.
SQL> select name from v$datafile union select member from v$logfile union select name from v$tempfile;

NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/DB11G_ORCL/example01.dbf
/u01/app/oracle/oradata/DB11G_ORCL/redo01.log
/u01/app/oracle/oradata/DB11G_ORCL/redo02.log
/u01/app/oracle/oradata/DB11G_ORCL/redo03.log
/u01/app/oracle/oradata/DB11G_ORCL/sysaux01.dbf
/u01/app/oracle/oradata/DB11G_ORCL/system01.dbf
/u01/app/oracle/oradata/DB11G_ORCL/temp01.dbf
/u01/app/oracle/oradata/DB11G_ORCL/undotbs01.dbf
/u01/app/oracle/oradata/DB11G_ORCL/users01.dbf

9 rows selected.

We’re done.

Leave a Reply

Your email address will not be published. Required fields are marked *