About Me

My photo
Bangalore, India
I am an Oracle Certified Professional working in SAP Labs. Everything written on my blog has been tested on my local environment, Please test before implementing or running in production. You can contact me at amit.rath0708@gmail.com.

Tuesday, March 5, 2013

Renaming SYSTEM datafiles of Oracle Database

System tablespace is the most critical tablespace in Oracle database. To rename datafiles related to SYSTEM tablespace , we cannot proceed as we proceed for normal ones. It has to be handled with care.

When we try to offline your system tablespace we will get below mentioned error :-

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

Renaming datafiles of SYSTEM tablespace:-

1. Shut down the database
SQL> shu immediate
Database closed.
Database dismounted.
ORACLE instance shut down. 

2. Start the database in mount mode :-

SQL> startup mount
ORACLE instance started.

Total System Global Area  612368384 bytes
Fixed Size                  1250428 bytes
Variable Size             226495364 bytes
Database Buffers          377487360 bytes
Redo Buffers                7135232 bytes
Database mounted.

3. Rename the system datafile of database:-
SQL> alter database rename  file 'C:\oracle\product\10.2.0\oradata\Amit\SYSTEM01.DBF' to 'C:\oracle\product\10.2.0\oradata\Amit\SYSTEM012.DBF' ;

Database altered.

4. Open the database :-
SQL> alter database open;

Database altered.

I hope this article helped you.

Amit Rath

No comments:

Post a Comment