Skip to Main Content
  • Questions
  • How can I export the tablespace that has been dropped one of the datafile?

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, Shiyin.

Asked: August 23, 2001 - 10:21 pm UTC

Last updated: March 01, 2019 - 1:27 am UTC

Version: 8.1.6

Viewed 1000+ times

You Asked

Hi,Tom

During my work,i have met such question,and hope yr answer.

1. I wrongly create a datafile under the tablespace DRSYS,then I
alter database 'datafile ' offline, and drop this datafile under OS command.Now the troubles arise.

When i backup the db (alter tablespace drsys begin backup ... end backup),then the db will report the error messages of the datafile.So I want to resolve this problem.

Fisrt I want to export the drsys then drop this tablespace and import this tablespace.But when I export this tablespace, I issued this command:
'alter tablespace drsys read only; ' then arise the error ORA-01230,ORA-01111,ORA-01110.So i can't export this tablespace.I wonder whether the 'exp imp ' method can solve this problem under such situation.

Secondly I use the backup controlfile to trace command to recreate the controlfile,but it will generate the datafile 'MISS000XX',what sould i do?

2. can't imp the tablespace

Once I exported one of the tablespace, oneday i wrongly dropped this datafile.then i want to imp this tablespace,but when i create a new datafile then the imp command need the file_id of the datafile that I have dropped.so i can't imp the tablespace,What sould i do?


thanks




and Tom said...

It is CRUCIAL to understand that once added, a file is part of the database until you DROP THE TABLESPACE it is associated with !!!

See
</code> http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:515222144417 <code>

I don't understand why you want to set the tablespace to read only -- looks like you are trying to transport the tablespace but that won't work since you DON'T have all of the files to begin with.

Step 1: make a full cold, consistent backup right now so you can at least get back to this point if need be.

Now, if we've caught this soon enough or you are running in archivelog mode (as you should be for a real database) and have the archives, we may be able to recover gracefully. Here is an example:


ops$tkyte@ORA817.US.ORACLE.COM> alter tablespace tmp_test add datafile '/tmp/bogus.dbf' size 1m;

Tablespace altered.

ops$tkyte@ORA817.US.ORACLE.COM> alter database datafile '/tmp/bogus.dbf' offline drop;

Database altered.

ops$tkyte@ORA817.US.ORACLE.COM> !rm -f /tmp/bogus.dbf

ops$tkyte@ORA817.US.ORACLE.COM> !ls /tmp/bogus.dbf
/tmp/bogus.dbf not found


ops$tkyte@ORA817.US.ORACLE.COM> alter tablespace tmp_test read only;
alter tablespace tmp_test read only
*
ERROR at line 1:
ORA-01230: cannot make read only - file 26 is offline
ORA-01110: data file 26: '/tmp/bogus.dbf'


So, here I am in the same situation. What I can do, since I have no backup of that datafile, is to create an empty one in its place:


ops$tkyte@ORA817.US.ORACLE.COM> alter database create datafile '/tmp/bogus.dbf';

Database altered.

ops$tkyte@ORA817.US.ORACLE.COM> alter tablespace tmp_test read only;
alter tablespace tmp_test read only
*
ERROR at line 1:
ORA-01230: cannot make read only - file 26 is offline
ORA-01110: data file 26: '/tmp/bogus.dbf'


ops$tkyte@ORA817.US.ORACLE.COM> alter database datafile '/tmp/bogus.dbf' online;
alter database datafile '/tmp/bogus.dbf' online
*
ERROR at line 1:
ORA-01113: file 26 needs media recovery
ORA-01110: data file 26: '/tmp/bogus.dbf'


So, we are not done yet -- it needs to be made consistent (i know, we didn't put anything into it but it STILL needs to be consistent. The database doesn't know nothing was in there ever).

In this example I was on a test database with noarchive log mode but I did this in a small enough window so that all of the needed logs were still available in the online redo log. You would simply:


ops$tkyte@ORA817.US.ORACLE.COM> alter database recover datafile '/tmp/bogus.dbf';

Database altered.

ops$tkyte@ORA817.US.ORACLE.COM> alter database datafile '/tmp/bogus.dbf' online;

Database altered.



and now you are back to where you started. You would NOT transport this tablespace at this point, you WOULD simply resize the file to be some small size and live with the fact that it is there.



2) this I don't understand unless you are talking about transporting in which case, as stated above, you don't want to do this. You need ALL of the files to transport. If you are missing one you cannot transport it.




Rating

  (5 ratings)

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

Comments

useful information

Yu Shiyin, August 26, 2001 - 9:13 pm UTC

very helpful!

Useful but awkward

Uwe Schneider, November 25, 2001 - 7:16 am UTC

Yes, we have dropped a datafile as well (Oracle 8.1.7, archivelog), so this article fits perfectly.

But we are very disappointed about the Oracle database product to let this happen. Why does the DB accept a "alter database datafile '...' offline drop" although it really KNOWS that this results in an inconsistency? If I issue a DROP command I want to have the object dropped and not made inconsistent.

We consider that behaviour as an Oracle bug.


Tom Kyte
November 25, 2001 - 11:24 am UTC

If it did not -- you could not recover from the fact that people "accidently erase" files in noarchivelog mode.

If you do not understand the ramification of a command, I cannot understand why you would use it...

You know, on Unix, I can as root issue "rm -rf /*" -- should it let me do it???

This command is important, it has its place, it is a necessary command. But, just like "drop tablespace t including contents" -- in the wrong hands, it is dangerous. That is why we have DBA's who know what the commands are, when to use them. There are many commands, that when used at the wrong time, in the wrong circumstances -- can case damage.

Dropping the database does NOT cause inconsistencies. It does not corrupt anything. It is simply the first step in cutting a tablespace out of the database when you've lost a file and have no backups of it. Without it -- we would be in a bad way.

A reader, April 16, 2002 - 12:13 pm UTC

I think the word "DROP" in the 'Alter Database datafile <fn> offline drop' creates all the confusion. The command is just for taking the datafile offline so that there is no SCN resync at the time of startup. 'Alter database datafile <fn> offline' will work only if the database is in archivelog mode. For non-archivelog mode, we have to add the word drop to get the same thing done. It doesn't drop anything even from the controlfile (only marks the file offline). It would be better if the syntax is changed from 'offline drop' to 'offline noarchive' - that will do a lot to clear the confusion on this command. Do you agree Tom?


Yes, change the 'drop' keyword in the command!

Steve Austin, August 06, 2006 - 12:01 pm UTC

I just had to help a DBA on our team with this exact issue.

I agree in principle of the last post ("A Reader" at the time of this writing) regarding "...offline drop" part of the "alter database datafile..." command. "Drop" in every other Oracle SQL context (of which I am aware) does indeed drop objects (when they properly execute, of course), removing them from the dictionary (and control file(s) when dropping tablespaces). Consistency is a good goal and Oracle's SQL should be no exception.

Another idea would be to instead ask for the kernel to give a helpful error message when "offline drop" was used on a database in archivelog mode, since it doesn't really apply here. (Since "offline drop" is still needed in the intepreter for databases not in archivelog mode, probably more keywords is more work for the developers, "A Reader". Besides, I like elegant syntax diagrams!)

Were this done, I would guess there would be fewer support calls from people who don't understand the ramifications of what they've just done.

Typically I'd expect them to do is wait for the "drop" to complete, remove the file from the filesystem and then unwittingly force themselves into a recovery situation while scratching their head and looking bad to the parties that care. They may not even notice right away -- they may find out later that "begin backup" fails as the file is offline. This could be much uglier, or worse, unrecoverable as the archivelogs may have passed their retention period.

I agree with your direction "A Reader" and think it's worthy of change. I think I'll log an enhancement request as I have a few other ideas too. I didn't know how until I just searched metalink, so here, I'll save you the search: Note:214168.1

Steve

Alex, February 28, 2019 - 11:58 am UTC

Very helpful if you in ' small enough window' .

I just have DB, where datafile go offline about 1 year ago.
Db is work but old archivelogs not present.
in this case - all RMAN backup ask old archvelogs at dbatabase recover moment.
How many ways solve this issue?
I now only one -
1 create new Tablespace
2 move all objects to the new tablespace
3 drop TS with offline datafile

But I really don't understand - way I can't restore db with offline datafile from rman backup (without cold backup)

With best regards
Alex
Connor McDonald
March 01, 2019 - 1:27 am UTC

By default, the database can't make assumptions that the data in a datafile is not intrinsically related to data in *other* datafiles. So a recovery is going to try get both of these back in sync to the current moment in time. And to do that, we need the archivelogs from the moment that datafile went offline.


More to Explore

Backup/Recovery

Check out the complete guide to all of the Backup & Recovery techniques in the Oracle Database.