Solved

Problem setting up shared server on Oracle 9i

Posted on 2006-11-08
14
740 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:
Stephen Lappin 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
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:Stephen Lappin
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:Stephen Lappin
ID: 17896991
Can you supply the appropraite section from your init.ora please?
0
 
LVL 7

Expert Comment

by:Stephen Lappin
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:Stephen Lappin
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:Stephen Lappin
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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
SQL Retrieve Values 4 67
passing parameters to sql script oracle 4 40
Create table from select - oracle 6 38
Oracle collections 15 17
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
Via a live example, show how to take different types of Oracle backups using RMAN.
This video shows how to recover a database from a user managed backup

808 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