?
Solved

how to recover dropped table

Posted on 2007-12-02
7
Medium Priority
?
5,762 Views
Last Modified: 2013-12-19
Hi,
      I am working with Oracle 10gR2 on HP-UX 11. I have accidentally dropped a user table from my database ( a test database) and now i want to recover it. I dont have any EXPORT backup of either the database (full) or just the table. Neither do i have a RMAN backup of the database. I have some naive questions regarding this scenario:

1. Suppose if i am running Oracle 9iR2 and i just have a RMAN backup of the database. I do not have any EXPORT backup. How can i go ahead recovering the dropped table?? Is the Full recover just before the dropping only option of recovering it??

2. If I am using Oracle 10gR2 and i have just a RMAN backup of the database, in this case
 
        2.a Can i recover just the dropped table using this RMAN backup??
        2.b There is a option of Flash Recovery Area in Oracle 10gR2. If i set it ON, then i know i can recover the dropped table using flashback logs. If this option is set to OFF, how can i recover the dropped table??

3. Oracle 10gR2 has the RECYCLE BIN option. Any table that has been dropped will be stored in this BIN. Now
      3.a For how much time will this data reside in the RECYCLE BIN?? Is there any retention period for this data in the BIN??
     3.b  Has the RECYCLE BIN got anything to do with the Flash Recovery Area?

Any information will be of a great help.

Thanks,
Harris.
0
Comment
Question by:harris2107
[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
7 Comments
 
LVL 28

Accepted Solution

by:
Naveen Kumar earned 600 total points
ID: 20393644
if your table name is EMP then,

FLASHBACK TABLE EMP TO BEFORE DROP;

Also refer to url for more details:

http://www.oracle-base.com/articles/10g/Flashback10g.php
0
 
LVL 28

Expert Comment

by:Naveen Kumar
ID: 20393653
any dropped table will be in recyclebin until you/DBA purge the recyclebin .

select * from recyclebin;

This will show all the objects which are in the recyclebin for the logged in user.

if you want to see for all users, then

select * from dba_recyclebin;

Thanks
0
 
LVL 28

Expert Comment

by:Naveen Kumar
ID: 20393663
The recyclebin is only available from 10g onwards. so if your version is less than this, then we may have to try to take it from export dump files or cold/hot backup.
0
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

 
LVL 48

Expert Comment

by:schwertner
ID: 20394660
You said you have RMAN backup of the DB.

Create another instance and recover the database using RMAN.

After that export/import the dropped table.
Of course it will be recovered to the time
you made the RMAN backup.
0
 
LVL 3

Assisted Solution

by:IanStuart
IanStuart earned 600 total points
ID: 20455749
Hello Harris,

Question 1 (V9i): Easiest way would be to restore and recover the database to another destination, then export the table and import it in the other Database.

Question 2 (V10g RMAN Only):a) I'd suggest to let RMAN perform a Tablespae Point in Time Recovery, so you'll not have the overhead to restore/recover the whole instance..
b) Yes, you could recover just the table with flashback.

Question 3 (V10 Recyclebin):
a) The objects will be in the recycle bin as long as the space isn't reclaimed by another object. So if you got plenty of space in your tablespace Oracle tries to hold the objects in the bin as long as possible.
b) No
0
 
LVL 1

Expert Comment

by:Computer101
ID: 20936942
Forced accept.

Computer101
Community Support Moderator
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.
Suggested Courses

801 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