How to release still “killed“ status session in v$session? (释放killed的session) (二)

前几日发现有套数据库的连接数有些异常,查看当时的session时发现还存在大量的”killed”状态的会话存在v$session 视图中,确认几个小时前有从数据库做过alter system kill session

Sessions remains and increase cumulatively never close if you use DEDICATED with XA transaction + shared dblink.

A web application using Tuxedo always keep 50 long connection to local database which then issues a SELECT from local db to remote db over shared db link(it’s created last weekend). But to run for a period of time after the found the remote db sessions become full(ora-18)

,

Troubleshooting these are too many scheduler jobs(owner sys) name like KWQICPOSTMSGDEL_nn in DB 11g

I faced a very interesting question today, An oracle database 11.2.0.3 RAC database ON hpux one had to many scheduler jobs , And the job’s owner is sys, All jobs name all like ‘KWQICPOSTMSGDEL_’, All these jobs has no start date as well no interval.

The Oracle Listener ofen restart and ora.net1.network check fails on HPUX (监听频繁重启)

上个月有套库的listener总是自动stop, 因为是crs的资源又被agent重新拉起,前几次的监听故障中都提到过listener依赖于network资源, 以前也处理过网络打嗝可能会导致监听频繁重启的案例,本次参照该案例修改弱依赖的方式无法避免该问题

如果listener.log 不可写会怎样? DBGRL Error: SLERC_OERC, 48180

listener会记录通过监听访问数据库的信息在listener.log,包括tnsping 监听的,但是如果listener.log不可写,监听会不会挂? 前几天清理目录发现了这个有个目录增长比较快,在listener的trace 目录下(11g的 ADR目录diag/tnslsnr/anbob1/listener/trace)生成了一些奇怪的trace文件如下

Listener no register service& INTERMEDIATE status with “Not All Endpoints Registered” in 11gR2 RAC

是一套11GR2 的RAC 环境, CRSCTL CHECK CRS检查CRS 服务已无法通讯,当时也让他查询了crsd.bin 进程确认不存在了, 当时通知重启CRS便可以解决,但是后来通知客户端依旧有个节点无法连接,检查LISTNER 并没有注册任何SERVICE,而且当时也只监听在PUBLIC IP, 检查DB PARAMETER LOCAL_LISTENER 是绑定VIP,

,

Troubleshooting ora-07445 [__lwp_kill()+48] [SIGIOT] error and instance crash

Exception [type: SIGIOT, unknown code] [ADDR:0x6CA9] [PC:0xC0000000003125F0, __lwp_kill()+48] [exception issued by pid: 27817, uid: 1024] [flags: 0x0, count: 1]
Errors in file /oracle/app/oracle/diag/rdbms/anbob/anbob1/trace/anbob1_lms3_27817.trc (incident=704134):
ORA-07445: exception encountered: core dump [__lwp_kill()+48] [SIGIOT] [ADDR:0x6CA9] [PC:0xC0000000003125F0] [unknown code] []

,

After OS reboot, Ohasd(cssd) start fail is due to OLR corrupted

前几天帮助同事处理了个案例, 主机意外重启后数据库无法启动, 环境是11.2.0.3 standalone o […]

Troubleshooting Instance crash when modify db_cache_size, ora-600 [kmgs_pre_process_request_6]

ORA-00600: internal error code, arguments: [kmgs_pre_process_request_6], [6], [895], [0], [3], [0x459C1F3D8], [], []
Mon Dec 22 22:40:43 2014
MMAN: terminating instance due to error 822
Instance terminated by MMAN, pid = 31205

Troubleshooting ORA-12012&ORA-29283&ORA-06512 issue

ORA-12012: error on auto execute of job “ORACLE_OCM”.”MGMT_CONFIG_JOB_2_2″
ORA-29283: invalid file operation
ORA-06512: at “SYS.UTL_FILE”, line 536
ORA-29283: invalid file operation
ORA-06512: at “ORACLE_OCM.MGMT_DB_LL_METRICS”, line 2436
ORA-06512: at line 1

How to release still “killed“ status session in v$session?(释放killed的session)

最近在一套生产库上发现了几个已经killed的会话一直保持在v$session 会话中,会话是user type的连接,而且已经killed了很久,通过SPID 发现操作系统层面已不存在该进程, 下面是我多次尝试后最终释放,

kjfspseudorcfg and kjxgrrcfgchk some reason #

kjxgrrcfgchk: Initiating reconfig, reason=3 #######<<<<<<<<< kjxgrrcfgchk: COMM rcfg - Disk Vote Required kjfmReceiverHealthCB_CheckAll: Recievers are healthy.

,

Troubleshooting TNS-12547&TNS-12560 AIX error 32: Broken pipe caused by tcp socket leak

上周出现个蹊跷案例,最近一直在忙今天简单的记录一下, 中间件反馈数据库连接时失败,在数据库使用lsnrctl status 查看监听状态会发现Listener一会儿正常,一会儿报错,但是在Listener正常时可以看出listener的start date并没有restart过

Troubleshooting ora-12519 or ora-12516 ,listener service ‘blocked’, and wait event ‘latch: ges resource hash

通常当出现ora-12519 or 12516时都是因为数据库进程数超过了数据库参数processes 或 sessions 时, 并且通常在db alert 中出现ora-20 or ora-18 的错误信息,如果当时查看监听服务状态使用lsnrctl service 会发现service 当时是”blocked”状态

Index partition unusable and wait ‘kpodplck wait before retrying ORA-54’ event during direct sqlldr

最近有人遇到用SQL*Loader 向数据库加载数据时发现很慢,并且反映有几个分区表上的local索引总是递归的变成invalid or unusable,rebuild该分区失效索引,另一个索引分区又失效。

RMAN-06900&RMAN-06901 RMAN-00600 [3045] ORA-19921 when rman connect target

RMAN-00571: ===========================================================
RMAN-00554: 内部恢复管理器程序包初始化失败
RMAN-00600: internal error, arguments [3045] [] [] [] []

Troubleshooting RMAN-00600: internal error, arguments [13205] [] [] [] []

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of backup command at 04/11/2011 10:03:29
RMAN-00600: internal error, arguments [13205] [] [] [] []

Troubleshooting the crsd.bin and listener process terminal after eanble TCP.VALIDNODE_CHECKING in sqlnet.ora

Recently, we met several times Oracle Listener terminal […]

,

ORA-31693&ORA-31640&ORA-19505 and ORA-39126&ORA-01555&ORA-22924 when impdp 11g rac using parallel

ORA-31693: Table data object “CTCARD”.”RES_IMSI1104IMPORTANT” failed to load/unload and is being skipped due to error:
ORA-31640: unable to open dump file “/home/oracle/datapump/UIM04.DUMP” for read
ORA-19505: failed to identify file “/home/oracle/datapump/UIM04.DUMP”
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3

ORA-04063: package body “SYS.DBMS_SQLTUNE_INTERNAL” and compile body raise ORA-00904: “XMLAGG”

ORA-04063: package body “SYS.DBMS_SQLTUNE_INTERNAL” 有错误
ORA-06508: PL/SQL: 无法找到正在调用 : “SYS.DBMS_SQLTUNE_INTERNAL” 的程序单元
ORA-06512: 在 “SYS.DBMS_SQLTUNE”, line 5664
ORA-06512: 在 line 55