Had a read only standby database , when shutdown abort, gave end-of-communication channel and after which does not startup. Checked for any open processes with ps -ef , brought down listener still no luck. Then observed that oracle process was holding the semaphores which is bug in 11.1.0.7 database.
SHUTDOWN: Active processes prevent shutdown operation
Mon Sep 08 13:20:35 2014
SHUTDOWN: Active processes prevent shutdown operation
Mon Sep 08 13:25:37 2014
SHUTDOWN: Active processes prevent shutdown operation
$ sqlplus sys
SQL*Plus: Release 11.1.0.7.0 - Production on Mon Sep 8 13:53:14 2014
Copyright (c) 1982, 2008, Oracle. All rights reserved.
Connected.
SQL> shut abort
ORA-03113: end-of-file on communication channel
$ sqlplus sys as sysdba
SQL*Plus: Release 11.1.0.7.0 - Production on Mon Sep 8 13:35:30 2014
Copyright (c) 1982, 2008, Oracle. All rights reserved.
Connected.
SQL> startup mount
ORA-01081: cannot start already-running ORACLE - shut it down first
SHUTDOWN: Active processes prevent shutdown operation
Mon Sep 08 13:20:35 2014
SHUTDOWN: Active processes prevent shutdown operation
Mon Sep 08 13:25:37 2014
SHUTDOWN: Active processes prevent shutdown operation
SQL*Plus: Release 11.1.0.7.0 - Production on Mon Sep 8 13:53:14 2014
Copyright (c) 1982, 2008, Oracle. All rights reserved.
Connected.
SQL> shut abort
ORA-03113: end-of-file on communication channel
$ sqlplus sys as sysdba
SQL*Plus: Release 11.1.0.7.0 - Production on Mon Sep 8 13:35:30 2014
Copyright (c) 1982, 2008, Oracle. All rights reserved.
Connected.
SQL> startup mount
ORA-01081: cannot start already-running ORACLE - shut it down first
Alert log -
Mon Sep 08 13:37:16 2014
Starting ORACLE instance (normal)
Mon Sep 08 13:38:53 2014
ORA-1092 : opitsk aborting process
Mon Sep 08 13:46:02 2014
ORA-1092 : opitsk aborting process
Mon Sep 08 13:46:21 2014
ORA-1092 : opitsk aborting process
Mon Sep 08 13:46:29 2014
License high water mark = 31
USER (ospid: 17717): terminating the instance
Termination issued to instance processes. Waiting for the processes to exit
Mon Sep 08 13:46:40 2014
Instance termination failed to kill one or more processes
Mon Sep 08 13:47:10 2014
Exception [type: SIGSEGV, Address not mapped to object] [ADDR:0x4CD0] [PC:0x13025B7, kewa_dump_time_diff()+157]
Errors in file /oraadmin/diag/rdbms/tmspstby/TMSPSTBY/trace/TMSPSTBY_ora_17717.trc (incident=61699):
ORA-07445: exception encountered: core dump [kewa_dump_time_diff()+157] [SIGSEGV] [ADDR:0x4CD0] [PC:0x13025B7] [Address not mapped to object] []
Incident details in: /oraadmin/diag/rdbms/tmspstby/TMSPSTBY/incident/incdir_61699/TMSPSTBY_ora_17717_i61699.trc
Mon Sep 08 13:50:23 2014
ORA-1092 : opitsk aborting process
Mon Sep 08 13:53:14 2014
ORA-1092 : opitsk aborting process
Mon Sep 08 13:53:23 2014
License high water mark = 31
so.. Here is the solution ------
sqlplus as sysdba
oradebug setmypid
oradebug ipcs
Trace file generated -
$ cat TMSPSTBY_ora_18230.trc
Trace file /oraadmin/diag/rdbms/tmspstby/TMSPSTBY/trace/TMSPSTBY_ora_18230.trc
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORACLE_HOME = /orahome/product/11.1.0/db
System name: Linux
Node name: arlclsoraq05.corp.cat.com
Release: 2.6.32-431.23.3.el6.x86_64
Version: #1 SMP Wed Jul 16 06:12:23 EDT 2014
Machine: x86_64
Instance name: TMSPSTBY
Redo thread mounted by this instance: 1
Oracle process number: 0
Unix process pid: 18230, image: oracle@arlclsoraq05.corp.cat.com
*** 2014-09-08 13:50:31.678
Processing Oradebug command 'setmypid'
*** 2014-09-08 13:50:31.678
Oradebug command 'setmypid' console output: <none>
*** 2014-09-08 13:50:36.415
Processing Oradebug command 'ipc'
Dump of unix-generic skgm context
areaflags 000000f7
realmflags 0000000f
mapsize 00000800
protectsize 00001000
lcmsize 00001000
seglen 00200000
largestsize 0000000200000000
smallestsize 0000000004000000
stacklimit 0x7fff939cc5c0
stackdir -1
mode 660
magic acc01ade
Handle: 0x7f34cfb060b0 `/orahome/product/11.1.0/dbTMSPSTBY'
Dump of unix-generic realm handle `/orahome/product/11.1.0/dbTMSPSTBY', flags = 00000000
Area #0 `Fixed Size' containing Subareas 0-0
Total size 000000000020f370 Minimum Subarea size 00000000
Area Subarea Shmid Stable Addr Actual Addr
0 0 98304 0x00000060000000 0x00000060000000
Subarea size Segment size
0000000000210000 0000000140200000
Area #1 `Variable Size' containing Subareas 4-4
Total size 0000000138000000 Minimum Subarea size 04000000
Area Subarea Shmid Stable Addr Actual Addr
1 4 98304 0x00000068000000 0x00000068000000
Subarea size Segment size
0000000138000000 0000000140200000
Area #2 `Redo Buffers' containing Subareas 1-1
Total size 0000000006712000 Minimum Subarea size 00000000
Area Subarea Shmid Stable Addr Actual Addr
2 1 98304 0x00000060210000 0x00000060210000
Subarea size Segment size
0000000006712000 0000000140200000
Area #3 `Base Allocator Control' containing Subareas 3-3
Total size 0000000000002000 Minimum Subarea size 00000000
Area Subarea Shmid Stable Addr Actual Addr
3 3 98304 0x00000067ffe000 0x00000067ffe000
Subarea size Segment size
0000000000002000 0000000140200000
Area #4 `Slab Allocator Control' containing Subareas 2-2
Total size 00000000016dc000 Minimum Subarea size 00000000
Area Subarea Shmid Stable Addr Actual Addr
4 2 98304 0x00000066922000 0x00000066922000
Subarea size Segment size
00000000016dc000 0000000140200000
Area #5 `skgm overhead' containing Subareas 5-5
Total size 0000000000002000 Minimum Subarea size 00000000
Area Subarea Shmid Stable Addr Actual Addr
5 5 98304 0x000001a0000000 0x000001a0000000
Subarea size Segment size
0000000000002000 0000000140200000
Dump of Linux-specific skgm context
sharedmmu 00000001
shareddec 0
used region 0: start 00000002240000000 length 000000147fff04000000
Maximum processes: = 512
Number of semaphores per set: = 129
Semaphores key overhead per set: = 4
User Semaphores per set: = 125
Number of semaphore sets: = 5
Semaphore identifiers: = 5
Semaphore List=
524290
557059
589828
622597
655366
-------------- system semaphore information -------------
------ Shared Memory Segments --------
key shmid owner perms bytes nattch status
0xf762bde4 98304 oracle 600 5370806272 1 - This size matches with SGA of the current database
0x00000000 163841 oracle 640 12582912 44
0x00000000 196610 oracle 640 612368384 44
0x34c29f3c 229379 oracle 640 2097152 44
0x00000000 294916 oracle 640 12582912 30
0x00000000 327685 oracle 640 524288000 30
0x71ffabb4 360454 oracle 640 2097152 30
0x00000000 425991 oracle 640 16777216 33
0x00000000 458760 oracle 640 1073741824 33
0x00000000 491529 oracle 640 536870912 33
0x00000000 524298 oracle 640 268435456 33
0x00000000 557067 oracle 640 134217728 33
0x00000000 589836 oracle 640 16777216 33
0x00000000 622605 oracle 640 100663296 33
0x9dc288ec 655374 oracle 640 2097152 33
0x00000000 720911 oracle 640 16777216 35
0x00000000 753680 oracle 640 2080374784 35
0xc14277a8 786449 oracle 640 2097152 35
------ Semaphore Arrays --------
key semid owner perms nsems
0x8872f968 524290 oracle 660 129
0x8872f969 557059 oracle 660 129
0x8872f96a 589828 oracle 660 129
0x8872f96b 622597 oracle 660 129
0x8872f96c 655366 oracle 660 129
0x9e25ad08 786439 oracle 640 154
0x78f75324 917512 oracle 640 154
0x0175c140 1048585 oracle 640 154
0x3b568778 1179658 oracle 640 154
------ Message Queues --------
key msqid owner perms used-bytes messages
*** 2014-09-08 13:50:36.495
Oradebug command 'ipc' console output:
Information written to trace file.
$ipcrm -m 98304
$ sqlplus sys as sysdba
SQL*Plus: Release 11.1.0.7.0 - Production on Mon Sep 8 14:07:41 2014
Copyright (c) 1982, 2008, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup mount
ORACLE instance started.
Total System Global Area 5344731136 bytes
Fixed Size 2159472 bytes
Variable Size 939527312 bytes
Database Buffers 4294967296 bytes
Redo Buffers 108077056 bytes
Database mounted.
Wonderful post, keep it up
ReplyDelete