Hi,
I have Linux server which contains five oracle instances. One instance for ASM and then two development and two test instances. The servers OS is Enterprise Linux Enterprise Linux Server release 5.1 (Carthage) and Oracle database is 11g EE R11.1.0.6.0 - 64 bit. the memory settings are hand tailored by a person who has installed those Oracle instances. I am a very rookie with DBA stuff...
For some reason I suddenly started to get ORA-00018 and ORA-00020. I cannot log in as sysdba because it won't let me in. I heard that other team had same problems (two of the instances are theirs) and they killed some processess (I don't know which one...)...
I checked instances alert logs and found out that:
INSTANCE1:
Fatal NI connect error 12170.
VERSION INFORMATION:
TNS for Linux: Version 11.1.0.6.0 - Production
Oracle Bequeath NT Protocol Adapter for Linux: Version 11.1.0.6.0 - Production
TCP/IP NT Protocol Adapter for Linux: Version 11.1.0.6.0 - Production
Time: 20-JUL-2009 15:55:27
Tracing not turned on.
Tns error struct:
ns main err code: 12535
TNS-12535: TNS:operation timed out
ns secondary err code: 12606
nt main err code: 0
nt secondary err code: 0
nt OS err code: 0
Client address: (ADDRESS=(PROTOCOL=tcp)(HOST=xxx.xxx.xxx.xxx)(PORT=xxxx))
WARNING: inbound connection timed out (ORA-3136)
PMON (ospid: 31934): terminating the instance due to error 56710
Mon Jul 20 15:55:27 2009
ORA-1092 : opiodr aborting process unknown ospid (29935_46912513465440)
Mon Jul 20 15:55:27 2009
ORA-1092 : opitsk aborting process
Instance terminated by PMON, pid = 31934
INSTANCE2:
Mon Jul 20 16:06:30 2009
Errors in file /u01/xxx/INSTANCE2/trace/INSTANCE2_j006_15450.trc:
ORA-27468: "EXFSYS.RLM$EVTCLEANUP" is locked by another process
Mon Jul 20 16:06:30 2009
Errors in file /u01/xxx/INSTANCE2/trace/INSTANCE2_j053_31481.trc:
ORA-00018: maximum number of sessions exceeded
ORA-00822: MMAN-process terminated with error
ORA-1092 : opidrv aborting process J053 ospid (31481_46912513465440)
Mon Jul 20 16:06:30 2009
Errors in file /u01/xxx/INSTANCE2/trace/INSTANCE2_j041_15932.trc:
ORA-00822: MMAN-process terminated with error
ORA-1092 : opidrv aborting process J041 ospid (15932_46912513465440)
Instance terminated by PMON, pid = 30908
Then I noticied that also in our teams two instances had the same problems. INSTANCE3 four told about ORA-00018 and INSTANCE4 about ORA-00020 and listener when trying to log in as sysdba.
Also I tried to clean up some processes from INSTANCE3. I cleaned up processes that had LOCAL=NO
ps -ef | grep 'INSTANCE3 (LOCAL=NO)'
ps -ef | grep 'INSTANCE3 (LOCAL=NO)' | awk '{print $2}' | xargs kill -9
ps -ef | grep 'INSTANCE3 (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))'
ps -ef | grep 'INSTANCE3 (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))' | awk '{print $2}' | xargs kill -9
After that I could log in as sysdba. I choosed to shutdown the INSTANCE3. Shutdown immediate seemed to hang so I made shutdown abort. But when trying to to get it up to do shutdown immediate, it didn't work. I mean the INSTANCE3 didn't start up. For the INSTANCE4 I also tried to clean up processess (there where a pile of old ora_j* processes and I killed them. But still I could not log in as sysdba. Same ORA-00020.
Then I thought I will shut down all the instances using dbshut and ensuring that all 5 instances has option Y in oratab file. dbshut executes in a second and result is following:
ASM:
SQL*Plus: Release 11.1.0.6.0 - Production on Tue Jul 21 14:55:54 2009
Copyright (c) 1982, 2007, Oracle. All rights reserved.
SQL> Connected.
SQL> ORA-15097: cannot SHUTDOWN ASM instance with connected RDBMS instance
SQL> Disconnected from Oracle Database 11g Enterprise Edition Release 11.1.0.6.0
- 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ASM instance "ASM" shut down.
INSTANCE1:
SQL*Plus: Release 11.1.0.6.0 - Production on Tue Jul 21 14:55:54 2009
Copyright (c) 1982, 2007, Oracle. All rights reserved.
SQL> Connected to an idle instance.
SQL> ORA-01034: ORACLE not available
ORA-27101: shared memory realm does not exist
Linux-x86_64 Error: 2: No such file or directory
SQL> Disconnected
Database instance "INSTANCE1" shut down.
INSTANCE2:
SQL*Plus: Release 11.1.0.6.0 - Production on Tue Jul 21 14:55:54 2009
Copyright (c) 1982, 2007, Oracle. All rights reserved.
SQL> Connected to an idle instance.
SQL> ORA-01034: ORACLE not available
ORA-27101: shared memory realm does not exist
Linux-x86_64 Error: 2: No such file or directory
SQL> Disconnected
Database instance "INSTANCE2" shut down.
INSTANCE3:
SQL*Plus: Release 11.1.0.6.0 - Production on Tue Jul 21 11:00:16 2009
Copyright (c) 1982, 2007, Oracle. All rights reserved.
SQL> Connected.
SQL>
SQL*Plus: Release 11.1.0.6.0 - Production on Tue Jul 21 14:55:54 2009
Copyright (c) 1982, 2007, Oracle. All rights reserved.
SQL> Connected to an idle instance.
SQL> ORA-01034: ORACLE not available
ORA-27101: shared memory realm does not exist
Linux-x86_64 Error: 2: No such file or directory
SQL> Disconnected
Database instance "INSTANCE3" shut down.
INSTANCE4:
SQL*Plus: Release 11.1.0.6.0 - Production on Tue Jul 21 14:55:54 2009
Copyright (c) 1982, 2007, Oracle. All rights reserved.
SQL> ERROR:
ORA-00020: maximum number of processes (%s) exceeded
SQL> ORA-24324: service handle not initialized
ORA-01041: internal error. hostdef extension doesn't exist
SQL> Database instance "INSTANCE4" shut down.
And if I try to startup with dbstart, at first it will try to start the ASM instance. The starting phase "hangs" with ASM.
What can I do? Boot the server and cross my hands that everything comes up (including ASM)? Or is there anything I could do with Oracle anymore? How can I ensure all the instances are nicely shutted down so I could start them with dbstart? And how can I shut down that INSTANCE4 (with ora-00020)? Do I dare to delete more processes?
Best regards,
Juha