oracle 9i错误,请帮忙查一下原因(贴上ALERT.LOG)

lvd123456

Thu Apr 21 10:28:08 2005
Thread 1 advanced to log sequence 483
Thu Apr 21 10:28:08 2005
ARC0: Evaluating archive   log 2 thread 1 sequence 482
Thu Apr 21 10:28:08 2005
  Current log# 3 seq# 483 mem# 0: E:\ORACLE\ORADATA\JDC2004\REDO03.LOG
Thu Apr 21 10:28:08 2005
ARC0: Beginning to archive log 2 thread 1 sequence 482
Creating archive destination LOG_ARCHIVE_DEST_1: 'E:\ORACLE\ORA92\RDBMS\ARC00482.001'
ARC0: Completed archiving  log 2 thread 1 sequence 482
Dump file e:\oracle\admin\jdc2004\bdump\alert_jdc2004.log
Thu Apr 21 13:47:29 2005
ORACLE V9.2.0.5.0 - Production vsnsta=0
vsnsql=12 vsnxtr=3
Windows 2000 Version 5.0 Service Pack 4, CPU type 586
Thu Apr 21 13:47:29 2005
Starting ORACLE instance (normal)
Disable cache advisory with old cache parameters
Thu Apr 21 13:47:29 2005
Window memory size 1073741824
Thu Apr 21 13:47:29 2005
Minimum working set window size : 4096
LICENSE_MAX_SESSION = 0
LICENSE_SESSIONS_WARNING = 0
SCN scheme 2
Using log_archive_dest parameter default value
LICENSE_MAX_USERS = 0
SYS auditing is disabled
Starting up ORACLE RDBMS Version: 9.2.0.5.0.
System parameters with non-default values:
  processes                = 500
  timed_statistics         = TRUE
  shared_pool_size         = 268435456
  large_pool_size          = 58720256
  java_pool_size           = 58720256
  use_indirect_data_buffers= TRUE
  control_files            = E:\oracle\oradata\jdc2004\control01.ctl, E:\oracle\oradata\jdc2004\control02.ctl, E:\oracle\oradata\jdc2004\control03.ctl
  db_block_buffers         = 200000
  db_block_size            = 8192
  compatible               = 9.2.0.0.0
  log_archive_start        = TRUE
  db_file_multiblock_read_count= 16
  fast_start_mttr_target   = 300
  undo_management          = AUTO
  undo_tablespace          = UNDOTBS1
  undo_retention           = 10800
  remote_login_passwordfile= EXCLUSIVE
  db_domain                =
  instance_name            = jdc2004
  dispatchers              = (PROTOCOL=TCP) (SERVICE=jdc2004XDB)
  job_queue_processes      = 4
  hash_join_enabled        = TRUE
  background_dump_dest     = E:\oracle\admin\jdc2004\bdump
  user_dump_dest           = E:\oracle\admin\jdc2004\udump
  core_dump_dest           = E:\oracle\admin\jdc2004\cdump
  sort_area_size           = 524288
  db_name                  = jdc2004
  open_cursors             = 300
  star_transformation_enabled= FALSE
  query_rewrite_enabled    = FALSE
  pga_aggregate_target     = 262144000
  aq_tm_processes          = 1
PMON started with pid=2
DBW0 started with pid=3
LGWR started with pid=5
CKPT started with pid=6
SMON started with pid=7
RECO started with pid=8
CJQ0 started with pid=9
QMN0 started with pid=11
Thu Apr 21 13:47:34 2005
starting up 1 shared server(s) ...
starting up 1 dispatcher(s) for network address '(ADDRESS=(PARTIAL=YES)(PROTOCOL=TCP))'...
ARCH: STARTING ARCH PROCESSES
ARC0 started with pid=14
ARC0: Archival started
ARC1 started with pid=15
Thu Apr 21 13:47:35 2005
ARC1: Archival started
Thu Apr 21 13:47:36 2005
ARCH: STARTING ARCH PROCESSES COMPLETE
Thu Apr 21 13:47:36 2005
ARC0: Thread not mounted
Thu Apr 21 13:47:36 2005
alter database mount exclusive
Thu Apr 21 13:47:37 2005
ARC1: Thread not mounted
Thu Apr 21 13:47:42 2005
Successful mount of redo thread 1, with mount id 1548302713
Thu Apr 21 13:47:42 2005
Database mounted in Exclusive Mode.
Completed: alter database mount exclusive
Thu Apr 21 13:47:43 2005
alter database open
Thu Apr 21 13:47:54 2005
Beginning crash recovery of 1 threads
Thu Apr 21 13:47:55 2005
Started redo scan
Thu Apr 21 13:47:55 2005
Completed redo scan
11151 redo blocks read, 2421 data blocks need recovery
Thu Apr 21 13:47:56 2005
Started recovery at
Thread 1: logseq 483, block 76884, scn 0.0
Recovery of Online Redo Log: Thread 1 Group 3 Seq 483 Reading mem 0
  Mem# 0 errs 0: E:\ORACLE\ORADATA\JDC2004\REDO03.LOG
Thu Apr 21 13:47:58 2005
Completed redo application
Thu Apr 21 13:48:04 2005
Ended recovery at
Thread 1: logseq 483, block 88035, scn 1801.764504390
2421 data blocks read, 2421 data blocks written, 11151 redo blocks read
Crash recovery completed successfully
Thu Apr 21 13:48:06 2005
LGWR: Primary database is in CLUSTER CONSISTENT mode
Thread 1 advanced to log sequence 484
Thread 1 opened at log sequence 484
  Current log# 1 seq# 484 mem# 0: E:\ORACLE\ORADATA\JDC2004\REDO01.LOG
Successful open of redo thread 1
Thu Apr 21 13:48:09 2005
SMON: enabling cache recovery
Thu Apr 21 13:48:10 2005
ARC0: Evaluating archive   log 3 thread 1 sequence 483
ARC0: Beginning to archive log 3 thread 1 sequence 483
Creating archive destination LOG_ARCHIVE_DEST_1: 'E:\ORACLE\ORA92\RDBMS\ARC00483.001'
Thu Apr 21 13:48:11 2005
Undo Segment 1 Onlined
Undo Segment 2 Onlined
Undo Segment 3 Onlined
Undo Segment 4 Onlined
Undo Segment 5 Onlined
Undo Segment 6 Onlined
Undo Segment 7 Onlined
Undo Segment 8 Onlined
Undo Segment 9 Onlined
Undo Segment 10 Onlined
Successfully onlined Undo Tablespace 1.
Thu Apr 21 13:48:13 2005
ARC0: Completed archiving  log 3 thread 1 sequence 483
Thu Apr 21 13:48:13 2005
SMON: enabling tx recovery
Thu Apr 21 13:48:13 2005
Database Characterset is ZHS16GBK
replication_dependency_tracking turned off (no async multimaster replication found)
Thu Apr 21 13:48:20 2005
Errors in file e:\oracle\admin\jdc2004\udump\jdc2004_ora_1532.trc:
ORA-00604: ?? SQL ? 1 ????
ORA-04092: COMMIT ???????
ORA-06512: ?"VEH_ADMIN.PKGETID", line 107
ORA-06512: ?line 2

Completed: alter database open
Thu Apr 21 13:51:08 2005
/* OracleOEM */ ALTER TABLESPACE "VEH_USER" ADD DATAFILE 'E:\ORACLE\ORADATA\JDC2004\VEH\veh_user_03.ora' SIZE 1000M
Thu Apr 21 13:51:59 2005
Completed: /* OracleOEM */ ALTER TABLESPACE "VEH_USER" ADD DA
Thu Apr 21 23:22:05 2005
Thread 1 advanced to log sequence 485
Thu Apr 21 23:22:05 2005
ARC0: Evaluating archive   log 1 thread 1 sequence 484
Thu Apr 21 23:22:05 2005
  Current log# 2 seq# 485 mem# 0: E:\ORACLE\ORADATA\JDC2004\REDO02.LOG
Thu Apr 21 23:22:05 2005
ARC0: Beginning to archive log 1 thread 1 sequence 484
Creating archive destination LOG_ARCHIVE_DEST_1: 'E:\ORACLE\ORA92\RDBMS\ARC00484.001'
ARC0: Completed archiving  log 1 thread 1 sequence 484
Fri Apr 22 00:00:05 2005
skgpspawn failed:category = 27143, depinfo = 9261, op = spdcr, loc = skgpspawn
skgpspawn failed:category = 27143, depinfo = 9261, op = spdcr, loc = skgpspawn
skgpspawn failed:category = 27143, depinfo = 9261, op = spdcr, loc = skgpspawn
Fri Apr 22 00:03:19 2005
skgpspawn failed:category = 27143, depinfo = 9261, op = spdcr, loc = skgpspawn
skgpspawn failed:category = 27143, depinfo = 9261, op = spdcr, loc = skgpspawn
skgpspawn failed:category = 27143, depinfo = 9261, op = spdcr, loc = skgpspawn
Fri Apr 22 08:27:09 2005
skgpspawn failed:category = 27143, depinfo = 9261, op = spdcr, loc = skgpspawn
Fri Apr 22 08:27:42 2005
skgpspawn failed:category = 27143, depinfo = 9261, op = spdcr, loc = skgpspawn
Fri Apr 22 08:28:15 2005
skgpspawn failed:category = 27143, depinfo = 9261, op = spdcr, loc = skgpspawn
Fri Apr 22 08:28:44 2005
skgpspawn failed:category = 27143, depinfo = 9261, op = spdcr, loc = skgpspawn
skgpspawn failed:category = 27143, depinfo = 9261, op = spdcr, loc = skgpspawn
Fri Apr 22 08:29:24 2005
skgpspawn failed:category = 27143, depinfo = 9261, op = spdcr, loc = skgpspawn
Fri Apr 22 08:29:46 2005
skgpspawn failed:category = 27143, depinfo = 9261, op = spdcr, loc = skgpspawn
Fri Apr 22 08:30:17 2005
skgpspawn failed:category = 27143, depinfo = 9261, op = spdcr, loc = skgpspawn
Dump file e:\oracle\admin\jdc2004\bdump\alert_jdc2004.log
Fri Apr 22 08:42:30 2005
ORACLE V9.2.0.5.0 - Production vsnsta=0
vsnsql=12 vsnxtr=3
Windows 2000 Version 5.0 Service Pack 4, CPU type 586
Fri Apr 22 08:42:30 2005
Starting ORACLE instance (normal)
Disable cache advisory with old cache parameters
Fri Apr 22 08:42:31 2005
Window memory size 1073741824
Fri Apr 22 08:42:31 2005
Minimum working set window size : 4096
LICENSE_MAX_SESSION = 0
LICENSE_SESSIONS_WARNING = 0
SCN scheme 2
Using log_archive_dest parameter default value
LICENSE_MAX_USERS = 0
SYS auditing is disabled
Starting up ORACLE RDBMS Version: 9.2.0.5.0.
System parameters with non-default values:
  processes                = 500
  timed_statistics         = TRUE
  shared_pool_size         = 268435456
  large_pool_size          = 58720256
  java_pool_size           = 58720256
  use_indirect_data_buffers= TRUE
  control_files            = E:\oracle\oradata\jdc2004\control01.ctl, E:\oracle\oradata\jdc2004\control02.ctl, E:\oracle\oradata\jdc2004\control03.ctl
  db_block_buffers         = 200000
  db_block_size            = 8192
  compatible               = 9.2.0.0.0
  log_archive_start        = TRUE
  db_file_multiblock_read_count= 16
  fast_start_mttr_target   = 300
  undo_management          = AUTO
  undo_tablespace          = UNDOTBS1
  undo_retention           = 10800
  remote_login_passwordfile= EXCLUSIVE
  db_domain                =
  instance_name            = jdc2004
  dispatchers              = (PROTOCOL=TCP) (SERVICE=jdc2004XDB)
  job_queue_processes      = 4
  hash_join_enabled        = TRUE
  background_dump_dest     = E:\oracle\admin\jdc2004\bdump
  user_dump_dest           = E:\oracle\admin\jdc2004\udump
  core_dump_dest           = E:\oracle\admin\jdc2004\cdump
  sort_area_size           = 524288
  db_name                  = jdc2004
  open_cursors             = 300
  star_transformation_enabled= FALSE
  query_rewrite_enabled    = FALSE
  pga_aggregate_target     = 262144000
  aq_tm_processes          = 1
PMON started with pid=3
DBW0 started with pid=2
LGWR started with pid=4
CKPT started with pid=5
SMON started with pid=6
RECO started with pid=8
CJQ0 started with pid=7
QMN0 started with pid=9
Fri Apr 22 08:42:36 2005
starting up 1 shared server(s) ...
starting up 1 dispatcher(s) for network address '(ADDRESS=(PARTIAL=YES)(PROTOCOL=TCP))'...
ARCH: STARTING ARCH PROCESSES
ARC0 started with pid=13
ARC0: Archival started
ARC1 started with pid=14
Fri Apr 22 08:42:37 2005
ARCH: STARTING ARCH PROCESSES COMPLETE
Fri Apr 22 08:42:37 2005
ARC0: Thread not mounted
Fri Apr 22 08:42:37 2005
alter database mount exclusive
Fri Apr 22 08:42:38 2005
ARC1: Archival started
Fri Apr 22 08:42:38 2005
ARC1: Thread not mounted
Fri Apr 22 08:42:43 2005
Successful mount of redo thread 1, with mount id 1548395390
Fri Apr 22 08:42:43 2005
Database mounted in Exclusive Mode.
Completed: alter database mount exclusive
Fri Apr 22 08:42:43 2005
alter database open
Fri Apr 22 08:42:45 2005
Beginning crash recovery of 1 threads
Fri Apr 22 08:42:46 2005
Started redo scan
Fri Apr 22 08:42:46 2005
Completed redo scan
17587 redo blocks read, 3361 data blocks need recovery
Fri Apr 22 08:42:46 2005
Started recovery at
Thread 1: logseq 485, block 70388, scn 0.0
Recovery of Online Redo Log: Thread 1 Group 2 Seq 485 Reading mem 0
  Mem# 0 errs 0: E:\ORACLE\ORADATA\JDC2004\REDO02.LOG
Fri Apr 22 08:42:50 2005
Completed redo application
Fri Apr 22 08:42:51 2005
Ended recovery at
Thread 1: logseq 485, block 87975, scn 1801.764819819
3361 data blocks read, 3361 data blocks written, 17587 redo blocks read
Crash recovery completed successfully
Fri Apr 22 08:42:53 2005
LGWR: Primary database is in CLUSTER CONSISTENT mode
Thread 1 advanced to log sequence 486
Thread 1 opened at log sequence 486
  Current log# 3 seq# 486 mem# 0: E:\ORACLE\ORADATA\JDC2004\REDO03.LOG
Successful open of redo thread 1
Fri Apr 22 08:43:06 2005
SMON: enabling cache recovery
Fri Apr 22 08:43:06 2005
ARC0: Evaluating archive   log 2 thread 1 sequence 485
ARC0: Beginning to archive log 2 thread 1 sequence 485
Creating archive destination LOG_ARCHIVE_DEST_1: 'E:\ORACLE\ORA92\RDBMS\ARC00485.001'
ARC0: Completed archiving  log 2 thread 1 sequence 485
Fri Apr 22 08:43:10 2005
Undo Segment 1 Onlined
Undo Segment 2 Onlined
Undo Segment 3 Onlined
Undo Segment 4 Onlined
Undo Segment 5 Onlined
Undo Segment 6 Onlined
Undo Segment 7 Onlined
Undo Segment 8 Onlined
Undo Segment 9 Onlined
Undo Segment 10 Onlined
Successfully onlined Undo Tablespace 1.
Fri Apr 22 08:43:10 2005
SMON: enabling tx recovery
Fri Apr 22 08:43:10 2005
Database Characterset is ZHS16GBK
replication_dependency_tracking turned off (no async multimaster replication found)
Fri Apr 22 08:43:16 2005
Errors in file e:\oracle\admin\jdc2004\udump\jdc2004_ora_1588.trc:
ORA-00604: ?? SQL ? 1 ????
ORA-04092: COMMIT ???????
ORA-06512: ?"VEH_ADMIN.PKGETID", line 107
ORA-06512: ?line 2

Completed: alter database open

lvd123456

对了,故障现象是
(1) 数据库故障(连接不上)
(2) 重新启动后,一切正常,但半天到一天后又连接不上
(3) 再重新启动又可以了

sshd

which kind of connection do you make? local or remote
if remote, post listener.log and client side ORA- error messages
if local, post init parameters, and ORA- error messages

hnnetwork

哥们我也碰到此问题，你是怎么解决的

Minsic

突然发现是05年的帖子，傻眼了

renxiao2003

终于找到毛病了，原来是没有权限。虽然当前用户执行语句是有权限的，但是放到存储过程中就必须要显式的赋个权限给当前用户。以下是我找到的资料，贴出来给大家也看一下吧。
=====================
【IT168 技术文档】我们知道，用户拥有的role权限在存储过程是不可用的。如：   
  
   SQL> select * from dba_role_privs where grantee='SUK';
  
   GRANTEE GRANTED_ROLE ADMIN_OPTION DEFAULT_ROLE
   ------------ ------------ ------------ ------------
   SUK DBA NO YES
   SUK CONNECT NO YES
   SUK RESOURCE NO YES
  
   --用户SUK拥有DBA这个role
  
   --再创建一个测试存储过程：
   create or replace procedure p_create_table   
   is
   begin
   Execute Immediate 'create table create_table(id int)';
   end p_create_table;
  
   --然后测试
   SQL> exec p_create_table;
  
   begin p_create_table; end;
  
   ORA-01031: 权限不足
   ORA-06512: 在"SUK.P_CREATE_TABLE", line 3
   ORA-06512: 在line 1
  
   --可以看到，即使拥有DBA role，也不能创建表。role在存储过程中不可用。
   --遇到这种情况，我们一般需要显式进行系统权限，如grant create table to suk;
   --但这种方法太麻烦，有时候可能需要进行非常多的授权才能执行存储过程
   --实际上，oracle给我们提供了在存储过程中使用role权限的方法：
   --修改存储过程，加入Authid Current_User时存储过程可以使用role权限。
   create or replace procedure p_create_table   
   Authid Current_User is
   begin
   Execute Immediate 'create table create_table(id int)';
   end p_create_table;
  
   --再尝试执行：
   SQL> exec p_create_table;
  
   PL/SQL procedure successfully completed
  
   --已经可以执行了。
  

renxiao2003

看来权限很重要。以后碰到问题多GOOGLE一下。