Troubleshooting

当你在windows NTFS文件系统操作大的数据文件时，有时会遇到下面的错误:
The operating system returned error 665 (The requested operation could not be completed due to a file system limitation)

Shrink operations can be performed only on segments in locally managed tablespaces with automatic segment space management (ASSM). Within an ASSM tablespace. Restrictions on the shrink_clause.The shrink_clause is subject to the following restrictions…

insert into table “with” subquery Failing with the following internel error.
ORA-600: internal error code, arguments: [voprvl1], [], [], [], [], [], [], []

A description for this ORA-600 [2003] error is not yet published. ora-600 2663/2662 is a data block SCN is ahead of the current SCN.

上周也就是2013年的最后一天，突然客户反应有些WEB应用页面报错只提到了ora-28002, 我立即用sqlplus 登录提示是ora-28001,显然密码已经过期, 我清清楚记的才因为一起类似问题, 前不久把所有的oracle 数据库应用用户profile PASSWORD_LIFE_TIME改为unlimited，怎么还会这样呢?

Large objects (LOBs) are not supported in comparison conditions. However, you can use PL/SQL programs for comparisons on CLOB data.

skgpspawn failed:category = 27143, depinfo = 24, op = pipe, loc = skgpspawn2
…

alter system set memory_max_target=0 scope=spfile;
shutdown immediate
startup
SQL> show parameter target
…
memory_max_target big integer 3824M

Exception [type: SIGSEGV, Address not mapped to object] [ADDR:0x10] [PC:0x938E8C5, dbgtrReserveSpace()+99] [flags: 0x0, count: 1]
Errors in file /oracle/diag/rdbms/ora11g/ora11g/trace/ora11g_ora_13864.trc (incident=60801):
ORA-07445: exception encountered: core dump [dbgtrReserveSpace()+99] [SIGSEGV] [ADDR:0x10] [PC:0x938E8C5] [Address not mapped to object] []
Incident details in: /oracle/diag/rdbms/ora11g/ora11g/incident/incdir_60801/ora11g_ora_13864_i60801.trc
Use ADRCI or Support Workbench to package the incident.

ORA-24247: network access denied by access control list (ACL)
ORA-06512: at “SYS.UTL_TCP”, line 17
ORA-06512: at “SYS.UTL_TCP”, line 267

ORA-39083: Object type JOB failed to create with error:
ORA-00001: unique constraint (SYS.I_JOB_JOB) violated

因安装数据库时启用了ASMM,过了几天发现分配不太理想想禁用ASMM，手动调整shared_pool_size, 这是一个动态参数，修改后立即生效无需重启数据库实例，但修改时遇到了比较奇怪的现象。

今天有个项目系统负载突然变大，查到top sql是一个存储过程,执行计划缺少合理的索引，SQL是个update操作，于是kill 调用的几个session 建立一个索引，结果发现kill几个用户进程后实例挂了.下面是alert日志，可见instance crash前出现过死锁,就是那个update sql Thu Aug 15 11:08:04 2013 ORA-00060: Deadlock detected. More info in file /oracle/admin/icme/udump/icme_ora_24011.trc. Thu Aug 15 11:27:00 2013 ORA-00060: Deadlock detected. More info in file /oracle/admin/icme/udump/icme_ora_26527.trc. Thu Aug 15 11:27:03 2013 ORA-00060: Deadlock detected. More info in file /oracle/admin/icme/udump/icme_ora_26912.trc. Thu Aug 15 11:32:46 2013 ORA-00060: Deadlock detected. More info … Read more

Enabling Listener Trace ,TNS-01251 Oracle version 11g introduced the ADR or Automatic Diagnostic Repository feature,Trying to change the log file using the Lsnrctl utility fails with: TNS-01251: Cannot set trace/log directory under ADR. oracle@db231 trace]$ lsnrctl status LSNRCTL for Linux: Version 11.2.0.3.0 – Production on 15-AUG-2013 14:15:59 Copyright (c) 1991, 2011, Oracle. All rights reserved. … Read more

ORA-00600: internal error code, arguments: [ORA-00600: internal error code, arguments: [qkssao2s1], [], [], [], [], [], [], []

昨一好朋友问我开了审计，有些表但无审计记录。 下面我来还原这个问题 db_version oracle 10201 for linux SQL> show parameter audit NAME TYPE VALUE ———————————— ———– —————————— audit_file_dest string /u1/oracle/admin/ORCL/adump audit_sys_operations boolean FALSE audit_syslog_level string audit_trail string DB, EXTENDED audit select any table by access; SQL> select os_username,username,terminal,timestamp,owner,obj_name,audit_option from DBA_AUDIT_TRAIL order by 4; OS_USERNAM USERNAME TERMINAL TIMESTAMP OWNER OBJ_NAME AUDIT_OPTION ———- ———- ———- ——————- —————————— … Read more

1) Export the snapshots and then do a manual purge To purge the Table SYS.WRH$_ACTIVE_SESSION_HISTORY, you may first query the Snapshot History with the query below: select snap_id, begin_interval_time from sys.dba_hist_snapshot order by snap_id; Then, you choose the range of snapshot you want to delete, and you execute the following Procedure: exec dbms_workload_repository.drop_snapshot_range ( low_snap_id … Read more

Oracle Database provides a means for suspending, and later resuming,
the execution of large database operations in the event of space allocation failures.

今天看到一个案例，从10G升级到11GR2后cpu负载迅速上升，很快达到100%,通过OWI显示出现大量“Cursor: mutex S”事件，mutex S是发生在cursor cache上的序列化mutex