Re: DB recovery 'opportunity' - not urgent

Comments embedded.
Ed Stevens wrote:
> Platform - Oracle 8.1.7 on Win2k server
>
> Disclaimer: I was called in to pull this db out of the fire. I have
> never seen this db before, and had no hand in its setup or current
> condition. The guy that normally covers this db is unavailable.
>

As usual. :)

> App and DB starting reporting problems on Jul 22. I was called in on
> July 28.
> Startup of db fails. Here are the last few lines from the alert log:
>
> Completed: ALTER DATABASE MOUNT
> Thu Jul 28 14:33:18 2005
> ALTER DATABASE OPEN
> ARCH: Beginning to archive log# 3 seq# 831
> Thu Jul 28 14:45:10 2005
> ARCH: I/O error 19502 archiving log 3 to 'E:\ORAARCH\XVLP\ARCH_831.ARC'
> ARCH: Archiving not possible: error count exceeded
> ARCH: Failed to archive log# 3 seq# 831
> ORA-16038 signalled during: ALTER DATABASE OPEN...
>

I can't tell if this is a hardware issue (SAN, disk) or a memory issue and one could suspect both with Win2K and Oracle. It depends upon how much RAM is on that server and how it's being used, or abused.

> The first thing I checked was available disk space at the archive
> destination. There were several dozen gig available. All web serches
> (MetaLink, this ng, AskTom, Google ...) keep pointing to disk full
> conditions. We do know that the server admins have been monkying with
> the disks, which are in a SAN unit. We have gotten little info from
> them ... they (and the server) are located in Mexico, and their English
> is little better than our Spanish.
>
> Further tidbits:
>
> There is very little alert log history available. There are scripts on
> the server for stopping and starting the DB's (two of them) and part of
> the shutdown renames the alert log to a backup, keeping only three
> generations. Unfortunately, this was done 3 times in one day -- after
> the problems began -- so any info on what led into the current
> situation has been lost.
>
> On the day the problems began the orginal DBA, for reasons unknown,
> modified the init.ora file, removing references to the 2d and 3d
> control files. Those files still exist, but of course are out of sync
> with the one remaining active file.
>

Why one would eliminate control files when the error clearly states archivelog issues is a mystery.

> Now, for the real kicker ... there are no backups ......
>

This is typical for installations in Mexico and South America, from my experience. The companies are willing to spend money for Oracle, but not for the hardware necessary to enact usable backups. I have no idea why this is true in your case; all of the systems with which I was involved were used for telecom applications, and the telco was apparently more interested in profit than reliability. (My two cents).

> Fortunately, the way this app shares data with the mainframe, we *CAN*
> recover by recreating the db from scratch and having the app issue a
> request to reload from the mainframe. But as an educational exercise,
> I'd like to explore other possibilities -- just in case I find myself
> in a similar situation with an app that doesn't so easily recover
> itself.
>
> The course of action that seems best is to:
> 1 - stop the db
> 2 - copy the one active control file over the two old ones (with
> corresponding renaming)
> 3 - re-instate the control file references in init.ora
> 4 - startup nomount
> 5 - open resetlogs
>
> What say the jury?
>

You have hit the nail on the head, so to speak, in my opinion, as this is where I'd begin in this situation. Before you do this, are there no SAN diagnostics which can be run to verify the unit is sound and operating correctly? I realise this is difficult to do as your Spanish is lacking, however I do feel such analysis is warranted given the nature of the problem. Of course, you've already thought of this. :)

> Yes, I've modified the shutdown script to keep much more alert.log
> history, and will be addressing the lack of backup ...

David Fitzjarrell Received on Fri Jul 29 2005 - 11:48:54 CDT