Home » RDBMS Server » Performance Tuning » Avoiding : ORA-00470: LGWR process terminated with error
Avoiding : ORA-00470: LGWR process terminated with error [message #65519] Tue, 12 October 2004 04:56 Go to next message
Josphat Kioko
Messages: 9
Registered: April 2004
Junior Member
Hi

I am running batch proceses (pl/sql) on oracle 8.0

and the job terminates forcing database shutdown with

the following message

ORA-00470: LGWR process terminated with error

ORA-06512: at line 21

Are there certain settings which should be changed in the database? How should I go about it?

Thanks

 

 
Re: Avoiding : ORA-00470: LGWR process terminated with error [message #335731 is a reply to message #65519] Wed, 23 July 2008 07:54 Go to previous message
sunil_v_mishra
Messages: 506
Registered: March 2005
Senior Member
For more understanding of ORA-00470 below are the two other case discussed hope this case may help you.

CASE 1
Problem Description:

If database performance is degrading and eventually you crash.

You look in the PMON trace file and find the following errors.

error 470 detected in background process

00447, 00000, "fatal error in background process"
// *Cause: One of the background processes died unexpectedly.
// *Action: Warm start the system.
ora-470
00470, 00000, "LGWR process terminated with error"
// *Cause: The log writer process died
// *Action: Warm start instance
DBWR and SMON indicate the same thing.



If you are receiving the following errors in PMON, LGWR and SMON trace files: error 470 detected in background process OPIRIP: Uncaught error 447. Error stack: ORA-00447: fatal error in background process ORA-00470: LGWR process terminated with error

No errors are being reported in the alert.log. You did receive the following alert.log entry: "ORACLE Instance <name> - Can not allocate log, archival required"

You are forced to do the following to get the database back up:

SHUTDOWN ABORT
STARTUP
SHUTDOWN IMMEDIATE
STARTUP
Solution: INCREASE MAXFILES SETTING


Solution Description:

That means we need to verify and possibly increase MAXFILES setting.

Solution Explanation: goes like this suppose, the user had recently added new datafiles to his instance.

On checking the MAXFILES kernal parameter, user discovered that it was set to 60. Checking V$datafiles showed 47 data files. Computing the total of datafiles, control files, mirror redo logs, init.ora and alert.log files, total exceeded the 60 file limit.


CASE 2

Problem Description:


If you have problems with the database crashing & if you look in the alert.log and find no errors or trace files referenced. When you check the bdump directory, you do find three trace files, a DBWR, a LGWR and a PMON trace file. The DBWR trace file contains the following errors:

ora-00447 fatal error in background process ora-00470 LGWR process terminated with error

In the LGWR trace file you find:

ora-00447 fatal error in background process
ora-00313 open failed for members of log group <name> of thread <name>
ora-00312 online log <name> thread <num>: <str>
ora-07362 sfifi: open error, unable to open file.
ora-00470 LGWR process terminated with error
In the PMON trace file you find:
ora-00447 fatal error in background process ora-00470 LGWR process terminated with error

Problem Explanation:


The OS kernel parameter 'NFILES' parameter is set too low. When Oracle is trying to open an online log file, it is failing due to operating system restraints.
You may have recently upgraded or migrated and added more redo log groups or mirrors. Thus you are now having this problem where you did not previously.

Solution: INCREASE OS PARAMETER 'NFILES'.

Solution Description:


Have your System Administrator increase the 'NFILES' parameter on the Operation System.

Solution Explanation:

The OS kernel parameter 'NFILES' parameter is set too low. When Oracle is trying to open an online log file, it is failing due to operating system restraints.



Note:- Please check that DB is patched and updated with all the latest patch (one-off as well as cpu patch).

[Updated on: Wed, 23 July 2008 13:32] by Moderator

Report message to a moderator

Previous Topic: performance issues in insert stmt
Next Topic: high cost
Goto Forum:
  


Current Time: Tue Dec 06 08:28:29 CST 2016

Total time taken to generate the page: 0.27230 seconds