DB auto shutdown after sometime.....

[Former Member]

Hi all,

we have ecc6 sr3 , orcle 10.2 0 2 0 with win server 2003. We have problem in our DB that the DB is auto shutdown after sometimg., error in syslog below

===================

SAP-Basis System: Work process has left reconnect status

Database: SQL error 1092 . Work processes in reconnect status

===================

Both the error is continously comming.........

aftab

[Former Member]

This message was moderated.

[Former Member]

Hi,

Check u r alertSID.log file for more detail information regarding DB shutdown problem.

Give the logs from this file.

Thnaks

R.

[Former Member]

========================================

Instance terminated by PMON, pid = 4604

Wed Nov 04 04:32:04 2009

Adjusting the default value of parameter parallel_max_servers

from 160 to 65 due to the value of parameter processes (80)

Wed Nov 04 04:32:04 2009

Starting ORACLE instance (normal)

Wed Nov 04 04:32:04 2009

Specified value of sga_max_size is too small, bumping to 3942645760

LICENSE_MAX_SESSION = 0

LICENSE_SESSIONS_WARNING = 0

Picked latch-free SCN scheme 3

Autotune of undo retention is turned on.

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.2.0.

System parameters with non-default values:

processes = 80

sessions = 96

event = 10191 trace name context forever, level 1

sga_max_size = 3942645760

shared_pool_size = 1946157056

shared_pool_reserved_size= 193226342

filesystemio_options = setall

control_files = E:\ORACLE\PRD\ORIGLOGA\CNTRL\CNTRLPRD.DBF, E:\ORACLE\PRD\ORIGLOGB\CNTRL\CNTRLPRD.DBF, G:\ORACLE\PRD\SAPDATA1\CNTRL\CNTRLPRD.DBF

control_file_record_keep_time= 30

db_block_size = 8192

db_cache_size = 1946157056

compatible = 10.2.0

log_archive_dest = F:\oracle\PRD\oraarch\PRDarch

log_buffer = 14270464

log_checkpoint_interval = 0

db_files = 254

log_checkpoints_to_alert = TRUE

dml_locks = 4000

replication_dependency_tracking= FALSE

undo_management = AUTO

undo_tablespace = PSAPUNDO

inmemory_undo = FALSE

undo_retention = 43200

recyclebin = off

remote_os_authent = TRUE

remote_login_passwordfile= EXCLUSIVE

job_queue_processes = 1

parallel_execution_message_size= 16384

tablelookup_prefetch_size= 0

background_dump_dest = G:\ORACLE\PRD\SAPTRACE\BACKGROUND

user_dump_dest = G:\ORACLE\PRD\SAPTRACE\USERTRACE

core_dump_dest = G:\ORACLE\PRD\SAPTRACE\BACKGROUND

sort_area_size = 2097152

sort_area_retained_size = 0

db_name = PRD

open_cursors = 800

sortelimination_cost_ratio= 10

btree_bitmap_plans = FALSE

star_transformation_enabled= true

query_rewrite_enabled = false

indexjoin_enabled = FALSE

optimpeek_user_binds = FALSE

pga_aggregate_target = 629145600

optimizermjc_enabled = FALSE

PMON started with pid=2, OS id=4756

PSP0 started with pid=3, OS id=6684

MMAN started with pid=4, OS id=2036

DBW0 started with pid=5, OS id=6492

LGWR started with pid=6, OS id=5756

CKPT started with pid=7, OS id=4304

SMON started with pid=8, OS id=3224

RECO started with pid=9, OS id=5868

CJQ0 started with pid=10, OS id=1128

MMON started with pid=11, OS id=6112

MMNL started with pid=12, OS id=6372

======================================

ed Nov 04 13:25:50 2009

Shutting down archive processes

Wed Nov 04 13:25:55 2009

ARCH shutting down

ARC2: Archival stopped

Wed Nov 04 13:31:10 2009

SMON: Restarting fast_start parallel rollback

Wed Nov 04 13:31:10 2009

Errors in file g:\oracle\prd\saptrace\background\prd_p000_5864.trc:

ORA-00600: internal error code, arguments: [4198], [9], [], [], [], [], [], []

Wed Nov 04 13:31:10 2009

Doing block recovery for file 2 block 9

Block recovery from logseq 109, block 433 to scn 102971680

Wed Nov 04 13:31:10 2009

Recovery of Online Redo Log: Thread 1 Group 4 Seq 109 Reading mem 0

Mem# 0 errs 0: E:\ORACLE\PRD\ORIGLOGB\LOG_G14M1.DBF

Mem# 1 errs 0: E:\ORACLE\PRD\MIRRLOGB\LOG_G14M2.DBF

Block recovery stopped at EOT rba 109.435.16

Block recovery completed at rba 109.435.16, scn 0.102971679

Doing block recovery for file 12 block 176257

Block recovery from logseq 109, block 433 to scn 102971678

Wed Nov 04 13:31:11 2009

Recovery of Online Redo Log: Thread 1 Group 4 Seq 109 Reading mem 0

Mem# 0 errs 0: E:\ORACLE\PRD\ORIGLOGB\LOG_G14M1.DBF

Mem# 1 errs 0: E:\ORACLE\PRD\MIRRLOGB\LOG_G14M2.DBF

Block recovery completed at rba 109.435.16, scn 0.102971679

Wed Nov 04 13:31:11 2009

SMON: ignoring slave err,downgrading to serial rollback

Wed Nov 04 13:31:11 2009

Errors in file g:\oracle\prd\saptrace\background\prd_smon_6120.trc:

ORA-00600: internal error code, arguments: [4137], [], [], [], [], [], [], []

ORACLE Instance prd (pid = 😎 - Error 600 encountered while recovering transaction (1, 24).

Wed Nov 04 13:31:11 2009

Errors in file g:\oracle\prd\saptrace\background\prd_smon_6120.trc:

ORA-00600: internal error code, arguments: [4137], [], [], [], [], [], [], []

Wed Nov 04 13:36:14 2009

SMON: Restarting fast_start parallel rollback

Wed Nov 04 13:36:14 2009

Errors in file g:\oracle\prd\saptrace\background\prd_p000_5484.trc:

ORA-00600: internal error code, arguments: [4198], [9], [], [], [], [], [], []

Wed Nov 04 13:36:14 2009

Doing block recovery for file 2 block 9

Block recovery from logseq 109, block 433 to scn 102979189

Wed Nov 04 13:36:14 2009

Recovery of Online Redo Log: Thread 1 Group 4 Seq 109 Reading mem 0

Mem# 0 errs 0: E:\ORACLE\PRD\ORIGLOGB\LOG_G14M1.DBF

Mem# 1 errs 0: E:\ORACLE\PRD\MIRRLOGB\LOG_G14M2.DBF

Block recovery completed at rba 109.12429.16, scn 0.102979202

Doing block recovery for file 12 block 176257

Block recovery from logseq 109, block 433 to scn 102979220

Wed Nov 04 13:36:14 2009

Recovery of Online Redo Log: Thread 1 Group 4 Seq 109 Reading mem 0

Mem# 0 errs 0: E:\ORACLE\PRD\ORIGLOGB\LOG_G14M1.DBF

Mem# 1 errs 0: E:\ORACLE\PRD\MIRRLOGB\LOG_G14M2.DBF

Block recovery completed at rba 109.12441.16, scn 0.102979221

Wed Nov 04 13:36:14 2009

SMON: ignoring slave err,downgrading to serial rollback

Wed Nov 04 13:36:15 2009

Errors in file g:\oracle\prd\saptrace\background\prd_smon_6120.trc:

ORA-00600: internal error code, arguments: [4137], [], [], [], [], [], [], []

ORACLE Instance prd (pid = 😎 - Error 600 encountered while recovering transaction (1, 24).

Wed Nov 04 13:36:15 2009

Errors in file g:\oracle\prd\saptrace\background\prd_smon_6120.trc:

ORA-00600: internal error code, arguments: [4137], [], [], [], [], [], [], []

Wed Nov 04 13:41:17 2009

SMON: Restarting fast_start parallel rollback

Wed Nov 04 13:41:17 2009

Errors in file g:\oracle\prd\saptrace\background\prd_p000_5108.trc:

ORA-00600: internal error code, arguments: [4198], [9], [], [], [], [], [], []

Wed Nov 04 13:41:18 2009

Doing block recovery for file 2 block 9

Block recovery from logseq 109, block 12698 to scn 102979415

Wed Nov 04 13:41:18 2009

Recovery of Online Redo Log: Thread 1 Group 4 Seq 109 Reading mem 0

Mem# 0 errs 0: E:\ORACLE\PRD\ORIGLOGB\LOG_G14M1.DBF

Mem# 1 errs 0: E:\ORACLE\PRD\MIRRLOGB\LOG_G14M2.DBF

Block recovery stopped at EOT rba 109.12700.16

Block recovery completed at rba 109.12700.16, scn 0.102979414

Doing block recovery for file 12 block 176257

Block recovery from logseq 109, block 12698 to scn 102979413

Wed Nov 04 13:41:18 2009

Recovery of Online Redo Log: Thread 1 Group 4 Seq 109 Reading mem 0

Mem# 0 errs 0: E:\ORACLE\PRD\ORIGLOGB\LOG_G14M1.DBF

Mem# 1 errs 0: E:\ORACLE\PRD\MIRRLOGB\LOG_G14M2.DBF

Block recovery completed at rba 109.12700.16, scn 0.102979414

Wed Nov 04 13:41:18 2009

SMON: ignoring slave err,downgrading to serial rollback

Wed Nov 04 13:41:18 2009

Errors in file g:\oracle\prd\saptrace\background\prd_smon_6120.trc:

ORA-00600: internal error code, arguments: [4137], [], [], [], [], [], [], []

ORACLE Instance prd (pid = 😎 - Error 600 encountered while recovering transaction (1, 24).

Wed Nov 04 13:41:18 2009

Errors in file g:\oracle\prd\saptrace\background\prd_smon_6120.trc:

ORA-00600: internal error code, arguments: [4137], [], [], [], [], [], [], []

Wed Nov 04 13:44:59 2009

Adjusting the default value of parameter parallel_max_servers

from 160 to 65 due to the value of parameter processes (80)

Wed Nov 04 13:44:59 2009

Starting ORACLE instance (normal)

Wed Nov 04 13:46:21 2009

SMON: Restarting fast_start parallel rollback

Wed Nov 04 13:46:21 2009

Errors in file g:\oracle\prd\saptrace\background\prd_p000_2416.trc:

ORA-00600: internal error code, arguments: [4198], [9], [], [], [], [], [], []

Wed Nov 04 13:46:21 2009

Doing block recovery for file 2 block 9

Block recovery from logseq 109, block 13785 to scn 102980318

Wed Nov 04 13:46:21 2009

Recovery of Online Redo Log: Thread 1 Group 4 Seq 109 Reading mem 0

Mem# 0 errs 0: E:\ORACLE\PRD\ORIGLOGB\LOG_G14M1.DBF

Mem# 1 errs 0: E:\ORACLE\PRD\MIRRLOGB\LOG_G14M2.DBF

Block recovery stopped at EOT rba 109.13793.16

Block recovery completed at rba 109.13793.16, scn 0.102980317

Doing block recovery for file 12 block 176257

Block recovery from logseq 109, block 13785 to scn 102980315

Wed Nov 04 13:46:21 2009

Recovery of Online Redo Log: Thread 1 Group 4 Seq 109 Reading mem 0

Mem# 0 errs 0: E:\ORACLE\PRD\ORIGLOGB\LOG_G14M1.DBF

Mem# 1 errs 0: E:\ORACLE\PRD\MIRRLOGB\LOG_G14M2.DBF

Block recovery completed at rba 109.13785.16, scn 0.102980316

Wed Nov 04 13:46:21 2009

SMON: ignoring slave err,downgrading to serial rollback

Wed Nov 04 13:46:22 2009

Errors in file g:\oracle\prd\saptrace\background\prd_smon_6120.trc:

ORA-00600: internal error code, arguments: [4137], [], [], [], [], [], [], []

Wed Nov 04 13:46:24 2009

Errors in file g:\oracle\prd\saptrace\background\prd_pmon_3396.trc:

ORA-00474: SMON process terminated with error

Wed Nov 04 13:46:24 2009

PMON: terminating instance due to error 474

Wed Nov 04 13:46:25 2009

Errors in file g:\oracle\prd\saptrace\background\prd_ckpt_6608.trc:

ORA-00474: SMON process terminated with error

Wed Nov 04 13:46:26 2009

Errors in file g:\oracle\prd\saptrace\background\prd_psp0_5212.trc:

ORA-00474: SMON process terminated with error

Wed Nov 04 13:46:26 2009

Errors in file g:\oracle\prd\saptrace\background\prd_mman_1040.trc:

ORA-00474: SMON process terminated with error

Wed Nov 04 13:46:26 2009

Errors in file g:\oracle\prd\saptrace\background\prd_dbw0_6424.trc:

ORA-00474: SMON process terminated with error

Wed Nov 04 13:46:26 2009

Errors in file g:\oracle\prd\saptrace\background\prd_q001_3380.trc:

ORA-00474: SMON process terminated with error

Wed Nov 04 13:46:27 2009

Errors in file g:\oracle\prd\saptrace\background\prd_lgwr_1188.trc:

ORA-00474: SMON process terminated with error

Wed Nov 04 13:46:33 2009

Errors in file g:\oracle\prd\saptrace\background\prd_reco_6072.trc:

ORA-00474: SMON process terminated with error

Wed Nov 04 13:46:34 2009

Instance terminated by PMON, pid = 3396

=========================================

Edited by: Aftab Mansoor on Nov 4, 2009 9:56 AM

[anindya_bose]

Increase sga_max_size, processes and sessions

Restart Database

Check process and sessions according to https://service.sap.com/sap/support/notes/830576

https://service.sap.com/sap/support/notes/789011

Edited by: Anindya Bose on Nov 4, 2009 3:06 PM

[Former Member]

Hi,

Please check the log file g:\oracle\prd\saptrace\background\prd_smon_6120.trc

Mostly this is looks like due to corrupt undo information which is preventing the rollback.

I would have open an ticket with OSS.

Thanks,

Shambo

[Former Member]

Hi,

See the Metalink Doc ID 43914.1 ORA-600 [4137] "XID in Undo and Redo Does Not Match.

Hope it will help you.

Thanks,

Shambo

[Former Member]

thanks shambo,

we have not matalink ID , can u plz told us how we can correct the XID in redo and undo

plz guide

regards

[Former Member]

Hi,

SAP have provided the metalink id to their customers. Please see OSS note.

758563 Oracle Metalink access for SAP customers

Thanks,

Shambo

[Former Member]

Aftab,

You got to check the prd_smon_nnnn.trc files for exact reason or the corrupt file.

Additionally, to check if a roolbak segment is corrup query dba_rollback_segs for status. If rollback needs recovery - then check Note 4190 - Recovery for defective rollback segments

Note 541695 - gives you some insight on the problem.

Looks like one of the file or segment needs recovery.

Madhu