Solved

Issue recovering data

Posted on 2013-01-25
8
251 Views
Last Modified: 2013-01-28
I dropped one tablespace that contained a couple of tables.

drop tablespace TABLESPACE1 including contents and datafiles;

 Before dropping the tables, I backed up database while it was mounted, using

backup database;  
via RMAN.

Then I ran

RESTORE DATABASE;
RECOVER DATABASE;

followed by
ALTER DATABASE OPEN RESETLOGS;

Then I had to run

@?/sqlplus/admin/pupbld.sql

After that I logged in as my regular user and executed
SELECT TABLE_NAME FROM USER_TABLES;

and none of the tables from my dropped tablespace were retrieved. The tablespace itself also was not recovered.
What did I do wrong? Is there a way to recover my tablespace with all the tables in it?
0
Comment
Question by:YZlat
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
  • 2
8 Comments
 
LVL 15

Expert Comment

by:Franck Pachot
ID: 38819928
Hi,
You must restore to the point in time just before the drop, so you should use UNTIL TIME in RMAN. If you had other transactions that happened later, and don't want to loose them, then you must do a tTablespace Point In Time Recovery: recover in another database and transport your tablespace.
Regards,
Franck.
0
 
LVL 35

Author Comment

by:YZlat
ID: 38819968
Ran

restore database until time "TO_DATE('25-JAN-2013 13:36:00','DD-MON-YYYY HH24:MI:SS')";

and got an error:

RMAN-03002: failure of restore command at 01/25/2013 14:24:12
RMAN-20207: UNTIL TIME or RECOVERY WINDOW is before RESETLOGS time
0
 
LVL 77

Accepted Solution

by:
slightwv (䄆 Netminder) earned 250 total points
ID: 38820546
I don't think you can do a simple recovery now what you have opened with resetlogs.

See if the following link helps:
http://docs.oracle.com/cd/E11882_01/backup.112/e10642/rcmflash.htm#i1006192
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 35

Author Comment

by:YZlat
ID: 38827065
Can you tell me what should I have done originally?
0
 
LVL 15

Assisted Solution

by:Franck Pachot
Franck Pachot earned 250 total points
ID: 38827384
Hi,
You should have done the 'set until time' for bith restore and recover.
Such as:

run { 
  set until time  "TO_DATE('25-JAN-2013 13:36:00','DD-MON-YYYY HH24:MI:SS')";
  restore database;
  recover database;
  alter database open resetlogs;
}

Open in new window



Now that it is restored you can't go back. Or you need to restore again.
Regards,
Franck.
0
 
LVL 35

Author Comment

by:YZlat
ID: 38827403
thanks!

is there any particular way I should perform a backup to ensure correct restore in this particular scenario?
0
 
LVL 77

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 250 total points
ID: 38827498
>>to ensure correct restore in this particular scenario?

There doesn't appear to be anything wrong with how you backed up.  It was how you performed the initial recovery.  Once you opened the database with resetlogs, you really can't go back and 'recover' again without another restore.

You also need to understand what resetlogs does.  You may not want to ALWAYS use that when recovering.
0
 
LVL 15

Assisted Solution

by:Franck Pachot
Franck Pachot earned 250 total points
ID: 38827499
No. As long as you have all archivelogs, you can restore to the point in time you want.
If you need to restore to an exact point in time (just before the drop) then you should only write down the SCN so that you can set until SCN.
No need to make an additional backup before as long as you have a backup and the archivelogs to go to that point.
0

Featured Post

MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
Via a live example, show how to take different types of Oracle backups using RMAN.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question