oracle_abnormally down

 

 

Thu Aug 2 06:12:54 2007
ORA-01555 caused by SQL statement below (Query Duration=60017 sec, SCN: 0x0000.295b8a29):
Thu Aug 2 06:12:54 2007
select a.d_srccaption d_caption, B.d_BillName,b.d_date,b.d_id,b.d_WkNodeID, '' d_actorid,'' d_actor,d.d_FromDocID as d_SrcID from out_wktask1 A, out_Finance B, s0391 C,s0392 d where A.d_CoID=B.d_CoID and A.d_srcbillname=B.d_BillName and A.d_SrcNodeID=B.d_WkNodeID and A.d_ID=B.d_ID and A.d_status='8' and A.d_desbillname='TFMMWHOUTCODE' and B.d_FiSet='GGRK01' and B.d_WkNodeID='CWOR20021206105628' and B.d_billname in('TFSDORDER','TFSDORDER02') and C.d_docID=A.d_ID and c.d_WkNodeID='CWOR20021206105628' and C.d_Date>=to_date('2007-07-29','YYYY-MM-DD') and C.d_Date<=to_date('2007-07-29','YYYY-MM-DD') and B.d_Date>=to_date('2007-07-29','YYYY-MM-DD') and B.d_Date<=to_date('2007-07-29','YYYY-MM-DD') Order By d.d_FromDocID
Thu Aug 2 06:26:20 2007
ORA-01555 caused by SQL statement below (Query Duration=60917 sec, SCN: 0x0000.295b88ee):
Thu Aug 2 06:26:20 2007
select a.d_srccaption d_caption, B.d_BillName,b.d_date,b.d_id,b.d_WkNodeID, '' d_actorid,'' d_actor,d.d_FromDocID as d_SrcID from out_wktask1 A, out_Finance B, s0391 C,s0392 d where A.d_CoID=B.d_CoID and A.d_srcbillname=B.d_BillName and A.d_SrcNodeID=B.d_WkNodeID and A.d_ID=B.d_ID and A.d_status='8' and A.d_desbillname='TFMMWHOUTCODE' and B.d_FiSet='GGRK01' and B.d_WkNodeID='CWOR20021206105628' and B.d_billname in('TFSDORDER','TFSDORDER02') and C.d_docID=A.d_ID and c.d_WkNodeID='CWOR20021206105628' and C.d_Date>=to_date('2007-07-29','YYYY-MM-DD') and C.d_Date<=to_date('2007-07-29','YYYY-MM-DD') and B.d_Date>=to_date('2007-07-29','YYYY-MM-DD') and B.d_Date<=to_date('2007-07-29','YYYY-MM-DD') Order By d.d_FromDocID
//表名|记录数：out_wktask1|1329917;out_Finance|273167;s0391|3360662;s0391|1636887;查询所产生的笛卡尔乘积和排序数据量太大，占用大量内存和CPU资源。
Thu Aug 2 07:14:40 2007
OER 7451 in Load Indicator : Error Code = Additional information: 1 !
Thu Aug 2 07:14:40 2007
Errors in file /opt/oracle/admin/DB/bdump/db_lgwr_855.trc:
ORA-00313: open failed for members of log group 2 of thread 1
ORA-00312: online log 2 thread 1: '/opt/oracle/oradata/DB/redo02.log'
ORA-27041: unable to open file
//可能原因：1.打开数据文件过多（目前有198个数据文件）；2.排序区内存过小导致启用虚拟内存，产生大量磁盘虚拟内存页面，被linux认为文件打开。
//查询linux打开文件数限制：
// [oracle@ggrkdbsvr oracle]$ ulimit -a
// core file size (blocks) 0
// data seg size (kbytes) unlimited
// file size (blocks) unlimited
// max memory size (kbytes) unlimited
// open files 1024
// pipe size (512 bytes) 8
// stack size (kbytes) 8192
// cpu time (seconds) unlimited
// max user processes 16000
// virtual memory (kbytes) unlimited
// 或查看/etc/security/limits.conf
// 或查看/proc/sys/fs/file-nr
// 8192 61 8192
//查询linux当前打开文件数：lsof |wc -l
//当前打开文件数:9482
Linux Error: 23: Too many open files in system
Additional information: 2
Thu Aug 2 07:14:40 2007
Errors in file /opt/oracle/admin/DB/bdump/db_lgwr_855.trc:
ORA-00313: open failed for members of log group 2 of thread 1
ORA-00312: online log 2 thread 1: '/opt/oracle/oradata/DB/redo02.log'
ORA-27041: unable to open file
Linux Error: 23: Too many open files in system
Additional information: 2
Thu Aug 2 07:14:40 2007
LGWR: terminating instance due to error 313
Instance terminated by LGWR, pid = 855