drop table and constraints

[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.

[hbk_emp]

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.

[rohitsn]

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 rohitsn@orasearch.net


Regards,
Rohit Nirkhe,Oracle DBA,OCP

[looser]

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?

[shirishck]

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][i]Originally posted by looser [/i]

[B]
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. [/B][/QUOTE]

[looser]

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' <is this normal to call for this file?
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

[jmodic]

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,

[looser]

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

[gpsingh]

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 .

[yenni143]

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