12 Replies Latest reply: Apr 18, 2012 9:07 PM by rp0428 RSS

    Database Not Responding and not allowing even SYS user to login

    886410
      HI All,

      I have an Oracle enterprise 11gR2 installed on windows server But currently server has stopped responding and no body is able to login. It is not even allowing SYS user to login but however we were able to login through command prompt. WE tried running queries for checking locks but it gave us the error ORA-00604 error occurred at recursive SQL level and even we kill the session which were able but that all are showing in KILLED state and not disappearing when seen from session browser and with server as pseudo.

      After trying all this we decide to issue command shutd immediate but that command also thrown the error ORA-00604 and nothing is happening.

      We also checked the V$Resource_limit view and found 399 processes are being used out of max 400.


      Can anybody please help in solving or guiding what can be done in order ot bring the db up quickly.


      This needs to be fixed ASAP. request all community DBA to help
        • 1. Re: Database Not Responding and not allowing even SYS user to login
          Lubiez Jean-Valentin
          Hello,

          Can anybody please help in solving or guiding what can be done in order ot bring the db up quickly.
          Do you have a good Backup of this Database ?

          If yes, you may try to execute this from MS-DOS:
          set ORACLE_SID=<SID>
          sqlplus /nolog
          connect / as sysdba
          startup force
          exit
          Then, check the alert.log and post an extract of the last 50 lines so that we can see it.


          Hope this help.
          Best regards,
          Jean-Valentin
          • 2. Re: Database Not Responding and not allowing even SYS user to login
            886410
            Hi ,

            I don't have the back up of the db and I am not in the situation to loos the data. SO can you Please advise accordignly.

            Will this command execution ensure my data is not lost.

            I am not in the state to loose the data as it contains 15 schemas .


            Regards
            Atyant
            • 3. Re: Database Not Responding and not allowing even SYS user to login
              Lubiez Jean-Valentin
              Hello,

              Will this command execution ensure my data is not lost.
              The startup force will cause a shutdown abort and a startup. So, you can execute it, the Database will use the online redo logs to start. However it's not the normal way to start a Database.

              http://docs.oracle.com/cd/E11882_01/server.112/e25494/start001.htm#i1006285

              On the other hand, I don't know how is your Database now and, in this kind of situation, it's always better to have a BACKUP. It's even more necessary if you cannot loose data.


              Hope this help.
              Best regards,
              Jean-Valentin
              • 4. Re: Database Not Responding and not allowing even SYS user to login
                Mark D Powell
                If you are using 399 out of 400 allowed sessions I would start by trying to kill some of the sessions using alter system kill session 'sid,serial#'

                If that does not work then shutdown abort followed by a start up should after crash recovery completes clean up the problem on a temporarily basis. The issue being why do you have so many processes?

                There have been numerous reported cases of connection pooling software (web server) spawing more processes when the database was slow resulting in slowing the database even more which in turn causes more connections to be spawned until the maximum number of processes exist.

                I have also seen .net code spawn a new connection every time through a loop where the developer had rigged up connection reuse but it was not working as expected.

                Another major question is why do you not have a backup of the database? The primary job of a DBA is to be able to restore the database in the event of a diaster. A DBA who fails in this task should not have a job unless management has failed to provide requested resource necessary to perform the backup such failure to provide adequate disk space.

                If this is test then an export is likely good enough to qualify as a backup.

                Good Luck
                -- Mark D Powell --
                • 5. Re: Database Not Responding and not allowing even SYS user to login
                  886410
                  Thanks Mark for the suggestion. I would go for back-up . I have the back up of the db but it some days old. I would need to take the fresh backup of the DB.

                  We have already tried to kill session by using the command alter system kill session 'sid,serial#' but still no of processes in v$session_limit is not gettign reduced.

                  As i Had already mentioned in my thread, we are not able to issue command shutd immediate and are able to see thread which is marked as killed . Even when we are issuign command select * from v$session we are abel to see no of connections as such and no connections are gettign reduced.


                  Can You Please elaborate on this.
                  • 6. Re: Database Not Responding and not allowing even SYS user to login
                    sb92075
                    883407 wrote:
                    Can You Please elaborate on this.
                    terminate threads using Task Manager at OS level
                    • 7. Re: Database Not Responding and not allowing even SYS user to login
                      886410
                      Can You Please elaborate on this . This is a database connection are beign made to the DB
                      • 8. Re: Database Not Responding and not allowing even SYS user to login
                        scapy
                        Hi,

                        For windows use the orakill tool(DOS), search in google, how to kill the session, or use the taskkill in DOS.

                        See the follow site: http://www.dba-oracle.com/t_kill_process_windows.htm


                        Regards

                        Ruben Morais
                        • 9. Re: Database Not Responding and not allowing even SYS user to login
                          Mark D Powell
                          Sorry for the delay in response but I notice a couple of others did respond. Here are a couple of suggestions if the problem comes back.

                          Stop the listener to prevent remote clients from getting sessions while you make whatever changes you need to protect/fix the database.

                          Stop and re-start the Oracle service(s) to eliminate and clean up the existing sessions.


                          HTH -- Mark D Powell --
                          • 10. Re: Database Not Responding and not allowing even SYS user to login
                            Mark Malakanov (user11181920)
                            figure out the machine and program that mounts DoS attack.

                            select machine, program, count(*) from v$session group by machine, program order by 3;

                            get one with largest #.

                            go to the machine and kill the program to stop the attack.

                            then ether restart DB or run
                            select 'alter system kill session '''||sid||','||serial#||''';' from v$session where machine=<machine> and program=<program> ;

                            execute output.
                            • 11. Re: Database Not Responding and not allowing even SYS user to login
                              stuartu
                              One assumes you have the database in archivelog mode? (... since you don't backup often)

                              Please paste the recent entries from the alert log showing the errors relating to this as already requested.

                              Once you get your db up and running again and identify which user(s) were the culprit, you might want to look at altering their profile to limit their sessions.

                              The last time I had this happen to me, it was the DBSNMP (grid agent) user. My browser had hung, but something had obviously had gone beserk in the background.
                              • 12. Re: Database Not Responding and not allowing even SYS user to login
                                rp0428
                                >
                                we kill the session which were able but that all are showing in KILLED state and not disappearing
                                >
                                You want to DISCONNECT the sessions to get rid of the now; not kill them.

                                See the DISCONNECT SESSION Clause of ALTER SYSTEM in the SQL language Reference
                                http://docs.oracle.com/cd/B28359_01/server.111/b28286/statements_2013.htm
                                >
                                DISCONNECT SESSION Clause

                                Use the DISCONNECT SESSION clause to disconnect the current session by destroying the dedicated server process (or virtual circuit if the connection was made by way of a Shared Sever).
                                >
                                See the KILL SESSION Clause for the difference.

                                A DISCONNECT terminates the process now and frees the associated resources while KILL says wait for things to complete. Similar to the difference between SHUTDOWN IMMEDIATE and SHUTDOWN ABORT.

                                By the way are there any logon triggers that are active?