14 Replies Latest reply on Mar 6, 2013 1:00 PM by FireFox88

    Oracle instance often down!!!

    FireFox88
      Hi

      I've have an Oracle 10g (10.2.0.4) 64bits database installed on a x64 window server. The server itself has 16gb ram. My current SGA + PGA is about 4-5gb memory, processes=500 but I am still getting random problem like oracle instance killed/down, low swap page memory from OS, listener could not hand off client connection etc. Should it be the existing server RAM(10-11gb RAM free after deduct from the SGA+PGA allocated) is more than enough?
        • 1. Re: Oracle instance often down!!!
          Fran
          post last 50 lines of alert.log.
          and
          listener.log

          FireFox88     
                
                
          Handle:      FireFox88  
          Status Level:      Newbie
          Registered:      Nov 22, 2009
          Total Posts:      39
          Total Questions:      20 (17 unresolved)
          Name      FireFox
          why so many unresolved questions?

          Edited by: Fran on 05-mar-2013 4:49
          • 2. Re: Oracle instance often down!!!
            FireFox88
            because most of my questions are not answered or solve.


            Back to my question. I did monitor my database for quite sometimes and I am really sure it's something to do with the server ram utilization. I just want to know how does oracle database utilize the server ram? SGA+PGA isn't all about what the database is going to use?

            FYI. my database is a dedicated database setting and it does run quite a lot heavy queries.

            Edited by: FireFox88 on Mar 5, 2013 5:04 AM
            • 3. Re: Oracle instance often down!!!
              John Stegeman
              It's more likely (based on looking at a few of your questions) that people are offering you solutions and that you are not going back and marking your questions as answered or reviewing the solutions being offered.
              • 4. Re: Oracle instance often down!!!
                vlethakula
                paste your alert log and related trace file
                • 5. Re: Oracle instance often down!!!
                  Nicolas.Gasparotto
                  FireFox88 wrote:
                  ... I am really sure it's something to do with the server ram utilization.
                  How comes ? Why do you think so ? Any other db or processes on the server ? Are you willing to paste alert.log ?

                  Nicolas.
                  • 6. Re: Oracle instance often down!!!
                    FireFox88
                    well..like I said most of the solution offered is not solving the problem I faced and no further follow up advices

                    Edited by: FireFox88 on Mar 5, 2013 5:14 AM
                    • 7. Re: Oracle instance often down!!!
                      FireFox88
                      i will paste the alert log once i got back to the office.
                      • 8. Re: Oracle instance often down!!!
                        Billy~Verreynne
                        So why not post some follow up for this thread as requested? What does the alert log say?

                        The database just does not terminate for the heck of it. And when a database server process does an abnormal database instance termination, there is a reason for it - which would be found the majority of times in the alert log. Exceptions being a kernel crash (oracle server processes do not get a chance to respond, or full file system and no more space for writing alert log entries). And you have not presented an exception.

                        So alert log please. Just the snippet before the new db instance start - where there should be a bunch of dumps and trace file create recorded, including a message along the lines of an Oracle system process initiating a shutdown. Likely some ORA 600 and 7445 errors too. This will contain the first hints of why a typically robust Oracle database, is not that, in your case.
                        • 9. Re: Oracle instance often down!!!
                          FireFox88
                          Hi

                          I will paste the trace file and alert log details once I got back to office.

                          So far from what I can recall my database server used to once a while getting "Significant virtual memory paging was detected on the host operating system" until i turn the OS page memory to system manage which is to use whatever hard disk space available, only then the warning seldom/rarely occur. listener could not hand off client connection is another error quite often prompt. When ever this error prompt, most of the time I've to bounce the database instance.
                          • 10. Re: Oracle instance often down!!!
                            marksmithusa
                            The fact that you're paging is a worry. It indicates that you do not have enough memory on the server.

                            Are you using sga_target/sga_max_size? What are they both set to?

                            Don't forget that pga_aggregate_target is just that: a target. If a process tries to do something insane, such as use 11,000 bind variables, the PGA will overflow and blow your server's memory out of the water.
                            1 person found this helpful
                            • 11. Re: Oracle instance often down!!!
                              Billy~Verreynne
                              FireFox88 wrote:

                              So far from what I can recall my database server used to once a while getting "Significant virtual memory paging was detected on the host operating system" until i turn the OS page memory to system manage which is to use whatever hard disk space available, only then the warning seldom/rarely occur. listener could not hand off client connection is another error quite often prompt. When ever this error prompt, most of the time I've to bounce the database instance.
                              Speculation - could be caused by PGA, as a result of poorly written PL/SQL code using very large PL/SQL arrays (for bulk collection). I have seen a single PGA (private process memory of a dedicated server process) growing to 50% the RAM size of the server. I have even seen a couple of server crashes due to the kernel being starved of CPU time due to swap space thrashing.

                              Before resetting a server in such a situation - get whatever diagnostics available. The problem will be gone (for the time being) after reboot, without the ability to get an actual look at just what the heck is going on. There are some magic sysrequest keys on Linux that is useful - all you need is the ability to echo specific keys to a proc device in order talk to the kernel and request various data and process dumps. Pity you are using Windows - not the easiest of kernels to troubleshoot.
                              1 person found this helpful
                              • 12. Re: Oracle instance often down!!!
                                jgarry
                                You might look at the advisors for SGA and especially PGA.
                                • 13. Re: Oracle instance often down!!!
                                  FireFox88
                                  SGA = 2gb
                                  PGA = 2gb

                                  So far never see it exceed 2gb PGA from PGA memory chart. Yes I can't deny it as the query is extremely huge as it join 3-4 tables that having avg of 30 fields and records around 5-10m each table.
                                  • 14. Re: Oracle instance often down!!!
                                    FireFox88
                                    In order to handle the the heavy complex query(direct joins with table) I've try to ease the database by creating MVs (with fast refresh) for these heavy queries.I am having MVs size 1.5x-2X of the total database size. I wonder does query this MVs that actually killing my database?

                                    FYI, my OS = window server 2003 Enterprise x64 with 16gb ram

                                    Latest error message reported by my colleague
                                    Listener refuse the connection with the following error : ORA-12516 TNS: Listener could not find available handler with matching protocol stack

                                    Edited by: FireFox88 on Mar 6, 2013 4:55 AM

                                    Edited by: FireFox88 on Mar 6, 2013 4:59 AM