How to take undo datafile offline ,which is in recovery mode?

Question

I am trying to take undo datafile in offline mode using following command

alter database datafile '<datafile path>' offline;

this datafile is in recovery mode. Oracle shows message as database successfully altered.

But after executing this command when I select entries in v$tablespace. The file that I just made offline remains in this table.

Can somebody please tell how to take undo datafile offline.

OS: RHEL
Oracle version: 11g
Datafile status: Recovery

Answer 1

Shouldn't you be looking in v$datafile to see if a datafile is offline rather than v$tablespace?

Offline Datafile:

08:09:38 coredev>select status, name from v$datafile where name like '%UNDO4%';
STATUS  NAME
------- ----------------------------------------------------------------------
ONLINE  F:\ORACLE\ORADATA\CD\UNDO4.DBF

08:09:47 coredev>alter database datafile 'F:\ORACLE\ORADATA\CD\UNDO4.DBF' offline;
Database altered.

08:09:58 coredev>select status, name from v$datafile where name like '%UNDO4%';
STATUS  NAME
------- ----------------------------------------------------------------------
RECOVER F:\ORACLE\ORADATA\CD\UNDO4.DBF

08:10:02 coredev>select * from v$tablespace where name='UNDO';
       TS# NAME                           INC BIG FLA ENC
---------- ------------------------------ --- --- --- ---
        33 UNDO                           YES NO  YES

Online Datafile:

08:10:53 coredev>alter database datafile 'F:\ORACLE\ORADATA\CD\UNDO4.DBF' online;
alter database datafile 'F:\ORACLE\ORADATA\CD\UNDO4.DBF' online
*
ERROR at line 1:
ORA-01113: file 21 needs media recovery
ORA-01110: data file 21: 'F:\ORACLE\ORADATA\CD\UNDO4.DBF'

08:11:04 coredev>recover datafile 'F:\ORACLE\ORADATA\CD\UNDO4.DBF';
Media recovery complete.

08:11:13 coredev>alter database datafile 'F:\ORACLE\ORADATA\CD\UNDO4.DBF' online;
Database altered.

08:11:49 coredev>select status, name from v$datafile where name like '%UNDO4%';
STATUS  NAME
------- ----------------------------------------------------------------------
ONLINE  F:\ORACLE\ORADATA\CD\UNDO4.DBF