Home » RDBMS Server » Backup & Recovery » Trying to recover dropped table
Trying to recover dropped table [message #197067] Mon, 09 October 2006 19:41 Go to next message
roopla
Messages: 52
Registered: May 2006
Member
I am new to RMAN. I am just trying to recover a table which was accidentally dropped.

I took the cold backup just one hour before I dropped the table
then using RMAN, I did full recovery and restore, but my dropped table didn't show up. No clue.

Following steps I did,


startup mount
rman target /
backup database
alter database open;
dropped the table;
shutdown immediate;


startup mount
rman target /
restore database
recover database
alter database open
checked the dropped table whether it recovered or not
but it's not there

Am I missing any step here. It seems pretty basic but not able to
recover the table which I dropped
Re: Trying to recover dropped table [message #197075 is a reply to message #197067] Mon, 09 October 2006 21:14 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
You need to do a Point-in-Time Recovery.
Re: Trying to recover dropped table [message #197207 is a reply to message #197067] Tue, 10 October 2006 08:36 Go to previous messageGo to next message
email.sandeepnaik
Messages: 121
Registered: August 2006
Senior Member
Process you have mentioned is very simple and it should work.

make sure your "restore" command is restoring all datafiles and specially datafiles pertaining to tablespace where table was located.

Sandeep
Re: Trying to recover dropped table [message #197229 is a reply to message #197207] Tue, 10 October 2006 11:06 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
email.sandeepnaik wrote on Tue, 10 October 2006 08:36

Process you have mentioned is very simple and it should work.



Not sure why you say that? The OP is not performing PITR and therefore, the actions that dropped table to begin with will essentially be re-performed with his recovery. He needs to do PITR.
Re: Trying to recover dropped table [message #197234 is a reply to message #197067] Tue, 10 October 2006 12:08 Go to previous messageGo to next message
roopla
Messages: 52
Registered: May 2006
Member
Is it possible to just Restore the database and avoid the Recover database command so that it will not apply redo logs. that way we can recover the old table

Re: Trying to recover dropped table [message #198004 is a reply to message #197067] Fri, 13 October 2006 14:22 Go to previous messageGo to next message
email.sandeepnaik
Messages: 121
Registered: August 2006
Senior Member
ebrian:

I think roopla was more of trying to restore and recover database right after cold backup and was expecting thta table should be around.


I think following syntax may help to solve roopla's question

Although, i have never tried to restore/recover database in in this fashion

restore database;
recover database noredo;
sql 'alter database open resetlogs';

"noredo" is key which should stop application of redo and hence allow to open database.

Sandeep
Re: Trying to recover dropped table [message #198006 is a reply to message #198004] Fri, 13 October 2006 14:34 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
Interesting.
I thought NOREDO is applicable only in NOARCHIVELOG mode.
I will set up a test case.
Re: Trying to recover dropped table [message #198011 is a reply to message #198006] Fri, 13 October 2006 15:26 Go to previous message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
RMAN> recover database noredo;

Starting recover at 13-OCT-06
using channel ORA_DISK_1
using channel ORA_DISK_2
Finished recover at 13-OCT-06

RMAN> alter database open resetlogs;

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of alter db command at 10/13/2006 09:13:52
ORA-01139: RESETLOGS option only valid after an incomplete database recovery

So i did the usual stuff (Incomplete recovery /TSPITR).
sql> recover database until cancel;
Then, it was behaving as it is supposed to do.
So,
At any case, you need to do an INCOMPLETE RECOVERY.

Previous Topic: logical backup without a specific table
Next Topic: table dropped without backup
Goto Forum:
  


Current Time: Sat Dec 10 20:56:28 CST 2016

Total time taken to generate the page: 0.08045 seconds