Oracle 7.3.4 database shuts down by it self

[Noraseh Singholka]

Hello DBAs,

I have a problem with a C3600 HP-UX 10.20 workstation.

The Oracle database shuts down from time to time every day, rebooting the workstation helps brign up the database but then it shuts it self down again within a couple of hours.

Attached is the alert logfile from ~oracle/rdbms/logs directory.

Thank you for your help.

[John Palmer]

Hi,

This sounds like a known problem, Oracle BUG 671491.

This is to do with an undocumented limit of 32767 extents associated with a rollback segment.

Oracle suggest running:-

SVRMGR> select max(extents) from v$rollstat;
and checking if any value returned is > 32767.

If so, they suggest contacting Oracle support for advise. They also recommend specifying a maxextents value of 32767 on rollback segments, setting 'unlimited' for example can hit this bug.

I suggest that you call Oracle Support in any case.

Regards,
John

[Noraseh Singholka]

Hi,

Thanks for the fast reply.

I've checked the WS with your suggestions and found that the max was 54, BUT in another workstation it was only 4 and still running with no problems.

The only difference between the two were that the working one was a C120 HP-UX workstation with NO 700QPK1020 patches installed on it and the problem WS was a C715 (apollo) WS WITH the 700QPK1020 patch installed on it.

Any other suggessions? thank you.

[William E. Bataille]

Hi Noraseh,
It looks like you might have a 'corrupted' rollback segment.
If you can start the database up, I would:
1. create a new tablespace (RBS2)
2. create new rollback segments in the new tablespace, avoid storage parameter maxextents unlimited.
3. take the old rollback segments offline, drop them, then drop the old rollback tablespace.
Good Luck, Bill Bataille
some oracle doc follows:

What could be some reasons a datablock is unaccessible for undo?
1-If a tablespace or a datafile is offline or missing.
2-If the object the datablock belongs to is corrupted.
3-If the datablock that is corrupt is actually in the rollback segment
itself rather than the object.


HOW TO RESOLVE IT
-----------------
1-MAKE sure that all tablespaces are online and all datafiles are
online. This can be checked through v$datafile, under the
status column. For tablespaces associated with the datafiles,
look in dba_tablespaces.

If that still does not resolve the problem then

2-PUT the following in the init.ora-
event = "10015 trace name context forever, level 10"

Setting this event will generate a trace file that will reveal the
necessary information about the transaction Oracle is trying to roll
back and most importantly, what object Oracle is trying to apply
the undo to.

3-SHUTDOWN the database (if normal does not work, immediate, if that does
not work, abort) and bring it back up.

Note: An ora-1545 may be encountered, or other errors. If the database
cannot startup, contact customer support at this point.

4-CHECK in the directory that is specified by the user_dump_dest parameter
(in the init.ora or show parameter command) for a trace file that was
generated at startup time.

5-IN the trace file, there should be a message similar to-
error recovery tx(#,#) object #.

TX(#,#) refers to transaction information.
The object # is the same as the object_id in sys.dba_objects.

6-USE the following query to find out what object Oracle is trying to
perform recovery on.

select owner, object_name, object_type, status
from dba_objects where object_id =

[Noraseh Singholka]

Thanks for your information, after knowing that the roll bacl segment was corrupted then I did an export of the database then recreated all the database files and then import the database back in, so now seems that it hasn't been crashing any more.

Thanks for your hint.