PROBLEM:

DBA is planning to move the system database file to a new location, for which he tried to make the system tablespace offline.

SQL> alter tablespace system offline;
alter tablespace system offline
*
ERROR at line 1:
ORA-01541: system tablespace cannot be brought offline; shut down if necessary

SOLUTION:

A system tablespace cann’t be offline for a running instance. So for renaming or moving a SYSTEM tablespace datafile, follow the below steps.

1. Shutdown the database.

SQL> select file_name,status from dba_data_files where tablespace_name='SYSTEM';

FILE_NAME
--------------------------------------------------------------------------------
STATUS
---------
/oracle/app/oracle/oradata/POCD/system01.dbf
AVAILABLE


SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

2. move that file to a new location.

 cp /oracle/app/oracle/oradata/POCD/system01.dbf /oracle/app/oracle/oradata/POCD/NEW/system01.dbf

3. Start the database in mount stage.

SQL> startup mount
ORACLE instance started.

Total System Global Area 6291456000 bytes
Fixed Size                  8616128 bytes
Variable Size            3456108352 bytes
Database Buffers         2801795072 bytes
Redo Buffers               24936448 bytes
Database mounted.

4. Rename the file.

SQL> alter database rename file '/oracle/app/oracle/oradata/POCD/system01.dbf' to '/oracle/app/oracle/oradata/POCD/NEW/system01.dbf';

Database altered.

5. Open the database:

SQL> SQL> alter database open;

Database altered.

SQL> select file_name,status from dba_data_files where tablespace_name='SYSTEM';


FILE_NAME
--------------------------------------------------------------------------------
STATUS
---------
/oracle/app/oracle/oradata/POCD/NEW/system01.dbf
AVAILABLE

Voilla.