Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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
share|improve this question

1 Answer 1

up vote 3 down vote accepted

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
share|improve this answer
    
Thanks this helped. Had undo in recovery mode. I was able to recover with this tip. –  user3535 Sep 21 '11 at 16:16

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.