Skip to Main Content
  • Questions

  • Can not make read only tablespace which has offline datafile

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Zaur.

Asked: February 03, 2018 - 9:20 pm UTC

Last updated: February 07, 2018 - 12:24 am UTC

Version: 10.2.0.5

Viewed 1000+ times

You Asked

Hello Dears,

A couple of months ago, we had problem:
we lost most recent added disk on the server. Which contained latest 4 datafiles.
As we had no database/archivelog backup, we had to mark those datafiles offline drop and open the instance.
We searched for segments, whose extents were on those datafiles, dropped those segments an recreated them.
Everything was ok.
Now, we are migrating this database to new host via transportable tablespace method.
no violation on dbms_tts.transport_set_check, but when tried make tablespace readonly, faced error: ORA-01230: cannot make read only - file is offline. Tried drop the datafiles, get error: ORA-03262: the file is non-empty.
select * from  dba_extents 
where file_id in (2857,2858,2856,2855,2854,2853,2852);


sql returned 3541 rows: all are temporary segments in these offline files.
What can we do in this situation? Moving all segments from this tablespace to another is not a solution, because size of this tablespace is ~15TB
Is there any way to drop not-empty offline datafiles from tablespace? Or force tablespace read-only?
current version: 10.2.0.5
migrating to : 11.2.0.4

Thanks in advance!

and Connor said...

Best you liaise with Support on this one.

They can (possibly) walk you through options using:

- DBMS_SPACE_ADMIN.TABLESPACE_VERIFY
- DBMS_SPACE_ADMIN.SEGMENT_CORRUPT
- DBMS_SPACE_ADMIN.SEGMENT_DROP_CORRUPT

or if necessary, the DROP_SEGMENTS event.

But ultimately...you were in trouble the moment you allowed this to happen:

"As we had no database/archivelog backup,"

Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Thanks for hint

Zaur Hajili, February 05, 2018 - 11:54 am UTC

Thanks for reply.
I've tested dbms_space_admin in test environment.
I could remove temporary segments, select from dba_extents returns no row. But still can not drop offline datafile.
Same error:
ORA-03262: the file is non-empty

Is there any way drop offline empty datafile?
Connor McDonald
February 06, 2018 - 5:46 am UTC

Have you purged the recyclebin ?

Zaur Hajili, February 06, 2018 - 6:20 am UTC

Yes purged, but i realized that there were segments whose header_file were one of lost files:

select * from  dba_segments 
where header_file in (2857,2858,2856,2855,2854,2853,2852);


But after dropping these segments, I faced with this error:
ORA-03264: cannot drop offline datafile of locally managed tablespace

Connor McDonald
February 07, 2018 - 12:24 am UTC

I think you're out of luck.

Mos Note 216683.1 details how to salvage the existing segments into new tablespaces.
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

Administration

Need more information on Administration? Check out the Administrators guide for the Oracle Database