Script:收集数据库安全风险评估信息

以下脚本可以用于收集数据库安全风险评估信息:

REM list database vulnerability assessment info

set escape on;
set linesize 140 ;
spool db_vulnerability_assessment.log

Select role
  from dba_roles r
 where role not in ('CONNECT',
                    'RESOURCE',
                    'DBA',
                    'SELECT_CATALOG_ROLE',
                    'EXECUTE_CATALOG_ROLE',
                    'DELETE_CATALOG_ROLE',
                    'EXP_FULL_DATABASE',
                    'WM_ADMIN_ROLE',
                    'IMP_FULL_DATABASE',
                    'RECOVERY_CATALOG_OWNER',
                    'AQ_ADMINISTRATOR_ROLE',
                    'AQ_USER_ROLE',
                    'GLOBAL_AQ_USER_ROLE',
                    'OEM_MONITOR',
                    'HS_ADMIN_ROLE')
   and not exists
 (Select 1 from dba_role_privs p where p.granted_role = r.role)
/

select tp.grantee, tp.table_name
  from dba_tab_privs tp, dba_users u
 where tp.owner = 'SYS'
   and (tp.table_name like 'V_$%' or tp.table_name like 'G_V$')
   and tp.grantee = u.username
   and u.username not in ('SYS',
                          'SYSTEM',
                          'SYSMAN',
                          'EXFSYS',
                          'WMSYS',
                          'OLAPSYS',
                          'OUTLN',
                          'DBSNMP',
                          'ORDSYS',
                          'ORDPLUGINS',
                          'MDSYS',
                          'DMSYS',
                          'CTXSYS',
                          'AURORA$ORB$UNAUTHENTICATED',
                          'XDB',
                          'FLOWS_030000',
                          'FLOWS_FILES')
 order by 1, 2
/


select *
  from (select 'Hidden User in DBA_USERS' ddview, name
          from sys.user$
         where type# = 1
        minus
        select 'Hidden User in DBA_USERS', username from SYS.dba_users) q1
union all
select *
  from (select 'Hidden User in ALL_USERS', name
          from sys.user$
         where type# = 1
        minus
        select 'Hidden User in ALL_USERS', username from SYS.all_users) q2
/



select grantee, granted_role
  from dba_role_privs
 where grantee in (select role from dba_roles)
 order by grantee, granted_role
/

select grantee, privilege, admin_option
  from dba_sys_privs sp, dba_users u
 where sp.GRANTEE = u.username
   and grantee not in ('SYS', 'SYSTEM')
   and privilege in (select name
                       from sys.system_privilege_map
                      where 0 = 1
                         or name like '%ANY%'
                         or name like '%DATABASE%'
                         or name like '%DIRECTORY%'
                         or name like '%LIBRARY%'
                         or name like '%LINK%'
                         or name like '%PROFILE%'
                         or name like '%RESTRICTED%'
                         or name like 'SYS%'
                         or name like '%SYSTEM%'
                         or name like '%TABLESPACE%'
                         or name like '%USER%')
 order by 1
/

select role,
       (select count(*)
          from dba_role_privs rp
         where rp.granted_role = r.role) GRANT_COUNT
  from dba_roles r
 where r.role in ('DBA', 'CONNECT', 'RESOURCE')
 order by 1
/

select grantee, granted_role, admin_option
  from dba_role_privs rp, dba_users u
 where rp.grantee = u.username
   and grantee not in ('SYS', 'SYSTEM')
   and granted_role in (select role
                          from dba_roles
                         where 0 = 1
                            or role like '%CATALOG%'
                            or role like '%DATABASE%'
                            or role like '%DBA%')
 order by 1
/

select distinct profile, resource_name, actual_limit 
from (select P.Profile, p.resource_Name,
             decode(p.limit, 'UNLIMITED', '9999999999999999999', 
                   'NULL', null, to_number(p.limit)) limit,
             limit actual_limit
      from ( select profile, resource_name, 
                    decode(resource_name,  'IDLE_TIME', decode(limit, 'DEFAULT', 'UNLIMITED', limit),
                                           'FAILED_LOGIN_ATTEMPTS', decode(limit, 'DEFAULT', '10', limit),
                                           'PASSWORD_LIFE_TIME', decode(limit, 'DEFAULT', 'UNLIMITED', limit),
                                           'PASSWORD_REUSE_MAX', decode(limit, 'DEFAULT', 'UNLIMITED', limit),
                                           'PASSWORD_REUSE_TIME', decode(limit, 'DEFAULT', 'UNLIMITED', limit),
                                           'PASSWORD_GRACE_TIME', decode(limit, 'DEFAULT', 'UNLIMITED', limit),
                                'PASSWORD_VERIFY_FUNCTION', decode(limit, 'NULL', '0', null, 0, 'DEFAULT', 0, 1), limit) limit
              from   dba_profiles
              where resource_name in ('IDLE_TIME', 'FAILED_LOGIN_ATTEMPTS',
                                      'PASSWORD_LIFE_TIME', 'PASSWORD_REUSE_MAX',
                                      'PASSWORD_REUSE_TIME','PASSWORD_GRACE_TIME',
                                      'PASSWORD_VERIFY_FUNCTION')) p ) 
where 1=0 
or    (RESOURCE_NAME = 'IDLE_TIME' AND LIMIT > 60)
or    (RESOURCE_NAME = 'FAILED_LOGIN_ATTEMPTS' AND LIMIT > 3)
or    (RESOURCE_NAME = 'PASSWORD_LIFE_TIME' AND LIMIT > 90)
or    (RESOURCE_NAME = 'PASSWORD_REUSE_MAX' AND LIMIT > 20)
or    (RESOURCE_NAME = 'PASSWORD_REUSE_TIME' AND LIMIT > 180)
or    (RESOURCE_NAME = 'PASSWORD_GRACE_TIME' AND LIMIT > 3)
or    (RESOURCE_NAME = 'PASSWORD_VERIFY_FUNCTION' AND LIMIT = 0)
order by 1,2
/

Select s.owner, s.synonym_name, s.table_owner, s.table_name
  from sys.DBA_synonyms s
 where not exists (Select 'x'
          from sys.DBA_objects o
         where o.owner = s.table_owner
           and o.object_name = s.table_name)
   and db_link is null
   and s.owner <> 'PUBLIC'
 order by 1
/

Select distinct profile
  from dba_profiles
minus
Select distinct profile from dba_users
/

select table_name
  from dba_tab_privs
 where owner = 'SYS'
   and grantee = 'PUBLIC'
   and table_name in ('UTL_SMTP',
                      'UTL_TCP',
                      'UTL_HTTP',
                      'UTL_FILE',
                      'DBMS_RANDOM',
                      'DBMS_LOB',
                      'DBMS_SYS_SQL',
                      'DBMS_BACKUP_RESTORE',
                      'EMD_SYSTEM',
                      'DBMS_NAMESPACE',
                      'DBMS_SCHEDULER')
 order by 1
/ 
 

select username, password from dba_users order by 1
/


select tp.grantee, tp.table_name, tp.privilege
  from dba_tab_privs tp, dba_users u, dba_tables t
 where tp.owner = 'SYS'
   and tp.grantee = u.username
   and tp.owner = t.owner
   and tp.table_name = t.table_name
   and u.username not in ('SYS',
                          'SYSTEM',
                          'SYSMAN',
                          'EXFSYS',
                          'WMSYS',
                          'OLAPSYS',
                          'OUTLN',
                          'DBSNMP',
                          'ORDSYS',
                          'ORDPLUGINS',
                          'MDSYS',
                          'CTXSYS',
                          'AURORA$ORB$UNAUTHENTICATED',
                          'XDB',
                          'FLOWS_030000',
                          'FLOWS_FILES')
 order by 1, 2, 3
/
 
 select sp.grantee, sp.privilege
   from dba_sys_privs sp, dba_users u
  where sp.admin_option = 'YES'
    and u.username = sp.grantee
    and u.username not in ('SYS',
                           'SYSTEM',
                           'SYSMAN',
                           'EXFSYS',
                           'WMSYS',
                           'OLAPSYS',
                           'OUTLN',
                           'DBSNMP',
                           'ORDSYS',
                           'ORDPLUGINS',
                           'MDSYS',
                           'CTXSYS',
                           'AURORA$ORB$UNAUTHENTICATED',
                           'XDB',
                           'FLOWS_030000',
                           'FLOWS_FILES')
  order by 1, 2
/
  
  select p.grantee, p.owner, p.table_name, p.privilege
    from dba_tab_privs p, dba_users u
   where p.grantable = 'YES'
     and u.USERNAME = p.grantee
     and u.username not in ('SYS',
                            'SYSTEM',
                            'SYSMAN',
                            'EXFSYS',
                            'WMSYS',
                            'OLAPSYS',
                            'OUTLN',
                            'DBSNMP',
                            'ORDSYS',
                            'ORDPLUGINS',
                            'MDSYS',
                            'CTXSYS',
                            'AURORA$ORB$UNAUTHENTICATED',
                            'XDB',
                            'FLOWS_030000',
                            'FLOWS_FILES')
   order by 1, 2, 3, 4
/

select username
  from dba_users
 where account_status!='EXPIRED \& LOCKED'
 order by 1
/

Select s.synonym_name, s.table_owner, s.table_name
  from sys.DBA_synonyms s
 where not exists (Select 'x'
          from sys.DBA_objects o
         where o.owner = s.table_owner
           and o.object_name = s.table_name)
   and db_link is null
   and s.owner = 'PUBLIC'
 order by 1
/

select r.grantee, r.granted_role
  from dba_role_privs r, dba_users u
 where r.admin_option = 'YES'
   and u.username = r.grantee
   and u.username not in ('SYS',
                          'SYSTEM',
                          'SYSMAN',
                          'EXFSYS',
                          'WMSYS',
                          'OLAPSYS',
                          'OUTLN',
                          'DBSNMP',
                          'ORDSYS',
                          'ORDPLUGINS',
                          'MDSYS',
                          'CTXSYS',
                          'AURORA$ORB$UNAUTHENTICATED',
                          'XDB',
                          'FLOWS_030000',
                          'FLOWS_FILES')
 order by 1, 2
/


select username
  from dba_users
 where password = 'EXTERNAL'
 order by username
/

PMON: TERMINATING INSTANCE DUE TO ERROR 600 on 8i

Alert logfile reported as below:

*********************
Wed May 27 13:11:47 2009
Errors in file /u01/app/oracle/admin/proa021/udump/proa021_ora_9533.trc:
ORA-07445: exception encountered: core dump [memset()+116] [SIGSEGV] [Address not mapped to object] [0] [] []

From Trace file
********************
Dump file /u01/app/oracle/admin/proa021/udump/proa021_ora_9533.trc

Oracle8i Enterprise Edition Release 8.1.7.4.0 - Production
With the Partitioning option
JServer Release 8.1.7.4.0 - Production
ORACLE_HOME = /u01/app/oracle/product/817proa021
System name:	SunOS
Node name:	v08k01
Release:	5.8
Version:	Generic_117350-38
Machine:	sun4u
Instance name: proa021
Redo thread mounted by this instance: 1

Process Info
******************
Oracle process number: 117
Unix process pid: 9533, image: oracle@v08k01 (TNS V1-V3)

Error
*********
2009-05-27 13:11:47.847
ksedmp: internal or fatal error
ORA-07445: exception encountered: core dump [memset()+116] [SIGSEGV] [Address not mapped to object] [0] [] []

Current SQL(Current SQL statement for this session)
***********************************************************************
:
SELECT COUNT(PO_LINE_ID) FROM PO_LINES_INTERFACE WHERE PO_HEADER_ID = :b1

Call Stack functions
*************************
ksedmp <- ssexhd <- sigacthandler <- memset


#####################################################################################
From Alert logfile
*********************
Wed May 27 13:18:39 2009
Errors in file /u01/app/oracle/admin/proa021/bdump/proa021_pmon_9584.trc:
ORA-00600: internal error code, arguments: [1115], [], [], [], [], [], [], []
Wed May 27 13:18:56 2009
Errors in file /u01/app/oracle/admin/proa021/bdump/proa021_pmon_9584.trc:
ORA-00600: internal error code, arguments: [1115], [], [], [], [], [], [], []


From Tracefile
*******************
Dump file /u01/app/oracle/admin/proa021/bdump/proa021_pmon_9584.trc

Oracle8i Enterprise Edition Release 8.1.7.4.0 - Production
With the Partitioning option
JServer Release 8.1.7.4.0 - Production
ORACLE_HOME = /u01/app/oracle/product/817proa021
System name:	SunOS
Node name:	v08k01
Release:	5.8
Version:	Generic_117350-38
Machine:	sun4u
Instance name: proa021
Redo thread mounted by this instance: 1

Process Info
****************
Oracle process number: 2
Unix process pid: 9584, image: oracle@v08k01 (PMON)


Error
********
2009-05-27 13:18:39.766
ksedmp: internal or fatal error
ORA-00600: internal error code, arguments: [1115], [], [], [], [], [], [], []

Call Stack Functions:
****************************
ksedmp <- kgeriv <- kgesiv <- ksesic0 <- kssdch
<- ksuxds <- kssxdl <- kssdch <- ksudlp <- kssxdl
<- ksuxdl <- ksuxda <- ksucln <- ksbrdp <- opirip
<- opidrv <- sou2o <- main <- start

CURRENT SESSION'S INSTANTIATION STATE
*********************************************************
current session=8c8fdfbc
---- Cursor Dump ------
Current cursor: 0, pgadep: 0
Cursor Dump:
End of cursor dump
END OF PROCESS STATE
******************** Cursor Dump ************************
Current cursor: 0, pgadep: 0
Cursor Dump:
End of cursor dump
ksedmp: no current context area

ERROR: ORA-600 [1115]

VERSIONS: versions 6.0 to 10.1

DESCRIPTION: We are encountering a problem while cleaning up a state object.

The State Object is already on free list or has the wrong parent State Object.

FUNCTIONALITY: Kernal Service State object manager

IMPACT:
POSSIBLE INSTANCE FAILURE
PROCESS FAILURE
NON CORRUPTIVE - No underlying data corruption.

SUGGESTIONS: This error may be reported as a direct result of another earlier problem.

Lot of bugs reported

Bug 3837965 : Abstract: ORA-7445'S AND 600'S LEADING UP TO DB CRASH
Comp Version: 8.1.7.4.0
Fixed In Version: 9.2.0.
-------------------------------------------------------------

Bug 3134843 : Abstract: ORACLE PROCESSES CRASHING WITH ORA-7445 SEGVIO ON A NUMBER OF DATABASES
Comp Version: 8.1.7.4
Status: Closed, could not be reproduced
----------------------------------------------------------------

Bug 2760836: Abstract: PMON cleanup of dead shared servers/dispatchers can crash instance(OERI:26599 / OERI 1115)

--------------------------------------------------------------
Note 2760836.8 PMON cleanup of dead shared servers/dispatchers can crash instance (OERI 26599 / OERI 1115)
----------------------------------------------------------------

PROPOSED SOLUTION JUSTIFICATION(S)
==================================
1. One-off patch for Bug 2760836 has fixed this issue...so after customer apply the one-off patch...then this issue will be solved.

OR

2. 9.2.0.4 or later version has fixed this issue...so after customer upgrade to at least 9.2.0.4 version...then this issue will be solved.

The solution can be justified by the followings:

Note 2760836.8 PMON cleanup of dead shared servers/dispatchers can crash instance (OERI 26599 / OERI 1115)

How does cpu_count parameter affect instance?

Parameter cpu_count is determined by Oracle Software when instance started, But we can set it manually. This parameter can affect lots of  hidden initialization parameters,see:

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
PL/SQL Release 10.2.0.4.0 - Production
CORE    10.2.0.4.0      Production
TNS for Linux: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production

SQL> host cat /proc/cpuinfo|grep processor
processor       : 0
processor       : 1

SQL> show parameter cpu_count

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
cpu_count                            integer     2

create table  cpu_2_parameters as 
SELECT x.ksppinm NAME, y.ksppstvl VALUE, x.ksppdesc describ
 FROM SYS.x$ksppi x, SYS.x$ksppcv y
 WHERE x.inst_id = USERENV ('Instance')
 AND y.inst_id = USERENV ('Instance')
 AND x.indx = y.indx;

SQL> alter system set cpu_count=128 scope=spfile;

System altered.

SQL> startup force;
ORACLE instance started.

Total System Global Area 1702887424 bytes
Fixed Size                  2093192 bytes
Variable Size             486543224 bytes
Database Buffers         1073741824 bytes
Redo Buffers              140509184 bytes
Database mounted.
Database opened.

SQL> show parameter cpu_count

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
cpu_count                            integer     128


SQL> create table  cpu_128_parameters as 
  2  SELECT x.ksppinm NAME, y.ksppstvl VALUE, x.ksppdesc describ
  3   FROM SYS.x$ksppi x, SYS.x$ksppcv y
  4   WHERE x.inst_id = USERENV ('Instance')
  5   AND y.inst_id = USERENV ('Instance')
  6   AND x.indx = y.indx; 

Table created.


SQL> col name for a35
SQL> set linesize 200 pagesize 2000;
SQL> col "while_cpu_count=   2" for a20
SQL> col "while_cpu_count= 128" for a02
SQL>  col "while_cpu_count= 128" for a20
SQL> SELECT a.name,
  2    a.value "while_cpu_count=   2",
  3    b.value "while_cpu_count= 128",
  4    a.describ
  5  FROM cpu_2_parameters a,
  6    cpu_128_parameters b
  7  WHERE a.name=b.name
  8  AND a.value!=b.value 
  9  ORDER BY 1;

NAME                                while_cpu_count=   2 while_cpu_count= 128 DESCRIB
----------------------------------- -------------------- -------------------- --------------------------------------------------------------------------------
__db_cache_size                     33554432             536870912            Actual size of DEFAULT buffer pool for standard block size buffers
_cursor_db_buffers_pinned           445                  841                  additional number of buffers a cursor can pin at once
_db_block_buffers                   67184                126464               Number of database blocks cached in memory: hidden parameter
_db_block_lru_latches               8                    512                  number of lru latches
_enqueue_hash_chain_latches         2                    128                  enqueue hash chain latches
_enqueue_locks                      2300                 2490                 locks for managed enqueues
_flashback_generation_buffer_size   8388608              536870912            flashback generation buffer size
_log_parallelism_max                2                    8                    Maximum number of log buffer strands
_log_simultaneous_copies            4                    256                  number of simultaneous copies into redo buffer(# of copy latches)
_num_longop_child_latches           2                    128                  number of child latches for long op array
_parallel_min_message_pool          903840               1076000              minimum size of shared pool memory to reserve for pq servers
_small_table_threshold              1343                 2529                 threshold level of table size for direct reads
cpu_count                           2                    128                  number of CPUs for this instance
db_cache_size                       33554432             536870912            Size of DEFAULT buffer pool for standard block size buffers
db_writer_processes                 1                    16                   number of background database writer  processes to start
log_buffer                          6104064              136249344            redo circular buffer size
parallel_max_servers                40                   135                  maximum parallel query servers per instance
sga_max_size                        1065353216           1702887424           max total SGA size


SQL> show parameter sga_max_size

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
sga_max_size                         big integer 1016M

It looks like sga may grow while the instance has more processors. Oracle Parallel Servers can has more differ with cpu_count . Sometimes this parameter make performance issues.

ORA-00600 [kddummy_blkchk]错误一例

一套HP-UX Itanium平台上的10.2.0.2,实例意外终止,维护人员尝试重启实例,在数据库打开后数秒,smon后台进程报ORA-00600: internal error code, arguments: [kddummy_blkchk], [120], [856039], [6110]错误,实例再次意外终止。

部分告警日志如下:

Corrupt Block Found
TSN = 50, TSNAME = TS_DNI_AAL_12
RFN = 120, BLK = 849708, RDBA = 504166188
OBJN = 701796, OBJD = 701796, OBJECT = MAP_WOL_SILJUK, SUBOBJECT =
SEGMENT OWNER = DBOWN, SEGMENT TYPE = Table Segment
Corrupt Block Found
TSN = 50, TSNAME = TS_DNI_AAL_12
RFN = 121, BLK = 897927, RDBA = 508408711
OBJN = 701796, OBJD = 701796, OBJECT = MAP_WOL_SILJUK, SUBOBJECT =
SEGMENT OWNER = DBOWN, SEGMENT TYPE = Table Segment
Mon May 4 19:38:19 2009
Errors in file /oracle/admin/TDAY2DB/udump/tday2db_ora_2080.trc:
ORA-00600: internal error code, arguments: [kddummy_blkchk], [121], [897927], [6110], [], [], [], []
Mon May 4 19:38:19 2009
Errors in file /oracle/admin/TDAY2DB/udump/tday2db_ora_2077.trc:
ORA-00600: internal error code, arguments: [kddummy_blkchk], [120], [849708], [6110], [], [], [], []

Mon May 4 21:58:55 2009
Recovery of Online Redo Log: Thread 1 Group 5 Seq 90582 Reading mem 0
Mem# 0: /dev/vx/rdsk/day2db1tdg03/redo05.log
Block recovery completed at rba 90582.42.16, scn 1858.566253676
ORACLE Instance TDAY2DB (pid = 22) - Error 81 encountered while recovering transaction (14, 22) on object 701796.
Mon May 4 21:58:55 2009
Errors in file /oracle/admin/TDAY2DB/bdump/tday2db_smon_17651.trc:
ORA-00081: address range [0x60000000000BD230, 0x60000000000BD234) is not readable
ORA-00600: internal error code, arguments: [kddummy_blkchk], [120], [856039], [6110], [], [], [], []
Mon May 4 21:58:55 2009
Errors in file /oracle/admin/TDAY2DB/bdump/tday2db_smon_17651.trc:
ORA-00081: address range [0x60000000000BD230, 0x60000000000BD234) is not readable
ORA-00081: address range [0x60000000000BD230, 0x60000000000BD234) is not readable
ORA-00600: internal error code, arguments: [kddummy_blkchk], [120], [856039], [6110], [], [], [], []
Mon May 4 21:58:57 2009
Errors in file /oracle/admin/TDAY2DB/bdump/tday2db_p020_17710.trc:
ORA-00600: internal error code, arguments: [kddummy_blkchk], [120], [856039], [6110], [], [], [], []
Mon May 4 21:58:58 2009
Doing block recovery for file 120 block 856039
Block recovery from logseq 90582, block 37 to scn 7980615489643
Mon May 4 21:58:58 2009
Recovery of Online Redo Log: Thread 1 Group 5 Seq 90582 Reading mem 0
Mem# 0: /dev/vx/rdsk/day2db1tdg03/redo05.log
Block recovery completed at rba 90582.42.16, scn 1858.566253676
Mon May 4 21:58:58 2009
SMON: Restarting fast_start parallel rollback
Mon May 4 21:58:58 2009
Errors in file /oracle/admin/TDAY2DB/bdump/tday2db_p000_17661.trc:
ORA-00600: internal error code, arguments: [kddummy_blkchk], [120], [856039], [6110], [], [], [], []
Mon May 4 21:58:59 2009
Doing block recovery for file 120 block 856039
Block recovery from logseq 90582, block 37 to scn 7980615489643
Mon May 4 21:58:59 2009
Recovery of Online Redo Log: Thread 1 Group 5 Seq 90582 Reading mem 0
Mem# 0: /dev/vx/rdsk/day2db1tdg03/redo05.log
Block recovery completed at rba 90582.42.16, scn 1858.566253676
Mon May 4 21:58:59 2009
SMON: ignoring slave err,downgrading to serial rollback
Mon May 4 21:59:00 2009
Errors in file /oracle/admin/TDAY2DB/bdump/tday2db_smon_17651.trc:
ORA-00600: internal error code, arguments: [kddummy_blkchk], [120], [856039], [6110], [], [], [], []
Mon May 4 21:59:09 2009
Errors in file /oracle/admin/TDAY2DB/bdump/tday2db_pmon_17633.trc:
ORA-00474: SMON process terminated with error
Mon May 4 21:59:09 2009
PMON: terminating instance due to error 474
Instance terminated by PMON, pid = 17633

当Oracle进程在读取数据块时会做一系列逻辑检测,当发现块当中存在逻辑讹误就会触发该ORA-00600 [kddummy_blkchk]等内部错误;[kddummy_blkchk]内部函数的功能大致与[kdBlkCheckError]相仿,它们都有3个参数argument:
ORA-600 [kddummy_blkchk] [file#] [block#] [check code]
ORA-600 [kdBlkCheckError]   [file#] [block#] [check code]

file#即问题块所在datafile的文件号,block#即问题块的块号,check code为发现逻辑讹误时的检测种类代码;我们也可以通过file#和block#查找到存在问题的对象,譬如这个case中的file#为120,block#为856039,检查种类代码为6110:

Select segment_name,segment_type,owner from dba_extents where file_id=120 and 856039 between block_id and block_id + blocks -1;

当然以上查询是建立在我们能够打开数据库的前提下的,针对由ORA-600[[kddummy_blkchk]或[kdBlkCheckError]引起的实例意外终止及启动实例失败等现象,我们可以通过修改db_block_checking和db_block_checksum 2个参数为false,来阻止Oracle进程对数据块的一些逻辑检测工作:

SQL> alter system set db_block_checking=false;
System altered.

SQL> alter system set db_block_checksum=false;
System altered.

以上参数能够一定程度上规避ORA-600[kddummy_blkchk]或[kdBlkCheckError]的出现;但因为10g中隐式参数_db_always_check_system_ts控制了Oracle是否对system表空间上的对象进行block check和checksum(_db_always_check_system_ts:Always perform block check and checksum for System tablespace),且该隐式参数默认为TRUE;因此你还是有一定概率无法打开数据库,如遇此类argument[a]对应为system表空间的ORA-600[kddummy_blkchk]内部错误,可以尝试使用10513来进一步阻止数据库打开后smon进程的事务恢复(transaction recovery)行为:

SQL> alter system set event='10513 trace name context forever,level 2' scope=spfile;
System altered.

SQL> shutdown immediate;
ORA-01507: database not mounted

ORACLE instance shut down.
SQL> startup ;
ORACLE instance started.

Total System Global Area 2634022912 bytes
Fixed Size                  2086288 bytes
Variable Size            2399144560 bytes
Database Buffers          218103808 bytes
Redo Buffers               14688256 bytes
Database mounted.
Database opened.

SQL> show parameter event
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
event                                string      10513 trace name context forever,level 2

在这个案例中我们尝试设置db_block_checksum 和 db_block_checking为false,打开了数据库,并进一步对存在问题块的表执行了导出导入的工作,最终解决了问题。
10g中默认参数db_block_checksum为TRUE,所以建议你在解决类似问题后,将该参数还原。

Oracle内部错误:ORA-00600[25012]一例

如果自己搞不定可以找诗檀软件专业ORACLE数据库修复团队成员帮您恢复!

诗檀软件专业数据库修复团队

服务热线 : 13764045638   QQ号:47079569    邮箱:service@parnassusdata.com

 

 

SQL> select count(*) from WWork;

COUNT(*)
----------
116114

select count(*) from WWork where Work_WorkID = 100;
select count(*) from WWork where Work_WorkID = 100
*
ERROR at line 1:
ORA-00600: internal error code, arguments: [25012], [15], [8], [], [], [], [],
[]

Tablespace id 15 is the same where the index is created.

analyze table livelink.WWORK validate structure cascade;

analyze table livelink.WWORK validate structure cascade
*
ERROR at line 1:
ORA-00600: internal error code, arguments: [25012], [15], [8], [], [], [], [],
[]

analyze index livelink.WWORK_PRIMARY validate structure;
analyze index livelink.WWORK_PRIMARY validate structure
*
ERROR at line 1:
ORA-08100: index is not valid - see trace file for diagnostics

I'm wondering if the issue could be resolved recreating the index.

alter index WWORK_PRIMARY rebuild online noparallel;

On the alert log file, ORA-00600 began at:
Sun Jun 5 23:29:10 2011
Errors in file /u001/app/oracle/admin/motpcom/udump/motpcom_ora_26554.trc:
ORA-00600: internal error code, arguments: [ktbair1], [1], [6], [], [], [], [], []
Mon Jun 6 00:35:51 2011
Thread 1 advanced to log sequence 303722
Current log# 19 seq# 303722 mem# 0: /u002/oradata/motpcom/redo19.log
Mon Jun 6 00:35:51 2011
And then below error often raised in alert log:
Mon Jun 6 02:45:00 2011
Errors in file /u001/app/oracle/admin/motpcom/udump/motpcom_ora_28348.trc:
ORA-00600: internal error code, arguments: [25012], [15], [8], [], [], [], [], []
Mon Jun 6 02:50:01 2011
I also found other errors:
Mon Jun 6 05:00:01 2011
ORA-01555 caused by SQL statement below (Query Duration=18448 sec, SCN: 0x09f0.52bb91c8):
Mon Jun 6 05:00:01 2011
SELECT COUNT(d.dataid)
FROM allemployees a, networks_dataids d, kuaf k, dversdata v
WHERE k.name=a.user_id(+) AND d.userid=k.id AND a.user_id is null
Mon Jun 6 05:00:13 2011
Errors in file /u001/app/oracle/admin/motpcom/udump/motpcom_ora_28348.trc:
ORA-00600: internal error code, arguments: [25012], [15], [8], [], [], [], [], []
Mon Jun 6 05:05:13 2011
And
Tue Jun 7 02:02:14 2011
Errors in file /u001/app/oracle/admin/motpcom/udump/motpcom_ora_25842.trc:
ORA-07445: exception encountered: core dump [00000001011B8AD8] [SIGSEGV] [Address not mapped to object] [0x000000A88] [] []
Tue Jun 7 02:03:11 2011
And
Tue Jun 7 02:41:35 2011
Errors in file /u001/app/oracle/admin/motpcom/udump/motpcom_ora_27325.trc:
ORA-00600: internal error code, arguments: [2662], [2544], [1396224900], [28954], [3445424704], [786437], [], []
Tue Jun 7 02:41:35 2011
Errors in file /u001/app/oracle/admin/motpcom/udump/motpcom_ora_27325.trc:
ORA-00603: ORACLE server session terminated by fatal error
ORA-00600: internal error code, arguments: [2662], [2544], [1396224900], [28954], [3445424704], [786437], [], []
Tue Jun 7 02:41:59 2011 

SQL> select count(*) FROM WWork where Work_WorkID=100;
select count(*) FROM WWork where Work_WorkID=100
*
ERROR at line 1:
ORA-00600: internal error code, arguments: [25012], [15], [8], [], [], [], [],
[]


SQL> alter index WWORK_PRIMARY rebuild online noparallel;

Index altered.

SQL> select count(*) FROM WWork where Work_WorkID=100;

COUNT(*)
----------
0

Index corruptions do not always mean that there is a Bug.
Indexes corrupt when the transaction ID that associates it with its data does not match.
In majority of the cases, this happens when a table has a large amount of DDL or DML processing as in a OLAP
processing. The index buffer cache is over written with an incorrect transaction id and an error results.
The error could be a ORA-00600 [25027] error, an ORA-8102/8103, or another ORA-00600 error.
Possibly, adding another index may resolve the issue of relying on just one index.

In addition, the index space must be reviewed to determine whether there is enough space.
As in the first example above, if there isn't space, then the index can be overwritten and an error can appear.
I suggest reading My Oracle Knowledge Note 33343.1 "How to Find Out How Much Space an Index is Using" which provides select
statements to show the actual usage of blocks within an index. This gives an idea of how 'full' an index is and
allows the DBA to adjust next extent sizes etc.

In addition, you can start the Index Tuning Wizard from Enterprise Manager in order to get advice on the indexes within the database.

We would like to emphasize that the best method to resolve a corrupt index is to drop and recreate it and not use a rebuild.
If this index corrupts again, then we suggest that it be dropped and recreated.


ORA-600 [25012] "Relative to Absolute File Number Conversion Error"


Note: For additional ORA-600 related information please read Note:146580.1

PURPOSE:            
  This article discusses the internal error "ORA-600 [25012]", what 
  it means and possible actions. The information here is only applicable 
  to the versions listed and is provided only for guidance.
 
ERROR:              
  ORA-600 [25012] [a] [b] [c]
 
VERSIONS:
  versions 8.0 and above
 
DESCRIPTION:

  We are trying to generate the absolute file number given a tablespace 
  number and relative file number and cannot find a matching file number
  or the file number is zero.
 
ARGUMENTS:          
  Arg [a] Tablespace Number 
  Arg [b] Relative file number
  Arg [c] Absolute file number (This arg is present is more recent releases)
 
FUNCTIONALITY:      
  KERNEL FILE MANAGEMENT TABLESPACE COMPONENT
 
IMPACT:             
  POSSIBLE PHYSICAL CORRUPTION
 
SUGGESTIONS:        

  The possibility of physical corruption exists.

  Obtain the trace files and alert.log for this error and log a Service Request
  with Oracle Support Services for diagnosis.

  If the Arg [b] Relative file number returns 0 (zero), look for fake indexes
  that can cause this error.

  The following query list fake indexes :

  select a.*,b.flags from dba_objects a, sys.ind$ b
  where a.object_id = b.obj#
  and bitand(b.flags,4096)=4096;

  Known Issues:




Known Bugs
You can restrict the list below to issues likely to affect one of the following versions by clicking the relevant button:


    NB	Bug	Fixed	Description
    	5653641 	11.2.0.1 	Corrupt dictionary from DROP TABLESPACE containing _offline_rollback_segments
    * 	8198906 	10.2.0.5, 11.2.0.1 	OERI [kddummy_blkchk] / OERI [5467] for an aborted transaction of allocating extents
    	4925342 	9.2.0.8, 10.2.0.3, 11.1.0.6 	OERI [25027] / OERI [25012] on IOT analyze estimate statistics
    	3258674 	9.2.0.7, 10.1.0.4, 10.2.0.1 	QMN process may dump or raise an internal error during DML
    	4186885 	10.2.0.1 	Partition numbers for IOT index/overflow segments are not synchronized
    	3751874 	9.2.0.6, 10.1.0.4, 10.2.0.1 	OERI[25012] can occur when _old_connect_by_enabled set to true
    	3430832 	9.2.0.6, 10.1.0.3, 10.2.0.1 	OERI[25012] in DML after ONLINE create index against PARTITIONED table
    	3915343 	9.2.0.7, 10.1.0.5, 10.2.0.1 	OERI [25012] on COMMIT with refresh ON-COMMIT Materialized view over cluster
    	4305391 	9.2.0.7, 10.1.0.5, 10.2.0.1 	OERI[25012] with kditalp can occur during a temporary LOB operation
    P* 	6047085 		Linux x64-64: SGA corruption / crash following any ORA-7445
    	2526334 	9.2.0.3, 10.1.0.2 	OERI:25012 from AND EQUAL with B-Tree and DOMAIN index
    	2531519 	9.2.0.3, 10.1.0.2 	OERI:[25012] from parallel direct load of bitmap managed segments
    	3150268 	9.2.0.5, 10.1.0.2 	OERI[kcbgcur_1] / OERI:25012 deleting rows from PARENT table with LIST subpartitions
    	3070856 	9.2.0.5, 10.1.0.2 	OERI:12700 / 25012 / ktrexc_1 on transported tablespace with SMU
    	3771508 	9.2.0.7 	OERI[kcbgtcr_1] / OERI[25012] selecting from V$AQ in a shared server
    	3900237 	9.2.0.7 	OERI[25012] changing length of indexed column for a global temporary table
    	1834530 	8.1.7.4, 9.0.1.4, 9.2.0.1 	OERI:25012 / wrong results after EXCHANGE 
        PARTITION with indexes with different FREELIST /FREELIST GROUPS
    	1698789 	9.2.0.1 	Wrong results, ORA-1410, ORA-8103, OERI:25012 on SELECT of UNSCOPED REF with ROWID
    	2189615 	8.1.7.4, 9.0.1.4, 9.2.0.1 	OERI:25012 / OERI:504 [cache buffers chains] selecting from certain V$ tables
    	1784708 	8.1.7.3, 9.0.1.1, 9.2.0.1 	OERI:KCBGTCR_1/OERI:25012 accessing LONG / LONG RAW in a HASH CLUSTER
    	1872985 	9.0.1.2, 9.2.0.1 	Dump / OERI:25012 from BITMAP CONVERSION of INDEX on GLOBAL TEMPORARY TABLE
    	1968815 	9.0.1.2, 9.2.0.1 	OERI:25012 possible from SMON following DROP TABLESPACE command
    	2287815 	9.0.1.4, 9.2.0.1 	OERI:KTSPISCHNT / OERI:25012 after exchange PARTITION with bitmap managed segments
    	2184731 	8.1.7.4, 9.2.0.1 	OERI:25012 possible from index prefetch
    	1837529 	8.1.7.3, 9.0.1.1, 9.2.0.1 	OERI:KFTR2BZ_1/OERI:25012 from CREATE sub-partitioned local INDEX ONLINE
    	2214167 	9.0.1.4, 9.2.0.1 	OERI:25012 / wrong results possible after TRUNCATE of bitmap managed index
    	2212389 	9.0.1.4, 9.2.0.1 	OERI:25012 / Cursor frame memory corruption when cursor with BINDS aged out / reloaded
    	1788648 	8.1.7.3, 9.0.1.1, 9.2.0.1 	OERI:25012 [2147483647] possible selecting from certain V$ views
    	1527982 	8.1.7.2, 9.0.1.0 	OERI:25012 / Bitmap indextable mismatch after UPDATE of PARTITION KEY moves rows
    	1949273 	8.1.7.3, 9.0.1.0 	OERI:25012 / ORA-1555 / ORA-22922 accessing LOB data after ALTER TABLE MOVE lob
    	1264970 	8.1.7.1, 9.0.1.0 	OERI:25012 / OERI:6050 possible coalescing index with freelists
    	1396571 	8.0.6.3, 8.1.6.3, 8.1.7.1, 9.0.1.0 	OERI:25012 possible importing with TRANSPORT_TABLESPACE=Y
    	1678963 	8.1.7.2, 9.0.1.0 	OERI:25012 / OERI:4142 possible on TRUNCATE of a table with a CLOB column
    	1297674 	8.0.6.2, 8.1.6.3, 8.1.7.0 	OERI:25012 Analyzing partitioned table with ESTIMATE
    	1138239 	8.1.6.2, 8.1.7.0 	OERI-25012 possible on direct read from plugged in (transportable) tablespace
    	1228658 	8.1.6.2, 8.1.7.0 	Create INDEX on SNAPSHOT/MV can produce corrupt index (OERI:13004 / OERI:25012 / ORA-1499)
    	1108002 	8.1.7.0 	OERI:25012 when DBMS_STATS tries to gather stats for a GLOBAL TEMPORARY table
    	986928 	8.1.7.0 	DBMS_SPACE.UNUSED_SPACE reports OERI:25012 for TEMPORARY tables
    	1312233 	8.1.6.3, 8.1.7.0 	OERI:25012 / OERI:KCBGCUR_1 possible on PQ STAR query with PARTITIONED fact table
    	718499 	8.0.6.1, 8.1.5.0 	OERI:25012 possible on OPS parallel create index
    	677243 	8.0.6.0 	OERI:kdddgbX on DELETE / UPDATE / SELECT for UPDATE with an invalid ROWID
    	679817 	8.0.5.1 	OERI:25012 from view containing 2+ LOB columns
    	595698 	8.0.4.3, 8.0.5.0 	DELETE on table with many child constraints may dump.

        '*' against a bug indicates that an alert exists for that issue.
        '+' indicates a particularly notable bug.
        'P' indicates a port specific bug.
        "OERI:nnnnn" is used as shorthand for ORA-600 [nnnnn]. 

Symptom(s)
~~~~~~~~~~

ORA-600 [25012] errors with same first argument and different
second arguments (second arguments changes during every SELECT Run)

Eg.:

ORA-600 [25012], [5], [1023], [], [], [], []


The first argument corresponds to a tablespace and second argument
corresponds to a file number.

The tablespace points to an index tablespace


Cause
~~~~~

Bug:2184731


Fix
~~~~

Bug:2184731 is fixed in 8.1.7.4 and 9.2

The workaround is to set _db_file_noncontig_mblock_read_count=1.


References
~~~~~~~~~~

Note:100073.1  ORA-600 [25012] "Relative to Absolute File Number 
                 Conversion Error"

Bug:2184731    ORA-600 [25012] WHEN SELECT FROM A TABLE USING AN INDEX

Most of library cache latches have gone in 11g

10g

SELECT t1.ksllasnam "parent_name",
t2.ksllwnam "location"
FROM x$ksllw t2, x$kslwsc t1
WHERE t2.indx = t1.indx
AND ksllasnam like '%library cache%'

parent_name location
-------------------------------------------------- ----------------------------------------------------------------
library cache kqlftc
library cache kqlmbfre: parent
library cache kqlmbpil: parent
library cache kqlmbinv: parent
library cache kqlmbfre: child: in loop
library cache kqlmbfre: child: no obj to free
library cache pin kqlmbpil: child
library cache pin kqlmbpil: child: free and continue
library cache pin kqlmbp: child
library cache pin kqlmbpil: child: post parent after timeout
library cache kgluka: parent
library cache kglScanDependency
library cache kglscn: parent
library cache kglsca: parent
library cache kglLockCursor
library cache kgldrp: child
library cache kgldrx: child
library cache kgldmc: parent
library cache kglhdiv: child
library cache kglivl: parent
library cache kglidp: child
library cache kgldtld: parent
library cache kgldte: parent
library cache kglilf: parent
library cache kglmvsca: parent
library cache kgldte: child 0
library cache kglget: KGLDSBYA
library cache kglget: set reserved lock
library cache kglget: hash collision
library cache kglget: unpin heap 0
library cache kglpin: child: KGLMX
library cache kglupi: child: outside loop
library cache kglkep: child
library cache kglukp: child
library cache kglupc: child
library cache kglgfc: child
library cache kglalt: child
library cache kglupd: child
library cache kglsts: child
library cache kglpur: child
library cache kglprg: child
library cache kglini: child
library cache kglver: child
library cache kglivl: child
library cache kglivr: child
library cache kgldte: child
library cache kglhdbrnl: child
library cache kglobr: child
library cache kglpnc: child
library cache kglati
library cache kglpim
library cache kglupkp
library cache kgllfr
library cache kglnti
library cache kglic
library cache kglpnp: child
library cache kglhfr: child
library cache kglrtl
library cache kglpin
library cache kglswp
library cache kgldti: 2child
library cache kgldtld: 2child
library cache kgl 2child pad56
library cache kgllkal: parent
library cache kglhdal: parent
library cache kglhdunp: parent: flush
library cache kglhdiv0: parent: invalidate
library cache kglhdbr0: parent: invalidate
library cache kgllldl: child:
library cache kglhdbr: child
library cache kgl parent pad2132
library cache kglhdgn: child:
library cache kglhdgc: child:
library cache kglhdgh: child:
library cache kglobpn: child:
library cache kglpndl: child: before processing
library cache kglhdiv0: child
library cache kglpndl: child: after processing
library cache kglobld
library cache kglpin: child: heap processing
library cache kglpnal: 2child
library cache load lock kglllal: llatch
library cache lock kglget: un-set reserved lock
library cache lock kgllkal: child: multiinstance
library cache lock kgllkal: child: in loop
library cache lock kgllkal: child: deadlk det: error
library cache lock kgllkdl: free heap 0
library cache lock kgllkdl: child: cleanup
library cache lock kgllkdl: child: multiinstance
library cache lock kgllkdl: child: no lock handle
library cache lock kgllkc: child
library cache lock kglpsl: child
library cache lock kglhdbrnl: child
library cache lock kglhdcb: child
library cache lock ksucallcbksafely: kqllhng
library cache pin ksucallcbksafely: kqlphng
library cache load lock ksucallcbksafely: kqldlhn
library cache pin kglobld: child:
library cache pin kgllkdl: child: free pin
library cache pin kglpnal: child: alloc space
library cache pin kglpnal: child: check granted
library cache pin kglpndl: child: multiinstance
library cache pin kglpnck
library cache pin kglupc
library cache pin kglpin: child: KGLMS
library cache pin kglupi: child: within loop
library cache pin kgldnp: child
library cache pin kglpnc: child
library cache pin kglpnp: child
library cache pin kglobf0
library cache pin kglpndl
library cache pin dummy
library cache pin kglivl0
library cache hash chains kglpnrel
library cache hash chains kglpnget
library cache hash chains spare
library cache pin allocation kglpnrel
library cache pin allocation kglpnget
library cache pin allocation spare1
library cache pin allocation spare2
library cache lock allocation kgllkrel
library cache lock allocation kgllkget
library cache lock allocation spare1
library cache lock allocation spare2
library cache load lock kglllal: llatch: in loop
library cache load lock kgllldl: llatch
library cache kgl 2child pad2137
library cache kgl 2child pad2138
library cache kksftc

11g

SELECT t1.ksllasnam "parent_name",
t2.ksllwnam "location"
FROM x$ksllw t2, x$kslwsc t1
WHERE t2.indx = t1.indx
AND ksllasnam like '%library cache%'

parent_name location
--------------------------------------- ---------------------------------------------
library cache load lock kglllal: llatch
library cache load lock ksucallcbksafely: kqldlhn
library cache load lock kglllal: llatch: in loop
library cache load lock kglllal: llatch: in loop 2
library cache load lock kgllldl: llatch

latch:
library cache lock
library cache lock allocation
library cache pin
library cache pin allocation
library cache

ALL above latches have gone(latch library cache load lock left), library cache lock/pin don't need acquire library cache any more in 11g ;

library cache lock/pin structure now are protected by Library cache type mutex also.
But We can not find out how kglpin/kgllkdl and else function are utlized with mutex from any V$/X$ dynamic views.
Mutex is more black-box than Latch.

SQL> select mutex_type ,location from v$mutex_sleep_history group by mutex_type,location;

MUTEX_TYPE LOCATION
-------------------------------- ---------------------------------------------
Library Cache kglhdgn2 106
Library Cache kglget1 1
Cursor Pin kksfbc [KKSCHLCREA]
Cursor Parent kksfbc [KKSPRTLOC1]
Library Cache kglrfcl1 79
Library Cache kgllkal1 80
Library Cache kgllkdl1 85
Cursor Pin kkslce [KKSCHLPIN2]
Library Cache kglhdgh1 64
Library Cache kglpin1 4
Library Cache kglpndl1 95
Cursor Pin kksfbc [KKSCHLPIN1]
Library Cache kglhdgn1 62
Library Cache kglpur1 28
Library Cache kglpnal1 90
Library Cache kglobpn1 71
Library Cache kglpnal2 91

[Repost]List of X$ Tables

This is a summary list of X$ Table Definitions - Last revision was 7.3.2
The main purpose of this note is to show the naming conventions.

[K]ernel Layer
[2]-Phase Commit
[G]lobal [T]ransaction [E]ntry
X$K2GTE  - Current 2PC tx
X$K2GTE2 - Current 2PC tx
[C]ache Layer
[B]uffer Management
Buffer [H]ash
X$BH - Hash Table
Buffer LRU Statistics
X$KCBCBH - [C]urrent [B]uffers (buckets) - lru_statistics
X$KCBRBH - [R]ecent [B]uffers (buckets) - lru_extended
Buffer [WAIT]s
X$KCBWAIT  - Waits by block class
X$KCBFWAIT - Waits by File
[W]orking Sets - 7.3 or higher
X$KCBWDS - Set [D]escriptors
[C]ontrol File Management
[C]ontrol [F]ile List - 7.0.16 or higher
X$KCCCF - Control File Names & status
[D]atabase [I]nformation
X$KCCDI - Database Information
Data [F]iles
X$KCCFE - File [E]ntries ( from control file )
X$KCCFN - [F]ile [N]ames
[L]og Files
X$KCCLE - Log File [E]ntries
X$KCCLH - Log [H]istory ( archive entries )
Thread Information
X$KCCRT - [R]edo [T]hread Information
[F]ile Management
X$KCFIO - File [IO] Statistics
[L]ock Manager Component ( LCK )
[H]ash and Bucket Tables - 7.0.15 to 7.1.1, and 7.2.0 or higher
X$KCLFH - File [H]ash Table
X$KCLFI - File Bucket Table
X$LE - Lock [E]lements
X$LE_STAT - Lock Conversion [STAT]istics
X$KCLFX - Lock Element [F]ree list statistics - 7.3 or higher
X$KCLLS - Per LCK free list statistics - 7.3 or higher
X$KCLQN - [N]ame (hash) table statistics - 7.3 or higher
[R]edo Component
[M]edia recovery  - kcra.h - 7.3 or higher
X$KCRMF - [F]ile context
X$KCRMT - [T]hread context
X$KCRMX - Recovery Conte[X]t
[F]ile read
X$KCRFX - File Read Conte[X]t -  7.3 or higher
Reco[V]ery Component
[F]ile [H]eaders
X$KCVFH - All file headers
X$KCVFHMRR - Files with [M]edia [R]ecovery [R]equired
X$KCVFHONL - [ONL]ine File headers
[K]ompatibility Management - 7.1.1 or higher
X$KCKCE - [C]ompatibility Segment [E]ntries
X$KCKTY - Compatibility [TY]pes
X$KCKFM - Compatibility [F]or[M]ats ( index into X$KCKCE )
[D]ata Layer
Sequence [N]umber Component
X$KDNCE - Sequence [C]ache [E]ntries - 7.2 or lower
[S]equence Enqueues - common area for enqueue objects
X$KDNSSC - [C]ache Enqueue Objects - 7.2 or lower
X$KDNSSF - [F]lush Enqueue Objects - 7.2 or lower
X$KDNST - Cache [ST]atistics - 7.2 or lower
Inde[X] Block Component
X$KDXHS - Index [H]i[S]togram
X$KDXST - Index [ST]atistics
[G]eneric Layer
[H]eap Manager
X$KGHLU - State (summary) of [L]R[U] heap(s) - defined in ksmh.h
[I]nstantiation Manager
[C]ursor [C]ache
X$KGICC - Session statistics - defined in kqlf.h
X$KGICS - System wide statistics - defined in kqlf.h
[L]ibrary Cache Manager  ( defined and mapped from kqlf )
Bind Variables
X$KKSBV - Library Object [B]ind [V]ariables
Object Cache
X$KGLOB - All [OB]jects
X$KGLTABLE   - Filter for [TABLE]s
X$KGLBODY    - Filter for [BODY] ( packages )
X$KGLTRIGGER - Filter for [TRIGGER]s
X$KGLINDEX   - Filter for [INDEX]es
X$KGLCLUSTER - Filter for [CLUSTER]s
X$KGLCURSOR  - Filter for [CURSOR]s
Cache Dependency
X$KGLDP - Object [D]e[P]endency table
X$KGLRD - [R]ead only [D]ependency table - 7.3 or higher
Object Locks
X$KGLLK - Object [L]oc[K]s
Object Names
X$KGLNA - Object [NA]mes (sql text)
X$KGLNA1 - Object [NA]mes (sql text) with newlines - 7.2.0 or higher
Object Pins
X$KGLPN - Object [P]i[N]s
Cache Statistics
X$KGLST - Library cache [ST]atistics
Translation Table
X$KGLTR - Address [TR]anslation
Access Table
X$KGLXS - Object Access Table
Authorization Table - 7.1.5 or higher
X$KGLAU - Object Authorization table
Latch Cleanup - 7.0.15 or higher
X$KGLLC - [L]atch [C]leanup for Cache/Pin Latches
[K]ompile Layer
[S]hared Objects
X$KKSAI - Cursor [A]llocation [I]nformation - 7.3.2 or higher
[L]oader
[L]ibrary
X$KLLCNT - [C]o[NT]rol Statistics
X$KLLTAB - [TAB]le Statistics
[M]ulti-Threaded Layer
[C]ircuit component
X$KMCQS - Current [Q]ueue [S]tate
X$KMCVC - [V]irtual [C]ircuit state
[M]onitor Server/dispatcher
[D]ispatcher
X$KMMDI - [D]ispatcher [I]nfo (status)
X$KMMDP - [D]ispatcher Config ( [P]rotocol info )
[S]erver
X$KMMSI - [S]erver [I]nfo ( status )
X$KMMSG - [SG]a info ( global statistics)
X$KMMRD - [R]equest timing [D]istributions
s[Q]l Version and Option Layer
Kernel [V]ersions
X$VERSION - Library versions
Kernel [O]ptions - 7.1.3 or higher
X$OPTION - Server Options
[Q]uery Layer
[D]ictionary Cache Management
X$KQDPG - [PG]a row cache cursor statistics
[F]ixed Tables/views Management
X$KQFCO - Table [CO]lumn definitions
X$KQFDT - [D]erived [T]ables
X$KQFSZ - Kernel Data structure type [S]i[Z]es
X$KQFTA - Fixed [TA]bles
X$KQFVI - Fixed [VI]ews
X$KQFVT - [V]iew [T]ext definition - 7.2.0 or higher
[R]ow Cache Management
X$KQRST - Cache [ST]atistics
X$KQRPD - [P]arent Cache [D]efinition - 7.1.5 or higher
X$KQRSD - [S]ubordinate Cache [D]efinition - 7.1.5 or higher
[S]ervice Layer
[B]ackground Management
[D]etached Process
X$KSBDD - Detached Process [D]efinition (info)
X$KSBDP - Detached [P]rocess Descriptor (name)
X$MESSAGES - Background Message table
[I]nstance [M]anagement - 7.3 or higher
X$KSIMAT - Instance [AT]tributes
X$KSIMAV - [A]ttribute [V]alues for all instances
X$KSIMSI - [S]erial and [I]nstance numbers
[L]ock Management
[E]vent Waits
X$KSLED - Event [D]escriptors
X$KSLEI - [I]nstance wide statistics since startup
X$KSLES - Current [S]ession statistics
[L]atches
X$KSLLD - Latch [D]escriptor (name)
X$KSLLT - Latch statistics [ + Child latches @ 7.3 or higher ]
X$KSLLW - Latch context ( [W]here ) descriptors - 7.3 or higher
X$KSLPO - Latch [PO]st statistics - 7.3 or higher
X$KSLWSC- No[W]ait and [S]leep [C]ount stats by Context -7.3 or higher
[M]emory Management
[C]ontext areas
X$KSMCX - E[X]tended statistics on usage - 7.3.1 or lower
Heap Areas
X$KSMSP - SGA Hea[P]
X$KSMPP - [P]GA Hea[P] - 7.3.2 and above
X$KSMUP - [U]GA Hea[P] - 7.3.2 and above
X$KSMHP - Any [H]ea[P] - 7.3.2 and above
X$KSMSPR- [S]hared [P]ool [R]eserved List - 7.1.5 or higher
[L]east recently used shared pool chunks
X$KSMLRU - LR[U] flushes from the shared pool
[S]GA Objects
X$KSMSD - Size [D]efinition for Fixed/Variable summary
X$KSMSS - Statistics (lengths) of SGA objects
SGA [MEM]ory
X$KSMMEM - map of the entire SGA - 7.2.0 or higher
X$KSMFSV - Addresses of [F]ixed [S]GA [V]ariables - 7.2.1 or higher
[P]arameter Component
X$KSPPI  - [P]arameter [I]nfo ( Names )
X$KSPPCV - [C]urrent Session [V]alues - 7.3.2 or above
X$KSPPSV - [S]ystem [V]alues - 7.3.2 or above
En[Q]ueue Management
X$KSQDN - Global [D]atabase [N]ame
X$KSQEQ - [E]n[Q]ueue Object
X$KSQRS - Enqueue [R]e[S]ource
X$KSQST - Enqueue [S]tatistics by [T]ype
[U]ser Management
[C]ost
X$KSUCF - Cost [F]unction (resource limit)
[L]icence
X$KSULL - Licence [L]imits
[L]anguage Manager
X$NLS_PARAMETERS - NLS parameters
X$KSULV - NLS [V]alid Values - 7.1.2 or higher
[MY] [ST]atistics
X$KSUMYSTA - [MY] [ST]atisics (current session)
[P]rocess Info
X$KSUPL - Process (resource) [L]imits
X$KSUPRLAT - [LAT]ch Holder
X$KSUPR - Process object
[R]esource
X$KSURU - Resource [U]sage
[S]tatistics
X$KSUSD - [D]escriptors (statistic names)
X$KSUSGSTA - [G]lobal [ST]atistics
[SE]ssions
X$KSUSECST - Session status for events
X$KSUSESTA - Session [STA]tistics
X$KSUSECON - [CON]nection Authentication - 7.2.1 or higher
X$KSUSE - [SE]ssion Info
X$KSUSIO - [S]ystem [IO] statistics per session
[T]imer
X$KSUTM - Ti[M]e in 1/100th seconds
Instance [X]
X$KSUXSINST - [INST]ance state
[T]race management
X$TRACE - Current traced events
X$TRACES - All possible traces
X$KSTEX - Code [EX]ecution - 7.2.1 or higher
E[X]ecution Management
Device/Node [A]ffinity - 7.3.2 and above
X$KSXAFA - Current File/Node Affinity
[T]ransaction Layer
Table [A]ccess [D]efinition
X$KTADM - D[M]L lock
[C]ontrol Component
X$KTCXB - Transaction O[B]ject
[S]or[T] Segments - 7.3 or higher
X$KTSTSSD - [S]ort [S]egment [D]escriptor - per tablespace statistics
[T]ablespace
X$KTTVS - [V]alid [S]aveundo
[U]ndo
X$KTURD - Inuse [D]escriptors
X$KTUXE - Transaction [E]ntry (table) - 7.3.2 or above
Performance Layer [V] - 7.0.16 or higher
[I]nformation tables
X$KVII - [I]nitialisation Instance parameters
X$KVIS - [S]izes of structure elements
X$KVIT - [T]ransitory Instance parameters
Security Layer [Z]
[D]ictionary Component
X$KZDOS - [OS] roles
[S]ecurity State
X$KZSPR - Enabled [PR]ivileges
X$KZSRO - Enabled [RO]les
[R]emote Logins - 7.1.1 or higher
X$KZSRT - [R]emote Password File [T]able entries
E[X]ecution Layer
Parallel Query (Execute [F]ast) - 7.1.1 or higher
[P]rocess and Queue Manager
Statistics - 7.1.3 or higher
X$KXFPYS - S[YS]tem Statistics
X$KXFPDP - [D]etached [P]rocess (slave) statistics
X$KXFQSROW - Table [Q]ueue Statistics - 7.3.2 or higher
[C]oordinator Component
X$KXFPCST - Query [ST]atistics
X$KXFPCMS - [M]essage [S]tatistics
X$KXFPCDS - [D]equeue [S]tatistics
[S]lave Component
X$KXFPSST - Query [ST]atistics
X$KXFPSMS - [M]essage [S]tatistics
X$KXFPCDS - [D]equeue [S]tatistics
[S]hared Cursor
X$KXSBD - [B]ind [D]ata - 7.3.2 and above
X$KXSCC - SQL [C]ursor [C]ache Data - 7.3.2 and above
[N]etwork Layer - 7.0.15 or higher
Network [CO]nnections
X$UGANCO - Current [N]etwork [CO]nnections

Script:Translate RDBA relative data block address

Script:Translate RDBA relative data block address

 

-- scripts/admin/rdba/rdba_to_file_block.sql
select trunc( to_number('&rdba','XXXXXXXX')
            / power(2,22) ) as rfile#
     , trunc(to_number('&rdba','XXXXXXXX')-
          trunc( to_number('&rdba','XXXXXXXX')
               / power(2,22))*power(2,22) ) as block#
  from dual
/

CREATE OR REPLACE FUNCTION getbfno (p_dba IN VARCHAR2)
   RETURN VARCHAR2
IS
   l_str   VARCHAR2 (255) DEFAULT NULL;
   l_fno   VARCHAR2 (15);
   l_bno   VARCHAR2 (15);
BEGIN
   l_fno :=
      DBMS_UTILITY.data_block_address_file (TO_NUMBER (LTRIM (p_dba, '0x'),
                                                       'xxxxxxxx'
                                                      )
                                           );
   l_bno :=
      DBMS_UTILITY.data_block_address_block (TO_NUMBER (LTRIM (p_dba, '0x'),
                                                        'xxxxxxxx'
                                                       )
                                            );
   l_str :=
         'datafile# is:'
      || l_fno
      || CHR (10)
      || 'datablock is:'
      || l_bno
      || CHR (10)
      || 'dump command:alter system dump datafile '
      || l_fno
      || ' block '
      || l_bno
      || ';';
   RETURN l_str;
END;
/

select getbfno('0x00400009') from dual;

Trace obtained enqueue information by set event 10704

Oracle事件10704可以帮助我们了解队列Enqueue的使用情况,使用方法如下:

[oracle@rh2 bdump]$ oerr ora 10704
10704, 00000, "Print out information about what enqueues are being obtained"
// *Cause:  When enabled, prints out arguments to calls to ksqcmi and
//          ksqlrl and the return values.
// *Action: Level indicates details:
//   Level: 1-4: print out basic info for ksqlrl, ksqcmi
//          5-9: also print out stuff in callbacks:  ksqlac, ksqlop
//          10+: also print out time for each line

SQL> oradebug setmypid;
Statement processed.

SQL> oradebug event 10704 trace name context forever,level 10;
Statement processed.

SQL> lock  table tm in share mode;
Table(s) Locked.

SQL> oradebug tracefile_name;
/s01/admin/G10R2/udump/g10r2_ora_28400.trc

ksqgtl *** CU-9fec6e30-00000000 mode=6 flags=0x10 timeout=300 ***
ksqgtl: no transaction
ksqgtl: use existing ksusetxn DID
ksqgtl:
        ksqlkdid: 0001-0017-00000008
*** 2011-05-07 21:17:16.139
*** ksudidTrace: ksqgtl
        ksusesdi:   0000-0000-00000000
        ksusetxn:   0001-0017-00000008
ksqgtl: RETURNS 0
*** 2011-05-07 21:17:16.160
ksqrcl: CU,9fec6e30,0
ksqrcl: returns 0
*** 2011-05-07 21:17:23.884
ksqgtl *** CU-9fec69f8-00000000 mode=6 flags=0x10 timeout=300 ***
ksqgtl: no transaction
ksqgtl: use existing ksusetxn DID
ksqgtl:
        ksqlkdid: 0001-0017-00000008
*** 2011-05-07 21:17:23.884
*** ksudidTrace: ksqgtl
        ksusesdi:   0000-0000-00000000
        ksusetxn:   0001-0017-00000008
ksqgtl: RETURNS 0
*** 2011-05-07 21:17:23.898
ksqrcl: CU,9fec69f8,0
ksqrcl: returns 0
*** 2011-05-07 21:17:23.899
ksqgtl *** TM-0000d06b-00000000 mode=4 flags=0x401 timeout=21474836 ***
ksqgtl: xcb=0xa69d0a00, ktcdix=2147483647, topxcb=0xa69d0a00
        ktcipt(topxcb)=0x0
*** 2011-05-07 21:17:23.899
ksucti: init txn DID from session DID 0001-0017-00000008
ksqgtl:
        ksqlkdid: 0001-0017-00000008
*** 2011-05-07 21:17:23.899

*** ksudidTrace: ksqgtl
        ktcmydid(): 0001-0017-00000008
        ksusesdi:   0000-0000-00000000
        ksusetxn:   0001-0017-00000008
ksqgtl: RETURNS 0

该10704事件可以配合10046事件一起诊断异常的队列锁问题,记以录之!

脚本:格式化的V$SQL_SHARED_CURSOR报告

The function generates a summary report of the v$sql_shared_cursor view and additional diagnostic information depending on the reason code.
Counts all the versions that have ‘Y’ in any of the columns and if any have all ‘N’ too.

This script may be useful to diagnose ORA-600 [17059] errors

Running the Script
— Generate reports for all cursors with more than 100 versions using SQL_ID (10g and up)
select b.* from v$sqlarea a ,table(version_rpt(a.sql_id)) b where loaded_versions >=100;

— Generate reports for all cursors with more than 100 versions using HASH_VALUE
select b.* from v$sqlarea a ,table(version_rpt(null,a.hash_value)) b where loaded_versions>=100;

— Generate the report for cursor with sql_id cyzznbykb509s
select * from table(version_rpt(‘cyzznbykb509s’));

version_rpt3_12.sql

沪ICP备14014813号

沪公网安备 31010802001379号