Solved

Issue recovering data

Posted on 2013-01-25
8
242 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 76

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
 
LVL 35

Author Comment

by:YZlat
ID: 38827065
Can you tell me what should I have done originally?
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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 76

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Subquery in Oracle: Sub queries are one of advance queries in oracle. Types of advance queries: •      Sub Queries •      Hierarchical Queries •      Set Operators Sub queries are know as the query called from another query or another subquery. It can …
I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

705 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

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now