In deep restore doo...... 2

[tman138]

I am running an Oracle 8i database in HPUX utilizing 2 schemas. Today all of the active production server .dbf files for one schema were accidentally deleted. I tried restoring just those files from tape, but I could not restart the database. So I went to yesterdays backup tapes and restored both schema .dbf files and the control files but I keep getting a multitude of errors.:
First is ORA-01113: file nsm1.dbf needs media recovery

And while I can start an Oracle instance, the database will not mount or start.. I get ORA-01033: ORACLE initialization or shutdown in progress
I have no archive log files for which to use alter database recover.
I am so screwed if I can’t get this thing running.
My previous backup is a week old. I really need to get this one restored.
Any help?

 

[SantaMufasa]

TMan,

When you say, "I have no archive log files," Does that mean your database is in NOARCHIVELOG mode? If that is the case, then your only prayer of recovery is if your oldest on-line redo log file is older than yesterday's backup tapes.

If your backups from yesterday postdate your oldest on-line redo log file, then you should be able to copy yesterday's backup files for the deleted files to the deleted file's last known location. Next you do a "startup mount", then issue a "recover database" command.

If, instead, your oldest on-line redo log file is newer than yesterday's backup files, then your only hope is to restore a full set of your most recent backup files. This means that you must copy from backup locations all control files, on-line redo log files, and database data files from the same consistent backup (hopefully yesterday's full backup).

Please tell us what your situation is based upon the above options. (BTW, how did the production data files get deleted?)



Mufasa
(aka Dave of Sandy, Utah, USA)

http://www.dasages.com

Do you use Oracle and live or work in Utah, USA?
Then click here to join Utah Oracle Users Group on Tek-Tips.

 

[tman138]

Couldn't you just feel the panic in my post? I certainly learned alot in a few hours including the need for archive log files!
I was unable to recover to Monday's backup. One of the datafiles kept reporting the ORA-01113 error. Then, I went to Fridays tape and found that it actually had the previous weeks backup and not Fridays. I was just sick at this point. I then got Thursdays tape and was plesantly surprised to find Fridays data. This tape restored correctly but I did lose 1 +1/2 days data.

As for how it happened...the problem began with a little HUGE mistake. I had exported the database the day before for import into my test machine. The larger shchema imported just fine, but the smaller one was split over 3 datafiles, and when the import began it wrote only to last ( & smallest) datafile and then halted once that file was full. So I decided to resize the datafile to a single file since the databse was empty -already dropped, and drop the 3 orignal datafiles files. I was using a HPUX workstation and telneting into both the production and test dbs from separtate windows and I thought I was in the test machine and zapped the entire smaller production schema with a few quick key strokes.
What a sinking feeling.
I obviously want to turn on archive logging and have to research that further as far as disk space required etc. I also need to revamp my backup procedures... too many bad surprises.
Does the archivelog setting get set in an init file?

Thanks for the reply.
...T

 

[dbtoo2001]

http://download-west.oracle.com/docs/cd/A87860_01/doc/server.817/a76956/archredo.htm#4553

 

[KenCunningham]

Hi Tman. Really pleased that you managed to restore at least most of your database - it's something like that which really does cause one to think around their procedures and backup strategy. To turn archive logging on when you next shutdown and restart your database, add the following to your init file:

log_archive_start = true
log_archive_dest = /path/to/destination
log_archive_format = _%s.arc

This will start logging to the destination specified. Bear in mind that this area tends to fill up and can cause your database to halt if regular housekeeping (eg compressing the files and archiving them when you know you have a good backup) isn't carried out. You can also start archive logging interactively. See:

http://www.adp-gmbh.ch/ora/sql/alter_system.html#archive_log

for details, but remember to include the entries in your init file as above too. Archive logs can also be written to a second (or third?) destination for a real belt-and-braces approach. Well done and good luck.

 

[SantaMufasa]

Tman,

Also glad to hear that you were able to recover at least something. Losing just 1 1/2 days of work is obviously better than losing 1 1/2 decades of work.

Now, to the function of ARCHIVELOGging: Ken's suggestions are excellent, as far as they go, but unfortunately, implementing these init<SID>.ora commands offers a very false sense of security insofar as archiving is concerned. The best they will get you is to be "automatically not archiving." By that I mean that "log_archive_start = true" turns on the Oracle background process, "ARCH", but that process simply provides "automatic archiving" if your database is in ARCHIVELOG mode, which, if these parameter changes are all you do, your database will not be in ARCHIVELOG mode !!!

Additionally, although the link that appears in Ken's post presents interactive alternatives to some of the init<SID>.ora commands, as with those init<SID>.ora commands, neither the interactive nor parameter-centric commands turn on ARCHIVELOG mode. And yet another false sense of security is that if you depend upon the interactive commands, none of them are permanent...they have effect, at most, during the life of the current instance, which means that the effects disappear if/when the instance goes down.

Now, dbtoo's link suggestion,

http://download-west.oracle.com/docs/cd/A87860_01/doc/server.817/a76956/archredo.htm#4553,

is excellent and discloses all the features that you need to be in ARCHIVELOG mode, but given the impressions surrounding the init<SID>.ora commands, it is safest to be completely explicit about the commands you need to be in ARCHIVELOG mode:

First, the method to determine your ARCHIVELOG mode and status is to issue this command, either in SQL*Plus for Oracle 9i or Oracle 8i, or in "svrmgrl" if you are running Oracle 8i. If you have implemented, either via init<SID>.ora or interactively, the commands in Ken's post, then the results following the command below is what you would see:

[b]archive log list[/b]

Database log mode              No Archive Mode
Automatic archival             Enabled
Archive destination            D:\Oracle\Ora92\RDBMS
Oldest online log sequence     217
Current log sequence           219

Notice that "Authomatic archival = Enabled"...thus giving the false sense of security. But, notice further that "Database log mode = No Archive Mode", thus my earlier statement that you are "automatically NOT archiving". They only thing running that has anything to do with archiving is the ARCH background process that is ready, willing, and able to automatically archive redo log files when they fill up, but sadly ARCH will never be called upon to archive anything since the database is in "No Archive Mode" !

This situation, is, sadly, the precarious (to devastating) situation of far too many Oracle databases worldwide...The DBA has the false sense of security that her/his database is in ARCHIVELOG mode simply because their parameter file contains the setting, "log_archive_start = true".

Now, on to a complete solution...the only effective method to place your database into ARCHIVELOG mode is to effect the following commands:

1) [b]Bring down a running, NOARCHIVELOG database[/b]: shutdown immediate
2) [b]Bring the database up to MOUNT status[/b]: startup mount
3) [b]Issue the command to put the database into ARCHIVELOG mode[/b]: ALTER DATABASE ARCHIVELOG;

The above command, "ALTER DATABASE ARCHIVELOG;" is the key to putting your database into a more risk-free mode...there is no alternative command to do so.

You still should supply the init<SID>.ora commands that Ken recommends, especially "log_archive_start = true". Yes, your database can be in ARCHIVELOG mode and "log_archive_start = false". Such a situation is temporarily workable and it is very dangerous. This scenario means that Oracle will cause your on-line redo log files to switch to the next file in sequence, but Oracle will not be automatically archiving them...you must manually archive your full on-line redo log files with at least one of these commands that appeared in Ken's link:

alter system archive log all;
alter system archive log next;

If you do not manually enter one of these commands, then Oracle will hang at the point that it must do a log switch, but the on-line redo log file has not yet successfully archived. Again this scenario occurs when your database is in ARCHIVELOG mode, but "log_archive_start = false". If your parameter setting is "log_archive_start = true", then this means the automatically archiving ARCH background process is running to do all of the otherwise-manual work for you.

Another recommendation is that following your putting your database into ARCHIVELOG mode, you do a full, cold backup as soon as it is reasonable, so that you have a baseline, fall-back origin in case you must recover your database in the near future.

A couple of comments to Ken:

1) You are one of my most highly respected and helpful colleagues here on Tek-Tips. By no means do I mean or imply any disrespect by my bringing this issue up. I bring it up because the omission of "ALTER DATABASE ARCHIVELOG;" is such a potentially devastating error, I had to speak very directly.

2) Given the above scenario, I propose that you check the databases that you want to have in ARCHIVELOG mode (using the command "ARCHIVE LOG LIST") to confirm their actual archiving status.

Everyone, please accept this posting with the good wishes and respect with which I intend it. Malice toward none...Glad tidings to all.

Ken, let us know how the ARCHIVELOG assessment goes with your databases (as a possible warning to others, since Oracle spends far too little time and resources and documentation time explaining this nearly univerally misunderstood concept). Tman, let us know how the dust settles with all of this in your shop.

Regards,


Mufasa
(aka Dave of Sandy, Utah, USA)

http://www.dasages.com

Do you use Oracle and live or work in Utah, USA?
Then click here to join Utah Oracle Users Group on Tek-Tips.

 

[KenCunningham]

Dave, absolutely no offence taken and you are absolutely correct, Oracle are guilty of misrepresenting the 'safety' of this feature if the essential ALTER DATABASE ARCHIVELOG step is omitted. It's my bad to have missed it in my post too, so thanks for pointing it out for tman's and my own benefit.

As it happens (and I felt compelled to check!!) all my production databases report that they are in archive mode and all init files have log_archive_start set to true, so hopefully all is well in my corner of the world.

Again, thanks for the heads up, it's good to know that someone of your experience and know-how is there when one fumbles a catch occasionally. Regards.