database.sarang.net
UserID
Passwd
Database
DBMS
MySQL
PostgreSQL
Firebird
ㆍOracle
Informix
Sybase
MS-SQL
DB2
Cache
CUBRID
LDAP
ALTIBASE
Tibero
DB 문서들
스터디
Community
공지사항
자유게시판
구인|구직
DSN 갤러리
도움주신분들
Admin
운영게시판
최근게시물
Oracle Q&A 38952 게시물 읽기
No. 38952
오라클 db문제입니다. 도와주세요. ㅠㅠ
작성자
긴급도움요청
작성일
2011-10-11 21:18
조회수
13,084

db사용중 갑자기 tns오류뜨면서 로그인이 안되는 문제발생. 급한마음에 서버를 껐다 켰습니다.
다시 올라온후 15~20분사이에 한번씩 ora-12154가 뜨면서  db연결이 안됩니다. 밑은 로그입니다..
뭐가 문제일까요. 분석 좀 해주세요 ㅠㅠ

Instance terminated by PMON, pid = 5120
Tue Oct 11 20:30:34 2011
Adjusting the default value of parameter parallel_max_servers
from 640 to 235 due to the value of parameter processes (250)
Tue Oct 11 20:30:34 2011
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 =30
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                = 250
  sga_max_size             = 1048576000
  __shared_pool_size       = 205520896
  __large_pool_size        = 8388608
  __java_pool_size         = 4194304
  __streams_pool_size      = 0
  spfile                   = D:\ORACLE\PRODUCT\10.2.0\DBS\SPFILEZEUS.ORA
  sga_target               = 1048576000
  control_files            = D:\ORACLE\PRODUCT\ORADATA\ZEUS\CONTROL01.CTL, D:\ORACLE\PRODUCT\ORADATA\ZEUS\CONTROL02.CTL, D:\ORACLE\PRODUCT\ORADATA\ZEUS\CONTROL03.CTL
  db_block_size            = 8192
  __db_cache_size          = 817889280
  compatible               = 10.2.0.1.0
  db_file_multiblock_read_count= 16
  cluster_database         = FALSE
  db_recovery_file_dest    = D:\oracle\product/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=ZEUSXDB)
  job_queue_processes      = 10
  audit_file_dest          = D:\ORACLE\PRODUCT\ADMIN\ZEUS\ADUMP
  background_dump_dest     = D:\ORACLE\PRODUCT\ADMIN\ZEUS\BDUMP
  user_dump_dest           = D:\ORACLE\PRODUCT\ADMIN\ZEUS\UDUMP
  core_dump_dest           = D:\ORACLE\PRODUCT\ADMIN\ZEUS\CDUMP
  db_name                  = ZEUS
  open_cursors             = 500
  pga_aggregate_target     = 203423744
PMON started with pid=2, OS id=5140
PSP0 started with pid=4, OS id=6104
MMAN started with pid=6, OS id=4564
DBW0 started with pid=8, OS id=5496
DBW1 started with pid=10, OS id=5968
DBW2 started with pid=12, OS id=2324
DBW3 started with pid=14, OS id=908
LGWR started with pid=16, OS id=5532
CKPT started with pid=18, OS id=5708
SMON started with pid=20, OS id=3364
RECO started with pid=22, OS id=4384
CJQ0 started with pid=24, OS id=2832
MMON started with pid=26, OS id=4784
Tue Oct 11 20:30:35 2011
starting up 1 dispatcher(s) for network address '(ADDRESS=(PARTIAL=YES)(PROTOCOL=TCP))'...
MMNL started with pid=28, OS id=3208
Tue Oct 11 20:30:35 2011
starting up 1 shared server(s) ...
Tue Oct 11 20:30:35 2011
ALTER DATABASE   MOUNT
Tue Oct 11 20:30:40 2011
Setting recovery target incarnation to 2
Tue Oct 11 20:30:40 2011
Successful mount of redo thread 1, with mount id 309190315
Tue Oct 11 20:30:40 2011
Database mounted in Exclusive Mode
Completed: ALTER DATABASE   MOUNT
Tue Oct 11 20:30:40 2011
ALTER DATABASE OPEN
Tue Oct 11 20:30:40 2011
Beginning crash recovery of 1 threads
 parallel recovery started with 16 processes
Tue Oct 11 20:30:40 2011
Started redo scan
Tue Oct 11 20:30:40 2011
Completed redo scan
 3625 redo blocks read, 751 data blocks need recovery
Tue Oct 11 20:30:41 2011
Started redo application at
 Thread 1: logseq 78856, block 11117
Tue Oct 11 20:30:41 2011
Recovery of Online Redo Log: Thread 1 Group 3 Seq 78856 Reading mem 0
  Mem# 0: D:\ORACLE\PRODUCT\ORADATA\ZEUS\REDO03.LOG
  Mem# 1: D:\ORACLE\PRODUCT\ORADATA\ZEUS\REDO06.LOG
Tue Oct 11 20:30:41 2011
Completed redo application
Tue Oct 11 20:30:41 2011
Completed crash recovery at
 Thread 1: logseq 78856, block 14742, scn 3553014024
 751 data blocks read, 749 data blocks written, 3625 redo blocks read
Tue Oct 11 20:30:41 2011
Thread 1 advanced to log sequence 78857
Thread 1 opened at log sequence 78857
  Current log# 1 seq# 78857 mem# 0: D:\ORACLE\PRODUCT\ORADATA\ZEUS\REDO01.LOG
  Current log# 1 seq# 78857 mem# 1: D:\ORACLE\PRODUCT\ORADATA\ZEUS\REDO04.LOG
Successful open of redo thread 1
Tue Oct 11 20:30:41 2011
MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
Tue Oct 11 20:30:41 2011
SMON: enabling cache recovery
Tue Oct 11 20:30:42 2011
Successfully onlined Undo Tablespace 1.
Tue Oct 11 20:30:42 2011
SMON: enabling tx recovery
Tue Oct 11 20:30:42 2011
Database Characterset is KO16MSWIN949
Tue Oct 11 20:30:43 2011
Errors in file d:\oracle\product\admin\zeus\bdump\zeus_p050_2412.trc:
ORA-07445: exception encountered: core dump [ACCESS_VIOLATION] [_kturblguba+231] [PC:0x95D0D3] [ADDR:0x14] [UNABLE_TO_READ] []

replication_dependency_tracking turned off (no async multimaster replication found)
Starting background process QMNC
QMNC started with pid=164, OS id=5896
Tue Oct 11 20:30:47 2011
db_recovery_file_dest_size of 2048 MB is 0.00% used. This is a
user-specified limit on the amount of space that will be used by this
database for recovery-related files, and does not reflect the amount of
space available in the underlying filesystem or ASM diskgroup.
Tue Oct 11 20:30:47 2011
Completed: ALTER DATABASE OPEN
Tue Oct 11 20:31:27 2011
SMON: slave died unexpectedly, downgrading to serial recovery
Tue Oct 11 20:31:30 2011
Errors in file d:\oracle\product\admin\zeus\bdump\zeus_smon_3364.trc:
ORA-00600: internal error code, arguments: [4137], [], [], [], [], [], [], []

ORACLE Instance zeus (pid = 20) - Error 600 encountered while recovering transaction (2, 44).
Tue Oct 11 20:31:30 2011
Errors in file d:\oracle\product\admin\zeus\bdump\zeus_smon_3364.trc:
ORA-00600: internal error code, arguments: [4137], [], [], [], [], [], [], []
 
이 글에 대한 댓글이 총 4건 있습니다.

현 상태를 export 등으로 빨리 백업을 받아놓아야 할거 같네요

디비를 재구성하셔야 할 거 같습니다. 백업이 잘 받아지길 바랍니다.

경력맨님이 2011-10-11 21:34에 작성한 댓글입니다. Edit Ｘ

OS나 기타 제반여건(Oracle Version, RAC 구성여부 등)을 모르는 상황에서 alert log만을 보고 판단하기에는 어려움이 있고, 두리뭉실하게 밖에 답을 드리기 어렵겠네요. 

일단 로그상으로는 smon이 비정상적으로 종료되는거 같은데요.

메모리나 디스크 등의 하드웨어 fail 측면에서 접근해보시기 바랍니다(OS의 errorlog/syslog 등 확인 필요).

aler log에 표기된 대로 d:\oracle\product\admin\zeus\bdump\zeus_p050_2412.trc 파일에 자세한 내용이 있을듯 하니 참조하시면 정보가 있을거 같구요. 아마 문제해결의 주요힌트가 저 파일에 되어 있을 겁니다.

해당 trace 파일을 참조하시면 도움을 얻으실 수 있을 겁니다.

viatoris님이 2011-10-12 09:05에 작성한 댓글입니다.
이 댓글은 2011-10-12 09:06에 마지막으로 수정되었습니다. Edit Ｘ

윗분말씀처럼 log만으로는 섣부른 판단을 하긴 어렵지만...

아마도 10.2.0.3에서 발생하는 Bug인것 같습니다.

(동일한 에러가 발생하지 않도록 패치셋을 적용하는게 좋을듯요...)

DB 시작후 export  받고 깔끔하게 DB를 재구성하시거나

Undo corruption이 발생하였으므로 UNDOTBS를 재생성하시는 방법도 있겠습니다.

 

 

Bug 8240762  Undo corruptions with ORA-600 [4193]/ORA-600 [4194] or ORA-600 [4137]

 

 This note gives a brief overview bug 8240762. 
 The content was last updated on: 18-NOV-2010
 Click here for details of each of the sections below.

Affects:

Product (Component) Oracle Server (Rdbms)
Range of versions believed to be affected Versions >= 10.2 but BELOW 11.2
Versions confirmed as being affected
Platforms affected Generic (all / most platforms affected)

Fixed:

This issue is fixed in

 

 

 

Symptoms:

Related To:

Description

Undo corruption may be caused after a shrink and the same undo block may be used
for two different transactions causing several internal errors like:
 
 ORA-600 [4193] / ORA-600 [4194] for new transactions
 ORA-600 [4137] for a transaction rollback
 
Undo segment shrink is internally done by Oracle.
 
Workaround
 Drop the undo segment.

 

Please note: The above is a summary description only. Actual symptoms can vary. Matching to any symptoms here does not confirm that you are encountering this problem. Always consult with Oracle Support for advice.

 

References

Bug:8240762 (This link will only work for PUBLISHED bugs)
Note:245840.1 Information on the sections in this article

 

 

,님이 2011-10-12 10:20에 작성한 댓글입니다.
이 댓글은 2011-10-12 10:21에 마지막으로 수정되었습니다. Edit Ｘ

답변달아주신 분들 감사합니다.

새벽에야 겨우 작업 마쳤네요.

버그패치하고  UNDOTBS를 재생성하는걸로 마무리 지었습니다.

 

긴급도움요청님이 2011-10-12 11:40에 작성한 댓글입니다. Edit Ｘ
[Top]
No.
제목
작성자
작성일
조회
38957[질문]쿼리시 order by 를 어떻게 하나요? [2]
최병련
2011-10-12
4414
38956쿼리 튜닝좀 부탁드립니다. [1]
박주영
2011-10-12
4714
38953ORA-24315 illegal attribute type
이동희
2011-10-12
4701
38952오라클 db문제입니다. 도와주세요. ㅠㅠ [4]
긴급도움요청
2011-10-11
13084
38951로또 쿼리질문 (통계표?) [4]
그렇구나~
2011-10-11
7085
38950group by 와 distinct의 차이점?? [1]
궁금
2011-10-11
5262
38949오라클 10g EM (최상위 작업에서 SQL문 텍스트 풀쿼리 조회 방법) [2]
라클리
2011-10-10
4054
Valid XHTML 1.0!
All about the DATABASE... Copyleft 1999-2024 DSN, All rights reserved.
작업시간: 0.027초, 이곳 서비스는 PostgreSQL v16.2로 자료를 관리합니다