Monday, June 14, 2010

Rename/Relocate SYSAUX tablespace

METHOD 1 (move the datafile(s) with the database open
============================================
1) ALTER TABLESPACE sysaux OFFLINE NORMAL;

2) Rename the datafile(s) at the OS level

3) ALTER TABLESPACE sysaux RENAME DATAFILE "\SYSAUX01.DBF";

4) ALTER TABLESPACE sysaux ONLINE;

NOTE : This method may not work at all times as there are processes that need to use the SYSAUX tablespace
and if they are using it ... the tablespace will not alter offline ... in which case you will need to use METHOD 1



METHOD 2 (move the datafiles in mount mode)
====================================

1) SHUTDOWN IMMEDIATE

2) Relocate the SYSAUX datafile(s) to the desired location

3) STARTUP MOUNT

4) ALTER DATABASE RENAME FILE
'/FULL_PATH_OF_OLD_LOCATION/AND_DATAFILE_NAME.DBF'
TO
'/FULL_PATH_OF_NEW_LOCATION/AND_DATAFILE_NAME.DBF';

5) ALTER DATABASE OPEN;



METHOD 3 (move the datafiles by recreating the controlfiles)
===============================================

1) ALTER DATABASE BACKUP CONTROLFILE TO TRACE;

2) Locate the trace file created in step 1 ... it will be in the USER_DUMP_DEST

3) Edit the create controlfile statement in the created trace ... change the location of the SYSAUX datafile(s) to their desired location

4) SHUTDOWN the database

5) Relocate the SYSAUX datafile(s) to the desired location

6) Run the script that was created in step 1 and modified in Step 3

2 comments:

Cyrille MODIANO said...

I explained in the following post how to do it with RMAN in order to reduce downtime, you sould take a look at it.
http://www.dba-scripts.com/2014/01/27/oracle-relocate-a-datafile/

Ferdous Nadvi said...

Great article Cyrille!