Hello guys,
I have the following Oracle Release 10.2.0.1.0 server configuration:
- Windows 2003 Server SP2
- 3,25 GB RAM (I think that is a 4 GB but this is a windows 2003 standard edition)
- 2 Intel Xeon Quad core
- 3 disk partitions (C -> 40GB, 25 GB free / E -> 100 GB 85 GB free / F 300 GB -> 270 GB free)
The PRISM instance configuration is:
Archivelog: TRUE
SGA Size: 584 MB
Actual PGA Size: 40 MB
Services running (OracleDBConsolePRISM, OracleOraDb10g_home1TNSListener, OracleServeicePRISM)
The problem is that there are just 5 users that use this database, but there are a lot of errors in the alert.log that makes the database not available, the error messages are like this:
Mon Feb 16 16:31:24 2009
Errors in file e:\oracle\product\10.2.0\admin\prism\bdump\prism_j001_11416.trc:
ORA-12012: error on auto execute of job 42567
ORA-00376: file ORA-00376: file 3 cannot be read at this time
ORA-01110: data file 3: 'E:\ORACLE\PRODUCT\10.2.0\ORADATA\PRISM\SYSAUX01.DBF'
ORA-06512: at "EXFSYS.DBMS_RLMGR_DR", line 15
ORA-06512: at line 1
cannot be read at this time
The content of prism_j001_11416.trc is:
Dump file e:\oracle\product\10.2.0\admin\prism\bdump\prism_mmon_4620.trc
Mon Feb 16 11:07:33 2009
ORACLE V10.2.0.1.0 - Production vsnsta=0
vsnsql=14 vsnxtr=3
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
Windows Server 2003 Version V5.2 Service Pack 2
CPU : 8 - type 586, 2 Physical Cores
Process Affinity : 0x00000000
Memory (Avail/Total): Ph:1981M/3325M, Ph+PgF:3612M/5221M, VA:1278M/2047M
Instance name: prism
Redo thread mounted by this instance: 1
Oracle process number: 11
Windows thread id: 4620, image: ORACLE.EXE (MMON)
*** SERVICE NAME:(SYS$BACKGROUND) 2009-02-16 11:07:33.480
*** SESSION ID:(161.1) 2009-02-16 11:07:33.480
KEWRCTLRD: OCIStmtFetch Error. ctl_dbid= 1515633963, sga_dbid= 1515633963
KEWRCTLRD: Retcode: -1, Error Message: ORA-00376: file 3 cannot be read at this time
ORA-01110: data file 3: 'E:\ORACLE\PRODUCT\10.2.0\ORADATA\PRISM\SYSAUX01.DBF'
*** SQLSTR: total-len=328, dump-len=240,
STR={select snap_interval, retention,most_recent_snap_time, most_recent_snap_id, status_flag, most_recent_purge_time, most_recent_split_id, most_recent_split_time, mrct_snap_time_num, mrct_purge_time_num, snapint_num, retention_num, swrf_version}
*** kewrwdbi_1: Error=13509 encountered during run_once
keaInitAdvCache: failed, err=604
02/16/09 11:07:33 >ERROR: exception at dbms_ha_alerts_prvt.post_instance_up308: SQLCODE -13917,ORA-13917: Posting system
alert with reason_id 135 failed with code [5] [post_error]
02/16/09 11:07:33 >ERROR: exception at dbms_ha_alerts_prvt.check_ha_resources637: SQLCODE -13917,ORA-13917: Posting syst
em alert with reason_id 136 failed with code [5] [post_error]
02/16/09 11:07:33 >parameter dump for dbms_ha_alerts_prvt.check_ha_resources
02/16/09 11:07:33 > - local_db_unique_name (PRISM)
02/16/09 11:07:33 > - local_db_domain (==N/A==)
02/16/09 11:07:33 > - rows deleted (0)
02/16/09 11:07:33 >ERROR: exception at dbms_ha_alerts_prvt.check_ha_resources637: SQLCODE -13917,ORA-13917: Posting syst
em alert with reason_id 136 failed with code [5] [post_error]
02/16/09 11:07:33 >parameter dump for dbms_ha_alerts_prvt.check_ha_resources
02/16/09 11:07:33 > - local_db_unique_name (PRISM)
02/16/09 11:07:33 > - local_db_domain (==N/A==)
02/16/09 11:07:33 > - rows deleted (0)
*** 2009-02-16 11:07:41.293
****KELR Apply Log Failed, return code 376
*** 2009-02-16 11:08:35.294
****KELR Apply Log Failed, return code 376
*** 2009-02-16 11:09:38.294
****KELR Apply Log Failed, return code 376
*** 2009-02-16 11:10:41.295
****KELR Apply Log Failed, return code 376
*** 2009-02-16 11:11:44.296
****KELR Apply Log Failed, return code 376
*** 2009-02-16 11:12:29.328
And this is an extract from listener.log file:
TNSLSNR for 32-bit Windows: Version 10.2.0.1.0 - Production on 16-FEB-2009 11:05:44
Copyright (c) 1991, 2005, Oracle. All rights reserved.
System parameter file is e:\oracle\product\10.2.0\db_1\network\admin\listener.ora
Log messages written to e:\oracle\product\10.2.0\db_1\network\log\listener.log
Trace information written to e:\oracle\product\10.2.0\db_1\network\trace\listener.trc
Trace level is currently 0
Started with pid=9212
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=akscl-mfs15.am.enterdir.com)(PORT=1521)))
Listener completed notification to CRS on start
TIMESTAMP * CONNECT DATA [* PROTOCOL INFO] * EVENT [* SID] * RETURN CODE
16-FEB-2009 11:07:25 * service_register * prism * 0
16-FEB-2009 11:07:31 * service_update * prism * 0
16-FEB-2009 11:07:34 * service_update * prism * 0
16-FEB-2009 11:07:37 * service_update * prism * 0
16-FEB-2009 11:07:57 * (CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=PRISM)(CID=(PROGRAM=\\akscl-mfs15\PRISMPM\PRISMPM.EXE)(HOST=xx-W20972)(USER=CSC3157))) * (ADDRESS=(PROTOCOL=tcp)(HOST=144.180.225.7)(PORT=1607)) * establish * PRISM * 0
16-FEB-2009 11:07:58 * service_update * prism * 0
16-FEB-2009 11:07:58 * (CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=PRISM)(CID=(PROGRAM=\\akscl-mfs15\PRISMPM\PRISMPM.EXE)(HOST=xx-W20972)(USER=CSC3157))) * (ADDRESS=(PROTOCOL=tcp)(HOST=144.180.225.7)(PORT=1608)) * establish * PRISM * 0
16-FEB-2009 11:07:58 * (CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=PRISM)(CID=(PROGRAM=\\akscl-mfs15\PRISMPM\PRISMPM.EXE)(HOST=xx-W20972)(USER=CSC3157))) * (ADDRESS=(PROTOCOL=tcp)(HOST=144.180.225.7)(PORT=1612)) * establish * PRISM * 0
16-FEB-2009 11:07:59 * (CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=PRISM)(CID=(PROGRAM=\\akscl-mfs15\PRISMPM\PRISMPM.EXE)(HOST=xx-W20972)(USER=CSC3157))) * (ADDRESS=(PROTOCOL=tcp)(HOST=144.180.225.7)(PORT=1613)) * establish * PRISM * 0
16-FEB-2009 11:07:59 * (CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=PRISM)(CID=(PROGRAM=\\akscl-mfs15\PRISMPM\PRISMPM.EXE)(HOST=xx-W20972)(USER=CSC3157))) * (ADDRESS=(PROTOCOL=tcp)(HOST=144.180.225.7)(PORT=1614)) * establish * PRISM * 0
16-FEB-2009 11:08:01 * service_update * prism * 0
16-FEB-2009 15:05:26 * (CONNECT_DATA=(SERVICE_NAME=PRISM)(CID=(PROGRAM=C:\Program Files\Microsoft Office\OFFICE11\EXCEL.EXE)(HOST=xx-W21498)(USER=csc2682))) * (ADDRESS=(PROTOCOL=tcp)(HOST=144.180.227.131)(PORT=1999)) * establish * PRISM * 0
16-FEB-2009 15:05:27 * (CONNECT_DATA=(SERVICE_NAME=PRISM)(CID=(PROGRAM=C:\Program Files\Microsoft Office\OFFICE11\EXCEL.EXE)(HOST=xx-W21498)(USER=csc2682))) * (ADDRESS=(PROTOCOL=tcp)(HOST=144.180.227.131)(PORT=2000)) * establish * PRISM * 0
16-FEB-2009 17:33:26 * (CONNECT_DATA=(CID=(PROGRAM=)(HOST=__jdbc__)(USER=))(SERVICE_NAME=PRISM)) * (ADDRESS=(PROTOCOL=tcp)(HOST=144.180.227.18)(PORT=2513)) * establish * PRISM * 0
16-FEB-2009 17:33:26 * (CONNECT_DATA=(CID=(PROGRAM=)(HOST=__jdbc__)(USER=))(SERVICE_NAME=PRISM)) * (ADDRESS=(PROTOCOL=tcp)(HOST=144.180.227.18)(PORT=2514)) * establish * PRISM * 0
16-FEB-2009 17:33:27 * (CONNECT_DATA=(CID=(PROGRAM=)(HOST=__jdbc__)(USER=))(SERVICE_NAME=PRISM)) * (ADDRESS=(PROTOCOL=tcp)(HOST=144.180.227.18)(PORT=2515)) * establish * PRISM * 0
16-FEB-2009 17:33:27 * (CONNECT_DATA=(CID=(PROGRAM=)(HOST=__jdbc__)(USER=))(SERVICE_NAME=PRISM)) * (ADDRESS=(PROTOCOL=tcp)(HOST=144.180.227.18)(PORT=2516)) * establish * PRISM * 0
16-FEB-2009 17:33:27 * (CONNECT_DATA=(CID=(PROGRAM=)(HOST=__jdbc__)(USER=))(SERVICE_NAME=PRISM)) * (ADDRESS=(PROTOCOL=tcp)(HOST=144.180.227.18)(PORT=2517)) * establish * PRISM * 0
16-FEB-2009 17:33:27 * (CONNECT_DATA=(CID=(PROGRAM=)(HOST=__jdbc__)(USER=))(SERVICE_NAME=PRISM)) * (ADDRESS=(PROTOCOL=tcp)(HOST=144.180.227.18)(PORT=2518)) * establish * PRISM * 0
16-FEB-2009 17:33:27 * (CONNECT_DATA=(CID=(PROGRAM=)(HOST=__jdbc__)(USER=))(SERVICE_NAME=PRISM)) * (ADDRESS=(PROTOCOL=tcp)(HOST=144.180.227.18)(PORT=2519)) * establish * PRISM * 0
16-FEB-2009 17:33:27 * (CONNECT_DATA=(CID=(PROGRAM=)(HOST=__jdbc__)(USER=))(SERVICE_NAME=PRISM)) * (ADDRESS=(PROTOCOL=tcp)(HOST=144.180.227.18)(PORT=2520)) * establish * PRISM * 0
16-FEB-2009 17:33:27 * (CONNECT_DATA=(CID=(PROGRAM=)(HOST=__jdbc__)(USER=))(SERVICE_NAME=PRISM)) * (ADDRESS=(PROTOCOL=tcp)(HOST=144.180.227.18)(PORT=2521)) * establish * PRISM * 0
16-FEB-2009 17:33:27 * (CONNECT_DATA=(CID=(PROGRAM=)(HOST=__jdbc__)(USER=))(SERVICE_NAME=PRISM)) * (ADDRESS=(PROTOCOL=tcp)(HOST=144.180.227.18)(PORT=2523)) * establish * PRISM * 0
16-FEB-2009 17:33:27 * (CONNECT_DATA=(CID=(PROGRAM=)(HOST=__jdbc__)(USER=))(SERVICE_NAME=PRISM)) * (ADDRESS=(PROTOCOL=tcp)(HOST=144.180.227.18)(PORT=2524)) * establish * PRISM * 0
16-FEB-2009 17:33:27 * (CONNECT_DATA=(CID=(PROGRAM=)(HOST=__jdbc__)(USER=))(SERVICE_NAME=PRISM)) * (ADDRESS=(PROTOCOL=tcp)(HOST=144.180.227.18)(PORT=2525)) * establish * PRISM * 0
16-FEB-2009 17:33:27 * (CONNECT_DATA=(CID=(PROGRAM=)(HOST=__jdbc__)(USER=))(SERVICE_NAME=PRISM)) * (ADDRESS=(PROTOCOL=tcp)(HOST=144.180.227.18)(PORT=2526)) * establish * PRISM * 0
16-FEB-2009 17:33:27 * (CONNECT_DATA=(CID=(PROGRAM=)(HOST=__jdbc__)(USER=))(SERVICE_NAME=PRISM)) * (ADDRESS=(PROTOCOL=tcp)(HOST=144.180.227.18)(PORT=2527)) * establish * PRISM * 0
16-FEB-2009 17:33:27 * (CONNECT_DATA=(CID=(PROGRAM=)(HOST=__jdbc__)(USER=))(SERVICE_NAME=PRISM)) * (ADDRESS=(PROTOCOL=tcp)(HOST=144.180.227.18)(PORT=2528)) * establish * PRISM * 0
16-FEB-2009 17:33:27 * (CONNECT_DATA=(CID=(PROGRAM=)(HOST=__jdbc__)(USER=))(SERVICE_NAME=PRISM)) * (ADDRESS=(PROTOCOL=tcp)(HOST=144.180.227.18)(PORT=2529)) * establish * PRISM * 0
16-FEB-2009 17:33:27 * (CONNECT_DATA=(CID=(PROGRAM=)(HOST=__jdbc__)(USER=))(SERVICE_NAME=PRISM)) * (ADDRESS=(PROTOCOL=tcp)(HOST=144.180.227.18)(PORT=2530)) * establish * PRISM * 0
16-FEB-2009 17:33:29 * service_update * prism * 0
Please need help to solve this issue, best regards.