How to convert Dedicated server database to shared server database
Hi,
to put your database in shared server configuration u need to add the following parameters in init
*.dispatchers=’(protocol=TCP)(listener=
*.max_shared_servers=100
*.shared_servers=5
*.max_dispatchers=150
after making the above changes you need shutdown and restart the database with init
Confirm that above parameters are set by logging in sys as sysdba and give the command
SQL> show parameter dispatchers;
SQL> show parameter max_shared_servers; …..
to check whether your listener is aware of the shared server connection from command prompt give the following command
c:\> lsnrctl services
Instance “ITALIC”, status READY, has 6 handler(s) for this service…
Handler(s):
“DEDICATED” established:0 refused:0 state:ready
LOCAL SERVER
“D004″ established:0 refused:0 current:0 max:1002 state:ready
DISPATCHER
(ADDRESS=(PROTOCOL=tcp)(HOST=01HW105868.India.TCS.com)(PORT=2141))
“D003″ established:1 refused:0 current:1 max:1002 state:ready
DISPATCHER
(ADDRESS=(PROTOCOL=tcp)(HOST=01HW105868.India.TCS.com)(PORT=2140))
“D002″ established:1 refused:0 current:0 max:1002 state:ready
DISPATCHER
(ADDRESS=(PROTOCOL=tcp)(HOST=01HW105868.India.TCS.com)(PORT=2139))
“D001″ established:1 refused:0 current:1 max:1002 state:ready
DISPATCHER
(ADDRESS=(PROTOCOL=tcp)(HOST=01HW105868.India.TCS.com)(PORT=2138))
“D000″ established:1 refused:0 current:0 max:1002 state:ready
DISPATCHER
(ADDRESS=(PROTOCOL=tcp)(HOST=01HW105868.India.TCS.com)(PORT=2137))
Also, to make shared server connections you need to add SERVER=SHARED parameters in the CONNECT_DATA parameter of all the tnsnames.ora of clients as shown
SSKIDB1 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 172.17.240.158)(PORT = 1521))
(CONNECT_DATA =
(SERVER = SHARED)
(SERVICE_NAME = SSKIDB)
(INSTANCE_NAME = SSKIDB)
)
)
Now, to check whether all the clients are using shared server connection fire the following query as sys
SQL> select USERNAME,SERVER,MACHINE,TERMINAL,type from v$session where type=’USER’;