1 2 3 Previous Next 42 Replies Latest reply: Jul 4, 2013 3:26 AM by Maahjoor RSS

    1000 concurent users connection problem.

    Maahjoor

      Dear all,

      i have the following structure using window server 2012 64 bit.

      SQL> select * from v$version;
      BANNER
      ----------------------------------------------------------------
      Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Prod
      PL/SQL Release 10.2.0.3.0 - Production
      CORE    10.2.0.3.0      Production
      TNS for 32-bit Windows: Version 10.2.0.3.0 - Production
      NLSRTL Version 10.2.0.3.0 - Production
      
      SQL>  SHOW PARAMETER TARGET
      NAME                                 TYPE        VALUE
      ------------------------------------ ----------- ------------------------------
      archive_lag_target                   integer     0
      db_flashback_retention_target        integer     1440
      fast_start_io_target                 integer     0
      fast_start_mttr_target               integer     0
      pga_aggregate_target                 big integer 399M
      sga_target                           big integer 1200M
      
      SQL> select * from v$resource_limit where
        2  resource_name='processes';
      RESOURCE_NAME                  CURRENT_UTILIZATION MAX_UTILIZATION INITIAL_AL LIMIT_VALU
      ------------------------------ ------------------- --------------- ---------- ----------
      processes                                       29              38        300        300
      

      i have 1000 concurent students to login with different applications like sqlplus forms etc.

      the problem is often they face th following error

      ORA-12519: TNS:no appropriate service handler found
      

       

      what sould i increase in my oracle server to avoid such a problem?

      should i convert my database to shared server mode?

      i have 8 gb RAM.

      kindly guide accordingly.

      Regards.

        • 1. Re: 1000 concurent users connection problem.
          EdStevens

          Maahjoor wrote:

           

          Dear all,

          i have the following structure using window server 2012 64 bit.

          1. SQL> select * from v$version; 
          2. BANNER 
          3. ---------------------------------------------------------------- 
          4. Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Prod 
          5. PL/SQL Release 10.2.0.3.0 - Production 
          6. CORE    10.2.0.3.0      Production 
          7. TNS for 32-bit Windows: Version 10.2.0.3.0 - Production 
          8. NLSRTL Version 10.2.0.3.0 - Production 
          9. SQL>  SHOW PARAMETER TARGET 
          10. NAME                                 TYPE        VALUE 
          11. ------------------------------------ ----------- ------------------------------ 
          12. archive_lag_target                   integer     0 
          13. db_flashback_retention_target        integer     1440 
          14. fast_start_io_target                 integer     0 
          15. fast_start_mttr_target               integer     0 
          16. pga_aggregate_target                 big integer 399M 
          17. sga_target                           big integer 1200M 
          18. SQL> select * from v$resource_limit where 
          19.   2  resource_name='processes'
          20. RESOURCE_NAME                  CURRENT_UTILIZATION MAX_UTILIZATION INITIAL_AL LIMIT_VALU 
          21. ------------------------------ ------------------- --------------- ---------- ---------- 
          22. processes                                       29              38        300        300 

          i have 1000 concurent students to login with different applications like sqlplus forms etc.

          the problem is often they face th following error

          1. ORA-12519: TNS:no appropriate service handler found 

           

          what sould i increase in my oracle server to avoid such a problem?

          should i convert my database to shared server mode?

          i have 8 gb RAM.

          kindly guide accordingly.

          Regards.

           

          What did your google search of "ORA-12519" suggest?

          • 3. Re: 1000 concurent users connection problem.
            Maahjoor

            hi ,

            i increase the processes but in vain.

            i want to increase the SGA.

            kindly guide.

            Regards.

            • 4. Re: 1000 concurent users connection problem.
              sb92075

              post results from SQL below

               

              SELECT  SESSIONS_HIGHWATER FROM V_$LICENSE;

              • 5. Re: 1000 concurent users connection problem.
                Tushar Thakker

                It may sound like silly question but did you set higher process value in SPFILE? and did you restart the database after that? This issue is mostly caused by processes value only.

                 

                Output of following please.

                SQL> SHOW PARAMETER PROCESSES

                • 6. Re: 1000 concurent users connection problem.
                  Maahjoor

                  hi,

                  i have increased the sga_max_size paramter to 5000mb, and has restarted the database. after a while i will post the result.

                  thanks for helping dear.

                  • 7. Re: 1000 concurent users connection problem.
                    Maahjoor

                    hi,

                    i am facing a very serious problem

                    i increase the max_sga_size to 5gb,

                    shutdown the database, then it is giving the following error

                    ERROR:
                    ORA-12514: TNS:listener does not currently know of service requested in connect
                    descriptor
                    
                    Enter password: ******
                    Connected to an idle instance.
                    SQL> shutdown abort;
                    ORACLE instance shut down.
                    SQL> startup
                    ORA-27100: shared memory realm already exists
                    SQL> shutdown abort;
                    ORACLE instance shut down.
                    SQL> startup
                    ORA-27100: shared memory realm already exists
                    

                     

                    i restart the server as well,

                    but the problem is the same.

                    kindly suggesst.(and this is the production database)

                    regards.

                    • 8. Re: 1000 concurent users connection problem.
                      Tushar Thakker

                      This error will come when you have set SGA_MAX_SIZE (MAX_SGA_SIZE is incorrect) to very close to system memory size. If you reduce SGA_MAX_SIZE then database will start fine.

                      • 9. Re: 1000 concurent users connection problem.
                        Tushar Thakker

                        The error clearly suggests that it is trying to startup a duplicate instance (may be memory was not properly cleaned up during last shutdown). Please shutdown and cleanup instance properly.

                        Also make sure any existing dead processes are not yet running since it may assume that there are existing processes using memory for same instance since you are doing shut abort again and again.

                        Also connect using "/ as sysdba" when you startup instead of TNS to avoid issues.

                        • 10. Re: 1000 concurent users connection problem.
                          Maahjoor

                          i have already clean shutdown and restart.

                          even i could'nt connect to my rman catalog to recover since the catalog is in the same database.(dump)

                           

                          Kindly suggest

                          regards.

                          • 11. Re: 1000 concurent users connection problem.
                            Tushar Thakker

                            You do not need to recover. Since the instance itself is giving error on starting up, your database is untouched.

                            The issue is directly related to SGA parameters. Revert the SGA parameter changes to startup the database first.

                             

                            Restart machine, Connect as sysdba (do not attempt to startup), Create pfile again from spfile, Revert the changes you did for SGA. Startup with PFILE.

                            • 12. Re: 1000 concurent users connection problem.
                              Maahjoor

                              ok, give me time.

                              i have done changes like below

                              alter system set sga_max_size=5000m scope=spfile;

                               

                              how to revert it?

                              • 13. Re: 1000 concurent users connection problem.
                                Maahjoor

                                hi,

                                result is the same,i have restart and try to login as sysdba

                                Enter password: ******
                                Connected to an idle instance.
                                SQL> startup pfile='c:\pfile.ora'
                                ORA-27100: shared memory realm already exists
                                SQL> shutdown abort;
                                ORACLE instance shut down.
                                SQL> startup pfile='c:\pfile.ora'
                                ORA-27100: shared memory realm already exists
                                SQL>
                                

                                regards.

                                • 14. Re: 1000 concurent users connection problem.
                                  Maahjoor

                                  hi,

                                  result is the follwoing.

                                   

                                  Enter password: ******
                                  Connected to an idle instance.
                                  SQL> startup pfile='c:\pfile.ora'
                                  ORA-27100: shared memory realm already exists
                                  SQL> shutdown abort;
                                  ORACLE instance shut down.
                                  SQL> startup pfile='c:\pfile.ora'
                                  ORA-27100: shared memory realm already exists
                                  SQL> SELECT  SESSIONS_HIGHWATER FROM V_$LICENSE;
                                  SELECT  SESSIONS_HIGHWATER FROM V_$LICENSE
                                  *
                                  ERROR at line 1:
                                  ORA-01034: ORACLE not available
                                  

                                   

                                  regards.

                                  1 2 3 Previous Next