Recovery

[Donna]

I have to recover a production DB and I do not know even how to start.
A table has been droped
Any ideas would help.

OS=Windows NT 4.0 /Oracle 7.3.4
DB =100GB/production
LOG_MODE =NOARCHIVELOG
Last_Back_Up=Every Saturday the whole server

Thank you

[pando]

easy, noarchivelog so restore your backup from Saturday. if it´s a parent table and no data has been changed then restore the backup in another machine and export the table then import to the production database

but it´s kind of stupid running 100gb in noarchivelog in production, you dont have a dba or ?if you have you can fire him already

btw you topic sounds funny/useless so I will change it

[oratune]

My first question would be this: do you have a recent export of the database? If so, this will be a rather pedestrian task. Simply import the missing table. Depending upon when the most recent export was created you may be missing some data from that table. Since you are running in NOARCHIVELOG mode there's not much more you can do.

If you DON'T have a recent export of the database (a necessary part of ANY database backup strategy) you must then restore ALL database files from the most recent backup. Shutdown the instance, restore all of the backed up files and restart the instance. You will lose any data created or modified since the backup was taken. Hopefully the database is shut down BEFORE the backup is taken, otherwise you'll have backup files that are useless. If the database WASN'T shut down prior to the backup then don't replace ANY database files as the backed up files are internally inconsistent and unusable.

[Donna]

Pando we do not have a DBA this I have to learn to do (they can fire me maybe).
Why is stuuupid running 100gb in noarchivelog in production ??
Oratune we do not have export file for the table that has been droped

[marist89]

Originally posted by oratune
...a necessary part of ANY database backup strategy

This statement is opinion, at best. I once had a database that took 26 hours to export. IMHO, export is used WAY to much as a backup strategy.

[marist89]

Originally posted by Donna
Why is stuuupid running 100gb in noarchivelog in production ??

Because you are now in a situation in which you have limited, at best, recovery options.

[pando]

Originally posted by Donna
Why is stuuupid running 100gb in noarchivelog in production ??

well I was joking, I didnt mean stupid, but it seems that your db generates quite a lot of data that means that you cannot afford to lose one day data, or even one hour (unless it´s datawarehouse) with archive log you wont lose data (well almost) or lose very little, in your situation most probably you are gonna loose several days, can your company afford this?

If it´s DWH then things are different

[sambavan]

Originally posted by marist89
[/B]

Jeff that was a good wealth you have found!!


Sam

[oratune]

marist89 dipped his finger in soot and doodled:
This statement is opinion, at best.

After over 14 years of Oracle database administration I have found that an export is a necessary part of the database backup procedure. Not the ONLY part of the procedure (and I do agree that simply using an export as a backup is overused and foolish) but an important part of it. It allows for scenarios like this one, where a table has been dropped and needs to be restored. I will state that running a 100gb database in ARCHIVELOG mode is preferable, since hot backups can be made and, as such, can be restored on a file-by-file basis. Recovery of the data would then be a function of applying the necessary archived redo logs. This would restore the table in question, as well as replace other tables in that same datafile. However, a time-based recovery would be necessary lest the table be again deleted by applying the errant transactions to the restored datafile. Therefore one would need to know WHEN the table was deleted so that recovery could be stopped prior to the DROP TABLE command that resulted in the recovery. Of course, if an instance requires 26 hours to export then possibly incremental exports would be in order.

[marist89]

Blah, blah, blah. People thought the world was flat for umpteen thousand years and they were wrong. Simply stating that and export is a necessary part of ANY database backup strategy is irresponsible. Period.

An export is NOT a necessary part of ANY backup strategy. DW do not need an export as part of the backup strategy and definitely should not be exported daily because of the precious space that they consume. In addition, most DW can be re-loaded faster than multiple incremental exports can be applied.

24x7 OLTP Apps should not have a daily export because of the precious CPU, I/O, and SGA resources they consume. I'd love to tell my users in Hong Kong that they are getting poor response because the export is going on. However, in a true 24x7 environment, that just can't happen.

Sure exports may have a place in SOME backup strategies, but, by no means are they a necessary part of ANY database backup strategy, IMHO of course.