Solved

Problem setting up shared server on Oracle 9i

Posted on 2006-11-08
14
734 Views
Last Modified: 2012-08-13
We have a problem setting up the shared server it seems. The initialization parameters as queried from 'show parameter' are as follows:

DISPATCHERS      (PROTOCOL=TCP)(DISPATCHERS=40)
SHARED_SERVERS   40
MAX_DISPATCHERS   50
MAX_SHARED_SERVERS  50
SHARED_SERVER_SESSIONS   1495

This, as per theory enables the shared server. But all the users are getting connected only as dedicated. We use localnaming method and the TNSNAMES.ORA file contents are as follows :

MYDS =
  (DESCRIPTION =
    (ADDRESS_LIST =
        (ADDRESS =
          (COMMUNITY = tcp.world)
          (PROTOCOL = TCP)
          (Host = 10.10.10.1)
          (Port = 1521)
        )
    )
    (CONNECT_DATA = (SERVICE_NAME = MYDS)
    )
  )


When v$session is queried, we see that 'server' column shows DEDICATED.

Help Needed.
0
Comment
Question by:mganesh
  • 7
  • 6
14 Comments
 
LVL 7

Accepted Solution

by:
lappins earned 125 total points
ID: 17896673
Try modifying your TNSNAMES.ORA as follows:


MYDS =
  (DESCRIPTION =
    (ADDRESS_LIST =
        (ADDRESS =
          (COMMUNITY = tcp.world)
          (PROTOCOL = TCP)
          (Host = 10.10.10.1)
          (Port = 1521)
        )
    )
    (CONNECT_DATA = (SERVICE_NAME = MYDS)
    (SERVER=shared)
    )
  )

0
 
LVL 3

Author Comment

by:mganesh
ID: 17896714
I suppose the default request from clients is for a shared server is available.
Anyway, I did what you said. I get the error,

ORA-12523 TNS:listener could not find instance appropriate for the client connection

0
 
LVL 3

Author Comment

by:mganesh
ID: 17896724
Correction to the above post:

I suppose the default request from clients is for a shared server if  available.
0
 
LVL 7

Expert Comment

by:lappins
ID: 17896739
What is your SID's service name?

Is it MYDS, or MYDS.domain.com

Try adding (SERVICE=MYDS) to your DISPATCHERS setting
0
 
LVL 3

Author Comment

by:mganesh
ID: 17896801
We have no DOMAIN_NAME configured.
show parameter domain_name shows null string.

Still, I did what you said. It is the same, I still get connected as dedicated.

(I am connecting through oracle net and not connecting from the same machine as the server)

Also, here is my listener.ora file :

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS_LIST =
        (ADDRESS = (PROTOCOL = TCP)(HOST = 10.10.10.1)(PORT = 1521))
      )

      (ADDRESS_LIST =
        (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))
      )


    )
  )



SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = /san/apps/oracle/920)
      (PROGRAM = extproc)
    )

    (SID_DESC =
      (GLOBAL_DBNAME = MYDS)
      (ORACLE_HOME = /san/apps/oracle/920)
      (SID_NAME = MYDS)
    )
  )
0
 
LVL 3

Author Comment

by:mganesh
ID: 17896959
And here is the output of
lsnrctl services


Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.10.10.1)(PORT=1521)))
Services Summary...
Service "MYDS" has 1 instance(s).
  Instance "MYDS", status UNKNOWN, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:3587 refused:0
         LOCAL SERVER
Service "PLSExtProc" has 1 instance(s).
  Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:0
         LOCAL SERVER
The command completed successfully
0
 
LVL 7

Expert Comment

by:lappins
ID: 17896991
Can you supply the appropraite section from your init.ora please?
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 7

Expert Comment

by:lappins
ID: 17897008
Does your init.ora have the following entry?

DISPATCHERS="(ADDRESS=(PROTOCOL=TCP)(HOST=10.10.10.1))"
0
 
LVL 3

Author Comment

by:mganesh
ID: 17897027
Here is the complete initMYDS.ora file :


##############################################################################
# Copyright (c) 1991, 2001, 2002 by Oracle Corporation
##############################################################################
 
###########################################
# Cache and I/O
###########################################
db_block_size=16384
db_cache_size=4294967296
db_file_multiblock_read_count=16
max_enabled_roles=148
###########################################
# Cursors and Library Cache
###########################################
open_cursors=450
db_writer_processes=10
###########################################
# Database Identification
###########################################
db_domain=""
db_name=MYDS

log_archive_start=TRUE
log_archive_format="archMYDS%S.arc"
log_archive_dest_1="location=/san/arch/MYDS/"
log_archive_dest_state_1=enable
log_archive_min_succeed_dest=1
 
###########################################
# Diagnostics and Statistics
###########################################
background_dump_dest=/san/apps/oracle/admin/MYDS/bdump
core_dump_dest=/san/apps/oracle/admin/MYDS/cdump
timed_statistics=TRUE
user_dump_dest=/san/apps/oracle/admin/MYDS/udump
 
###########################################
# File Configuration
###########################################
control_files=("/san/apps/oracle/oradata/MYDS/control01.ctl", "/san/usr2/MYDS/control02.ctl", "/san/usr3/MYDS/control03.ctl", "/san/usr4/MYDS/control04.ctl")
 
###########################################
# Instance Identification
###########################################
instance_name=MYDS
 
###########################################
# Job Queues
###########################################
job_queue_processes=10
 
###########################################
# MTS
###########################################
dispatchers="(PROTOCOL=TCP) (DISPATCHERS=40)"
 
###########################################
# Miscellaneous
###########################################
aq_tm_processes=1
compatible=9.2.0.4.0
 
###########################################
# Optimizer
###########################################
hash_join_enabled=TRUE
query_rewrite_enabled=FALSE
star_transformation_enabled=FALSE
 
###########################################
# Pools
###########################################
java_pool_size=104857600
large_pool_size=2147483648
shared_pool_size=1073741824

shared_servers=40

max_shared_servers=50
max_dispatchers=50
###########################################
# Processes and Sessions
###########################################
processes=1100
sessions=1500
###########################################
# Redo Log and Recovery
###########################################
fast_start_mttr_target=300
 
###########################################
# Security and Auditing
###########################################
remote_login_passwordfile=EXCLUSIVE
 
###########################################
# Sort, Hash Joins, Bitmap Indexes
###########################################
pga_aggregate_target=536870912
sort_area_size=524288
 
###########################################
# System Managed Undo and Rollback Segments
###########################################
undo_management=AUTO
undo_retention=10800
undo_tablespace=UNDOTBS1
 
0
 
LVL 7

Expert Comment

by:lappins
ID: 17897043
Try adding host to DISPATCHERS in init.ora

DISPATCHERS="(ADDRESS=(PROTOCOL=TCP)(HOST=10.10.10.1))"


0
 
LVL 3

Author Comment

by:mganesh
ID: 17897061
I gave the command :

alter system set dispatchers='(ADDRESS=(PROTOCOL=TCP)(HOST=10.10.10.1))(DISPATCHERS=40)'

I got the error :

ORA-00105: dispatching mechanism not configured for network protocol
(ADDRESS=(PROTOCOL=TCP)(HOST=10.10.10.1))


0
 
LVL 7

Expert Comment

by:lappins
ID: 17897088
Can you try chaning the init.ora and restarting the instance?
0
 
LVL 3

Author Comment

by:mganesh
ID: 17897100
I will do that once the server becomes sort of free and post back. Thanks for the lightening fast replies.
0
 
LVL 7

Assisted Solution

by:vishal68
vishal68 earned 125 total points
ID: 17904433
try setting the dispatchers as follows

DISPATCHERS=(PROTOCOL=TCP)(DISPATCHERS=40)(LISTENER=(ADDRESS=(PROTOCOL=TCP)(HOST=10.10.10.1)(PORT=1521)))

Then try

HTH
Vishal
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

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…
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
This video shows how to recover a database from a user managed backup

759 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

24 Experts available now in Live!

Get 1:1 Help Now