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

1000 concurent users connection problem.

Maahjoor Newbie
Currently Being Moderated

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 Guru
    Currently Being Moderated

    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?

  • 2. Re: 1000 concurent users connection problem.
    sb92075 Guru
    Currently Being Moderated

    PROCESSES

     

    increase it

  • 3. Re: 1000 concurent users connection problem.
    Maahjoor Newbie
    Currently Being Moderated

    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 Guru
    Currently Being Moderated

    post results from SQL below

     

    SELECT  SESSIONS_HIGHWATER FROM V_$LICENSE;

  • 5. Re: 1000 concurent users connection problem.
    TusharThakker Explorer
    Currently Being Moderated

    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 Newbie
    Currently Being Moderated

    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 Newbie
    Currently Being Moderated

    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.
    TusharThakker Explorer
    Currently Being Moderated

    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.
    TusharThakker Explorer
    Currently Being Moderated

    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 Newbie
    Currently Being Moderated

    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.
    TusharThakker Explorer
    Currently Being Moderated

    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 Newbie
    Currently Being Moderated

    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 Newbie
    Currently Being Moderated

    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 Newbie
    Currently Being Moderated

    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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points