database is shutting down frequently
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 8 of 8

Thread: database is shutting down frequently

  1. #1
    Join Date
    Apr 2009
    Posts
    17

    database is shutting down frequently

    My database is shutting down frequently (once in 24 hours)

    Please see the below Alert file and trace file and suggest me what should I do


    Alert file
    =========
    Mon May 04 11:44:05 2009
    Process q002 started up but failed with error = 20
    Process q002 started up but failed with error = 20
    Process q002 started up but failed with error = 20
    Process q002 started up but failed with error = 20
    Process q002 started up but failed with error = 20
    Mon May 04 11:49:18 2009
    Process q005 started up but failed with error = 20
    Process q005 started up but failed with error = 20
    Process q005 started up but failed with error = 20
    Process q005 started up but failed with error = 20
    Process q005 started up but failed with error = 20
    Mon May 04 11:51:02 2009
    Private_strands 7 at log switch
    Thread 1 advanced to log sequence 44692
    Current log# 3 seq# 44692 mem# 0: D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCLJDE\REDO03.LOG
    Mon May 04 11:52:31 2009
    Errors in file d:\oracle\product\10.1.0\admin\orcljde\udump\orcljde_ora_3800.trc:
    ORA-25254: time-out in LISTEN while waiting for a message
    ORA-06512: at “SYS.DBMS_AQ”, line 577
    ORA-06512: at “SYSMAN.EMD_NOTIFICATION”, line 492
    ORA-06512: at line 1
    ————————————————————————————————

    When I checked individual trace file it shows as below
    =====================================

    Dump file d:\oracle\product\10.1.0\admin\orcljde\bdump\orcljde_ora_4392.trc
    Mon May 04 11:02:28 2009
    ORACLE V10.1.0.2.0 - Production vsnsta=0
    vsnsql=13 vsnxtr=3
    Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Production
    With the Partitioning, OLAP and Data Mining options
    Windows Server 2003 Version V5.2 Service Pack 2
    CPU : 4 - type 586, 2 Physical Cores
    Process Affinity: 0Χ00000000
    Memory (A/P) : PH:20392M/24575M, PG:27290M/32151M, VA:940M/2047M
    Instance name: orcljde

    Redo thread mounted by this instance: 1

    Oracle process number: 0

    4392

    Died during process startup with error 20 (seq=1464)
    OPIRIP: Uncaught error 20. Error stack:
    ORA-00020: maximum number of processes (150) exceeded


    Plz help me

  2. #2
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    ORA-00020: maximum number of processes (150) exceeded

    How about starting by pumping up processes and sessions values?
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  3. #3
    Join Date
    Apr 2009
    Posts
    17
    Yes ,To increase the number of processes.

    Current value is 150 . Im planning to increase it to 200 or 250.

    But my worry is, will it affect anywhere else in the system.. Do I need to provide more space in SGA or PGA? because we have only 4Gb RAM in which I have mentioned SGA = 700 MB and PGA = 100 MB..And I have enabled Automatic Share Memory Management.

    Please let me know which part of the system will affected by increasing the number of processes? Also what are all the negative part (if any) by increasing the processes

  4. #4
    Join Date
    Nov 2002
    Location
    Mooresville, NC
    Posts
    349
    You are executing with only 700MB of SGA. Did you check buffer pool advisory? Try what PAVB mentioned and let us know if that helps.
    http://www.perf-engg.com
    A performance engineering forum

  5. #5
    Join Date
    Oct 2006
    Posts
    175
    Why is PGA only 100m? How many concurrent users work in a time? I also doubt you've your db in dedicated server mode.

  6. #6
    Join Date
    Apr 2009
    Posts
    17
    our server is dedicated server. and we have hardly 100 users logging at a time.

    Currently we have enabled ASMM (Automatic Shared Memory Management). So I think even if I increase the processes ,Shared pool size will be taken care by ASMM (as no manual control due to enabling ASMM)

    hope increasing processes will not affect sharedpool and other areas.. if you know what other areas it can affect please let me know.. So that i can be more cautious in that relevant areas.

    Thanks

  7. #7
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    Quote Originally Posted by nishantprabha View Post
    if you know what other areas it can affect please let me know.
    Yes, we know.

    Do you know why we know? in most cases because we bother in reading the documentation.

    Take a look here http://download-east.oracle.com/docs...43/process.htm
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  8. #8
    Join Date
    Oct 2006
    Posts
    175
    Please go through the document suggested by PAVB. This will surely help you learn more about processes with Oracle, giving you closer look at Oracle architecture and how it functions.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width