Solved

Shared Server and Listeners

Posted on 2009-04-14
15
2,181 Views
Last Modified: 2013-12-18
Hi all,

I have a database that will most likely have a few thousand connections.  I wanted to break up the connections so some can have dedicated server processes, and others use pooled/shared processes.  What's the best way to handle this?

I am an Oracle n00b, so here's what I envisioned, and it may be way off...

I set up (2) net service names in tnsnames.ora, and set up (2) listeners in listener.ora (NOTE: I don't use the default LISTENER (1521)). See code below.  PSQA will be for "processing" type connections, as well as for OEM.  PSQAHH will be for user connections, which there will be maybe a thousand or so.

I think I also have to set some of the database init parameters (using SPFILE):

 local_listener
 dispatchers
 service_names

So I figured I need to update the service_names parameter to "PSQA, PSQAHH", and local_listener to "PSQA".  With everything so far, I can connect to the database using both while connected directly to the server:

 $ sqlplus username@psqa
 $ sqlplus username@psqahh

Now is there a way so that only connections that use the service_name "PSQAHH" to get a dispatcher, rather than a dedicated process?  

I tried setting the dispatchers init parameter to "(PROTOCOL=TCP)(SERVICE=PSQAHH)(DISPATCHERS=5)".

When I view the output from both listeners after connecting to PSQAHH, I see the dispatched processes under PSQA:

  $ lsnrctl service psqa

... but I would imagine they should be listed here:

  $ lsnrctl service psqahh

I only see dedicated connections there...


Am I on the right track?

Thanks in advance,

Keith
# tnsnames.ora
 

PSQA =

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = psqadb)(PORT = 4052))

    (CONNECT_DATA =

      (SERVER=DEDICATED)

      (SERVICE_NAME = PSQA)

    )

  )
 

PSQAHH =

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = psqadb)(PORT = 4062))

    (CONNECT_DATA =

      (SERVICE_NAME = PSQAHH)

    )

  )
 
 
 

# listener.ora
 

SID_LIST_PSQA =

  (SID_LIST =

    (SID_DESC =

      (GLOBAL_DBNAME = PSQA)

      (SID_NAME = PSQA)

      (ORACLE_HOME = /in/PSQA/oracle/product/10.2.0.1)

    )

    (SID_DESC =

      (SID_NAME = PLSExtProc)

      (ORACLE_HOME = /in/PSQA/oracle/product/10.2.0.1)

      (PROGRAM = extproc)

    )

  )
 

PSQA =

  (DESCRIPTION_LIST =

    (DESCRIPTION =

      (ADDRESS = (PROTOCOL = TCP)(HOST = psqadb)(PORT = 4052))

    )

  )
 

SID_LIST_PSQAHH =

  (SID_LIST =

    (SID_DESC =

      (GLOBAL_DBNAME = PSQAHH)

      (SERVICE_NAME = PSQAHH)

      (SID_NAME = PSQA)

      (ORACLE_HOME = /in/PSQA/oracle/product/10.2.0.1)

    )

  )
 

PSQAHH =

  (DESCRIPTION_LIST =

     (DESCRIPTION =

       (ADDRESS = (PROTOCOL = TCP)(HOST = psqadb)(PORT = 4062))

     )

  )

Open in new window

0
Comment
Question by:ocgstyles
  • 6
  • 4
  • 3
  • +1
15 Comments
 
LVL 47

Accepted Solution

by:
schwertner earned 500 total points
ID: 24141645
I do not see

(SERVER=shared)

like

sales=
(DESCRIPTION=
  (ADDRESS=(PROTOCOL=tcp)(HOST=sales-server)(PORT=1521))
  (CONNECT_DATA=
     (SERVICE_NAME=sales.us.acme.com)
     (SERVER=shared)))

If the database is configured for shared server and a particular client requires a dedicated server, you can configure the client to use a dedicated server in one of the following ways:

A net service name can be configured with a connect descriptor that contains (server=dedicated) in the CONNECT_DATA section. For example:
sales=
(DESCRIPTION=
  (ADDRESS=(PROTOCOL=tcp)(HOST=sales-server)(PORT=1521))
  (CONNECT_DATA=
     (SERVICE_NAME=sales.us.acme.com)
     (SERVER=dedicated)))



The client profile (sqlnet.ora file) can be configured with USE_DEDICATED_SERVER=on. This adds (server=dedicated) to the CONNECT_DATA section of the connect descriptor the client uses.

Look here

http://www.rdbprime.com/Oracle/Oracle_Docs/Oracle9iDB_Server/network.920/a96580/mts.htm
0
 
LVL 5

Author Comment

by:ocgstyles
ID: 24141817
Do I still need the PSQAHH listener?

I updated tnsnames.ora to reflect "shared":

PSQAHH =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = psqadb)(PORT = 4062))
    (CONNECT_DATA =
      (SERVICE_NAME = PSQAHH)
      (SERVER = shared)
    )
  )

I get this when I try to connect:

ERROR: ORA-12523: TNS:listener could not find instance appropriate for the client connection
0
 
LVL 5

Author Comment

by:ocgstyles
ID: 24143416
Is my my described method of using 2 listeners a viable solution?  It'd be nice if I could use 2 separate service names.  Here's why...

The users use an application running on the app server.  Other application processes run on that app server too, such as daily/weekly close.  I want those other background processes to run dedicated, but I don't care if the user processes run shared.  

Now, I don't even know if I SHOULD care if the background processes run dedicated or not.  Will this mean that Oracle's background processes and OEM will use shared connections?


0
 
LVL 5

Author Comment

by:ocgstyles
ID: 24143619
I think I figured it out...

1) I don't need two listeners.
2) Set service_names database init parameter to "PSQA, PSQAHH".
3) Enable Oracle Shared Server

My listener.ora and tnsnames.ora files now look like attached.  

Now when I connect to each of those net service names, I can see whether or not the connection was shared or dedicated in the listener log.
# TNSNAMES.ORA
 

PSQA =

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = psqadb)(PORT = 4052))

    (CONNECT_DATA =

      (SERVER=DEDICATED)

      (SERVICE_NAME = PSQA)

    )

  )
 

PSQAHH =

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = psqadb)(PORT = 4052))

    (CONNECT_DATA =

      (SERVER=SHARED)

      (SERVICE_NAME = PSQAHH)

    )

  )
 

# LISTENER.ORA
 

SID_LIST_PSQA =

  (SID_LIST =

    (SID_DESC =

      (GLOBAL_DBNAME = PSQA)

      (SID_NAME = PSQA)

      (ORACLE_HOME = /in/PSQA/oracle/product/10.2.0.1)

    )

    (SID_DESC =

      (SID_NAME = PLSExtProc)

      (ORACLE_HOME = /in/PSQA/oracle/product/10.2.0.1)

      (PROGRAM = extproc)

    )

    (SID_DESC =

      (SERVICE_NAME = PSQAHH)

      (GLOBAL_DBNAME = PSQAHH)

      (SID_NAME = PSQA)

      (ORACLE_HOME = /in/PSQA/oracle/product/10.2.0.1)

    )

  )
 

PSQA =

  (DESCRIPTION_LIST =

    (DESCRIPTION =

      (ADDRESS = (PROTOCOL = TCP)(HOST = psqadb)(PORT = 4052))

    )

  )

Open in new window

0
 
LVL 40

Expert Comment

by:mrjoltcola
ID: 24144107
Looks like you have it in hand, I just want to add a note for the PAQ database for future readers.

It is not required to use 2 different services, you can simply provide 2 different TNS aliases in your client tnsnames.ora to connect either via dedicated or shared to the same service. But ocgstyles has a good idea to separate them out, so you can differentiate between shared and dedicated via the listener log or other instance views.
0
 
LVL 47

Expert Comment

by:schwertner
ID: 24145722
Yes, you do not two listeners.
One Listener can serve dedicated and shared connections
according the parameter in tnsname.ora (or connection string
if the connection is done via mechanism other than Oracle client).

Keep it simple and short to avoid two listeners and two ports.
0
 
LVL 34

Expert Comment

by:Mark Geerlings
ID: 24150038
Since you mentioned that you are "an Oracle n00b", I'll ask a couple of related questions.

1. Which server O/S do you have?  I hope it is not Windows.  We tried using Oracle shared_server connections with Windows a few years ago, and it created more problems for us that it solved.  If you have a version of UNIX or Linux, you should be OK.

2. What kind of disk system do you have (local SCSI or SATA, NAS, SAN, etc.).  I hope you don't have local disks only with all of them configured as RAID5.
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 5

Author Comment

by:ocgstyles
ID: 24151993
Thanks for your input everyone.

The box is a 64-bit AIX LPAR with 8 logical CPUs (4 dual cores I think) and 16GB RAM.  All the disk is mapped out on a 2-channel fiber-connected SAN.  The data is striped across 8 4-drive RAID-10 arrays, archive logs on 1 4-drive RAID-5 array, and redo on 1 4-drive RAID-10 array.  Or something like that... ;)
0
 
LVL 34

Expert Comment

by:Mark Geerlings
ID: 24152332
I don't have a lot of 64-bit experience yet, but 16GB of RAM looks a bit small compared to the number of CPUs, the number of disks and the number of users.  That RAM should be relatively easy and inexpensive to expand, if you want to.  I would expect that the system might get much better performance with some more RAM, like 32GB total, or more.

Having the archived redo logs on a RAID-5 array does not look ideal to me, but assuming that the SAN has some write cache, and assuming the write cache is not saturated most of the time, that may be OK.  I would rather see those on non-RAID, RAID0, RAID1 or RAID10, since they tend to be written to, but then never (or rarely) read from, and RAID5 is particularly slow for write operations.  I would rather see the data on RAID-5, than on RAID-10, since most of the data in an Oracle database tends to be read many more times than it gets written, and RAID-5 is very good for read operations.
0
 
LVL 40

Expert Comment

by:mrjoltcola
ID: 24152843
Brings back memories of days at Big Blue. Anyway....

Since you have decided to use shared server, have you any more specifics than "a few thousand" users? What is the architecture of the system? Are these real individual users with full DB sessions, or are you estimating based on a web application.

If this is a web app, I would be doing dedicated server, tuned for maybe 250-500 processes, with connection pooling in the app server.

A few thousand full-session users is a LOT for a single node Oracle box, and I would recommend shared in that case, but I also agree with markgeer, you have a lot of horsepower for only 16GB of RAM. I've had much fewer users on a box with 64GB!

Have you considered RAC?
0
 
LVL 40

Expert Comment

by:mrjoltcola
ID: 24152951
I do not use RAID5 anymore due to bad experience. It has multiple weaknesses that make it unattractive. Recovery time and redundancy are both worse than RAID10. mark's point about data on RAID 5 for reads is true, but the read advantage is not enough to make me choose RAID5 anymore.

I have had multiple systems that lost multiple drives in a RAID 5 before anyone caught it, and the whole volume was toast.

The times we do lose a RAID5 drive, the rebuilds are longer by far.

With RAID10 you can afford to lose multiples, as long as you don't lose both in a mirror and I have experienced this in a live system with a small 4 drives setup, I had 2 drives fail at the SAME time! The RAID10 kept on trucking because I only lost one from each mirror. Yes I was a bit lucky, but not as lucky as I would have to be with RAID5.

With large caches and large SGA, RAID 5 is not for me. Just my 2 cents.
0
 
LVL 5

Author Comment

by:ocgstyles
ID: 24153165
@markgeer
We haven't had any problems with amount of RAM as of yet, but then again the system is fairly new, and not yet up to maximum capacity.  We'll know if that is adequate in a few months.  Since we are still in the deploy phase, most of the time the box is idle...

Yes, the SAN does have a write cache.  The database sees the data as commited when both write caches (mirrored) have the data.  Shortly after redo logs are archived, they are moved to tape.  

I think I reversed the data drive layout.  I think it was supposed to be 4 8-drive RAID10 arrays.  I'd have to check my notes...  The sysadmins that set all the disk up did a few other Oracle databases, and they handle much more volume than this will, so I'm kinda going by what they give me at this point.  So far, its working...  

@mrjoltcola
The other connections are (~300) stores.  The database connections could be from client computers or from handheld devices.  On average, there may be between 8-10 connections per store, but honestly, most of those connections just idle connections most of the time.  I don't anticipate constant usage from all of these connections in a store.  The connections do remain active throughout the day even know someone isn't using the client device.  Because of that, I thought the shared server would be a better way to go.  Otherwise I'd have ~3000 client connections, with their own PGAs, etc.... just doing nothing but wasting RAM.  I'd figure somehow tune the shared server for ~100 concurrent sessions.  

There were talks of RAC, but that idea was scrapped for some reason...

Also, I do recall other systems that suffered failures on RAID5...  maybe that's why the sysadmins stayed away from that for the data... ???
0
 
LVL 40

Expert Comment

by:mrjoltcola
ID: 24153195
>>The other connections are (~300) stores

1) What is a store?
2) Handheld devices with a persistent connection to your DB? What sort of client is running on the handheld? Have you considered a synchronization architecture like Oracle Lite or iAnywhere that does not need persistent connections, or wouldn't that work for your design?

Anyway, I don't mean to question everything, I think shared server is appropriate if you really have this many connections, I was just curious about the architecture.
0
 
LVL 47

Expert Comment

by:schwertner
ID: 24155147
Please do not mix  " few thousand connections" with  "few thousand Oracle sessions".
Practically "few thousand Oracle sessions" will not work.
The Oracle DB will colapse.

There are midletier products like Application servers that use connection pooling
and use significant less Oracle sessions for the " few thousand connections"
that in fact are " few thousand WEB concurent users"

Your decission to use shared mode is correct, but the problem lies in the application
and their load balancing.
0
 
LVL 5

Author Closing Comment

by:ocgstyles
ID: 31570109
Most of the config was with my listeners, but this was the partial solution.  Still testing...
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Suggested Solutions

Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
Via a live example, show how to take different types of Oracle backups using RMAN.
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now