1 2 Previous Next 22 Replies Latest reply on Nov 23, 2010 4:21 PM by 471379

    Can't connect to Oracle with sqlplus /nolog as sysdba

    471379
      Oracle has gone bad on the [Red Hat Enterprise Linux ES release 4 (Nahant Update 6)] box taking up all the CPU.

      When I try and connect with sqlplus /nolog as sysdba nothing happens, no output.

      Any ideas, apart from restarting the box please?

      We tried killing all the Oracle processes too & restarting the listener, but this just gives the same...

      Log follows...

      version = 10.2.0.1.0

      Oh, its happened before, last time we couldn't work it out either so restarted the box - it was fine then.


      $. oraenv
      ORACLE_SID = [oralt] ?
      dbhome
      ORACLE_HOME = /u01/oracle/oracle/product/10.2.0/db_1
      ORACLE_SID = oralt

      rh4-ora-t3:oracle:oralt/opt/oracle$ sqlplus /nolog

      (nothing)

      vi /u01/oracle/oracle/product/10.2.0/db_1/admin/oralt/bdump/alert_oralt.log

      Mon Nov 22 02:01:57 2010
      Memory Notification: Library Cache Object loaded into SGA
      Heap size 5118K exceeds notification threshold (2048K)
      Details in trace file /u01/oracle/oracle/product/10.2.0/db_1/admin/oralt/udump/oralt_ora_12801.trc
      KGL object name :SELECT /*+rule*/ SYS_XMLGEN(VALUE(KU$), XMLFORMAT.createFormat2('TABLE_T', '7')), KU$.OBJ_NUM FROM SYS.KU$_FHTABLE_VIEW KU$ WHERE NOT (BITAND (KU$.PROPERTY,8192)=8192) AND  NOT BITAND(KU$.SCHEMA_OBJ.FLAGS,128)!=0 AND  KU$.SCHEMA_OBJ.NAME=:NAME1 AND  KU$.SCHEMA_OBJ.OWNER_NAME=:SCHEMA2
      Mon Nov 22 11:13:21 2010
      Thread 1 advanced to log sequence 3280
      Current log# 3 seq# 3280 mem# 0: /u02/oradata/oralt/redo03_1.rdo
      Current log# 3 seq# 3280 mem# 1: /u03/oradata/oralt/redo03_2.log

      (end)


      rh4-ora-t3:oracle:oralt/opt/oracle$ top
      top - 10:27:16 up 199 days, 19:32, 2 users, load average: 24.82, 24.67, 24.19
      Tasks: 146 total, 30 running, 116 sleeping, 0 stopped, 0 zombie
      Cpu(s): 38.0% us, 62.0% sy, 0.0% ni, 0.0% id, 0.0% wa, 0.0% hi, 0.0% si
      Mem: 3115248k total, 2736140k used, 379108k free, 247956k buffers
      Swap: 6289436k total, 80168k used, 6209268k free, 2229880k cached

      PID USER PR NI %CPU TIME+ %MEM VIRT RES SHR S COMMAND
      2173 oracle 25 0 17 263:51.52 2.5 607m 74m 70m R oracle
      2211 oracle 25 0 17 260:43.53 0.7 620m 21m 10m R oracle
      2154 oracle 25 0 17 262:21.71 0.5 605m 14m 13m R oracle
      2159 oracle 25 0 17 262:38.20 0.8 604m 24m 23m R oracle
      2161 oracle 25 0 17 263:05.82 3.1 607m 93m 90m R oracle
      2163 oracle 25 0 17 261:55.21 0.7 620m 19m 18m R oracle
      2165 oracle 25 0 17 261:13.85 0.8 606m 24m 22m R oracle
      2169 oracle 25 0 17 261:47.42 0.8 605m 22m 21m R oracle
      2171 oracle 25 0 17 263:20.01 1.8 606m 55m 52m R oracle
      2175 oracle 25 0 17 263:03.81 0.7 604m 22m 21m R oracle
      2177 oracle 25 0 17 262:15.91 0.4 605m 10m 9500 R oracle
      2209 oracle 25 0 17 261:55.91 0.7 620m 22m 10m R oracle
      2745 oracle 25 0 17 257:08.69 0.5 604m 15m 14m R oracle
      6885 oracle 25 0 17 89:05.80 0.1 19652 4564 3228 R exp
      10344 oracle 25 0 17 1:26.30 0.2 91856 6584 5720 R oracle
      17568 oracle 25 0 17 263:29.11 0.7 605m 21m 19m R oracle
      22383 oracle 25 0 17 111:38.92 0.1 19576 4600 3280 R sqlplus
      27963 oracle 25 0 17 22:56.01 0.2 91856 6584 5720 R oracle
      8185 oracle 25 0 16 169:09.85 0.2 29816 6004 3300 R rman
      13633 oracle 25 0 16 261:50.72 0.5 605m 14m 13m R oracle
      12518 oracle 25 0 16 260:35.53 0.5 605m 14m 13m R oracle
      2156 oracle 25 0 15 262:28.54 0.4 604m 11m 9.9m R oracle
      2167 oracle 25 0 15 261:06.86 3.2 606m 98m 95m R oracle
      2669 oracle 25 0 15 261:46.97 0.6 604m 18m 16m R oracle
      15816 root 17 0 1 0:00.09 0.0 5680 1332 1052 R hpetfe
      15663 root 16 0 1 0:00.12 0.1 7968 2348 1852 R sshd
      15767 root 16 0 1 0:00.19 0.0 2156 1056 780 R top
      15768 oracle 16 0 1 0:00.15 0.0 1948 1048 784 R top
      1 root 16 0 0 0:58.86 0.0 2468 548 468 S init
      2 root RT 0 0 3:44.17 0.0 0 0 0 S migration/0
      3 root 34 19 0 0:12.75 0.0 0 0 0 S ksoftirqd/0
      4 root RT 0 0 4:24.86 0.0 0 0 0 S migration/1
      5 root 34 19 0 0:11.23 0.0 0 0 0 S ksoftirqd/1
      6 root RT 0 0 4:28.40 0.0 0 0 0 S migration/2
      7 root 34 19 0 0:12.55 0.0 0 0 0 S ksoftirqd/2
      8 root RT 0 0 3:59.44 0.0 0 0 0 S migration/3
      9 root 34 19 0 0:13.29 0.0 0 0 0 S ksoftirqd/3
      10 root 5 -10 0 0:00.84 0.0 0 0 0 S events/0
      11 root 5 -10 0 0:00.43 0.0 0 0 0 S events/1
      12 root 5 -10 0 0:01.82 0.0 0 0 0 S events/2
      13 root 5 -10 0 0:00.80 0.0 0 0 0 S events/3
      14 root 6 -10 0 3:08.62 0.0 0 0 0 S khelper
      15 root 5 -10 0 0:00.00 0.0 0 0 0 S kblockd/0


      #killed backkup jobs...
      rh4-ora-t3:oracle:oralt/opt/oracle$ kill 6885
      rh4-ora-t3:oracle:oralt/opt/oracle$ kill 8185
      rh4-ora-t3:oracle:oralt/opt/oracle$ kill 22383


      rh4-ora-t3:oracle:oralt/opt/oracle$ lsnrctl

      LSNRCTL for Linux: Version 10.2.0.1.0 - Production on 23-NOV-2010 10:37:39

      Copyright (c) 1991, 2005, Oracle. All rights reserved.

      Welcome to LSNRCTL, type "help" for information.

      LSNRCTL> status
      Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1)))
      STATUS of the LISTENER
      ------------------------
      Alias LISTENER
      Version TNSLSNR for Linux: Version 10.2.0.1.0 - Production
      Start Date 06-AUG-2010 14:57:55
      Uptime 108 days 20 hr. 39 min. 46 sec
      Trace Level off
      Security ON: Local OS Authentication
      SNMP ON
      Listener Parameter File /u01/oracle/oracle/product/10.2.0/db_1/network/admin/listener.ora
      Listener Log File /u01/oracle/oracle/product/10.2.0/db_1/network/log/listener.log
      Listening Endpoints Summary...
      (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1)))
      (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.1.209.208)(PORT=1521)))
      Services Summary...
      Service "PLSExtProc" has 1 instance(s).
      Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
      Service "oralt" has 1 instance(s).
      Instance "oralt", status READY, has 1 handler(s) for this service...
      Service "oraltXDB" has 1 instance(s).
      Instance "oralt", status READY, has 1 handler(s) for this service...
      Service "oralt_XPT" has 1 instance(s).
      Instance "oralt", status READY, has 1 handler(s) for this service...
      The command completed successfully
      LSNRCTL> stop
      Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1)))
      start

      (nothing)

      rh4-ora-t3:oracle:oralt/opt/oracle$ uptime
      10:38:35 up 199 days, 19:43, 2 users, load average: 23.13, 23.30, 23.62


      (lots of playing with listener - but it won't start and it won't let me connect with sqlplus /nolog)

      Killed all Oracle processors, still can't connect to sqlplus


      Tasks: 98 total, 1 running, 97 sleeping, 0 stopped, 0 zombie
      Cpu(s): 0.7% us, 0.2% sy, 0.0% ni, 99.1% id, 0.0% wa, 0.0% hi, 0.0% si
      Mem: 3115248k total, 2679604k used, 435644k free, 248572k buffers
      Swap: 6289436k total, 80168k used, 6209268k free, 2271904k cached

      PID USER PR NI %CPU TIME+ %MEM VIRT RES SHR S COMMAND
      1 root 16 0 0 0:58.87 0.0 2468 548 468 S init
      2 root RT 0 0 3:44.19 0.0 0 0 0 S migration/0
      3 root 34 19 0 0:12.75 0.0 0 0 0 S ksoftirqd/0
      4 root RT 0 0 4:24.88 0.0 0 0 0 S migration/1
      5 root 34 19 0 0:11.23 0.0 0 0 0 S ksoftirqd/1
      6 root RT 0 0 4:28.42 0.0 0 0 0 S migration/2
      7 root 34 19 0 0:12.55 0.0 0 0 0 S ksoftirqd/2
      8 root RT 0 0 3:59.45 0.0 0 0 0 S migration/3
      9 root 34 19 0 0:13.29 0.0 0 0 0 S ksoftirqd/3
      10 root 5 -10 0 0:00.84 0.0 0 0 0 S events/0
      11 root 5 -10 0 0:00.43 0.0 0 0 0 S events/1
      12 root 5 -10 0 0:01.82 0.0 0 0 0 S events/2
      13 root 5 -10 0 0:00.80 0.0 0 0 0 S events/3
      14 root 6 -10 0 3:08.63 0.0 0 0 0 S khelper
      15 root 5 -10 0 0:00.00 0.0 0 0 0 S kblockd/0
      16 root 5 -10 0 0:00.00 0.0 0 0 0 S kblockd/1
      17 root 5 -10 0 0:00.01 0.0 0 0 0 S kblockd/2
      18 root 5 -10 0 0:00.00 0.0 0 0 0 S kblockd/3
      19 root 15 0 0 0:00.00 0.0 0 0 0 S khubd
      69 root 15 0 0 3:20.26 0.0 0 0 0 S pdflush
      70 root 16 0 0 2:25.34 0.0 0 0 0 S pdflush
      71 root 16 0 0 53:43.69 0.0 0 0 0 S kswapd0
      72 root 6 -10 0 0:00.00 0.0 0 0 0 S aio/0
      73 root 6 -10 0 0:00.00 0.0 0 0 0 S aio/1
      74 root 6 -10 0 0:00.00 0.0 0 0 0 S aio/2
      75 root 6 -10 0 0:00.00 0.0 0 0 0 S aio/3
      219 root 25 0 0 0:00.00 0.0 0 0 0 S kseriod
      462 root 25 0 0 0:00.00 0.0 0 0 0 S scsi_eh_0
      463 root 15 0 0 0:00.00 0.0 0 0 0 S ahc_dv_0
      465 root 19 0 0 0:00.00 0.0 0 0 0 S scsi_eh_1
      466 root 15 0 0 0:00.00 0.0 0 0 0 S ahc_dv_1
      502 root 15 0 0 5:02.83 0.0 0 0 0 S kjournald
      1064 root 6 -10 0 0:00.00 0.0 0 0 0 S kauditd
      1683 root 6 -10 0 0:00.05 0.0 3336 448 368 S udevd
      1795 root 25 0 0 0:00.00 0.0 0 0 0 S phpd_event
      1957 root 15 0 0 0:00.01 0.0 0 0 0 S kjournald
      1958 root 15 0 0 0:00.01 0.0 0 0 0 S kjournald
      1959 root 15 0 0 0:01.44 0.0 0 0 0 S kjournald
      1960 root 15 0 0 0:12.13 0.0 0 0 0 S kjournald
      1961 root 15 0 0 0:04.08 0.0 0 0 0 S kjournald
      1962 root 15 0 0 0:03.89 0.0 0 0 0 S kjournald
      1963 root 15 0 0 0:04.93 0.0 0 0 0 S kjournald
      1964 root 15 0 0 39:23.43 0.0 0 0 0 S kjournald

      Edited by: sunpat on Nov 23, 2010 11:52 AM
        • 1. Re: Can't connect to Oracle with sqlplus /nolog as sysdba
          CKPT
          Hi,

          connect as
          sqlplus -prelim / as sysdba
          and post the errors from alert log file

          Thanks
          • 2. Re: Can't connect to Oracle with sqlplus /nolog as sysdba
            471379
            Thanks - here you go...

            rh4-ora-t3:oracle:oralt/opt/oracle$ sqlplus -prelim / as sysdba

            (nothing)

            tail /u01/oracle/oracle/product/10.2.0/db_1/admin/oralt/bdump/alert_oralt.log
            KGL object name :SELECT TOWNER, TNAME, NAME, LENGTH, PRECISION, SCALE, TYPE, ISNULL, CONNAME, COLID, INTCOLID, SEGCOLID, COMMENT$, DEFAULT$, DFLTLEN, ENABLED, DEFER, FLAGS, COLPROP, ADTNAME, ADTOWNER, CHARSETID, CHARSETFORM, FSPRECISION, LFPRECISION, CHARLEN, TFLAGS, TYPESYN, COLCLASS FROM SYS.EXU10COE WHERE TOBJID = :1 ORDER BY COLCLASS
            Mon Nov 22 02:01:57 2010
            Memory Notification: Library Cache Object loaded into SGA
            Heap size 5118K exceeds notification threshold (2048K)
            Details in trace file /u01/oracle/oracle/product/10.2.0/db_1/admin/oralt/udump/oralt_ora_12801.trc
            KGL object name :SELECT /*+rule*/ SYS_XMLGEN(VALUE(KU$), XMLFORMAT.createFormat2('TABLE_T', '7')), KU$.OBJ_NUM FROM SYS.KU$_FHTABLE_VIEW KU$ WHERE NOT (BITAND (KU$.PROPERTY,8192)=8192) AND  NOT BITAND(KU$.SCHEMA_OBJ.FLAGS,128)!=0 AND  KU$.SCHEMA_OBJ.NAME=:NAME1 AND  KU$.SCHEMA_OBJ.OWNER_NAME=:SCHEMA2
            Mon Nov 22 11:13:21 2010
            Thread 1 advanced to log sequence 3280
            Current log# 3 seq# 3280 mem# 0: /u02/oradata/oralt/redo03_1.rdo
            Current log# 3 seq# 3280 mem# 1: /u03/oradata/oralt/redo03_2.log

            (nothing new)

            rh4-ora-t3:oracle:oralt/opt/oracle$
            • 3. Re: Can't connect to Oracle with sqlplus /nolog as sysdba
              CKPT
              Hi,

              these are due to memory fragmentation.

              read *Memory Notification: Library Cache Object Loaded Into Sga [ID 330239.1]*

              Thanks
              • 4. Re: Can't connect to Oracle with sqlplus /nolog as sysdba
                471379
                "Memory Notification: Library Cache Object loaded into SGA"

                this warning occurs nearly every day - and doesn't seem to have a detrimental effect on the server. It's been running fine with this warning since Aug, so I don't believe that is the problem now.
                • 5. Re: Can't connect to Oracle with sqlplus /nolog as sysdba
                  Billy~Verreynne
                  sunpat wrote:
                  Oracle has gone bad on the [Red Hat Enterprise Linux ES release 4 (Nahant Update 6)] box taking up all the CPU.
                  Oracle very seldom goes bad - typically it is query or workload from hell that a client demands to be executed. And as the client is always right (as long as the syntax check out and the code can be compiled), Oracle has no choice other but to execute it.
                  When I try and connect with sqlplus /nolog as sysdba nothing happens, no output.
                  Things may not be what they seem - thanks to alias or paths or other factors in the shell environment.

                  Set your Oracle environment as you did. Then fully qualify the path and executable and provide the I-am-a-dba-god authentication details:

                  +$ORACLE_HOME/bin/sqlplus "/ as sysdba"+
                  We tried killing all the Oracle processes too & restarting the listener, but this just gives the same...
                  Which processes would that be? Was the database's shared memory forcible released afterward?
                  • 6. Re: Can't connect to Oracle with sqlplus /nolog as sysdba
                    471379
                    $ORACLE_HOME/bin/sqlplus "/ as sysdba"

                    also gives no output.

                    In fact this normally works, so I'm confident it's not an environmental variable problem.
                    sqlplus /nolog as sysdba

                    I think we're hitting an Oracle bug to be honest, since it has occurred before.

                    No strange SQL on the server overnight and that still doesn't explain why everything is hanging on the server when there is no CPU load now.

                    thanks
                    • 7. Re: Can't connect to Oracle with sqlplus /nolog as sysdba
                      CKPT
                      have you tried..

                      sqlplus -prelim / as sysdba
                      :(
                      • 8. Re: Can't connect to Oracle with sqlplus /nolog as sysdba
                        471379
                        Suggested before, yes, thanks.

                        rh4-ora-t3:oracle:oralt/opt/oracle$ sqlplus -prelim / as sysdba

                        [no output]
                        [nothing extra in alert file]


                        Looks like the reboot is the only option again then...

                        Edited by: sunpat on Nov 23, 2010 2:45 PM
                        • 9. Re: Can't connect to Oracle with sqlplus /nolog as sysdba
                          CKPT
                          if any database hangs, by reboot the problem can be solved..

                          there is no even issues with swapping... better if you reboot instead of downtime...
                          • 10. Re: Can't connect to Oracle with sqlplus /nolog as sysdba
                            user18198
                            Cpu(s): 38.0% us, 62.0% sy, 0.0% ni, 0.0% id, 0.0% wa, 0.0% hi, 0.0% si

                            the problem may be your server is too busy. "0.0% id" from the top output.

                            how many RAM do you have? and how many ram does oracle use?
                            • 11. Re: Can't connect to Oracle with sqlplus /nolog as sysdba
                              sb92075
                              Looks like the reboot is the only option again then...
                              This is KNOWN bug & yes reboot will fix it.

                              post results from
                              uptime

                              This bug manifests itself after OS has been up awhile (like around 200 days).
                              1 person found this helpful
                              • 12. Re: Can't connect to Oracle with sqlplus /nolog as sysdba
                                471379
                                Tried to start the lintener again...

                                top -U oracle

                                top - 14:52:46 up 199 days, 23:57, 2 users, load average: 1.15, 1.26, 0.98
                                Tasks: 97 total, 1 running, 96 sleeping, 0 stopped, 0 zombie
                                Cpu(s): 0.2% us, 0.2% sy, 0.0% ni, 99.6% id, 0.0% wa, 0.0% hi, 0.0% si
                                Mem: 3115248k total, 2679996k used, 435252k free, 249164k buffers
                                Swap: 6289436k total, 80168k used, 6209268k free, 2272352k cached

                                PID USER PR NI %CPU TIME+ %MEM VIRT RES SHR S COMMAND
                                3646 oracle 16 0 0 0:00.01 0.0 3872 1016 784 R top
                                13305 oracle 16 0 0 0:00.41 0.0 8268 1464 1008 S sshd
                                13306 oracle 15 0 0 0:00.42 0.0 4516 1544 1212 S bash

                                [nothing running]

                                rh4-ora-t3:oracle:oralt/opt/oracle$ lsnrctl

                                LSNRCTL for Linux: Version 10.2.0.1.0 - Production on 23-NOV-2010 14:53:16

                                Copyright (c) 1991, 2005, Oracle. All rights reserved.

                                Welcome to LSNRCTL, type "help" for information.

                                LSNRCTL> status
                                Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1)))
                                TNS-12541: TNS:no listener
                                TNS-12560: TNS:protocol adapter error
                                TNS-00511: No listener
                                Linux Error: 111: Connection refused
                                Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.1.209.208)(PORT=1521)))
                                TNS-12541: TNS:no listener
                                TNS-12560: TNS:protocol adapter error
                                TNS-00511: No listener
                                Linux Error: 111: Connection refused
                                LSNRCTL> start
                                Starting /u01/oracle/oracle/product/10.2.0/db_1/bin/tnslsnr: please wait...

                                [no more feedback]

                                rh4-ora-t3:oracle:oralt/opt/oracle$ tail /u01/oracle/oracle/product/10.2.0/db_1/network/log/listener.log
                                Copyright (c) 1991, 2005, Oracle. All rights reserved.

                                System parameter file is /u01/oracle/oracle/product/10.2.0/db_1/network/admin/listener.ora
                                Log messages written to /u01/oracle/oracle/product/10.2.0/db_1/network/log/listener.log
                                Trace information written to /u01/oracle/oracle/product/10.2.0/db_1/network/trace/listener.trc
                                Trace level is currently 0

                                Started with pid=4240
                                Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1)))
                                Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.1.209.208)(PORT=1521)))

                                rh4-ora-t3:oracle:oralt/opt/oracle$ top -U oracle

                                top - 14:54:19 up 199 days, 23:59, 2 users, load average: 1.03, 1.13, 0.95
                                Tasks: 98 total, 2 running, 96 sleeping, 0 stopped, 0 zombie
                                Cpu(s): 10.5% us, 16.0% sy, 0.0% ni, 73.5% id, 0.0% wa, 0.0% hi, 0.0% si
                                Mem: 3115248k total, 2681772k used, 433476k free, 249188k buffers
                                Swap: 6289436k total, 80168k used, 6209268k free, 2272328k cached

                                PID USER PR NI %CPU TIME+ %MEM VIRT RES SHR S COMMAND
                                4240 oracle 25 0 100 0:54.82 0.2 22212 5364 3748 R tnslsnr
                                5349 oracle 16 0 0 0:00.03 0.0 3324 1016 784 R top
                                13305 oracle 16 0 0 0:00.42 0.0 8268 1464 1008 S sshd
                                13306 oracle 16 0 0 0:00.45 0.0 4516 1544 1212 S bash

                                [100% cpu - nice!]
                                • 13. Re: Can't connect to Oracle with sqlplus /nolog as sysdba
                                  471379
                                  rh4-ora-t3:oracle:oralt/opt/oracle$ uptime
                                  14:55:58 up 200 days, 0 min, 2 users, load average: 1.04, 1.10, 0.96

                                  200 DAYS !!!
                                  • 14. Re: Can't connect to Oracle with sqlplus /nolog as sysdba
                                    sb92075
                                    Tried to start the lintener again...
                                    NO listener is required to connect to local DB

                                    post results from following OS command

                                    uptime
                                    1 2 Previous Next