drop table and constraints

OK,

I did my backup about 3hrs ago. Just now I had drop a table by accident. how do I recover this back?

I'm new at this, so please give me a step-by-step.

thanks.

do you know what tablespace the table was from, or is your database running in archivelog mode. if it is running in archive log mode than issue the command> alter database recover tablespace mytb ;
probably if it is drop very recently than it will do the changes from you redo's other wise executable will make you enter destination of all your archive logs.

Hi,
This is ROhit Nirkhe from India,OCP.Tell me whether u have taken the export of the table or u have performed backup of the database by taking individual tablespace backup.Let me know about the same so that I can very well tell u how u can recover the table.
Write to me at [email protected]


Regards,
Rohit Nirkhe,Oracle DBA,OCP

Yes, I am running in archive mode.

this is what I get.

SQL> alter database recover tablespace users;
alter database recover tablespace users
*
ERROR at line 1:
ORA-00283: recovery session canceled due to errors
ORA-01124: cannot recover data file 2 - file is in use or recovery
ORA-01110: data file 2: '/u03/oradata/gold/users01.dbf'

what to do now?

restore the backup and do the point in time recovery (till last 3 hrs). u will get the earlier data.
if export is available then import can also help if the earlier option is of no use

Quote:

---

Originally posted by looser


OK,

I did my backup about 3hrs ago. Just now I had drop a table by accident. how do I recover this back?

I'm new at this, so please give me a step-by-step.

thanks.

---

K, here's what I did,

I copied the users01.dbf back.

shutdown the database
startup mount
connect internal
alter database datafile '/u03/oradata/gold/users01.dbf' online;
recover datafile '/u03/oradata/gold/users01.dbf' ;

SQL> recover database until time '2001-02-27:14:00:00';
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: '/u02/oradata/gold/oradata/gold/system01.dbf' so, what I did, was copied this system01.dbf file over and run through the same commands, and I got this.
SQL> recover database until time '2001-02-27:14:00:00';
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01152: file 1 was not restored from a sufficiently old backup
ORA-01110: data file 1: '/u02/oradata/gold/oradata/gold/system01.dbf'

please tell me what I did wrong?

thanks

You are trying to perform point-in-time recovery, so you must restore your complete backup, not only one file.

1. Restore all db files from your backup
2. Perform a recovery up to specified time in the past.
3. Open with resetlogs option.

HTH,

Ummm, I don't really want to restore the whole thing, if I can avoid it. All that is lost was just one table.

but if I do have to do the whole restore, do I have to run through the same commands for all the files?

or do I just do something like:
alter database reset logfile;

I wait for your responds.

thanks

OK,

Did u do export or hot backup.
If export , then it is easy u import at table level .
If hot backup, then u have to restore this whole database backup, bring up the database , export at table level and bring the orig(latest) database backup and import from the export.

Or if u have enough disk space, u can bring up the old database with new db_name and use export/import, Create as select , copy .

If u have a backup already and the database is in archivelog mode,
connect internal
startup mount
recover database until time 'yyyy-mm-dd:24HH:mi:ss';
alter database open resetlogs;

now i'm really confused.

I did a hot backup. thats all I can answer you. the rest, I don't quite understand how to do so.

couldn't I just cp /u03/stage/system01.dbf and all of the related files back to its orginal location and run some kind of a recover command?


thanks

After point-in-time recovery to 3 hours ago, won't all subsequent transactions be lost upon open resetlogs?

In 8.1.6 u can use tablespace point in time recovery.
But do have the latest database available or the backup , as previous threads suggest u have already overwritten latest datafiles.

k
this is the moment of truth. I manually cp everything back.

now this is what i got.
SQL> recover database until time '2001-02-28:12:00:00';
ORA-00283: recovery session canceled due to errors
ORA-01610: recovery using the BACKUP CONTROLFILE option must be done


what next?