После сбоя создал новую базу, импортом залил в новую базу дамп.
При нагрузке порядка 60 пользователей СМ 2000, иногда возникает ошибка ORA-00060.
Сервер:
Память 4 Гб
4 процессора Xeon 3.66 по 2 ядра, 32бита
база лежит на 4 скази дисках не в рейде.
ОС Windows 2003
oracle 9
параметры запуска базы:
Код:
O7_DICTIONARY_ACCESSIBILITY=TRUE
*.background_dump_dest='e:\ORACLE\admin\LAKMINS\bdump'
compatible='9.2.0.0.0'
*.control_files='e:\ORACLE\oradata\LAKMINS\control01.ctl','e:\ORACLE\oradata\LAKMINS\control02.ctl','e:\ORACLE\oradata\LAKMINS\control03.ctl'
*.core_dump_dest='e:\ORACLE\admin\LAKMINS\cdump'
cursor_sharing=force
optimizer_index_cost_adj=10
db_block_size=8192
db_block_buffers=262144
##db_block_buffers=131072
use_indirect_data_buffers=true
db_domain=''
db_file_multiblock_read_count=64
db_writer_processes=4
db_name='LAKMINS'
fast_start_mttr_target=300
hash_join_enabled=FALSE
instance_name='LAKMINS'
java_pool_size=16777216
large_pool_size=0
log_buffer=20971520
pga_aggregate_target=201326592
workarea_size_policy=auto
open_cursors=1000
processes=800
query_rewrite_enabled=true
query_rewrite_integrity=trusted
remote_login_passwordfile='EXCLUSIVE'
##shared_pool_size=402653184
shared_pool_size=201326592
star_transformation_enabled='FALSE'
timed_statistics=TRUE
undo_management='AUTO'
undo_retention=10800
undo_tablespace='UNDOTBS1'
*.user_dump_dest='e:\ORACLE\admin\LAKMINS\udump'
## PARALLEL ##
#CPU_COUNT=8
#PARALLEL_AUTOMATIC_TUNING=TRUE
#parallel_min_servers=16
#parallel_max_servers=128
#parallel_threads_per_cpu=4
parallel_max_servers=0
job_queue_processes=10
resource_limit=true
Трейс:
Код:
Thu Apr 22 09:43:39 2010
ORACLE V9.2.0.8.0 - Production vsnsta=0
vsnsql=12 vsnxtr=3
Windows 2000 Version 5.2 Service Pack 2, CPU type 586
Oracle9i Enterprise Edition Release 9.2.0.8.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.8.0 - Production
Windows 2000 Version 5.2 Service Pack 2, CPU type 586
Instance name: lakmins
Redo thread mounted by this instance: 1
Oracle process number: 182
Windows thread id: 4608, image: ORACLE.EXE
*** 2010-04-22 09:43:39.359
*** SESSION ID:(185.1231) 2010-04-22 09:43:39.359
DEADLOCK DETECTED ( ORA-00060 )
The following deadlock is not an ORACLE error. It is a
deadlock due to user error in the design of an application
or from issuing incorrect ad-hoc SQL. The following
information may aid in determining the deadlock:
Deadlock graph:
---------Blocker(s)-------- ---------Waiter(s)---------
Resource Name process session holds waits process session holds waits
TX-000c000a-00000118 182 185 X 24 31 S
TX-0001001f-00002476 24 31 X 182 185 S
session 185: DID 0001-00B6-00000002 session 31: DID 0001-0018-000003E4
session 31: DID 0001-0018-000003E4 session 185: DID 0001-00B6-00000002
Rows waited on:
Session 31: obj - rowid = 00001B09 - AAABsJAADAAAAAAAAA
(dictionary objn - 6921, file - 3, block - 0, slot - 0)
Session 185: obj - rowid = 00002845 - AAAChFAAHAAAAE1AAA
(dictionary objn - 10309, file - 7, block - 309, slot - 0)
Information on the OTHER waiting sessions:
Session 31:
pid=24 serial=337 audsid=135588 user: 23/SUPERMAG
O/S info: user: user1, term: CC1, ospid: 3236:3988, machine: LAKMIN\CC1
program: C:\Documents and Settings\user1\? ?\?.lnk
application name: C:\Documents and Settings\user1\? ?\?, hash value=0
Current SQL Statement:
UPDATE SMDOCUMENTS SET DOCSTATE=:b1 WHERE DOCTYPE = :b2 AND ID = :b3 AND DOCSTATE = :b4
End of information on OTHER waiting sessions.
Current SQL statement for this session:
UPDATE SMDOCUMENTS SET DOCSTATE=:b1 WHERE DOCTYPE = :b2 AND ID = :b3 AND DOCSTATE = :b4
----- PL/SQL Call Stack -----
object line object
handle number name
887D6CD0 6510 package body SUPERMAG.DOCUMENTS
887D6CD0 6658 package body SUPERMAG.DOCUMENTS
89DC1DA8 12 procedure SUPERMAG.SMDOCSTATEACCSTOREOR
91A75A94 1 anonymous block
===================================================
PROCESS STATE
-------------
Process global information:
process: 81A4FA6C, call: 8A6D8628, xact: 86FC6E8C, curses: 81B590B4, usrses: 81B590B4
----------------------------------------
SO: 81A4FA6C, type: 2, owner: 00000000, flag: INIT/-/-/0x00
(process) Oracle pid=182, calls cur/top: 8A6D8628/8A6D85C4, flag: (0) -
int error: 0, call error: 0, sess error: 0, txn error 0
(post info) last post received: 0 0 50
last post received-location: kcbzww
last process to post me: 81a2913c 244 0
last post sent: 0 0 50
last post sent-location: kcbzww
last process posted by me: 81a29cf4 99 0
(latch info) wait_event=0 bits=0
Process Group: DEFAULT, pseudo proc: 81AE7510
O/S info: user: SYSTEM, term: PROLIANT, ospid: 4608
OSD pid info: Windows thread id: 4608, image: ORACLE.EXE
----------------------------------------
SO: 86851E70, type: 8, owner: 81A4FA6C, flag: INIT/-/-/0x00
(FOB) flags=2 fib ptr=86b9ab74 incno=0 pending i/o cnt=0
----------------------------------------
SO: 86851E04, type: 8, owner: 81A4FA6C, flag: INIT/-/-/0x00
(FOB) flags=2 fib ptr=86b9bb2c incno=0 pending i/o cnt=0
----------------------------------------
SO: 81B590B4, type: 4, owner: 81A4FA6C, flag: INIT/-/-/0x00
(session) trans: 86FC6E8C, creator: 81A4FA6C, flag: (8100041) USR/- BSY/-/-/-/-/-
DID: 0001-00B6-00000002, short-term DID: 0000-0000-00000000
txn branch: 00000000
oct: 0, prv: 0, sql: 88744F84, psql: 886547A4, user: 23/SUPERMAG
O/S info: user: lena, term: CCTOVAROVED3, ospid: 2820:748, machine: LAKMIN\CCTOVAROVED3
program: C:\Documents and Settings\lena\? ?\?.lnk
application name: C:\Documents and Settings\lena\? ?\?, hash value=0
last wait for 'enqueue' blocking sess=0x81afe354 seq=2064 wait_time=2999504
name|mode=54580004, id1=1001f, id2=2476
temporary object counter: 4
----------------------------------------
SO: 8B3620B8, type: 51, owner: 81B590B4, flag: INIT/-/-/0x00
LIBRARY OBJECT LOCK: lock=8b3620b8 handle=87c36a18 mode=N
call pin=00000000 session pin=8897AFD0
htl=8B3620F4[8895C904,8F82CF98] htb=8F82CF98
user=81b590b4 session=81b590b4 count=1 flags=[00] savepoint=19481
LIBRARY OBJECT HANDLE: handle=87c36a18
namespace=CRSR flags=RON/KGHP/PN0/[10010000]
kkkk-dddd-llll=0000-0041-0041 lock=N pin=0 latch#=7
lwt=87C36A30[87C36A30,87C36A30] ltm=87C36A38[87C36A38,87C36A38]
pwt=87C36A48[87C36A48,87C36A48] ptm=87C36AA0[87C36AA0,87C36AA0]
ref=87C36A20[87C36C54, 87C36C54] lnd=87C36AAC[87C36AAC,87C36AAC]
LIBRARY OBJECT: object=87c367a0
type=CRSR flags=EXS[0001] pflags= [00] status=VALD load=0
DEPENDENCIES: count=5 size=16
AUTHORIZATIONS: count=1 size=16 minimum entrysize=16
ACCESSES: count=1 size=16
TRANSLATIONS: count=1 size=16
DATA BLOCKS:
data# heap pointer status pins change
----- -------- -------- ------ ---- ------
0 87c369a8 87c35d40 I/P/A 0 NONE
6 87c3683c 87c34aec I/-/A 0 NONE
----------------------------------------
SO: 83014640, type: 51, owner: 81B590B4, flag: INIT/-/-/0x00
LIBRARY OBJECT LOCK: lock=83014640 handle=87c3a50c mode=N
call pin=8D785A10 session pin=00000000
htl=8301467C[8F82D0C0,8F82D0C0] htb=8F82D0C0
user=81b590b4 session=81b590b4 count=1 flags=[00] savepoint=19480
LIBRARY OBJECT HANDLE: handle=87c3a50c
name=SELECT MIN(NVL(:b1,MACHINE)),MIN(NVL(:b2,OSUSER)) FROM SUPERMAG_SESSIONS S WHERE S.AUDSID = USERENV('SESSIONID')
hash=7ae2104d timestamp=04-21-2010 20:11:06
namespace=CRSR flags=RON/KGHP/TIM/PN0/MED/[50010000]
kkkk-dddd-llll=0000-0001-0001 lock=N pin=0 latch#=7
lwt=87C3A524[87C3A524,87C3A524] ltm=87C3A52C[87C3A52C,87C3A52C]
pwt=87C3A53C[87C3A53C,87C3A53C] ptm=87C3A594[87C3A594,87C3A594]
ref=87C3A514[87C3A514, 87C3A514] lnd=87C3A5A0[87BEBA4C,87C90938]
LIBRARY OBJECT: object=87c36d20
type=CRSR flags=EXS[0001] pflags= [00] status=VALD load=0
CHILDREN: size=16
child# table reference handle
------ -------- --------- --------
0 87c36ecc 87c36c54 87c36a18
DATA BLOCKS:
data# heap pointer status pins change
----- -------- -------- ------ ---- ------
0 87c36f28 87c36da8 I/P/A 0 NONE
----------------------------------------