中烟Oracle故障
从3月6日12:31开始有报错，主要报ORA-04031, ORA-00604
Sun Mar 6 12:31:30 2016
Errors in file /oracle/ECP/saptrace/background/ecp_smon_6190.trc:
ORA-04031: unable to allocate 4120 bytes of shared memory ("shared pool","update sys.col_usage$ set ...","Typecheck","kgghteInit")
Sun Mar 6 12:31:31 2016
Errors in file /oracle/ECP/saptrace/background/ecp_smon_6190.trc:
ORA-04031: unable to allocate 4120 bytes of shared memory ("shared pool","update sys.col_usage$ set ...","Typecheck","kgghteInit")
Sun Mar 6 12:31:32 2016
Errors in file /oracle/ECP/saptrace/background/ecp_smon_6190.trc:
ORA-00604: error occurred at recursive SQL level 1
ORA-04031: unable to allocate 4120 bytes of shared memory ("shared pool","select f.file#, f.block#, f....","Typecheck","seg:kggfaAllocSeg
在3月7日08:00开始报相同的错误。
Beginning log switch checkpoint up to RBA [0x13c6b.2.10], SCN: 40201962836
Thread 1 advanced to log sequence 81003
Current log# 6 seq# 81003 mem# 0: /oracle/ECP/origlogB/log_g16_m1.dbf
Current log# 6 seq# 81003 mem# 1: /oracle/ECP/mirrlogB/log_g16_m2.dbf
Mon Mar 7 07:53:18 2016
Incremental checkpoint up to RBA [0x13c6a.145e1.0], current log tail at RBA [0x13c6b.24ff.0]
Mon Mar 7 07:55:16 2016
Completed checkpoint up to RBA [0x13c6b.2.10], SCN: 40201962836
Mon Mar 7 08:00:49 2016
Errors in file /oracle/ECP/saptrace/usertrace/ecp_ora_25515.trc:
ORA-00604: error occurred at recursive SQL level 1
ORA-04031: unable to allocate 4120 bytes of shared memory ("shared pool","select name,online$,contents...","Typecheck","kgghteInit")
ORA-00604: error occurred at recursive SQL level 1
ORA-04031: unable to allocate 4120 bytes of shared memory ("shared pool","select name,online$,contents...","Typecheck","kgghteInit")
ORA-00604: error occurred at recursive SQL level 2
ORA-04031: unable to allocate 4120 bytes of shared memory ("shared pool","select name,password,datats#...","Typecheck","kgghteInit")
Mon Mar 7 08:00:52 2016
Errors in file /oracle/ECP/saptrace/background/ecp_cjq0_6194.trc:
ORA-00604: error occurred at recursive SQL level 3
ORA-04031: unable to allocate 4120 bytes of shared memory ("shared pool","select name,online$,contents...","Typecheck","kgghtInit")
Mon Mar 7 08:23:24 2016
Incremental checkpoint up to RBA [0x13c6b.dfd6.0], current log tail at RBA [0x13c6b.10a1b.0]
Mon Mar 7 08:27:50 2016
在3月7日11:00至11:54分开始一直报ORA-04031和ORA-00604错误
在11:25分时
Mon Mar 7 11:25:11 2016 MMNL absent for 1201 secs; Foregrounds taking over Mon Mar 7 11:25:11 2016
Mon Mar 7 11:54:18 2016
Errors in file /oracle/ECP/saptrace/background/ecp_cjq0_6194.trc:
ORA-00604: error occurred at recursive SQL level 3
ORA-04031: unable to allocate 4120 bytes of shared memory ("shared pool","select name,password,datats#...","Typecheck","kgghteInit")
Mon Mar 7 11:54:47 2016
Starting ORACLE instance (normal)
Mon Mar 7 11:54:47 2016
Specified value of sga_max_size is too small, bumping to 3539992576
LICENSE_MAX_SESSION = 0
LICENSE_SESSIONS_WARNING = 0
Picked latch-free SCN scheme 3
Autotune of undo retention is turned on.
数据库配制（实际启动配制）
System parameters with non-default values: processes = 220 sessions = 440 event = 10191 trace name context forever, level 1 sga_max_size = 3539992576 shared_pool_size = 1744830464 filesystemio_options = setall control_files = /oracle/ECP/origlogA/cntrl/cntlrECP.dbf, /oracle/ECP/origlogB/cntrl/cntrlECP.dbf, /oracle/ECP/sapdata1/cntrl/cntrlECP.dbf control_file_record_keep_time= 30 db_block_size = 8192 db_cache_size = 1744830464 compatible = 10.2.0 log_archive_dest = /oracle/ECP/oraarch/ECParch log_buffer = 14497792 log_checkpoint_interval = 0 db_files = 254 log_checkpoints_to_alert = TRUE dml_locks = 4000 replication_dependency_tracking= FALSE undo_management = AUTO undo_tablespace = PSAPUNDO _in_memory_undo = FALSE undo_retention = 43200 recyclebin = off remote_os_authent = TRUE remote_login_passwordfile= EXCLUSIVE job_queue_processes = 1 parallel_max_servers = 40 parallel_execution_message_size= 16384 _table_lookup_prefetch_size= 0 _fix_control = 4728348:OFF background_dump_dest = /oracle/ECP/saptrace/background user_dump_dest = /oracle/ECP/saptrace/usertrace max_dump_file_size = 20000 core_dump_dest = /oracle/ECP/saptrace/background sort_area_size = 2097152 sort_area_retained_size = 0 db_name = ECP open_cursors = 800 _sort_elimination_cost_ratio= 10 _b_tree_bitmap_plans = FALSE star_transformation_enabled= true parallel_threads_per_cpu = 1 optimizer_index_cost_adj = 20 query_rewrite_enabled = false _optim_peek_user_binds = FALSE pga_aggregate_target = 2319282339 workarea_size_policy = AUTO statistics_level = typical _optimizer_mjc_enabled = FALSE _bloom_filter_enabled = FALSE _optimizer_cbqt_no_size_restriction= FALSE
系统环境
ORACLE_HOME = /oracle/ECP/102_64 System name: HP-UX Node name: ZYEPRD2 Release: B.11.31 Version: U Machine: ia64 Instance name: ECP Redo thread mounted by this instance: 1 Oracle process number: 8 Unix process pid: 6190, image: oracle@ZYEPRD2 (SMON)
ecp_smon_6190.trc
Begin 4031 Diagnostic Information ================================= The following information assists Oracle in diagnosing causes of ORA-4031 errors. This trace may be disabled by setting the init.ora _4031_dump_bitvec = 0 ===================================== Allocation Request Summary Informaton ===================================== Current information setting: 04014fff SGA Heap Dump Interval=3600 seconds Dump Interval=300 seconds Last Dump Time=03/07/2016 11:15:24 Dump Count=4 Allocation request for: kgghtInit Heap: c0000000e7d58f18, size: 4120 ****************************************************** HEAP DUMP heap name="sga heap" desc=c000000040e0b030 extent sz=0xfe0 alt=216 het=32767 rec=9 flg=-126 opc=0 parent=0000000000000000 owner=0000000000000000 nex=0000000000000000 xsz=0x1 ****************************************************** HEAP DUMP heap name="Typecheck" desc=c0000000e7d58f18 extent sz=0xfc0 alt=32767 het=32767 rec=0 flg=2 opc=0 parent=c00000010ea62e68 owner=0000000000000000 nex=0000000000000000 xsz=0x1000000 Subheap has 42752 bytes of memory allocated