Making System tablespace Offline – Oracle

If you search the Oracle documentation it says that you cannot bring the system tablespace offline.
However, here is a way to do it —

– Each tablespace is assigned one or more datafile. You can bring the datafile offline.
In the below case, I have deleted the system datafile and trying to start the database.

>>
ORACLE instance started.

Total System Global Area 1068937216 bytes
Fixed Size 2182592 bytes
Variable Size 335544896 bytes
Database Buffers 725614592 bytes
Redo Buffers 5595136 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 1 – see DBWR trace file
ORA-01110: data file 1: ‘C:\ORACLE\ORADATA\WINDEV\SYSTEM01.ODF’

 

SQL> alter database datafile 1 offline;

Database altered.

and then you can bring it back up using the same datafile.

In my case, i have done a rman restore of the tablespace.

SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01147: SYSTEM tablespace file 1 is offline
ORA-01110: data file 1: ‘C:\ORACLE\ORADATA\WINDEV\SYSTEM01.ODF’

 

SQL> alter database datafile 1 online;

Database altered.

SQL> alter database open;

Database altered.

SQL>

Advertisement

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s