Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 260
  • Last Modified:

Issue recovering data

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
YZlat
Asked:
YZlat
  • 3
  • 3
  • 2
4 Solutions
 
Franck PachotCommented:
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
 
YZlatAuthor Commented:
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
 
slightwv (䄆 Netminder) Commented:
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
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
YZlatAuthor Commented:
Can you tell me what should I have done originally?
0
 
Franck PachotCommented:
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
 
YZlatAuthor Commented:
thanks!

is there any particular way I should perform a backup to ensure correct restore in this particular scenario?
0
 
slightwv (䄆 Netminder) Commented:
>>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
 
Franck PachotCommented:
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

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

  • 3
  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now