How to Move System Tablespace Datafiles

In an earlier post I explained how to move datafiles. The method I used then is NOT suitable for datafiles belonging to the system, undo or temporary tablespaces as they cannot be taken offline. I said I would publish a post about this in due course and here it is.

I created a database using dbca recently. I was in a hurry and forgot to put a slash at the end of the directory name where I wanted to put the datafiles so they ended up like this:

SQL> l
  1  select file_id, file_name
  2  from dba_data_files
  3* order by 1
SQL> /

   FILE_ID FILE_NAME
---------- ------------------------------------------
         1 /agasprd/qcsprod/qcs_systemsystem01.dbf
         2 /agasprd/qcsprod/qcs_systemsysaux01.dbf
         3 /agasprd/qcsprod/qcs_systemundotbs01.dbf
         4 /agasprd/qcsprod/qcs_systemusers01.dbf
         5 /agasprd/qcsprod/qcs_data/qc_data_a.dbf

SQL>

Files 1 to 4 were in the wrong place. File 5, which was created afterwards, is OK. I closed the database and backed it up. Then I renamed the files in UNIX:

UNIX > pwd
/agasprd/qcsprod
UNIX > mv qcs_systemsystem01.dbf qcs_system/system01.dbf
UNIX > mv qcs_systemsysaux01.dbf qcs_system/sysaux01.dbf
UNIX > mv qcs_systemundotbs01.dbf qcs_system/undotbs01.dbf
UNIX > mv qcs_systemusers01.dbf qcs_system/users01.dbf
UNIX >

I mounted the database:

UNIX > sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Thu Dec 6 14:12:20 2012

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup mount

ORACLE instance started.

Total System Global Area  522092544 bytes
Fixed Size                  2149672 bytes
Variable Size             390075096 bytes
Database Buffers          121634816 bytes
Redo Buffers                8232960 bytes
Database mounted.

SQL>

... and renamed the files in the database 1 by 1:

SQL> l
  1  alter database rename file
  2  '/agasprd/qcsprod/qcs_systemsystem01.dbf' to
  3* '/agasprd/qcsprod/qcs_system/system01.dbf'
SQL> /

Database altered.

SQL>

SQL> l
  1  alter database rename file
  2  '/agasprd/qcsprod/qcs_systemsysaux01.dbf' to
  3* '/agasprd/qcsprod/qcs_system/sysaux01.dbf'
SQL> /

Database altered.

SQL>

SQL> l
  1  alter database rename file
  2  '/agasprd/qcsprod/qcs_systemundotbs01.dbf' to
  3* '/agasprd/qcsprod/qcs_system/undotbs01.dbf'
SQL> /

Database altered.

SQL>

SQL> l
  1  alter database rename file
  2  '/agasprd/qcsprod/qcs_systemusers01.dbf' to
  3* '/agasprd/qcsprod/qcs_system/users01.dbf'
SQL> /

Database altered.

SQL>

Finally, I opened the database:

SQL> alter database open;

Database altered.

SQL> select file_id, file_name
  2  from dba_data_files
  3  order by 1;

   FILE_ID FILE_NAME
---------- ---------------------------------------------
        1 /agasprd/qcsprod/qcs_system/system01.dbf
        2 /agasprd/qcsprod/qcs_system/sysaux01.dbf
        3 /agasprd/qcsprod/qcs_system/undotbs01.dbf
        4 /agasprd/qcsprod/qcs_system/users01.dbf
        5 /agasprd/qcsprod/qcs_data/qc_data_a.dbf

SQL>

The tempfile was in the wrong place too but I dealt with that by recreating the temporary tablespace.