Solved

Issue recovering data

Posted on 2013-01-25
8
250 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 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
Independent Software Vendors: 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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
migration MS SQL database to Oracle 30 71
Oracle DB monitor SW 21 71
run sql script from putty 4 158
any step by steps guide on how to install Oracle 12c on Windows 10 8 97
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…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

679 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