oracle.html

<!doctype html public "-//w3c//dtd html 4.0 transitional//en">
<html>
<head>
   <title>Oracle Recovery Procedure</title>
</head>

<center><font face="TIMES"><font size=+4>Recovering Oracle</font></font></center>

<p><font face="TIMES"><font size=-1>Since an Oracle database consists of
several interrelated parts, recovering such a database is done through
a process of elimination. Identify which pieces work, then recover the
pieces that don't work. The following recovery guidefollows that logic
and works regardless of the chosen backup method. It consists of a flowchart
and a procedure whose numbered steps correspond to the elements in the
flowchart.</font></font>
<p><map NAME="flowchrt">
<area HREF="#Step12" SHAPE="RECT" COORDS="561,517,689,592">
<area HREF="#Step14" SHAPE="RECT" COORDS="565,605,689,656">
<area HREF="#Step18" SHAPE="POLY" COORDS="612,970,614,969,689,928,761,973,688,1014,612,970">
<area HREF="#Step19" SHAPE="POLY" COORDS="563,874,564,874,640,833,720,879,640,921,563,874">
<area HREF="#Step21" SHAPE="RECT" COORDS="644,740,755,813">
<area HREF="#Step20" SHAPE="RECT" COORDS="495,699,632,776">
<area HREF="#Step17" SHAPE="POLY" COORDS="407,844,408,844,483,804,558,847,484,889,407,844">
<area HREF="#Step22" SHAPE="POLY" COORDS="427,935,371,971,426,1002,485,969,427,935">
<area HREF="#Step23" SHAPE="RECT" COORDS="224,942,355,1002">
<area HREF="#Step25" SHAPE="POLY" COORDS="313,871,314,871,377,906,432,868,374,841,313,871">
<area HREF="#Step26" SHAPE="RECT" COORDS="170,847,289,894">
<area HREF="#Step24" SHAPE="RECT" COORDS="89,916,200,958">
<area HREF="#Step28" SHAPE="RECT" COORDS="177,776,289,831">
<area HREF="#Step10" SHAPE="RECT" COORDS="34,779,147,804">
<area HREF="#Step31" SHAPE="RECT" COORDS="57,727,193,769">
<area HREF="#Step27" SHAPE="POLY" COORDS="301,732,377,691,450,737,377,778,301,732">
<area HREF="#Step13" SHAPE="POLY" COORDS="389,660,466,618,540,662,465,705,389,660">
<area HREF="#Step29" SHAPE="POLY" COORDS="28,653,106,609,182,655,106,695,28,653">
<area HREF="#Step30" SHAPE="RECT" COORDS="198,590,365,647">
<area HREF="#Step11" SHAPE="POLY" COORDS="391,550,392,550,467,507,542,553,466,594,391,550">
<area HREF="#Step16" SHAPE="RECT" COORDS="210,518,365,575">
<area HREF="#Step15" SHAPE="POLY" COORDS="29,522,106,478,180,523,105,567,29,522">
<area HREF="#Step10" SHAPE="POLY" COORDS="129,450,130,450,207,409,279,455,204,496,129,450">
<area HREF="#Step8" SHAPE="RECT" COORDS="117,311,338,365">
<area HREF="#Step9" SHAPE="RECT" COORDS="358,308,633,368">
<area HREF="#Step31" SHAPE="RECT" COORDS="607,257,671,303">
<area HREF="#Step1" SHAPE="RECT" COORDS="662,152,773,178">
<area HREF="#Step4" SHAPE="POLY" COORDS="652,99,654,99,728,57,801,102,724,144,652,99">
<area HREF="#Step7" SHAPE="RECT" COORDS="545,163,656,208">
<area HREF="#Step6" SHAPE="POLY" COORDS="345,224,423,184,494,228,420,271,345,224">
<area HREF="#Step3" SHAPE="RECT" COORDS="367,54,502,152">
<area HREF="#Step4" SHAPE="POLY" COORDS="206,225,271,262,332,226,268,193,206,225">
<area HREF="#Step5" SHAPE="RECT" COORDS="141,140,245,197">
<area HREF="#Step2" SHAPE="POLY" COORDS="198,100,199,100,274,57,349,102,272,146,198,100">
<area HREF="#Step1" SHAPE="POLY" COORDS="27,100,107,55,175,102,103,143,27,100">
</map>
<img SRC="oracle.gif" NAME="flowchrt" USEMAP="#flowchrt" height=1061 width=827>


<p><a NAME="Step1"></a><b><font face="TIMES"><font size=+1>Step 1: Try
Startup Mount</font></font></b>
<p><font face="TIMES"><font size=-1>The first step in verifying the condition
of an Oracle database is to attempt to mount it. This works because mounting
a database (without opening it) reads the control files but does not open
the data files. If the control files are mirrored, Oracle attempts to open
each of the control files that are listed in the <i>initORACLE_SID.ora</i>
file. If any of them is damaged, the mount fails.</font></font>
<p><font face="TIMES"><font size=-1>To mount a database, simply run <i>svrmgrl</i>,
connect to the database, and enter <i>startup mount</i>.</font></font>
<dir><font face="Courier"><font size=-2>$ <b>svrmgrl</b></font></font>
<p><font face="Courier"><font size=-2>SVRMGR > <b>connect internal;</b></font></font>
<p><font face="Courier"><font size=-2>Connected.</font></font>
<p><font face="Courier"><font size=-2>SVRMGR > <b>startup mount;</b></font></font>
<p><font face="Courier"><font size=-2>Statement processed.</font></font></dir>
<font face="TIMES"><font size=-1>If it succeeds, the output looks something
like this:</font></font>
<dir><font face="Courier"><font size=-2>SVRMGR > <b>startup mount;</b></font></font>
<p><font face="Courier"><font size=-2>ORACLE instance started.</font></font>
<p><font face="Courier"><font size=-2>Total System Global Area 5130648
bytes</font></font>
<p><font face="Courier"><font size=-2>Fixed Size 44924 bytes</font></font>
<p><font face="Courier"><font size=-2>Variable Size 4151836 bytes</font></font>
<p><font face="Courier"><font size=-2>Database Buffers 409600 bytes</font></font>
<p><font face="Courier"><font size=-2>Redo Buffers 524288 bytes</font></font>
<p><font face="Courier"><font size=-2>Database mounted.</font></font>
<dir><font face="TIMES"><font size=-1>If the attempt to mount the database
succeeds, proceed to Step 10.</font></font></dir>
</dir>
<font face="TIMES"><font size=-1>If the attempt to mount the database fails,
the output looks something like this:</font></font>
<dir><font face="Courier"><font size=-2>SVRMGR > <b>startup mount;</b></font></font>
<p><font face="Courier"><font size=-2>Total System Global Area 5130648
bytes</font></font>
<p><font face="Courier"><font size=-2>Fixed Size 44924 bytes</font></font>
<p><font face="Courier"><font size=-2>Variable Size 4151836 bytes</font></font>
<p><font face="Courier"><font size=-2>Database Buffer to s 409600 bytes</font></font>
<p><font face="Courier"><font size=-2>Redo Buffers 524288 bytes</font></font>
<p><font face="Courier"><font size=-2>ORACLE instance started.</font></font>
<p><font face="Courier"><font size=-2>ORA-00205: error in identifying controlfile,
check alert log for more info</font></font>
<dir><font face="TIMES"><font size=-1>If the attempt to mount the database
fails, proceed to Step 2.</font></font></dir>
</dir>
<a NAME="Step2"></a><b><font face="TIMES"><font size=+1>Step 2: Are All
Control Files Missing?</font></font></b>
<p><font face="TIMES"><font size=-1>Don't panic if the attempt to mount
the database fails. Control files are easily restored if they were mirrored,
and can even be rebuilt from scratch if necessary. The first important
piece of information is that one or more control files are missing.</font></font>
<p><font face="TIMES"><font size=-1>Unfortunately, since Oracle aborts
the mount at the first failure it encounters, it could be missing one,
two, or all of the control files, but so far you know only about the first
missing file. So, before embarking on a course of action, determine the
severity of the problem. In order to do that, do a little research.</font></font>
<p><font size=-1><font face="TIMES">First, determine the names of all of
the control files. Do that by looking at the <i>configORACLE_SID.ora </i>file
next to the word </font><font face="Courier">control files</font><font face="TIMES">.
It looks something like this:</font></font>
<dir><font face="Courier"><font size=-2>control_files = (/db/Oracle/a/oradata/crash/control01.ctl,</font></font>
<p><font face="Courier"><font size=-2>/db/Oracle/b/oradata/crash/control02.ctl,</font></font>
<p><font face="Courier"><font size=-2>/db/Oracle/c/oradata/crash/control03.ctl)</font></font></dir>
<font size=-1><font face="TIMES">It's also important to get the name of
the control file that Oracle is complaining about. Find this by looking
for the phrase </font><font face="Courier">control file:</font><font face="TIMES">
in the alert log. (The alert log can be found in the location specified
by the </font><font face="Courier">background_dump_dest</font><font face="TIMES">
value in the <i>configinstance.ora</i> file. (Typically, it is in the <i>ORACLE_BASE/ORACLE_SID/admin/bdump</i>
directory.) In that directory, there should be a file called <i>alert_ORACLE_SID.log</i>.
In that file, there should be an error that looks something like this:</font></font>
<dir><font face="Courier"><font size=-2>Sat Feb 21 13:46:19 1998</font></font>
<p><font face="Courier"><font size=-2>alter database mount exclusive</font></font>
<p><font face="Courier"><font size=-2>Sat Feb 21 13:46:20 1998</font></font>
<p><font face="Courier"><font size=-2>ORA-00202: controlfile: '/db/a/oradata/crash/control01.ctl'</font></font>
<p><font face="Courier"><font size=-2>ORA-27037: unable to obtain file
status</font></font>
<p><font face="Courier"><font size=-2>SVR4 Error: 2: No such file or directory</font></font>
<dir><font face="TIMES"><font size=-1>Warning! Some of the following procedures
may say to override a potentially corrupted control file. Since one never
knows which file may be needed, always make backup copies of all of the
control files before doing any of this. That offers an "undo" option that
isn't possible otherwise. (Also make copies of the online redo logs as
well.)</font></font></dir>
</dir>
<font face="TIMES"><font size=-1>With the names of all of the control files
and the name of the damaged file, it's easy to determine the severity of
the problem. Do this by listing each of the control files and comparing
their size and modification time. (Remember the game "Which one of these
is not like the other," on Sesame Street?) The following scenarios assume
that the control files were mirrored to three locations, which is a very
common practice. The possible scenarios are:</font></font>
<p><font face="TIMES"><font size=-1>The damaged file is missing, and at
least one other file is present</font></font>
<dir><font face="TIMES"><font size=-1>If the file that Oracle is complaining
about is just missing, that's an easy thing to fix.</font></font>
<dir><font face="TIMES"><font size=-1>If this is the case, proceed to Step
3.</font></font></dir>
</dir>
<font face="TIMES"><font size=-1>The damaged file is not missing. It is
corrupted</font></font>
<dir>
<dir><font face="TIMES"><font size=-1>This is probably the most confusing
one, since it's hard to tell if a file is corrupted. What to do in this
situation is a personal choice. Before going any farther, make backup copies
of all control files. Once you do that, try a "shell game" with the different
control files. The shell game consists of taking one of the three control
files and copying it to the other two files' locations. Then attempt to
mount the database again. The "shell game" is covered in Step 3.</font></font></dir>
<font face="TIMES"><font size=-1>However, if all the online redo logs are
present, it's probably easier at this point to just run the "create controlfile"
script discussed in Steps 6 and 7. This rebuilds the control file to all
locations automatically. (Before that, though, follow Steps 4 and 5 to
verify if all the data files and log files are present.)</font></font>
<dir><font face="TIMES"><font size=-1>To rebuild the control file using
the "create controlfile" script, proceed to Steps 4 through 7.</font></font></dir>
</dir>
<font face="TIMES"><font size=-1>All of the control files are missing,
or they are all different sizes and/or times.</font></font>
<dir><font face="TIMES"><font size=-1>If all of the control files are corrupt
or missing, they must be rebuilt or the entire database must be restored.
Hopefully your backup system has been running the <i>backup control file
to trace</i> command on a regular basis. (The output of this command is
a SQL script that will rebuild the control files automatically.)</font></font>
<dir><font face="TIMES"><font size=-1>If the <i>backup control file to
trace</i> command has been running, proceed to Steps 4 through 7. If not,
then proceed to Step 8.</font></font></dir>
</dir>
<a NAME="Step3"></a><b><font face="TIMES"><font size=+1>Step 3: Replace
Missing Control File</font></font></b>
<p><font face="TIMES"><font size=-1>If the file that Oracle is complaining
about is either missing or appears to have a different date and time than
the other control files, this will be easy. Simply copy another one of
the mirrored copies of the control file to the damaged control file's name
and location. (The details of this procedure are below.) Once this is done,
just attempt to mount the database again.</font></font>
<dir>
<dir><font face="TIMES"><font size=-1>Warning! Make sure to make backup
copies of all of the control files before overwriting them!</font></font></dir>
</dir>
<font face="TIMES"><font size=-1>The first thing to do is to get the name
of the damaged control file. Again, this is relatively easy. Look in the
alert log for a section like the one below:</font></font>
<dir><font face="Courier"><font size=-2>Sat Feb 21 13:46:19 1998</font></font>
<p><font face="Courier"><font size=-2>alter database mount exclusive</font></font>
<p><font face="Courier"><font size=-2>Sat Feb 21 13:46:20 1998</font></font>