ORA-00470: LGWR process terminated with error
Johne_uk
I am currently running two Oracle 10G instances from a single Solaris
M4000 server. Every few days one of the instances crashes with the
following error and has to be restarted.
ORA-00470: LGWR process terminated with error
PMON: terminating instance due to error 470
I have spend weeks running various trace files etc with Oracle support
and they are basically clueless as to what is the cause. They are
saying it is a Solaris OS issue but surely this would affect both
instances and not just one.
Essentially something is killing the LGWR process and the instance is
shutting itself down. I think the way ahead is to try and find out
what is killing this process but I'm not sure how to go about this and
worried that any logging may degrade server performance.
Can anybody offer any suggestions ?
Thanks in advance
John
Mladen Gogala
> Essentially something is killing the LGWR process and the instance is
> shutting itself down. I think the way ahead is to try and find out what
> is killing this process but I'm not sure how to go about this and
> worried that any logging may degrade server performance.
>
> Can anybody offer any suggestions ?
Let me see whether I understood you correctly: Oracle support is trying
to fix your issue for weeks, they presumably have all the trace files,
core files and RDA output but are unable to determine the exact cause of
the problem despite having all this information and you are asking us to
guess what the problem is without even knowing the version of the RDBMS
and Solaris? No problem, I can do that. The answer to your problem is the
number 42.
Johne_uk
Actually I was looking for some assistance on determining what is
killing the LGWR process. Oracle do not provide OS support ! I thought
the question was clear enough but obviously not. Thanks for the
comment though as its must have wasted five minutes.
ddf
>
> - Show quoted text -
I understand you are looking for assistance however you failed to
provide sufficient information in your vague problem description to
allow anyone to help. Yes, the LGWR process is dying; is it
accompanied by an ORA-07445 error? Is there any indication in the
resulting trace files that Solaris is at fault? Which release of
Oracle are you running? Yes, I know, you blurted out the wonderful
marketing speak of 10g but that umbrella covers two major releases
plus 8 patch levels with major changes between the two so please do us
a favor and take the time and effort to post that information to 4 or
5 numbers (10.1.0.4, 10.2.0.3, etc.). Also post the version of
Solaris this database is running on, including the curent patch level
(a uname -a can provide that information). As noted in a previous
post you've supplied far more information to Oracle and they,
apparently, can't solve the problem; why would you even think that
giving us such sketchy details would allow us to magically provide an
answer that Oracle (who has far more data than we do) cannot?
This is a help forum, not a carnival sideshow; we cannot pull answers
out of nothing more than thin air and thin air, really, is all you've
provided us. You have no right to complain regarding the response
first given you since you ambiguously described your problem and
expected detailed assistance to be forthcoming.
Supply much more information and you may find better help with your
issue.
David Fitzjarrell
Johne_uk
Understood David I will follow up with more information
cheers
Alberto Frosi
It's very strange behaviour from Oracle support, after send a trace,
log file and more and more, they don't tell anything...for me are very
busy with Sun's fusion or with your trimarano BMW Oracle in New
Zeland.
Version solaris?
Eugene Pokopac
http://www.orafaq.com/forum/t/23468/2/
Please check that the DB is patched and updated with all the latest
patches (one-off as well as cpu patch).
Best of luck in your debugging efforts.
Regards
Eugene Pokopac (Oracle DBA - Tucker, GA)
Charles Hooper
> killing the LGWR process. Oracle do not provide OS support ! I thought
> the question was clear enough but obviously not. Thanks for the
> comment though as its must have wasted five minutes.
If you have access to Oracle support (as you stated), you should be
able to review the following Metalink documents:
Doc ID 1022782.6 - ORA-447, ORA-470 Found in PMON Trace File, Database
Crashed
Doc ID 431246.1 - Pmon terminated instance due to LGWR termination
ORA-470
Doc ID 874832.1 - LNS: Standby redo logfile ,DB Crash
Doc ID 745198.1 - RAC DATABASE HANGS WHEN RMAN IS STARTED
Doc ID 1029808.6 - BACKGROUND PROCESS DIES, NO TRACE FILE GENERATED
Doc ID 564830.1 - ORA-00470 Error Creating 20GB Tablespace
Doc ID 1016959.102 - HP-UX: DB CRASH. ALERT LOG SHOWS NO ERRORS, BUT
TRC FILES CREATED.
Some of the documents are specific to other operating systems, but may
provide you with clues that will help resolve the problem. One of the
articles mentioned that the problem could be caused by a slow file
system. Several of the articles indicated that the error may be
caused by exceeding the operating system limit for the maximum number
of open files.
Charles Hooper
Co-author of "Expert Oracle Practices: Oracle Database Administration
from the Oak Table"
http://hoopercharles.wordpress.com/
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc.
joel garry
> Hi,
>
> I am currently running two Oracle 10G instances from a single Solaris
> M4000 server. Every few days one of the instances crashes with the
> following error and has to be restarted.
>
> ORA-00470: LGWR process terminated with error
> PMON: terminating instance due to error 470
>
> I have spend weeks running various trace files etc with Oracle support
> and they are basically clueless as to what is the cause. They are
> saying it is a Solaris OS issue but surely this would affect both
> instances and not just one.
Charles gave more details, but let me emphasize this: if the issue is
one of OS resources, it will surely affect one more than the other,
possibly the second one started.
You might want to google for dtrace, especially as it applies to
Oracle. Don't have time to look just now, but IIRC Tanel Poder, James
Morle and Kerry Osborne have some things about dtrace on their blogs.
Hey, oracle wants sun admins for aces: http://www.sun.com/bigadmin/home/index.jsp
>
> Essentially something is killing the LGWR process and the instance is
> shutting itself down. I think the way ahead is to try and find out
> what is killing this process but I'm not sure how to go about this and
> worried that any logging may degrade server performance.
Performance of a crashed instance is always the worst degradation.
jg
--
@home.com is bogus.
http://stevejmitchell.blogspot.com/2009/07/computer-programming-as-stochastic-art.html
Mladen Gogala
> Performance of a crashed instance is always the worst degradation.
I beg to differ. If the database crashes, all your queries finish
instantly. I would even suggest that database crash is the ultimate thing
in application tuning.
Mladen Gogala
> Thanks for the
> comment though as its must have wasted five minutes.
It didn't. I'm the fastest typist in the west.
joel garry
Depends what your definition of is is. I've seen apps that don't tell
the user the db has gone away until they do some more input or tcp
times out and they get a "server connection lost" error. Though the
more common issue is they X out the client because they entered too
broad of a filter or are otherwise impatient, and happily start
another, blissfully unaware they are giving the server a stress-test
workout.
But of course, worse degradation is obscure data corruption over a
period of time. So maybe a crash isn't so bad, at least it's
recoverable. Unless of course you have some weirdo virtualization
that lies to Oracle about having written redo.
Note to John, about this group: http://dbaoracle.net/readme-cdos.htm
Welcome!
jg
--
@home.com is bogus.
A snarky answer I managed to forbear on forums:
> Our oracle server *SYS(as sysdba)* user *logon by given any password*.
>
> We are shocking and need to be arrested this issue immediately as its very danger.
> I have altered the user "SYS" with new password. Eventhough, still its logon by using any password.
>
> Kindly guide / help me to address this issue ASAP.
>
> Thanks,
> Orahar.
I agree, your DBA needs to be arrested immediately before cardiac
damage ensues.
vsevolod afanassiev
ORACLE_HOME.
If the are not then the issue is version-related, or installation
related.
Assuming that both instances are run from the same ORACLE_HOME:
I see two possibilities:
1. Instance is killed by something external, similar to someone doing
'kill -p <pid of LGWR>"
2. Instance dies
1. Instance is killed by something external
There are two instances on the server, but only one experiences this
problem, correct? Are instances in any way different? For example,
instance A has 3 GB SGA while instance B has 10 GB SGA?
If they are different then try to make them identical, make sure that
all init.ora parameters are the same (with obvious exceptions - things
like control_files, background_dump_dest). If possible try to achieve
it by REDUCING values, not increasing them. Once this is done we
could expect two outcomes:
- The crashes will stop. It is possible that "something" was killing
instance as it was too big. Once it is made smaller it will no longer
get killed.
- The crashes will affect both instances. This indicates that killing
wasn't based on size.
2. Instance dies
Oracle uses following facilities provided by OS:
- CPU
- memory
- disk
- IPC facilities (shared memory and semaphores on Solaris)
CPU is unlikely to disappear, IPC facilities are allocated at startup,
so most likely the issue is either
memory-related or disk-related. As this is LGWR disk issue seems more
likely.
Do you have dedicated filesystems for each instance? Or filesystems
are shared?
What filesystems you are using: UFS, ZFS, Veritas? Do you use
something fancy like
ODM (Oracle Disk Manager)?
Finally: do you use any fancy Solaris 10 stuff like zones/containers?
- - - - - - - - -
I had a similar issue on Tru64 several years ago, very frustrating.
The database had nightly cold backup. Once-twice per month the
instance would start in corrupted state - it was possible to connect
to it but not run any SQL. Oracle Support pointed to a bug where
instance gets corrupted on startup if something tries to connect to it
in the brief moment between 'startup' command and 'Oracle instance
started' message (just a second or two). It had to be SYSDBA
connection, and it was happening at 6am. What could possibly do that?
Eventually it was traced to UNIX script provided by DEC. It took
several months to locate.
Steve Howard
You need to ask for a better analyst, and that isn't being facetious.
The ability to predict the quality of the analyst you can get at
Oracle support is about as reliable as their support site.
Seriously, ask for the SR to be escalated/duty managed.
Alberto Frosi
For example for the better analysis i ask, the LGWR process it's
always the same for the same instance or is it ramdom?
because otherwise could be a hundred of variables for OS for DB.