Home » RDBMS Server » Backup & Recovery » How to recover a dropped tabled in oracle 9i
How to recover a dropped tabled in oracle 9i [message #213566] Thu, 11 January 2007 05:12 Go to next message
ravivkumar
Messages: 34
Registered: April 2005
Location: chennai
Member

HI Friends
Kindly give the solution,
Suddently a table is dropped from the production database,
we have the last day backup and also the database is in archive mode.how to recover the dropped tabled.

Regards
Ravikumar.V
Re: How to recover a dropped tabled in oracle 9i [message #213573 is a reply to message #213566] Thu, 11 January 2007 05:39 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
You'll have to restore your backups to an auxiliary database and then export the table from the auxiliary database and import into your primary database.

[Updated on: Thu, 11 January 2007 05:40]

Report message to a moderator

Re: How to recover a dropped tabled in oracle 9i [message #213585 is a reply to message #213573] Thu, 11 January 2007 06:08 Go to previous messageGo to next message
Littlefoot
Messages: 21808
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
If your database supports flashback facility, you could restore it in no time; here is an example:
SQL> create table brisime (col number);

Table created.

SQL> insert into brisime values (1234);

1 row created.

SQL> commit;

Commit complete.

SQL> drop table brisime;

Table dropped.

SQL> select * from recyclebin where original_name = 'BRISIME';

OBJECT_NAME                    ORIGINAL_NAME                    OPERATION
------------------------------ -------------------------------- ---------
TYPE                      TS_NAME                        CREATETIME
------------------------- ------------------------------ -------------------
DROPTIME               DROPSCN PARTITION_NAME                   CAN CAN
------------------- ---------- -------------------------------- --- ---
   RELATED BASE_OBJECT PURGE_OBJECT      SPACE
---------- ----------- ------------ ----------
BIN$RbEey9RpQOKWjjTzjpyXDQ==$0 BRISIME                          DROP
TABLE                     USERS                          2007-01-11:13:06:31
2007-01-11:13:06:44 3.6671E+10                                  YES YES
    117918      117918       117918          8


SQL> flashback table brisime to before drop;

Flashback complete.

SQL> select * From brisime;

       COL
----------
      1234

SQL>
Re: How to recover a dropped tabled in oracle 9i [message #213599 is a reply to message #213585] Thu, 11 January 2007 07:04 Go to previous messageGo to next message
flyboy
Messages: 1903
Registered: November 2006
Senior Member
The title of this topic is "How to recover a dropped tabled in oracle 9i".
Flashback is introduced in 10g, so it is not applicable to OP.
Re: How to recover a dropped tabled in oracle 9i [message #213602 is a reply to message #213599] Thu, 11 January 2007 07:20 Go to previous messageGo to next message
Littlefoot
Messages: 21808
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Ahhh, my mistake /forum/fa/1604/0/ ... sorry. I swear, I double-checked message text and didn't see Oracle version (of course), but *forgot* to read the subject.
Re: How to recover a dropped tabled in oracle 9i [message #213605 is a reply to message #213602] Thu, 11 January 2007 07:25 Go to previous messageGo to next message
Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE
Senior Member

No problem...
It happen with everyone..Wink

regards
Taj

We can you FLASHBACK QUERY option in Oracle 9i.( DML OPERATION)
For Drop table
We have to perform Incomplete recovery.

[Updated on: Thu, 11 January 2007 07:28]

Report message to a moderator

Re: How to recover a dropped tabled in oracle 9i [message #214407 is a reply to message #213605] Tue, 16 January 2007 05:41 Go to previous message
ninja
Messages: 6
Registered: August 2006
Location: INDIA
Junior Member
hi there,
i went through ur quey and ppl said u required incomplete reecovery
well its still possib to recover completely

1.take backup of ur current db
2. apply previous day backup
3. do point in time recovery to get table back
4. export table
5. shutdown and apply latest backup (done 2day)
6. import table back

regards
hardik

Previous Topic: Error creating a standby database using RMAN
Next Topic: Oracle Secure Backup is not Appeared in EM
Goto Forum:
  


Current Time: Sun Apr 28 16:01:11 CDT 2024