1 2 Previous Next 25 Replies Latest reply: Oct 11, 2013 5:32 AM by yxes2013 RSS

    Weird Db behavior

    yxes2013

      Hi all,

       

      11.2.0.3

      Aix 6.1

       

      It has been two(2) weeks now that every Saturday our system encounters maximun 200 process exceeded.

      But during weekdays it does not hit this error. How can I trace what causes this abnormal maximum process error?

      Then our OracleHome get 100% usage. I deleted all *.trc *.trm and reset network.log.

      Whats else logs do I need to delete at OracleHome?

       

       

      Thanks a lot,

      zxy

        • 1. Re: Weird Db behavior
          Alvaro

          1) The .trcs you are deleting are gold information on your issue. Next time read them to figure out where these processes are coming.

           

          2) It sounds like it's some batch processing scheduled for saturday that floods your database with sessions or processes. Check v$session/v$process at the time the issue is happening to find out where they are coming from and what are they doing.

           

          3) Navigate to the root folder of the file system and run: du -m ./* | sort -n | tail -20   That will give you the top 20 biggest folders along with their total MB size.

           

          4) It could also be the case that some queries and/or jobs are implementing a high degree of parallelism which would also fill up to your maximum processes parameter giving you that error. (depends also on your parallel_max_servers)

           

          By the way, the DB is victim here. It's simply being flooded with sessions/processes by someone or something. It's up to you to find what/who.

           

          Regards

          • 2. Re: Weird Db behavior
            sb92075

            What exactly do you know about maintaining & troubleshooting Oracle DB, besides asking for your hand to be held by folks here?

             

            >It has been two(2) weeks now that every Saturday our system encounters maximun 200 process exceeded.

            back out the change that was made 2 weeks ago

             

            >Whats else logs do I need to delete at OracleHome?

             

            stop fighting the symptom & stop the errant files from being generated in the first place.

            • 3. Re: Weird Db behavior
              yxes2013

              Thanks Al,

               

              Can I not find it is AWR, ASH, ADDM repors?

              • 4. Re: Weird Db behavior
                sb92075

                yxes2013 wrote:

                 

                Thanks Al,

                 

                Can I not find it is AWR, ASH, ADDM repors?

                 

                you are a clueless twit that needs to be replaced with somebody who knows more than spouting acronyms totally out of context.

                • 5. Re: Weird Db behavior
                  Alvaro

                  yxes2013 escreveu:

                   

                  Thanks Al,

                   

                  Can I not find it is AWR, ASH, ADDM repors?

                  Yes you could infer it through a differential-AWR.

                   

                  Compare two range of snapshots, one from where the issue is happening and a baseline (normal).

                   

                  In case of sessions:

                   

                  Compare number of sessions as described in the AWR header session.

                   

                  In case of PQs:

                   

                  On instance activity session of  the AWR: Compare stat : "queries parallelized"

                  Watch for the wait events on the problem snapshot that start with PX which would indicate parallel query processing.

                   

                   

                  Regards

                  • 6. Re: Weird Db behavior
                    yxes2013

                    Thanks again

                    • 7. Re: Weird Db behavior
                      yxes2013

                      Hi Again Al,

                       

                      I run :

                      du -m ./* | sort -n | tail -20

                      586.93  ./app/oracle/diag/rdbms/prod/prod

                      611.77  ./app/oracle/diag/rdbms

                      670.71  ./app/oracle/diag

                      963.43  ./app/oracle/product/11.2.0/dbhome_1/lib

                      1202.77 ./app/oracle/product/11.2.0.3/dbhome_1/lib

                      2351.93 ./oracle11g_installers/database/stage/Components

                      2609.77 ./app/oracle/product/11.2.0/dbhome_1/bin

                      2610.39 ./oracle11g_installers/database/stage

                      2653.79 ./oracle11g_installers/database

                      3328.52 ./app/oracle/product/11.2.0.3/dbhome_1/bin

                      4901.28 ./oracle11g_installers.tar.gz

                      5106.13 ./oracle11g_installers

                      6711.24 ./app/oracle/product/11.2.0

                      6711.24 ./app/oracle/product/11.2.0/dbhome_1

                      7986.20 ./prod_dbhome_1.bkp

                      8301.30 ./app/oracle/product/11.2.0.3

                      8301.30 ./app/oracle/product/11.2.0.3/dbhome_1

                      15012.54        ./app/oracle/product

                      15756.32        ./app/oracle

                      15763.55        ./app

                      =====================

                       

                      But its  the folder that is being displayed. Is there a way to display the file itself?

                       

                      Thanks,

                      zxy

                      • 8. Re: Weird Db behavior
                        sb92075

                        >But its  the folder that is being displayed. Is there a way to display the file itself?

                        yes

                        • 9. Re: Weird Db behavior
                          yxes2013

                          How dear...I will give a penny for your thought

                          • 10. Re: Weird Db behavior
                            sb92075

                            yxes2013 wrote:

                             

                            How dear...I will give a penny for your thought

                            how do  you have your OS report/display the size of any file?

                            • 11. Re: Weird Db behavior
                              yxes2013

                              ls -lt ? ls -l ? ls -la? I give up

                              • 12. Re: Weird Db behavior
                                sb92075

                                >I give up

                                Please do  & don't return.

                                • 13. Re: Weird Db behavior
                                  jgarry

                                  What do you want to do, sort by file size?  I use ls -lR|sort -nrk5|more (exact command varies by OS).  But there are probably better ways, you might google for find command by file size.  Or do you want to list the files in the directories with the largest du?

                                  • 14. Re: Weird Db behavior
                                    yxes2013

                                    Thanks Jg, It works fine in Aix

                                    1 2 Previous Next