Daily Archives: 2012 年 06 月 08 日

autotrace显示Statistics很多信息为0

Posted on by

一朋友使用autotrace查看数据库执行计划发现结果如下,Statistics中很多信息为0,这个肯定是不正常现象,什么都可以为0,consistent gets也不可能为0.

SQL> set autot on
SQL> select count(*) from RACV_DATA.PARTY_DUMMY;

  COUNT(*)
----------
        47

Elapsed: 00:00:00.00

Execution Plan
----------------------------------------------------------
Plan hash value: 3621440939

--------------------------------------------------------------------------
| Id  | Operation          | Name        | Rows  | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |             |     1 |     3   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE    |             |     1 |            |          |
|   2 |   TABLE ACCESS FULL| PARTY_DUMMY |    47 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          0  consistent gets
          0  physical reads
          0  redo size
          0  bytes sent via SQL*Net to client
          0  bytes received via SQL*Net from client
          0  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

解决方法

SQL>  select owner,object_name from dba_objects where object_name='PLAN_TABLE';

no rows selected

SQL> @?/rdbms/admin/utlxplan.sql

Table created.

重新查看Statistics信息

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          7  consistent gets
          0  physical reads
          0  redo size
        522  bytes sent via SQL*Net to client
        524  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

记录一次ORA-00600[2252]故障解决

Posted on by

数据库alert日志报ORA-00600[2252]

Wed Jun 06 08:56:02 2012
Thread 1 cannot allocate new log, sequence 552
Checkpoint not complete
  Current log# 1 seq# 551 mem# 0: D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\REDO01.LOG
Thread 1 advanced to log sequence 552
  Current log# 2 seq# 552 mem# 0: D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\REDO02.LOG
Sun Jun 06 09:39:19 2010
Errors in file d:\oracle\product\10.2.0\admin\orcl\bdump\orcl_m000_3344.trc:
ORA-00600: 内部错误代码, 参数: [2252], [2834], [4076554712], [], [], [], [], []

Errors in file d:\oracle\product\10.2.0\admin\orcl\bdump\orcl_m000_3344.trc:
ORA-00600: 内部错误代码, 参数: [2252], [2834], [4076554712], [], [], [], [], []

Sun Jun 06 10:19:49 2010
Errors in file d:\oracle\product\10.2.0\admin\orcl\bdump\orcl_m000_4904.trc:
ORA-00600: 内部错误代码, 参数: [2252], [2834], [4076555573], [], [], [], [], []

Sun Jun 06 10:20:49 2010
Errors in file d:\oracle\product\10.2.0\admin\orcl\bdump\orcl_m000_5984.trc:
ORA-00600: 内部错误代码, 参数: [2252], [2834], [4076555594], [], [], [], [], []

Sun Jun 06 10:21:49 2010
Errors in file d:\oracle\product\10.2.0\admin\orcl\bdump\orcl_m000_4204.trc:
ORA-00600: 内部错误代码, 参数: [2252], [2834], [4076555614], [], [], [], [], []

Sun Jun 06 10:22:49 2010
Errors in file d:\oracle\product\10.2.0\admin\orcl\bdump\orcl_m000_5896.trc:
ORA-00600: 内部错误代码, 参数: [2252], [2834], [4076555634], [], [], [], [], []

Sun Jun 06 10:23:49 2010
Errors in file d:\oracle\product\10.2.0\admin\orcl\bdump\orcl_m000_4612.trc:
ORA-00600: 内部错误代码, 参数: [2252], [2834], [4076555654], [], [], [], [], []

Sun Jun 06 10:24:49 2010
Errors in file d:\oracle\product\10.2.0\admin\orcl\bdump\orcl_m000_4696.trc:
ORA-00600: 内部错误代码, 参数: [2252], [2834], [4076555676], [], [], [], [], []

Sun Jun 06 10:25:50 2010
Errors in file d:\oracle\product\10.2.0\admin\orcl\bdump\orcl_m000_5568.trc:
ORA-00600: 内部错误代码, 参数: [2252], [2834], [4076555696], [], [], [], [], []

Sun Jun 06 10:26:50 2010
Errors in file d:\oracle\product\10.2.0\admin\orcl\bdump\orcl_m000_5776.trc:
ORA-00600: 内部错误代码, 参数: [2252], [2834], [4076555716], [], [], [], [], []

--启动数据库
Mon Jun 07 09:18:39 2010
Starting ORACLE instance (normal)
LICENSE_MAX_SESSION = 0
LICENSE_SESSIONS_WARNING = 0
Mon Jun 07 09:18:49 2010
Picked latch-free SCN scheme 2
Using LOG_ARCHIVE_DEST_10 parameter default value as USE_DB_RECOVERY_FILE_DEST
Autotune of undo retention is turned on. 
IMODE=BR
ILAT =18
LICENSE_MAX_USERS = 0
SYS auditing is disabled
ksdpec: called for event 13740 prior to event group initialization
Starting up ORACLE RDBMS Version: 10.2.0.1.0.
System parameters with non-default values:
  processes                = 150
  __shared_pool_size       = 100663296
  __large_pool_size        = 12582912
  __java_pool_size         = 4194304
  __streams_pool_size      = 0
  spfile                   = D:\ORACLE\PRODUCT\10.2.0\DB_1\DBS\SPFILEORCL.ORA
  nls_language             = SIMPLIFIED CHINESE
  nls_territory            = CHINA
  sga_target               = 452984832
  control_files            = D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\CONTROL01.CTL
  db_block_size            = 8192
  __db_cache_size          = 327155712
  compatible               = 10.2.0.1.0
  db_file_multiblock_read_count= 16
  db_recovery_file_dest    = D:\oracle\product\10.2.0/flash_recovery_area
  db_recovery_file_dest_size= 2147483648
  undo_management          = AUTO
  undo_tablespace          = UNDOTBS1
  remote_login_passwordfile= EXCLUSIVE
  db_domain                = 
  dispatchers              = (protocol=TCP)
  shared_servers           = 1
  job_queue_processes      = 10
  audit_file_dest          = D:\ORACLE\PRODUCT\10.2.0\ADMIN\ORCL\ADUMP
  background_dump_dest     = D:\ORACLE\PRODUCT\10.2.0\ADMIN\ORCL\BDUMP
  user_dump_dest           = D:\ORACLE\PRODUCT\10.2.0\ADMIN\ORCL\UDUMP
  core_dump_dest           = D:\ORACLE\PRODUCT\10.2.0\ADMIN\ORCL\CDUMP
  db_name                  = orcl
  open_cursors             = 300
  pga_aggregate_target     = 149946368
PSP0 started with pid=3, OS id=3028
MMAN started with pid=4, OS id=3528
PMON started with pid=2, OS id=2772
DBW0 started with pid=5, OS id=816
CKPT started with pid=7, OS id=3372
SMON started with pid=8, OS id=2584
RECO started with pid=9, OS id=3976
CJQ0 started with pid=10, OS id=1912
MMON started with pid=11, OS id=624
Mon Jun 07 09:19:00 2010
starting up 1 dispatcher(s) for network address '(ADDRESS=(PARTIAL=YES)(PROTOCOL=TCP))'...
MMNL started with pid=12, OS id=2696
Mon Jun 07 09:19:00 2010
starting up 1 shared server(s) ...
LGWR started with pid=6, OS id=3128
Mon Jun 07 09:19:04 2010
alter database mount exclusive
Mon Jun 07 09:19:09 2010
Setting recovery target incarnation to 2
Mon Jun 07 09:19:10 2010
Successful mount of redo thread 1, with mount id 1248834568
Mon Jun 07 09:19:10 2010
Database mounted in Exclusive Mode
Completed: alter database mount exclusive
Mon Jun 07 09:19:10 2010
alter database open
Mon Jun 07 09:19:15 2010
Beginning crash recovery of 1 threads
 parallel recovery started with 2 processes
Mon Jun 07 09:19:18 2010
Started redo scan
Mon Jun 07 09:19:19 2010
Completed redo scan
 13 redo blocks read, 7 data blocks need recovery
Mon Jun 07 09:19:20 2010
Started redo application at
 Thread 1: logseq 552, block 28631
Mon Jun 07 09:19:20 2010
Recovery of Online Redo Log: Thread 1 Group 2 Seq 552 Reading mem 0
  Mem# 0 errs 0: D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\REDO02.LOG
Mon Jun 07 09:19:20 2010
Completed redo application
Mon Jun 07 09:19:20 2010
Completed crash recovery at
 Thread 1: logseq 552, block 28644, scn 12176013920948
 7 data blocks read, 7 data blocks written, 13 redo blocks read
Mon Jun 07 09:19:28 2010
Errors in file d:\oracle\product\10.2.0\admin\orcl\udump\orcl_ora_2688.trc:
ORA-00600: 内部错误代码, 参数: [2252], [2834], [4076604085], [], [], [], [], []

通过这些日志可以看出数据库一直在报ORA-00600[2252],在后来因某种原因数据库异常重启后启动不了.仔细观察可以发现系统显示的时间是2010年6月7日和当前时间相差了整整两年.
At any point in time, the Oracle Database calculates a “not to exceed” limit for the number of SCNs a database can have used, based on the number of seconds elapsed since 1988, multiplied by 16,384. This is known as the database’s current maximum SCN limit. Doing this ensures that Oracle Databases will ration SCNs over time, allowing over 500 years of data processing for any Oracle Database.

错误原因
根据错误提示计算scn(2834为现在系统的SCN WRAP，4076604085就是BASE)=2834*2^32+4076604085=12176013920949
根据数据库日志显示系统时间计算最大scn值:

SQL>select to_number( ((to_date('20100607 09:19:28','yyyymmdd hh24:mi:ss')-
  2 to_date('19880101','yyyymmdd'))*24*3600*16*1024),'999999999999999999') max_scn from dual;

   MAX_SCN
--------------
11598377254912

通过这里的计算可以知道数据库当前的SCN大于系统时间点上允许的最大时间的SCN,从而出现ORA-00600[2252]错误.

解决方法
知道了数据库报该错误的原因,那么解决该问题很简单,修改系统时间到正确的时间点即可

SQL> select to_number(((sysdate-to_date('19880101','yyyymmdd'))*24*3600*16*1024),
   2 '999999999999999999') max_scn from dual;

   MAX_SCN
--------------
12634899464192

该SCN大于数据库当前SCN所有数据库不会报ORA-00600[2252]错误可以正常启动.

记录另一起ORA-00600[13013]处理

Posted on by

发现ORA-00600[13013]错误
During the execution of an UPDATE statement, after several attempts (Arg [a] passcount) we are unable to get a stable set of rows that conform to the WHERE clause.

Fri Jun  1 03:00:33 2012
Errors in file /opt/oracle/admin/oraapp/bdump/oraapp_m000_12104.trc:
ORA-00600: internal error code, arguments: [13013], [5001], [8943], [12596577], [25], [12596578], [17], []
Sat Jun  2 03:01:05 2012
Errors in file /opt/oracle/admin/oraapp/bdump/oraapp_m000_1052.trc:
ORA-00600: internal error code, arguments: [13013], [5001], [8943], [12596577], [25], [12596578], [17], []
Sun Jun  3 15:00:50 2012
Errors in file /opt/oracle/admin/oraapp/bdump/oraapp_m000_13876.trc:
ORA-00600: internal error code, arguments: [13013], [5001], [8943], [12596577], [25], [12603219], [17], []
Mon Jun  4 03:01:05 2012
Errors in file /opt/oracle/admin/oraapp/bdump/oraapp_m000_7704.trc:
ORA-00600: internal error code, arguments: [13013], [5001], [8943], [12596577], [25], [12596578], [17], []
Tue Jun  5 03:00:35 2012
Errors in file /opt/oracle/admin/oraapp/bdump/oraapp_m000_27983.trc:
ORA-00600: internal error code, arguments: [13013], [5001], [8943], [12596577], [25], [12596578], [17], []
Wed Jun  6 03:01:07 2012
Errors in file /opt/oracle/admin/oraapp/bdump/oraapp_m000_19204.trc:
ORA-00600: internal error code, arguments: [13013], [5001], [8943], [12596577], [25], [12596578], [17], []
Thu Jun  7 03:00:37 2012
Errors in file /opt/oracle/admin/oraapp/bdump/oraapp_m000_7273.trc:
ORA-00600: internal error code, arguments: [13013], [5001], [8943], [12596577], [25], [12605556], [17], []

以前处理过一次ORA-600[13013],里面包含了各参数含义,这次也按照常规方法处理,分析如下:
1.通过trace文件找出对应表

*** 2012-06-01 03:00:33.325
ksedmp: internal or fatal error
ORA-00600: internal error code, arguments: [13013], [5001], [8943], [12596577], [25], [12596578], [17], []
Current SQL statement for this session:
UPDATE WRH$_SQL_BIND_METADATA SET snap_id = :lah_snap_id  WHERE dbid = :dbid    AND (SQL_ID) 
IN (SELECT STR1_KEWRATTR FROM X$KEWRATTRSTALE)

2.通过ORA-600[13013]中表示rdba参数找出表

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

     file#     block#
---------- ----------
         3      13665

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

OWNER      SEGMENT_NAME           SEGMENT_TYPE     EXTENT_ID    FILE_ID   BLOCK_ID    BLOCKS
---------- --------------------  --------------- --------------- ---------- -------  ------
SYS        SYS_LOB0000008933C00  LOBSEGMENT           7          3      13657         8    
SYS        WRH$_SQL_BIND_METADA  TABLE                1          3      13665         8

检查对象WRH$_SQL_BIND_METADA是否有坏块或者表和index不一致

SQL> analyze table SYS.WRH$_SQL_BIND_METADATA validate structure cascade online;

Table analyzed.

这里分析WRH$_SQL_BIND_METADA表正常,但是通过上面的查询证明WRH$_SQL_BIND_METADA的第一个extent的第一个数据块上可能出现问题,使得analyze未检查(自己猜猜,未做深入验证).针对这个问题,直接备份WRH$_SQL_BIND_METADATA表,truncate掉该表,然后重新插入数据(注意操作时间避开awr插入数据时间段)

create table SQL_BIND_METADATA_BAK
AS
SELECT * FROM SYS.WRH$_SQL_BIND_METADATA;

TRUNCATE TABLE SYS.WRH$_SQL_BIND_METADATA;

INSERT INTO SYS.WRH$_SQL_BIND_METADATA
SELECT * FROM SQL_BIND_METADATA_BAK;

DROP TABBLE SQL_BIND_METADATA_BAK PURGE;