For some odd reason, we were seeing a message indicating that the database was down:
[oracle@proddb1 trace]$ sqlplus / as sysdba SQL*Plus: Release 12.1.0.2.0 Production on Thu Nov 30 23:21:39 2017 Copyright (c) 1982, 2014, Oracle. All rights reserved. Connected to an idle instance. SQL>
All the Oracle processes were running
[oracle@proddb1 trace]$ ps -ef | grep -i mon 579 19405 1 0 Nov27 ? 00:00:21 ora_pmon_PRODDB1 579 19437 1 0 Nov27 ? 00:00:07 ora_smon_PRODDB1 579 19447 1 0 Nov27 ? 00:01:53 ora_mmon_PRODDB1 579 20369 1 0 Nov27 ? 00:00:02 ora_tmon_PRODDB1
Oracle SID, home, base etc were set correctly:
[oracle@proddb1 trace]$ echo $ORACLE_BASE;echo $ORACLE_HOME;echo $ORACLE_SID;hostname;uname -a /apps/opt/oracle/product /apps/opt/oracle/product/12.1.0.2/db_1 PRODDB1 proddb1.vzbi.com Linux proddb1.vzbi.com 3.10.0-327.el7.x86_64 #1 SMP Thu Oct 29 17:29:29 EDT 2015 x86_64 x86_64 x86_64 GNU/Linux
The one unusual thing was that the “*mon” processes were owned by userid 579 rather than “oracle”. Checked the /etc/passwd:
[oracle@proddb1]$ cat /etc/passwd | grep -i oracle oracle:x:1579:122:Oracle Support:/home/oracle:/bin/bash
The Systems Administrator had moved the Oracle userid from 579 to 1579 to allow FTP/SCP etc. commands. This was done while Oracle was running. I killed the SMON process to shut down the database and brought it back up. However, the issue persisted. I had to remove the below files:
[oracle@proddb1 ~]$ ls -la /var/tmp/.oracle total 8 drwxrwxrwt 2 root dba 4096 Nov 30 22:49 . drwxrwxrwt. 5 root root 4096 Nov 27 16:18 .. srwxrwxrwx 1 579 dba 0 Nov 22 19:55 s#13746.1 srwxrwxrwx 1 579 dba 0 Nov 22 19:55 s#13746.2 srwxrwxrwx 1 579 dba 0 Nov 30 22:49 s#18231.1 srwxrwxrwx 1 579 dba 0 Nov 30 22:49 s#18231.2 srwxrwxrwx 1 579 dba 0 Nov 10 19:42 s#20024.1 srwxrwxrwx 1 579 dba 0 Nov 10 19:42 s#20024.2 srwxrwxrwx 1 579 dba 0 Nov 22 18:26 s#23729.1 srwxrwxrwx 1 579 dba 0 Nov 22 18:26 s#23729.2 srwxrwxrwx 1 579 dba 0 Nov 22 18:16 s#3272.1 srwxrwxrwx 1 579 dba 0 Nov 22 18:16 s#3272.2 srwxrwxrwx 1 579 dba 0 Nov 22 21:10 s#8843.1 srwxrwxrwx 1 579 dba 0 Nov 22 21:10 s#8843.2 srwxrwxrwx 1 579 dba 0 Nov 30 22:49 sREGISTER_PROD [oracle@proddb1 datapump]$ ls -l /dev/shm total 9348 -rwxrwx--- 1 579 dba 4096 Nov 27 21:11 JOXSHM_EXT_0_PRODDB1_458756 -rwxrwx--- 1 579 dba 4096 Nov 27 21:11 JOXSHM_EXT_100_PRODDB1_458756 -rwxrwx--- 1 579 dba 4096 Nov 27 21:11 JOXSHM_EXT_101_PRODDB1_458756 -rwxrwx--- 1 579 dba 4096 Nov 27 21:11 JOXSHM_EXT_102_PRODDB1_458756 -rwxrwx--- 1 579 dba 8192 Nov 27 21:11 JOXSHM_EXT_103_PRODDB1_458756 -rwxrwx--- 1 579 dba 4096 Nov 27 21:11 JOXSHM_EXT_104_PRODDB1_458756 -rwxrwx--- 1 579 dba 4096 Nov 27 21:11 JOXSHM_EXT_105_PRODDB1_458756
In addition to the above, the listener and other files/semaphores were owned by the “old” oracle id (579). Finally, we decided to bounce the server to get rid of all the old semaphores.