alert日志频繁报错
[quote]Mon Jun 29 16:00:06 2009
Memory Notification: Library Cache Object loaded into SGA
Heap size 4365K exceeds notification threshold (2048K)
KGL object name :SYS.ALERT_QUE [/quote]
其相关trc文件显示
[quote]Dump file g:\oracle\product\10.2.0\admin\dota\udump\dota_ora_65148.trc
Mon Jun 29 15:59:36 2009
ORACLE V10.2.0.1.0 - Production vsnsta=0
vsnsql=14 vsnxtr=3
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
Windows XP Version V5.1 Service Pack 3, v.5657
CPU : 2 - type 586, 2 Physical Cores
Process Affinity : 0x00000000
Memory (Avail/Total): Ph:271M/2046M, Ph+PgF:1598M/3932M, VA:1270M/2047M
Instance name: dota
Redo thread mounted by this instance: 1
Oracle process number: 19
Windows thread id: 65148, image: ORACLE.EXE (SHAD)
*** SERVICE NAME:(SYS$USERS) 2009-06-29 15:59:36.000
*** SESSION ID:(138.6167) 2009-06-29 15:59:36.000
Memory Notification: Library Cache Object loaded into SGA
Heap size 4358K exceeds notification threshold (2048K)
LIBRARY OBJECT HANDLE: handle=33ccb7cc mutex=33CCB880(0)
name=SYS.ALERT_QUE
hash=ac8b6e1e4ca94b6f517f04aaaa810f64 timestamp=08-30-2005 14:11:40
namespace=QUEU flags=KGHP/TIM/SML/[02000000]
kkkk-dddd-llll=0000-0000-0000 lock=N pin=S latch#=1 hpc=0a98 hlc=0a98
lwt=33CCB828[33CCB828,33CCB828] ltm=33CCB830[33CCB830,33CCB830]
pwt=33CCB80C[33CCB80C,33CCB80C] ptm=33CCB814[33CCB814,33CCB814]
ref=33CCB848[33CCB848,33CCB848] lnd=33CCB854[33CCB854,33CCB854]
LIBRARY OBJECT: object=2f75a8a4
type=QUEU flags=EXS/LOC[0005] pflags=[0000] status=VALD load=X
DEPENDENCIES: count=4 size=16
dependency# table reference handle position flags
----------- -------- --------- -------- -------- -------------------
0 2f75ac60 2f6505f4 33d584fc 0 DEP[01]
1 2f75ac60 2f650628 33d583a4 0 DEP[01]
2 2f75ac60 2f65065c 33d5824c 0 DEP[01]
3 2f75ac60 2f650690 33e5d438 0 DEP[01]
DATA BLOCKS:
data# heap pointer status pins change whr alloc(K) size(K)
----- -------- -------- --------- ---- ------ --- -------- --------
0 28227d50 2f75a93c I/P/A/-/- 0 NONE 00 4358.61 0.00[/quote]
ALERT_QUE表格主要用于dbconsole或者gird control监视系统警告信息所用,根据alert日志提示,其堆栈大小超过了系统设定值,解决办法如下
[quote]SQL> connect / as sysdba
SQL>alter system enable restricted session;
To drop server alert schema.
SQL>@$ORACLE_HOME/rdbms/admin/catnoalr.sql
To re-create tables, sequence, type and queue for server alert
SQL>@$ORACLE_HOME/rdbms/admin/dbmsslrt.sql
SQL>@$ORACLE_HOME/rdbms/admin/catalrt.sql
To recompile the invalid objects
SQL>@$ORACLE_HOME/rdbms/admin/utlrp.sql
SQL> alter system disable restricted session; [/quote]
或者
[quote]SQL> connect / as sysdba
SQL>alter system enable restricted session;
To drop server alert schema.
SQL>@$ORACLE_HOME/rdbms/admin/catnoalr.sql
Rerun catproc.sql
SQL>@$ORACLE_HOME/rdbms/admin/catproc.sql
SQL> alter system disable restricted session; [/quote]
[quote]Mon Jun 29 16:00:06 2009
Memory Notification: Library Cache Object loaded into SGA
Heap size 4365K exceeds notification threshold (2048K)
KGL object name :SYS.ALERT_QUE [/quote]
其相关trc文件显示
[quote]Dump file g:\oracle\product\10.2.0\admin\dota\udump\dota_ora_65148.trc
Mon Jun 29 15:59:36 2009
ORACLE V10.2.0.1.0 - Production vsnsta=0
vsnsql=14 vsnxtr=3
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
Windows XP Version V5.1 Service Pack 3, v.5657
CPU : 2 - type 586, 2 Physical Cores
Process Affinity : 0x00000000
Memory (Avail/Total): Ph:271M/2046M, Ph+PgF:1598M/3932M, VA:1270M/2047M
Instance name: dota
Redo thread mounted by this instance: 1
Oracle process number: 19
Windows thread id: 65148, image: ORACLE.EXE (SHAD)
*** SERVICE NAME:(SYS$USERS) 2009-06-29 15:59:36.000
*** SESSION ID:(138.6167) 2009-06-29 15:59:36.000
Memory Notification: Library Cache Object loaded into SGA
Heap size 4358K exceeds notification threshold (2048K)
LIBRARY OBJECT HANDLE: handle=33ccb7cc mutex=33CCB880(0)
name=SYS.ALERT_QUE
hash=ac8b6e1e4ca94b6f517f04aaaa810f64 timestamp=08-30-2005 14:11:40
namespace=QUEU flags=KGHP/TIM/SML/[02000000]
kkkk-dddd-llll=0000-0000-0000 lock=N pin=S latch#=1 hpc=0a98 hlc=0a98
lwt=33CCB828[33CCB828,33CCB828] ltm=33CCB830[33CCB830,33CCB830]
pwt=33CCB80C[33CCB80C,33CCB80C] ptm=33CCB814[33CCB814,33CCB814]
ref=33CCB848[33CCB848,33CCB848] lnd=33CCB854[33CCB854,33CCB854]
LIBRARY OBJECT: object=2f75a8a4
type=QUEU flags=EXS/LOC[0005] pflags=[0000] status=VALD load=X
DEPENDENCIES: count=4 size=16
dependency# table reference handle position flags
----------- -------- --------- -------- -------- -------------------
0 2f75ac60 2f6505f4 33d584fc 0 DEP[01]
1 2f75ac60 2f650628 33d583a4 0 DEP[01]
2 2f75ac60 2f65065c 33d5824c 0 DEP[01]
3 2f75ac60 2f650690 33e5d438 0 DEP[01]
DATA BLOCKS:
data# heap pointer status pins change whr alloc(K) size(K)
----- -------- -------- --------- ---- ------ --- -------- --------
0 28227d50 2f75a93c I/P/A/-/- 0 NONE 00 4358.61 0.00[/quote]
ALERT_QUE表格主要用于dbconsole或者gird control监视系统警告信息所用,根据alert日志提示,其堆栈大小超过了系统设定值,解决办法如下
[quote]SQL> connect / as sysdba
SQL>alter system enable restricted session;
To drop server alert schema.
SQL>@$ORACLE_HOME/rdbms/admin/catnoalr.sql
To re-create tables, sequence, type and queue for server alert
SQL>@$ORACLE_HOME/rdbms/admin/dbmsslrt.sql
SQL>@$ORACLE_HOME/rdbms/admin/catalrt.sql
To recompile the invalid objects
SQL>@$ORACLE_HOME/rdbms/admin/utlrp.sql
SQL> alter system disable restricted session; [/quote]
或者
[quote]SQL> connect / as sysdba
SQL>alter system enable restricted session;
To drop server alert schema.
SQL>@$ORACLE_HOME/rdbms/admin/catnoalr.sql
Rerun catproc.sql
SQL>@$ORACLE_HOME/rdbms/admin/catproc.sql
SQL> alter system disable restricted session; [/quote]