#acl merlyn:read,write All:read = 中烟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 }}}