LOCK ON SYS.SMON_SCN_TIME [ID 747745.1]
来源:互联网 发布:淘宝电线类目运营策划 编辑:程序博客网 时间:2024/06/06 09:29
LOCK ON SYS.SMON_SCN_TIME [ID 747745.1]
Modified 27-NOV-2008 Type PROBLEM Status MODERATED
In this Document
Symptoms
Cause
Solution
This document is being delivered to you via Oracle Support's Rapid Visibility (RaV) process, and therefore has not been subject to an independent technical review.
Applies to:
Oracle Server - Enterprise Edition - Version: 10.1.0.5 to 10.2.0.4
This problem can occur on any platform.
Symptoms
As soon as customer starts up the database, a lock is put on
SYS.SMON_SCN_TIME by SMON and it never go away.
Database Performance becomes slow.
SMON_SCN_TIME has huge no.of records.
SQL> select count(*) from sys.smon_scn_time;
COUNT(*)
----------
137545
1 row selected.
It is found that the object has been locked.
SQL> select object_id from dba_objects where object_name = 'SMON_SCN_TIME';
OBJECT_ID
----------
575
1 row selected.
SQL> select * from v$locked_object where object_id = 575;
XIDUSN XIDSLOT XIDSQN OBJECT_ID SESSION_ID
---------- ---------- ---------- ---------- ----------
ORACLE_USERNAME OS_USER_NAME PROCESS
------------------------------ ------------------------------ ------------
LOCKED_MODE
-----------
5 5 1494 575 164
dbadmin 4444350
3 <= Locked in row exclusive mode
Cause
From the systemstate dump, it is seen that SMON process is doing some delete operation on that table.
Systemstate dump
~~~~~~~~~~~~~~~~~
PROCESS 8:
----------------------------------------
SO: 70000001fe572b0, type: 2, owner: 0, flag: INIT/-/-/0x00
(process) Oracle pid=8, calls cur/top: 70000001ff98ea0/70000001ff95f68, flag: (16) SYSTEM
int error: 0, call error: 0, sess error: 0, txn error 0
(post info) last post received: 0 0 112
last post received-location: kcbzww
last process to post me: 70000001fe59230 2 0
last post sent: 0 0 112
last post sent-location: kcbzww
last process posted by me: 70000001fe59230 2 0
(latch info) wait_event=0 bits=0
Process Group: DEFAULT, pseudo proc: 70000001fe9dd18
O/S info: user: dbadmin, term: UNKNOWN, ospid: 3367182
OSD pid info: Unix process pid: 3367182, image: oracle@dwic501 (SMON)
Dump of memory from 0x070000001FE41340 to 0x070000001FE41548
....
....
LIBRARY OBJECT HANDLE: handle=70000001fa60b38 mtx=70000001fa60c68(1) cdp=1
name=delete from smon_scn_time where thread=0 and scn = (select min(scn) from smon_scn_time
where thread=0)
What happens here is due to the inconsistency between the table and indexes. The delete returns
zero rows; so the delete is executed continuously to reduce the smon_scn_time below the maximum
mappings.
Because of this the database performance could become slow especially the gather_stats_job or any statistics collection.
Solution
To delete the records from SMON_SCN_TIME manually.
Setting the 12500 event at system level should stop SMON from updating the SMON_SCN_TIME table.
This should allow you to check the content of the table (count(*) for number of rows etc, analyze
validate to confirm if it is corrupt or not, plus check the actual row content in case there are any
timestamps in the
table in the future).
The content of this table just maintains a rough mapping between timestamps and SCN values
so if there are excess rows or rows in the future then you can delete rows from the table manually
to get back to a sensible start point.
The SMON time mapping is mainly for flashback type queries to map a time to an SCN so it is probably
simplest to copy the content to a holding table then delete ALL rows, then recycle the instance.
SMON should start to populate the table with new time / SCN pairs from the time that the instance
is started
SQL> conn / as sysdba
/* Set the event at system level */
SQL> alter system set events '12500 trace name context forever, level 10';
/* Delete the records from SMON_SCN_TIME */
SQL> delete from smon_scn_time;
SQL> commit;
SQL> alter system set events '12500 trace name context off';
Now restart the instance.
------------------------------------------------------------------------------
Blog: http://blog.csdn.net/tianlesoftware
网上资源: http://tianlesoftware.download.csdn.net
相关视频:http://blog.csdn.net/tianlesoftware/archive/2009/11/27/4886500.aspx
DBA1 群:62697716(满); DBA2 群:62697977(满)
DBA3 群:62697850 DBA 超级群:63306533;
聊天 群:40132017
--加群需要在备注说明Oracle表空间和数据文件的关系,否则拒绝申请
- LOCK ON SYS.SMON_SCN_TIME [ID 747745.1]
- Hi3516 sys id 确认
- Transaction (Process ID ) was deadlocked on lock resources with another process and has been chosen
- Transaction (Process ID) was deadlocked on lock resources with another process and has been chose
- Dataguard Error 1017 ORA-16191 sys lock
- 关于对象SMON_SCN_TIME
- Oracle smon_scn_time 表 说明
- Oracle smon_scn_time 表 说明
- Choosing What To Lock On
- finding lock details on database
- Implement Lock files on Windows
- Android Pattern Lock on iPhone
- ubuntu lock and rely on
- sys:treeselect 树型框 获取制定ID
- can't create transaction lock on /var/lock/rpm/transaction
- 重建smon_scn_time表和索引
- Set-group-id on directory
- Failed to get lock on destination repos
- HTML Help Workshop 工程文件格式解析
- Hibernate学习笔记(一)--------针对不同的数据库不同的配置
- Struts2+Spring+Hibernate学习总结——XML才是王道
- Wayos计费系统——EasyRadius
- 锁IE主页的插件 锁IE 具防删除 防覆盖 过杀毒
- LOCK ON SYS.SMON_SCN_TIME [ID 747745.1]
- How to map SCN with Timestamp before 10g [ID 365536.1]
- 关于读取大文本文件
- hdoj 2042 不容易系列之二 解题报告 (老汉牵羊过收费站问题)(超水递推)
- QML学习(2)
- zoj 1935 || poj 1932 XYZZY(SPFA+Floyd)
- 文件虚拟偏移地址和物理偏移地址的转换
- 全套外挂制作教程
- MongoDB 基本操作语法