-
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.
Best Regards,
Harsh Shah
-
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
-
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?
-
drop table
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]
-
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
-
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,
Jurij Modic
ASCII a stupid question, get a stupid ANSI
24 hours in a day .... 24 beer in a case .... coincidence?
-
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 <instance_name>
recover database until time 'yyyy-mm-dd:24HH:mi:ss';
alter database open resetlogs;
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|