分类目录归档：Oracle

ORA-600[6749] 发生在 SYSMAN.MGMT_METRICS_RAW表

发表于 2012 年 6 月 7 日由惜分飞

数据库alert日志长时间出现ORA-00600[6749]错误
日志报错如下

Fri Jun  1 12:01:30 2012
Errors in file /opt/oracle/admin/oraapp/bdump/oraapp_j000_396.trc:
ORA-00600: internal error code, arguments: [6749], [3], [12596882], [49], [], [], [], []
Fri Jun  1 12:01:34 2012
Errors in file /opt/oracle/admin/oraapp/bdump/oraapp_j000_396.trc:
Fri Jun  1 13:01:06 2012
Errors in file /opt/oracle/admin/oraapp/bdump/oraapp_j000_13226.trc:
ORA-00600: internal error code, arguments: [6749], [3], [12596882], [49], [], [], [], []
Fri Jun  1 13:01:10 2012
Errors in file /opt/oracle/admin/oraapp/bdump/oraapp_j000_13226.trc:
Fri Jun  1 14:01:46 2012
Errors in file /opt/oracle/admin/oraapp/bdump/oraapp_j000_26691.trc:
ORA-00600: internal error code, arguments: [6749], [3], [12596882], [49], [], [], [], []
Fri Jun  1 14:01:51 2012
Errors in file /opt/oracle/admin/oraapp/bdump/oraapp_j000_26691.trc:
Fri Jun  1 15:01:21 2012
Errors in file /opt/oracle/admin/oraapp/bdump/oraapp_j000_7119.trc:
ORA-00600: internal error code, arguments: [6749], [3], [12596882], [49], [], [], [], []

查看trace日志

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
ORACLE_HOME = /opt/oracle/product/10/oraapp
System name:	Linux
Node name:	oracle2
Release:	2.6.18-92.el5
Version:	#1 SMP Tue Apr 29 13:16:15 EDT 2008
Machine:	x86_64
Instance name: oraapp
Redo thread mounted by this instance: 1
Oracle process number: 44
Unix process pid: 26691, image: oracle@oracle2 (J000)

*** ACTION NAME:(target 5) 2012-06-01 14:01:00.298
*** MODULE NAME:(Oracle Enterprise Manager.rollup) 2012-06-01 14:01:00.298
*** SERVICE NAME:(SYS$USERS) 2012-06-01 14:01:00.298
*** SESSION ID:(406.24103) 2012-06-01 14:01:00.298
Dumping current redo log in thread 1

DUMP OF REDO FROM FILE '/opt/oracle/oradata/oraapp/systable/redo03.log'
 Opcodes 11.*
 DBAs (file#, block#):
      (3, 13970)
 RBAs: 0x000000.00000000.0000 thru 0xffffffff.ffffffff.ffff
 SCNs: scn: 0x0000.00000000 thru scn: 0xffff.ffffffff
 Times: creation thru eternity
 FILE HEADER:
	Compatibility Vsn = 169869568=0xa200100
	Db ID=1462349529=0x5729aed9, Db Name='ORAAPP'
	Activation ID=1462334681=0x572974d9
	Control Seq=2614156=0x27e38c, File size=245760=0x3c000
	File Number=3, Blksiz=512, File Type=2 LOG
 descrip:"Thread 0001, Seq# 0000003963, SCN 0x0000129fc9df-0xffffffffffff"

猜测ORA-600[6749]部分参数

SQL> select  DBMS_UTILITY.data_block_address_file (12596882) "file#",
  2  DBMS_UTILITY.data_block_address_block (12596882) "block#" 
  3  from dual;

     file#     block#
---------- ----------
         3      13970

ORA-600[6749][a][b]{c}
这里证明c表示rdba

根据dba查询对象

SQL> select * from dba_extents where 13970 between block_id and block_id + blocks and file_id=3;

OWNER      SEGMENT_NAME                     SEGMENT_TYPE   
---------- ------------------------------- -------------------
SYSMAN        SYS_IOT_OVER_10448                 TABLE         

SQL> select owner,iot_name from dba_tables where table_name = 'SYS_IOT_OVER_10448';

OWNER                          IOT_NAME
------------------------------ ------------------------------
SYSMAN                         MGMT_METRICS_RAW

SQL>  ANALYZE TABLE SYSMAN.MGMT_METRICS_RAW  VALIDATE STRUCTURE CASCADE;

Table analyzed.

按照常理ORA-00600[6749]错误是因为坏块或者表和索引数据不一致导致,通过ANALYZE可以检查出来.这里显示正常,那可能是其他原因导致,查询MOS果然发现是ORA-600 [6749] Occurring on SYSMAN.MGMT_METRICS_RAW [ID 467439.1]

解决方法

The following workaround may resolve the problem temporarily:

1. Ensure you have a good backup before proceeding.

2. Create a copy of the SYSMAN.MGMT_METRICS_RAW table:

SQL> create table SYSMAN.MGMT_METRICS_RAW_COPY 
as select * from SYSMAN.MGMT_METRICS_RAW;

3. Truncate the table:

SQL> truncate table SYSMAN.MGMT_METRICS_RAW;

May need  to disable trigger: "sysman.raw_metrics_after_insert" before proceeding.  
Re-enable after the insert.

4. Re-insert the rows:

SQL> insert into SYSMAN.MGMT_METRICS_RAW 
select * from SYSMAN.MGMT_METRICS_RAW_COPY; 
SQL> commit;

5. Drop the copy table:

SQL> drop table SYSMAN.MGMT_METRICS_RAW_COPY;

发表在 ORA-xxxxx | 标签为 ORA-600 6749, ORA-600[6749] | 3 条评论

DEFERRED_SEGMENT_CREATION 参数相关说明

发表于 2012 年 6 月 6 日由惜分飞

DEFERRED_SEGMENT_CREATION specifies the semantics of deferred segment creation. If set to true, then segments for tables and their dependent objects (LOBs, indexes) will not be created until the first row is inserted into the table.
这句话的意思是 DEFERRED_SEGMENT_CREATION 参数的作用是:创建表的时候延迟创建这个表相关的segment(包括lobs,indexes),直到第一次插入数据的时候才创建segment.补充说明:DEFERRED_SEGMENT_CREATION 参数从11.2.0.1引进,默认值为true;如果要使其恢复老版本功能,设置该参数为false.

DEFERRED_SEGMENT_CREATION默认值

SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
PL/SQL Release 11.2.0.3.0 - Production
CORE    11.2.0.3.0      Production
TNS for Linux: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production

SQL> select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') "WWW.XIFENFEI.COM" FROM DUAL;

WWW.XIFENFEI.COM
--------------------------------------
2012-06-01 05:31:03

SQL> show parameter DEFERRED_SEGMENT_CREATION;

NAME                                 TYPE       VALUE
------------------------------------ ---------- --------
deferred_segment_creation            boolean    TRUE

DEFERRED_SEGMENT_CREATION效果验证

SQL> create table t_xifenfei (id number,name varchar2(30));

Table created.

SQL> create index ind_t_xifenfei on t_xifenfei(id);

Index created.

SQL> select segment_name,segment_type from dba_segments where
  2   segment_name in('T_XIFENFEI','IND_T_XIFENFEI') AND OWNER='CHF';

no rows selected
--未创建segment

SQL> INSERT INTO T_XIFENFEI VALUES(1,'WWW.XIFENFEI.COM');

1 row created.

SQL> commit;

Commit complete.

SQL> select segment_name,segment_type from dba_segments where 
  2  segment_name in('T_XIFENFEI','IND_T_XIFENFEI') AND OWNER='CHF';

SEGMENT_NAME         SEGMENT_TYPE
-------------------- ------------------------------------
IND_T_XIFENFEI       INDEX
T_XIFENFEI           TABLE
--创建segment

SQL> alter session set deferred_segment_creation=false;

Session altered.

SQL> create table t_xifenfei_2 (id number,name varchar2(30));

Table created.

SQL> select segment_name,segment_type from dba_segments where segment_name='T_XIFENFEI_2';

SEGMENT_NAME         SEGMENT_TYPE
-------------------- ------------------------------------
T_XIFENFEI_2         TABLE
--创建segment

问题1(朋友疑惑为什么它没有给相关表空间分配配额但是创建表成功)

SQL> create user xifenfei identified by xifenfei default tablespace users;

User created.

SQL> grant connect,resource to xifenfei;

Grant succeeded.

SQL> revoke unlimited tablespace from xifenfei;

Revoke succeeded.

SQL> alter user xifenfei quota unlimited on users;

User altered.

SQL> conn xifenfei/xifenfei
Connected.
SQL> create table t_xifenfei (id number,name varchar2(30)) tablespace system;

Table created.
--在system表空间无配额,但是创建表成功

SQL> insert into t_xifenfei values(1,'www.xifenfei.com');
insert into t_xifenfei values(1,'www.xifenfei.com')
            *
ERROR at line 1:
ORA-01950: no privileges on tablespace 'SYSTEM'
--插入数据库失败,因为在system上创建segment失败


SQL> alter session set deferred_segment_creation=false;

Session altered.

SQL> create table t_xifenfei_2 (id number,name varchar2(30)) tablespace system;
create table t_xifenfei_2 (id number,name varchar2(30)) tablespace system
*
ERROR at line 1:
ORA-01950: no privileges on tablespace 'SYSTEM'
--deferred_segment_creation设置为false后,创建表直接失败

问题2(exp未导segment不存在表)
该问题帮朋友解决过.因为暂时无11.2.0.1版本数据库,直接摘录MOS

In 11.2 the deferred storage segment feature is enabled by default. 
Conventional export (exp) silently skips tables with deferred segment
creation if no segment has yet been created. ie: If the table does
not yet contain any rows. In some cases "exp" will report EXP-11 for 
the table.
 
eg:
 create table t(c1 int) tablespace sysaux;
 select segment_created from user_tables where table_name='T';
  SEG
  ---
  NO
 
 Table level export:
   exp scott/tiger file=/tmp/scott.dmp tables=t
   ^
   EXP-11 SCOTT.T does not exist
 
 Schema level export:
   exp scott/tiger file=/tmp/scott.dmp owner=scott statistics=none
   ^
   Export completes successfully but silently does not export
   table "T".
 
Rediscovery Notes:
  Tables that may be affected by this can be found thus:
    select owner, table_name from dba_tables 
     where segment_created='NO';
 
  EXP-11 on export for tables with no data.
  Tables missing after exp/imp
 
Workaround
  Re-create the missing table at the export site from DDL.
  (the table did not contain rows otherwise it would have
   had a segment created for it)

In 11.2 the deferred storage segment feature is enabled by default. 
Conventional export (exp) silently skips tables with deferred segment
creation if no segment has yet been created. ie: If the table does
not yet contain any rows. In some cases "exp" will report EXP-11 for 
the table.
 
eg:
 create table t(c1 int) tablespace sysaux;
 select segment_created from user_tables where table_name='T';
  SEG
  ---
  NO
 
 Table level export:
   exp scott/tiger file=/tmp/scott.dmp tables=t
   ^
   EXP-11 SCOTT.T does not exist
 
 Schema level export:
   exp scott/tiger file=/tmp/scott.dmp owner=scott statistics=none
   ^
   Export completes successfully but silently does not export
   table "T".
 
Rediscovery Notes:
  Tables that may be affected by this can be found thus:
    select owner, table_name from dba_tables 
     where segment_created='NO';
 
  EXP-11 on export for tables with no data.
  Tables missing after exp/imp
 
Workaround
  Re-create the missing table at the export site from DDL.
  (the table did not contain rows otherwise it would have
   had a segment created for it)
 
This issue is fixed in	
•12.1 (Future Release) 
•11.2.0.2 (Server Patch Set)

发表在 Oracle | 标签为 DEFERRED_SEGMENT_CREATION | 评论关闭

动态修改PGA_AGGREGATE_TARGET 导致ORA-600[723]

发表于 2012 年 6 月 6 日由惜分飞

在以前分析过ORA-600[729](SGA内存泄露),这次遇到ORA-600[723](PGA内存泄露)
操作系统数据库信息

ORACLE V9.2.0.3.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.3.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.3.0 - Production
Windows 2000 Version 5.2 Service Pack 2, CPU type 586
Instance name: dsdata

alert报错ORA-600[723]

Tue Jun 05 12:16:35 2012
Shutting down instance: further logons disabled
Shutting down instance (immediate)
License high water mark = 274
Tue Jun 05 12:16:40 2012
alter database close normal 
Tue Jun 05 12:16:40 2012
SMON: disabling tx recovery
SMON: disabling cache recovery
Tue Jun 05 12:16:40 2012
Shutting down archive processes
Archiving is disabled
Tue Jun 05 12:16:40 2012
ARCH shutting down
Tue Jun 05 12:16:40 2012
ARCH shutting down
ARC1: Archival stopped
Tue Jun 05 12:16:40 2012
ARC0: Archival stopped
Tue Jun 05 12:16:40 2012
Thread 1 closed at log sequence 406
Successful close of redo thread 1.
Tue Jun 05 12:16:41 2012
Completed: alter database close normal
Tue Jun 05 12:16:41 2012
alter database dismount
Completed: alter database dismount
ARCH: Archiving is disabled
Shutting down archive processes
Archiving is disabled
Archive process shutdown avoided: 0 active
ARCH: Archiving is disabled
Shutting down archive processes
Archiving is disabled
Archive process shutdown avoided: 0 active
Tue Jun 05 12:16:43 2012
Errors in file d:\oracle\admin\dsdata\udump\dsdata_ora_504.trc:
ORA-00600: internal error code, arguments: [723], [20664], [20664], [memory leak], [], [], [], []

通过alert日志可以知道,数据库shutdown immediate的时候报ORA-600[723]

分析trace文件

…………
EXTENT 147 addr=062ACCBC
  Chunk  62accc4 sz=     1252    free      "               "
  Chunk  62ad1a8 sz=     2060    freeable  "qesmmaLogInitia"
  Chunk  62ad9b4 sz=     2060    freeable  "qesmmaLogInitia"
  Chunk  62ae1c0 sz=     2060    freeable  "qesmmaLogInitia"
  Chunk  62ae9cc sz=     2060    freeable  "qesmmaLogInitia"
…………
EXTENT 153 addr=04232414
  Chunk  423241c sz=     4476    perm      "perm           "  alo=2868
  Chunk  4233598 sz=    18516    free      "               "
  Chunk  4237dec sz=     2060    freeable  "qesmmaLogInitia"
  Chunk  42385f8 sz=     2060    freeable  "qesmmaLogInitia"
  Chunk  4238e04 sz=     2060    freeable  "qesmmaLogInitia"
  Chunk  4239610 sz=     2060    freeable  "qesmmaLogInitia"
  Chunk  4239e1c sz=     2060    freeable  "qesmmaLogInitia"
…………
--查询发现没有释放的内容都是在qesmmaLogInitia部分

*** 2012-06-05 12:16:43.000
ksedmp: internal or fatal error
ORA-00600: internal error code, arguments: [723], [20664], [20664], [memory leak], [], [], [], []
Current SQL information unavailable - no SGA.
----- Call Stack Trace -----
calling              call     entry                argument values in hex      
location             type     point                (? means dubious value)     
-------------------- -------- -------------------- ----------------------------
_ksedmp+147          CALLrel  _ksedst+0            
_ksfdmp.108+e        CALLrel  _ksedmp+0            3
_kgeriv+89           CALLreg  00000000             217190 3
_kgesiv+4e           CALLrel  _kgeriv+0            217190 0 2D3 3 418FC2C
_ksesic3+3b          CALLrel  _kgesiv+0            217190 0 2D3 3 418FC2C 2D3 3
                                                   418FC2C
__VInfreq__ksmdpg+e  CALLrel  _ksesic3+0           2D3 0 50B8 0 50B8 1 B 26A3F28
f                                                  
_opidcl+1db          CALLrel  _ksmdpg+0            
_opidrv+3bf          CALLrel  _opidcl+0            21D328 0
_sou2o+19            CALLrel  _opidrv+0            
_opimai+150          CALLrel  _sou2o+0             418FE20 32 0 0
_BackgroundThreadSt  CALLrel  _opimai+0            
art@4+164                                          
77E6482C             CALLreg  00000000             
 
--------------------- Binary Stack Dump ---------------------

通过查询MOS发现[ID 242260.1]上的Stack Trace比较匹配.上面说到通过sql来直接修改pga_aggregate_target导致,查找alert日志,果然发现:

Mon May 21 15:18:33 2012
ALTER SYSTEM SET pga_aggregate_target='1048576000' SCOPE=MEMORY;
Mon May 21 15:18:33 2012
ALTER SYSTEM SET pga_aggregate_target='1048576000' SCOPE=SPFILE;

现在基本上可以确定引起整个ORA-600[723]的原因是:用户直接修改pga_aggregate_target参数,然后关闭数据库引起Bug:2975617导致

处理建议
Don’t alter the pga_aggregate_target dynamically Change it in init.ora file
针对本库,再次开启数据库应该处于正常状态(spfile已经修改),无需继续关注该问题.

发表在 ORA-xxxxx | 标签为 ORA-600 723, ORA-600[723] | 2 条评论

17813235971

QQ 咨询

分类目录归档：Oracle

ORA-600[6749] 发生在 SYSMAN.MGMT_METRICS_RAW表

DEFERRED_SEGMENT_CREATION 参数相关说明

动态修改PGA_AGGREGATE_TARGET 导致ORA-600[723]

文章分类

最近发表

分类目录归档：Oracle

ORA-600[6749] 发生在 SYSMAN.MGMT_METRICS_RAW表

DEFERRED_SEGMENT_CREATION 参数相关说明

动态修改PGA_AGGREGATE_TARGET 导致ORA-600[723]

标签云

文章分类

最近发表