5 Replies Latest reply: Apr 10, 2012 11:19 PM by baskar.l RSS

    db hang

    user3266490
      Hi,

      Db : 11.1
      Os :Aix6

      Yesterday our db hang we couldn't login into sqlplus also.OS team replied lot of run queue(1400) cause for the problem.
      At the time how to react?we tried finally restart the db.

      Usually dead lock automatically released by oracle?right?

      Thanks & Regards,
      VN

      Edited by: user3266490 on Apr 11, 2012 2:19 AM
        • 1. Re: db hang
          sb92075
          user3266490 wrote:
          Hi,

          Db : 11.1
          Os :Aix6

          Yesterday our db hang we couldn't login into sqlplus also.OS team replied lot of run queue(1400) cause for the problem.
          At the time how to react?we tried finally restart the db.

          Thanks & Regards,
          VN
          post excerpt from alert_SID.log file for time period when problem existed
          • 2. Re: db hang
            user3266490
            Hi,

            Thanks for your reply.

            Please check the below info

            ORA-00060: Deadlock detected. More info in file /u01/app/oracle/admin/PMTT/diag/rdbms/PMTT/PMTT/trace/PMTT_ora_23003452.trc.
            Mon Apr 09 23:08:06 2012
            opiodr aborting process unknown ospid (13238664) as a result of ORA-28
            Mon Apr 09 23:09:09 2012
            opiodr aborting process unknown ospid (30933310) as a result of ORA-28
            Mon Apr 09 23:09:54 2012
            opiodr aborting process unknown ospid (66453900) as a result of ORA-28
            Mon Apr 09 23:10:39 2012
            opiodr aborting process unknown ospid (11731678) as a result of ORA-28
            Mon Apr 09 23:11:45 2012
            opiodr aborting process unknown ospid (41877524) as a result of ORA-28
            Mon Apr 09 23:14:04 2012
            ORA-00060: Deadlock detected. More info in file /u01/app/oracle/admin/PMTT/diag/rdbms/PMTT/PMTT/trace/PMTT_ora_24969776.trc.
            Mon Apr 09 23:14:54 2012
            ORA-00060: Deadlock detected. More info in file /u01/app/oracle/admin/PMTT/diag/rdbms/PMTT/PMTT/trace/PMTT_ora_23134472.trc.
            Mon Apr 09 23:15:05 2012
            opiodr aborting process unknown ospid (28443090) as a result of ORA-28
            Mon Apr 09 23:17:29 2012
            opiodr aborting process unknown ospid (9568600) as a result of ORA-28
            Mon Apr 09 23:20:00 2012
            opiodr aborting process unknown ospid (65732900) as a result of ORA-28
            Mon Apr 09 23:20:00 2012
            opiodr aborting process unknown ospid (7668266) as a result of ORA-28
            Mon Apr 09 23:20:00 2012
            opidcl aborting process unknown ospid (20316686) as a result of ORA-1013
            Mon Apr 09 23:21:45 2012
            ORA-00060: Deadlock detected. More info in file /u01/app/oracle/admin/PMTT/diag/rdbms/PMTT/PMTT/trace/PMTT_ora_20054524.trc.
            Mon Apr 09 23:24:06 2012
            ORA-00060: Deadlock detected. More info in file /u01/app/oracle/admin/PMTT/diag/rdbms/PMTT/PMTT/trace/PMTT_ora_19071728.trc.
            ORA-00060: Deadlock detected. More info in file /u01/app/oracle/admin/PMTT/diag/rdbms/PMTT/PMTT/trace/PMTT_ora_23003452.trc.
            Mon Apr 09 23:27:57 2012
            opiodr aborting process unknown ospid (41681224) as a result of ORA-28
            Mon Apr 09 23:27:57 2012
            opidcl aborting process unknown ospid (52428826) as a result of ORA-1013
            Mon Apr 09 23:27:57 2012
            opidcl aborting process unknown ospid (57409928) as a result of ORA-1013
            Mon Apr 09 23:28:14 2012
            ORA-00060: Deadlock detected. More info in file /u01/app/oracle/admin/PMTT/diag/rdbms/PMTT/PMTT/trace/PMTT_ora_4981116.trc.
            Mon Apr 09 23:30:28 2012
            opidcl aborting process unknown ospid (32309660) as a result of ORA-28
            Mon Apr 09 23:30:28 2012
            opidcl aborting process unknown ospid (65143120) as a result of ORA-1013
            Mon Apr 09 23:30:28 2012
            opidcl aborting process unknown ospid (64029040) as a result of ORA-1013
            Mon Apr 09 23:31:05 2012
            ORA-00060: Deadlock detected. More info in file /u01/app/oracle/admin/PMTT/diag/rdbms/PMTT/PMTT/trace/PMTT_ora_19071728.trc.
            Mon Apr 09 23:33:03 2012
            opiodr aborting process unknown ospid (11534512) as a result of ORA-28
            Mon Apr 09 23:33:03 2012
            opidcl aborting process unknown ospid (41681128) as a result of ORA-1013
            Mon Apr 09 23:33:24 2012
            ORA-00060: Deadlock detected. More info in file /u01/app/oracle/admin/PMTT/diag/rdbms/PMTT/PMTT/trace/PMTT_ora_55247152.trc.
            Mon Apr 09 23:35:43 2012
            opidcl aborting process unknown ospid (17104914) as a result of ORA-1013
            Mon Apr 09 23:35:43 2012
            opidcl aborting process unknown ospid (35390074) as a result of ORA-28
            Mon Apr 09 23:35:43 2012
            opidcl aborting process unknown ospid (28442810) as a result of ORA-1013
            Mon Apr 09 23:36:17 2012
            ORA-00060: Deadlock detected. More info in file /u01/app/oracle/admin/PMTT/diag/rdbms/PMTT/PMTT/trace/PMTT_ora_4981116.trc.
            Mon Apr 09 23:38:18 2012
            opiodr aborting process unknown ospid (32374816) as a result of ORA-28
            Mon Apr 09 23:38:18 2012
            opidcl aborting process unknown ospid (35324538) as a result of ORA-1013
            Mon Apr 09 23:38:18 2012
            opidcl aborting process unknown ospid (56426978) as a result of ORA-1013
            Mon Apr 09 23:38:32 2012
            ORA-00060: Deadlock detected. More info in file /u01/app/oracle/admin/PMTT/diag/rdbms/PMTT/PMTT/trace/PMTT_ora_34013370.trc.
            Mon Apr 09 23:40:46 2012
            ORA-00060: Deadlock detected. More info in file /u01/app/oracle/admin/PMTT/diag/rdbms/PMTT/PMTT/trace/PMTT_ora_23003452.trc.
            Mon Apr 09 23:40:56 2012
            opidcl aborting process unknown ospid (24773216) as a result of ORA-1013
            Mon Apr 09 23:40:56 2012
            opidcl aborting process unknown ospid (1966770) as a result of ORA-1013
            Mon Apr 09 23:41:01 2012
            opiodr aborting process unknown ospid (55902688) as a result of ORA-28
            Mon Apr 09 23:43:32 2012
            opiodr aborting process unknown ospid (36110774) as a result of ORA-28
            Mon Apr 09 23:43:32 2012
            opidcl aborting process unknown ospid (8389158) as a result of ORA-28
            Mon Apr 09 23:43:32 2012
            opiodr aborting process unknown ospid (31523062) as a result of ORA-28
            Tue Apr 10 00:03:50 2012
            opiodr aborting process unknown ospid (46924234) as a result of ORA-28
            Tue Apr 10 00:03:51 2012
            opidcl aborting process unknown ospid (16449818) as a result of ORA-1013
            Tue Apr 10 00:04:23 2012
            opidcl aborting process unknown ospid (29491526) as a result of ORA-1013
            Tue Apr 10 00:04:23 2012
            opidcl aborting process unknown ospid (66846986) as a result of ORA-28
            Tue Apr 10 00:06:05 2012
            ORA-00060: Deadlock detected. More info in file /u01/app/oracle/admin/PMTT/diag/rdbms/PMTT/PMTT/trace/PMTT_ora_23003452.trc.
            Tue Apr 10 00:06:59 2012
            opidcl aborting process unknown ospid (52560306) as a result of ORA-1013
            Tue Apr 10 00:06:59 2012
            opidcl aborting process unknown ospid (13369938) as a result of ORA-28
            Tue Apr 10 00:06:59 2012
            opiodr aborting process unknown ospid (33161836) as a result of ORA-28
            Tue Apr 10 00:12:11 2012
            ORA-00060: Deadlock detected. More info in file /u01/app/oracle/admin/PMTT/diag/rdbms/PMTT/PMTT/trace/PMTT_ora_19071728.trc.
            Tue Apr 10 00:25:17 2012
            opiodr aborting process unknown ospid (11141518) as a result of ORA-28
            Tue Apr 10 00:25:17 2012
            opidcl aborting process unknown ospid (16056804) as a result of ORA-1013
            Tue Apr 10 00:25:17 2012
            opidcl aborting process unknown ospid (10617380) as a result of ORA-1013
            Tue Apr 10 00:27:22 2012
            ORA-00060: Deadlock detected. More info in file /u01/app/oracle/admin/PMTT/diag/rdbms/PMTT/PMTT/trace/PMTT_ora_4981116.trc.
            Tue Apr 10 00:27:51 2012
            opiodr aborting process unknown ospid (2556162) as a result of ORA-28
            Tue Apr 10 00:27:51 2012
            opidcl aborting process unknown ospid (16515784) as a result of ORA-28
            Tue Apr 10 00:27:51 2012
            ORA-00060: Deadlock detected. More info in file /u01/app/oracle/admin/PMTT/diag/rdbms/PMTT/PMTT/trace/PMTT_ora_2556480.trc.
            Tue Apr 10 00:38:17 2012
            opiodr aborting process unknown ospid (65339496) as a result of ORA-28
            Tue Apr 10 00:38:18 2012
            opidcl aborting process unknown ospid (8716766) as a result of ORA-28
            Tue Apr 10 00:43:32 2012
            opidcl aborting process unknown ospid (8388958) as a result of ORA-1013
            Tue Apr 10 00:43:32 2012
            opidcl aborting process unknown ospid (55443860) as a result of ORA-1013
            Tue Apr 10 00:43:32 2012
            opidcl aborting process unknown ospid (23396972) as a result of ORA-1013
            Tue Apr 10 00:43:32 2012
            opiodr aborting process unknown ospid (29622858) as a result of ORA-28
            Tue Apr 10 00:43:32 2012
            opidcl aborting process unknown ospid (26608228) as a result of ORA-1013
            Tue Apr 10 00:46:08 2012
            opiodr aborting process unknown ospid (35258462) as a result of ORA-28
            Tue Apr 10 00:48:46 2012
            Suspending MMON slave action kewrmafsa_ for 82800 seconds
            Tue Apr 10 00:48:46 2012
            opidcl aborting process unknown ospid (28114958) as a result of ORA-28
            Tue Apr 10 00:48:47 2012
            opidcl aborting process unknown ospid (28050094) as a result of ORA-1013
            Tue Apr 10 00:51:23 2012
            opiodr aborting process unknown ospid (39518518) as a result of ORA-28
            Tue Apr 10 00:54:05 2012
            opidcl aborting process unknown ospid (2753260) as a result of ORA-1013
            Tue Apr 10 00:54:05 2012
            opiodr aborting process unknown ospid (23855130) as a result of ORA-28
            Tue Apr 10 00:54:06 2012
            opidcl aborting process unknown ospid (28443202) as a result of ORA-1013
            Tue Apr 10 00:56:44 2012
            opidcl aborting process unknown ospid (31129654) as a result of ORA-1013
            Tue Apr 10 00:56:45 2012
            ORA-00060: Deadlock detected. More info in file /u01/app/oracle/admin/PMTT/diag/rdbms/PMTT/PMTT/trace/PMTT_ora_23134472.trc.
            Tue Apr 10 01:17:42 2012
            opiodr aborting process unknown ospid (29622440) as a result of ORA-28
            Tue Apr 10 01:17:42 2012
            opidcl aborting process unknown ospid (36044888) as a result of ORA-1013
            Tue Apr 10 01:17:42 2012
            opidcl aborting process unknown ospid (2949586) as a result of ORA-1013
            Tue Apr 10 01:17:42 2012
            opiodr aborting process unknown ospid (46137458) as a result of ORA-28
            Tue Apr 10 01:17:42 2012
            opiodr aborting process unknown ospid (20447942) as a result of ORA-28
            Tue Apr 10 01:28:22 2012
            ORA-00060: Deadlock detected. More info in file /u01/app/oracle/admin/PMTT/diag/rdbms/PMTT/PMTT/trace/PMTT_ora_4981116.trc.
            Tue Apr 10 01:30:07 2012
            ORA-00060: Deadlock detected. More info in file /u01/app/oracle/admin/PMTT/diag/rdbms/PMTT/PMTT/trace/PMTT_ora_38535408.trc.
            Tue Apr 10 01:33:02 2012
            ORA-00060: Deadlock detected. More info in file /u01/app/oracle/admin/PMTT/diag/rdbms/PMTT/PMTT/trace/PMTT_ora_4981116.trc.
            Tue Apr 10 01:34:33 2012
            ORA-00060: Deadlock detected. More info in file /u01/app/oracle/admin/PMTT/diag/rdbms/PMTT/PMTT/trace/PMTT_ora_38535408.trc.
            Tue Apr 10 01:36:00 2012
            ORA-00060: Deadlock detected. More info in file /u01/app/oracle/admin/PMTT/diag/rdbms/PMTT/PMTT/trace/PMTT_ora_23003452.trc.
            Tue Apr 10 01:36:47 2012
            ORA-00060: Deadlock detected. More info in file /u01/app/oracle/admin/PMTT/diag/rdbms/PMTT/PMTT/trace/PMTT_ora_23134472.trc.
            Tue Apr 10 01:37:45 2012
            ORA-00060: Deadlock detected. More info in file /u01/app/oracle/admin/PMTT/diag/rdbms/PMTT/PMTT/trace/PMTT_ora_24969776.trc.
            Tue Apr 10 01:38:06 2012
            ORA-00060: Deadlock detected. More info in file /u01/app/oracle/admin/PMTT/diag/rdbms/PMTT/PMTT/trace/PMTT_ora_4981116.trc.
            Tue Apr 10 01:38:16 2012
            ORA-00060: Deadlock detected. More info in file /u01/app/oracle/admin/PMTT/diag/rdbms/PMTT/PMTT/trace/PMTT_ora_19071728.trc.
            Tue Apr 10 01:38:31 2012
            ORA-00060: Deadlock detected. More info in file /u01/app/oracle/admin/PMTT/diag/rdbms/PMTT/PMTT/trace/PMTT_ora_38535408.trc.
            Tue Apr 10 01:38:45 2012
            ORA-00060: Deadlock detected. More info in file /u01/app/oracle/admin/PMTT/diag/rdbms/PMTT/PMTT/trace/PMTT_ora_4981116.trc.
            Tue Apr 10 01:38:48 2012
            Tue Apr 10 01:40:16 2012
            Shutting down instance (immediate)
            Stopping background process SMCO
            Shutting down instance: further logons disabled
            Tue Apr 10 01:40:25 2012
            Stopping background process CJQ0
            Stopping background process QMNC
            Tue Apr 10 01:40:28 2012
            Stopping background process MMNL
            Stopping background process MMON
            License high water mark = 2233
            Tue Apr 10 01:41:21 2012
            All dispatchers and shared servers shutdown
            Tue Apr 10 01:45:27 2012
            License high water mark = 2233
            USER (ospid: 25166166): terminating the instance
            Termination issued to instance processes. Waiting for the processes to exit
            Tue Apr 10 01:45:37 2012
            Instance termination failed to kill one or more processes
            Instance terminated by USER, pid = 25166166
            Tue Apr 10 01:47:21 2012
            Starting ORACLE instance (normal)
            sskgpgetexecname failed to get name
            LICENSE_MAX_SESSION = 0
            LICENSE_SESSIONS_WARNING = 0
            Picked latch-free SCN scheme 3
            Autotune of undo retention is turned on.
            IMODE=BR
            ILAT =409
            LICENSE_MAX_USERS = 0
            SYS auditing is disabled
            Starting up:
            Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
            With the Partitioning, OLAP, Data Mining and Real Application Testing options.
            Using parameter settings in server-side pfile /u01/app/oracle/11.2.0.1/product/rdbms/oracle_home/dbs/initPMTT.ora
            System parameters with non-default values:
            processes = 5200
            sessions = 7872
            cpu_count = 64
            sga_max_size = 105G
            shared_pool_reserved_size= 128M
            spfile = "+PMTT_DATAGROUP01/PMTT/spfilePMTT.ora"
            nls_sort = "BINARY"
            nls_date_language = "AMERICAN"
            nls_date_format = "RRRR-MM-DD-HH24:MI"
            nls_currency = "$"
            nls_numeric_characters = ".,"
            nls_iso_currency = "AMERICA"
            nls_calendar = "GREGORIAN"
            nls_time_format = "AM"
            nls_timestamp_format = "DD-MON-RR HH.MI.SSXFF AM"
            nls_time_tz_format = "TZR"
            nls_timestamp_tz_format = "DD-MON-RR HH.MI.SSXFF AM TZR"
            nls_dual_currency = "$"
            nls_comp = "BINARY"
            nls_length_semantics = "CHAR"
            filesystemio_options = "SETALL"
            resource_manager_plan = ""
            sga_target = 100G
            control_files = "+PMTT_DATAGROUP01/PMTT/control01.ctl"
            control_files = "+PMTT_REDOGROUP01/PMTT/control02.ctl"
            control_file_record_keep_time= 15
            db_block_size = 16384
            db_2k_cache_size = 1G
            db_keep_cache_size = 1G
            db_writer_processes = 20
            compatible = "11.2.0.0.0"
            log_archive_dest_1 = "location=+PMTT_ARCHGROUP01/PMTT/ARCH mandatory reopen=5"
            log_archive_format = "arch_%t_%s_%r.arc"
            log_archive_max_processes= 15
            log_buffer = 14188544
            db_files = 1024
            dml_locks = 2048
            transactions = 4096
            undo_tablespace = "UNDOTBS1"
            recyclebin = "OFF"
            remote_login_passwordfile= "EXCLUSIVE"
            db_domain = ""
            dispatchers = "(PROTOCOL=TCP) (SERVICE=PMTTXDB)"
            session_cached_cursors = 7200
            job_queue_processes = 1000
            cursor_sharing = "SIMILAR"
            audit_file_dest = "/u01/app/oracle/admin/PMTT/adump"
            audit_trail = "DB"
            sort_area_size = 134217728
            db_name = "PMTT"
            db_unique_name = "PMTT"
            open_cursors = 4096
            parallel_threads_per_cpu = 1
            query_rewrite_integrity = "TRUSTED"
            pga_aggregate_target = 60G
            diagnostic_dest = "/u01/app/oracle/admin/PMTT"
            max_dump_file_size = "5242880"
            Tue Apr 10 01:47:29 2012
            PMON started with pid=2, OS id=18546800
            Tue Apr 10 01:47:30 2012
            VKTM started with pid=3, OS id=19136608 at elevated priority
            VKTM running at (10)millisec precision with DBRM quantum (100)ms
            Tue Apr 10 01:47:30 2012
            GEN0 started with pid=4, OS id=19071090
            Tue Apr 10 01:47:30 2012
            DIAG started with pid=5, OS id=18677972
            Tue Apr 10 01:47:30 2012
            DBRM started with pid=6, OS id=47382686
            Tue Apr 10 01:47:30 2012
            PSP0 started with pid=7, OS id=18481228
            Tue Apr 10 01:47:30 2012
            DIA0 started with pid=8, OS id=18415682
            Tue Apr 10 01:47:30 2012
            MMAN started with pid=9, OS id=18808914
            Tue Apr 10 01:47:30 2012
            DBW0 started with pid=10, OS id=18612306
            Tue Apr 10 01:47:30 2012
            DBW1 started with pid=11, OS id=16777300
            Tue Apr 10 01:47:30 2012
            DBW2 started with pid=12, OS id=18284594
            Tue Apr 10 01:47:31 2012
            DBW3 started with pid=13, OS id=18219056
            Tue Apr 10 01:47:31 2012
            DBW4 started with pid=14, OS id=18153534
            Tue Apr 10 01:47:31 2012
            DBW5 started with pid=15, OS id=18087986
            Tue Apr 10 01:47:31 2012
            DBW6 started with pid=16, OS id=18022452
            Tue Apr 10 01:47:31 2012
            DBW7 started with pid=17, OS id=17956906
            Tue Apr 10 01:47:31 2012
            DBW8 started with pid=18, OS id=18350178
            Tue Apr 10 01:47:31 2012
            DBW9 started with pid=19, OS id=17891388
            Tue Apr 10 01:47:31 2012
            DBWa started with pid=20, OS id=17498190
            Tue Apr 10 01:47:31 2012
            DBWb started with pid=21, OS id=15794916
            Tue Apr 10 01:47:31 2012
            DBWc started with pid=22, OS id=18743506
            Tue Apr 10 01:47:32 2012
            DBWd started with pid=23, OS id=17236000
            Tue Apr 10 01:47:32 2012
            DBWe started with pid=24, OS id=17825846
            Tue Apr 10 01:47:32 2012
            DBWf started with pid=25, OS id=17170466
            Tue Apr 10 01:47:32 2012
            DBWg started with pid=26, OS id=17039400
            Tue Apr 10 01:47:32 2012
            DBWh started with pid=27, OS id=16908320
            Tue Apr 10 01:47:32 2012
            DBWi started with pid=28, OS id=16842772
            Tue Apr 10 01:47:32 2012
            DBWj started with pid=29, OS id=16580612
            Tue Apr 10 01:47:32 2012
            LGWR started with pid=30, OS id=16253000
            Tue Apr 10 01:47:32 2012
            CKPT started with pid=31, OS id=16187398
            Tue Apr 10 01:47:32 2012
            SMON started with pid=32, OS id=15990796
            Tue Apr 10 01:47:32 2012
            RECO started with pid=33, OS id=15728766
            Tue Apr 10 01:47:33 2012
            RBAL started with pid=34, OS id=15532042
            Tue Apr 10 01:47:33 2012
            ASMB started with pid=35, OS id=15466744
            Tue Apr 10 01:47:33 2012
            MMON started with pid=36, OS id=15401182
            Tue Apr 10 01:47:33 2012
            MMNL started with pid=37, OS id=15007786
            Tue Apr 10 01:47:33 2012
            starting up 1 dispatcher(s) for network address '(ADDRESS=(PARTIAL=YES)(PROTOCOL=TCP))'...
            NOTE: initiating MARK startup
            Starting background process MARK
            starting up 1 shared server(s) ...
            Tue Apr 10 01:47:33 2012
            MARK started with pid=39, OS id=13959232
            NOTE: MARK has subscribed
            ORACLE_BASE from environment = /u01/app/oracle/11.2.0.1/product/rdbms/oracle_base
            Tue Apr 10 01:47:33 2012
            ALTER DATABASE MOUNT
            NOTE: Loaded library: System
            SUCCESS: diskgroup PMTT_DATAGROUP01 was mounted
            SUCCESS: diskgroup PMTT_REDOGROUP01 was mounted
            NOTE: dependency between database PMTT and diskgroup resource ora.PMTT_DATAGROUP01.dg is established
            NOTE: dependency between database PMTT and diskgroup resource ora.PMTT_REDOGROUP01.dg is established
            Successful mount of redo thread 1, with mount id 153929272
            Database mounted in Exclusive Mode
            Lost write protection disabled
            Completed: ALTER DATABASE MOUNT
            Tue Apr 10 01:47:41 2012
            ALTER DATABASE OPEN
            Beginning crash recovery of 1 threads
            parallel recovery started with 32 processes
            Started redo scan
            Completed redo scan
            read 153 KB redo, 12 data blocks need recovery
            Started redo application at
            Thread 1: logseq 1696, block 6253452
            Recovery of Online Redo Log: Thread 1 Group 8 Seq 1696 Reading mem 0
            Mem# 0: +PMTT_REDOGROUP01/PMTT/redo08a.log
            Mem# 1: +PMTT_DATAGROUP01/PMTT/redo08b.log
            Completed redo application of 0.01MB
            Completed crash recovery at
            Thread 1: logseq 1696, block 6253758, scn 5455019031011
            12 data blocks read, 12 data blocks written, 153 redo k-bytes read
            Tue Apr 10 01:47:46 2012
            LGWR: STARTING ARCH PROCESSES
            Tue Apr 10 01:47:46 2012
            ARC0 started with pid=77, OS id=19661028
            ARC0: Archival started
            LGWR: STARTING ARCH PROCESSES COMPLETE
            ARC0: STARTING ARCH PROCESSES
            Tue Apr 10 01:47:46 2012
            ARC1 started with pid=78, OS id=12517548
            Tue Apr 10 01:47:46 2012
            ARC2 started with pid=79, OS id=42663956
            Tue Apr 10 01:47:46 2012
            ARC3 started with pid=80, OS id=64159744
            Tue Apr 10 01:47:47 2012
            ARC4 started with pid=81, OS id=58786276
            Tue Apr 10 01:47:47 2012
            ARC5 started with pid=82, OS id=52756842
            Tue Apr 10 01:47:47 2012
            ARC6 started with pid=83, OS id=42467790
            Tue Apr 10 01:47:47 2012
            ARC7 started with pid=84, OS id=58982718
            Thread 1 advanced to log sequence 1697 (thread open)
            Thread 1 opened at log sequence 1697
            Current log# 1 seq# 1697 mem# 0: +PMTT_REDOGROUP01/PMTT/redo01a.log
            Current log# 1 seq# 1697 mem# 1: +PMTT_DATAGROUP01/PMTT/redo01b.log
            Successful open of redo thread 1
            MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
            Tue Apr 10 01:47:47 2012
            SMON: enabling cache recovery
            Tue Apr 10 01:47:47 2012
            ARC8 started with pid=85, OS id=27525228
            Tue Apr 10 01:47:47 2012
            ARC9 started with pid=86, OS id=65732954
            Tue Apr 10 01:47:47 2012
            ARCa started with pid=87, OS id=59179366
            Tue Apr 10 01:47:47 2012
            ARCb started with pid=88, OS id=58589588
            Tue Apr 10 01:47:47 2012
            ARCc started with pid=89, OS id=12845638
            Tue Apr 10 01:47:47 2012
            ARCd started with pid=90, OS id=58261784
            ARC1: Archival started
            Tue Apr 10 01:47:48 2012
            ARCe started with pid=91, OS id=56295748
            ARC2: Archival started
            ARC3: Archival started
            ARC4: Archival started
            ARC5: Archival started
            ARC6: Archival started
            ARC7: Archival started
            ARC8: Archival started
            ARC9: Archival started
            ARCa: Archival started
            ARCb: Archival started
            ARCc: Archival started
            ARCd: Archival started
            ARC1: Becoming the 'no FAL' ARCH
            ARC1: Becoming the 'no SRL' ARCH
            ARC2: Becoming the heartbeat ARCH
            SUCCESS: diskgroup PMTT_ARCHGROUP01 was mounted
            ARCe: Archival started
            ARC0: STARTING ARCH PROCESSES COMPLETE
            Tue Apr 10 01:47:52 2012
            Successfully onlined Undo Tablespace 2.
            Verifying file header compatibility for 11g tablespace encryption..
            Verifying 11g file header compatibility for tablespace encryption completed
            SMON: enabling tx recovery
            Database Characterset is AL32UTF8
            No Resource Manager plan active
            replication_dependency_tracking turned off (no async multimaster replication found)
            Starting background process QMNC
            Tue Apr 10 01:47:54 2012
            QMNC started with pid=98, OS id=56164634
            Completed: ALTER DATABASE OPEN
            Tue Apr 10 01:47:58 2012
            Starting background process CJQ0
            Tue Apr 10 01:47:58 2012
            CJQ0 started with pid=100, OS id=19071732
            Setting Resource Manager plan SCHEDULER[0x3003]:DEFAULT_MAINTENANCE_PLAN via scheduler window
            Setting Resource Manager plan DEFAULT_MAINTENANCE_PLAN via parameter
            Tue Apr 10 01:48:01 2012
            Starting background process VKRM
            Tue Apr 10 01:48:01 2012
            VKRM started with pid=99, OS id=31129672
            Tue Apr 10 01:48:04 2012
            Archived Log entry 1666 added for thread 1 sequence 1696 ID 0x8db3363 dest 1:
            Tue Apr 10 01:52:09 2012
            Clearing Resource Manager plan via parameter
            ALTER SYSTEM SET resource_manager_plan='' SCOPE=BOTH;
            Clearing Resource Manager plan via parameter
            ALTER SYSTEM SET resource_manager_plan='' SCOPE=BOTH SID='PMTT';
            Tue Apr 10 01:57:56 2012
            Starting background process SMCO
            Tue Apr 10 01:57:56 2012
            SMCO started with pid=43, OS id=55050626
            Tue Apr 10 02:00:00 2012
            Clearing Resource Manager plan via parameter
            Tue Apr 10 02:00:43 2012
            ALTER SYSTEM ARCHIVE LOG
            Tue Apr 10 02:00:43 2012
            Thread 1 cannot allocate new log, sequence 1698
            Private strand flush not complete
            Current log# 1 seq# 1697 mem# 0: +PMTT_REDOGROUP01/PMTT/redo01a.log
            Current log# 1 seq# 1697 mem# 1: +PMTT_DATAGROUP01/PMTT/redo01b.log
            Thread 1 advanced to log sequence 1698 (LGWR switch)
            Current log# 2 seq# 1698 mem# 0: +PMTT_REDOGROUP01/PMTT/redo02a.log
            Current log# 2 seq# 1698 mem# 1: +PMTT_DATAGROUP01/PMTT/redo02b.log
            Archived Log entry 1667 added for thread 1 sequence 1697 ID 0x8db3363 dest 1:
            Tue Apr 10 02:00:49 2012
            NOTE: dependency between database PMTT and diskgroup resource ora.PMTT_ARCHGROUP01.dg is established
            Tue Apr 10 02:53:19 2012
            ORA-00060: Deadlock detected. More info in file /u01/app/oracle/admin/PMTT/diag/rdbms/PMTT/PMTT/trace/PMTT_ora_59375920.trc.


            Thanks & Regards,
            VN
            • 3. Re: db hang
              sb92075
              Tue Apr 10 01:38:45 2012
              ORA-00060: Deadlock detected. More info in file /u01/app/oracle/admin/PMTT/diag/rdbms/PMTT/PMTT/trace/PMTT_ora_4981116.trc.
              Tue Apr 10 01:38:48 2012
              Tue Apr 10 01:40:16 2012
              Shutting down instance (immediate)

              two observations
              1) application has serious flaw in that numerous DEADLOCKS occur
              2) somebody did SHUTDOWN IMMEDIATE which only delayed getting more ORA-00060 errors

              was application changed/upgraded recently?
              • 4. Re: db hang
                user3266490
                Hi,

                Thanks for your reply.

                First we tried to shut immediate but it was taking more time than usual so again we did shut abort.

                we found in trace file Some delete operation going on those time
                [Transaction Deadlock]

                we changed JDA7.4 to 7.7 at application level. and db level to 10.2 to 11(by exp/imp).

                Thanks & Regards,
                VN

                Edited by: user3266490 on Apr 11, 2012 2:26 AM
                • 5. Re: db hang
                  baskar.l
                  Hi,
                  Yes dead locks are automatically released by oracle.

                  Dead locks are caused by application design flaw, unindexed foreign keys and bitmay indexes on tables that are modified in real time.

                  Thanks,
                  baskar.l