Skip to Main Content
  • Questions

  • RMAN - How to Recover a Database Having Added a tablespace, without a baseline backup

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question.

Asked: May 29, 2004 - 3:26 pm UTC

Last updated: July 09, 2004 - 11:57 am UTC

Version: 9.2.0

Viewed 1000+ times

You Asked

Is it possible to recover a newly added tablespace WITHOUT having a baseline backup using RMAN?

For example say, a database is backed up every night (level 0 on Sun, level 1 on other nights). Archivelogs are backed up by the hour. I create a NEW tablespace (tablespace 'TEST_TS') at 1pm today and add a bunch of tables to it. I then rename the underlying datafile '/u12/oradata/TESTRMAN/test_ts01.dbf' to test_ts01.dbf.old or something.

Now how do I recover this tablespace (TEST_TS)? I tried offlining it, and restoring it from rman..but that doesn't seem to work since it doesn't have a baseline. (Keep in mind the TEST_TS hadn't been backed up during the previous level 1 backup)

The below link shows how 'traditional' hot backups used to handle it and how one could perform recovery.
</code> http://metalink.oracle.com/metalink/plsql/ml2_documents.showDocument?p_id=29430.1&p_database_id=NOT <code>

Wonder why RMAN does not address it.

PS : Yes, I realize that it is a good practise to backup the database immediately after creating a new tablespace..but let us keep that aside for now.


and Tom said...

RMAN does address it, in fact, it is listed in the table of contents directly:

<quote>
Recovering a Lost Datafile Without a Backup

In this scenario, the following sequence of events occurs:

1. You make a whole database backup of your ARCHIVELOG mode database.
2. You create a tablespace containing a single datafile called ?/oradata/trgt/history01.dbf.
3. You populate the newly created datafile with data.
4. You archive all the active online redo logs.
5. Someone accidentally deletes ?/oradata/trgt/history01.dbf from the operating system before you have a chance to back it up.
</quote>


</code> http://docs.oracle.com/cd/B10501_01/server.920/a96566/rcmrecov.htm#1008332 <code>



Rating

  (4 ratings)

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

Comments

Thank you!!

A reader, May 30, 2004 - 1:15 pm UTC

Thanks a lot. My fault - obviously I hadn't read the manual well enough.

PS : I'd opened an iTAR with Oracle Support and they closed it saying it was simply not recoverable without a baseline!

Tom Kyte
May 30, 2004 - 1:53 pm UTC

what tar number, i'd be glad to email the analyst so they don't make the same mistake.

TAR number

A reader, May 30, 2004 - 2:29 pm UTC

3732219.996

Thanks.

Tom Kyte
May 30, 2004 - 2:54 pm UTC

Thanks, I followed up with them.

be careful..

bob, June 10, 2004 - 12:50 pm UTC

Make sure you have the appropriate archive for the datafile in 9iR2. If you don't have the appropriate archive logs, and the recovery cancels, the DB won't open. what a bummer!

This has happened to me many times and I could never figure out what was going on. I ended up recreating the db since it was just my test db and I didn't know anyway around it. When it happened to me again today, I went looking on metalink and found a week old bug that described the problem exactly. Bug No. 2786723

THANKS!

Christo Kutrovsky, July 09, 2004 - 11:57 am UTC

Thanks for the feedback Bob.

This is a very important bug to be aware of.


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 channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

Backup/Recovery

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