Sunday, May 27, 2012

ORA-00470: LGWR process terminated with error, ora-00600 [3700]

Today i am seeing following message in my SMS Gateway sever's alert log file.
its seems problem in the file system of this server, log file can't work properly.....
so, i do a temprary solution for immediatelly solve the problem.



Sun May 27 10:31:35 2012
Shutting down instance (abort)
Sun May 27 10:31:35 2012
Starting ORACLE instance (normal)
LICENSE_MAX_SESSION = 0
LICENSE_SESSIONS_WARNING = 0
Picked latch-free SCN scheme 2
Using LOG_ARCHIVE_DEST_10 parameter default value as USE_DB_RECOVERY_FILE_DEST
Autotune of undo retention is turned on.
IMODE=BR
ILAT =97
LICENSE_MAX_USERS = 0
SYS auditing is disabled
ksdpec: called for event 13740 prior to event group initialization
Starting up ORACLE RDBMS Version: 10.2.0.3.0.
System parameters with non-default values:
processes = 800
sga_max_size = 734003200
__shared_pool_size = 255852544
__large_pool_size = 4194304
__java_pool_size = 12582912
__streams_pool_size = 0
sga_target = 734003200
control_files = E:\ORACLE\PRODUCT\10.2.0\ORADATA\SMSDB\CONTROL01.CTL, E:\ORACLE\PRODUCT\10.2.0\ORADATA\SMSDB\CONTROL02.CTL, E:\ORACLE\PRODUCT\10.2.0\ORADATA\SMSDB\CONTROL03.CTL
db_block_size = 8192
__db_cache_size = 452984832
compatible = 10.2.0.3.0
db_file_multiblock_read_count= 16
db_recovery_file_dest = E:\oracle\product\10.2.0\flash_recovery_area
db_recovery_file_dest_size= 2147483648
undo_management = AUTO
undo_tablespace = UNDOTBS1
remote_login_passwordfile= EXCLUSIVE
db_domain =
dispatchers = (PROTOCOL=TCP) (SERVICE=smsdbXDB)
job_queue_processes = 20
audit_file_dest = E:\ORACLE\PRODUCT\10.2.0\ADMIN\SMSDB\ADUMP
background_dump_dest = E:\ORACLE\PRODUCT\10.2.0\ADMIN\SMSDB\BDUMP
user_dump_dest = E:\ORACLE\PRODUCT\10.2.0\ADMIN\SMSDB\UDUMP
core_dump_dest = E:\ORACLE\PRODUCT\10.2.0\ADMIN\SMSDB\CDUMP
db_name = smsdb
open_cursors = 300
pga_aggregate_target = 314572800
aq_tm_processes = 2
PMON started with pid=2, OS id=2776
PSP0 started with pid=3, OS id=3404
MMAN started with pid=4, OS id=4088
DBW0 started with pid=5, OS id=4092
LGWR started with pid=6, OS id=1916
CKPT started with pid=7, OS id=448
SMON started with pid=8, OS id=444
RECO started with pid=9, OS id=868
CJQ0 started with pid=10, OS id=1688
MMON started with pid=11, OS id=1576
Sun May 27 10:31:35 2012
starting up 1 dispatcher(s) for network address '(ADDRESS=(PARTIAL=YES)(PROTOCOL=TCP))'...
MMNL started with pid=12, OS id=3276
Sun May 27 10:31:35 2012
starting up 1 shared server(s) ...
Sun May 27 10:31:35 2012
ALTER DATABASE MOUNT
Sun May 27 10:31:39 2012
Setting recovery target incarnation to 2
Sun May 27 10:31:39 2012
Successful mount of redo thread 1, with mount id 113714055
Sun May 27 10:31:39 2012
Database mounted in Exclusive Mode
Completed: ALTER DATABASE MOUNT
Sun May 27 10:31:39 2012
ALTER DATABASE OPEN
Sun May 27 10:31:40 2012
Errors in file e:\oracle\product\10.2.0\admin\smsdb\bdump\smsdb_lgwr_1916.trc:
ORA-00600: internal error code, arguments: [3700], [1], [15], [3], [3], [], [], []

Sun May 27 10:31:40 2012
Errors in file e:\oracle\product\10.2.0\admin\smsdb\bdump\smsdb_lgwr_1916.trc:
ORA-00600: internal error code, arguments: [3700], [1], [15], [3], [3], [], [], []

Sun May 27 10:31:40 2012
LGWR: terminating instance due to error 470
Sun May 27 10:31:40 2012
Errors in file e:\oracle\product\10.2.0\admin\smsdb\bdump\smsdb_dbw0_4092.trc:
ORA-00470: LGWR process terminated with error

Sun May 27 10:31:41 2012
Errors in file e:\oracle\product\10.2.0\admin\smsdb\bdump\smsdb_pmon_2776.trc:
ORA-00470: LGWR process terminated with error

Sun May 27 10:31:41 2012
Errors in file e:\oracle\product\10.2.0\admin\smsdb\bdump\smsdb_reco_868.trc:
ORA-00470: LGWR process terminated with error

Sun May 27 10:31:41 2012
Errors in file e:\oracle\product\10.2.0\admin\smsdb\bdump\smsdb_smon_444.trc:
ORA-00470: LGWR process terminated with error

Sun May 27 10:31:41 2012
Errors in file e:\oracle\product\10.2.0\admin\smsdb\bdump\smsdb_mman_4088.trc:
ORA-00470: LGWR process terminated with error

Sun May 27 10:31:41 2012
Errors in file e:\oracle\product\10.2.0\admin\smsdb\bdump\smsdb_psp0_3404.trc:
ORA-00470: LGWR process terminated with error

Sun May 27 10:31:42 2012
Errors in file e:\oracle\product\10.2.0\admin\smsdb\bdump\smsdb_ckpt_448.trc:
ORA-00470: LGWR process terminated with error

Instance terminated by LGWR, pid = 1916




===========================================================================
=============================solution======================================
===========================================================================

For thid i do the follwing steps:

SQL> CONN / AS SYSDBA
SQL> startup mount
SQL> recover database until cancel;
SQL> alter database open resetlogs;


like.........


SQL*Plus: Release 10.2.0.3.0 - Production on Sun May 27 10:28:15 2012

Copyright (c) 1982, 2006, Oracle. All Rights Reserved.

SQL> conn / as sysdba
Connected to an idle instance.
SQL> startup
ORACLE instance started.

Total System Global Area 734003200 bytes
Fixed Size 1292780 bytes
Variable Size 272631316 bytes
Database Buffers 452984832 bytes
Redo Buffers 7094272 bytes
Database mounted.
ORA-03113: end-of-file on communication channel



SQL> conn / as sysdba
Connected to an idle instance.
SQL> startup force
ORACLE instance started.

Total System Global Area 734003200 bytes
Fixed Size 1292780 bytes
Variable Size 276825620 bytes
Database Buffers 448790528 bytes
Redo Buffers 7094272 bytes
Database mounted.
ORA-03113: end-of-file on communication channel


SQL> shutdown immediate
ORA-24324: service handle not initialized
ORA-01041: internal error. hostdef extension doesn't exist
SQL>
SQL>
SQL> conn / as sysdba
Connected to an idle instance.
SQL>
SQL>
SQL> startup mount
ORACLE instance started.

Total System Global Area 734003200 bytes
Fixed Size 1292780 bytes
Variable Size 281019924 bytes
Database Buffers 444596224 bytes
Redo Buffers 7094272 bytes
Database mounted.
SQL>
SQL>
SQL> recover database until cancel ;
Media recovery complete.
SQL>
SQL>
SQL> alter database open resetlogs ;

Database altered.

SQL>
SQL>
SQL> $exp smsgtway1/smsgtway2 file=D:/smsgtway2_27052012.dmp owner=smsgtway1 ;

Export: Release 10.2.0.3.0 - Production on Sun May 27 10:42:06 2012

Copyright (c) 1982, 2005, Oracle. All rights reserved.


Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Produc
tion
With the Partitioning, OLAP and Data Mining options
Export done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set

About to export specified users ...
. exporting pre-schema procedural objects and actions
. exporting foreign function library names for user SMSGTWAY1
. exporting PUBLIC type synonyms
. exporting private type synonyms
. exporting object type definitions for user SMSGTWAY1
About to export SMSGTWAY1's objects ...
. exporting database links
. exporting sequence numbers
. exporting cluster definitions
. about to export SMSGTWAY1's tables via Conventional Path ...
. . exporting table SMS_ALL_TRAN 66131 rows exported
. . exporting table SMS_ALL_TRAN_BA 63166 rows exported
. . exporting table SMS_ALL_TRAN_REJECT 101 rows exported
. exporting synonyms
. exporting views
. exporting stored procedures
. exporting operators
. exporting referential integrity constraints
. exporting triggers
. exporting indextypes
. exporting bitmap, functional and extensible indexes
. exporting posttables actions
. exporting materialized views
. exporting snapshot logs
. exporting job queues
. exporting refresh groups and children
. exporting dimensions
. exporting post-schema procedural objects and actions
. exporting statistics
Export terminated successfully without warnings.

SQL>







5 comments:

priyadarshani said...

hi,

I have also encountered same problem today
I read your block
and tried your solution
I did recovery with

recover database using backup controlfile until cancel;

and applied redologs

and database opened with resetlogs

it worked

thank you so much

regards
priyadarshani
Oracle DBA

priyadarshani said...

hi

I have also encountered same problem today.
Read your block which help me a lot
I did recovery using

recover database using backup controlfile until cancel;

applied redologs

alter database open resetlogs

it worked

thank you so much
regard
priyadarshani

priyadarshani said...

hi

thanks a lot

your solution was very useful for me

regards
priyadarshani

Anonymous said...

Thanks...

Useful solution..

working....

Thanks a lot

halimdba said...

It's my pleasure. thanks for feedback.

Regards
Halim