Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Issue recovering data

Posted on 2013-01-25
8
Medium Priority
?
258 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
  • 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 78

Accepted Solution

by:
slightwv (䄆 Netminder) earned 1000 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
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

 
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 1000 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 78

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 1000 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 1000 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

NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

Question has a verified solution.

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

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.
Suggested Courses

916 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